=COUNTA(UNIQUE(A2:A9)) menghitung berapa banyak nilai berbeda di A2:A9. UNIQUE menghasilkan setiap nilai satu kali, lalu COUNTA menghitung daftar itu. Rumus ini butuh Excel 2021 atau Microsoft 365; versi yang lebih lama dibahas di bawah.
| 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 |
Delapan pesanan datang dari lima pelanggan. C2 menampilkan daftar nama hasil UNIQUE sebagai spill agar kamu bisa melihat apa yang dihitung, dan D2 menghitungnya tanpa perlu menaruh daftar itu di lembar kerja. Ubah A9 menjadi Ana dan hitungannya turun menjadi 4; ketik nama baru dan hitungannya naik.
UNIQUE mengabaikan huruf besar kecil, jadi Ana dan ana dihitung sebagai satu pelanggan.
Menghitung nilai unik di Excel versi lama
Excel 2019 dan sebelumnya tidak punya UNIQUE. Rumus klasiknya adalah:
=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))
COUNTIF dengan seluruh rentang sebagai kriterianya menghasilkan, untuk setiap baris, berapa kali nilai di baris itu muncul. Nama yang muncul 3 kali mendapat angka 3 di setiap barisnya, jadi 1/3 dijumlahkan tiga kali dan nama itu totalnya tepat 1. Kolom B menampilkan hitungan untuk setiap baris dan kolom C pecahannya.
| 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 |
Ketiga baris Ana masing-masing menambahkan 0,33, kedua baris Ben masing-masing menambahkan 0,50, dan tiga nama yang muncul sekali masing-masing menambahkan 1: totalnya 5, sama dengan SUM dari kolom bantu. Untuk puluhan ribu baris, rumus ini lambat, karena COUNTIF memindai seluruh rentang satu kali untuk setiap baris; UNIQUE tidak punya beban seperti itu.
Distinct vs unik: nilai yang hanya muncul sekali
Kata "unik" dipakai untuk dua hitungan yang berbeda. Hitungan di atas menghitung nilai distinct: setiap nama satu kali. Yang lainnya menghitung nilai yang muncul tepat satu kali, misalnya pelanggan yang hanya memesan satu kali. UNIQUE melakukannya dengan argumen ketiganya, exactly_once, diisi 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 |
Ada lima pelanggan yang berbeda, tetapi hanya tiga di antaranya, Cara, Dan, dan Eva, yang memesan satu kali. Versi untuk Excel lama menghitung baris yang hasil COUNTIF-nya tepat 1. Kalau setiap nilai muncul berulang, UNIQUE dengan exactly_once menghasilkan #CALC! dan COUNTA menghitung error itu sebagai 1; versi SUMPRODUCT menghasilkan 0.
Menghitung nilai unik dengan kondisi
Untuk menghitung pelanggan yang berbeda di satu wilayah, saring barisnya dulu lalu hitung yang tersisa. FILTER mempertahankan baris North dan UNIQUE membuang pengulangannya.
| 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 punya lima pesanan dari tiga pelanggan: Ana, Cara, dan Ben. E3 menghitung South dengan cara yang sama. E4 adalah versi untuk Excel 2019 dan sebelumnya: COUNTIFS menghitung setiap pasangan pelanggan dan wilayah, lalu kondisinya hanya mempertahankan pecahan untuk North.
Kalau tidak ada baris yang cocok, FILTER menghasilkan #CALC!, dan COUNTA menghitung error itu sebagai satu nilai: ketik West di D2 dan E2 menampilkan 1, bukan 0. Membungkus rumusnya dengan IFERROR tidak membantu, karena COUNTA tidak menghasilkan error. Hitung jumlah baris hasilnya saja, yang memang meneruskan error itu: =IFERROR(ROWS(UNIQUE(FILTER(A2:A9,B2:B9="West"))),0) menghasilkan 0.
Menghitung nilai unik tanpa sel kosong
Sel kosong di dalam rentang menjadi satu "nilai" tambahan. UNIQUE mengembalikannya sebagai 0 dan COUNTA menghitung 0 itu, jadi untuk Ana, sel kosong, Ben, Ana, sel kosong, Cara, dan Ben, Excel menghasilkan:
=COUNTA(UNIQUE(A2:A8)) 4 three names plus the 0 for the empty cells
Di rumus versi lama, baris kosong membuat COUNTIF menghasilkan 0, jadi 1/0 menghasilkan #DIV/0!. Buang dulu sel kosongnya:
| 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 |
Kedua rumus menghitung tiga pelanggan. FILTER dengan A2:A8<>"" membuang sel kosong sebelum sampai ke UNIQUE. Di rumus versi lama, A2:A8&"" mengubah setiap sel kosong menjadi string kosong sehingga COUNTIF tidak pernah menghasilkan 0, dan (A2:A8<>"") memberi bobot 0 untuk baris-baris itu.
Latihan: hitung produknya
| 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 |
Giliranmu: Hitung berapa banyak produk berbeda yang muncul di B2:B9. Tulis rumusnya di E2.
Rumus mana untuk versi Excel-mu
| Hitungan | Excel 365 / 2021 | Excel 2019 dan sebelumnya |
|---|---|---|
| Nilai distinct | =COUNTA(UNIQUE(A2:A9)) | =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)) |
| Nilai yang muncul sekali | =COUNTA(UNIQUE(A2:A9,,TRUE)) | =SUMPRODUCT(--(COUNTIF(A2:A9,A2:A9)=1)) |
| Distinct, dengan kondisi | =COUNTA(UNIQUE(FILTER(A2:A9,B2:B9="North"))) | =SUMPRODUCT((B2:B9="North")/COUNTIFS(A2:A9,A2:A9,B2:B9,B2:B9)) |
| Distinct, tanpa sel kosong | =COUNTA(UNIQUE(FILTER(A2:A9,A2:A9<>""))) | =SUMPRODUCT((A2:A9<>"")/COUNTIF(A2:A9,A2:A9&"")) |
Di pivot table, ringkasan Distinct Count melakukan pekerjaan yang sama tanpa rumus, tetapi hanya kalau pivot table-nya dibuat dengan opsi "Add this data to the Data Model" dicentang. Untuk menghapus pengulangannya, bukan menghitungnya, lihat menghapus duplikat.
Pertanyaan yang sering diajukan
Bagaimana cara menghitung nilai unik di Excel?
Di Excel 365 atau 2021, pakai =COUNTA(UNIQUE(A2:A9)): UNIQUE menampilkan setiap nilai satu kali, lalu COUNTA menghitung daftar itu. Di versi lama, pakai =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)).
Bagaimana cara menghitung nilai yang hanya muncul satu kali?
Isi argumen ketiga UNIQUE, exactly_once, dengan TRUE: =COUNTA(UNIQUE(A2:A9,,TRUE)). Untuk Ana, Ana, Ben hasilnya 1, karena hanya Ben yang muncul satu kali. Di Excel 2019 dan sebelumnya, pakai =SUMPRODUCT(--(COUNTIF(A2:A9,A2:A9)=1)).
Bagaimana cara menghitung nilai unik dengan kondisi?
Saring dulu, lalu hitung: =COUNTA(UNIQUE(FILTER(A2:A9,B2:B9="North"))) menghitung pelanggan yang berbeda di baris North. Kalau tidak ada baris yang cocok, COUNTA menghitung error #CALC! dari FILTER sebagai 1, jadi kalau itu bisa terjadi, pakai =IFERROR(ROWS(UNIQUE(FILTER(A2:A9,B2:B9="North"))),0).
Bagaimana cara menghitung nilai unik tanpa menyertakan sel kosong?
Buang sel kosongnya sebelum UNIQUE: =COUNTA(UNIQUE(FILTER(A2:A9,A2:A9<>""))). Di Excel versi lama, =SUMPRODUCT((A2:A9<>"")/COUNTIF(A2:A9,A2:A9&"")) melewatinya.
