Sử Dụng Hàm Sumproduct Trong Excel Từ Cơ Bản Đến Nâng Cao

501 Views 0

Hàm Sumproduct là gì?

Hàm sumproduct trong excel là hàm excel cho phép nhân các số trong các mảng (vùng) cụ thể rồi cộng các kết quả của các tích này để cho ra một kết quả cuối cùng.

Hàm SUMPRODUCT là một hàm rất hữu ích trong Excel. Hàm này thay thế được cho hầu hết các hàm khác như hàm SUM, hàm COUNT, hàm SUMIF, COUNTIF, SUMIFS, COUNTIFS… ngoài ra hàm Sumproduct còn giúp bạn tính được trong cả những mảng 2 chiều mà các hàm khác không làm được.

Có thể hiểu đơn giả như sau:

Sumproduct = Sum ( Product )

Product: Tích
Sum: Tổng

Sumproduct là Tổng của các Tích

Lưu ý:

Trên thực tế, đây là một hàm Khó ở cả 2 cấp độ:

– Khó hiểu để viết hàm

– Khó vận dụng để tính toán trong excel

Nhưng hàm này cực kỳ hiệu quả trong nhiều trường hợp khi bạn biết cách vận dụng.

Cú Pháp hàm sumproduct:

SUMPRODUCT(array1, [array2], [array3], …)

Array là mảng.

Lưu ý quan trọng về Array/ Mảng:

Bạn có thể dùng tới 255 mảng trong excel 2007 trở đi, và 30 mảng đối với excel 2003
Độ lớn các mảng phải giống nhau, ngược lại Sumproduct sẽ trả về kết quả lỗi #VALUE
Bất kỳ dữ liệu nào có trong các mảng không phải dữ liệu số, hàm sẽ trả về kết quả là 0

Cách sử dụng hàm sumproduct

Hướng dẫn cách sử dụng hàm sumproduct để tính tổng có điều kiện hay nhiều điều kiện và áp dụng vào các trường hợp củ thể ngay dưới đây nhé.

==> Thảm khảo ngay:

cách sử dụng cơ bản của hàm sumproduct Tính tổng của tích

Như trong hình trên, nếu không biết cách dùng hàm vlookup bạn hoàn toàn có thể tính được tổng doanh thu một cách dễ dàng:

Thêm cột Doanh thu
Tính doanh thu cho từng lần bán
Tính tổng doanh thu ở cuối cùng của danh sách hàng bán.

Nhưng làm như vậy sẽ hơi mất công.

Dùng hàm sumproduct để tính tổng của các tích (tích ở đây là số lượng * giá bán)

Cú pháp hàm tại trường hợp này như sau:

= Sumproduct (A2:A5, B2:B5) = Sum (A2*B2, A3*B3, A4*B4, A5*B5) = 135

Trong đó:

A2:A5: Số lượng
B2:B5: Giá bán

Sử dụng hàm sumproduct để đếm với 1 điều kiện

Trong bảng kê bán hàng ở hình trên, ta có 2 chỉ tiêu là: Số lượng bán dự kiến và số lượng bán thực tế.

Nhiệm vụ của ta là đếm xem có bao nhiêu giao dịch bán hàng có lượng bán thực tế ít hơn dự kiến.

Nếu không biết dùng hàm sumproduct thì bạn sẽ phải thêm cột phụ rồi dùng countif để đếm, hơi mất công cũng như sẽ khó làm khi bảng kê có cả nghìn giao dịch.

Còn với hàm sumproduct thì quá đơn giản như sau:

= Sumproduct ( — (Vùng ô bán dự kiến > Vùng ô bán thực tế ) = Sumproduct (–(B2:B10>C2:C10)) = 4

xong hoàn thành với 30 giây hehe

Hàm sumproduct với nhiều điều kiện dùng để đếm

Tiếp nối phần a của phần ứng dụng nâng cao hàm sumproduct. Ta thêm 1 điều kiện nữa khi đếm đó là:

Đếm số giao dịch của mặt hàng A thỏa mãn điều kiện Số lượng bán thực tế < dự kiến.

Thực sự là khó nhằn hơn rất nhiều,

Ta vận dụng hàm như sau:

= Sumproduct ((A2:A10 = “A”)*(B2:B10<C2:C10)) = 2

Dấu * sẽ giúp kết nối các điều kiện với nhau.

Đây là cách vận dụng rất phổ biến của hàm tính tổng của tích Sumproduct trong thực tế công việc.

HÀM SUMPRODUCT TÍNH TOÁN CHO BẢNG DỮ LIỆU 2 CHIỀU

Trong bảng tính trên, ta có yêu cầu xác định đơn giá của 1 mặt hàng gồm 2 điều kiện: Mã hàng và Mã công ty.

Bảng chứa thông tin cần tìm (Bảng đơn giá) là bảng 2 chiều:

Chiều dọc là thông tin mã Công ty
Chiều ngang là thông tin mã hàng
Giao điểm của các dòng, các cột trong bảng này chính là đơn giá cần tìm của sản phẩm.

Khi đó chúng ta sử dụng hàm SUMPRODUCT như sau:

G3=SUMPRODUCT(($B$14:$B$17=C3)*($C$13:$F$13=B3)*$C$14:$F$17)

Xét mảng Mã công ty (B14:B17) có chứa mã công ty tại ô C3 không
Xét mảng Mã hàng (C13:F13) có chứa mã hàng tại ô B3 không
Nếu chứa kết quả đúng ở cả 2 mảng trên, thì sẽ lấy tương ứng theo đơn giá trong vùng C14:F17 theo dòng và cột chứa các mã đó.

Như vậy qua một số định nghĩa về hàm Sumproduct chúng ta đã hiểu hơn về hàm này có công dụng gì cũng như các sử dụng hàm Sumproduct trong excel.

Với nhiều ví dụ khác nhau về cách sử dụng hàm Sumproduct tính tổng các điều kiện trên, chúng ta đã có thể biết được cách kết hợp các hàm khác bên trong hàm SUMPRODUCT, cách sử dụng hàm SUMPRODUCT để tính toán trên những bảng tính 2 chiều.

Ngoài ra các bạn có thể tham khảo thêm một số bài viết sau về các hàm trong excel để biết được những ưu nhược điểm của hàm SUMPRODUCT khi so sánh với hàm SUMIFS, COUNTIFS. Chúc bạn thành công