Általános képlet
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Összegzés
Ha csak bizonyos karakterek felsorolásának engedélyezéséhez kívánja használni az adatellenőrzést, használhat meglehetősen bonyolult tömbképletet, amely a COUNT, MATCH és LEN függvényeken alapul. A bemutatott példában az adatok érvényesítését a következő képlettel alkalmazzák:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
ahol "megengedett" a D5: D11 megnevezett tartomány.
Magyarázat
Belülről kifelé haladva a MID függvény segítségével tömböt lehet létrehozni a B5-be beírt szövegből ezzel a kódrészlettel:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
itt részletesen kifejtve. Az eredmény egy ilyen tömb:
("A";"A";"A";"-";"1";"1";"1")
amely a MATCH-ba megy mint keresési érték. A keresési tömbhöz a "megengedett" nevű tartományt használjuk, üres karakterláncra összefűzve (""):
allowed&""
Az összefűzés bármely számot húrokká alakít át, így az almát az almához illesztjük. Az eredmény egy ilyen tömb:
("A";"B";"C";"1";"2";"3";"-")
A MATCH, match_type utolsó argumentuma nullára van állítva a pontos egyezés kikényszerítéséhez. Mivel a MATCH számára több keresési értéket adunk meg, több eredményt tartalmazó tömböt kapunk vissza:
(1;1;1;7;4;4;4)
Ebben a tömbben minden szám egyezést jelent. Abban az esetben, ha egy karakterhez nem található egyezés, a tömb # N / A hibát tartalmaz.
Végül a COUNT függvényt használjuk az eredménytömb számainak megszámlálásához, amelyet összehasonlítunk a cellában a LEN függvénnyel kiszámított összes karakter számával. Amikor a MATCH minden karakternek talál egyezést, a számok megegyeznek, a képlet IGAZ értéket ad vissza, és az adatok ellenőrzése sikeres lesz. Ha a MATCH nem talál egyező karaktert, akkor a szám helyett # N / A értéket ad vissza. Ebben az esetben a számok nem egyeznek, és az adatok ellenőrzése sikertelen.
Megjegyzés: ez a képlet nyers erőre támaszkodik a munka elvégzéséhez. Ha jobb megközelítése van, kérjük, hagyjon megjegyzést alább.
