Generisk formel
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Resumé
For at bruge datavalidering til kun at tillade en liste over specifikke tegn kan du bruge en ret kompliceret matrixformel baseret på funktionerne TÆL, MATCH og LEN. I det viste eksempel anvendes datavalidering med denne formel:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
hvor "tilladt" er det navngivne område D5: D11.
Forklaring
Arbejder indefra og ud, bruges MID-funktionen til at generere en matrix fra tekst indtastet i B5 med dette uddrag:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
forklaret i detaljer her. Resultatet er en matrix som denne:
("A";"A";"A";"-";"1";"1";"1")
som går i MATCH som opslagsværdi. Til opslagsarray bruger vi det navngivne interval "tilladt", sammenkædet til en tom streng (""):
allowed&""
Sammenkædningen konverterer alle tal til strenge, så vi matcher æbler til æbler. Resultatet er en matrix som denne:
("A";"B";"C";"1";"2";"3";"-")
Det sidste argument i MATCH, match_type er sat til nul for at tvinge et nøjagtigt match. Fordi vi giver MATCH flere opslagsværdier, får vi tilbage en matrix med flere resultater:
(1;1;1;7;4;4;4)
Hvert tal i denne matrix repræsenterer et match. Hvis et match ikke findes for et tegn, vil arrayet indeholde en # N / A-fejl.
Endelig bruges COUNT-funktionen til at tælle tallene i resultatarrayet, som sammenlignes med et antal af alle tegn i cellen beregnet med LEN-funktionen. Når MATCH finder et match for alle tegn, er tællingerne ens, formlen returnerer SAND, og datavalidering lykkes. Hvis MATCH ikke finder et match med et tegn, returnerer det # N / A i stedet for et tal. I så fald stemmer tællerne ikke overens, og datavalidering mislykkes.
Bemærk: denne formel er afhængig af brutal kraft for at få arbejdet gjort. Hvis du har en bedre tilgang, bedes du efterlade en kommentar nedenfor.
