Rumus Mengkalkulasikan Banyak Data Unik Dengan Fungsi Frequency Excel

Menghitung jumlah data unik (count unique values) atau data yang berlainan (count distinct values) pada microsoft excel menggunakan fungsi Frequency Excel.

Rumus Excel Menghitung Jumlah Data Yang Berbeda - Office excel sudah menawarkan terlalu banyak fungsi-fungsi default yang dapat kita manfaatkan untuk aneka macam keperluan.

• Menghitung Banyak Data Berbeda Dengan Menagabikan Nilai Kembar
• Menghitung Banyak Angka (Numeric) Yang Unik
  • Dengan Fungsi SUM dan FREQUENCY
  • Dengan Fungsi SUM , IF dan FREQUENCY
• Menghitung Banyak Teks dan Angka (Numeric) Yang Unik
  • Sumber Data Tidak Mengandung Sel Kosong (Blank Cell)
  • Sumber Data Terdapat Sel Kosong (Blank Cell)

Menghitung Banyak Data Berbeda Dengan Mengabaikan Nilai Duplikat/Kembar

Dalam kasus-kasus tertentu fungsi-fungsi excel ini perlu kita gabungkan dengan fungsi yang lain sehingga menjadi suatu rumus (Formula) untuk menciptakan sesuatu menyerupai yang kita harapkan.

Misalnya apabila anda bermaksud mengenali banyak data unik atau jumlah data yang berlainan , 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 berlainan 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 bimbingan tersebut saya menyatakan bahwa menjumlah banyak data unik dengan mengabaiakan duplikasinya menggunakan gabungan fungsi SUM atau SUMPRODUCT dengan fungsi COUNTIF menyerupai yang sudah diterangkan pada bimbingan tersebut bersamaan kurang maksimal.

Setelah menanti sekian usang , alhasil beberapa hari yang kemudian ada suatu pertanyaan masuk:

Jika ada 2 atau lebih nomor NIK yg sama , bagaimana rumusnya ya apabila dijumlah cuma sekali saja. Tapi tidak menggunakan Data>Remove Duplication?

Selain dari sisi performance , rumus menjumlah jumlah data unik (count unique value) atau data yang berlainan (count distinct) dengan rumus tersebut tidak dapat dipraktekkan untuk mengenali jumlah unik NIK (nomor Induk Kependudukan) atau Nomor Kartu keluarga (KK). Hal ini disebabkan adanya batas kesanggupan excel yang cuma membaca 15 Digit angka/numeric pertama.

Meskipun data NIK atau Nomor KK anda format selaku teks , dikala menggunakan gabungan fungsi SUM atau SUMPRODUCT dengan fungsi COUNTIF excel , rumus tersebut secara otomatis mengganti format teks tersebut selaku numeric sehingga cuma 15 digit NIK atau nomor KK yang dapat dijumlah selaku nilai unik dan berbeda.

Kembali ke kendala cara menjumlah banyak data unik dan berlainan , kali ini kita akan membahas beberapa alternatif formula excel untuk mengenali banyak data unik tanpa menjumlah data duplikat (Count Tanpa Duplikat) selaku kelanjutan bimbingan sebelumnya yang sudah disebutkan di atas.

Sebelum melanjutkan membaca bimbingan ini sungguh dianjurkan untuk membaca bimbingan rumus excel sebelumnya berikut ini: Rumus Excel Menghitung Jumlah Data Unik

Setelah membaca bimbingan tersebut , perlu anda pahami bahwa apabila data yang anda hitung jumlah nilai uniknya cukup besar maka lebih saya sarankan untuk melibatkan fungsi FREQUENCY ketimbang menggunakan fungsi COUNTIF sebagaimana diterangkan pada halaman tersebut.

Ada beberapa alternatif rumus excel yang dapat anda pakai 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 menyerupai berikut ini:

Menghitung Jumlah Data Numerik Yang Unik

Rumus excel pada pecahan ini cuma bisa digunakan untuk menjumlah 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 lazim yang dapat digunakan untuk menjumlah data dalam format angka (numeric) dengan gabungan fungsi SUM dan FREQUENCY yakni selaku 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 cuma menjumlah 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 serupa anda dapat juga menggunakan gabungan fungsi SUM , IF dan FREQUENCY untuk menjumlah jumlah unik data numeric yang anda miliki.

