Hôm nay Hocgiasuketoan.com gửi tới các bạn seri bài viết về chủ đề Excel với kế toán: “Ứng dụng hàm SUMPRODUCT”. Đây là một hàm có sức mạnh rất lớn, có thể thay thế hầu hết mọi hàm khi làm kế toán trên excel, hay nói cách khác khi nghĩ tới làm kế toán trên excel là chúng ta phải nghĩ ngay tới hàm SUMPRODUCT. Tại sao nó lại quan trọng như vậy? Sau đây chúng ta cùng tìm hiểu cách ứng dụng hàm SUMPRODUCT sẽ rõ.

Nội dung Seri bài viết:

Phần 1: Cấu trúc hàm SUMPRODUCT, ứng dụng vào việc tính tổng theo nhiều điều kiện, so sánh với hàm SUMIFS

Phần 2: Tính tổng phát sinh của tài khoản tổng hợp dựa trên ghi sổ của tài khoản chi tiết

Phần 3: Ứng dụng hàm SUMPRODUCT trong kế toán kho

Phần 4: Ứng dụng hàm SUMPRODUCT trong kế toán tiền lương: xử lý dữ liệu máy chấm công

….

Ứng dụng 4: Ứng dụng trong kế toán tiền lương: Xử lý dữ liệu máy chấm công vào bảng chấm công

Bài tập:

Cho bảng dữ liệu trích xuất từ máy chấm công (phần bên phải hình, từ cột AI đến AN)

Yêu cầu: tự động đưa dữ liệu cột chấm công (AN) vào bảng chấm công tháng 10 tương ứng với từng nhân viên theo từng ngày trong tháng.

Cách làm:

Bước 1: Phân tích đề bài

Ở bảng dữ liệu trích xuất từ máy chấm công chúng ta có các đối tượng sau:

• Mã NV (cột AI) tương ứng với mã NV (cột B) trong Bảng chấm công
• Ngày (Cột AJ) tương ứng với dòng 4 trong Bảng chấm công (ngày trong tháng, vùng C4:AG4)
• Kết quả chấm công (cột AN) là kết quả chúng ta cần điền từ bảng dữ liệu máy chấm công vào Bảng chấm công tương ứng với 2 điều kiện: Mã NV và Ngày.

Ở đây cột chấm công có 2 loại công (hoặc nhiều hơn, chúng ta ví dụ với 2), và kết quả yêu cầu không phải tính tổng, mà là đối chiếu dữ liệu, nên chúng ta không thể dùng được các hàm như SUMIFS mà phải sử dụng hàm COUNTIFS hoặc SUMPRODUCT. Trong seri bài viết này chúng ta sẽ ưu tiên việc xử lý bằng hàm SUMPRODUCT

Xem ngay: học Excel kế toán ở Hà Nội

Bước 2: Xây dựng nội dung hàm SUMPRODUCT

* Xét loại công là X

Hàm Sumproduct 1 = SUMPRODUCT((Cot_Ngay=C$4)*(Cot_Ma_NV=$B6)*(Cot_Cham_cong=”X”)*1)

Cot_Ngay = cột AJ

Cot_Ma_NV = cột AI

Cot_Cham_cong = cột AN

Hàm này cho kết quả >0 hoặc <0. Nếu <0 thì không có số công là X, còn nếu >0 thì có số công là X

* Xét loại công là A1

Hàm Sumproduct 2 = SUMPRODUCT((Cot_Ngay=C$4)*(Cot_Ma_NV=$B6)*(Cot_Cham_cong=”A1″)*1)

Hàm này cho kết quả >0 hoặc <0. Nếu <0 thì không có số công là A1, còn nếu >0 thì có số công là A1

Lưu ý:

• Các giá trị C4 được cố định phần hàng để khi copy công thức xuống dưới không thay đổi nội dung Ngày ở dòng 4 của Bảng chấm công
• Các giá trị B6 được cố dịnh phần cột để khi copy công thức sang bên phải không bị thay đổi nội dung Mã NV ở cột B của Bảng chấm công
• Các giá trị X hay A1 đặt trong dấu nháy kép vì là dạng ký tự cố định.

Vì 2 loại công này có thể xuất hiện cùng trên kết quả, nên ta sẽ sử dụng hàm IF để biện luận tính logic của thứ tự các nội dung:

• Nếu hàm Sumproduct 1 cho kết quả >0 thì sẽ lấy kết quả chấm công là X
• Nếu hàm Sumproduct 1 cho kết quả = 0 thì sẽ xét sang hàm Sumproduct 2
• Nếu hàm Sumproduct 2 cho kết quả > 0 thì sẽ lấy kết quả chấm công là A1
• Nếu hàm Sumproduct 2 cho kết quả = 0 thì sẽ lấy kết quả chấm công là rỗng

Nội dung hàm biểu diễn như sau:

C6 = IF(SUMPRODUCT((Cot_Ngay=C$4)*(Cot_Ma_NV=$B6)*(Cot_Cham_cong=”X”)*1)>0,”X”,IF(SUMPRODUCT((Cot_Ngay=C$4)*(Cot_Ma_NV=$B6)*(Cot_Cham_cong=”A1″)*1)>0,”A1″,””))

Chúng ta có thể copy công thức từ C6 sang các ô ngày công còn lại cho các nhân viên khác trong bảng chấm công

Kết quả cuối cùng như sau:

Cảm ơn các bạn đã luôn đồng hành cùng Học Gia Sư Kế Toán