Formula umum
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Ringkasan
Untuk menghitung nilai teks unik dalam suatu rentang, Anda dapat menggunakan rumus yang menggunakan beberapa fungsi: FREQUENCY, MATCH, ROW dan SUMPRODUCT. Dalam contoh yang diperlihatkan, rumus di F5 adalah:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
yang mengembalikan 4, karena ada 4 nama unik di B5: B14.
Catatan: Cara lain untuk menghitung nilai unik adalah dengan menggunakan fungsi COUNTIF. Ini adalah rumus yang jauh lebih sederhana, tetapi dapat berjalan lambat pada kumpulan data yang besar. Dengan Excel 365, Anda dapat menggunakan rumus yang lebih sederhana dan lebih cepat berdasarkan UNIQUE.
Penjelasan
Rumus ini lebih rumit daripada rumus serupa yang menggunakan FREQUENCY untuk menghitung nilai numerik unik karena FREQUENCY tidak berfungsi dengan nilai non-numerik. Akibatnya, sebagian besar rumus hanya mengubah data non-numerik menjadi data numerik yang dapat ditangani FREQUENCY.
Bekerja dari dalam ke luar, fungsi MATCH digunakan untuk mendapatkan posisi setiap item yang muncul di data:
MATCH(B5:B14,B5:B14,0)
Hasil dari MATCH adalah larik seperti ini:
(1;1;1;4;4;6;6;6;9;9)
Karena MATCH selalu mengembalikan posisi pertandingan pertama, nilai yang muncul lebih dari sekali dalam data mengembalikan posisi yang sama. Misalnya, karena "Jim" muncul 3 kali dalam daftar, dia muncul dalam larik ini 3 kali sebagai angka 1.
Larik ini dimasukkan ke FREQUENCY sebagai argumen data_array . The bins_array argumen dibangun dari bagian rumus:
ROW(B5:B14)-ROW(B5)+1)
yang membangun daftar nomor berurutan untuk setiap nilai dalam data:
(1;2;3;4;5;6;7;8;9;10)
Pada titik 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 larik angka yang menunjukkan hitungan untuk setiap angka dalam larik data, diatur menurut bin. Jika suatu angka sudah dihitung, FREQUENCY akan mengembalikan nol. Ini adalah fitur utama dalam pengoperasian rumus ini. Hasil dari FREQUENCY adalah larik seperti ini:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Catatan: FREQUENCY selalu mengembalikan larik dengan satu item lebih banyak daripada bins_array .
Sekarang kita dapat menulis ulang rumusnya seperti ini:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Selanjutnya, kami memeriksa nilai yang lebih besar dari nol (> 0), yang mengonversi angka menjadi TRUE atau FALSE, lalu gunakan negatif ganda (-) untuk mengonversi nilai TRUE dan FALSE menjadi 1 dan 0. Sekarang kita punya:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Terakhir, SUMPRODUCT hanya menjumlahkan angka-angka itu dan mengembalikan total, yang dalam hal ini adalah 4.
Menangani sel kosong
Sel kosong dalam rentang akan menyebabkan rumus mengembalikan kesalahan # N / A. Untuk menangani sel kosong, Anda bisa menggunakan rumus array yang lebih rumit yang menggunakan fungsi IF untuk memfilter nilai kosong:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Catatan: menambahkan IF membuat ini menjadi rumus array yang memerlukan control-shift-enter.
Untuk informasi lebih lanjut, lihat halaman ini.
Cara lain untuk menghitung nilai unik
Jika Anda memiliki Excel 365, Anda dapat menggunakan fungsi UNIK untuk menghitung nilai unik dengan rumus yang lebih sederhana.
Tabel pivot juga merupakan cara terbaik untuk menghitung nilai unik.
