Formula umum
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Ringkasan
Untuk menghitung nilai numerik unik dalam rentang, Anda bisa menggunakan rumus yang didasarkan pada fungsi FREQUENCY, SUM, dan IF. Pada contoh yang diperlihatkan, nomor karyawan muncul dalam rentang B5: B14. Rumus di G6 adalah:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
yang mengembalikan 2, karena ada 2 id karyawan unik di gedung A.
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter, kecuali Anda menggunakan Excel 365.
Penjelasan
Catatan: Sebelum Excel 365, Excel tidak memiliki fungsi khusus untuk menghitung nilai unik. Rumus ini memperlihatkan satu cara untuk menghitung nilai unik, asalkan berupa numerik. Jika Anda memiliki nilai teks, atau campuran teks dan angka, Anda perlu menggunakan rumus yang lebih rumit.
Fungsi Excel FREQUENCY mengembalikan distribusi frekuensi, yang merupakan tabel ringkasan yang berisi frekuensi nilai numerik, diatur dalam "bins". Kami menggunakannya di sini sebagai cara memutar untuk menghitung nilai numerik unik. Untuk menerapkan kriteria, kami menggunakan fungsi IF.
Bekerja dari dalam ke luar, pertama-tama kita memfilter nilai dengan fungsi IF:
IF(C5:C14="A",B5:B14) // filter on building A
Hasil dari operasi ini adalah larik seperti ini:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Perhatikan bahwa semua ID di gedung B sekarang SALAH. Larik ini dikirim langsung ke fungsi FREQUENCY sebagai data_array . Untuk bins_array , kami menyediakan idnya sendiri:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
Dengan konfigurasi ini, FREQUENCY mengembalikan array di bawah ini:
(4;0;0;0;2;0;0;0;0;0;0)
Hasilnya agak samar, tetapi artinya 905 muncul empat kali, dan 773 muncul dua kali. Nilai FALSE secara otomatis diabaikan.
FREQUENCY memiliki fitur khusus yang secara otomatis mengembalikan nol untuk setiap angka yang telah muncul dalam larik data, itulah sebabnya nilai-nilai menjadi nol setelah sebuah angka ditemukan. Ini adalah fitur yang memungkinkan pendekatan ini bekerja.
Selanjutnya, setiap nilai ini diuji agar lebih besar dari nol:
(4;0;0;0;2;0;0;0;0;0;0)>0
Hasilnya adalah array seperti ini:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Setiap TRUE dalam daftar mewakili nomor unik dalam daftar, dan kita hanya perlu menjumlahkan nilai TRUE dengan SUM. Namun, SUM tidak akan menjumlahkan nilai logika dalam sebuah array, jadi kita harus terlebih dahulu memaksa nilainya menjadi 1 atau nol. Ini dilakukan dengan negatif ganda (-). Hasil array hanya 1 atau 0:
(1;0;0;0;1;0;0;0;0;0;0)
Akhirnya, SUM menambahkan nilai-nilai ini dan mengembalikan total, yang dalam hal ini adalah 2.
Berbagai kriteria
Anda dapat memperluas rumus untuk menangani beberapa kriteria seperti ini:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
