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

Nguyễn Duy Tuân

Nghị Hách
Thành viên danh dự
Tham gia ngày
13 Tháng sáu 2006
Bài viết
4,080
Được thích
9,532
Điểm
860
Nơi ở
Hà Nội
Mình xin phân tích cách dùng SumProduct và Công thức mảng.

Hàm SumProduct:
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 đó.

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

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!
 
Chỉnh sửa lần cuối bởi điều hành viên:

khuongan

Thành viên mới
Tham gia ngày
13 Tháng sáu 2006
Bài viết
1
Được thích
20
Điểm
0
Thế còn hàm DOTPRODUCT và QUADPRODUCT cũng là một hàm tương tự như SUMPRODUCT nhưng có đối số linh hoạt hơn, hình như cũng là một Function nằm trong Add-in Solver, nhưng mình không hiểu rõ lắm hoạt động của hàm này, TuanVNUI và các bạn khác, ai biết về 2 hàm này thì Share cho mọi người hiểu thêm nhé
 

hanhdhyd

Thành viên mới
Tham gia ngày
11 Tháng sáu 2006
Bài viết
7
Được thích
29
Điểm
0
Hàm Sumproduct cho phép tính toán trên các mảng cùng chiều, cùng kích thước với nhau, còn hàm Dotproduct và Quadproduct cho phép tính toán trên nhiều vùng lựa chọn khác hướng nhau, khác kích thước, tuy vậy đây là hàm trong Add-in Solver Preminium, nên không phải MS-Excel nào cũng có sẵn, nên cũng không thông dụng, và không có điều kiện để tìm hiểu kỹ. Bạn nào biết thêm về các hàm này cùng trao đổi thêm.
Tôi cũng muốn biết thêm về ma trận và các hàm ma trận trong Excel, nhờ các bạn hướng dẫn giùm
 

Hatmit

Thành viên mới
Tham gia ngày
9 Tháng ba 2007
Bài viết
2
Được thích
10
Điểm
0
Mình dùng hàm này nhưng không thể kết hợp được 2 điều kiện được. Bạn nào giúp mình với nhe
- Tôi muốn có một công thức thế này: nếu A1:A10 = "X" va B1:b10="Y" thì kq sẽ là : tổng từ c1:C10 thoả mãn 2 đk trên
 

yeudoi

Thành viên gắn bó
Thành viên BQT
Moderator
Tham gia ngày
12 Tháng sáu 2006
Bài viết
2,822
Được thích
6,990
Điểm
910
Nơi ở
TP Hội An
Bạn hãy xem lại hướng dẫn ở trên rất kỹ, nếu vẫn chưa hiểu bạn hãy download file 173 công thức trong thư viện của diễn đàn.Bài toán của bạn đưa ra rất dễ (dùng sumproduct), chúc bạn thành công
 

hoaloaken

Thành viên chính thức
Tham gia ngày
5 Tháng tư 2007
Bài viết
76
Được thích
24
Điểm
0
giúp lập công thức

giúp mình lập công thức thống kê tổng số người theo từng loại thâm niên công tác,theo từng bộ phận nữa. Bảng dữ liệu ở sheet1, bảng tổng hợp ở sheet 2 . mình đã ghi chú trong file đính kèm
hepl meeeeeeeeeeeeeee
Xin cảm ơn-=09= +-+-+-+ -0-/.
 

File đính kèm

Lần chỉnh sửa cuối:

ThuNghi

Hãy cho rồi sẽ nhận!
Thành viên danh dự
Tham gia ngày
16 Tháng tám 2006
Bài viết
3,814
Được thích
4,451
Điểm
860
Bạn đặt name như sau:
seni: =Sheet2!$A$3:$A$7
tại O4 sheet1
=INDEX(Seni,MATCH(DAYS360($N4,$M$2)/30/12,{0;1;3;5;7},1),0)
Copy xuống.
 

hoaloaken

