Všeobecný vzorec
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Zhrnutie
Ak chcete pomocou overenia údajov povoliť iba zoznam konkrétnych znakov, môžete použiť pomerne zložitý vzorec poľa založený na funkciách COUNT, MATCH a LEN. V zobrazenom príklade sa overenie údajov použije pomocou tohto vzorca:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
kde „povolené“ je pomenovaný rozsah D5: D11.
Vysvetlenie
Pri práci zvnútra von sa funkcia MID používa na vygenerovanie poľa z textu zadaného do kódu B5 pomocou tohto úryvku:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
tu podrobne vysvetlené. Výsledkom je pole ako je toto:
("A";"A";"A";"-";"1";"1";"1")
ktorý ide do MATCH ako vyhľadávacia hodnota. Pre vyhľadávacie pole používame pomenovaný rozsah „povolený“, zreťazený na prázdny reťazec („“):
allowed&""
Zreťazenie prevádza ľubovoľné čísla na reťazce, takže sa zhodujeme medzi jablkami. Výsledkom je pole ako je toto:
("A";"B";"C";"1";"2";"3";"-")
Posledný argument v MATCH, match_type je nastavený na nulu, aby sa vynútila presná zhoda. Pretože MATCHU dávame viac vyhľadávacích hodnôt, dostaneme späť pole s viacerými výsledkami:
(1;1;1;7;4;4;4)
Každé číslo v tomto poli predstavuje zhodu. V prípade, že sa nenájde zhoda pre znak, pole bude obsahovať chybu # N / A.
Nakoniec sa použije funkcia COUNT na spočítanie čísel v poli výsledkov, ktoré sa porovná s počtom všetkých znakov v bunke vypočítaným pomocou funkcie LEN. Keď MATCH nájde zhodu pre všetky znaky, počty sú rovnaké, vzorec vráti hodnotu TRUE a overenie údajov je úspešné. Ak MATCH nenájde zhodu so žiadnym znakom, namiesto čísla vráti # N / A. V takom prípade sa počty nezhodujú a overenie údajov zlyhá.
Poznámka: tento vzorec sa pri uskutočňovaní úlohy spolieha na hrubú silu. Ak máte lepší prístup, zanechajte prosím komentár nižšie.
