Formula generik
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Ringkasan
Sekiranya anda ingin menguji sel untuk melihat apakah ia mengandungi semua item dalam senarai, anda boleh melakukannya dengan formula yang menggunakan fungsi SEARCH, dengan bantuan fungsi ISNUMBER, SUMPRODUCT, dan COUNTA.
Konteks
Katakan anda mempunyai senarai rentetan teks dalam julat B5: B8, dan anda ingin mengetahui apakah sel-sel ini mengandungi semua perkataan dalam julat lain, E5: E7.
Anda boleh membina formula yang menggunakan penyataan IF bersarang untuk memeriksa setiap item, tetapi itu tidak akan bertambah baik jika anda mempunyai banyak perkara yang perlu dicari. Setiap kali anda menambah perkataan untuk dicari, anda perlu menambahkan IF bersarang lain dan menyesuaikan tanda kurung.
Penyelesaian
Penyelesaiannya adalah dengan membuat formula yang mengira semua perlawanan sekaligus. Setelah memperolehnya, kita hanya membandingkan jumlah itu dengan jumlah item yang kita cari. Sekiranya sesuai, kita tahu sel mengandungi semua item.
Dalam contoh yang ditunjukkan, formula yang kita gunakan adalah:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Penjelasan
Kuncinya ialah coretan ini:
ISNUMBER(SEARCH(things,B5)
Ini berdasarkan formula lain (dijelaskan secara terperinci di sini) yang hanya memeriksa sel untuk satu substring. Sekiranya sel mengandungi substring, formula mengembalikan BENAR. Sekiranya tidak, formula mengembalikan PALSU.
Walau bagaimanapun, jika kita memberikan senarai perkara yang sama dengan formula yang sama (dalam hal ini, kita menggunakan julat bernama yang disebut "benda", E5: E7) ia akan memberi kita kembali senarai nilai BENAR / SALAH, satu untuk setiap item dalam . Hasilnya adalah susunan yang kelihatan seperti ini:
(BENAR; BENAR; BENAR)
Di mana setiap BENAR mewakili item yang dijumpai, dan masing-masing SALAH mewakili item yang tidak dijumpai.
Kita boleh memaksa nilai BENAR / SALAH menjadi 1s dan 0s dengan negatif ganda (-, juga disebut double unary):
--ISNUMBER(SEARCH(things,B5))
yang menghasilkan susunan seperti ini:
(1; 1; 1)
Seterusnya, kami memproses susunan ini dengan SUMPRODUCT, yang akan memberikan jumlah keseluruhan kepada kami. Sekiranya jumlah ini sama dengan jumlah item dalam julat "benda" yang dinamakan, kita tahu bahawa kita telah menemui semua perkara dan dapat mengembalikan BENAR. Cara kami melakukan ini adalah membandingkan kedua nombor secara langsung. Kami mendapat jumlah sel kosong dalam "perkara" menggunakan COUNTA:
=COUNTA(things)
Dengan senarai keras
Tidak ada syarat anda menggunakan rangkaian untuk senarai perkara anda. Sekiranya anda hanya mencari sebilangan kecil perkara, anda boleh menggunakan senarai dalam format array, yang dipanggil pemalar array. Contohnya, jika anda hanya mencari warna merah, biru, dan hijau, anda boleh menggunakan ("merah", "biru", "hijau") seperti ini:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
