Rumus Menghitung Banyak Data Unik Dengan Fungsi Frequency Excel
Rumus Excel Menghitung Jumlah Data Yang Berbeda - Office excel telah menyediakan begitu banyak fungsi-fungsi default yang bisa kita manfaatkan untuk banyak sekali 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 menyerupai yang kita harapkan.
Misalnya kalau anda bermaksud mengetahui banyak data unik atau jumlah data yang berbeda, anda tidak bisa hanya memakai 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 tamat tutorial tersebut saya menyatakan bahwa menghitung banyak data unik dengan mengabaiakan duplikasinya memakai gabungan fungsi SUM atau SUMPRODUCT dengan fungsi COUNTIF menyerupai yang telah dijelaskan pada tutorial tersebut sebetulnya kurang maksimal.
Setelah menunggu sekian lama, hasilnya beberapa hari yang kemudian ada sebuah pertanyaan masuk:
Jika ada 2 atau lebih nomor NIK yg sama, bagaimana rumusnya ya kalau dihitung hanya sekali saja. Tapi tidak memakai 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, ketika memakai 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 masalah 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 kalau data yang anda hitung jumlah nilai uniknya cukup besar maka lebih saya sarankan untuk melibatkan fungsi FREQUENCY daripada memakai 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 menciptakan sumber data menyerupai berikut ini:
Menghitung Jumlah Data Numerik Yang Unik
Rumus excel pada pecahan ini hanya bisa dipakai 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 dipakai untuk menghitung data dalam format angka (numeric) dengan gabungan fungsi SUM dan FREQUENCY ialah sebagai berikut:
=SUM(--(FREQUENCY(RangeData;RangeData)>0))Contoh penerapan rumus excel di atas bisa anda lihat pada gambar berikut:
Dari pola 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 memakai rumus gabungan SUM dan FREQUENCY, untuk hasil yang sama anda bisa juga memakai 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 pola 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 pecahan ini bisa dipakai untuk menghitung data numerik (angka) atau teks maupun gabungan 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 acuan Range dari data yang akan anda hitung jumlah nilai uniknya. Adapun AwalRangedata merupakan acuan sel yang mengatakan posisi data pertama dari Range Data yang akan dihitung.
Sebagai pola untuk menghitung banyak data unik tanpa menghitung data yang mengandung duplikat pada range A3:A12 maka rumus yang bisa dipakai ialah sebagai berikut:
=SUMPRODUCT(--(FREQUENCY(MATCH(A3:A12;A3:A12;0);ROW(A3:A12)-ROW(A3)+1)>0))Selanjutnya silahkan perhatikan pola rumus berikut:
Rumus di atas memakai sumber data yang sama dengan contoh-contoh sebelumnya. Jika anda perhatikan, rumus di atas bisa dipakai untuk menghitung nilai unik untuk data teks, numerik maupun gabungan keduanya. Namun, kalau pada data tersebut mengandung sel kosong (Blank Cell) maka rumus ini akan menghasilkan Nilai error #NA.
Makara kalau 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 memakai 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 kalau pada data yang anda hitung terdapat sel kosong (Blank Cell).
Kedua rumus pada pecahan ini bisa dipakai untuk menghitung teks atau angka unik. Namun kalau 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 pecahan 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 memakai rumus excel berikut:
=SUM(IF(FREQUENCY(IF(RangeData<>"";MATCH(RangeData;RangeData;0));ROW(RangeData)-ROW(AwalRangedata)+1);1))Silahkan perhatikan pola berikut:
Rumus gabungan SUM, IF, MATCH dan ROW diatas harus diterapkan sebagai Array formula, sehingga sesudah menuliskan rumus ini harus diakhir dengan menekan Ctrl+Shift+Enter.
Sebagai alternatif anda bisa juga memakai 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 pecahan 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 dipakai untuk menghitung jumlah unik NIK atau nomor Kartu Keluarga?
Selain memakai beberapa alternatif rumus excel di atas, pertimbangkan juga untuk memakai 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 Praktis Menghapus Data Ganda di Excel.
Untuk mempelajari cara memakai masing-masing fungsi Excel yang dipakai pada rumus-rumus excel gabungan diatas silahkan menuju pada halaman berikut:
Sebagai epilog silahkan di bookmark halaman ini, barangkali lain waktu anda akan membutuhkan kembali untuk membaca tutorial di atas. Selain itu kalau anda merasa mendapat manfaat positif dari tutorial ini silahkan di share juga sehingga lebih banyak lagi yang akan mendapat manfaatnya. Salam Kelas excel.
Post a Comment for "Rumus Menghitung Banyak Data Unik Dengan Fungsi Frequency Excel"