Formula generik
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Ringkasan
Untuk mengira nilai teks unik dalam julat dengan kriteria, anda boleh menggunakan formula larik berdasarkan fungsi FREQUENCY dan MATCH. Dalam contoh yang ditunjukkan, formula dalam G6 adalah:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
yang mengembalikan 3, kerana tiga orang berbeza mengusahakan projek Omega.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter.
Penjelasan
Ini adalah rumus rumit yang menggunakan FREQUENCY untuk mengira nilai berangka yang diperoleh dengan fungsi MATCH. Berfungsi dari dalam ke luar, fungsi MATCH digunakan untuk mendapatkan posisi setiap nilai yang muncul dalam data:
MATCH(B5:B11,B5:B11,0)
Hasil dari MATCH adalah susunan seperti ini:
(1;1;3;1;1;6;7)
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 4 kali dalam senarai, dia muncul dalam susunan ini 4 kali sebagai nombor 1.
Di luar fungsi MATCH, fungsi IF digunakan untuk menerapkan kriteria, yang dalam hal ini melibatkan pengujian jika projek itu "omega" (dari sel G5):
IF(C5:C11=G5 // filter on "omega"
Fungsi IF bertindak seperti penapis, hanya membenarkan nilai dari MATCH melewati jika dikaitkan dengan "omega". Hasilnya adalah susunan seperti ini:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Array yang disaring dihantar terus ke fungsi FREQUENCY sebagai argumen data_array . Seterusnya, fungsi ROW digunakan untuk membina senarai nombor yang berurutan untuk setiap nilai dalam data:
ROW(B3:B12)-ROW(B3)+1
Ini membuat susunan seperti ini:
(1;2;3;4;5;6;7;8;9;10)
yang menjadi hujah bins_array dalam FILTER. Pada ketika ini, kami mempunyai:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY mengembalikan array nombor yang menunjukkan kiraan untuk setiap nilai dalam array data, yang disusun oleh tong sampah. Apabila nombor telah dihitung, FREQUENCY akan mengembalikan sifar. Hasil dari FREQUENCY adalah susunan seperti ini:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Catatan: FREQUENCY selalu mengembalikan array dengan satu item lagi daripada bins_array .
Pada ketika ini, kita dapat menulis semula formula seperti ini:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Kami memeriksa nilai yang lebih besar daripada sifar, yang menukar nombor menjadi BENAR atau SALAH:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Kemudian kita menggunakan negatif dua untuk memaksa nilai logik menjadi 1s dan 0s:
=SUM((1;0;0;0;0;1;1;0))
Akhirnya, fungsi SUM mengembalikan 3 sebagai hasil akhir.
Catatan: ini adalah formula larik dan mesti dimasukkan menggunakan Control + Shift + Enter.
Mengendalikan sel kosong dalam julat
Sekiranya ada sel dalam julat yang kosong, anda perlu menyesuaikan formula untuk mengelakkan sel kosong dihantar ke fungsi MATCH, yang akan menyebabkan kesalahan. Anda boleh melakukan ini dengan menambahkan fungsi IF bersarang lain 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
Sekiranya anda mempunyai dua kriteria, anda boleh memperluas logik formula dengan menambahkan IF bersarang lain:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Mana c1 = criteria1, c2 = criteria2 dan vals = nilai pelbagai.
Dengan logik boolean
Dengan logik boolean, anda dapat mengurangkan IF bersarang:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Ini menjadikannya lebih mudah untuk menambah dan menguruskan kriteria tambahan.
