Formula generik
=COUNT(FIND(xlist,A1))=0
Ringkasan
Untuk menggunakan pengesahan data untuk menyekat tanda baca, anda dapat menggunakan rentang bernama dan formula berdasarkan fungsi FIND dan COUNT. Dalam contoh yang ditunjukkan, pengesahan data yang berlaku untuk C5: C10 adalah:
=COUNT(FIND(xlist,B5))=0
di mana xlist adalah julat bernama D5: D11.
Penjelasan
Peraturan pengesahan data dipicu ketika pengguna menambahkan atau mengubah nilai sel. Apabila formula khusus mengembalikan BENAR, pengesahan berlalu dan input diterima. Apabila formula mengembalikan FALSE, pengesahan gagal dan input ditolak dengan mesej pop timbul.
Dalam kes ini, sebelumnya kita telah menentukan julat bernama "xlist" sebagai D5: D11. Julat ini mengandungi watak yang tidak dibenarkan.
Rumus yang kami gunakan untuk pengesahan data adalah:
=COUNT(FIND(xlist,B5))=0
Bekerja dari dalam ke luar fungsi FIND dikonfigurasi dengan xlist untuk "cari teks", dan sel B5 sebagai teks untuk dicari. Kerana kami memberikan FIND array dengan pelbagai nilai, FIND mengembalikan array hasil, satu untuk setiap watak dalam julat bernama "xlist". Untuk sel B5, hasil dari FIND kelihatan seperti ini:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Setiap kesalahan #VALUE mewakili satu watak yang tidak dijumpai. Sekiranya kami cuba memasukkan, katakan, "pembongkaran @", yang merangkumi watak terhad, FIND mengembalikan:
(#VALUE!;11;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Perhatikan nilai kedua dalam array sekarang adalah 11.
Seterusnya, fungsi COUNT mengembalikan kiraan semua nombor dalam tatasusunan. Apabila larik tidak mengandungi nombor (iaitu tidak ada aksara terhad) COUNT mengembalikan sifar, ungkapan mengembalikan BENAR, dan pengesahan data berjaya. Walau bagaimanapun, Apabila array tidak mengandungi nombor (iaitu terdapat sekurang-kurangnya satu watak terhad yang dijumpai) COUNT mengembalikan nombor, ungkapan mengembalikan PALSU, dan pengesahan data gagal.
Karakter yang muncul dalam senarai xlist yang dinamakan dapat disesuaikan dengan syarat yang sesuai.
Catatan: Rujukan sel dalam formula pengesahan data relatif terhadap sel kiri atas dalam julat yang dipilih ketika peraturan pengesahan ditentukan, dalam hal ini B5.
