Formula umum
=SUMPRODUCT(--(A1=things))>0
Ringkasan
Jika Anda ingin menguji sel untuk melihat apakah itu sama dengan salah satu dari beberapa hal, Anda bisa melakukannya dengan rumus yang menggunakan fungsi SUMPRODUCT.
Konteks
Katakanlah Anda memiliki daftar string teks dalam rentang B5: B11, dan Anda ingin menguji setiap sel terhadap daftar hal lain dalam rentang E5: E9. Dengan kata lain, untuk setiap sel di B5: B11, Anda ingin tahu: apakah sel ini sama dengan semua yang ada di E5: E9?
Anda bisa mulai membuat rumus besar berdasarkan pernyataan IF bertumpuk, tetapi rumus array yang didasarkan pada SUMPRODUCT adalah pendekatan yang lebih sederhana dan lebih bersih.
Larutan
Solusinya adalah membuat rumus yang akan menguji beberapa nilai dan mengembalikan daftar nilai TRUE / FALSE. Setelah kami memilikinya, kami dapat memproses daftar itu (sebenarnya sebuah array) dengan SUMPRODUCT.
Rumus yang kami gunakan terlihat seperti ini:
=SUMPRODUCT(--(B5=things))>0
Penjelasan
Kuncinya adalah cuplikan ini:
--(B5=things)
yang hanya membandingkan nilai di B5 dengan setiap nilai dalam rentang bernama "things". Karena kita membandingkan B5 dengan sebuah array (yaitu rentang bernama "things", E5: E11) hasilnya adalah array nilai TRUE / FALSE seperti ini:
(TRUE; FALSE; FALSE; FALSE; FALSE)
Jika kita memiliki bahkan satu TRUE dalam larik, kita tahu bahwa B5 sama dengan setidaknya satu hal dalam daftar, jadi, untuk memaksa nilai TRUE / FALSE menjadi 1 dan 0, kita menggunakan negatif ganda (-, juga disebut ganda unary). Setelah pemaksaan ini, kami memiliki ini:
(1; 0; 0; 0; 0)
Sekarang kami memproses hasilnya dengan SUMPRODUCT, yang akan menjumlahkan elemen dalam array. Jika kita mendapatkan hasil bukan nol, kita memiliki setidaknya satu kecocokan, jadi kita menggunakan> 1 untuk memaksa hasil akhir baik BENAR atau SALAH.
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:
--(B5=("red","blue","green"))
Berurusan dengan ruang ekstra
Jika sel yang Anda uji berisi ruang ekstra, mereka tidak akan cocok dengan benar. Untuk menghapus semua spasi ekstra, Anda dapat mengubah rumus untuk menggunakan fungsi TRIM seperti:
=SUMPRODUCT(--(TRIM(A1)=things))>0
