Formula generik
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Ringkasan
Untuk menggunakan pengesahan data untuk membolehkan senarai watak tertentu sahaja, anda boleh menggunakan formula susunan yang agak rumit berdasarkan fungsi COUNT, MATCH, dan LEN. Dalam contoh yang ditunjukkan, pengesahan data diterapkan dengan formula ini:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
di mana "dibenarkan" adalah julat bernama D5: D11.
Penjelasan
Berfungsi dari dalam ke luar, fungsi MID digunakan untuk menghasilkan array dari teks yang dimasukkan dalam B5 dengan potongan ini:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
dijelaskan secara terperinci di sini. Hasilnya adalah susunan seperti ini:
("A";"A";"A";"-";"1";"1";"1")
yang masuk ke MATCH sebagai nilai carian. Untuk tatapan carian, kami menggunakan julat bernama "dibenarkan", digabungkan menjadi rentetan kosong (""):
allowed&""
Gabungan menukar nombor apa pun menjadi rentetan, sehingga kami memadankan epal-ke-epal. Hasilnya adalah susunan seperti ini:
("A";"B";"C";"1";"2";"3";"-")
Argumen terakhir dalam MATCH, match_type ditetapkan ke sifar untuk memaksa padanan tepat. Kerana kami memberikan banyak nilai pencarian MATCH, kami mendapatkan kembali array dengan pelbagai hasil:
(1;1;1;7;4;4;4)
Setiap nombor dalam tatasusunan ini menunjukkan padanan. Sekiranya kecocokan tidak dijumpai untuk watak, array akan mengandungi kesalahan # N / A.
Akhirnya, fungsi COUNT digunakan menghitung nombor dalam array hasil, yang dibandingkan dengan kiraan semua watak dalam sel yang dikira dengan fungsi LEN. Apabila MATCH menemui padanan untuk semua watak, kiraannya sama, formula mengembalikan BENAR, dan pengesahan data berjaya. Sekiranya MATCH tidak menemui watak mana-mana, ia akan mengembalikan # N / A dan bukan nombor. Sekiranya demikian, jumlahnya tidak sepadan dan pengesahan data gagal.
Catatan: formula ini bergantung pada kekuatan kasar untuk menyelesaikan tugas. Sekiranya anda mempunyai pendekatan yang lebih baik, sila tinggalkan komen di bawah.
