Excel 2003 hình như không có công thức COUNTIFS ??? (1 người xem)

[Blad01]

Tôi sử dụng hàm này để đếm số điểm trong khoảng từ 7 đến 8.9 Excell 2007 tính toán rất chính xác, thế nhung cũng với công thức đó chuyển qua Excell 2003 nó báo lỗi #NAME. Vậy có nghĩa là sao ???
Công thức tôi sử dụng như sau:

=COUNTIFS(E6:E105,">=7",E6:E105,"<9")

Mong các bạn giải thích giúp. Và nếu tôi muốn đếm các số nằm trong khoảng đó thì phải dùng hàm gì (với excell 2003).
Cũng tiện đây tôi cũng thắc mắc một vấn đề thế này: Tôi có công thức sau (sử dụng trên Excell 2007 chạy rất tốt)

=IF(COUNTIFS(M$6:M$103,">=7",M$6:M$103,"<9")<>0,COUNTIFS(M$6:M$103,">=7",M$6:M$103,"<9"),"")

Nhưng khi mở File chứa công thức đó trên Excell 2003 thì nó tự thành như sau và báo lỗi #NAME:

=IF([COLOR=Red]_xlfn.[/COLOR]COUNTIFS(M$6:M$103,">=7",M$6:M$103,"<9")<>0,[COLOR=Red][B]_xlfn.[/B][/COLOR]COUNTIFS(M$6:M$103,">=7",M$6:M$103,"<9"),"")

Công thức đã bị thêm vào phần tôi bôi đỏ ??? BÓ TAY.
Chân thành cảm ơn.

 

[Mr Okebab]

Tôi sử dụng hàm này để đếm số điểm trong khoảng từ 7 đến 8.9 Excell 2007 tính toán rất chính xác, thế nhung cũng với công thức đó chuyển qua Excell 2003 nó báo lỗi #NAME. Vậy có nghĩa là sao ???
Công thức tôi sử dụng như sau:

=COUNTIFS(E6:E105,">=7",E6:E105,"<9")

Mong các bạn giải thích giúp. Và nếu tôi muốn đếm các số nằm trong khoảng đó thì phải dùng hàm gì (với excell 2003).
Cũng tiện đây tôi cũng thắc mắc một vấn đề thế này: Tôi có công thức sau (sử dụng trên Excell 2007 chạy rất tốt)

=IF(COUNTIFS(M$6:M$103,">=7",M$6:M$103,"<9")<>0,COUNTIFS(M$6:M$103,">=7",M$6:M$103,"<9"),"")

Nhưng khi mở File chứa công thức đó trên Excell 2003 thì nó tự thành như sau và báo lỗi #NAME:

=IF([COLOR=Red]_xlfn.[/COLOR]COUNTIFS(M$6:M$103,">=7",M$6:M$103,"<9")<>0,[COLOR=Red][B]_xlfn.[/B][/COLOR]COUNTIFS(M$6:M$103,">=7",M$6:M$103,"<9"),"")

Công thức đã bị thêm vào phần tôi bôi đỏ ??? BÓ TAY.
Chân thành cảm ơn.

Hãy dùng hàm sumproduct bạn ạ.Nó sẽ giúp bạn còn nhiều hơn là Sumifs hoặc countifs giúp bạn.

Hai hàm Sumifs và Countifs chỉ có ở Office 2007 và Office 2009 thôi (Bill nói)

Chúc thành công.

Thân!

 

[Blad01]

Hãy dùng hàm sumproduct bạn ạ.Nó sẽ giúp bạn còn nhiều hơn là Sumifs hoặc countifs giúp bạn.

Hai hàm Sumifs và Countifs chỉ có ở Office 2007 và Office 2009 thôi (Bill nói)

Chúc thành công.

Thân!

Trong trường hợp này phải dùng Sumproduct thế nào vậy bạn sao mình đã đọc về hàm này nhưng vẫn chưa thành thục cách làm mấy. mà dùng để đếm có điều kiện thì mình chưa làm bao giờ cả. Mong bạn chỉ giáo.

 

