Formula generică
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
rezumat
Pentru a utiliza validarea datelor pentru a permite doar o listă de caractere specifice, puteți utiliza o formulă de matrice destul de complicată bazată pe funcțiile COUNT, MATCH și LEN. În exemplul prezentat, validarea datelor se aplică cu această formulă:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
unde „permis” este intervalul numit D5: D11.
Explicaţie
Funcționând din interior spre exterior, funcția MID este utilizată pentru a genera o matrice din textul introdus în B5 cu acest fragment:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
explicat în detaliu aici. Rezultatul este o matrice ca aceasta:
("A";"A";"A";"-";"1";"1";"1")
care intră în MATCH ca valoare de căutare. Pentru matricea de căutare, folosim intervalul numit „permis”, concatenat într-un șir gol („„):
allowed&""
Concatenarea convertește orice număr în șiruri, astfel încât să potrivim mere-mere. Rezultatul este o matrice ca aceasta:
("A";"B";"C";"1";"2";"3";"-")
Ultimul argument din MATCH, match_type este setat la zero pentru a forța o potrivire exactă. Deoarece oferim MATCH valori multiple de căutare, obținem înapoi o matrice cu rezultate multiple:
(1;1;1;7;4;4;4)
Fiecare număr din această matrice reprezintă o potrivire. În cazul în care nu se găsește o potrivire pentru un caracter, tabloul va conține o eroare # N / A.
În cele din urmă, funcția COUNT este utilizată pentru a număra numerele din matricea de rezultate, care este comparată cu un număr al tuturor caracterelor din celulă calculat cu funcția LEN. Când MATCH găsește o potrivire pentru toate caracterele, numărul este egal, formula returnează TRUE și validarea datelor reușește. Dacă MATCH nu găsește niciun personaj potrivit, acesta returnează # N / A în loc de un număr. În acest caz, numărul nu se potrivește și validarea datelor nu reușește.
Notă: această formulă se bazează pe forța brută pentru a face treaba. Dacă aveți o abordare mai bună, vă rugăm să lăsați un comentariu mai jos.
