Rumus Menghitung Banyak Data Unik Dengan Fungsi Frequency Excel
Rumus Excel Menghitung Jumlah Data Yang Berbeda - Office excel tlah menyediakan begitu banyak fungsi-fungsi default yg 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 sprti yg kita harapkan.
Misalnya jika anda bermaksud mengetahui banyak data unik atau jumlah data yg berbeda, anda tidak bisa hanya menggunakan fungsi Count & 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 sdh pernah kita bahas & bisa Anda pelajari pada halaman brkut: 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 sprti yg tlah dijelaskan pada tutorial tersebut sebenarnya kurang maksimal.
Setlah menunggu sekian lama, akhirnya beberapa hari yg 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 yg 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 a&ya batas kemampuan excel yg hanya membaca 15 Digit angka/numeric pertama.
Meskipun data NIK atau Nomor KK anda format sbgai teks, saat menggunakan gabungan fungsi SUM atau SUMPRODUCT dengan fungsi COUNTIF excel, rumus tersebut secara otomatis merubah format teks tersebut sbgai numeric sehingga hanya 15 digit NIK atau nomor KK yg bisa dihitung sbgai nilai unik & berbeda.
Kembali ke persoalan cara menghitung banyak data unik & berbeda, kali ini kita akan membahas beberapa alternatif formula excel untuk mengetahui banyak data unik tanpa menghitung data duplikat (Count Tanpa Duplikat) sbgai kelanjutan tutorial sebelumnya yg tlah disebutkan di atas.
Sebelum melanjutkan membaca tutorial ini sangat disarankan untuk membaca tutorial rumus excel sebelumnya brkut ini: Rumus Excel Menghitung Jumlah Data Unik
Setlah membaca tutorial tersebut, perlu anda ketahui bahwa jika data yg anda hitung jumlah nilai uniknya cukup besar maka lebih saya sarankan untuk melibatkan fungsi FREQUENCY daripada menggunakan fungsi COUNTIF sbgaimana dijelaskan pada halaman tersebut.
Ada beberapa alternatif rumus excel yg bisa anda gunakan sesuai kebutuhan dlm hal ini. Silahkan baca & praktekkan rumus-rumus excel brkut ini. Namun sebelum mempraktekkan rumus-rumus excel brkut, slahkan membuat sumber data sprti brkut ini:
Menghitung Jumlah Data Numerik Yang Unik
Rumus excel pada bagian ini hanya bisa digunakan untuk menghitung data numerik (angka) saja. data yg berupa teks tidak akan terhitung jumlah uniknya dengan rumus-rumus excel brkut.
Menghitung Banyak Angka Unik Dengan Fungsi SUM & FREQUENCY
Rumus umum yg bisa digunakan untuk menghitung data dlm format angka (numeric) dengan gabungan fungsi SUM & FREQUENCY adalah sbgai brkut:
=SUM(--(FREQUENCY(RangeData;RangeData)>0))Contoh penerapan rumus excel di atas bisa anda lihat pada gambar brkut:
Dari contoh di atas bisa anda lihat bahwa rumus excel di atas hanya menghitung nilai unik untuk format angka saja. Nilai yg berupa sel kosong (Blank Cell) & Teks di abaikan.
Menghitung Banyak Angka Unik Dengan Fungsi SUM, IF & FREQUENCY
Selain menggunakan rumus gabungan SUM & FREQUENCY, untuk hasil yg sama anda bisa juga menggunakan gabungan fungsi SUM, IF & FREQUENCY untuk menghitung jumlah unik data numeric yg anda miliki.
Seperti rumus sebelumnya rumus brkut hanya menghitung data numeric saja. Sel kosong & teks tidak akan dihitung sbgai nilai unik dengan rumus brkut:
=SUM(IF(FREQUENCY(RangeData;RangeData)>0;1))Silahkan perhatikan hasil rumus di atas pada contoh brkut 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 brkutnya
Menghitung Jumlah Data Teks & 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 brkut:
=SUMPRODUCT(--(FREQUENCY(MATCH(RangeData;RangeData;0);ROW(RangeData)-ROW(AwalRangedata)+1)>0))Pada rumus diatas slahkan sesuaikan RangeData dengan referensi Range dari data yg akan anda hitung jumlah nilai uniknya. Adapun AwalRangedata adalah referensi sel yg menunjukkan posisi data pertama dari Range Data yg akan dihitung.
Sebagai contoh untuk menghitung banyak data unik tanpa menghitung data yg mengandung duplikat pada range A3:A12 maka rumus yg bisa digunakan adalah sbgai brkut:
=SUMPRODUCT(--(FREQUENCY(MATCH(A3:A12;A3:A12;0);ROW(A3:A12)-ROW(A3)+1)>0))Selanjutnya slahkan perhatikan contoh rumus brkut:
Rumus di atas menggunakan sumber data yg 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 yg 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 brkut ini untuk hasil yg 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 yg anda hitung terdapat sel kosong (Blank Cell).
Kedua rumus pada bagian ini bisa digunakan untuk menghitung teks atau angka unik. Namun jika pada data yg dihitung terdapat sel kosong (Blank Cell) maka rumus ini akan menghasilkan Nilai Error #NA.
Waduh... Jika terdapat sel kosong pada data yg 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 yg berbeda dengan mengabaikan nilai duplikat & pada data tersebut terdapat blank cell, maka anda bisa menggunakan rumus excel brkut:
=SUM(IF(FREQUENCY(IF(RangeData<>"";MATCH(RangeData;RangeData;0));ROW(RangeData)-ROW(AwalRangedata)+1);1))Silahkan perhatikan contoh brkut:
Rumus gabungan SUM, IF, MATCH & ROW diatas harus diterapkan sbgai Array formula, sehingga setlah menuliskan rumus ini harus diakhir dengan menekan Ctrl+Shift+Enter.
Sebagai alternatif anda bisa juga menggunakan rumus excel brkut untuk hasil yg 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 & MATCH ini adalah rumus Array (Array Formula) sehingga akhiri setiap penulisan rumus ini dengan Ctrl+Shift+Enter.
Kedua rumus pada bagian ini adalah Array Formula. Akhiri setiap penulisan rumus dengan Ctrl+Shift+Enter sehigga muncul kurung kurawal atau curly brackets {...} secara otomatis
Setlah mempelajari beberapa rumus excel diatas, kira-kira rumus yg mana yg 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 yg tlah disediakan oleh office excel untuk mengetahui jumlah data atau banyak data yg unik & berbeda ini. Panduannya bisa anda baca pada halaman brkut : Cara Mudah Menghapus Data Ganda di Excel.
Untuk mempelajari cara menggunakan masing-masing fungsi Excel yg digunakan pada rumus-rumus excel gabungan diatas slahkan menuju pada halaman brkut:
Sebagai penutup slahkan 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 slahkan di share juga sehingga lebih banyak lagi yg akan mendapatkan manfaatnya. Salam Kelas excel. Sumber https://www.kelasexcel.id
Belum ada Komentar untuk "Rumus Menghitung Banyak Data Unik Dengan Fungsi Frequency Excel"
Posting Komentar