Khi tính tổng có điều kiện trong Excel, chắc nhiều bạn hay sử dụng hàm tổng có điều kiện SUMIF hoặc SUMIFS. Tuy nhiên để tính tổng các mảng có điều kiện trong Excel, thì việc sử dụng hàm SUMPRODUCT hiệu quả hơn nhiều.
Bài viết này sẽ hướng dẫn các bạn cách sử dụng hàm SUMPRODUCT tính tổng nhiều điều kiện, so sánh dữ liệu ở nhiều dải ô và cách kết hợp với các hàm khác mà nếu không nhờ hàm SUMPRODUCT thì kết quả hàm mảng trong Excel thông thường đó sẽ không chính xác.
Mục Lục Bài Viết
1, Hàm SUMPRODUCT Là Gì? Cú Pháp Và Cách Sử Dụng
1.1, Cú Pháp Hàm SUMPRODUCT
Hàm SUMPRODUCT nhân các dải ô hoặc mảng với nhau và trả về tổng của các phép tính nhân đó.
Lệnh SUMPRODUCT có cú pháp:
=SUMPRODUCT(array1, [array2], [array3], …)
Trong đó:
-
Array1, array2, … là các dải ô liên tục hoặc mảng mà bạn muốn nhân các thành phần của chúng với nhau rồi cộng lại để tính tổng. Tối đa 255 mảng trong Excel 2007, 2010, 2013, 2016, 2019 và 365.
-
Array 1 là bắt buộc.Cách hàm SUMPRODUCT hoạt động: nhân, sau đó tính tổng. Nếu chỉ có một mảng, SUMPRODUCT sẽ chỉ đơn thuần tính tổng các mục trong mảng.
- Tất cả mảng/ array trong công thức SUMPRODUCT phải có cùng kích thước, nếu không thì hàm SUMPRODUCT sẽ trả về lỗi #VALUE!
Trên đây là những lưu ý cơ bản nhất về công thức hàm SUMPRODUCT.
Các lưu ý khi sử dụng hàm SUMPRODUCT nâng cao cho các công thức mảng và tính tổng có điều kiện được hướng dẫn tại mục 2.
1.2, Cách Dùng Hàm SUMPRODUCT Trong Excel - Chức Năng Cơ Bản Nhất
Ví Dụ 1: Tính Tổng Các Số Lượng Nhân Đơn Giá
Ví dụ dưới đây cho số lượng và đơn giá của các sản phẩm. Yêu cầu tính tổng doanh thu của các sản phẩm (tổng các số lượng nhân đơn giá).
Hàm SUMPRODUCT xử lý nhanh bài toán này với chỉ 1 câu lệnh, trong khi nếu sử dụng phép cộng và lệnh nhân trong Excel một cách thủ công, bạn sẽ phải thao tác và thêm cột phụ:
Diễn giải hàm SUMPRODUCT trong ví dụ trên: Mỗi ô trong cột B được nhân với ô tương ứng cùng hàng ngang trong cột C và cộng các kết quả đó lại với nhau. Tổng số tiền là 300.
= SUMPRODUCT ( {10, 5, 2}, {15, 10, 50})
= 10*15 + 5*10 + 2*50
Ví Dụ 2: Cách Sử Dụng SUMPRODUCT Làm Hàm Tính Trung Bình
Công thức SUMPRODUCT tính trung bình cộng có trọng số:
SUMPRODUCT(giá trị, trọng số) / SUM(trọng số)
2, Các Ví Dụ Cách Sử Dụng Hàm SUMPRODUCT Nâng Cao Trong Excel Với Công Thức Mảng
Nếu chỉ dừng lại ở ví dụ trên, bạn sẽ thấy hàm SUMPRODUCT phức tạp và vô nghĩa vì hàm cộng và hàm nhân trong Excel đủ để giải quyết bài toán đó rồi.
Nhưng thực tế, hàm SUMPRODUCT là linh hoạt và nhiều công dụng hơn rất nhiều khi tính tổng theo điều kiện.
Các ví dụ ở dưới đây sẽ làm rõ điều đó.
Lưu ý: Hàm SUMPRODUCT hoạt động với mảng, nhưng nó không yêu cầu cú pháp mảng bình thường (Ctrl + Shift + Enter) để chạy mà chỉ cần Enter như bất kỳ hàm Excel cơ bản nào khác.
2.1, Sử Dụng Hàm SUMPRODUCT Đếm Có Điều Kiện
Ví Dụ 3: Đếm Số Sản Phẩm Thỏa Mãn Điều Kiện So Sánh Với 1 Số
Ví dụ này có thể xử lý đơn giản bằng hàm COUNTIF, tuy nhiên được đưa vào để các bạn hiểu rõ cách hoạt động của hàm SUMPRODUCT có điều kiện với mảng:
Lí giải cách hoạt động của hàm SUMPRODUCT có 1 mảng có điều kiện ở trên:
-
Biểu thức (C4:C8>140): thực hiện so sánh từng phần tử của mảng với 140.
-
Sau khi so sánh xong các phần tử trong mảng, hàm SUMPRODUCT trở thành:
SUMPRODUCT(- {FALSE, FALSE, TRUE, TRUE, FALSE})
-
Dấu âm “ - ” đặt trước mảng để biến các giá trị TRUE/ FALSE về dạng số nguyên -1 và 0 thì kết quả hàm SUMPRODUCT mới trả về số chính xác, nếu không hàm sẽ trả về 0.
Có thể sử dụng 2 dấu “-” để trả TRUE/ FALSE về 1 và 0.
Ví Dụ 4: Đếm Số Sản Phẩm Khi So Sánh 2 Mảng Với Nhau
Yêu cầu đếm số sản phẩm có doanh số thực tế lớn hơn kế hoạch.
Bạn không thể sử dụng hàm COUNTIF đơn thuần để tính toán như ở ví dụ 2 nữa. Để dùng hàm COUNTIF, bạn cần thêm cột phụ đặt điều kiện so sánh thực tế và kế hoạch rồi đếm số kết quả TRUE.
Với công thức SUMPRODUCT, chỉ cần sử dụng 1 câu lệnh duy nhất. Ví dụ này dần cho thấy sự linh hoạt của hàm SUMPRODUCT khi thực hiện so sánh 2 mảng mà không cần sử dụng cột phụ:
Chi tiết cách hoạt động của hàm SUMPRODUCT:
-
Biểu thức (C4:C8>B4:B8): thực hiện so sánh từng phần tử của 2 mảng với nhau: C4 với B4, C5 với B5…
-
Sau khi so sánh xong các phần tử của 2 mảng, hàm SUMPRODUCT trở thành:
SUMPRODUCT(- {TRUE, FALSE, FALSE, FALSE, TRUE})
-
Dấu âm “ - ” đưa hàm về dạng SUMPRODUCT({-1, 0, 0, 0, -1}), kết quả bằng -2.
Trước hàm SUMPRODUCT có thêm 1 dấu âm nên kết quả đếm là 2.
Bạn cũng có thể sử dụng 2 dấu âm liên tiếp =SUMPRODUCT(--(C4:C8>B4:B8)).
Ví Dụ 5: Sử Dụng Hàm SUMPRODUCT Nhiều Điều Kiện Thay Thế Hàm COUNTIFS
Yêu cầu đếm số sản phẩm là “Táo” có số lượng trên 99.
Ví dụ này có thể xử lý đơn giản bằng hàm COUNTIFS, nhưng vẫn được đưa vào để các bạn hiểu rõ cách hoạt động của hàm SUMPRODUCT nhiều điều kiện. Tương tự các hàm Excel khác, điều kiện là văn bản không phân biệt viết hoa viết thường:
Giải thích logic 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 của nó trả về số sản phẩm thỏa mãn đồng thời cả 2 điều kiện (vì 1 nhân 0 bằng 0 nên chỉ khi cả 2 phần tử bằng 1 thì mới được cộng vào kết quả của hàm).
Lưu ý quan trọng:
Ví dụ 3 và 4 đều sử dụng cách dùng dấu âm còn ví dụ 5 sử dụng phép nhân để biến TRUE/ FALSE về dạng số.
Đây là chìa khóa cho cách sử dụng hàm SUMPRODUCT nhiều điều kiện:
-
Nếu mảng là biểu thức so sánh lô gic, thì nó sẽ trả về giá trị TRUE và FALSE
- Bạn luôn phải đổi các giá trị TRUE và FALSE này thành 1 và 0 bằng cách sử dụng một trong 3 cách sau, hoặc kết hợp nhiều cách khi viết hàm:
- Phép trừ/ dấu âm (–) đặt trước mảng, ví dụ -(A4:A8="táo") hoặc --(A4:A8="táo"). Cần lưu ý dấu của kết quả, mỗi 1 dấu âm được sử dụng khiến kết quả của hàm được nhân -1 tương ứng;
- Phép nhân với số 1, như 1*(A4:A8="táo");
-
Phép nhân giữa các mảng với nhau nếu có từ 2 array trong hàm SUMPRODUCT, ví dụ (A4:A8="táo")*(C4:C8>99).
-
Mảng và điều kiện của mảng cần đặt trong dấu ngoặc đơn.
2.2, Sử Dụng Hàm SUMPRODUCT Tính Tổng Có Điều Kiện
Ví dụ 6: Hàm SUMPRODUCT Tính Tổng Các Mã Giống Nhau Trong Excel
Để tính tổng doanh thu sản phẩm là Táo, bạn chỉ cần xử lý tương tự như logic ở các ví dụ trên, và thêm mảng doanh thu vào công thức SUMPRODUCT:
= SUMPRODUCT ((A4:A8=”táo”)*C4:C8)
Kết quả mảng: SUMPRODUCT ({1, 1, 0, 0, 1} * {100, 150, 220, 155, 80})
Ví dụ 7: Hàm SUMPRODUCT Tính Tổng Theo Điều Kiện
Để tính tổng doanh thu sản phẩm là Táo bán tại khu vực B, dù 2 hay nhiều điều kiện thì bạn chỉ cần hiểu logic như đã trình bày ở trên, và thêm mảng doanh thu vào công thức SUMPRODUCT:
Ví dụ 8: Hàm SUMPRODUCT Tính Tổng Theo Điều Kiện Có Toán Tử OR (Hoặc)
Các ví dụ đã nêu về hàm SUMPRODUCT ở trên chủ yếu là điều kiện AND (kết hợp thỏa mãn nhiều điều kiện).
Hàm SUMPRODUCT cũng có thể kết hợp hàm OR như các hàm tính tổng có điều kiện khác:
Diễn giải công thức
=SUMPRODUCT((OR(A4:A9=”táo”,A4:A9=”ổi”)*(B4:B9=”B”)*C4:C9))
-
(OR(A4:A9="táo",A4:A9="ổi") kiểm tra Sản phẩm có phải là Táo hoặc Ổi không, trả về {TRUE, TRUE, TRUE, TRUE, FALSE, TRUE}
-
(B4:B9="B") trả về {FALSE, TRUE, TRUE, FALSE, FALSE, TRUE}
-
C4:C9 là các phần tử cột Doanh thu
- Phép nhân giữa 3 mảng trên trở thành:
2.3, Hàm SUMPRODUCT Kết Hợp Hàm Excel Tạo Công Thức Mảng
Một số hàm khi trong công thức có đối số là một dải ô sẽ trả về kết quả không giống yêu cầu của người viết do chúng không được xây dựng để làm việc với các mảng.
Ví dụ sử dụng hàm LEN để đếm số ký tự trong dải ô A3:A5, sử dụng Ctrl + Shift + Enter để chạy công thức mảng, kết quả mảng bằng 6 không phải kết quả cần tìm:
Vì hàm SUMPRODUCT được xây dựng để làm việc với các mảng nên nó có thể thực hiện các phép tính trực tiếp trên các mảng giúp tiết kiệm dung lượng trong trang tính Excel bằng cách loại bỏ cột phụ.
Ví Dụ 9: Kết Hợp Hàm SUMPRODUCT Với Hàm Len
Tiếp tục ví dụ công thức mảng ở trên, để đếm tổng số ký tự trong một dải ô thì bạn chỉ cần sử dụng kết hợp cùng hàm SUMPRODUCT để thực hiện phép tính (hàm LEN) trực kết lên mảng như sau:
Ví dụ 10: Đếm Loại Bỏ Các Giá Trị Giống Nhau Bằng Cách Kết Hợp Hàm COUNTIF và SUMPRODUCT
Như đã nêu trong bài viết Hàm COUNTIF Nâng Cao, hoctin.vn sử dụng công thức kết hợp hàm đếm có điều kiện cho cả trường hợp đếm ô trống là một giá trị riêng biệt và không đếm ô trống:
Trên đây là các ví dụ và ứng dụng hay của hàm SUMPRODUCT từ cơ bản đến nâng cao trong Excel. Hi vọng bài viết của hoctin.vn đã mang lại cho các bạn các thông tin hữu ích.
Ngoài ra, để ứng dụng hiệu quả Excel, bạn cần phải sử dụng tốt các hàm, các công cụ khác của Excel, hãy cùng đón đọc series bài viết ứng dụng các hàm Excel:
6 Cách Tính Tổng Trong Excel Và Ví Dụ Cụ Thể