giúp em công thức đếm với (1 người xem)

[tuananhya2]

em muốn đếm xem ông nguyễn văn A trong bảng có bao nhiêu buổi là công tác và bao nhiêu buổi đi học, đi làm. Kiến thức ex e còn hạn chế mong các bác giúp đỡ
Em đặt công thức báo lỗi hoài (

 

[bebo021999]

Thử một trong hai cách:
=SUMPRODUCT(($B$5:$I$11=$D$16)*($B$6:$I$12=$D$17))
=COUNTIFS($B$5:$I$11,D16,$B$6:$I$12,D17)

 

[tuananhya2]

cám ơn bác e thử được cả 2 công thức rồi

 

[Đỗ Tú Anh]

=SUMPRODUCT(($B$5:$I$11=$D$16)*($B$6:$I$12=$D$17))=3

Anh bebo021999 ơi,
Cho em hỏi ngu anh nghen. Vì hàm Sumproduct() em không rành lắm nên câu hỏi quá ngu thì xin đừng phiền lòng.
Em đang học hàm Sumproduct() như sau= (Array 1, Array 2,...., Array n). Sao trong công thức Sumproduct() của anh lại có toán tử * thế, thay vì phải là ","
Thêm nữa, khi em thay đổi vùng dữ liệu như sau=SUMPRODUCT(($B$5:$I$12=$D$16)*($B$5:$I$12=$D$17))=0 (≠3) → Sao công thức lại ra trớt quớt vậy anh??? Em chỉ thêm vùng dữ liệu thôi mà, đâu có bớt đâu, dư chứ đâu có thiếu đâu mà sao Excel lại không có work??? Em sai chỗ nào thế anh, phiền anh giải thích rồi. Chân thành cảm ơn anh.

=COUNTIFS($B$5:$I$11,D16,$B$6:$I$12,D17)

Công thức Countifs() này là công thức mảng phải không anh, nếu vậy thì thôi em chưa học. Em chỉ mới học Countif() thôi chứ chưa học Countifs().
Không phải là em lười không muốn học công thức mảng nhưng mà em tự thấy Excel của em còn rất hạn chế nên đợi thêm thời gian nữa em tự mày mò học mảng sau. Trời!!! Cứ nghĩ tới mảng là sợ không tả được, chắc nói ra anh cũng không hình dung được cảm giác em sợ công thức mảng đến như thế nào đâu.
Chúc anh ngày Chúa Nhật rất là vui vẻ, hạnh phúc & bình an.

 

[phihndhsp]

Anh bebo021999 ơi,
Cho em hỏi ngu anh nghen. Vì hàm Sumproduct() em không rành lắm nên câu hỏi quá ngu thì xin đừng phiền lòng.
Em đang học hàm Sumproduct() như sau= (Array 1, Array 2,...., Array n). Sao trong công thức Sumproduct() của anh lại có toán tử * thế, thay vì phải là ","
Thêm nữa, khi em thay đổi vùng dữ liệu như sau=SUMPRODUCT(($B$5:$I$12=$D$16)*($B$5:$I$12=$D$17))=0 (≠3) → Sao công thức lại ra trớt quớt vậy anh??? Em chỉ thêm vùng dữ liệu thôi mà, đâu có bớt đâu, dư chứ đâu có thiếu đâu mà sao Excel lại không có work??? Em sai chỗ nào thế anh, phiền anh giải thích rồi. Chân thành cảm ơn anh.

Công thức Countifs() này là công thức mảng phải không anh, nếu vậy thì thôi em chưa học. Em chỉ mới học Countif() thôi chứ chưa học Countifs().
Không phải là em lười không muốn học công thức mảng nhưng mà em tự thấy Excel của em còn rất hạn chế nên đợi thêm thời gian nữa em tự mày mò học mảng sau. Trời!!! Cứ nghĩ tới mảng là sợ không tả được, chắc nói ra anh cũng không hình dung được cảm giác em sợ công thức mảng đến như thế nào đâu.
Chúc anh ngày Chúa Nhật rất là vui vẻ, hạnh phúc & bình an.

countif là trường hợp đặc biệt của countifs. Countif đếm có 1 đk, còn countifs là đếm thỏa nhiều đk
Countifs(vùng đk1, đk1, vùng đk2, vùng đk2,...)

 

[Đỗ Tú Anh]

countif là trường hợp đặc biệt của countifs. Countif đếm có 1 đk, còn countifs là đếm thỏa nhiều đk
Countifs(vùng đk1, đk1, vùng đk2, vùng đk2,...)

phihndhsp ơi,
Vậy nếu Countifs() & Sumproduct() cùng ra kết quả như nhau vậy Sumproduct() cũng là hàm đếm thỏa nhiều điều kiện sao??? Theo mình được hiểu là hàm nào mà có chữ Sum.... có nghĩa là tính tổng mà chẳng hạn tổng doanh thu... đâu phải là hàm đếm đâu. Sao mình vẫn rối rối thế nào ấy với cái hàm Sumproduct() này. Chả biết diễn tả sao cho bạn hiểu nữa. Tệ quá!
Chúc phihndhsp luôn vui.

 

[phihndhsp]

Hiểu nôn na như thế này nè
sumproduct là hàm cha hàm mẹ
nó có thể bao quát hàm sum, sumif,sumifs, count,countif,countifs
dao thì có nhiều loại dao, không ai lấy dao mổ trâu để mổ gà
(ý trên là ý chủ quan của cá nhân tôi thôi nha)

 

[Đỗ Tú Anh]

Hiểu nôn na như thế này nè
sumproduct là hàm cha hàm mẹ
nó có thể bao quát hàm sum, sumif,sumifs, count,countif,countifs
dao thì có nhiều loại dao, không ai lấy dao mổ trâu để mổ gà
(ý trên là ý chủ quan của cá nhân tôi thôi nha)

Cám ơn phihndhsp nha.
Phiền bạn nhiều quá.
Hy vọng là trong tương lai thì mình có thể hiểu rành về hàm Sumproduct() hơn hen, bây giờ thì vẫn cứ rối rối, buồn ghê, vẫn chưa hình dung 1 cách toàn diện cách áp dụng hàm Sumproduct() vào bài toán cụ thể, tệ thật!
Chúc phihndhsp luôn vui & hạnh phúc.

 

[batman1]

Theo mình được hiểu là hàm nào mà có chữ Sum.... có nghĩa là tính tổng mà chẳng hạn tổng doanh thu... đâu phải là hàm đếm đâu.

SUMPRODUCT, SUM thì là tổng, COUNTIF là đếm. Nhưng một khi bạn chỉ cần kết quả mà trong trường hợp cụ thể nào đó cả 3 hàm đều trả về cùng 1 giá trị thì lúc đó bạn có thể dùng hàm nào cũng được.
Tôi cho vd. Giả sử bạn có A1 = A3 = A7 = 1, còn các ô khác đều là 0.
Giả sử bạn cần ĐẾM các ô có giá trị là 1

=COUNTIF(A1:A10;1)

Kết quả trả về là 3 - có 3 ô với giá trị 1. Nhưng do có 3 ô 1 và 7 ô 0 nên

=SUM(A1:A10)

cũng trả về kết quả 3

=SUMPRODUCT(A1:A10)

cũng trả về kết quả 3

Do bạn chỉ quan tâm tới kết quả (3) nên bạn dùng hàm nào cũng được.

Bạn hãy cố hiểu các công thức

=SUMPRODUCT(($B$5:$I$11=$D$16)*($B$6:$I$12=$D$17))
=COUNTIFS($B$5:$I$11,D16,$B$6:$I$12,D17)

Công thức thứ 2 là ĐẾM các ô có ở $B$5:$I$11 giá trị D16 mà đồng thời ở ô tương ứng trong $B$6:$I$12 có giá trị D17. Giả sử có tất cả x ô như thế.

Biểu thức ($B$5:$I$11=$D$16) sẽ trả về 1 mảng có 7 dòng 8 cột các giá trị TRUE/FALSE. Những ô nào trong $B$5:$I$11 có giá trị D16 thì ở những ô tương ứng trong mảng trả về ($B$5:$I$11=$D$16) sẽ có giá trị TRUE, còn các ô khác có giá trị FALSE

Tương tự biểu thức ($B$6:$I$12=$D$17) sẽ trả về 1 mảng có 7 dòng 8 cột các giá trị TRUE/FALSE. Những ô nào trong $B$6:$I$12 có giá trị D17 thì ở những ô tương ứng trong mảng trả về ($B$6:$I$12=$D$17) sẽ có giá trị TRUE, còn các ô khác có giá trị FALSE

Biểu thức ($B$5:$I$11=$D$16)*($B$6:$I$12=$D$17) - nhân 2 mảng - sẽ trả về 1 mảng có 7 dòng 8 cột các giá trị 1/0. Những ô nào trong $$B$5:$I$11=$D$16 có giá trị D16 mà đồng thời ở ô tương ứng trong $B$6:$I$12 có giá trị D17 thì ở những ô tương ứng trong mảng trả về ($B$5:$I$11=$D$16)*($B$6:$I$12=$D$17) sẽ có giá trị 1, còn các ô khác có giá trị 0. Rõ ràng trong mảng ($B$5:$I$11=$D$16)*($B$6:$I$12=$D$17) có đúng x ô với giá trị 1, các ô còn lại có giá trị 0. Vậy thì SUMPRODUCT cũng trả về giá trị x (Tổng = 1 + 1 + ... + 1 (x số 1) + 0 + 0 + ... + 0 = x)

Do bạn chỉ quan tâm tới kết quả mà 2 hàm đều trả về cùng giá trị thì dùng hàm nào chả được. Sao lại cứ phải lăn tăn chuyện đếm hay tổng?

Cô giáo cho bài: Hãy tính TỔNG 100 số tự nhiên đầu tiên.
Các bạn hì hục tính tổng 1 + 2 + ... + 100. Riêng cậu bé Gauss tính (1 + 100)*(100 / 2)

Thực ra cậu ta có tính thuần TỔNG đâu. Cậu ta làm cả phép NHÂN đấy chứ. Nhưng nói cho cùng thì cô giáo cần kết quả, và bé Gauss có kết quả cho cô.

 

[bebo021999]

Anh bebo021999 ơi,
Cho em hỏi ngu anh nghen. Vì hàm Sumproduct() em không rành lắm nên câu hỏi quá ngu thì xin đừng phiền lòng.
Em đang học hàm Sumproduct() như sau= (Array 1, Array 2,...., Array n). Sao trong công thức Sumproduct() của anh lại có toán tử * thế, thay vì phải là ","
....
Công thức Countifs() này là công thức mảng phải không anh

Vụ dùng "*" hay "," trong SUMPRODUCT bàn nhiều rồi mà: Dùng * thì dùng bao quát các trường hợp text, true/false không cần +0 nữa

Countifs là công thức mảng? Có vẻ như Tú Anh đanh đánh đồng tất cả những gì mà mình thấy khó hiểu, cao siêu, sợ nó và gán cho nó cái tên "mảng"?
Danh từ số nhiều có "s" tức là dạng Countif nhiều điều kiện thế thôi.

Countifs(BT1,dk1,BT2,dk2,...)

Countifs là phạm trù hẹp hơn countif. Đếm số thành viên nữ trong GPE thì ra 100 chẳng hạn, nhưng đếm số thành viên nữ trong GPE mà giỏi excel, đương nhiên kết quả chỉ trên đầu ngón tay

 

[bebo021999]

phihndhsp ơi,
Vậy nếu Countifs() & Sumproduct() cùng ra kết quả như nhau vậy Sumproduct() cũng là hàm đếm thỏa nhiều điều kiện sao??? Theo mình được hiểu là hàm nào mà có chữ Sum.... có nghĩa là tính tổng mà chẳng hạn tổng doanh thu... đâu phải là hàm đếm đâu. Sao mình vẫn rối rối thế nào ấy với cái hàm Sumproduct() này. Chả biết diễn tả sao cho bạn hiểu nữa.

Countif, sumif là hàm chuyên dụng để đếm, sum có điều kiện. Sumproduct là hàm bao quát hơn, có thể đếm và sum. Tuy nhiên, nếu có thể dùng countif, sumif thì không nên dùng Sumproduct để giảm tốc độ tính toán. VD: có cầu thì nên đi qua cầu cho khỏe, không nên lội qua sông.
Tuy nhiên, có trường hợp phải dùng Sumproduct thay thế cho Countif hay Sumif, ví dụ như sau:
Cột A chứa ngày tháng , cột B chứa con số
Muốn đếm hay cộng cột B theo điều kiện ngày cụ thể nào đó, người ta dùng countif hay sumif
Muốn đếm hay cộng cột B theo điều kiện tháng cụ thể nào đó (vd tháng 2), countif và sumif không thể, nhưng sumproduct làm được:
=COUNTIF(A1:A2,2)==> báo lỗi
=SUMPRODUCT((MONTH(A1:A2)=2)*B1:B2)==> ra kết quả

Ghi nhớ: Countif và sumif làm việc trên vùng cụ thể trên bảng tính (A1:A2), nhưng không làm việc với vùng trung gian tính toán (MONTH(A1:A2))

 

[Mộng Thường 2001]

Tuy nhiên, nếu có thể dùng countif, sumif thì không nên dùng Sumproduct để giảm tốc độ tính toán. VD: có cầu thì nên đi qua cầu cho khỏe, không nên lội qua sông.

Bằng cách nào để biết tốc độ tính toán khi so sánh các hàm với nhau như so sánh giữa: Sumif, Countif và Sumproduct hả anh?

 

[moctriet]

Bằng cách nào để biết tốc độ tính toán khi so sánh các hàm với nhau như so sánh giữa: Sumif, Countif và Sumproduct hả anh?

Mình thấy bài khá hay, bạn dùng đc VBA thì test thử nha
http://dailydoseofexcel.com/archives/2015/04/25/timing-formulas/

Bạn chỉ việc copy nó vào một module, chọn cell có công thức rồi. Nhấn Alt F8 chọn TimeFormula.

 

[bebo021999]

Bằng cách nào để biết tốc độ tính toán khi so sánh các hàm với nhau như so sánh giữa: Sumif, Countif và Sumproduct hả anh?

Đơn giản thôi, xét ví dụ Sumif và Sumproduct như bảng sau: Cột B là tháng, cột C là số tiền
Tính tổng số tiền 3 tháng, từ tháng 1 tới tháng 3
=SUMIF(B2:B13,"<=3",C2:C13)
=SUMPRODUCT((B2:B13<=3)*C2:C13)
Cái nào nhanh hơn:
Cả 2 cái đều duyệt cột B từ tháng 1 tới tháng 12, nhưng:
* SUMIF: Xét từng dòng trong cột B trước, nếu thỏa điều kiện <=3 thì mới tính toán:
Tháng 1 =5
Tháng 2 = 5+10= 15
Tháng 3: 15+15 =30
Từ tháng 4 trở đi, nó không thực hiện tính toán vì nhận thấy không thỏa điều kiện tháng <=3
* SUMPRODUCT: Xét từng dòng trong cột B, cộng bất chấp thỏa hay không thỏa:
Tháng 1 = (1<=3)*5 = 1*5 = 5
Tháng 2 = 5+(2<=3)*10= 5 + 1*10 = 15
Tháng 3= 15+(3<=3)*15 =15+1*15=30
Tháng 4=30+(4<=3)*20 = 30+0*20=30
Tháng 5=30+(5<=3)*25 = 30+0*25=30
...
Tháng 12=30+(12<=3)*60 = 30+0*60=30

Kết luận: SUMIF thực hiện 3 phép tính, SUMPRODUCT thực hiện 12 phép tính

Với data lớn, vài chục ngàn dòng, sẽ thấy sự khác biệt!!!

 

[bebanhbeo]

Đơn giản thôi, xét ví dụ Sumif và Sumproduct như bảng sau: Cột B là tháng, cột C là số tiền
Tính tổng số tiền 3 tháng, từ tháng 1 tới tháng 3
=SUMIF(B2:B13,"<=3",C2:C13)
=SUMPRODUCT((B2:B13<=3)*C2:C13)
Cái nào nhanh hơn:
Cả 2 cái đều duyệt cột B từ tháng 1 tới tháng 12, nhưng:
* SUMIF: Xét từng dòng trong cột B trước, nếu thỏa điều kiện <=3 thì mới tính toán:
Tháng 1 =5
Tháng 2 = 5+10= 15
Tháng 3: 15+15 =30
Từ tháng 4 trở đi, nó không thực hiện tính toán vì nhận thấy không thỏa điều kiện tháng <=3
* SUMPRODUCT: Xét từng dòng trong cột B, cộng bất chấp thỏa hay không thỏa:
Tháng 1 = (1<=3)*5 = 1*5 = 5
Tháng 2 = 5+(2<=3)*10= 5 + 1*10 = 15
Tháng 3= 15+(3<=3)*15 =15+1*15=30
Tháng 4=30+(4<=3)*20 = 30+0*20=30
Tháng 5=30+(5<=3)*25 = 30+0*25=30
...
Tháng 12=30+(12<=3)*60 = 30+0*60=30

Kết luận: SUMIF thực hiện 3 phép tính, SUMPRODUCT thực hiện 12 phép tính

Với data lớn, vài chục ngàn dòng, sẽ thấy sự khác biệt!!!

View attachment 191278

 

[Mộng Thường 2001]

Mình thấy bài khá hay, bạn dùng đc VBA thì test thử nha
http://dailydoseofexcel.com/archives/2015/04/25/timing-formulas/

Bạn chỉ việc copy nó vào một module, chọn cell có công thức rồi. Nhấn Alt F8 chọn TimeFormula.

Code này không chạy trên máy 64bit.
Bạn chịu khó chỉnh giúp code file này cho phù hợp máy 64bit, để chạy thử các hàm chứa trong file.
Cảm ơn nhiều. hihi ^o^

 

[Mộng Thường 2001]

Đơn giản thôi, xét ví dụ Sumif và Sumproduct như bảng sau: Cột B là tháng, cột C là số tiền
Tính tổng số tiền 3 tháng, từ tháng 1 tới tháng 3
=SUMIF(B2:B13,"<=3",C2:C13)
=SUMPRODUCT((B2:B13<=3)*C2:C13)
Cái nào nhanh hơn:
Cả 2 cái đều duyệt cột B từ tháng 1 tới tháng 12, nhưng:
* SUMIF: Xét từng dòng trong cột B trước, nếu thỏa điều kiện <=3 thì mới tính toán:
Tháng 1 =5
Tháng 2 = 5+10= 15
Tháng 3: 15+15 =30
Từ tháng 4 trở đi, nó không thực hiện tính toán vì nhận thấy không thỏa điều kiện tháng <=3
* SUMPRODUCT: Xét từng dòng trong cột B, cộng bất chấp thỏa hay không thỏa:
Tháng 1 = (1<=3)*5 = 1*5 = 5
Tháng 2 = 5+(2<=3)*10= 5 + 1*10 = 15
Tháng 3= 15+(3<=3)*15 =15+1*15=30
Tháng 4=30+(4<=3)*20 = 30+0*20=30
Tháng 5=30+(5<=3)*25 = 30+0*25=30
...
Tháng 12=30+(12<=3)*60 = 30+0*60=30

Kết luận: SUMIF thực hiện 3 phép tính, SUMPRODUCT thực hiện 12 phép tính

Với data lớn, vài chục ngàn dòng, sẽ thấy sự khác biệt!!!

View attachment 191278

Cái này cũng mới là suy luận theo suy nghĩ chủ quan, chứ chưa phải là bằng chứng xác thực máy thật sự xử lý bằng cách nào, chỉ có mấy ông tạo ra nó mới trả lời chính xác.
Tuy nhiên, cách của bạn @moctriet hy vọng là câu trả lời khách quan bằng việc cân đong đo đếm.
hihi ^o^

 

[bebo021999]

Cái này cũng mới là suy luận theo suy nghĩ chủ quan, chứ chưa phải là bằng chứng xác thực máy thật sự xử lý bằng cách nào, chỉ có mấy ông tạo ra nó mới trả lời chính xác.
Tuy nhiên, cách của bạn @moctriet hy vọng là câu trả lời khách quan bằng việc cân đong đo đếm.
hihi ^o^

Bạn vào đây xem kết quả thử nhé.
https://exceloffthegrid.com/optimize-formula-calculation-speed-part-8-sum-with-multiple-criteria/
Đại khái thế này:
Họ test SUM nhiều điều kiện. Xét trên 999.000 dòng, có các phương án sau:
1)Dùng cột phụ, sau đó SUMIF => 0.72 s
2) SUMIFS => 1.29s
3) Sum(array) : Dùng công thức mảng =>7.34s
4) Sumproduct: => 7.35s

