Hàm Sumproduct

Thay vì nhân từng đối tượng sau đó gộp lại tính tổng, với hàm sumproduct bạn chỉ cần thực hiện một bước mà thôi. Ngoài ra, bạn có thể sử dụng các biến thể nâng cao của hàm này vào nhiều công việc khác, rút ngắn thời gian thống kê, tính toán. Cú pháp và cách sử dụng hàm sumproduct chi tiết trong bài viết dưới đây.

1. Cú pháp và cách sử dụng hàm sumproduct

Hiểu đơn giản, hàm sumproduct gộp hai bước nhân các đối tượng trong một mảng và cộng các tích đó lại với nhau. Từ đó, bạn nhanh chóng nhận được tổng mong muốn, tránh thêm sai sót. 

Cú pháp hàm sumproduct như sau:

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

Trong đó, array1, array2, array3… là các mảng tương ứng chứa các thành phần bạn muốn nhân sau đó tính tổng. Theo đó, số mảng tối thiểu là 1, tối đa là 255 (đối với Excel 2004, 2010, 2013, 2016), tối đa là 30 đối với các phiên bản Excel trước 2007.

Một số lưu ý khi sử dụng hàm sumproduct như sau:

  • Bạn không sử dụng Ctrl + Shift + Enter để kết thúc hàm (mặc dù sumproduct là hàm xử lý mảng), chỉ cần nhấn Enter là được.

  • Khi nhập công thức hàm sumproduct, tất cả các mảng đều phải có cùng số hàng và số cột (vì nó nhân với nhau), nếu không hàm sẽ cho kết quả lỗi #VALUE!

  • Bất cứ ô nào trong mảng không phải giá trị số, hàm sẽ tự hiểu chúng có giá trị bằng 0.

  • Hàm sumproduct sẽ trả về giá trị TRUE và FALSE nếu các mảng được nhập trong hàm là phép thử logic. Các phép thử này sẽ được đổi thành 1 và 0 tương ứng.

  • Không hỗ trợ ký tự đại điện trong hàm sumproduct.

Ví dụ hàm sumproduct như sau: Tính tổng thành tiền hàng hóa cần mua.

Nếu không sử dụng hàm sumproduct, trong trường hợp này bạn sẽ thực hiện 2 bước như sau:

  • Bước 1: Tính tiền phải trả cho từng loại mặt hàng bằng cách nhân từng Đơn giá với Số lượng.

  • Bước 2: Tính tổng số tiền phải trả bằng cách tính tổng các kết quả phép nhân ở trên (như hình dưới đây).

Nếu sử dụng hàm sumproduct công việc này sẽ nhanh hơn nhiều, chỉ trong 1 bước là xong. Bạn nhập công thức hàm như sau:

C8=SUMPRODUCT(C3:C7,D3:D7), trong đó:

  • C3:C7: Mảng 1 chứa tất cả Đơn giá sản phẩm.

  • D3:D7: Mảng 2 chứa tất cả Số lượng sản phẩm.

2. Một số biến thể khác của hàm sumproduct

Hàm sumproduct còn một số biến thể khác nâng cao, trong công thức đi kèm điều kiện các mảng. Nó giúp thực hiện nhiều công việc phức tạp khác một cách nhanh chóng, đơn giản hơn. 

2.1. Hàm sumproduct một điều kiện

Trong trường hợp này, bạn có thể sử dụng hàm sumproduct như một hàm điều kiện, kết quả trả về 1 và 0. Cú pháp hàm như sau:

=SUMPRODUCT(--(<điều kiện tương tác>))

Để hiểu rõ hơn, mời bạn tham khảo ví dụ như hình dưới đây:

Hình trên là bảng thống kê tiền đầu tư cho một dự án và số tiền thu về (doanh thu) từ dự án đó của một công ty. Trong đó, nếu doanh thu > vốn là công ty lời, doanh thu < vốn là công ty lỗ, doanh thu = vốn nghĩa là hòa vốn. Làm thế nào để xác định có bao nhiêu dự án kiếm lời, bao nhiêu dự án lỗ và bao nhiêu hòa vốn? 

Bạn chỉ cần sử dụng công thức hàm sumproduct 1 điều kiện là được. Công thức cụ thể có trong phần in đậm bảng trên. Lúc này, hàm sumproduct không tính tích rồi cộng tổng nữa, nó thực hiện so sánh các thành phần trong mảng rồi trả về kết quả 1 nếu điều kiện đúng và trả về 0 nếu điều kiện sai. Sau đó, các số 1 được công với nhau để ra số lượng cần đếm tương ứng. 

2.2. Hàm sumproduct nhiều điều kiện 

Không chỉ với 1 điều kiện, bạn có thể áp dụng nhiều điều kiện một lúc trong hàm sumproduct bàng cách sử dụng and và or. 

Vẫn ví dụ tính lãi đầu tư dự án như trên, yêu cầu là tìm số dự án có cả lãi và vốn đều trên 10 tỷ đồng. Lúc này có 2 điều kiện là Vốn đầu tư < Doanh thu và Vốn đầu tư > 10. Công thức hàm sumproduct được sử dụng có trong hình dưới đây:

Với ví dụ này, hàm sẽ trả về kết quả thỏa mãn cả 2 điều kiện. Trường hợp khác, nếu bạn muốn hàm sumproduct trả về kết quả chỉ cần thỏa mãn một trong 2 điều kiện thì sao? Bạn hãy tham khảo ví dụ dưới đây:

Bạn có để ý thấy 2 công thức khác nhau chỉ ở duy nhất 1 dấu phẩy trong vùng điều kiện không? Như vậy, trong công thức hàm sumproduct nhiều điều kiện:

  • Nếu thêm dấu phẩy vào giữa các điều kiện nó có nghĩa là “và”, kết quả đúng cần thỏa mãn tất cả điều kiện trong hàm.

  • Nếu không có dấu phẩy nghĩa là “hoặc”, kết quả đúng chỉ cần thỏa mãn 1 trong số các điều kiện trong hàm.

2.3. Tính tổng có điều kiện

Kết hợp phép đếm kèm điều kiện như trên, bạn có thể sử dụng hàm sumproduct để tính tổng các đối tượng phù hợp với điều kiện đó. Cú pháp hàm như sau:

=SUMPRODUCT(--(<điều kiện tương tác>),)

Mời bạn theo dõi ví dụ trong hình dưới đây:

Yêu cầu là tính tổng vốn đầu tư đổ vào các dự án lỗ. Để làm được điều này, bạn cần tìm ra các dự án lỗ có Vốn đầu tư > Doanh thu, sau đó tính tổng chúng với nhau. Công thức hàm sumproduct có trong hình dưới đây:

Trong công thức hàm này, phần trước giống với hàm điều kiện, phần sau thêm vào mảng chứa dữ liệu tính tổng. Khi đó, không chỉ đếm các phần tử phù hợp với điều kiện mà còn tính tổng chúng với nhau. 

Trên đây đã giải thích toàn bộ cú pháp và cách sử dụng hàm sumproduct trong Excel. Từ đây bạn có thể áp dụng nó vào việc tính tổng các số có kèm theo điều kiện một cách nhanh chóng hơn. Nếu thấy bài viết hữu ích, bạn hãy chia sẻ với bạn bè và theo dõi website để cập nhập thêm các nội dung mới về hàm Excel nhé!

giỏ hàng dbk 0 (0) sản phẩm.
DBK VIỆT NAM, https://dbk.vn/ham-sumproduct.html,
21/15 đường số 17
Thu Đuc, HCM, 700000
Việt Nam
+84919219111