[cadafi]

Trong trường hợp này phải dùng Sumproduct thế nào vậy bạn sao mình đã đọc về hàm này nhưng vẫn chưa thành thục cách làm mấy. mà dùng để đếm có điều kiện thì mình chưa làm bao giờ cả.

Chào bạn,
SUMPRODUCT thay thế COUNTIFS và SUMIFS như sau:

Thay:
=COUNTIFS(E6:E105,">=7",E6:E105,"<9")

Bằng:
=SUMPRODUCT(--(E6:E105>=7),--(E6:E105<9))

Thay
=SUMIFS(E6:E105,E6:E105,">=7",E6:E105,"<9")

Bằng:
=SUMPRODUCT((E6:E105),--(E6:E105>=7),--(E6:E105<9))

 

[hacmuli]

Chào bạn,
SUMPRODUCT thay thế COUNTIFS và SUMIFS như sau:

Thay:
=COUNTIFS(E6:E105,">=7",E6:E105,"<9")

Bằng:
=SUMPRODUCT(--(E6:E105>=7),--(E6:E105<9))

Thay
=SUMIFS(E6:E105,E6:E105,">=7",E6:E105,"<9")

Bằng:
=SUMPRODUCT((E6:E105),--(E6:E105>=7),--(E6:E105<9))

Cám ơn bác nhiều cho em hỏi bác có thể nói rõ hơn va fgiải thích cho em sao có cái dấu "--" trước mảng so sánh trên không??

 

[DOSNET]

Cám ơn bác nhiều cho em hỏi bác có thể nói rõ hơn va fgiải thích cho em sao có cái dấu "--" trước mảng so sánh trên không??

Khi làm việc với công thức mảng, phép tính trả về mảng giá trị {TRUE ,FAULT,TRUE ,FAULT...} để thức hiện được phép tính người ta thường nhân mảng đó với 1 để trả về mảng {1,0,1,0...}, "--" cũng là một dạng tùy biến của phép tính *1 nhưng công thức dễ nhìn và thuận tiện khi nhập hơn.
Bạn nghiên cứu thêm công thức mảng tại đây . Thân !

 

[cadafi]

Sự khác biệt giữa "*1" và "--" trong hàm SUMRPODUCT

Khi làm việc với công thức mảng, phép tính trả về mảng giá trị {TRUE ,FAULT,TRUE ,FAULT...} để thức hiện được phép tính người ta thường nhân mảng đó với 1 để trả về mảng {1,0,1,0...}, "--" cũng là một dạng tùy biến của phép tính *1 nhưng công thức dễ nhìn và thuận tiện khi nhập hơn.
Bạn nghiên cứu thêm công thức mảng tại đây . Thân !

To hacmuli:

Giải thích như anh DOSNET là đầy đủ rồi. Tuy nhiên mình cũng nêu một số vấn đề để bạn hiểu rõ hơn:

1. Bạn phải đồng ý quan điểm này (giống như là tiên đề):

[B][COLOR="Blue"]1*TRUE = 1
1*FALSE=0[/COLOR][/B]

2. Trong excel, giả sử ô A1 có giá trị là 10, khi bạn gõ:

=[B][COLOR="Blue"]-[/COLOR][/B]A1
=[B][COLOR="Blue"]-[/COLOR][/B]10
Có nghĩa là excel tự ngầm định công thức của bạn là :
[COLOR="Blue"][B]=[B]-[/B]1*A1 = [B]-[/B]10[/B][/COLOR]

Như vậy, khi bạn gõ:

=[B][COLOR="Blue"]--[/COLOR][/B]A1
=10
Có nghĩa là excel tự ngầm định công thức của bạn là :
[B][COLOR="Blue"]=-1*(-1*A1) = 10[/COLOR][/B]

Như vậy:

=[COLOR="Blue"][B]-[/B]TRUE = [B]-1*[/B]TRUE = [B]-[/B][/COLOR]1

Và:

