Đếm bỏ qua các giá trị trùng nhau.

Chia sẻ bởi:hands

★★★★★

‹ tạo dự đoán nhập trong combobox bằng... Dùng hàm nào để đếm số giá... ›

Quảng cáo

Em là người mới học excel nên gặp vấn đề khó khăn nhờ các pro giúp dùm. Em có một cột với các giá trị sau:
VD:
Cột A:

A
B
C
A
E

Nếu dùng hàm COUNT thông thường thì nó sẽ ra là 5, em muốn nó chỉ đếm là 4 (tức là các giá trị có rồi thì không đếm nữa), có pro nào biết giúp dùm em với.
Thanks with best regard.

cái này nói không biết bao nhiêu lần trên diển đàn rồi, bạn phải search trước khi hỏi chứ ([URL='https://www.google.com.vn/custom?domains=www.giaiphapexcel.com&q=%C4%90%E1%BA%BFm+kh%C3%B4ng+tr%C3%B9ng&sitesearch=www.giaiphapexcel.com']Đếm không trùng)
Công thức tổng quát là:
=SUMPRODUCT(1/COUNTIF(Vung,Vung))
Với Vung là địa chỉ vùng chứ dử liệu, ví dụ:
=SUMPRODUCT(1/COUNTIF(A1:A7,A1:A7))

www.giaiphapexcel.com/diendan/threads/%C4%90%E1%BA%BFm-b%E1%BB%8F-qua-c%C3%A1c-gi%C3%A1-tr%E1%BB%8B-tr%C3%B9ng-nhau.22869/

thầy cho em hỏi là nếu trong vùng điều kiện mà có ô trắng thì mình dùng công thức nào ạ, vì dùng công thức trên bị lỗi ạ lại hiện ra #DIV/0!, em cảm ơn thầy ah

Hàm này khi trong vùng có giá trị rỗng sẽ bị lỗi. Mình có hàm này vẫn đếm được và bỏ qua ô rỗng
SUMPRODUCT((Vung<>"")/COUNTIF(Vung,Vung&""))

Bạn thử:
=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

Khóa học SprinGO phù hợp

Xây dựng Lương 3P, KPI cho Doanh nghiệp

Làm thế nào để trả lương cho nhân viên chính xác nhất? Đây là một trong những câu hỏi khó trong quản trị nhân...

Xem khóa học

★★★★★ 5 ★ 1 👤 10 ▥ 0

‹ tạo dự đoán nhập trong combobox bằng userform Dùng hàm nào để đếm số giá trị... ›

Quảng cáo

Bạn nên đọc

10 Responses

hands says:

06/01/2023 at 1:29 pm

Em mới học excel. Hàm đếm các bác nói không hiểu rõ lắm, mong các bác chỉ thêm.
Mình có file này, bác nào giúp với
```
c16=sumproduct((month($a$3:$a$10)=b16)*($b$3:$b$10=a16))
```
xin hỏi tôi có 1 bài toán như sau:
Đếm số khách hàng có Lai suat <=10% và >=9, có kỳ hạn: "Ngan han".
Rất mong được giúp đỡ.
1633
Mình đã dùng hàm CountIfs() nhưng nó chưa lọc được KH trùng nhau.
=COUNTIFS(B2:B11,">=9%",B2:B11,"<=10%",D2:D11,"Ngan han") = 4
Kết quả chính xác phải là 3
```
=SUMPRODUCT((B$1:B$11>=9%)*(B$1:B$11<=10%)*(D$1:D$11="Ngan han")*(MATCH(A$1:A$11&B$1:B$11,A$1:A$11&B$1:B$11,0)=ROW($1:$11)))
```
Mình copy công thức trên vào bảng tính nhưng kết quả vẫn ra là 4 bạn ạ.
Kết quả đúng phải là 3

