Công thức chung
=SUMPRODUCT(expression,range)
Tóm lược
Để lọc kết quả của hàm SUMPRODUCT với các tiêu chí cụ thể, bạn có thể áp dụng trực tiếp các biểu thức logic đơn giản cho các mảng trong hàm, thay vì sử dụng hàm IF. Trong ví dụ được hiển thị, các công thức trong H5: H7 là:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
trong đó các phạm vi được đặt tên sau được xác định:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Nếu bạn muốn tránh các phạm vi được đặt tên, hãy sử dụng các phạm vi ở trên đã nhập làm tham chiếu tuyệt đối. Các biểu thức logic trong H6 và H7 có thể được kết hợp, như được giải thích bên dưới.
Giải trình
Ví dụ này minh họa một trong những điểm mạnh chính của hàm SUMPRODUCT - khả năng lọc dữ liệu bằng các biểu thức logic cơ bản thay vì hàm IF. Bên trong SUMPRODUCT, mảng đầu tiên là một biểu thức logic để lọc trên màu "đỏ":
--(color="red")
Điều này dẫn đến một mảng hoặc các giá trị TRUE FALSE, các giá trị này bị ép buộc thành các giá trị đơn và không bằng phép toán kép âm (-). Kết quả là mảng này:
(1;0;1;0;0;0;1;0;0;0)
Lưu ý rằng mảng chứa 10 giá trị, một giá trị cho mỗi hàng. Một biểu thị một hàng có màu "đỏ" và một số 0 biểu thị một hàng có bất kỳ màu nào khác.
Tiếp theo, chúng ta có hai mảng nữa: một mảng cho số lượng và một mảng cho giá cả. Cùng với kết quả này từ mảng đầu tiên, chúng ta có:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Mở rộng các mảng, chúng ta có:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),(10;6;14;9;11;10;8;9;11;10),(15;18;15;16;18;18;15;16;18;16))
Hành vi cốt lõi của SUMPRODUCT là nhân, sau đó tính tổng các mảng. Kể từ khi chúng tôi đang làm việc với ba mảng, chúng ta có thể hình dung hoạt động như thể hiện trong bảng dưới đây, nơi cột kết quả là kết quả của nhân array1 * array2 * array3 :
| array1 | array2 | mảng 3 | kết quả |
|---|---|---|---|
| 1 | 10 | 15 | 150 |
| 0 | 6 | 18 | 0 |
| 1 | 14 | 15 | 210 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 18 | 0 |
| 1 | số 8 | 15 | 120 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 16 | 0 |
Chú ý array1 hoạt động như một bộ lọc - các giá trị không ở đây là các giá trị "không ra" trong các hàng có màu không phải là "đỏ". Đưa kết quả trở lại SUMPRODUCT, chúng tôi có:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Trả về kết quả cuối cùng là 480.
Thêm tiêu chí bổ sung
Bạn có thể mở rộng tiêu chí bằng cách thêm một biểu thức logic khác. Ví dụ: để tìm tổng doanh số bán hàng trong đó màu là "Đỏ" và trạng thái là "TX", H6 chứa:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Lưu ý: SUMPRODUCT không phân biệt chữ hoa chữ thường.
Đơn giản hóa với một mảng duy nhất
Các chuyên gia Excel thường sẽ đơn giản hóa cú pháp bên trong SUMPRODUCT một chút bằng cách nhân các mảng trực tiếp bên trong array1 như sau:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Điều này hoạt động vì phép toán (phép nhân) tự động ép các giá trị TRUE và FALSE từ hai biểu thức đầu tiên thành đơn vị và số không.
