Formula umum
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Ringkasan
Untuk menyorot sel yang berisi salah satu dari banyak string teks, Anda bisa menggunakan rumus yang didasarkan pada fungsi ISNUMBER dan SEARCH, bersama dengan fungsi SUMPRODUCT. Dalam contoh yang diperlihatkan, pemformatan bersyarat yang diterapkan ke B4: B11 didasarkan pada rumus ini:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Penjelasan
Bekerja dari dalam ke luar, bagian dari rumus ini mencari setiap sel di B4: B11 untuk semua nilai dalam rentang bernama "things":
--ISNUMBER(SEARCH(things,B4)
Fungsi SEARCH mengembalikan posisi nilai jika ditemukan, dan kesalahan #VALUE jika tidak ditemukan. Untuk B4, hasilnya ditampilkan dalam array seperti ini:
(8;#VALUE!;#VALUE!)
Fungsi ISNUMBER mengubah semua hasil menjadi TRUE atau FALSE:
(TRUE;FALSE;FALSE)
Negatif ganda di depan ISNUMBER memaksa TRUE / FALSE menjadi 1/0:
(1;0;0)
Fungsi SUMPRODUCT kemudian menambahkan hasil, yang diuji terhadap nol:
=SUMPRODUCT((1;0;0))>0
Hasil bukan nol berarti setidaknya satu nilai ditemukan, sehingga rumus mengembalikan TRUE, memicu aturan.
Abaikan hal-hal kosong
Untuk mengabaikan sel kosong dalam rentang bernama "hal", Anda dapat mencoba rumus yang dimodifikasi seperti ini:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Ini berfungsi selama nilai teks yang Anda uji tidak berisi string "FALSE". Jika ya, Anda dapat memperluas fungsi IF untuk menyertakan nilai jika salah diketahui tidak terjadi dalam teks (yaitu "zzzz", "####", dll.)
Opsi peka huruf besar-kecil
SEARCH tidak peka huruf besar / kecil. Untuk memeriksa kasus juga, ganti SEARCH dengan FIND seperti:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Mencegah kecocokan palsu
Satu masalah dengan pendekatan ini adalah Anda mungkin melihat kecocokan palsu yang disebabkan oleh substring yang muncul di dalam kata yang lebih panjang. Misalnya, jika Anda mencoba mencocokkan "dr", Anda juga dapat menemukan "Andrea", "drink", "dry", dll. Karena "dr" muncul di dalam kata ini. Ini terjadi karena SEARCH secara otomatis melakukan kecocokan "berisi".
Untuk perbaikan sebagian, Anda dapat menambahkan spasi di sekitar kata pencarian (yaitu "dr", atau "dr") untuk menghindari menangkap "dr" di kata lain. Tapi ini akan gagal jika "dr" muncul pertama atau terakhir dalam sel, atau muncul di sebelah tanda baca. Ini sebagian dapat diatasi dengan menambahkan spasi juga di sekitar teks asli. Untuk menambahkan spasi ke awal dan akhir keduanya secara bersamaan, Anda dapat mencoba rumus seperti ini:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Namun, ini tidak akan memperbaiki masalah yang disebabkan oleh tanda baca.
Jika Anda memerlukan solusi yang lebih lengkap, salah satu opsinya adalah menormalkan teks terlebih dahulu di kolom pembantu, berhati-hatilah untuk juga menambahkan spasi di depan dan di belakang. Kemudian Anda dapat mencari seluruh kata yang dikelilingi oleh spasi.