=[B][COLOR="Blue"]--[/COLOR][/B]TRUE = [B][COLOR="Blue"]-1[/COLOR][/B]*([B][COLOR="Blue"]-1[/COLOR][/B]*TRUE) = 1

Vậy, xét mảng MyArray = {TRUE,FALSE,TRUE,TRUE}
Khi bạn gõ:

[B][COLOR="Blue"]=--MyArray = --({TRUE,FALSE,TRUE,TRUE})[/COLOR][/B]

Diễn giải như sau:

[COLOR="Blue"][B]=--MyArray = -1*(-1*({TRUE,FALSE,TRUE,TRUE}))
={1,0,1,1}[/B][/COLOR]

----------------------------------
Vậy tại sao không dùng 1*(MyArray) mà lại dùng --MyArray, có hai lý do:

1. Công thức của bạn sẽ dể nhìn hơn, và khoa học hơn, vì dấu "--" giống như ngăn cách giữa các điều kiện.
2. Cái nào excel tự hiểu thì chắc chắn nhanh hơn là user khai báo. Khái niệm này hơi trừu tượng, nhưng bạn thử nghĩ ví dụ này xem:

Nếu bạn học thuộc lòng bảng cửu chương ở mức thuần thục, có nghĩa là ai hỏi 9*8 bằng bao nhiêu thì bạn trả lời ngay 9*8 = 72
Nếu không học thuộc lòng được thì phải làm phép cộng tám lần con số 9, hoặc phải mở bảng cửu chương ra!

Nếu bạn học thuộc lòng các bảng tính lũy thừa 2:
Ví dụ:
2^2 = 4
3^2 = 9
4^2 = 16
....
Vậy khi ai đó hỏi bạn 12^2 bằng bao nhiêu, bạn trả lời ngay 12^2 = 144
Nếu không thuộc lòng thì bạn phải làm toán nhân 12*12 = 144 hoặc phải lấy máy tính ra bấm

Như vậy là rõ rồi nhé bạn.

 

[ndu96081631]

Tùy công thức cụ thể mà xài dấu --
Như các công thức trên thì chẳng có cái nào cần đến dấu này, bỏ luôn cũng không có vấn đề!
Như trường hợp này đây, 1 cột dử liệu duy nhất thì sẽ dùng đến dấu --
=SUMPRODUCT(--(A1:A20="A")) (đếm xem có bao nhiêu dử liệu =A)
Với cột A là các giá trị A, B, C

 

[cadafi]

Như các công thức trên thì chẳng có cái nào cần đến dấu này, bỏ luôn cũng không có vấn đề!

Các công thức bên dưới nếu không dùng "*1" hoặc "--" sẽ không cho ra kết quả đâu bạn.

Hãy thử với ví dụ sau:

=SUMPRODUCT([B]--[/B](E6:E105>=7),[B]--[/B](E6:E105<9))
hoặc
=SUMPRODUCT((E6:E105>=7)[B]*[/B](E6:E105<9))

và
[B]=SUMPRODUCT((E6:E105>=7),(E6:E105<9))[/B]

 

[ndu96081631]

Các công thức bên dưới nếu không dùng "*1" hoặc "--" sẽ không cho ra kết quả đâu bạn.

Hãy thử với ví dụ sau:

=SUMPRODUCT([B]--[/B](E6:E105>=7),[B]--[/B](E6:E105<9))
hoặc
=SUMPRODUCT((E6:E105>=7)[B]*[/B](E6:E105<9))
 
và
[B]=SUMPRODUCT((E6:E105>=7),(E6:E105<9))[/B]

Thì đương nhiên là vậy rồi
Không dùng -- thì dùng *
Tôi vẫn thường dùng theo cách này! Cãm giác dể hiểu hơn!
Thói quen là chính mà

 

[cadafi]

Không dùng -- thì dùng *
Tôi vẫn thường dùng theo cách này! Cãm giác dể hiểu hơn!
Thói quen là chính mà

