Formula generik
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Ringkasan
Untuk mengira nilai numerik unik dalam julat, anda boleh menggunakan formula berdasarkan fungsi FREQUENCY, SUM, dan IF. Dalam contoh yang ditunjukkan, nombor pekerja muncul dalam julat B5: B14. Formula dalam G6 adalah:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
yang mengembalikan 2, kerana terdapat 2 id pekerja yang unik di bangunan A.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter, kecuali anda menggunakan Excel 365.
Penjelasan
Catatan: Sebelum Excel 365, Excel tidak mempunyai fungsi khusus untuk menghitung nilai unik. Rumus ini menunjukkan satu cara untuk mengira nilai unik, asalkan berangka. Sekiranya anda mempunyai nilai teks, atau gabungan teks dan nombor, anda perlu menggunakan formula yang lebih rumit.
Fungsi FREQUENCY Excel mengembalikan taburan frekuensi, yang merupakan jadual ringkasan yang mengandungi frekuensi nilai angka, yang disusun dalam "tong". Kami menggunakannya di sini sebagai kaedah bulatan untuk mengira nilai angka unik. Untuk menerapkan kriteria, kami menggunakan fungsi IF.
Berfungsi dari dalam ke luar, pertama-tama kami menapis nilai dengan fungsi IF:
IF(C5:C14="A",B5:B14) // filter on building A
Hasil operasi ini adalah susunan seperti ini:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Perhatikan semua id di bangunan B kini SALAH. Array ini dihantar terus ke fungsi FREQUENCY sebagai data_array . Untuk bins_array , kami menyediakan id 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 susunan di bawah:
(4;0;0;0;2;0;0;0;0;0;0)
Hasilnya agak samar, tetapi maknanya 905 muncul empat kali, dan 773 muncul dua kali. Nilai PALSU diabaikan secara automatik.
FREQUENCY mempunyai ciri khas yang secara automatik mengembalikan sifar untuk nombor apa pun yang telah muncul dalam array data, itulah sebabnya nilainya menjadi sifar setelah angka dijumpai. Inilah ciri yang membolehkan pendekatan ini berfungsi.
Seterusnya, setiap nilai ini diuji lebih besar daripada sifar:
(4;0;0;0;2;0;0;0;0;0;0)>0
Hasilnya adalah susunan seperti ini:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Setiap BENAR dalam senarai mewakili nombor unik dalam senarai, dan kita hanya perlu menambahkan nilai BENAR dengan SUM. Walau bagaimanapun, SUM tidak akan menambahkan nilai logik dalam array, jadi kita perlu memaksa nilai menjadi 1 atau sifar. Ini dilakukan dengan negatif-ganda (-). Hasilnya, array hanya 1 atau 0:
(1;0;0;0;1;0;0;0;0;0;0)
Akhirnya, SUM menambahkan nilai-nilai ini dan mengembalikan jumlahnya, yang dalam kes ini adalah 2.
Pelbagai kriteria
Anda boleh memperluas formula untuk menangani pelbagai kriteria seperti ini:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