Tóm lại, Nếu dùng được cột phụ là tốt nhất. Không thì dùng SUMIFS.
Công thức mảng và SUMPRODUCT là lựa chọn cuối cùng (vì phải thực hiện 999.000 phép tính)

 

[Mộng Thường 2001]

Bạn vào đây xem kết quả thử nhé.
https://exceloffthegrid.com/optimize-formula-calculation-speed-part-8-sum-with-multiple-criteria/
Đại khái thế này:
Họ test SUM nhiều điều kiện. Xét trên 999.000 dòng, có các phương án sau:
1)Dùng cột phụ, sau đó SUMIF => 0.72 s
2) SUMIFS => 1.29s
3) Sum(array) : Dùng công thức mảng =>7.34s
4) Sumproduct: => 7.35s

Tóm lại, Nếu dùng được cột phụ là tốt nhất. Không thì dùng SUMIFS.
Công thức mảng và SUMPRODUCT là lựa chọn cuối cùng (vì phải thực hiện 999.000 phép tính)

Mấy ông này đưa bằng chứng "ăn gian": trưng cột phụ đánh bằng tay mà không dùng công thức!?? của đáng tội!?, đáng lẽ phải cộng cả hai thời gian của Sumif dùng cột phụ + công thức tạo ra cột phụ nữa mới tương đồng với các hàm khác được chứ!

