[Hàm Excel] SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện

Cấu trúc SUMPRODUCT(array1,array2,array3, ...)

Array - Mảng dữ liệu là một tập hợp dãy giá trị liên tiếp trong một khảng nào đó. VD A1:C1 hoặ A1:A10,...

Phép tính này cho phép chúng ta tính tổng của tích array1*array2*array3* ...array30.

VD: A: Số lượng; B: Đơn giá

A1 =2 B1=20 C1="Cam" D1="Giống lai"

A2 =3 B2=10 C2="Bưởi" D2="Không"

A3 =4 B3=25 C3="Cam" D3="Không"

Bây giờ cần tính doanh thu của các loại hoa quả

array1=A1:A3

array2=B1:B3

Công thức =SumProduct(A1:A3, B1:B3) = 170

Bản chất công thức làm việc như thế này =A1*B1+A2*B2+A3*B3 kết quả là 170

Nhắc lại về phép tính logic:

Giá trị kiểu logic chỉ cho ra 1 trong 2 giá trị là TRUE/1, FALSE/0

Phép toán logic:<, >, <>, =, >=, <=, Not()

VD:

2>3=False

3>1=True

4>3=True

*) Logic và - AND

=(2>3)*(3>1)*(4>3)=False*True*True=0*1*1=False/0 tương đương với hàm AND(2>3,3>1,4>3). Ít nhất một logic=False thì kết quả sẽ là False hay 0.

* Logic hoặc - OR

=(2>3)+(3>1)+(4>3)=False+True+True=0+1=True/1 tương đương với hàm OR(2>3,3>1,4>3). Ít nhất một logic=True thì kết quả sẽ là True hay 1. 

Lưu ý tổng của các giá trị là True=True=1).

*) Tính tổng có nhiều điều kiện:

Cách 1: dùng SUMPRODUCT

Tính tổng doanh thu của loại là "Cam"

=SUMPRODUCT(A1:A3,B1:B3*(C1:C3="Cam")) hoặc =SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) = 140

Công thức tính như sau: 

=A1*B1*(C1="Cam")+A2*B2*(C2="Cam")+A3*B3*(C3="Cam") 

=2*20*True+3*10*False+4*25*True

=2*20*1+3*10*0+4*25*1= 140

Cách 2: dung Công thức mảng - "Formula Array"

=Sum(IF(C1:C3="Cam",A1:A3*B1:B3,0))

Kết thức nhẫn tổ hợp CTRL+SHIFT+ENTER. Với cách làm này EXCEL sẽ phân tích như sau:

Xét trên từng dòng trong mảng (array)

dòng1: (c1="Cam")=true nên lấy A1*B1=2*20

dòng2: (c2="Cam")=false nên lấy 0 (theo cách của lấy của hàm IF)

dòng3: (c3="Cam")=true nên lấy A3*B3=4*25

Sau khi chạy hết các dòng, EXCEL sẽ dùng hàm SUM để tính tổng kết quả tính được ở từng dòng=2*20+0+2*25=140. Nếu trong công thức là hàm khác hàm SUM thì cách tính sẽ theo hàm đó.

=SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) và

=Sum(IF(C1:C3="Cam",A1:A3*B1:B3,0))

Các bạn nhớ lại cấu trúc của SUM là

SUM(number1,number2, ...)

Còn SUMPRODUCT là

SUMPRODUCT(array1,array2,array3, ...)

number <> array

Nếu nhấn tổ hợp CTRL+SHIFT+ENTER. Với cách làm này EXCEL sẽ phân tích như sau:

Xét trên từng dòng trong mảng (array)

dòng1: A1*B1*(c1="Cam")=2*20*True=2*20*1

dòng2: A2*B2*(c2="Cam")=3*10*False=3*10*0

dòng3: A3*B3*(c3="Cam")=2*25*True=4*25*1

Sau khi chạy hết các dòng, EXCEL sẽ dùng hàm SUM để tính tổng kết quả tính được ở từng dòng=2*20*1+3*10*0

+4*25*1=140.

Như vậy đến đây chúng ta có có 3 cách tính:

Chúng có thể kết hợp rất nhiều điều kiện vào trong hàm thông qua phép toán logic nhân-và- And, cộng - hoặc - Or. 

*) Dùng hàm SUMPRODUCT hay dùng SUM kết hợp CTRL+SHIFT+ENTER đều cho ra được kết quả như nhau chính là do phép toán logic của bạn.

*) Hàm SUMPRODUCT chỉ có thể tính tổng theo nhiều điều kiện

*) Công thức mảng - Formula Array ngoài việc tính tổng có nhiều điều kiện còn làm rất nhiều phép tính khác do cách sử dụng hàm mà thôi.