Formula umum
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Ringkasan
Untuk menguji sel untuk melihat apakah itu berisi salah satu dari banyak string, Anda bisa menggunakan rumus berdasarkan fungsi SEARCH, ISNUMBER dan SUMPRODUCT. Rumus di C5, disalin, adalah:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
di mana hal - hal tersebut dinamai rentang E5: E9.
Penjelasan
Kami ingin menguji setiap sel di B5: B11 untuk melihat apakah itu berisi salah satu string dalam berbagai hal bernama (E5: E9). Rumus yang kami gunakan di C5, disalin, adalah:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Rumus ini didasarkan pada rumus (dijelaskan di sini) yang memeriksa sel untuk satu substring. Jika sel berisi substring, rumus mengembalikan TRUE. Jika tidak, rumus mengembalikan FALSE:
ISNUMBER(SEARCH(things,B5))
Namun, dalam kasus ini, kami memberikan SEARCH daftar string. Karena ada 5 string dalam berbagai hal , SEARCH mengembalikan 5 hasil dalam array seperti ini:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Ketika SEARCH menemukan string, ia mengembalikan posisi string itu. Jika SEARCH tidak menemukan string, ini mengembalikan #VALUE! kesalahan. Karena "kuning" muncul sebagai kata pertama di B5, kita melihat a 1. Karena string lainnya tidak ditemukan, 4 item lainnya salah.
Larik ini dikembalikan langsung ke fungsi ISNUMBER. ISNUMBER kemudian mengembalikan larik nilai TRUE / FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Jika kita memiliki bahkan satu TRUE dalam larik, kita tahu sel berisi setidaknya satu string yang kita cari. Cara termudah untuk memeriksa BENAR adalah dengan menjumlahkan semua nilai. Kita dapat melakukannya dengan SUMPRODUCT, tetapi pertama-tama kita perlu memaksa nilai TRUE / FALSE menjadi 1 dan 0 dengan negatif ganda (-) seperti ini:
--ISNUMBER(SEARCH(things,B5))
Ini menghasilkan array baru yang hanya berisi 1 dan 0:
(1;0;0;0;0)
dikirim langsung ke SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Dengan hanya satu larik untuk diproses, SUMPRODUCT menambahkan item dalam larik dan mengembalikan hasil. Setiap hasil bukan nol berarti kita memiliki "hit", jadi kita menambahkan> 0 untuk memaksa hasil akhir TRUE atau FALSE:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Dengan daftar hard-coded
Tidak perlu menggunakan rentang untuk mencari daftar string. Anda juga dapat menggunakan konstanta array. Misalnya, untuk mencentang "merah", "biru", dan "hijau", Anda dapat menggunakan rumus seperti ini:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Mencegah kecocokan palsu
Satu masalah dengan pendekatan ini adalah Anda mungkin mendapatkan kecocokan palsu dari 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 peretasan cepat, 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 dengan tanda baca.
Jika Anda membutuhkan solusi yang lebih akurat, salah satu opsinya adalah menormalkan teks terlebih dahulu di kolom pembantu, berhati-hatilah untuk juga menambahkan spasi di depan dan di belakang. Kemudian Anda menggunakan rumus di halaman ini pada teks yang dihasilkan.
