Rumus Excel Menghitung Jumlah Data Yang Berbeda - Office excel telah menyediakan begitu banyak fungsi-fungsi default yang bisa kita manfaatkan untuk berbagai keperluan.
Menghitung Banyak Data Berbeda Dengan Mengabaikan Nilai Duplikat/Kembar
Dalam kasus-kasus tertentu fungsi-fungsi excel ini perlu kita gabungkan dengan fungsi lainnya sehingga menjadi sebuah rumus (Formula) untuk menghasilkan sesuatu seperti yang kita harapkan.
Misalnya jika anda bermaksud mengetahui banyak data unik atau jumlah data yang berbeda, anda tidak bisa hanya menggunakan fungsi Count dan variant-nya (Countif & Countifs) saja. Namun dengan menggabungkan fungsi Countif dengan fungsi Sum atau Sumproduct, jumlah data unik atau data berbeda tersebut bisa anda hitung.
Kasus ini sudah pernah kita bahas dan bisa Anda pelajari pada halaman berikut: Menghitung Jumlah Data Unik (Mengabaikan duplikasi) di Excel
Pada akhir tutorial tersebut saya menyatakan bahwa menghitung banyak data unik dengan mengabaiakan duplikasinya menggunakan gabungan fungsi SUM atau SUMPRODUCT dengan fungsi COUNTIF seperti yang telah dijelaskan pada tutorial tersebut sebenarnya kurang maksimal.
Setelah menunggu sekian lama, akhirnya beberapa hari yang lalu ada sebuah pertanyaan masuk:
Jika ada 2 atau lebih nomor NIK yg sama, bagaimana rumusnya ya jika dihitung hanya sekali saja. Tapi tidak menggunakan Data>Remove Duplication?
Selain dari segi performance, rumus menghitung jumlah data unik (count unique value) atau data yang berbeda (count distinct) dengan rumus tersebut tidak bisa diterapkan untuk mengetahui jumlah unik NIK (nomor Induk Kependudukan) atau Nomor Kartu keluarga (KK). Hal ini disebabkan adanya batas kemampuan excel yang hanya membaca 15 Digit angka/numeric pertama.
Meskipun data NIK atau Nomor KK anda format sebagai teks, saat menggunakan gabungan fungsi SUM atau SUMPRODUCT dengan fungsi COUNTIF excel, rumus tersebut secara otomatis merubah format teks tersebut sebagai numeric sehingga hanya 15 digit NIK atau nomor KK yang bisa dihitung sebagai nilai unik dan berbeda.
Kembali ke persoalan cara menghitung banyak data unik dan berbeda, kali ini kita akan membahas beberapa alternatif formula excel untuk mengetahui banyak data unik tanpa menghitung data duplikat (Count Tanpa Duplikat) sebagai kelanjutan tutorial sebelumnya yang telah disebutkan di atas.
Sebelum melanjutkan membaca tutorial ini sangat disarankan untuk membaca tutorial rumus excel sebelumnya berikut ini: Rumus Excel Menghitung Jumlah Data Unik
Setelah membaca tutorial tersebut, perlu anda ketahui bahwa jika data yang anda hitung jumlah nilai uniknya cukup besar maka lebih saya sarankan untuk melibatkan fungsi FREQUENCY daripada menggunakan fungsi COUNTIF sebagaimana dijelaskan pada halaman tersebut.
Ada beberapa alternatif rumus excel yang bisa anda gunakan sesuai kebutuhan dalam hal ini. Silahkan baca dan praktekkan rumus-rumus excel berikut ini. Namun sebelum mempraktekkan rumus-rumus excel berikut, silahkan membuat sumber data seperti berikut ini:
Menghitung Jumlah Data Numerik Yang Unik
Rumus excel pada bagian ini hanya bisa digunakan untuk menghitung data numerik (angka) saja. data yang berupa teks tidak akan terhitung jumlah uniknya dengan rumus-rumus excel berikut.
Menghitung Banyak Angka Unik Dengan Fungsi SUM dan FREQUENCY
Rumus umum yang bisa digunakan untuk menghitung data dalam format angka (numeric) dengan gabungan fungsi SUM dan FREQUENCY adalah sebagai berikut:
=SUM(--(FREQUENCY(RangeData;RangeData)>0))Contoh penerapan rumus excel di atas bisa anda lihat pada gambar berikut:
Dari contoh di atas bisa anda lihat bahwa rumus excel di atas hanya menghitung nilai unik untuk format angka saja. Nilai yang berupa sel kosong (Blank Cell) dan Teks di abaikan.
Menghitung Banyak Angka Unik Dengan Fungsi SUM, IF dan FREQUENCY
Selain menggunakan rumus gabungan SUM dan FREQUENCY, untuk hasil yang sama anda bisa juga menggunakan gabungan fungsi SUM, IF dan FREQUENCY untuk menghitung jumlah unik data numeric yang anda miliki.
Seperti rumus sebelumnya rumus berikut hanya menghitung data numeric saja. Sel kosong dan teks tidak akan dihitung sebagai nilai unik dengan rumus berikut:
=SUM(IF(FREQUENCY(RangeData;RangeData)>0;1))Silahkan perhatikan hasil rumus di atas pada contoh berikut ini.
Kedua rumus diatas hanya berlaku untuk menghitung banyak data unik dari nilai numeric/angka saja tanpa menghitung sel kosong.
Lalu bagaimana untuk menghitung jumlah unik data teks? kita lanjutkan pada alternatif rumus excel berikutnya
Menghitung Jumlah Data Teks dan atau Angka Yang Unik
Rumus excel pada bagian ini bisa digunakan untuk menghitung data numerik (angka) atau teks maupun campuran keduanya.
Menghitung Banyak Data (Teks/Angka) Unik Tanpa Sel Kosong
Jika data anda tidak mengandung sel kosong anda bisa menghitung nilai uniknya dengan rumus excel berikut:
=SUMPRODUCT(--(FREQUENCY(MATCH(RangeData;RangeData;0);ROW(RangeData)-ROW(AwalRangedata)+1)>0))Pada rumus diatas silahkan sesuaikan RangeData dengan referensi Range dari data yang akan anda hitung jumlah nilai uniknya. Adapun AwalRangedata merupakan referensi sel yang menunjukkan posisi data pertama dari Range Data yang akan dihitung.
Sebagai contoh untuk menghitung banyak data unik tanpa menghitung data yang mengandung duplikat pada range A3:A12 maka rumus yang bisa digunakan adalah sebagai berikut:
=SUMPRODUCT(--(FREQUENCY(MATCH(A3:A12;A3:A12;0);ROW(A3:A12)-ROW(A3)+1)>0))Selanjutnya silahkan perhatikan contoh rumus berikut:
Rumus di atas menggunakan sumber data yang sama dengan contoh-contoh sebelumnya. Jika anda perhatikan, rumus di atas bisa digunakan untuk menghitung nilai unik untuk data teks, numerik maupun campuran keduanya. Namun, jika pada data tersebut mengandung sel kosong (Blank Cell) maka rumus ini akan menghasilkan Nilai error #NA.
Jadi jika data yang ingin anda ketahui jumlah nilai uniknya mengandung sel kosong, maka jangan gunakan rumus di atas.
Apabila anda merasa kesulitan dengan rumus di atas, alternatif lain anda bisa menggunakan rumus berikut ini untuk hasil yang sama:
=SUM(IF(FREQUENCY(MATCH(A3:A12;A3:A12;0);MATCH(A3:A12;A3:A12;0))>0;1))Seperti rumus sebelumnya, rumus excel ini juga akan menghasilkan nilai Error #NA jika pada data yang anda hitung terdapat sel kosong (Blank Cell).
Kedua rumus pada bagian ini bisa digunakan untuk menghitung teks atau angka unik. Namun jika pada data yang dihitung terdapat sel kosong (Blank Cell) maka rumus ini akan menghasilkan Nilai Error #NA.
Waduh... Jika terdapat sel kosong pada data yang akan dihitung jumlah data uniknya, bagaimana rumus excelnya? Kita lanjutkan pada bagian selanjutnya.
Menghitung Banyak Data (Teks/Angka) Unik Dengan Sel Kosong
Apabila anda bermaksud menghitung jumlah data yang berbeda dengan mengabaikan nilai duplikat dan pada data tersebut terdapat blank cell, maka anda bisa menggunakan rumus excel berikut:
=SUM(IF(FREQUENCY(IF(RangeData<>"";MATCH(RangeData;RangeData;0));ROW(RangeData)-ROW(AwalRangedata)+1);1))Silahkan perhatikan contoh berikut:
Rumus gabungan SUM, IF, MATCH dan ROW diatas harus diterapkan sebagai Array formula, sehingga setelah menuliskan rumus ini harus diakhir dengan menekan Ctrl+Shift+Enter.
Sebagai alternatif anda bisa juga menggunakan rumus excel berikut untuk hasil yang sama:
=SUM(IF(FREQUENCY(IF(LEN(RangeData)>0;MATCH(RangeData;RangeData;0);""); IF(LEN(RangeData)>0;MATCH(RangeData;RangeData;0);""))>0;1))Seperti rumus sebelumnya, gabungan fungsi SUM, IF, FREQUENCY, LEN dan MATCH ini merupakan rumus Array (Array Formula) sehingga akhiri setiap penulisan rumus ini dengan Ctrl+Shift+Enter.
Kedua rumus pada bagian ini merupakan Array Formula. Akhiri setiap penulisan rumus dengan Ctrl+Shift+Enter sehigga muncul kurung kurawal atau curly brackets {...} secara otomatis
Setelah mempelajari beberapa rumus excel diatas, kira-kira rumus yang mana yang bisa digunakan untuk menghitung jumlah unik NIK atau nomor Kartu Keluarga?
Selain menggunakan beberapa alternatif rumus excel di atas, pertimbangkan juga untuk menggunakan fitur remove duplikat atau fitur advanced filter yang telah disediakan oleh office excel untuk mengetahui jumlah data atau banyak data yang unik dan berbeda ini. Panduannya bisa anda baca pada halaman berikut : Cara Mudah Menghapus Data Ganda di Excel.
Sebagai penutup silahkan di bookmark halaman ini, barangkali lain waktu anda akan membutuhkan kembali untuk membaca tutorial di atas. Selain itu jika anda merasa mendapat manfaat positif dari tutorial ini silahkan di share juga sehingga lebih banyak lagi yang akan mendapatkan manfaatnya. Salam Kelas excel.
