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

Chào các bạn,

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

…

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:

Cấu trúc hàm SUMPRODUCT(array1, [array2], [array3], …)

Hàm Sumproduct là hàm xử lý với đối tượng là các mảng. Ở đây chúng ta cần lưu ý phân biệt khái niệm “Mảng = Array” với khái niệm “Vùng = Range” (hàm SUMIFS là xét trên đối tượng Range). Array có sức mạnh hơn hẳn Range khi chúng ta có thể tham chiếu tới một phần nội dung nhất định trong mỗi ô trong mảng (có thể không phải toàn bộ nội dung trong mỗi ô đó), còn Range không làm được điều đó. Đổi lại việc sử dụng Array lại khó hơn dùng Range.

Sumproduct là ghép giữa SUM với PRODUCT, là Tổng với Tích, được hiểu là thỏa mãn đồng thời nhiều điều kiện thì sẽ tính tổng các giá trị thỏa mãn điều kiện.

Ứng dụng 1: Tính tổng theo nhiều điều kiện, so sánh với hàm SUMIFS

Đề bài: Cho bảng số liệu từ vùng A1:E9, yêu cầu tính tổng số Nợ cho tài khoản 1111 (G6) theo thời gian tùy chọn Từ ngày, Đến ngày (H3 và H4) theo 2 cách:

Cách 1: ô H6 sử dụng hàm SUMPRODUCT

Cách 2: ô H7 sử dụng hàm SUMIFS

Cách làm:

với hàm SUMIFS chúng ta phân tích nội dung hàm như sau:

Sum_range là vùng cần tính tổng, là E2:E9

Criteria_range1 là vùng điều kiện thứ 1, cột TK Nợ (C2:C9)
Criteria1 là điều kiện thứ 1, nằm trong vùng điều kiện thứ 1, ô G6
Criteria_range2 là vùng điều kiện thứ 2, cột Ngày (A2:A9)
Criteria2 là điều kiện thứ 2, nằm trong vùng điều kiện thứ 2, ô H3, ở đây là >=H3
Criteria_range3 là vùng điều kiện thứ 3, cột Ngày (A2:A9)
Criteria3 là điều kiện thứ 3, nằm trong vùng điều kiện thứ 2, ô H4, ở đây là <=H4 (vì giá trị ngày bao gồm 2 giá trị nên phải tách ra 2 điều kiện)

H7 = SUMIFS($E$2:$E$9,$C$2:$C$9,$G$7,$A$2:$A$9,”>=”&$H$3,$A$2:$A$9,”<=”&$H$4)

Với hàm SUMPRODUCT chúng ta phân tích nội dung hàm như sau:

Array1 là vùng cần tính tổng E2:E9
Array2 là vùng chứa TK Nợ, với các ô có giá trị là 1111
Array3 là vùng chứa Ngày, với các ô có ngày >=H3
Array4 là vùng chứa Ngày, với các ô có ngày <=H4

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

*** Nhận xét:

Kết quả của 2 hàm là như nhau => Có tác dụng giống nhau trong trường hợp này (điều kiện được xác định cụ thể và đối tượng tham chiếu trong Array là đối tượng có thể xác định trực tiếp).
Điều kiện hàm SUMIFS biểu diễn khác hàm SUMPRODUCT, Criteria_range và Criteria được phân biệt rõ từng thành phần trong hàm, còn Array được gộp cả Range và Criteria vào một
Lưu ý khi xác định điều kiện dạng >=, <= thì với hàm SUMIFS phải đặt dấu đó trong dấu nháy kép, nối với Criteria bởi dấu &, còn hàm SUMPRODUCT có thể sử dụng trực tiếp.
Trường hợp này có thể thấy SUMPRODUCT giống với SUMIFS về độ dài và cách biện luận, thứ tự logic cũng giống nhau. Hàm SUMIFS là một hàm có tính ứng dụng rất cao và dễ viết, và chúng ta có thể thấy hàm SUMPRODUCT cũng có thể thay thế hoàn toàn cho hàm SUMIFS

Trong các bài học sau chúng ta sẽ tìm hiểu các ứng dụng khác của hàm SUMPRODUCT để khám phá thêm sức mạnh của hàm này.

Cảm ơn các bạn đã chú ý theo dõi.