Formula generik
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Ringkasan
Untuk mengira nilai unik dengan satu atau lebih syarat, anda boleh menggunakan formula berdasarkan UNIK dan FILTER. Dalam contoh yang ditunjukkan, formula dalam H7 adalah:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
yang mengembalikan 3, kerana terdapat tiga nama unik dalam B6: B15 yang dikaitkan dengan projek Omega.
Catatan: formula ini memerlukan Formula Array Dinamik, hanya tersedia di Excel 365. Dengan versi Excel yang lebih lama, anda boleh menggunakan formula alternatif yang lebih kompleks.
Penjelasan
Pada intinya, formula ini menggunakan fungsi UNIK untuk mengekstrak nilai unik, dan fungsi FILTER menerapkan kriteria.
Berfungsi dari dalam ke luar, fungsi FILTER digunakan untuk menerapkan kriteria dan hanya mengekstrak nama yang dikaitkan dengan projek "Omega":
FILTER(B6:B15,C6:C15=H6) // Omega names only
Hasil dari FILTER adalah susunan seperti ini:
("Jim";"Jim";"Carl";"Sue";"Carl")
Seterusnya, fungsi UNIK digunakan untuk membuang pendua:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
yang menghasilkan susunan baru seperti ini:
("Jim";"Carl";"Sue") // after UNIQUE
Pada ketika ini, kami mempunyai senarai nama unik yang berkaitan dengan Omega, dan kami hanya perlu menghitungnya. Atas sebab yang dijelaskan di bawah, kami melakukan ini dengan fungsi LEN dan fungsi SUM. Untuk memperjelas, kami akan menulis semula formula untuk memasukkan senarai unik:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
Fungsi LEN mendapat panjang setiap item dalam senarai, dan mengembalikan pelbagai panjang:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Seterusnya, kami memeriksa sama ada panjang lebih besar daripada sifar:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
Dan gunakan dua kali ganda negatif untuk memaksakan nilai BENAR dan SALAH kepada 1 dan 0:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Akhirnya, kami menambahkan hasilnya dengan fungsi SUM:
=SUM((1;1;1)) // returns 3
Susunan ini dihantar terus ke fungsi COUNTA, yang mengembalikan kiraan terakhir:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Perhatikan bahawa kerana kami memeriksa panjang setiap item yang dikembalikan oleh UNIK, sel kosong atau kosong yang memenuhi kriteria diabaikan. Formula ini bersifat dinamik dan akan dikira semula dengan segera sekiranya data sumber diubah.
Kira unik dengan pelbagai kriteria
Untuk mengira nilai unik berdasarkan beberapa kriteria, dapat memperluas logik "sertakan" di dalam FILTER. Sebagai contoh, untuk mengira nama unik untuk projek Omega pada bulan Jun sahaja, gunakan:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Ini adalah contoh penggunaan logik boolean untuk menerapkan lebih dari satu syarat. Pendekatannya dijelaskan dengan lebih terperinci di sini.
Untuk lebih jelasnya, lihat video latihan ini: Cara menapis dengan pelbagai kriteria.
NEGARA
Adalah mungkin untuk menulis formula yang lebih mudah yang menjawab fungsi COUNTA. Walau bagaimanapun, satu peringatan penting ialah COUNTA akan kembali 1 apabila tidak ada nilai yang sepadan. Ini kerana fungsi FILTER mengembalikan ralat apabila tidak ada data yang sesuai dengan kriteria, dan kesalahan ini akhirnya dihitung oleh fungsi COUNTA. Formula COUNTA asas kelihatan seperti ini:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Sekali lagi, formula ini akan mengembalikan 1 apabila tidak ada data yang sepadan. Ia juga akan merangkumi sel kosong yang memenuhi kriteria. Formula berdasarkan LEN dan SUM adalah pilihan yang lebih baik.
Tiada susunan dinamik
Sekiranya anda menggunakan versi Excel yang lebih lama tanpa sokongan array dinamik, anda boleh menggunakan formula yang lebih kompleks. Untuk perbincangan yang lebih umum mengenai alternatif array dinamik, lihat: Alternatif untuk Formula Array Dinamik.
