Formula generik
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Ringkasan
Untuk menguji sel untuk salah satu daripada banyak rentetan, sementara tidak termasuk yang lain, anda boleh menggunakan formula berdasarkan fungsi SEARCH, ISNUMBER, dan SUMPRODUCT. Dalam contoh yang ditunjukkan formula di C5 adalah:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
di mana "include" adalah julat bernama E5: E9, dan "exclude" adalah julat bernama G5: G6.
Penjelasan
Pada intinya, formula ini menggunakan fungsi SEARCH untuk mencari pelbagai rentetan di dalam sel. Di dalam SUMPRODUCT kiri, SEARCH mencari semua rentetan dalam julat bernama "include".
Di SUMPRODUCT yang betul, SEARCH mencari semua rentetan dalam julat bernama "exclude".
Di kedua-dua bahagian formula, SEARCH mengembalikan kedudukan berangka ketika rentetan dijumpai, dan kesalahan ketika tidak. Fungsi ISNUMBER menukar nombor menjadi BENAR dan kesalahan menjadi FALSE, dan negatif berganda menukar nilai BENAR BENAR menjadi 1 dan 0.
Hasilnya pada ketika ini kelihatan seperti ini:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Kemudian:
=(1>0)*(0=0) =TRUE*TRUE =1
Catatan: formula ini mengembalikan sama ada 1 atau sifar, yang ditangani seperti BENAR dan SALAH dalam formula, pemformatan bersyarat, atau pengesahan data.