Như đúng lý ra họ phải xét Sumproduct() như sau:
=SUMPRODUCT(('List Unsorted Data - Non Unique'!$G$2:$G$100000=A6&"-"&B6&"-"&C6&"-"&D6&"-"&E6&"-"&F6)*('List Unsorted Data - Non Unique'!$H$2:$H$100000)) thì mới tương đồng với Sumif() được chứ!

Nhưng dù sao, đây là mới là bằng chứng thuyết phục nhất bằng việc cân đong đo đếm, chỉ tiếc là VBA này cũng dùng có 32bit.

Cảm ơn anh bebo021999 nhiều.
hihi ^o^

 

[HieuCD]

Đơn giản thôi, xét ví dụ Sumif và Sumproduct như bảng sau: Cột B là tháng, cột C là số tiền
Tính tổng số tiền 3 tháng, từ tháng 1 tới tháng 3
=SUMIF(B2:B13,"<=3",C2:C13)
=SUMPRODUCT((B2:B13<=3)*C2:C13)
Cái nào nhanh hơn:
Cả 2 cái đều duyệt cột B từ tháng 1 tới tháng 12, nhưng:
* SUMIF: Xét từng dòng trong cột B trước, nếu thỏa điều kiện <=3 thì mới tính toán:
Tháng 1 =5
Tháng 2 = 5+10= 15
Tháng 3: 15+15 =30
Từ tháng 4 trở đi, nó không thực hiện tính toán vì nhận thấy không thỏa điều kiện tháng <=3
* SUMPRODUCT: Xét từng dòng trong cột B, cộng bất chấp thỏa hay không thỏa:
Tháng 1 = (1<=3)*5 = 1*5 = 5
Tháng 2 = 5+(2<=3)*10= 5 + 1*10 = 15
Tháng 3= 15+(3<=3)*15 =15+1*15=30
Tháng 4=30+(4<=3)*20 = 30+0*20=30
Tháng 5=30+(5<=3)*25 = 30+0*25=30
...
Tháng 12=30+(12<=3)*60 = 30+0*60=30

