Formula generik
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Ringkasan
Untuk menyoroti sel yang mengandungi salah satu daripada banyak rentetan teks, anda boleh menggunakan formula berdasarkan fungsi ISNUMBER dan SEARCH, bersama dengan fungsi SUMPRODUCT. Dalam contoh yang ditunjukkan, pemformatan bersyarat yang digunakan untuk B4: B11 berdasarkan formula ini:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Penjelasan
Berfungsi dari dalam ke luar, bahagian formula ini mencari setiap sel di B4: B11 untuk semua nilai dalam julat "benda" yang dinamakan:
--ISNUMBER(SEARCH(things,B4)
Fungsi SEARCH mengembalikan kedudukan nilai jika dijumpai, dan ralat # NILAI jika tidak dijumpai. Untuk B4, hasilnya kembali dalam susunan seperti ini:
(8;#VALUE!;#VALUE!)
Fungsi ISNUMBER mengubah semua hasil menjadi BENAR atau SALAH:
(TRUE;FALSE;FALSE)
Negatif berganda di hadapan ISNUMBER memaksa TRUE / FALSE ke 1/0:
(1;0;0)
Fungsi SUMPRODUCT kemudian menambahkan hasilnya, yang diuji terhadap sifar:
=SUMPRODUCT((1;0;0))>0
Apa-apa hasil bukan sifar bermakna sekurang-kurangnya satu nilai dijumpai, jadi formula mengembalikan BENAR, memicu peraturan.
Abaikan perkara kosong
Untuk mengabaikan sel kosong dalam julat "benda" yang dinamakan, anda boleh mencuba formula yang diubah seperti ini:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Ini berfungsi selagi nilai teks yang anda uji tidak mengandungi rentetan "FALSE". Sekiranya benar, anda boleh memperluas fungsi IF untuk memasukkan nilai jika salah diketahui tidak berlaku dalam teks (iaitu "zzzz", "####", dll.)
Pilihan peka kes
PENCARIAN tidak peka dengan kes. Untuk memeriksa kes juga, ganti SEARCH dengan FIND seperti:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Mencegah pertandingan palsu
Satu masalah dengan pendekatan ini adalah anda mungkin melihat padanan palsu yang disebabkan oleh substring yang muncul di dalam kata-kata yang lebih panjang. Contohnya, jika anda mencocokkan "dr", anda mungkin juga menemui "Andrea", "minuman", "kering", dan lain-lain kerana "dr" terdapat di dalam kata-kata ini. Ini berlaku kerana SEARCH secara automatik melakukan pertandingan "mengandung".
Untuk memperbaiki separa, anda boleh menambahkan ruang di sekitar kata carian (iaitu "dr", atau "dr") untuk mengelakkan menangkap "dr" dengan kata lain. Tetapi ini akan gagal jika "dr" muncul pertama atau terakhir di sel, atau muncul di sebelah tanda baca. Ini dapat diatasi sebahagiannya dengan menambahkan ruang di sekitar teks asal. Untuk menambahkan ruang pada permulaan dan akhir keduanya pada masa yang sama, anda boleh mencuba formula seperti ini:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Namun, ini tidak akan menyelesaikan masalah yang disebabkan oleh tanda baca.
Sekiranya anda memerlukan penyelesaian yang lebih lengkap, satu pilihan adalah menormalkan teks terlebih dahulu di kolom penolong, berhati-hati juga menambah ruang depan dan belakang. Kemudian anda boleh mencari keseluruhan perkataan yang dikelilingi oleh spasi.
