Rumus Mengkalkulasikan Banyak Data Unik Dengan Faedah Dari Frequency Excel
Menghitung jumlah data unik (count unique values) atau data yang berlawanan (count distinct values) pada microsoft excel menggunakan fungsi Frequency Excel.
Rumus Excel Menghitung Jumlah Data Yang Berbeda – Office excel sudah memberikan terlampau banyak fungsi-fungsi default yang bisa kita manfaatkan untuk berbagai keperluan.
Menghitung Banyak Data Berbeda Dengan Mengabaikan Nilai Duplikat/Kembar
Dalam masalah-kasus tertentu fungsi-fungsi excel ini perlu kita gabungkan dengan fungsi lainnya sehingga menjadi suatu rumus (Formula) untuk menciptakan sesuatu seperti yang kita harapkan.
Misalnya jika anda berencana memahami banyak data unik atau jumlah data yang berbeda , anda tidak dapat cuma menggunakan fungsi Count dan variant-nya (Countif & Countifs) saja. Namun dengan memadukan fungsi Countif dengan fungsi Sum atau Sumproduct , jumlah data unik atau data berlawanan tersebut bisa anda hitung.
Kasus ini sudah pernah kita diskusikan dan bisa Anda pelajari pada halaman berikut: Menghitung Jumlah Data Unik (Mengabaikan duplikasi) di Excel
Pada final tutorial tersebut saya menyatakan bahwa menjumlah banyak data unik dengan mengabaiakan duplikasinya menggunakan adonan fungsi SUM atau SUMPRODUCT dengan fungsi COUNTIF mirip yang telah dijelaskan pada panduan tersebut bekerjsama kurang maksimal.
Setelah menunggu sekian lama , kesudahannya beberapa hari yang kemudian 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 berlainan (count distinct) dengan rumus tersebut tidak bisa diterapkan untuk mengenali 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 selaku teks , ketika memakai campuran fungsi SUM atau SUMPRODUCT dengan fungsi COUNTIF excel , rumus tersebut secara otomatis mengganti format teks tersebut sebagai numeric sehingga cuma 15 digit NIK atau nomor KK yang dapat dijumlah selaku nilai unik dan berlainan.
Kembali ke duduk perkara rumus mengkalkulasikan banyak data unik dan berlainan , kali ini kita akan membicarakan beberapa alternatif formula excel untuk mengetahui banyak data unik tanpa menjumlah data duplikat (Count Tanpa Duplikat) sebagai kelanjutan bimbingan sebelumnya yang sudah disebutkan di atas.
Sebelum melanjutkan membaca tutorial ini sangat diusulkan untuk membaca tutorial rumus excel sebelumnya berikut ini: Rumus Excel Menghitung Jumlah Data Unik
Setelah membaca bimbingan tersebut , perlu anda ketahui bahwa bila data yang anda hitung jumlah nilai uniknya cukup besar maka lebih saya sarankan untuk melibatkan fungsi FREQUENCY dibandingkan dengan memakai fungsi COUNTIF sebagaimana diterangkan pada halaman tersebut.
Ada beberapa alternatif rumus excel yang mampu anda gunakan sesuai keperluan dalam hal ini. Silahkan baca dan praktekkan rumus-rumus excel berikut ini. Namun sebelum mempraktekkan rumus-rumus excel berikut , silahkan menciptakan sumber data ibarat berikut ini:
Menghitung Jumlah Data Numerik Yang Unik
Rumus excel pada bagian ini cuma mampu dipakai untuk mengkalkulasikan data numerik (angka) saja. data yang berbentukteks tidak akan terhitung jumlah uniknya dengan rumus-rumus excel berikut.
Menghitung Banyak Angka Unik Dengan Fungsi SUM dan FREQUENCY
Rumus biasa yang bisa digunakan untuk mengkalkulasikan data dalam format angka (numeric) dengan adonan fungsi SUM dan FREQUENCY merupakan selaku berikut:
=SUM(--(FREQUENCY(RangeData;RangeData)>0))Contoh penerapan rumus excel di atas bisa anda lihat pada gambar berikut:
Dari teladan di atas mampu anda lihat bahwa rumus excel di atas hanya mengkalkulasikan 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 adonan SUM dan FREQUENCY , untuk hasil yang serupa anda mampu juga memakai adonan fungsi SUM , IF dan FREQUENCY untuk menjumlah jumlah unik data numeric yang anda miliki.
Seperti rumus sebelumnya rumus berikut hanya menghitung data numeric saja. Sel kosong dan teks tidak akan dijumlah selaku nilai unik dengan rumus berikut:
=SUM(IF(FREQUENCY(RangeData;RangeData)>0;1))Silahkan amati hasil rumus di atas pada pola berikut ini.
Kedua rumus diatas hanya berlaku untuk mengkalkulasikan banyak data unik dari nilai numeric/angka saja tanpa menjumlah sel kosong.
Lalu bagaimana untuk mengkalkulasikan jumlah unik data teks? kita teruskan pada alternatif rumus excel selanjutnya
Menghitung Jumlah Data Teks dan atau Angka Yang Unik
Rumus excel pada belahan ini bisa dipakai untuk mengkalkulasikan 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 mampu menjumlah 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 rujukan Range dari data yang akan anda hitung jumlah nilai uniknya. Adapun AwalRangedata merupakan rujukan sel yang memberikan posisi data pertama dari Range Data yang mau dijumlah.
Sebagai pola untuk menjumlah banyak data unik tanpa menjumlah data yang mengandung duplikat pada range A3:A12 maka rumus yang bisa digunakan ialah selaku berikut:
=SUMPRODUCT(--(FREQUENCY(MATCH(A3:A12;A3:A12;0);ROW(A3:A12)-ROW(A3)+1)>0))Selanjutnya silahkan amati contoh rumus berikut:
Rumus di atas menggunakan sumber data yang serupa dengan teladan-contoh sebelumnya. Jika anda amati , rumus di atas bisa dipakai untuk mengkalkulasikan 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 jikalau 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 dapat 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 membuat nilai Error #NA kalau pada data yang anda hitung terdapat sel kosong (Blank Cell).
Kedua rumus pada potongan ini bisa dipakai untuk menjumlah teks atau angka unik. Namun jikalau pada data yang dijumlah terdapat sel kosong (Blank Cell) maka rumus ini akan membuat Nilai Error #NA.
Waduh… Jika terdapat sel kosong pada data yang mau dihitung jumlah data uniknya , bagaimana rumus excelnya? Kita lanjutkan pada bab berikutnya.
Menghitung Banyak Data (Teks/Angka) Unik Dengan Sel Kosong
Apabila anda berniat mengkalkulasikan jumlah data yang bertentangan 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 amati contoh berikut:
Rumus gabungan SUM , IF , MATCH dan ROW diatas harus dipraktekkan selaku Array formula , sehingga sehabis menuliskan rumus ini mesti 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 , adonan 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 kepingan 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 dapat digunakan untuk mengkalkulasikan jumlah unik NIK atau nomor Kartu Keluarga?
Selain memakai beberapa alternatif rumus excel di atas , pertimbangkan juga untuk menggunakan fitur remove duplikat atau fitur advanced filter yang sudah disediakan oleh office excel untuk mengerti jumlah data atau banyak data yang unik dan berlainan ini. Panduannya bisa anda baca pada halaman berikut : Cara Mudah 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 penutup silahkan di bookmark halaman ini , barangkali lain waktu anda akan memerlukan kembali untuk membaca tutorial di atas. Selain itu kalau anda merasa mendapatkan faedah positif dari panduan ini silahkan di share juga sehingga lebih banyak lagi yang mau menemukan manfaatnya. Salam Kelas excel.>> Gabung Telegram Channel <<