Seperti rumus sebelumnya rumus berikut hanya menjumlah 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 cuma berlaku untuk menjumlah banyak data unik dari nilai numeric/angka saja tanpa menjumlah sel kosong.

Lalu bagaimana untuk menjumlah jumlah unik data teks? kita teruskan pada alternatif rumus excel berikutnya

Menghitung Jumlah Data Teks dan atau Angka Yang Unik

Rumus excel pada pecahan ini bisa digunakan untuk menjumlah 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 dapat 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 hendak anda hitung jumlah nilai uniknya. Adapun AwalRangedata ialah rujukan sel yang memamerkan posisi data pertama dari Range Data yang hendak dihitung.

Sebagai pola untuk menjumlah banyak data unik tanpa menjumlah data yang mengandung duplikat pada range A3:A12 maka rumus yang dapat digunakan yakni selaku berikut:

=SUMPRODUCT(--(FREQUENCY(MATCH(A3:A12;A3:A12;0);ROW(A3:A12)-ROW(A3)+1)>0))

Selanjutnya silahkan amati pola rumus berikut:



Rumus di atas menggunakan sumber data yang serupa dengan contoh-contoh sebelumnya. Jika anda amati , rumus di atas bisa digunakan untuk menjumlah nilai unik untuk data teks , numerik maupun gabungan keduanya. Namun , apabila pada data tersebut mengandung sel kosong (Blank Cell) maka rumus ini akan menciptakan Nilai error #NA.

Jadi apabila data yang ingin anda pahami jumlah nilai uniknya mengandung sel kosong , maka jangan gunakan rumus di atas.

Apabila anda merasa kesusahan 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 menciptakan nilai Error #NA apabila pada data yang anda hitung terdapat sel kosong (Blank Cell).

Kedua rumus pada pecahan ini bisa digunakan untuk menjumlah teks atau angka unik. Namun apabila pada data yang dijumlah terdapat sel kosong (Blank Cell) maka rumus ini akan menciptakan Nilai Error #NA.

Waduh... Jika terdapat sel kosong pada data yang hendak dijumlah jumlah data uniknya , bagaimana rumus excelnya? Kita teruskan pada pecahan selanjutnya.

Menghitung Banyak Data (Teks/Angka) Unik Dengan Sel Kosong

Apabila anda bermaksud menjumlah jumlah data yang berlainan dengan mengabaikan nilai duplikat dan pada data tersebut terdapat blank cell , maka anda dapat menggunakan rumus excel berikut:

=SUM(IF(FREQUENCY(IF(RangeData<>"";MATCH(RangeData;RangeData;0));ROW(RangeData)-ROW(AwalRangedata)+1);1))

Silahkan amati pola berikut:



Rumus gabungan SUM , IF , MATCH dan ROW diatas mesti dipraktekkan selaku Array formula , sehingga setelah menuliskan rumus ini mesti diakhir dengan menekan Ctrl+Shift+Enter.

Sebagai alternatif anda dapat 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 ialah rumus Array (Array Formula) sehingga akhiri setiap penulisan rumus ini dengan Ctrl+Shift+Enter.

Kedua rumus pada pecahan ini ialah Array Formula. Akhiri setiap penulisan rumus dengan Ctrl+Shift+Enter sehigga timbul kurung kurawal atau curly brackets {...} secara otomatis

Setelah mempelajari beberapa rumus excel diatas , kira-kira rumus yang mana yang dapat digunakan untuk menjumlah 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 sudah ditawarkan oleh office excel untuk mengenali jumlah data atau banyak data yang unik dan berlainan ini. Panduannya bisa anda baca pada halaman berikut : Cara Praktis Menghapus Data Ganda di Excel.

Untuk mempelajari cara menggunakan masing-masing fungsi Excel yang digunakan pada rumus-rumus excel gabungan diatas silahkan menuju pada halaman berikut:

1. Fungsi SUM
2. Fungsi SUMPRODUCT
3. Fungsi IF
4. Fungsi FREQUENCY
5. Fungsi ROW
6. Fungsi MATCH

Sebagai epilog silahkan di bookmark halaman ini , barangkali lain waktu anda akan memerlukan kembali untuk membaca bimbingan di atas. Selain itu apabila anda merasa mendapat faedah positif dari bimbingan ini silahkan di share juga sehingga lebih banyak lagi yang hendak mendapat manfaatnya. Salam Kelas excel.

>> Gabung Telegram Channel <<