Formula umum
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Ringkasan
Untuk menggunakan validasi data untuk memperbolehkan daftar karakter tertentu saja, Anda dapat menggunakan rumus array yang agak rumit berdasarkan fungsi COUNT, MATCH, dan LEN. Dalam contoh yang diperlihatkan, validasi data diterapkan dengan rumus ini:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
di mana "diizinkan" adalah rentang bernama D5: D11.
Penjelasan
Bekerja dari dalam ke luar, fungsi MID digunakan untuk menghasilkan larik dari teks yang dimasukkan dalam B5 dengan cuplikan ini:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
dijelaskan secara rinci di sini. Hasilnya adalah array seperti ini:
("A";"A";"A";"-";"1";"1";"1")
yang masuk ke MATCH sebagai nilai pencarian. Untuk larik pencarian, kami menggunakan rentang bernama "diperbolehkan", digabung menjadi string kosong (""):
allowed&""
Rangkaian mengonversi bilangan apa pun menjadi string, sehingga kami mencocokkan apples-to-apples. Hasilnya adalah array seperti ini:
("A";"B";"C";"1";"2";"3";"-")
Argumen terakhir dalam MATCH, match_type disetel ke nol untuk memaksa pencocokan tepat. Karena kami memberikan MATCH beberapa nilai pencarian, kami mendapatkan kembali array dengan beberapa hasil:
(1;1;1;7;4;4;4)
Setiap angka dalam larik ini mewakili kecocokan. Jika kecocokan tidak ditemukan untuk sebuah karakter, array akan berisi kesalahan # N / A.
Terakhir, fungsi COUNT digunakan untuk menghitung angka dalam larik hasil, yang dibandingkan dengan jumlah semua karakter dalam sel yang dihitung dengan fungsi LEN. Saat MATCH menemukan kecocokan untuk semua karakter, jumlahnya sama, rumus mengembalikan TRUE, dan validasi data berhasil. Jika MATCH tidak menemukan kecocokan karakter apa pun, ia mengembalikan # N / A alih-alih angka. Jika demikian, jumlah tidak cocok dan validasi data gagal.
Catatan: rumus ini bergantung pada kekerasan untuk menyelesaikan pekerjaan. Jika Anda memiliki pendekatan yang lebih baik, silakan tinggalkan komentar di bawah.
