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 ]
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:B10140]: thực hiện so sánh từng phần tử của mảng với 140.
Ví dụ này sử dụng hàm SUMPRODUCT nhiều điều kiện thay cho hàm COUNTIFS vẫn thường sử dụng
Giải thích cách sử dụng hàm SUMPRODUCT nhiều điều kiện:
- Biểu thức [A4:A8=”táo”]: thực hiện kiểm tra từng phần tử của mảng có phải là Táo hay không: kết quả {TRUE, TRUE, FALSE, FALSE, TRUE}
- Biểu thức [C4:C8>99]: thực hiện kiểm tra từng phần tử của mảng có lớn hơn 99 hay không: kết quả {TRUE, TRUE, TRUE, TRUE, FALSE}
- Dấu * trong hàm biến TRUE/ FALSE về dạng số 1/ 0
Hàm SUMPRODUCT thực hiện chức năng trả về số sản phẩm thỏa mãn cả 2 điều kiện
Khi mới học cách sử dụng hàm sumproduct, nhiều người thường mắc phả lỗi #VALUE và dưới đây là một trong những nguyên nhân gây nên tình trạng này
Vùng tham chiếu trong đối số của hàm Sumproduct có kích thước không giống nhau, điều này sẽ tạo ra lỗi #VALUE!
Trong vùng tham chiếu có 1 hay nhiều ô chứa lỗi #VALUE! cũng là lý do gây ra lỗi #VALUE!
Cách khắc phục: chỉnh sửa lại dữ liệu dùng để tính toán trước khi áp dụng hàm Sumproduct
Kết luận
Trên đây mình đã giới thiệu cho các bạn về hàm sumproduct. Hy vọng với những gì hướng dẫn, mọi người nắm rõ và có thể ứng dụng được vào trong công việc của mình.