=COUNTA(UNIQUE(A2:A9)) đếm có bao nhiêu giá trị khác nhau trong A2:A9. UNIQUE trả về mỗi giá trị một lần, và COUNTA đếm danh sách đó. Công thức cần Excel 2021 hoặc Microsoft 365; cách làm cho các phiên bản cũ hơn có ở bên dưới.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Unique list | Count | |
| 2 | Ana | Ana | 5 | |
| 3 | Ben | Ben | ||
| 4 | Ana | Cara | ||
| 5 | Cara | Dan | ||
| 6 | Ben | Eva | ||
| 7 | Dan | |||
| 8 | Ana | |||
| 9 | Eva |
Tám đơn hàng đến từ năm khách hàng. C2 trả ra (spill) danh sách tên từ UNIQUE để bạn thấy những gì đang được đếm, còn D2 đếm danh sách đó mà không cần nó nằm trên trang tính. Đổi A9 thành Ana thì kết quả giảm còn 4; nhập một tên mới thì kết quả tăng lên.
UNIQUE không phân biệt chữ hoa chữ thường, nên Ana và ana được tính là một khách hàng.
Đếm giá trị duy nhất trong Excel đời cũ
Excel 2019 trở về trước không có UNIQUE. Công thức kinh điển là:
=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))
Khi COUNTIF nhận cả vùng làm điều kiện, nó trả về, cho từng hàng, số lần giá trị của hàng đó xuất hiện. Một tên xuất hiện 3 lần sẽ nhận 3 ở mỗi hàng của nó, nên 1/3 được cộng ba lần và tên đó cộng lại vừa đúng 1. Cột B hiện số lần cho từng hàng và cột C hiện phân số.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Times | 1/Times | Count | |
| 2 | Ana | 3 | 0.33 | 5 | |
| 3 | Ben | 2 | 0.50 | 5.00 | |
| 4 | Ana | 3 | 0.33 | ||
| 5 | Cara | 1 | 1.00 | ||
| 6 | Ben | 2 | 0.50 | ||
| 7 | Dan | 1 | 1.00 | ||
| 8 | Ana | 3 | 0.33 | ||
| 9 | Eva | 1 | 1.00 |
Ba hàng của Ana mỗi hàng cộng 0,33, hai hàng của Ben mỗi hàng cộng 0,50, và ba tên chỉ có một lần mỗi tên cộng 1: tổng là 5, bằng với SUM của cột phụ. Với hàng chục nghìn hàng, công thức này chạy chậm vì COUNTIF quét cả vùng một lần cho mỗi hàng; UNIQUE không tốn chi phí đó.
Giá trị khác nhau và giá trị duy nhất: giá trị chỉ xuất hiện một lần
Từ "duy nhất" được dùng cho hai cách đếm khác nhau. Cách ở trên đếm các giá trị khác nhau: mỗi tên một lần. Cách còn lại đếm các giá trị xuất hiện đúng một lần, ví dụ những khách hàng chỉ đặt hàng một lần. UNIQUE làm được việc này khi tham số thứ ba, exactly_once, được đặt thành TRUE.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Count | Result | |
| 2 | Ana | Distinct | 5 | |
| 3 | Ben | Exactly once | 3 | |
| 4 | Ana | Exactly once, older Excel | 3 | |
| 5 | Cara | |||
| 6 | Ben | |||
| 7 | Dan | |||
| 8 | Ana | |||
| 9 | Eva |
Có năm khách hàng khác nhau, nhưng chỉ ba người trong số đó, Cara, Dan và Eva, đặt hàng một lần. Phiên bản cho Excel đời cũ đếm các hàng có COUNTIF đúng bằng 1. Nếu mọi giá trị đều lặp lại, UNIQUE với exactly_once trả về #CALC! và COUNTA tính lỗi đó là 1; phiên bản SUMPRODUCT cho kết quả 0.
Đếm giá trị duy nhất theo điều kiện
Để đếm số khách hàng khác nhau trong một khu vực, hãy lọc các hàng trước rồi đếm phần còn lại. FILTER giữ lại các hàng North và UNIQUE loại bỏ các giá trị lặp.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Region | Region | Customers | |
| 2 | Ana | North | North | 3 | |
| 3 | Ben | South | South | 3 | |
| 4 | Ana | North | North, older Excel | 3 | |
| 5 | Cara | North | |||
| 6 | Ben | North | |||
| 7 | Dan | South | |||
| 8 | Ana | North | |||
| 9 | Eva | South |
North có năm đơn hàng từ ba khách hàng: Ana, Cara và Ben. E3 đếm South theo cùng cách. E4 là phiên bản cho Excel 2019 trở về trước: COUNTIFS đếm từng cặp khách hàng và khu vực, còn điều kiện chỉ giữ lại các phân số của North.
Nếu không có hàng nào khớp, FILTER trả về #CALC!, và COUNTA tính lỗi đó là một giá trị: nhập West vào D2 thì E2 hiện 1 chứ không phải 0. Bọc công thức trong IFERROR cũng không giúp được gì, vì COUNTA không trả về lỗi. Thay vào đó, hãy đếm số hàng của kết quả, vì cách này có truyền lỗi ra ngoài: =IFERROR(ROWS(UNIQUE(FILTER(A2:A9,B2:B9="West"))),0) trả về 0.
Đếm giá trị duy nhất và bỏ qua ô trống
Một ô trống trong vùng sẽ thành thêm một "giá trị". UNIQUE trả nó về dưới dạng số 0 và COUNTA đếm số 0 đó, nên với Ana, một ô trống, Ben, Ana, một ô trống, Cara và Ben, Excel cho:
=COUNTA(UNIQUE(A2:A8)) 4 three names plus the 0 for the empty cells
Trong công thức đời cũ, một hàng trống khiến COUNTIF trả về 0, nên 1/0 cho lỗi #DIV/0!. Hãy loại ô trống trước:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Formula | Count | |
| 2 | Ana | Skip blanks | 3 | |
| 3 | Older Excel | 3 | ||
| 4 | Ben | |||
| 5 | Ana | |||
| 6 | ||||
| 7 | Cara | |||
| 8 | Ben |
Cả hai công thức đều đếm được ba khách hàng. FILTER với A2:A8<>"" loại các ô trống trước khi UNIQUE nhìn thấy chúng. Trong công thức đời cũ, A2:A8&"" biến mỗi ô trống thành một chuỗi rỗng để COUNTIF không bao giờ trả về 0, còn (A2:A8<>"") gán trọng số 0 cho những hàng đó.
Luyện tập: đếm số sản phẩm
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Count | Result | |
| 2 | 1001 | Apple | Products | ||
| 3 | 1002 | Pear | |||
| 4 | 1003 | Apple | |||
| 5 | 1004 | Plum | |||
| 6 | 1005 | Pear | |||
| 7 | 1006 | Apple | |||
| 8 | 1007 | Plum | |||
| 9 | 1008 | Fig |
Đến lượt bạn: Đếm xem có bao nhiêu sản phẩm khác nhau trong B2:B9. Viết công thức vào E2.
Dùng công thức nào cho phiên bản Excel của bạn
| Đếm | Excel 365 / 2021 | Excel 2019 trở về trước |
|---|---|---|
| Giá trị khác nhau | =COUNTA(UNIQUE(A2:A9)) | =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)) |
| Giá trị xuất hiện một lần | =COUNTA(UNIQUE(A2:A9,,TRUE)) | =SUMPRODUCT(--(COUNTIF(A2:A9,A2:A9)=1)) |
| Giá trị khác nhau, có điều kiện | =COUNTA(UNIQUE(FILTER(A2:A9,B2:B9="North"))) | =SUMPRODUCT((B2:B9="North")/COUNTIFS(A2:A9,A2:A9,B2:B9,B2:B9)) |
| Giá trị khác nhau, bỏ qua ô trống | =COUNTA(UNIQUE(FILTER(A2:A9,A2:A9<>""))) | =SUMPRODUCT((A2:A9<>"")/COUNTIF(A2:A9,A2:A9&"")) |
Trong Pivot Table, kiểu tổng hợp Distinct Count làm được việc tương tự mà không cần công thức, nhưng chỉ khi Pivot Table được tạo với tùy chọn "Add this data to the Data Model" được đánh dấu. Để xóa các giá trị lặp thay vì đếm chúng, xem xóa dữ liệu trùng lặp.
Câu hỏi thường gặp
Làm sao đếm giá trị duy nhất trong Excel?
Trong Excel 365 hoặc 2021, dùng =COUNTA(UNIQUE(A2:A9)): UNIQUE liệt kê mỗi giá trị một lần và COUNTA đếm danh sách đó. Với các phiên bản cũ hơn, dùng =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)).
Làm sao đếm các giá trị chỉ xuất hiện một lần?
Đặt tham số thứ ba của UNIQUE, exactly_once, thành TRUE: =COUNTA(UNIQUE(A2:A9,,TRUE)). Với Ana, Ana, Ben, kết quả là 1 vì chỉ Ben xuất hiện một lần. Trong Excel 2019 trở về trước, dùng =SUMPRODUCT(--(COUNTIF(A2:A9,A2:A9)=1)).
Làm sao đếm giá trị duy nhất theo điều kiện?
Lọc trước rồi đếm: =COUNTA(UNIQUE(FILTER(A2:A9,B2:B9="North"))) đếm số khách hàng khác nhau trong các hàng North. Nếu không có hàng nào khớp, COUNTA tính lỗi #CALC! của FILTER là 1, nên khi có thể xảy ra trường hợp đó, hãy dùng =IFERROR(ROWS(UNIQUE(FILTER(A2:A9,B2:B9="North"))),0).
Làm sao đếm giá trị duy nhất và bỏ qua ô trống?
Loại ô trống trước khi dùng UNIQUE: =COUNTA(UNIQUE(FILTER(A2:A9,A2:A9<>""))). Trong Excel đời cũ, =SUMPRODUCT((A2:A9<>"")/COUNTIF(A2:A9,A2:A9&"")) sẽ bỏ qua chúng.
