Formula umum
=COUNT(FIND(xlist,A1))=0
Ringkasan
Untuk menggunakan validasi data untuk membatasi tanda baca, Anda dapat menggunakan rentang bernama dan rumus yang didasarkan pada fungsi FIND dan COUNT. Pada contoh yang ditunjukkan, validasi data yang diterapkan ke C5: C10 adalah:
=COUNT(FIND(xlist,B5))=0
di mana xlist adalah rentang bernama D5: D11.
Penjelasan
Aturan validasi data dipicu saat pengguna menambahkan atau mengubah nilai sel. Saat rumus kustom mengembalikan TRUE, validasi lolos dan input diterima. Saat rumus mengembalikan FALSE, validasi gagal dan input ditolak dengan pesan popup.
Dalam kasus ini, kita sebelumnya telah mendefinisikan range bernama "xlist" sebagai D5: D11. Rentang ini menampung karakter yang tidak diperbolehkan.
Rumus yang kami gunakan untuk validasi data adalah:
=COUNT(FIND(xlist,B5))=0
Bekerja dari dalam ke luar, fungsi FIND dikonfigurasi dengan xlist untuk "menemukan teks", dan sel B5 sebagai teks yang akan dicari. Karena kami memberikan FIND array dengan beberapa nilai, FIND mengembalikan array hasil, satu untuk setiap karakter dalam rentang bernama "xlist". Untuk sel B5, hasil dari FIND terlihat seperti ini:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Setiap kesalahan #VALUE mewakili satu karakter yang tidak ditemukan. Jika kami mencoba untuk masuk, katakan, "demolition @", yang menyertakan karakter terbatas, FIND mengembalikan:
(#VALUE!;11;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Perhatikan nilai kedua dalam larik sekarang adalah 11.
Selanjutnya, fungsi COUNT mengembalikan hitungan semua angka dalam larik. Ketika array tidak berisi angka (tidak ada karakter yang dibatasi) HITUNGAN mengembalikan nol, ekspresi mengembalikan TRUE, dan validasi data berhasil. Namun, ketika array tidak berisi angka (yaitu setidaknya ada satu karakter terbatas yang ditemukan) COUNT mengembalikan angka, ekspresi mengembalikan FALSE, dan validasi data gagal.
Karakter yang muncul dalam daftar xlist bernama dapat disesuaikan sesuai kebutuhan.
Catatan: Referensi sel dalam rumus validasi data relatif terhadap sel kiri atas dalam rentang yang dipilih saat aturan validasi ditentukan, dalam hal ini B5.
