Công thức Excel: SUMPRODUCT với IF
Công thức Excel: SUMPRODUCT với IF

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)

nơi sau phạm vi được đặt tên được định nghia:

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 được nhập dưới dạng tài liệu tham khảo 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, phần đầu tiên mảng 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 ép buộc thành các giá trị đơn và không với Âm kép (-) hoạt động. 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 số cho biết một hàng có màu “đỏ” và một số không cho biết 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. Vì chúng tôi đang làm việc với ba mảng, chúng tôi có thể hình dung hoạt động như được hiển thị trong bảng bên dưới, trong đó cột kết quả là kết quả của phép nhân array1 * array2 * mảng 3:

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

Để ý 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ư thế này:

=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.

https://exceljet.net/formula/sumproduct-with-if