Splošna formula
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Povzetek
Če želite s preverjanjem podatkov omogočiti seznam samo določenih znakov, lahko uporabite precej zapleteno formulo matrike, ki temelji na funkcijah COUNT, MATCH in LEN. V prikazanem primeru se validacija podatkov uporabi s to formulo:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
kjer je "dovoljeno" imenovano območje D5: D11.
Pojasnilo
Funkcija MID, ki deluje od znotraj navzven, se uporablja za ustvarjanje matrike iz besedila, vnesenega v B5 s tem delčkom:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
tukaj podrobno razloženo. Rezultat je matrika, kot je ta:
("A";"A";"A";"-";"1";"1";"1")
ki gre v MATCH kot iskalna vrednost. Za iskalno polje uporabimo imenovani obseg "dovoljeno", združen v prazen niz (""):
allowed&""
Združevanje pretvori poljubna števila v nize, tako da se ujemajo med jabolki in jabolki. Rezultat je matrika, kot je ta:
("A";"B";"C";"1";"2";"3";"-")
Zadnji argument v MATCH, match_type je nastavljen na nič, da prisili natančno ujemanje. Ker damo MATCH več iskalnih vrednosti, dobimo nazaj matriko z več rezultati:
(1;1;1;7;4;4;4)
Vsaka številka v tej matriki predstavlja ujemanje. V primeru, da za znak ni mogoče najti ujemanja, bo matrika vsebovala napako # N / A.
Na koncu se uporabi funkcija COUNT, šteje števila v matriki rezultatov, ki se primerja s številom vseh znakov v celici, izračunanim s funkcijo LEN. Ko MATCH najde ujemanje za vse znake, je štetje enako, formula vrne TRUE in preverjanje podatkov uspe. Če MATCH ne najde nobenega znaka, vrne # N / A namesto številke. V tem primeru se štetje ne ujema in preverjanje podatkov ne uspe.
Opomba: ta formula se pri delu opira na surovo silo. Če imate boljši pristop, pustite komentar spodaj.