Xin phép tranh luận tí. Nếu có người hỏi tại sao dùng * mà không dùng "--" thì bạn trả lời sao? Do thói quen!?

 

[SA_DQ]

Tôi sử dụng hàm này để đếm số điểm trong khoảng từ 7 đến 8.9 Excell 2007 tính toán rất chính xác, thế nhung cũng với công thức đó chuyển qua Excell 2003 nó báo lỗi #NAME. Vậy có nghĩa là sao ???
Công thức tôi sử dụng như sau:

=COUNTIFS(E6:E105,">=7",E6:E105,"<9")

Mong các bạn giải thích giúp. Và nếu tôi muốn đếm các số nằm trong khoảng đó thì phải dùng hàm gì (với excell 2003). Chân thành cảm ơn.

Với yêu cầu này trong E2003 thì mình dùng ngay DCOUNT() là xong ngay tấp lự;
Bạn xem thêm trong file đính kèm!
À mà bạn đếm hay tìm tổng vậy?
Mình thấy câu: " để đếm số điểm . . . " sao nó tối nghĩa quá; hay trong nghề dùng vậy; Có gì xin lỗi, khí không phải!!!

 

[Blad01]

Với yêu cầu này trong E2003 thì mình dùng ngay DCOUNT() là xong ngay tấp lự;
Bạn xem thêm trong file đính kèm!
À mà bạn đếm hay tìm tổng vậy?
Mình thấy câu: " để đếm số điểm . . . " sao nó tối nghĩa quá; hay trong nghề dùng vậy; Có gì xin lỗi, khí không phải!!!

"Đếm số điểm" nghĩa là có 5 điểm 9 chẳng hạn thì sẽ xây dựng công thức sao cho nó hiểu rằng có 5 điểm 9.

 

[kieuhunglc]

Như vậy trong mọi trường hợp để bảo đảm an toàn thì cứ cho -- vào là được phải ko??

 

[cadafi]

Như vậy trong mọi trường hợp để bảo đảm an toàn thì cứ cho -- vào là được phải ko??

Chẳng lẽ sumproduct(A1:A10,B1:B10) mà cũng xài sumproduct(--(A1:A10),--(B1:B10)).
Tùy trường hợp mà dùng bạn à!

 

[DOSNET]

Như vậy trong mọi trường hợp để bảo đảm an toàn thì cứ cho -- vào là được phải ko??

"An toàn" là như nào, xem ra mình và Ca_dafi giải thích ở trên ko được bạn ngâm cứu. Câu hỏi của bạn nó giống như trường hợp cứ cho mỳ chính vào thức ăn là ngọt, vậy uống nước đường có nên cho mỳ chính vào không ?
- Khi hiểu ý nghĩa cách dùng và công thức mảng thì bạn sẽ biết lúc nào cần dùng, lúc nào không !

 

[haonlh]

Bỗng đọc được đề tài này, thấy hay. Xin có 1 vài bình luận
- Tôi có cảm giác giá trị logic TRUE và FALSE là tương ứng với 1 và 0. Ví dụ =3 + (5>1) cho KQ là 4. Nhưng chỉ là số khi nó tham gia phép toán.
- Hàm SUMPRODUCT coi tham số có 2 giá trị logic này là Text, coi là 0, dù là TRUE hay FALSE.
Ví dụ đếm số điểm trung bình trong vùng A1:A10 (từ 5 đến < 7)
=SUMPRODUCT(($A$1:$A$10>=5),($A$1:$A$10<7)) cho KQ là 0.
(hãy thử xem, SUMPRODUCT(v1, v2) với v1, v2 cùng chiều, lẫn Text thì hàm coi Text là số 0).
- Nếu ta đưa vào dấu phép toán thì những giá trị logic này bị "ép kiểu" (cách nói của nhiều giáo sư tin học, có lẽ vậy) thì kết quả lại đúng. Vấn ví dụ trên, dùng dấu - là dấu phép toán đổi dấu nên TRUE ép thành -1, FALSE ép thành 0, -- là đổi dấu 2 lần:
=SUMPRODUCT(--($A$1:$A$10>=5),--($A$1:$A$10<7))
Và nếu tiết kiệm hơn (làm tin học, 2 anh đều thành công nhưng anh lười thì thành công hơn) chỉ cần 1 lần đổi dấu như sau:
=SUMPRODUCT(-($A$1:$A$10>=5),-($A$1:$A$10<7))
Cảm ơn vì đã xem và quấy quả.

 

