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

Liên hệ QC

Nguyễn Duy Tuân

Nghị Hách
Thành viên danh dự
Tham gia
13/6/06
Bài viết
4,649
Được thích
10,138
Giới tính
Nam
Nghề nghiệp
Giáo viên, CEO tại Bluesofts
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"

224365

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!
 
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é
 
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
 
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
 
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
 
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

  • test.xls
    20.5 KB · Đọc: 2,989
Lần chỉnh sửa cuối:
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

  • test.xls
    21 KB · Đọc: 1,605
Lần chỉnh sửa cuối:
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
 
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

  • TH tham nien.xls
    55 KB · Đọc: 694
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

  • TH tham nien.zip
    22.8 KB · Đọc: 1,502
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:
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
 
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........
 
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
 
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é.
Thanks!!

Thân!
 
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

  • Or - Sumproduct.7z
    2.1 KB · Đọc: 1,296
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ỹ...
 
Web KT
Back
Top Bottom