Excel kế toán: Ứng dụng hàm Sumproduct – Phần 2

Hôm nay hocexcel.online 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 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

Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:

Đề bài: Cho bảng dữ liệu từ vùng A1:E9, yêu cầu tính tổng phát sinh Nợ và phát sinh Có theo thời gian xác định tại vùng H3:H4, theo tài khoản chi tiết 1111 và tài khoản tổng hợp 111 (áp dụng công thức SUMPRODUCT)

Cách làm:

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

Các điều kiện cần tính:

H6 = Tổng sốtiền + Phát sinh Nợ Tài khoản 111 + Thời gian từ ngày H3 + Thời gian đến ngày H4
I6 = Tổng sốtiền + Phát sinh Cợ Tài khoản 111 + Thời gian từ ngày H3 + Thời gian đến ngày H4
H7 = Tổng sốtiền + Phát sinh Nợ Tài khoản 1111 + Thời gian từ ngày H3 + Thời gian đến ngày H4
I7 = Tổng sốtiền + Phát sinh Cợ Tài khoản 1111 + Thời gian từ ngày H3 + Thời gian đến ngày H4

Như vậy khi áp dụng hàm SUMPRODUCT chúng ta cần sử dụng 4 Array

Bước 2: Xây dựng công thức (hàm) SUMPRODUCT

Xét tại ô H6:

Array1 là mảng cần tính tổng, ở đây là cột Số tiền =E2:E9
Array2 là mảng Tài khoản Nợ với những giá trị là 111, là vùng C2:C9=G6
Array3 là mảng Ngày với những giá trị là Từ ngày H3, là vùng A2:A9>=H3
Array4 là mảng Ngày với những giá trị là Đến ngày H4, là vùng A2:A9<=H4

H6 =SUMPRODUCT($E$2:$E$9*($C$2:$C$9=$G6)*($A$2:$A$9>=$H$3)*($A$2:$A$9<=$H$4))

Tương tự chúng ta làm cho các kết quả khác, lưu ý những yêu cầu về Phát sinh Có sẽ tham chiếu vùng D2:D9 thay cho C2:C9

I6 =SUMPRODUCT($E$2:$E$9*($D$2:$D$9=$G6)*($A$2:$A$9>=$H$3)*($A$2:$A$9<=$H$4))

H7 =SUMPRODUCT($E$2:$E$9*($C$2:$C$9=$G7)*($A$2:$A$9>=$H$3)*($A$2:$A$9<=$H$4))

I7 =SUMPRODUCT($E$2:$E$9*($D$2:$D$9=$G7)*($A$2:$A$9>=$H$3)*($A$2:$A$9<=$H$4))

Công thức trên chỉ đúng với tài khoản 1111 vì các nội dung trên ghi sổ tại cột TK Nợ và TK Có có tài khoản này, còn không có tài khoản 111.

Vậy làm thế nào để tùy biến công thức trên để vẫn tính được với tài khoản 111 (được hiểu là tổng của các tài khoản chi tiết của tài khoản 111)?

Xem thêm: học Excel kế toán online cùng các chuyên gia

Bước 3: Xử lý mảng cho Tài khoản tổng hợp

Ở cột TK Nợ, chúng ta thấy tài khoản tổng hợp có 3 chữ số, nên chúng ta xét phần bên trái của mỗi đối tượng trong mảng này như sau:

Array2 = LEFT(C2:C9,3)=”111″

Áp dụng trong hàm SUMPRODUCT như sau:

H6 = SUMPRODUCT($E$2:$E$9*(LEFT($C$2:$C$9,3)=”111″)*($A$2:$A$9>=$H$3)*($A$2:$A$9<=$H$4))

I6 = SUMPRODUCT($E$2:$E$9*(LEFT($D$2:$D$9,3)=”111″)*($A$2:$A$9>=$H$3)*($A$2:$A$9<=$H$4))