Formula umum
=SUMPRODUCT(1/COUNTIF(data,data))
Ringkasan
Untuk menghitung jumlah nilai unik dalam rentang sel, Anda bisa menggunakan rumus yang didasarkan pada fungsi COUNTIF dan SUMPRODUCT. Pada contoh show, rumus di F6 adalah:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Penjelasan
Bekerja dari dalam ke luar, COUNTIF dikonfigurasi ke nilai dalam rentang B5: B14, menggunakan semua nilai yang sama sebagai kriteria:
COUNTIF(B5:B14,B5:B14)
Karena kami memberikan 10 nilai untuk kriteria, kami mendapatkan kembali sebuah array dengan 10 hasil seperti ini:
(3;3;3;2;2;3;3;3;2;2)
Setiap angka mewakili hitungan - "Jim" muncul 3 kali, "Sue" muncul 2 kali, dan seterusnya.
Larik ini dikonfigurasi sebagai pembagi dengan 1 sebagai pembilangnya. Setelah pembagian, kita mendapatkan larik lain:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Nilai apa pun yang muncul hanya sekali dalam rentang akan muncul sebagai 1, tetapi nilai yang muncul beberapa kali akan muncul sebagai nilai pecahan yang sesuai dengan kelipatan. (yaitu nilai yang muncul 4 kali dalam data akan menghasilkan 4 nilai = 0,25).
Terakhir, fungsi SUMPRODUCT menjumlahkan semua nilai dalam larik dan mengembalikan hasilnya.
Menangani sel kosong
Salah satu cara untuk menangani sel kosong atau kosong adalah dengan menyesuaikan rumus sebagai berikut:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Dengan menggabungkan string kosong ("") ke data, kami mencegah nol berakhir di larik yang dibuat oleh COUNTIF saat ada sel kosong dalam data. Ini penting, karena nol pada pembagi akan menyebabkan rumus menampilkan kesalahan # DIV / 0. Ini berfungsi karena menggunakan string kosong ("") untuk kriteria akan menghitung sel kosong.
Namun, meskipun versi rumus ini tidak akan menampilkan kesalahan # DIV / 0 saat dengan sel kosong, itu akan menyertakan sel kosong dalam hitungan. Jika Anda ingin mengecualikan sel kosong dari hitungan, gunakan:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Ini memiliki efek membatalkan hitungan sel kosong dengan menjadikan pembilang nol untuk hitungan terkait.
Performa Lambat?
Ini adalah rumus yang keren dan elegan, tetapi menghitung jauh lebih lambat daripada rumus yang menggunakan FREQUENCY untuk menghitung nilai unik. Untuk kumpulan data yang lebih besar, Anda mungkin ingin beralih ke rumus yang didasarkan pada fungsi FREQUENCY. Berikut adalah rumus untuk nilai numerik, dan satu untuk nilai teks.
