Formula umum
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Ringkasan
Jika Anda ingin menguji sel untuk melihat apakah itu berisi semua item dalam daftar, Anda bisa melakukannya dengan rumus yang menggunakan fungsi SEARCH, dengan bantuan dari fungsi ISNUMBER, SUMPRODUCT, dan COUNTA.
Konteks
Misalkan Anda memiliki daftar string teks dalam rentang B5: B8, dan Anda ingin mengetahui apakah sel-sel ini berisi semua kata dalam rentang lain, E5: E7.
Anda bisa membuat rumus yang menggunakan pernyataan IF bertumpuk untuk memeriksa setiap item, tetapi itu tidak akan diskalakan dengan baik jika Anda memiliki banyak hal yang harus dicari. Setiap kali Anda menambahkan kata yang akan dicari, Anda harus menambahkan IF bertingkat lainnya dan menyesuaikan tanda kurung.
Larutan
Solusinya adalah dengan membuat rumus menghitung semua kecocokan sekaligus. Setelah kami memilikinya, kami cukup membandingkan jumlah itu dengan jumlah item yang kami cari. Jika cocok, kita tahu sel berisi semua item.
Dalam contoh yang ditunjukkan, rumus yang kami gunakan adalah:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Penjelasan
Kuncinya adalah cuplikan ini:
ISNUMBER(SEARCH(things,B5)
Ini didasarkan pada rumus lain (dijelaskan secara detail di sini) yang hanya memeriksa sel untuk satu substring. Jika sel berisi substring, rumus mengembalikan TRUE. Jika tidak, rumus mengembalikan FALSE.
Namun, jika kita memberikan rumus yang sama daftar hal (dalam hal ini, kita menggunakan rentang bernama "hal", E5: E7) itu akan memberi kita kembali daftar nilai TRUE / FALSE, satu untuk setiap item di . Hasilnya adalah sebuah array yang terlihat seperti ini:
(BETUL BETUL BETUL)
Dimana setiap TRUE mewakili item yang ditemukan, dan setiap FALSE mewakili item yang tidak ditemukan.
Kita dapat memaksa nilai TRUE / FALSE menjadi 1 dan 0 dengan negatif ganda (-, juga disebut unary ganda):
--ISNUMBER(SEARCH(things,B5))
yang menghasilkan array seperti ini:
(1; 1; 1)
Selanjutnya, kami memproses larik ini dengan SUMPRODUCT, yang akan memberi kami jumlah total. Jika jumlah ini sama dengan jumlah item dalam rentang bernama "hal", kita tahu bahwa kita telah menemukan semua hal dan dapat mengembalikan TRUE. Cara kami melakukannya adalah dengan membandingkan kedua angka tersebut secara langsung. Kami mendapatkan jumlah sel tidak kosong dalam "sesuatu" menggunakan COUNTA:
=COUNTA(things)
Dengan daftar hard-coded
Tidak ada persyaratan bahwa Anda menggunakan rentang untuk daftar barang Anda. Jika Anda hanya mencari sejumlah kecil hal, Anda dapat menggunakan daftar dalam format array, yang disebut konstanta array. Misalnya, jika Anda hanya mencari warna merah, biru, dan hijau, Anda dapat menggunakan ("merah", "biru", "hijau") seperti ini:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