Kết luận: SUMIF thực hiện 3 phép tính, SUMPRODUCT thực hiện 12 phép tính

Với data lớn, vài chục ngàn dòng, sẽ thấy sự khác biệt!!!

View attachment 191278

Nếu công thức

=SUMIF(B2:B13,"<=300",C2:C13)
=SUMPRODUCT((B2:B13<=300)*C2:C13)

số phép tính ngang nhau, theo bạn công thức nào nhanh hơn?

 

[bebo021999]

Nếu công thức

=SUMIF(B2:B13,"<=300",C2:C13)
=SUMPRODUCT((B2:B13<=300)*C2:C13)

số phép tính ngang nhau, theo bạn công thức nào nhanh hơn?

Chắc chắn SUMIF nhanh hơn rồi.
Giả sử tất cả các số trong vùng B2:B13 đều <=300
* SUMIF tính như sau:
C2+C3+...+C13
* SUMPRODUCT tính như sau (Tổng của tích)
TRUE*C2=1*C2=C2==>Đưa C2 vào bộ nhớ
TRUE*C3=1*C3=C3==>Đưa C3 vào bộ nhớ
...
TRUE*C12=1*C13=C13==> Đưa C13 vào bộ nhớ
Sau đó mới làm phép tính C2+C3+...+C13 như SUMIF