Thành viên chính thức
Tham gia ngày
5 Tháng tư 2007
Bài viết
76
Được thích
24
Điểm
0
Cám ơn bạn ThuNghi đã trả lời. Nhưng ý mình không phải thế
Có lẽ do mình viết câu hỏi không được rõ ràng cho lắm.
ý mình là lấy cột thâm niên mình đã làm ở sheet 1, để tổng hợp sang sheet 2.
Mình đã ghi chú lại trong file đính kèm.
ThuNghi giúp mình làm lại nhé
Cảm ơn bạn nhiều
 

File đính kèm

Lần chỉnh sửa cuối:

kietbui

Thành viên mới
Tham gia ngày
23 Tháng năm 2007
Bài viết
43
Được thích
53
Điểm
675
Tuổi
53
Trong các ứng dụng về công thức mãng mình được biết thì dạng công thức mãng dạng sum nhiều điều kiện là mình tâm đắc nhất vì cú pháp đơn giản, khi kết hợp với những hàm khác thì nó rất linh hoạt , giải quyết rất nhiều tình huống trước đây mình xử lý bằng hàm thông thường rất vất vã, hình như nó tính hơi lâu nhưng kết quả rất ưng ý
Ví dụ
1/ sum nhiều if - thay thế một macro rút trích rồi tính tổng
vd:tính tiền thu được do bán mặt hàng là "sơn" thời gian từ 01/06/07 ( tungay) đến 30/06/07( denngay)
{sum(if(ngay=>tungay,1,0)*if(ngay<=denngay,1,0)*if(mat_hang="son",1,0)*tien)}
2/ Sumproduct nhiều if
vd:tính tổng diện tích các thửa ruộng ấp A, xã B, Huyện C
{sum(if(ap="a",1,0)*if(xa="b",1,0)*if(huyen="c",1,0)*dai*rong)}
3/ count nhiều if
vd: đếm số sv điểm trên trung bình là nam,lớp B, khoa C, Trường D
{sum(if(diem>=5,1,0)*if(gioitinh="nam",1,0)*if(lop="b",1,0)*if(khoa="c",1,0)*if(truong="d",1,0)}
4/max nhiều if
vd:tự động nhảy số hóa đơn thứ mấy xuất cho đơn vị A, chứ không phải là STT hóa đơn, stt hóa đơn =max(stt_hd)
{max(if(dv="a",1,0)*shd_dv)}
5/ Vlookup_if
vd: tìm trong vùng dữ liệu cột 1 mã máy bằng AAA, cột ngày di chuyển=gần nhất, xem nơi đến là nơi nào ( tìm giá trị cột noi_den )
- kết hợp hàm findtwoconditions trên giaiphapexcel và hàm max_if trên
 

hoaloaken

Thành viên chính thức
Tham gia ngày
5 Tháng tư 2007
Bài viết
76
Được thích
24
Điểm
0
hỏi về công thức mảng

Chào bạn ThuNghinh
Bài trước bạn đã giúp mình lọc theo thâm niên công tác bằng công thức mảng. Cột thâm niên được tính theo công thức DAYS360. Mình đã lập được công thức rồi. Nhưng theo sự gợi ý của bạn anhtuan1606 công thức này là không chính xác, mình đã sửa lại cột năm riêng và cột tháng riêng.
Khi áp dụng công thức mảng của bạn của lọc thâm niên (lấy tiêu chí cột năm) thì không hiểu sao công thức của mình lại thành sai. Bạn thử check lại hộ mình nhé. Mặt khác trong quá trình làm việc khi xoá dòng hoặc insert một số dòng thêm vào danh sách thì bảng tổng hợp thường bị lỗi, làm mình lại phải sửa lại define name. Vậy có cách nào khắc phục được không bạn???
Cảm ơn bạn nhiều.

(Ah, bạn anhtuan cũng gợi ý mình dùng dùng VLOOKUP để lọc nhưng lại phải sắp sếp cột thâm niên theo thứ tự tăng dần hoặc giảm dần, như vậy mình thấy không tiện lắm. vì chủ yếu mình để danh sách theo tầng hoặc theo tổ. )
 

File đính kèm

anhtuan1066

Thành viên gạo cội
Tham gia ngày
10 Tháng ba 2007
Bài viết
5,814
Được thích
6,819
Điểm
860
Tuổi
52
Nơi ở
Biên Hòa, Đồng Nai
Công thức bạn làm có sử dụng hàm ROW theo tôi cũng sáng tạo đấy! Tuy nhiên tôi nghĩ ko nên sử dụng vì nó khá nguy hiểm nếu như 1 ngày nào đó bạn Insert row!
Bạn xem bài giải của tôi có đúng ý ko nha! Trong này chỉ sử dụng 2 Name và 1 hàm SUMPRODUCT đơn giản là dc mà
ANH TUẤN
 

File đính kèm

hoaloaken

Thành viên chính thức
Tham gia ngày
5 Tháng tư 2007
Bài viết
76
Được thích
24
Điểm
0
bạn làm vào file mình thì rất đúng, không hiểu sao khi mình mở file của mình ra làm thì nó lại hiện ở dòng B3 danh sách của cả tổ luôn. Chứ nó không phân loại ra cho mình. Mình đặt đi đặt lại name mà vẫn không được.
Đến lúc copy công thức từ file bạn làm ra, và sửa lại vùng của name thì lại được. Nhân tiện cho mình hỏi luôn: dùng công thức nào mình có thể lọc được những người sinh nhật trong tháng 12 nhỉ? Mình toàn phải insert thêm 1 cột phụ tháng sinh, sau đó mới lọc được.
CẢM ƠN BAN NHIEU
 
Lần chỉnh sửa cuối:

anhtuan1066

Thành viên gạo cội
Tham gia ngày
10 Tháng ba 2007
Bài viết
5,814
Được thích
6,819
Điểm
860
Tuổi
52
Nơi ở
Biên Hòa, Đồng Nai
Khi bạn làm trên file tôi thì đúng, copy công thức sang file bạn lại sai... hi... hi... Cốt lổi vấn đề là ở chổ Format Custom trong các cell A3 đến A7... bạn nhìn thấy nó là text nhưng thực chất nó là number (chọn vào 1 trong các cell A3 đến A7 rồi nhìn lên thanh Formula sẽ rõ)
Tóm lại tình trạng hiện giờ trong sheet TH Tham nien là:
A2 = 0
A3 = 1
A4 = 3
A5 = 5
A6 = 7
A7 = 1000
.....
Còn vấn đề lọc ra người nào sinh trong tháng 12 tôi nghĩ bạn dùng Conditonal Formating là tiện nhất... Quét chọn vùng từ L5 đến cuối... vào conditional formating, Conditon1 chọn Formula Is và gỏ vào công thức =MONTH($L5)=12 rồi vào Format tô màu gì tùy bạn...
Mến
ANH TUẤN
 

hoaloaken

Thành viên chính thức
Tham gia ngày
5 Tháng tư 2007
Bài viết
76
Được thích
24
Điểm
0
chào bạn anh tuấn.
Thì ra công thức là như vậy. Mình thì không có được hiểu biết cơ bản, bản chất của công thức nên chỉ học lỏm, học mót của mọi người thôi....... hiiiiiii nên nhiều lúc cũng thấy mình ngô ngố .......hiiiiiiiiiiiiiiiiii..
Nếu sử dụng conditional formating thì chỉ để nhìn phân biệt với các tháng khác. Nhưng mục đích của mình là để lọc ra được 1 list những người đó. Vì hàng tháng mình phải lập 1 list công nhân sinh nhật trong tháng để làm phiếu tặng quà mà. Bạn có cách nào khác không.
Thân........
 

Mr Okebab

Ngon Ngất Ngây
Thành viên đã mất
Tham gia ngày
6 Tháng tám 2006
Bài viết
3,262
Được thích
3,762
Điểm
0
Tuổi
42
Các bạn thắc mắc rằng dùng Sumproduct kết hợp với OR sẽ như thế nào nhỉ ?? Vì kết hợp với AND thì dễ rồi, còn với OR ??

Các bạn xem File VD nhé :

Thân!
 

anhtuan1066

Thành viên gạo cội
Tham gia ngày
10 Tháng ba 2007
Bài viết
5,814
Được thích
6,819
Điểm
860
Tuổi
52
Nơi ở
Biên Hòa, Đồng Nai
Hình như chưa đúng thì phải... Sửa cell D8 thành 311 lý ra nó phải cho kết quả khác, thế mà nó vẫn y nguyên!
Hay ý Bắp muốn nói rằng chỉ cần trong cột có sự hiện diện của 111 hoặc 411 thì lấy hết cả cột? Vì tôi thay hết tất cả các cell thành 311 chỉ chừa C2 = 111 và D2 = 411 mà kết quả vẫn = 850,000,000 ???
Tôi lại đang nghĩ ý bạn phải giống như kết quả của tôi chứ:
=SUMPRODUCT((C2:C18<>311)*(D2:D18<>311)*(E2:E18))
Xin cho biết ý tưởng!
ANH TUẤN
 

Mr Okebab

Ngon Ngất Ngây
Thành viên đã mất
Tham gia ngày
6 Tháng tám 2006
Bài viết
3,262
Được thích
3,762
Điểm
0
Tuổi
42
anhtuan1066 đã viết:
Hình như chưa đúng thì phải... Sửa cell D8 thành 311 lý ra nó phải cho kết quả khác, thế mà nó vẫn y nguyên!
Hay ý Bắp muốn nói rằng chỉ cần trong cột có sự hiện diện của 111 hoặc 411 thì lấy hết cả cột? Vì tôi thay hết tất cả các cell thành 311 chỉ chừa C2 = 111 và D2 = 411 mà kết quả vẫn = 850,000,000 ???
Tôi lại đang nghĩ ý bạn phải giống như kết quả của tôi chứ:
=SUMPRODUCT((C2:C18<>311)*(D2:D18<>311)*(E2:E18))
Xin cho biết ý tưởng!
ANH TUẤN
Xin lỗi bác vì vội nên em tải nhầm File. Em đã sửa lại, bác xem lại nhé.
Cảm ơn!!

Thân!
 

Mr Okebab

Ngon Ngất Ngây
Thành viên đã mất
Tham gia ngày
6 Tháng tám 2006
Bài viết
3,262
Được thích
3,762
Điểm
0
Tuổi
42
anhtuan1066 đã viết:
Sửa ở chổ nào đâu? Sao tôi down về thấy vẩn vậy mà... tức là khi tôi sửa hết các cell thành 311 chỉ chừa 2 cell trên cùng thì kết quả vẩn là 850,000,000 ???
ANH TUẤN
Oái, em đã cho file đúng rồi, sao vẫn là File cũ.
Tức thật.
Bác xem nhé :

Thân!
 

File đính kèm

anhtuan1066

Thành viên gạo cội
Tham gia ngày
10 Tháng ba 2007
Bài viết
5,814
Được thích
6,819
Điểm
860
Tuổi
52
Nơi ở
Biên Hòa, Đồng Nai
Vẫn sai! Theo như tôi hiểu thì ý bạn là: Nếu trong cột C hoặc D có giá trị 111 hoặc 411 thì lấy, chỉ khi nào cả 2 cột cùng khác cả 2 giá trị này mới bỏ qua...
Bạn thử thay D8=311 xem, đúng ra kết quả phải thay đổi, nhưng thật tế là u như kỹ...
 
Top