Formula generik
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Ringkasan
Sebagai rumusan jika sel mengandungi satu rentetan teks atau yang lain (iaitu mengandungi "kucing" atau "tikus") anda boleh menggunakan fungsi SUMPRODUCT bersama dengan ISNUMBER + SEARCH atau FIND. Dalam contoh yang ditunjukkan, formula dalam sel F5 adalah:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
yang mengembalikan jumlah nilai dalam C4: C8 apabila sel di B4: B8 mengandungi "kucing" atau "tikus".
Penjelasan
Apabila anda menjumlahkan sel dengan kriteria "ATAU", anda harus berhati-hati untuk tidak mengira dua kali ganda apabila ada kemungkinan kedua-dua kriteria tersebut akan kembali benar. Dalam contoh yang ditunjukkan, kami ingin menjumlahkan nilai di Lajur C apabila sel di lajur B mengandungi "kucing" atau "tikus". Kami tidak dapat menggunakan SUMIF dengan dua kriteria, kerana SUMIFS didasarkan pada logik DAN. Dan jika kita cuba menggunakan dua SUMIFS (iaitu SUMIFS + SUMIFS) kita akan mengira dua kali ganda kerana terdapat sel yang mengandungi "kucing" dan "tikus".
Sebaliknya, kami menggunakan formula seperti ini:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Inti formula ini berdasarkan formula yang dijelaskan di sini yang menempatkan teks di dalam sel dengan ISNUMBER dan CARI:
ISNUMBER(SEARCH("text",range)
Apabila diberi julat sel, coretan ini akan mengembalikan array nilai BENAR / SALAH, satu nilai untuk setiap sel julat. Dalam formula ini, kami menggunakan coretan ini dua kali, sekali untuk "kucing" dan satu lagi untuk "tikus", jadi kami akan mendapat dua tatasusunan. Pada ketika ini, kami mempunyai:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Seterusnya, kami menambah tatasusunan ini kerana penambahan digunakan dalam algebra boolean untuk logik ATAU. Operasi matematik secara automatik memaksakan nilai BENAR dan SALAH kepada 1s dan 0s, jadi kami berakhir dengan susunan di bawah:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Setiap nombor dalam susunan ini adalah hasil penambahan nilai BENAR dan SALAH dalam dua tatasusunan bersamaan. Dalam contoh yang ditunjukkan, susunan kelihatan seperti ini:
(2;0;2;1;0)
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, kami memaksa semua nilai menjadi BENAR atau SALAH dengan memeriksa array dengan "> 0". Ini mengembalikan BENAR / SALAH:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Yang kemudian kami ubah menjadi 1/0 menggunakan negatif ganda (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
dan akhirnya:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT menggandakan unsur yang sepadan dari dua susunan itu bersama-sama dan menjumlahkan hasilnya, mengembalikan 70.
Pilihan peka kes
Fungsi SEARCH mengabaikan kes. Sekiranya anda memerlukan pilihan sensitif, ganti SEARCH dengan fungsi FIND.