SUMPRODUCT phải qua bước tính tích trước, sau đó mới là tổng.

 

[HieuCD]

Chắc chắn SUMIF nhanh hơn rồi.
Giả sử tất cả các số trong vùng B2:B13 đều <=300
* SUMIF tính như sau:
C2+C3+...+C13
* SUMPRODUCT tính như sau (Tổng của tích)
TRUE*C2=1*C2=C2==>Đưa C2 vào bộ nhớ
TRUE*C3=1*C3=C3==>Đưa C3 vào bộ nhớ
...
TRUE*C12=1*C13=C13==> Đưa C13 vào bộ nhớ
Sau đó mới làm phép tính C2+C3+...+C13 như SUMIF

SUMPRODUCT phải qua bước tính tích trước, sau đó mới là tổng.

Trường hợp đặc biệt

=SUMIF($A$2:$A$100000,">=0",$A$2:$A$100000)
=SUMPRODUCT($A$2:$A$100000)

Không biết công thức nào nhanh hơn

 

[Mộng Thường 2001]

Chắc chắn SUMIF nhanh hơn rồi.
Giả sử tất cả các số trong vùng B2:B13 đều <=300
* SUMIF tính như sau:
C2+C3+...+C13
* SUMPRODUCT tính như sau (Tổng của tích)
TRUE*C2=1*C2=C2==>Đưa C2 vào bộ nhớ
TRUE*C3=1*C3=C3==>Đưa C3 vào bộ nhớ
...
TRUE*C12=1*C13=C13==> Đưa C13 vào bộ nhớ
Sau đó mới làm phép tính C2+C3+...+C13 như SUMIF

SUMPRODUCT phải qua bước tính tích trước, sau đó mới là tổng.

Để thực nghiệm khách quan, nhờ chị bebanhbeo (nếu máy của chị chạy nền 32bit), thử dùm và trả kết quả bảng tính cho các công thức đã quy đổi tương đồng dưới đây, bằng cách nhấn nút Timer có trong sheet:

=SUMPRODUCT(('List Unsorted Data - Non Unique'!$G$2:$G$100000=A6&"-"&B6&"-"&C6&"-"&D6&"-"&E6&"-"&F6)*('List Unsorted Data - Non Unique'!$H$2:$H$100000))
=SUMIFS('List Unsorted Data - Non Unique'!$H$2:$H$100000,'List Unsorted Data - Non Unique'!$G$2:$G$100000,A6&"-"&B6&"-"&C6&"-"&D6&"-"&E6&"-"&F6)
=SUMIF('List Unsorted Data - Non Unique'!$G$2:$G$100000,A6&"-"&B6&"-"&C6&"-"&D6&"-"&E6&"-"&F6,'List Unsorted Data - Non Unique'!$H$2:$H$100000)
=SUM(('List Unsorted Data - Non Unique'!$G$2:$G$100000=A6&"-"&B6&"-"&C6&"-"&D6&"-"&E6&"-"&F6)*('List Unsorted Data - Non Unique'!$H$2:$H$100000))
Cảm ơn chị bebanhbeo nhìu nhìu!
hihi ^o^

 

[bebanhbeo]

Để thực nghiệm khách quan, nhờ chị bebanhbeo (nếu máy của chị chạy nền 32bit), thử dùm và trả kết quả bảng tính cho các công thức đã quy đổi tương đồng dưới đây, bằng cách nhấn nút Timer có trong sheet:

=SUMPRODUCT(('List Unsorted Data - Non Unique'!$G$2:$G$100000=A6&"-"&B6&"-"&C6&"-"&D6&"-"&E6&"-"&F6)*('List Unsorted Data - Non Unique'!$H$2:$H$100000))
=SUMIFS('List Unsorted Data - Non Unique'!$H$2:$H$100000,'List Unsorted Data - Non Unique'!$G$2:$G$100000,A6&"-"&B6&"-"&C6&"-"&D6&"-"&E6&"-"&F6)
=SUMIF('List Unsorted Data - Non Unique'!$G$2:$G$100000,A6&"-"&B6&"-"&C6&"-"&D6&"-"&E6&"-"&F6,'List Unsorted Data - Non Unique'!$H$2:$H$100000)
=SUM(('List Unsorted Data - Non Unique'!$G$2:$G$100000=A6&"-"&B6&"-"&C6&"-"&D6&"-"&E6&"-"&F6)*('List Unsorted Data - Non Unique'!$H$2:$H$100000))
Cảm ơn chị bebanhbeo nhìu nhìu!
hihi ^o^

