Formula umum
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Ringkasan
Untuk menghitung nilai teks unik dalam rentang dengan kriteria, Anda bisa menggunakan rumus array berdasarkan fungsi FREQUENCY dan MATCH. Pada contoh yang ditunjukkan, rumus di G6 adalah:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
yang menghasilkan 3, karena tiga orang yang berbeda mengerjakan proyek Omega.
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.
Penjelasan
Ini adalah rumus kompleks yang menggunakan FREQUENCY untuk menghitung nilai numerik yang diturunkan dengan fungsi MATCH. Bekerja dari dalam ke luar, fungsi MATCH digunakan untuk mendapatkan posisi setiap nilai yang muncul di data:
MATCH(B5:B11,B5:B11,0)
Hasil dari MATCH adalah larik seperti ini:
(1;1;3;1;1;6;7)
Karena MATCH selalu mengembalikan posisi pertandingan pertama, nilai yang muncul lebih dari sekali dalam data mengembalikan posisi yang sama. Misalnya, karena "Jim" muncul 4 kali dalam daftar, dia muncul dalam larik ini sebanyak 4 kali sebagai angka 1.
Di luar fungsi MATCH, fungsi IF digunakan untuk menerapkan kriteria, yang dalam hal ini melibatkan pengujian jika proyek tersebut adalah "omega" (dari sel G5):
IF(C5:C11=G5 // filter on "omega"
Fungsi IF bertindak seperti filter, hanya mengizinkan nilai dari MATCH untuk melewati jika dikaitkan dengan "omega". Hasilnya adalah array seperti ini:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Array yang difilter dikirim langsung ke fungsi FREQUENCY sebagai argumen data_array . Selanjutnya, fungsi ROW digunakan untuk membuat daftar angka berurutan untuk setiap nilai dalam data:
ROW(B3:B12)-ROW(B3)+1
Ini membuat array seperti ini:
(1;2;3;4;5;6;7;8;9;10)
yang menjadi argumen bins_array di FILTER. Saat ini, kami memiliki:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY mengembalikan larik angka yang menunjukkan hitungan untuk setiap nilai dalam larik data, diatur menurut bin. Jika suatu angka sudah dihitung, FREQUENCY akan mengembalikan nol. Hasil dari FREQUENCY adalah larik seperti ini:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Catatan: FREQUENCY selalu mengembalikan larik dengan satu item lebih banyak daripada bins_array .
Pada titik ini, kita dapat menulis ulang rumusnya seperti ini:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Kami memeriksa nilai yang lebih besar dari nol, yang mengonversi angka menjadi TRUE atau FALSE:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Kemudian kami menggunakan negatif ganda untuk memaksa nilai logika menjadi 1 dan 0:
=SUM((1;0;0;0;0;1;1;0))
Akhirnya, fungsi SUM mengembalikan 3 sebagai hasil akhir.
Catatan: ini adalah rumus array dan harus dimasukkan menggunakan Control + Shift + Enter.
Menangani sel kosong dalam rentang tersebut
Jika ada sel dalam rentang yang kosong, Anda harus menyesuaikan rumus untuk mencegah sel kosong diteruskan ke fungsi MATCH, yang akan menimbulkan kesalahan. Anda bisa melakukan ini dengan menambahkan fungsi IF bertumpuk lainnya untuk memeriksa sel kosong:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Dengan dua kriteria
Jika Anda memiliki dua kriteria, Anda bisa memperluas logika rumus dengan menambahkan IF bertumpuk lain:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Dimana c1 = kriteria1, c2 = kriteria2 dan vals = rentang nilai.
Dengan logika boolean
Dengan logika boolean, Anda bisa mengurangi IF bertingkat:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Ini mempermudah untuk menambahkan dan mengelola kriteria tambahan.
