Obecný vzorec
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
souhrn
Chcete-li pomocí ověření dat povolit pouze seznam konkrétních znaků, můžete použít poměrně komplikovaný vzorec pole založený na funkcích COUNT, MATCH a LEN. V zobrazeném příkladu se použije ověření dat s tímto vzorcem:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
kde „povoleno“ je pojmenovaný rozsah D5: D11.
Vysvětlení
Při práci zevnitř ven se funkce MID používá ke generování pole z textu zadaného v B5 pomocí tohoto fragmentu:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
zde podrobně vysvětleno. Výsledkem je pole jako toto:
("A";"A";"A";"-";"1";"1";"1")
který jde do MATCH jako vyhledávací hodnota. Pro vyhledávací pole používáme pojmenovaný rozsah "povoleno", zřetězený na prázdný řetězec (""):
allowed&""
Zřetězení převádí libovolná čísla na řetězce, takže porovnáváme jablka s jablky. Výsledkem je pole jako toto:
("A";"B";"C";"1";"2";"3";"-")
Poslední argument v MATCH, match_type je nastaven na nulu, aby vynutil přesnou shodu. Protože dáme MATCH více hodnot vyhledávání, dostaneme zpět pole s více výsledky:
(1;1;1;7;4;4;4)
Každé číslo v tomto poli představuje shodu. V případě, že pro znak nebude nalezena shoda, bude pole obsahovat chybu # N / A.
Nakonec je použita funkce POČET spočítat čísla v matici výsledků, která je porovnána s počtem všech znaků v buňce vypočítaných pomocí funkce LEN. Když MATCH najde shodu pro všechny znaky, počty jsou stejné, vzorec vrátí TRUE a ověření dat bude úspěšné. Pokud MATCH nenajde shodu s žádným znakem, vrátí místo čísla N / A místo čísla. V takovém případě se počty neshodují a ověření dat selže.
Poznámka: tento vzorec závisí na hrubé síle, aby byla práce hotová. Pokud máte lepší přístup, zanechte prosím komentář níže.