Kết quả đây

 

[moctriet]

Cái này cũng mới là suy luận theo suy nghĩ chủ quan, chứ chưa phải là bằng chứng xác thực máy thật sự xử lý bằng cách nào, chỉ có mấy ông tạo ra nó mới trả lời chính xác.
Tuy nhiên, cách của bạn @moctriet hy vọng là câu trả lời khách quan bằng việc cân đong đo đếm.
hihi ^o^

Mình sắp code xong rồi. Để xong cái mình đang làm sẽ thử xử lý.

 

[Mộng Thường 2001]

Kết quả đây View attachment 191292

hihi hihi hihi ^o^, 5 phần vạn giây là sao chị!? hổng hỉu lun!
hihi ^o^

 

[bebanhbeo]

hihi hihi hihi ^o^, 5 phần vạn giây là sao chị!? hổng hỉu lun!
hihi ^o^

Hic, bữa giờ đi tất niên chuột nhà mình uống nhiều quá hay sao í ! Cứ quay vòng vòng hoài
p/s: Code có lỗi hông ta ?

 

[Mộng Thường 2001]

Hic, bữa giờ đi tất niên chuột nhiều uống nhiều quá hay sao í ! Cứ quay vòng vòng hoài
p/s: Code có lỗi hông ta ?

Code "ngoại" theo link của anh bebo021999 gửi mà chị: https://exceloffthegrid.com/optimize-formula-calculation-speed-part-8-sum-with-multiple-criteria/
Để mấy anh nhà mình xử lý: xem kết quả "U23 VN" ra sao chị!
Cảm ơn chị nhìu!
hihi ^o^

 

[Đỗ Tú Anh]

Vụ dùng "*" hay "," trong SUMPRODUCT bàn nhiều rồi mà: Dùng * thì dùng bao quát các trường hợp text, true/false không cần +0 nữa

Anh bebo021999 ơi, đừng thấy phiền vì em hay quên , dạo này não em đang trong giai đoạn lão hóa nên không còn nhớ dai như xưa nữa nên có lẽ lỡ quên cái kiến thức đó rồi. Giờ thì biết rồi anh ạ, cùi thơm ghê hen anh.
Thêm nữa, hôm qua thầy ndu cũng trả lời tương tự là nếu trong hàm Sumproduct() có toán tử */+ thay vì "," thì nguyên cả cái cụm đó là 1 Array. Cho nên bây giờ em gom vô cách giải thích của anh luôn là hàm Sumproduct() có toán tử */+ thay vì "," thì nguyên công thức đó là 1 Array & có thể dùng để ép "chuỗi dạng No" sang No hay True/False sang 1/0.
Dạ, sẽ ráng ghi nhớ kiến thức này ạ, hy vọng sẽ không hỏi câu hỏi cùi thơm như vậy trong tương lai nữa. Nếu mà lỡ có thì xin bỏ qua, đừng thấy phiền lòng . Em cảm ơn anh đã kiên nhẫn chịu đựng mấy câu hỏi ngu của em ạ.

Countifs là công thức mảng? Có vẻ như Tú Anh đanh đánh đồng tất cả những gì mà mình thấy khó hiểu, cao siêu, sợ nó và gán cho nó cái tên "mảng"?
Danh từ số nhiều có "s" tức là dạng Countif nhiều điều kiện thế thôi.
Countifs(BT1,dk1,BT2,dk2,...)
Countifs là phạm trù hẹp hơn countif. Đếm số thành viên nữ trong GPE thì ra 100 chẳng hạn, nhưng đếm số thành viên nữ trong GPE mà giỏi excel, đương nhiên kết quả chỉ trên đầu ngón tay

Anh ơi, em không biết tí ti gì về mảng hết, hễ cứ thấy công thức lạ giống giống công thức từng học thì cứ nghĩ là mảng, sai rồi anh nhỉ, tệ thật!

Countif, sumif là hàm chuyên dụng để đếm, sum có điều kiện. Sumproduct là hàm bao quát hơn, có thể đếm và sum. Tuy nhiên, nếu có thể dùng countif, sumif thì không nên dùng Sumproduct để giảm tốc độ tính toán.

Dạ, em nghe anh ạ. Anh bảo sao thì em nghe thế ạ. Cám ơn anh đã hướng dẫn.
Chúc anh 1 buổi chiều rực rỡ, hoành tráng & sôi động

 

[Đỗ Tú Anh]

Các bạn ơi cho mình hỏi ngu câu này, nếu công thức làm file nặng thì có ảnh hưởng đến tốc độ tính toán không??? Có hay không??? Nếu có thì giảm hay tăng tốc độ tính toán???
Cám ơn đã trả lời.
Chúc các bạn luôn vui.

 

[bebo021999]

Các bạn ơi cho mình hỏi ngu câu này, nếu công thức làm file nặng thì có ảnh hưởng đến tốc độ tính toán không??? Có hay không??? Nếu có thì giảm hay tăng tốc độ tính toán???
Cám ơn đã trả lời.
Chúc các bạn luôn vui.

Tùy thuộc vào việc em định nghĩa thế nào là "nặng"? File có dung lượng lớn (MB) hay khi tính toán máy tốn nhiều thời gian để ra kết quả?
Nếu File có dung lượng lớn thì open lâu, nhưng ít công thức mảng thì khi open rồi thì vẫn xử lý ào ào.
Còn công thức làm file nặng (công thức liên quan đến mảng mà duyệt nguyên cột, kiểu như SUMPRODUCT(A:A,BB)) thì yên tâm mà ngồi đón giao thừa Congo

 

[Đỗ Tú Anh]

