Rumus Excel Untuk Menghitung Jumlah Data Unik

Rumus Excel Untuk Menghitung Jumlah Data Unik

Jika memiliki data yang mengandung data kembar (double/duplikat). Bagaimanakah rumus excel yang digunakan untuk mengetahui jumlah data uniknya (count unique value excel)?

Menghitung jumlah data unik berarti mencacah jumlah data, namun jika pada data tersebut memiliki duplikat hanya dihitung satu kali saja.

Data unik ini bisa saja berupa teks maupun angka, juga campuran dari keduanya.

Untuk tutorial mendapatkan list data uniknya bisa anda pelajari pada halaman: Rumus Excel Untuk Menghilangkan Data Yang Sama dan Mengambil Data Unik.

Salah satu cara untuk menghitung jumlah data unik dari sebuah range data pada excel adalah dengan menggunakan rumus-rumus excel berikut:

MENGHITUNG JUMLAH DATA UNIK


Untuk menghitung jumlah data unik bisa menggunakan rumus gabungan fungsi Sum dan Countif seperti berikut:

=SUM(1/COUNTIF(RangeData; RangeData))

Akhiri dengan Ctrl+Shift+Enter (Array Formula).

Menghitung Data Unik Dengan Fungsi SUM dan COUNTIF

Pada contoh diatas jumlah jenis buah pada range A2:A11 dihitung dengan rumus:

=SUM(1/COUNTIF(A2:A11;A2:A11))

Kemudian tekan Ctrl+Shift+Enter, sehingga rumus excel tersebut akan diapit tanda kurung ({...}) seperti yang terlihat di contoh.

Cara kerja rumus excel diatas adalah sebagai berikut:

1. Fungsi Countif akan menghitung jumlah masing-masing jenis buah pada range data:

Mangga = 1, Manggis = 3, Rambutan= 1, Sirsak = 1, Manggis = 3, Jeruk = 1, Apel = 2, Manggis= 3, Nanas = 1, Apel = 2

2. Sehingga menghasilkan angka berikut:
{1;3;1;1;3;1;2;3;1;2}

3. Angka diatas kemudian digunakan untuk membagi angka 1.
1/{1;3;1;1;3;1;2;3;1;2}

4. Setelah mendapatkan angka tersebut kemudian dijumlahkan dengan fungsi SUM
=SUM(1/1;1/3;1/1;1/1;1/3;1/1;1/2;1/3;1/1;1/2)

5. Sehingga menghasilkan jumlah unik jenis buah ada 7.

Penjelasan cara kerja rumus diatas sekaligus sebagai penjelasan untuk rumus-rumus excel berikutnya pada tutorial excel kali ini.

Selain menggunakan rumus array gabungan fungsi SUM dan COUNTIF diatas, untuk rumus non Array anda bisa menggunakan gabungan fungsi SUMPRODUCT dan COUNTIF berikut:

=SUMPRODUCT(1/COUNTIF(RangeData;RangeData))

Menghitung Data Unik Dengan Fungsi SUMPRODUCT dan COUNTIF

Pada contoh diatas rumus excel yang digunakan untuk menghitung jumlah jenis buah adalah:

=SUMPRODUCT(1/COUNTIF(A2:A11;A2:A11))

Cara kerja rumus diatas kurang lebih sama dengan cara kerja rumus sebelumnya.

Penjelasan lebih detail untuk masing-masing fungsi bisa anda baca pada laman berikut: Rumus SUM Excel, Rumus SUMPRODUCT Excel, Rumus COUNTIF Excel.

Jika data yang kita hitung jumlah nilai uniknya mengandung sel kosong (blank cells), ternyata kedua rumus excel diatas tidak bekerja sesuai harapan. Kedua rumus diatas akan menghasilkan pesan error #DIV/0! seperti pada gambar ini.

Menghitung Jumlah Data Unik Error Jika Ada Blank Cells

Penjelasan tentang jenis-jenis error bisa anda baca pada : Mengenal 7 Jenis Pesan Error pada Rumus Excel.

MENGHITUNG JUMLAH DATA UNIK YANG MENGANDUNG SEL KOSONG


Jika pada range data yang kita hitung nilai uniknya terdapat sel kosong (blank cells). Maka solusinya adalah dengan menambahkan (&"") pada kriteria fungsi Countif, sehingga rumus excelnya menjadi:

=SUM(1/COUNTIF(RangeData;RangeData&""))

Akhiri dengan Ctrl+Shift+Enter.

Untuk rumus non Array menjadi:

=SUMPRODUCT(1/COUNTIF(RangeData;RangeData&""))

Dan hasilnya seperti pada gambar berikut:

Menghitung Jumlah Data Unik Tanpa Mengabaikan Blank Cells

Jika anda cermati, ternyata dengan menggunakan rumus excel diatas sel yang kosong (Blank Cells) juga ikut terhitung. Data unik pada contoh diatas seharusnya 6 (Mangga, Manggis, Rambutan, Jeruk, Apel, Nanas), namun hasil rumus diatas 7 sebab sel kosong juga ikut terhitung sehingga plus 1 menjadi 7 Jenis.

Untuk menandai sel yang mengandung data ganda maupun sebaliknya menandai data unik bisa anda baca pada halaman: Cara Menemukan Data Ganda (Duplikat) dan Data Unik Pada Excel

Jika anda bermaksud mengabaikan sel kosong (Blank Cells), maka gunakan rumus excel berikutnya dibawah ini.

MENGHITUNG JUMLAH DATA UNIK & MENGABAIKAN SEL KOSONG


Rumus excel yang digunakan untuk mengetahui jumlah data unik dengan mengabaikan sel kosong (blank cells) adalah sebagai berikut:

=SUM((RangeData<>"")/COUNTIF(RangeData;RangeData&""))

Akhiri dengan Ctrl+Shift+Enter.

Untuk rumus non array bisa menggunakan gabungan Sumproduct dan Countif seperti dibawah ini:

=SUMPRODUCT((RangeData<>"")/COUNTIF(RangeData;RangeData&""))

Contoh penerapannya seperti yang terlihat dalam gambar berikut:

Menghitung Jumlah Data Unik Dan Mengabaikan Blank Cells

Seperti yang terlihat pada gambar diatas dengan menggunakan rumus array:

=SUM((A2:A11<>"")/COUNTIF(A2:A11;A2:A11&""))

Akhiri dengan Ctrl+Shift+Enter.

atau untuk non Array dengan rumus:

=SUMPRODUCT((A2:A11<>"")/COUNTIF(A2:A11;A2:A11&""))

Kita bisa menghitung jumlah nilai unik dari sebuah range data dan mengabaikan sel yang kosong (sel kosong tidak dihitung).

Untuk data yang cukup besar, menghitung jumlah data unik dengan formula excel diatas sebenarnya masih kurang maksimal. Namun saya kira untuk saat ini saya cukupkan dulu.

0 komentar