Formula generik
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
Ringkasan
Untuk mengira sel yang mengandungi x atau y, anda boleh menggunakan formula berdasarkan fungsi SUMPRODUCT. Dalam contoh yang ditunjukkan, formula dalam sel F5 adalah:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
Ini adalah penyelesaian formula sel tunggal, yang dijelaskan di bawah. Anda juga boleh menggunakan formula yang lebih mudah berdasarkan kolum penolong, yang juga dijelaskan di bawah.
Penjelasan
Apabila anda mengira sel dengan "OR logik", anda perlu berhati-hati agar tidak mengira dua kali ganda. Sebagai contoh, jika anda mengira sel yang mengandungi "abc" atau "def", anda tidak boleh menambahkan dua fungsi COUNTIF, kerana anda mungkin mengira dua sel yang mengandungi "abc" dan "def".
Penyelesaian sel tunggal
Untuk satu formula, anda boleh menggunakan SUMPRODUCT dengan ISNUMBER + FIND. Formula dalam F5 adalah:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
Formula ini berdasarkan formula yang dijelaskan di sini yang menempatkan teks di dalam sel:
ISNUMBER(FIND("abc",B5:B11)
Apabila diberi julat sel, coretan ini akan mengembalikan array nilai BENAR / SALAH, satu nilai untuk setiap sel julat. Oleh kerana kita menggunakan ini dua kali (sekali untuk "abc" dan sekali untuk "def"), kita akan mendapat dua tatasusunan.
Seterusnya, kami menambahkan tatasusunan ini bersama-sama (dengan +), yang menghasilkan susunan nombor tunggal yang baru. Setiap nombor dalam susunan ini adalah hasil penambahan nilai BENAR dan SALAH dalam dua tatasusunan bersamaan. Dalam contoh yang ditunjukkan, susunan yang dihasilkan kelihatan seperti ini:
(2;0;2;0;1;0;2)
Kita perlu menambahkan nombor ini, tetapi kita tidak mahu mengira dua kali ganda. Oleh itu, kita perlu memastikan nilai yang lebih besar daripada sifar dikira sekali sahaja. Untuk melakukan itu, kita memaksa semua nilai menjadi BENAR atau SALAH dengan "> 0", kemudian memaksa ke 1/0 dengan negatif ganda (-).
Akhirnya, SUMPRODUCT mengembalikan jumlah semua nilai dalam tatasusunan.
Penyelesaian lajur penolong
Dengan kolom penolong untuk memeriksa setiap sel secara individu, masalahnya kurang rumit. Kita boleh menggunakan COUNTIF dengan dua nilai (disediakan sebagai "pemalar array"). Formula dalam C5 adalah:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
COUNTIF akan mengembalikan array yang mengandungi dua item: kiraan untuk "abc" dan kiraan untuk "def". Untuk mengelakkan pengiraan berganda, kami menambahkan item ke atas dan kemudian memaksa hasilnya menjadi BENAR / SALAH dengan "> 0". Akhirnya, kita menukar nilai BENAR / SALAH menjadi 1 dan 0 dengan negatif dua kali ganda (-).
Hasil akhir adalah sama ada 1 atau 0 untuk setiap sel. Untuk mendapatkan jumlah semua sel dalam julat, jumlahkan kolom pembantu.
