Formula generik
=SUMPRODUCT(1/COUNTIF(data,data))
Ringkasan
Untuk mengira jumlah nilai unik dalam rangkaian sel, anda boleh menggunakan formula berdasarkan fungsi COUNTIF dan SUMPRODUCT. Dalam pertunjukan contoh, formula dalam F6 adalah:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Penjelasan
Bekerja dari dalam ke luar, COUNTIF dikonfigurasi ke nilai dalam julat B5: B14, menggunakan semua nilai yang sama sebagai kriteria:
COUNTIF(B5:B14,B5:B14)
Kerana kami memberikan 10 nilai untuk kriteria, kami mendapatkan kembali array dengan 10 hasil seperti ini:
(3;3;3;2;2;3;3;3;2;2)
Setiap nombor mewakili kiraan - "Jim" muncul 3 kali, "Sue" muncul 2 kali, dan seterusnya.
Susunan ini dikonfigurasikan sebagai pembahagi dengan 1 sebagai pengangka. Selepas pembahagian, kami mendapat pelbagai lagi:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Setiap nilai yang berlaku hanya sekali dalam julat akan muncul sebagai 1s, tetapi nilai yang terjadi berkali-kali akan muncul sebagai nilai pecahan yang sesuai dengan nilai gandaan. (iaitu nilai yang muncul 4 kali dalam data akan menghasilkan 4 nilai = 0.25).
Akhirnya, fungsi SUMPRODUCT menjumlahkan semua nilai dalam larik dan mengembalikan hasilnya.
Mengendalikan sel kosong
Salah satu cara untuk menangani sel kosong atau kosong adalah dengan menyesuaikan formula seperti berikut:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Dengan menggabungkan rentetan kosong ("") ke data, kami mencegah angka nol berakhir dalam array yang dibuat oleh COUNTIF apabila terdapat sel kosong dalam data. Ini penting, kerana angka sifar di pembahagi akan menyebabkan formula membuang ralat # DIV / 0. Ia berfungsi kerana menggunakan rentetan kosong ("") untuk kriteria akan mengira sel kosong.
Namun, walaupun versi formula ini tidak akan menimbulkan kesalahan # DIV / 0 ketika dengan sel kosong, ia akan memasukkan sel kosong dalam kiraan. Sekiranya anda ingin mengecualikan sel kosong dari kiraan, gunakan:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Ini mempunyai kesan membatalkan jumlah sel kosong dengan menjadikan pengangka sifar untuk kiraan yang berkaitan.
Prestasi Lambat?
Ini adalah formula yang menarik dan elegan, tetapi mengira jauh lebih perlahan daripada formula yang menggunakan FREQUENCY untuk mengira nilai unik. Untuk set data yang lebih besar, anda mungkin mahu beralih ke formula berdasarkan fungsi FREQUENCY. Inilah formula untuk nilai angka, dan satu untuk nilai teks.