Tùy thuộc vào việc em định nghĩa thế nào là "nặng"? File có dung lượng lớn (MB) hay khi tính toán máy tốn nhiều thời gian để ra kết quả?
Nếu File có dung lượng lớn thì open lâu, nhưng ít công thức mảng thì khi open rồi thì vẫn xử lý ào ào.

Anh ơi, em chỉ hỏi file nặng do công thức thôi (đặc biệt mảng), không phải là do dung lượng file lớn.

Còn công thức làm file nặng (công thức liên quan đến mảng mà duyệt nguyên cột, kiểu như SUMPRODUCT(A:A,BB)) thì yên tâm mà ngồi đón giao thừa Congo

Tốt! Vậy coi như em có thể có câu trả lời rồi cho câu hỏi của mình rồi. Bây giờ chỉ còn chờ ý kiến của các bạn khác ra sao nữa thôi. Nếu sau vài tiếng mà chả thấy ai buồn có ý kiến gì thì em có thể hiểu là các bạn cũng đồng ý với post của anh là công thức liên quan đến mảng làm file nặng sẽ làm giảm tốc độ tính toán.
Cuối cùng em sẽ chốt ý kiến của mình.
Chúc anh luôn vui.

 

[bebanhbeo]

Anh ơi, em chỉ hỏi file nặng do công thức thôi (đặc biệt mảng), không phải là do dung lượng file lớn.

Tốt! Vậy coi như em có thể có câu trả lời rồi cho câu hỏi của mình rồi. Bây giờ chỉ còn chờ ý kiến của các bạn khác ra sao nữa thôi. Nếu sau vài tiếng mà chả thấy ai buồn có ý kiến gì thì em có thể hiểu là các bạn cũng đồng ý với post của anh là công thức liên quan đến mảng làm file nặng sẽ làm giảm tốc độ tính toán.
Cuối cùng em sẽ chốt ý kiến của mình.
Chúc anh luôn vui.

Còn mình muốn biết nặng hay nhẹ thì làm như thế này

 

[Đỗ Tú Anh]

Còn mình muốn biết nặng hay nhẹ thì làm như thế này

Mình thiệt lòng không hiểu ý bạn bebanhbeo là gì cả, nặng nhẹ của tập gym thì liên quan gì đến File nặng hay nhẹ do công thức???
Thông cảm dùm mình vì não mình bã đậu.

 

[Đỗ Tú Anh]

SUMPRODUCT, SUM thì là tổng, COUNTIF là đếm. Nhưng một khi bạn chỉ cần kết quả mà trong trường hợp cụ thể nào đó cả 3 hàm đều trả về cùng 1 giá trị thì lúc đó bạn có thể dùng hàm nào cũng được.

Dạ, hơi hơi hiểu rồi bác ạ. Tuy nhiên con nghĩ là con cần thực hành nhiều thì mới thuần thục & có cái nhìn tổng quan hơn về sự tương quan giữa những hàm Excel. Dạo trước lười quá, lại nản nữa (vì rất nhiều lý do) nên không muốn học Excel. Mấy hôm nay mới lại lôi Excel ra mày mò. Excel nhiều hàm quá, càng học càng thấy mênh mông, hoang mang. Hiện tại con chỉ biết có vài hàm mà khi áp dụng vô yêu cầu cụ thể lại đuối. Buồn ghê! Chân thành cảm ơn bác đã chỉ dẫn cho con ạ. Hy vọng bác tiếp tục chỉ dẫn cho con trong tương lai hen, cám ơn bác lắm lắm ạ.

Cô giáo cho bài: Hãy tính TỔNG 100 số tự nhiên đầu tiên.
Các bạn hì hục tính tổng 1 + 2 + ... + 100 = (1 + 100)*(100 / 2)

Bác ơi, giải thích dùm con lập luập logic của 1 + 2 + ... + 100 = (1 + 100)*(100 / 2), con vẫn chưa hiểu ạ, thiệt là củ chuối ghê!!!
Cám ơn bác.
Chúc bác luôn vui.

 

[bebo021999]

logic của 1 + 2 + ... + 100 = (1 + 100)*(100 / 2)

1+100 là cặp đầu tiên, 2+99 là cặp thứ 2, có 50 cặp như vậy với tổng bằng 101 mỗi cặp.kết quả là 101*50

 

[Đỗ Tú Anh]

Sau 1 thời gian dài đằng đẳng chờ đợi mà chả thấy ai trả lời câu hỏi của tôi ngoại trừ anh bebo021999 thì hôm nay tôi sẽ chốt lại vấn đề mà mọi người cứ tranh luận chưa có hồi kết.
Để trả lời cho câu hỏi

Bằng cách nào để biết tốc độ tính toán khi so sánh các hàm với nhau như so sánh giữa: Sumif, Countif và Sumproduct

Câu hỏi này thì tôi không biết câu trả lời nhưng tôi sẽ mượn bài viết của 2 thành viên kỳ cựu tinh thông Excel của GPE để trả lời câu hỏi cho người thắc mắc

Cái này là trao đổi thôi nghe! chủ yếu theo kinh nghiệm làm việc chung với mảng, hoàn toàn do "để ý" thấy, nên có gì "sai sai" cũng bỏ qua cho "em nhỏ nó mừng"
1. Các dạng COUNTIFs(), SUMIFs() nói chung tính toán so khớp bằng Range, không phải mảng (Array), tức nó ghi nhận mốc đầu mốc cuối của Vùng rồi cho chạy như vòng lặp For trong VBA, chứ không bê nguyên cả mảng vào bộ nhớ nên nhẹ. Cái này là do mình dùng Evaluate Formula xem biến chuyển của nó trong nội tại SUMIF hoặc COUNTIF, các tiêu chí nếu có tính toán hay gì gì, thì nó thực hiện trước, dừng lại và vẫn hiển thị Vùng y xì như trước, liền tức thì ra kết quả mà không qua công đoạn nào nữa.

