giúp em công thức đếm với (2 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?