[cadafi]

Bỗng đọc được đề tài này, thấy hay. Xin có 1 vài bình luận

- Tôi có cảm giác giá trị logic TRUE và FALSE là tương ứng với 1 và 0. Ví dụ =3 + (5>1) cho KQ là 4. Nhưng chỉ là số khi nó tham gia phép toán.

- Hàm SUMPRODUCT coi tham số có 2 giá trị logic này là Text, coi là 0, dù là TRUE hay FALSE.
Ví dụ đếm số điểm trung bình trong vùng A1:A10 (từ 5 đến < 7)

=SUMPRODUCT(($A$1:$A$10>=5),($A$1:$A$10<7)) cho KQ là 0.

(hãy thử xem, SUMPRODUCT(v1, v2) với v1, v2 cùng chiều, lẫn Text thì hàm coi Text là số 0).

- Nếu ta đưa vào dấu phép toán thì những giá trị logic này bị "ép kiểu" (cách nói của nhiều giáo sư tin học, có lẽ vậy) thì kết quả lại đúng.

Vấn ví dụ trên, dùng dấu - là dấu phép toán đổi dấu nên TRUE ép thành -1, FALSE ép thành 0, -- là đổi dấu 2 lần:

=SUMPRODUCT(--($A$1:$A$10>=5),--($A$1:$A$10<7))

Và nếu tiết kiệm hơn (làm tin học, 2 anh đều thành công nhưng anh lười thì thành công hơn) chỉ cần 1 lần đổi dấu như sau:

=SUMPRODUCT(-($A$1:$A$10>=5),-($A$1:$A$10<7))

Chào bạn,
Thực tế mà nói, công thức chỉ có một dấu trừ của bạn chưa tổng quát. Vì nếu tôi thêm một điều kiện nữa vào hàm sumproduct thì kết quả cho ra số âm hay sao.
Lấy ví dụ:

=SUMPRODUCT(-($A$1:$A$10>=5),-($A$1:$A$10<7),-($B$1:$B$10="K"))

Nói cách khác, diễn giải của bạn chỉ đúng khi số điều kiện trong sumproduct của bạn là chẵn, vì -1*-1=1.

Khi số điều kiện là số lẻ thì công thức của bạn không còn đúng nữa vì -1*-1*-1=-1

Tuy nhiên, cảm ơn vì bạn đã hiểu ra được cái dấu --.

 

[tubu16]

thân chào các bạn^^
Tôi đang tìm tài liệu về hàm sumproduct, và đã tìm được topic này nhưng ko biết nó còn hoạt động ko>< nếu còn, mong bạn nào hiểu thì giải thích giùm tôi : tại sao ý nghĩa của hàm này là tiính tổng của tích mà nó lại dùng để đếm được , có thể nói cho tôi biết nguyên lý khi nó dùng để đếm ko? tôi đã đọc topic này nhưg vẫn ko thể hiểu nổi vãn đề của mình . Mong các bạn giúp đỡ..
Cảm ơn các bạn!!!!!!!

 

[huuthanhdtd]

