Formula generik
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Ringkasan
Untuk tidak membenarkan input yang mengandungi salah satu dari banyak hal, anda dapat menggunakan aturan pengesahan data khusus berdasarkan fungsi SEARCH.
Dalam contoh yang ditunjukkan, pengesahan data yang berlaku untuk B5: B11 adalah:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Penjelasan
Peraturan pengesahan data dipicu ketika pengguna menambahkan atau mengubah nilai sel.
Rumus ini menggunakan fungsi SEARCH untuk menguji input pengguna untuk setiap nilai dalam "senarai" julat yang dinamakan. Logik carian "mengandung" - apabila nilai dari "senarai" dijumpai, SEARCH mengembalikan kedudukan nilai sebagai nombor. Sekiranya tidak dijumpai, SEARCH mengembalikan ralat.
Fungsi ISNUMBER kemudian menukar nombor menjadi BENAR dan kesalahan menjadi FALSE, dan operator negatif berganda mengubah nilai BENAR BENAR menjadi 1s dan sifar. Oleh kerana julat bernama "senarai" mengandungi 5 nilai, kami mendapat kembali 5 hasil dalam susunan seperti ini:
(0; 0; 0; 0; 0)
SUMPRODUCT kemudian menjumlahkan item dalam larik dan hasilnya diuji dengan sifar. Selagi semua item sifar, SUMPRODUCT mengembalikan sifar dan pengesahan berjaya. Sekiranya SUMPRODUCT mengembalikan nombor lain (iaitu apabila item dalam "senarai" dijumpai) formula mengembalikan PALSU dan pengesahan gagal.
Catatan: Rujukan sel dalam formula pengesahan data relatif terhadap sel kiri atas dalam julat yang dipilih ketika peraturan pengesahan ditentukan, dalam hal ini B5.
