Formula generik
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Ringkasan
Untuk menguji sel untuk melihat apakah ia mengandungi salah satu daripada banyak rentetan, anda boleh menggunakan formula berdasarkan fungsi SEARCH, ISNUMBER dan SUMPRODUCT. Rumus dalam C5, disalin, adalah:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
di mana perkara adalah julat bernama E5: E9.
Penjelasan
Kami mahu menguji setiap sel dalam B5: B11 untuk melihat jika ia mengandungi apa-apa daripada tali-tali dalam julat bernama perkara (E5: E9). Rumus yang kami gunakan di C5, disalin, adalah:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Formula ini berdasarkan formula (dijelaskan di sini) yang memeriksa sel untuk satu substring. Sekiranya sel mengandungi substring, formula mengembalikan BENAR. Sekiranya tidak, formula mengembalikan PALSU:
ISNUMBER(SEARCH(things,B5))
Walau bagaimanapun, dalam kes ini, kami memberikan senarai rentetan SEARCH. Oleh kerana terdapat 5 rentetan perkara , SEARCH mengembalikan 5 hasil dalam array seperti ini:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Apabila SEARCH menemui rentetan, ia mengembalikan kedudukan rentetan itu. Sekiranya SEARCH tidak menemui rentetan, ia mengembalikan # NILAI! kesilapan. Kerana "kuning" muncul sebagai kata pertama dalam B5, kita melihat angka 1. Oleh kerana rentetan lain tidak dijumpai, 4 item lain adalah kesilapan.
Susunan ini dikembalikan terus ke fungsi ISNUMBER. ISNUMBER kemudian mengembalikan pelbagai nilai BENAR / SALAH:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Sekiranya kita mempunyai satu BENAR dalam array, kita tahu sel mengandungi sekurang-kurangnya satu rentetan yang kita cari. Cara termudah untuk memeriksa BENAR adalah dengan menambahkan semua nilai bersama-sama. Kita boleh melakukannya dengan SUMPRODUCT, tetapi pertama-tama kita perlu memaksa nilai TRUE / FALSE ke 1s dan 0s dengan double negatif (-) seperti ini:
--ISNUMBER(SEARCH(things,B5))
Ini menghasilkan susunan baru yang hanya mengandungi 1s dan 0s:
(1;0;0;0;0)
dihantar terus ke SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Dengan hanya satu array untuk diproses, SUMPRODUCT menambahkan item dalam array dan mengembalikan hasilnya. Apa-apa hasil bukan sifar bermaksud kita mempunyai "hit", jadi kita tambahkan> 0 untuk memaksa hasil akhir BENAR atau SALAH:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Dengan senarai keras
Tidak perlu menggunakan julat untuk senarai rentetan yang dicari. Anda juga boleh menggunakan pemalar array. Contohnya, untuk memeriksa "merah", "biru", dan "hijau", anda boleh menggunakan formula seperti ini:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Mencegah pertandingan palsu
Satu masalah dengan pendekatan ini ialah anda mungkin mendapat padanan palsu dari substring yang muncul dalam perkataan 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 penggodaman cepat, 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 dengan tanda baca.
Sekiranya anda memerlukan penyelesaian yang lebih tepat, satu pilihan adalah menormalkan teks terlebih dahulu di kolom penolong, berhati-hati juga menambah ruang depan dan ketinggalan. Kemudian anda menggunakan formula di halaman ini pada teks yang dihasilkan.
