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 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

Đề 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))

Như vậy chúng ta đã có thể tùy biến mảng trong hàm SUMPRODUCT để áp dụng cho trường hợp đối tượng cần tính chỉ gồm một phần trong mảng.

Trong các bài học sau chúng ta sẽ tìm hiểu thêm những trường hợp tùy biến khác của hàm SUMPRODUCT trong kế toán để sử dụng hàm này hiệu quả hơn.

Cảm ơn các bạn đã luôn theo dõi và đồng hành cùng Học Gia Sư Kế Toán chúng tôi.!