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

[ptm0412]

=SUMPRODUCT (((DK1)+(DK2))*(VungTong))
thì sẽ có một số hàng bị tính 2 lần do thoả DK1 VÀ DK2
có phương án nào để loại trừ 2(*) ko nhi? có nghĩa là ép 2(*) thành boolean

Bạn thay cái tô đỏ thành ((DK1)+(DK2)>0)
trở thành:

=SUMPRODUCT (((DK1)+(DK2)>0)*(VungTong))

 

[luongtranap82]

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

Đã có bài viết trên GPE về "khả năng chiếm dụng bộ nhớ " của công thức dạng mảng.Việc phải bỏ ra quá nhiều bộ nhớ để lưu trữ công thức mảng "cồng kềnh" sẽ làm giảm khả năng xử lý của PC,đấy chính là nguyên nhân làm EX sẽ "bò" thay vì đi như bình thường khi Cơ sở dữ liệu phình to lên.Bạn nào xử lý dữ liệu ví dụ như quản lý nhân sự của Xn cỡ vài trăm người sẽ là người hiểu rõ nhất.Chính vì điểm này nên khuyến cáo hạn chế việc dùng công thức dạng mảng.EX các phiên bản cao hơn : 2007,2010 dường như đã giúp chúng ta điều này.Thay vi một loạt các mảng Sum(IF...) cồng kềnh giờ ta đã có : SumIF(),SumIFs(),IFERROR()...Rất tiện lợi và gọn gàng.Vì thế,nên theo tôi "úp" các bản EX cao dùng là ok

 

[thanhnam0119]

Trợ giúp về phép tính mảng cho Bệnh viện

Mình làm phép tính tổng thủ công bằng hàm Sum như thế này thì mất công quá, có cách lập hàm nào tự động theo điều kiện hãy giúp mình với các bạn ơi

 

[dat_butmuc]

Mình làm phép tính tổng thủ công bằng hàm Sum như thế này thì mất công quá, có cách lập hàm nào tự động theo điều kiện hãy giúp mình với các bạn ơi

Dùng công thức này tại C2 rồi fill xuống:
=IF(A2<>A1,SUM(OFFSET($B2,,,COUNTIF($A$2:$A$26,A2))),"")

 

[Ba Tê]

Mình làm phép tính tổng thủ công bằng hàm Sum như thế này thì mất công quá, có cách lập hàm nào tự động theo điều kiện hãy giúp mình với các bạn ơi

Thêm vài công thức nữa tham khảo cho vui:

C2=IF(A2=A1;"";SUMIF(A2:$A$26;A2;B2:$B$26))
C2=IF((A2=A1)+(A2="");"";SUMIF($A$2:$A$26;A2;$B$2:$B$26))

 

[excelorwife]

Mời các bạn tham khảo cho vui: SUMIF(A2:$A$26;IF(A2=A1;"",A2);B2:$B$26)

 

[excelorwife]

Gửi bạn tham khảo nhé! View attachment 46193

 

[vuthanhbinh]

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!

Nghe bạn nói hay quá.Thế bạn làm sao tạo được một mảng, để sau này còn để áp dụng với những hàm trên, và các hàm khác đòi hỏi cấu trúc của nó là có một đối số là Array

 

[Nguyễn Duy Tuân]

Nghe bạn nói hay quá.Thế bạn làm sao tạo được một mảng, để sau này còn để áp dụng với những hàm trên, và các hàm khác đòi hỏi cấu trúc của nó là có một đối số là Array

Bạn có thể nói rõ ý hơn được không?

 

[quydangktk]

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 đó.

Em đã làm đúng như hướng dẫn mà sao ko ra kết quả 140 mà excel nó trả về giá trị bằng ko thử tất cả các cánh tính mà ko đc. Mọi người có thể giải thích cho mình đc ko. Mình đang cần tính tổng có nhiều điều kiện mà dùng hàm sumproduct toàn bị trả về giá trị 0.

 

[Nguyễn Duy Tuân]

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 đó.

Em đã làm đúng như hướng dẫn mà sao ko ra kết quả 140 mà excel nó trả về giá trị bằng ko thử tất cả các cánh tính mà ko đc. Mọi người có thể giải thích cho mình đc ko. Mình đang cần tính tổng có nhiều điều kiện mà dùng hàm sumproduct toàn bị trả về giá trị 0.

Bạn kiểm tra dữ liệu nhập có thực sự là con số không?

 

[tungngoctn]

Cho hỏi thêm vể SumProduct

Các bro xem giúp bt dưới đây.Rồi chỉ giúp 2 bảng thống kê làm sao nhé.(SUMPRODUCT)

 

[khoavu87]

Các bro xem giúp bt dưới đây.Rồi chỉ giúp 2 bảng thống kê làm sao nhé.(SUMPRODUCT)

tại H23 bạn đặt công thức này :

=SUMPRODUCT((LEFT($B$4:$B$12;1)=H$22)*(MONTH($D$4:$D$12)=$G23)*$H$4:$H$12)

Sau đó bạn fill sang ngang xuống dưới các ô tiếp theo

 

[dat_butmuc]

Các bro xem giúp bt dưới đây.Rồi chỉ giúp 2 bảng thống kê làm sao nhé.(SUMPRODUCT)

Chỉ dùng một công thức tại H18:

=SUMPRODUCT((NOT(ISERR(FIND(H$17,$B$4:$B$12))=TRUE)*(MONTH($D$4:$D$12)=$G18)*$H$4:$H$12))

 

[duchm]

công thức sumproduct chạy chậm quá,các bác giúp em với.

công thức sumproduct trong sheet KHOANG 1A của em chạy chậm quá,các cao thủ chỉ giùm em cho nó chạy nhanh hơn được không?khi dòng tới 10000 thì chạy chậm lắm.

 

[dat_butmuc]

công thức sumproduct trong sheet KHOANG 1A của em chạy chậm quá,các cao thủ chỉ giùm em cho nó chạy nhanh hơn được không?khi dòng tới 10000 thì chạy chậm lắm.

Cái này thì điều hiển nhiên thôi!
Bạn thử gửi file lên xem có giải pháp gì hay hơn không?

 

[TongDe]

Đặt tên động cho các dãy cân thống kê
Xem bằng control+f3
Dùng Sumif thay cho sumproduct

 

[duchm]

Đặt tên động cho các dãy cân thống kê
Xem bằng control+f3
Dùng Sumif thay cho sumproduct

nhưng nếu dùng sumif thay cho sumproduct thì chỉ tính sum 1 điều kiền trong khi yêu cầu là sum 2 điều kiện.

 

[TongDe]

Trong 2007 có sumifs

 

[thewarvn]

Mình có một vấn đề thế này mong các bác giải giúp nhé! cám ơn trước! mình gà exec lắm đừng cười nha!