Formula umum
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Ringkasan
Untuk menguji sel untuk salah satu dari banyak string, sambil mengecualikan yang lain, Anda bisa menggunakan rumus berdasarkan fungsi SEARCH, ISNUMBER, dan SUMPRODUCT. Pada contoh yang diperlihatkan rumus di C5 adalah:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
dengan "sertakan" adalah rentang bernama E5: E9, dan "kecualikan" adalah rentang bernama G5: G6.
Penjelasan
Pada intinya, rumus ini menggunakan fungsi SEARCH untuk mencari beberapa string di dalam sel. Di dalam SUMPRODUCT kiri, SEARCH mencari semua string dalam rentang bernama "sertakan".
Di SUMPRODUCT yang tepat, SEARCH mencari semua string dalam rentang bernama "kecualikan".
Di kedua bagian rumus, SEARCH mengembalikan posisi numerik saat string ditemukan, dan error jika tidak. Fungsi ISNUMBER mengonversi angka menjadi TRUE dan kesalahan menjadi FALSE, dan negatif ganda mengonversi nilai TRUE FALSE menjadi 1 dan 0.
Hasilnya saat ini terlihat seperti ini:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Kemudian:
=(1>0)*(0=0) =TRUE*TRUE =1
Catatan: rumus ini mengembalikan 1 atau nol, yang ditangani seperti TRUE dan FALSE dalam rumus, pemformatan bersyarat, atau validasi data.
