Formula generik
=SUMPRODUCT(--(A1=things))>0
Ringkasan
Sekiranya anda ingin menguji sel untuk melihat sama ada salah satu daripada beberapa perkara, anda boleh melakukannya dengan formula yang menggunakan fungsi SUMPRODUCT.
Konteks
Katakan anda mempunyai senarai rentetan teks dalam julat B5: B11, dan anda ingin menguji setiap sel dengan senarai perkara lain dalam julat E5: E9. Dengan kata lain, untuk setiap sel di B5: B11, anda ingin tahu: adakah sel ini menyamai mana-mana perkara di E5: E9?
Anda boleh mula membina formula besar berdasarkan pernyataan IF bersarang, tetapi formula larik berdasarkan SUMPRODUCT adalah pendekatan yang lebih sederhana dan lebih bersih.
Penyelesaian
Penyelesaiannya adalah dengan membuat formula yang akan menguji pelbagai nilai dan mengembalikan senarai nilai BENAR / SALAH. Setelah memilikinya, kita dapat memproses senarai itu (sebenarnya, array) dengan SUMPRODUCT.
Formula yang kami gunakan kelihatan seperti ini:
=SUMPRODUCT(--(B5=things))>0
Penjelasan
Kuncinya ialah coretan ini:
--(B5=things)
yang hanya membandingkan nilai dalam B5 dengan setiap nilai dalam julat "benda" yang dinamakan. Kerana kita membandingkan B5 dengan array (iaitu julat bernama "things", E5: E11) hasilnya akan menjadi array nilai BENAR / SALAH seperti ini:
(BENAR; SALAH; SALAH; SALAH; SALAH)
Sekiranya kita mempunyai satu BENAR dalam array, kita tahu bahawa B5 sama dengan sekurang-kurangnya satu perkara dalam senarai, jadi, untuk memaksa nilai BENAR / SALAH menjadi 1s dan 0s, kita menggunakan negatif ganda (-, juga disebut ganda tidak sedar). Selepas paksaan ini, kami mempunyai ini:
(1; 0; 0; 0; 0)
Sekarang kita memproses hasilnya dengan SUMPRODUCT, yang akan menambahkan elemen dalam array. Sekiranya kita mendapat keputusan bukan sifar, kita mempunyai sekurang-kurangnya satu perlawanan, jadi kita menggunakan> 1 untuk memaksa hasil akhir sama ada BENAR atau SALAH.
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:
--(B5=("red","blue","green"))
Berurusan dengan ruang tambahan
Sekiranya sel yang anda uji mengandungi ruang tambahan, sel tersebut tidak akan sesuai. Untuk melucutkan semua ruang tambahan, anda boleh mengubah formula untuk menggunakan fungsi TRIM seperti:
=SUMPRODUCT(--(TRIM(A1)=things))>0
