Bài viết: SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện

[Nguyễn Duy Tuân]

Hàm SUMPRODUCT()

Cấu trúc hàm 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ặc 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).

*) Áp dụng - 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: dùng 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òng 1: (C1="Cam")=True nên lấy A1*B1=2*20
dòng 2: (C2="Cam")=False nên lấy 0 (theo cách của lấy của hàm IF)
dòng 3: (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 đó.

Như vậy có 2 cách tính:

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

*) Vậy tại sao không dùng là =SUM(A1:A3*B1:B3*(C1:C3="Cam"))
mà phải dùng hàm =SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) ?

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 SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) rồi ENTER là đúng vì đối số của nó phải là mảng - Array.

Nếu công thức =SUM(A1:A3*B1:B3*(C1:C3="Cam")) rồi ENTER kết quả là #VALUE! - lỗi vì A1:A3 là một array chứ không phải là một number.

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òng 1: A1*B1*(c1="Cam")=2*20*True=2*20*1
Dòng 2: A2*B2*(c2="Cam")=3*10*False=3*10*0
Dòng 3: 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.

Vậy vẫn dùng được =SUM(A1:A3*B1:B3*(C1:C3="Cam")) với điều kiện nhấn tổ hợp phím CTRL+SHIFT+ENTER


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

=SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) nhấn phím ENTER
=SUM(IF(C1:C3="Cam",A1:A3*B1:B3,0)) nhấn phím CTRL+SHIFT+ENTER
=SUM(A1:A3*B1:B3*(C1:C3="Cam")) nhấn phím CTRL+SHIFT+ENTER

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.

Thân chào!

Một số bài viết có liên quan:
1/ 7 hàm hữu ích bị lãng quên trong Excel
2/ Sử dụng Excel để ra quyết định hằng ngày
3/ Excel nâng cao: Sử dụng sự lặp lại và các tham chiếu tuần hoàn
4/ 10 lý do để học công thức Excel
5/ Làm việc với công thức mảng trong Excel
6/ VLOOKUP với Cột động
7/ 23 điều bổ ích về VLOOKUP có thể bạn muốn biết (phần 2)
8/ 23 điều bổ ích về VLOOKUP có thể bạn muốn biết (phần 1)
9/ Sử dụng hàm Subtotal
10/ Cách viết hàm hiệu quả

 

[HeSanbi]

Bạn có thể xem toàn văn bài viết tại: http://www.giaiphapexcel.com/dienda...à-Công-thức-mảng-Phép-tính-có-nhiều-điều-kiện

Anh Tuân ơi ! trong bài ..."ModFastArray"... của anh năm 2013 , trong File khi chạy For Next / Memory thì thời gian được ghi lại cho 10 lần lặp, xin hỏi anh đoạn code để có thể ghi lại thời gian chạy mỗi macro bình thường như thế nào vậy?

 

[Nguyễn Duy Tuân]

Anh Tuân ơi ! trong bài ..."ModFastArray"... của anh năm 2013 , trong File khi chạy For Next / Memory thì thời gian được ghi lại cho 10 lần lặp, xin hỏi anh đoạn code để có thể ghi lại thời gian chạy mỗi macro bình thường như thế nào vậy?

Em xem code ví dụ anh làm trong MacroTest rồi làm theo nhé. Nguyên tắc là:
1. Lưu thời gian hiện tại
2. Chạy một hoặc nhiều macro
3. Lưu thời gian kết thúc và tính khoảng thời gian từ 1-3.