Formula generik
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Ringkasan
Untuk mengira nilai teks unik dalam julat, anda boleh menggunakan formula yang menggunakan beberapa fungsi: FREQUENCY, MATCH, ROW dan SUMPRODUCT. Dalam contoh yang ditunjukkan, formula di F5 adalah:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
yang mengembalikan 4, kerana terdapat 4 nama unik di B5: B14.
Catatan: Cara lain untuk mengira nilai unik adalah dengan menggunakan fungsi COUNTIF. Ini adalah formula yang lebih mudah, tetapi dapat berjalan perlahan pada set data yang besar. Dengan Excel 365, anda boleh menggunakan formula yang lebih ringkas dan pantas berdasarkan UNIK.
Penjelasan
Formula ini lebih rumit daripada formula serupa yang menggunakan FREQUENCY untuk mengira nilai numerik yang unik kerana FREQUENCY tidak berfungsi dengan nilai bukan angka. Akibatnya, sebahagian besar formula hanya mengubah data bukan angka menjadi data berangka yang dapat dikendalikan oleh FREQUENCY.
Berfungsi dari dalam ke luar, fungsi MATCH digunakan untuk mendapatkan posisi setiap item yang muncul dalam data:
MATCH(B5:B14,B5:B14,0)
Hasil dari MATCH adalah susunan seperti ini:
(1;1;1;4;4;6;6;6;9;9)
Oleh kerana MATCH selalu mengembalikan kedudukan pertandingan pertama, nilai yang muncul lebih dari sekali dalam data mengembalikan kedudukan yang sama. Sebagai contoh, kerana "Jim" muncul 3 kali dalam senarai, dia muncul dalam susunan ini 3 kali sebagai nombor 1.
Susunan ini dimasukkan ke dalam FREQUENCY sebagai argumen data_array . The bins_array hujah dibina daripada bahagian ini formula:
ROW(B5:B14)-ROW(B5)+1)
yang membina senarai nombor berturutan untuk setiap nilai dalam data:
(1;2;3;4;5;6;7;8;9;10)
Pada ketika ini, FREQUENCY dikonfigurasi seperti ini:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY mengembalikan array nombor yang menunjukkan kiraan untuk setiap nombor dalam array data, yang disusun oleh tong sampah. Apabila nombor telah dihitung, FREQUENCY akan mengembalikan sifar. Ini adalah ciri utama dalam operasi formula ini. Hasil dari FREQUENCY adalah susunan seperti ini:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Catatan: FREQUENCY selalu mengembalikan array dengan satu item lagi daripada bins_array .
Kita sekarang boleh menulis semula formula seperti ini:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Seterusnya, kami memeriksa nilai yang lebih besar daripada sifar (> 0), yang menukar nombor menjadi BENAR atau SALAH, kemudian menggunakan dua negatif (-) untuk menukar nilai BENAR dan SALAH menjadi 1s dan 0s. Sekarang kita mempunyai:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Akhirnya, SUMPRODUCT hanya menambah nombor dan mengembalikan jumlahnya, yang dalam kes ini adalah 4.
Mengendalikan sel kosong
Sel kosong dalam julat akan menyebabkan formula mengembalikan ralat # N / A. Untuk menangani sel kosong, anda boleh menggunakan formula array yang lebih rumit yang menggunakan fungsi IF untuk menyaring nilai kosong:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Catatan: menambahkan IF menjadikannya menjadi formula larik yang memerlukan kawalan-shift-enter.
Untuk maklumat lebih lanjut, lihat halaman ini.
Kaedah lain untuk mengira nilai unik
Sekiranya anda mempunyai Excel 365, anda boleh menggunakan fungsi UNIK untuk menghitung nilai unik dengan formula yang lebih mudah.
Jadual pangsi juga merupakan kaedah terbaik untuk mengira nilai unik.