Mình xin mạo muội trả lời bạn tubu16 như thế này:
Ví dụ bạn có 2 cột:
Cột A1:A10 tương ứng nhập giá trị từ 1 đến 10
Cột B1:B10 cũng tương ứng nhập giá trị từ 1 đến 10
Ở ô C1 nếu bạn dùng công thức: =SUMPRODUCT(A1:A10,B1:B10)
sẽ cho kết quả là 385 = Tổng các tích hai mảng {1,2,3,4,5,6,7,8,9,10} và {1,2,3,4,5,6,7,8,9,10}
Nhưng ở ô C2 nếu dùng công thức: =SUMPRODUCT((A1:A10>=9)*(B1:B10>=9))
sẽ cho kết quả là 2
Điều này có nghĩa là hàm Sumproduct tính tổng các tích hai mảng {0,0,0,0,0,0,0,0,1,1} và {0,0,0,0,0,0,0,0,1,1}
chứ không phải hai mảng {1,2,3,4,5,6,7,8,9,10} và {1,2,3,4,5,6,7,8,9,10}
Chú ý trongmảng {0,0,0,0,0,0,0,0,1,1} thì 0 = FALSE và 1 = TRUE
Đây là kết quả so sánh lần lượt từng số {1,2,3,4,5,6,7,8,9,10} >= 9 => {0,0,0,0,0,0,0,0,1,1}

Như vậy hàm =SUMPRODUCT((A1:A10>=9)*(B1:B10>=9)) sẽ giống như đếm các số lớn hơn hoặc bằng 9 trong cả hai mảng A1:A10 và B1:B10
Hi vọng bạn sẽ hiểu

 

[Hoàng Trọng Nghĩa]

Có ai đó nói hàm SUMIF cũng giống hàm SUMPRODUCT với 1 điều kiện, điều này đúng hay sai?

Tôi cho rằng CHƯA CHẮC.

Nếu không dùng hàm khác hỗ trợ thì làm sao cộng được điều kiện này:

Cột A:

Nghia
Trong Nghia
Hoang Trong Nghia

Cột B:

1
2
3

Với SUMIF: =SUMIF(A1:A3,"*NGHIA*",B1:B3) ==> Kết quả: 6

Với SUMPRODUCT: =SUMPRODUCT((A1:A3="*NGHIA*")*(B1:B3)) ==> Kết quả: 0


Vậy làm sao để cả 2 kết quả bằng nhau?

 

[Hoàng Trọng Nghĩa]

Có ai đó nói hàm SUMIF cũng giống hàm SUMPRODUCT với 1 điều kiện, điều này đúng hay sai?

Tôi cho rằng CHƯA CHẮC.

Nếu không dùng hàm khác hỗ trợ thì làm sao cộng được điều kiện này:

Cột A:

Nghia
Trong Nghia
Hoang Trong Nghia

Cột B:

1
2
3

Với SUMIF: =SUMIF(A1:A3,"*NGHIA*",B1:B3) ==> Kết quả: 6

Với SUMPRODUCT: =SUMPRODUCT((A1:A3="*NGHIA*")*(B1:B3)) ==> Kết quả: 0


Vậy làm sao để cả 2 kết quả bằng nhau?

Có ai biết trong hàm của Excel có hàm nào giống hàm LIKE như VBA hay không? Xin cám ơn.

Nếu dùng nhiều điều kiện để SUM mà gặp trường hợp *xxx* thì không biết phải SUM như thế nào với SUMPRODUCT hết!

 

[bebo021999]

Có ai biết trong hàm của Excel có hàm nào giống hàm LIKE như VBA hay không? Xin cám ơn.

Nếu dùng nhiều điều kiện để SUM mà gặp trường hợp *xxx* thì không biết phải SUM như thế nào với SUMPRODUCT hết!

Có hàm SEARCH hoặc FIND
=SUMPRODUCT(ISNUMBER(SEARCH("Nghia",A1:A3))*B1:B3)

 

[ndu96081631]

Nếu dùng nhiều điều kiện để SUM mà gặp trường hợp *xxx* thì không biết phải SUM như thế nào với SUMPRODUCT hết!

Bạn cho 1 ví dụ thử xem

Có ai biết trong hàm của Excel có hàm nào giống hàm LIKE như VBA hay không? Xin cám ơn.

LIKE là 1 TOÁN TỬ, hổng phải hàm gì cả
Trong công thức Excel, nếu bác Bill xây dựng hàm mà dùng đến toán tử này thì ta có thể dùng ký tự đại diện, ngược lại thì không