Sửa lại Công thức của bạn [URL='https://www.giaiphapexcel.com/forum/member.php?895544-nginh'%5Dnginh chút. Bạn bỏ &B$1:B$11 đi xem thế nào?
```
=SUMPRODUCT((B$1:B$11>=9%)*(B$1:B$11<=10%)*(D$1:D$11="Ngan han")*(MATCH(A$1:A$11,A$1:A$11,0)=ROW($1:$11)))
```
nếu có cell rổng thì sao bạn?
ví dụ delete cái cell A9 đi…………….hihihih

Công thức này thực chất là của bạn [URL="https://www.giaiphapexcel.com/forum/member.php?895544-nginh"%5Dnginh, mình chỉ sửa chút xíu để đúng yêu cầu của chủ Top. Nếu có ô rỗng có lẽ thế này là ổn:
```
=SUMPRODUCT((B$1:B$11>=9%)*(B$1:B$11<=10%)*(D$1:D$11="Ngan han")*(MATCH(A$1:A$11&"",A$1:A$11&"",0)=ROW($1:$11)))
```
Cảm ơn bạn đã góp ý.

Reply
hands says:

06/01/2023 at 1:41 pm
cái này nói không biết bao nhiêu lần trên diển đàn rồi, bạn phải search trước khi hỏi chứ ([URL='https://www.google.com.vn/custom?domains=www.giaiphapexcel.com&q=%C4%90%E1%BA%BFm+kh%C3%B4ng+tr%C3%B9ng&sitesearch=www.giaiphapexcel.com'%5DĐếm không trùng)
Công thức tổng quát là:
=SUMPRODUCT(1/COUNTIF(Vung,Vung))
Với Vung là địa chỉ vùng chứ dử liệu, ví dụ:
=SUMPRODUCT(1/COUNTIF(A1:A7,A1:A7))

Cám ơn thầy,

Thưa thầy em hỏi thêm một ý ạ, nếu trường hợp dữ liệu của em không phải khi nào cũng fix ở dòng 7 nó biến đổi nên em muốn dùng công thức này và cho dòng lớn hơn một chút nhưng lỗi bởi vì có thể các dòng sau cùng không có dữ liệu gì ạ. Thầy có giải pháp nào không ạ.
Ví dụ em đang để ở file em là:
```
[B]=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))[/B]
```
Cám ơn thầy nhiều,
em Khoi
Các bạn cho mình hỏi làm thế nào để Đếm số ô có giá trị và giá trị đó không trùng nhau

VD:
Cột A

A
B
C
(ô trống)
A
E

bạn thử công thức sau
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

Nhờ tư vấn về excell

Mình có một danh sách nhân viên đi công tác ở các nước gồm: Họ tên, tên nước

Mình muốn bảng tổng hợp phải tổng hợp được Mỗi người đi được vao nhiêu lần và đi được bao nhiêu nước (những nước đi nhiều lần thì chỉ tính 1)

Nhờ các Pro giúp đỡ với a.
em rất yếu về môn này.

DỮ LiỆU THÔ (ĐÃ SORT)

BẢNG TỔNG HỢP

STT
Họ tên
Đi công tác tại
nước:

STT
Họ tên
Số lần đi Nước ngoài
Số nước (các nước trùng thì chỉ tính 1)
Ghi chú

1
Nguyen Van A
Thái lan

1
Nguyen Van A
5
3
(Thái lan, Lào, Cămpudia)

Nguyen Van A
Lao

2
Vo Thi B
6
4
(Nhật, Hàn quốc, Đức, Mỹ)

Nguyen Van A
Campudia

Nguyen Van A
Thai lan

Nhờ Anh Chị cho công thức tại cột I với ạ.

Nguyen Van A
Lao

Thanks

2
Vo Thi B
Nhat

Vo Thi B
Han quoc

Vo Thi B
Đức

Vo Thi B
Mỹ

Vo Thi B
Nhat

Vo Thi B
Nhat

thử với công thức sau nhé I11=SUMPRODUCT(($B$11:$B$21=G11)*(1/COUNTIF($C$11:$C$21,$C$11:$C$21)))

Cam on Nothing1988. Minh da thu nhung khi thay doi ten nuoc khac thi so luong lai sai a. Nhờ Pro ho tro them a.

Em có gửi lại file số liệu thực. Nhờ Anh xem giúp a.
Cảm ơn nhiều.

Áp dụng vào file thực tế kết quả sai thật. Để tìm công thức khác cho bạn
Có bạn nào giải thích hộ em. Tại sao khi với ít trường hợp công thức trên lại chính xác ko ạ?

Công thức kết thúc bằng 3 phím nha Ctrl+Shift+Enter
=COUNT(1/FREQUENCY(IF('Theo ten'!$I$7:$I$176=$B10,MATCH('Theo ten'!$F$7:$F$176,'Theo ten'!$F$7:$F$176,0)),IF('Theo ten'!$I$7:$I$176=$B10,MATCH('Theo ten'!$F$7:$F$176,'Theo ten'!$F$7:$F$176,0))))

Tôi phát hiện được thêm 1 điều này. sửa công thức #22 thành
=SUMPRODUCT(('Theo ten'!$I$6:$I$175=B10)*(1/COUNTIFS('Theo ten'!$F$6:$F$175,'Theo ten'!$F$6:$F$175,'Theo ten'!$I$6:$I$175,'Theo ten'!$I$6:$I$175)))
Là có thể đếm được số nước đi

Reply
hands says:

06/01/2023 at 1:44 pm

Nhờ Pro chỉ giáo thêm cho phần này với ạ.

Việc là em có một bảng định mức NL cho các mặt hàng, và trải dài rất nhiều cột.

Muốn nó tự động gom lại : mội mặt hàng có định mức nguyên liêu (>0) thì gom lại thành 1 bảng tổng hợp, chứ không để tràn lan như bảng dữ liệu thô.

Em có kèm theo file ví dụ đính kèm.
Nhờ các Pro gíup đỡ với a.

Cảm ơn nhiều.
```
=IFERROR(INDEX($C$23:$O$23,SMALL(IF(ISNUMBER(OFFSET($C$23:$O$23,MATCH($B41,$B$24:$B$28,0),)),COLUMN($C$23:$O$23)-2),COLUMN()-2)),"")
```
cong thuc mang, nhớ nhấn Ctrl Shift Enter

Reply
hands says:

06/01/2023 at 1:46 pm

Hi bạn,
đại ý file mình muốn đếm số Part No của sheet "Result" (loại bỏ giá trị bị trùng) của ngày "2-Nov", line "LTE", lane "1", nhưng data của mình update hàng ngày nên mình muốn khi mình update raw data của ngày 3 thì ô Part No của ngày 3 cũng tự động nhảy số.
Công thức của mình đang ở ô E10

Thử:
```
D10=COUNT(1/(MATCH(Result!$G1:$G1000,IF((Result!$A$1:$A$1000=D$7)*(Result!$E$1:$E$1000=$E$4)*(Result!$F$1:$F$1000=$E$5),Result!$G$1:$G$1000),)=ROW($1:$1000)))
```
Kết thúc bằng Ctrl+Shift+Enter. Rồi fill qua phải.

Không nên dùng tham chiếu cả cột như D: D; E: E…. vì sẽ làm trì trệ máy, thay vì vậy bạn nên khống chế theo một mức biên tối đa có thể có của dữ liệu, Ví dụ: Result!$G1:$G1000 chẳng hạn.

Thân

Reply
hands says:

06/01/2023 at 1:47 pm
Em có dữ liệu của cột A1:A12 như sau:
```
DH-NAQ-2019-T4-19
DH-NAQ-2019-T4-19
DH-NAQ-2019-T4-18
DH-NAQ-2019-T4-17
DH-NAQ-2019-T4-17
DH-NAQ-2019-T4-17
DH-NAQ-2019-T4-17
DH-NAQ-2019-T4-16
DH-NAQ-2019-T4-15
DH-NAQ-2019-T3-01
DH-NAQ-2019-T3-02
DH-NAQ-2019-T3-02
```
Em muốn đếm xem có bao nhiêu mã T4 và ko bị trùng. Mọi người bớt chút thời gian chỉ giúp em với. Thanks
```
=SUMPRODUCT(1/COUNTIF($A$1:$A$12,$A$1:$A$12)*(ISNUMBER(SEARCH("T4",$A$1:$A$12))))
```
Tham khảo công thức:

Thử công thức cho cả trường hợp số dòng nhiều hơn, hoặc chứa dòng rỗng chen giữa dữ liệu:
```
=COUNT(1/(MATCH(A1:A100,IF(SEARCH("T4",A1:A100),A1:A100),)=ROW(1:100)))
```
Kết thúc bằng Ctrl+Shift+Enter.

Thân

Mọi người cho em hỏi nếu như em thêm 1 cột Date và điều kiện đếm thay đổi là: Đếm xem có bao nhiêu mã T4, Ko bị trùng và trong tháng 4.
Và ở cột ngày này sẽ có những dòng chưa được nhập ngày, vì chưa xác định được ngày.
Mọi người chỉ giúp em với nhé.
Trân trọng!

Giả sử cột B chứa ngày dạng dd/mm/yyyy, Nếu chọn tháng 04/2019 thêm vào công thức:
```
=COUNT(1/(MATCH(A1:A100,IF(SEARCH("T4",A1:A100)*(Text(B1:B100,"mm/yyyy")="04/2019"),A1:A100),)=ROW(1:100)))
```
Kết thúc bằng Ctrl+Shift+Enter.

Anh cho em hỏi cái phần = Row(1:100) có tác dụng gì vậy. Có cách nào để thu nhỏ vùng này không. Em sợ sau này dữ liệu em nhiều lên vài trăm hoặc nghìn dòng mà chọn hết cả 1 vùng lớn thế sẽ gây chậm file.
Các ô A1:A100 thì em sẽ thay bằng 1 cái name range động nên dữ liệu phát sinh đến đâu thì vùng đếm sẽ chạy đến đấy.

Dùng Name để tạo Vùng động co giản cũng là cách hay cho dữ liệu <1.000 dòng.

Với công thức COUNT(1/ <Điều kiện lọc dữ liệu trùng= ROW(1:100)> ), hàm ROW(1:100) là 1 Mảng với ý nghĩa chỉ tạo ra "số thứ tự dòng" từ 1 đến 100, nó không bị "lệ thuộc" vào số dòng bạn có thực là bao nhiêu, miễn sao nó bằng hoặc lớn hơn là được. Ví dụ: Nếu số dòng thực của bạn là 125 dòng thì bạn có thể tạo ra stt: ROW(1:125), hoặc ROW(1:150), hoặc ROW(1:200), tức số 125 (hoặc 150, hoặc 200)>=125.

Các công thức trên đều là công thức mảng (tính cho toàn bộ Vùng hiện hữu, rồi lưu và tính tiếp trong bộ nhớ), cho nên hễ dữ liệu lên hơn 1.000 dòng sẽ làm cho trì trệ máy. Khi buộc lòng phải xử lý dữ liệu trên 1.000 dòng, bạn nên nghĩ đến:
- Tạo cột phụ: để loại dữ liệu trùng theo như yêu cầu quy định.
- Dùng VBA. (Khả năng này tốt hơn với dữ liệu lớn, nhưng tôi lại không biết làm).
Vậy hén!

Thân

Anh ơi vậy dùng hàm Sumproduct có được không anh nhỉ, em thử kết hợp mà nó toàn báo lỗi. Thêm nữa phần Row có cách nào để nó cũng động đc ko anh.

Bạn đưa file báo lỗi lên đi, rồi hẵn hỏi!

Reply
hands says:

06/01/2023 at 1:48 pm

Hi các anh chị,

Mình là thành viên mới, đang gặp khó không biết xài công thức nào cho 1 bảng tính mà muốn lấy các giá trị trong một cột có cùng tham số? Mình dùng index+match nhưng chỉ ra 1 giá trị đầu tiên, còn các giá trị bên dưới ko ra dc. Mình có tham số (0143/2019) và mình muốn lấy ra các số HĐ (cái nào trùng chỉ lấy 1 lần ah). Mong anh chị có thể giúp đỡ mình xin cảm ơn ah !

SHIPMENT OUTBOUND NO.
OUBOUND DATE
IB JOBFILES NO.
BACK UP FILES
MATERIAL INBOUND DATE
MATERIAL NO.
CONTRACT NUMBER

0143/2019
7-May-19

176
6-Apr-19
X102116696
44KV/HĐKNQ.TMS.2019

0143/2019
7-May-19

176
6-Apr-19
X102116696
44KV/HĐKNQ.TMS.2019

0143/2019
7-May-19

176
6-Apr-19
X102116695
44KV/HĐKNQ.TMS.2019

0143/2019
7-May-19

176
6-Apr-19
LTSPCWO970
44KV/HĐKNQ.TMS.2019

0143/2019
7-May-19

178
6-Apr-19
LTSPCWO968
45KV/HĐKNQ.TMS.2019

0143/2019
7-May-19

178
6-Apr-19
LTSPCWO968
45KV/HĐKNQ.TMS.2019

0143/2019
7-May-19

178
6-Apr-19
X102116695
45KV/HĐKNQ.TMS.2019

0143/2019
7-May-19

178
6-Apr-19
X102116695
45KV/HĐKNQ.TMS.2019

0143/2019
7-May-19

179
6-Apr-19
X102199173
46KV/HĐKNQ.TMS.2019

0143/2019
7-May-19

179
6-Apr-19
LTSTE3T544
46KV/HĐKNQ.TMS.2019

Giả sử:
I2="0143/2019"
Thử:
```
J3=IFERROR(INDEX($G$3:$G$100,MATCH(,INDEX(COUNTIF($J$2:$J2,$G$3:$G$100)+($A$3:$A$100<>$I$2),),)),"")
```
Enter, fill xuống.

Thân

Reply
hands says:

06/01/2023 at 1:49 pm

cho mình hỏi cách để đếm ví dụ kepk WR189 có bao nhiêu số lượng và các mặt hàng khác cũng trùng nhau nhưng ko cần đếm tay ạ. em cám ơn các anh chị

Bạn xem thử file.

Reply
hands says:

06/01/2023 at 1:52 pm

Nhờ bạn chỉ dùm với ạ

Thêm điều kiện tí bạn à
=SUMPRODUCT((C2:C7<>"")/COUNTIFS(C2:C7,C2:C7&"",A2:A7,A2:A7&""))

giờ mình muốn đếm có điều kiện như trên, nhưng khi dùng nút filter nó vẫn ra kết quả ( bỏ qua giá trị lọc)
nhờ bạn giúp mình với ạ, thanks bạn

Phải vầy không!
```
=COUNT(1/(MATCH(A2:A7&C2:C7,IF(SUBTOTAL(103,OFFSET(B1,ROW(1:6),)),A2:A7&C2:C7),)=ROW(1:100)))
```
kết thúc bằng Ctrl+Shift+Enter.

Thân

Reply
hands says:

06/01/2023 at 1:53 pm

Mình có 1 bài toán thế này, trong cột "Số lượng" chỉ đếm 1 lần nếu giá trị bị trùng, còn những giá trị không bị trùng nhau thì vẫn đếm 1 lần
Mình không viết được công thức này, mong các bạn giúp đỡ
1644

Giả sử dữ liệu của bạn bắt đầu từ A2 đến A100, tại B2:
```
=IF(COUNTIF($A$2:$A$100,A2)=1,1,"")
```
cảm ơn bạn, nhưng chưa đúng ý mình lắm, nếu số trùng nhau thì vẫn được tính là 1(chỉ 1 lần xuất hiện thôi), còn những số trùng sau bằng trống

Reply
hands says:

06/01/2023 at 2:32 pm
cảm ơn bạn, nhưng chưa đúng ý mình lắm, nếu số trùng nhau thì vẫn được tính là 1(chỉ 1 lần xuất hiện thôi), còn những số trùng sau bằng trống

Bạn sửa thành như sau:
```
=IF(COUNTIF($A$2:A2,A2)=1,1,"")
```
=IF(COUNTIF($A$2:A2,A2)>1,"",1)

Bạn thử công thức này:

Nếu không có dòng trống em hay đếm như này ạ=counta(unique(vùng DL))

">=9%",B2:B11,"<=10%",)

Reply