2. SUMPRODUCT() do nó thực hiện với mảng, nên tùy thuộc mảng lấy vào là 1 hay 2 chiều, nhiều dòng nhiều cột không, thì tùy theo đó nó "bê nguyên cả mảng" rồi phải nhớ trong bộ nhớ, do vậy nó nặng chỉ khi dữ liệu đem vào quá nhiều.
Nên nếu cái gì xử được bằng SUMIF() hay COUNTIF() nên tận dụng tối đa, còn dùng SUMPRODUCT() thì nên thận trọng chút đừng sử dụng nhặng xị cái gì cũng dùng nó thì khó lòng biết tại sao mở file ra "nặng quá điiiii!"

Tùy thuộc vào việc em định nghĩa thế nào là "nặng"? File có dung lượng lớn (MB) hay khi tính toán máy tốn nhiều thời gian để ra kết quả?
Nếu File có dung lượng lớn thì open lâu, nhưng ít công thức mảng thì khi open rồi thì vẫn xử lý ào ào.
Còn công thức làm file nặng (công thức liên quan đến mảng mà duyệt nguyên cột, kiểu như SUMPRODUCT(A:A,BB)) thì yên tâm mà ngồi đón giao thừa Congo

→ Tôi hy vọng là 2 bài viết trên đã trả lời thắc mắc của người hỏi.
Bạn HieuCD đã từng kết luận thế này

trước đây có bài dùng sumproduct chạy rất chậm, phải dùng cột phụ và dùng sumif nhẹ hơn nhiều lần

bổ xung thêm hàm Sumifs cũng khá nặng, nhiều món đồ cổ có từ thời máy tính cấu hình thấp thì chạy rất nhẹ, còn các món hiện đại thì sài bộ nhớ hơi sang

Cớ sai lại hỏi anh bebo021999 câu hỏi cắc cớ này

Nếu công thức

=SUMIF(B2:B13,"<=300",C2:C13)
=SUMPRODUCT((B2:B13<=300)*C2:C13)

số phép tính ngang nhau, theo bạn công thức nào nhanh hơn?

Câu hỏi trên hoặc là thừa thải vì bạn HieuCD đã biết câu trả lời hoặc là bạn HieuCD có ý khác khi hỏi anh bebo021999 câu hỏi ấy. Vậy là vế đầu hay vế sau???
Còn trường hợp đặc biệt mà bạn HieuCD hỏi

Trường hợp đặc biệt

=SUMIF($A$2:$A$100000,">=0",$A$2:$A$100000)
=SUMPRODUCT($A$2:$A$100000)

Không biết công thức nào nhanh hơn

Tôi không giỏi Excel nhưng tôi sẽ thay mặt hiệp sĩ bebo021999 của tôi trả lời câu hỏi của bạn HieuCD bằng câu hỏi này "Để tính mức độ thịnh vượng của người dân VN thì đo bằng sự thịnh vượng của đại gia như Phạm Nhật Vượng... hay là người dân nghèo trong XHVN???" Sau khi bạn HieuCD trả lời câu hỏi của tôi thì bạn tự khắc trả lời được câu hỏi của bạn. Có đời thủa nào mà ai đem trường hợp dị biệt ra mà so sánh với trường hợp chung chung bao giờ đâu??? Có cùng đề tài đâu mà so sánh với thắc mắc hay hỏi, có phải là đang làm khó nhau không???
Mà thiệt là lạ lùng ghê, câu hỏi của tôi rất là rõ ràng

Các bạn ơi cho mình hỏi ngu câu này, nếu công thức làm file nặng thì có ảnh hưởng đến tốc độ tính toán không??? Có hay không??? Nếu có thì giảm hay tăng tốc độ tính toán???

thế nhưng tất cả đều quăng cục lơ, chả có ai buồn điếm xỉa trả lời ngoại trừ anh bebo021999, còn vấn đề tôi chưa từng hỏi thì tự bịa đặt ra trong đầu câu hỏi rồi tự gán ghép vấn đề đó cho tôi rồi lên tiếng nhục mạ, mạt sát... tôi. Thật là nực cười!!! Vậy là lý lẽ gì vậy???
Cuối cùng, tôi chốt lại là anh bebo021999 kết luận câu sau là hoàn toàn chính xác & có cơ sở lập luận vững chắc

nếu có thể dùng countif, sumif thì không nên dùng Sumproduct để giảm tốc độ tính toán.

Nếu ai còn chưa thỏa mãn với kết luận trên thì làm ơn liên hệ với bác quocgiacan mà hỏi vì bác quocgiacan là người giúp tôi đưa ra cơ sở cho kết luận của anh bebo021999, đừng tiếp tục hỏi anh bebo021999 những câu hỏi thừa thải nữa, làm mất thời gian quý báu của anh ấy 1 cách vô ích. Thank you very much!!!
Khuyến mãi

xét vlookup và lookup đứng riêng không lồng với hàm khác mới so sánh được
thông thường các hàm xử lý trên bảng nhiều dòng và nhiều cột như Vlookup, sumproduct, sumifs ... và được phép tùy chọn cột hoặc dòng trong bảng đó sẽ nặng hơn các hàm chỉ cho phép chọn 1 cột hoặc 1 dòng trong từng tham số như sumif, lookup, match...
lookup có thể có tham số là bảng gồm nhiều dòng và nhiều cột, nhưng các cột giữa nó không quan tâm tới, mà chỉ coi có 2 cột là cột đầu và cột cuối

Lookup có hai dạng, dang tham chiếu và bảng dò thì nhẹ hơn vlookup, nhưng lookup có điều kiện thì sẽ nặng hơn, vì nó có dinh tới mảng, mà mảng thì lại được lưu trong bộ nhớ ảo và phải tính toán trên đó, không thể thấy được mà chúng ta chỉ có thể tưởng tượng cách hoạt động của nó, nên nó sẽ nặng hơn so với những mảng đã được đưa sẵn ra bảng tính excel hay còn gọi là range, trong mấy cách dò thì index+macth có lẽ là nhanh nhất!!!

Chúc tất cả mọi người rất là vui vẻ, bình an & thỏa mãn

~ THE END ~​