Generisk formel
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Sammendrag
For å bruke datavalidering for kun å tillate en liste over bestemte tegn, kan du bruke en ganske komplisert matriseformel basert på COUNT, MATCH og LENN-funksjonene. I eksemplet vist brukes datavalidering med denne formelen:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
der "tillatt" er det navngitte området D5: D11.
Forklaring
Arbeider fra innsiden og ut, brukes MID-funksjonen til å generere en matrise fra tekst som er skrevet inn i B5 med denne kodebiten:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
forklart i detalj her. Resultatet er en matrise som dette:
("A";"A";"A";"-";"1";"1";"1")
som går inn i MATCH som oppslagsverdi. For oppslagsmatrise bruker vi det navngitte området "tillatt", sammenkoblet til en tom streng (""):
allowed&""
Sammenkoblingen konverterer alle tall til strenger, slik at vi matcher epler til epler. Resultatet er en matrise som dette:
("A";"B";"C";"1";"2";"3";"-")
Det siste argumentet i MATCH, match_type er satt til null for å tvinge et eksakt samsvar. Fordi vi gir MATCH flere oppslagsverdier, får vi tilbake en matrise med flere resultater:
(1;1;1;7;4;4;4)
Hvert tall i denne matrisen representerer en kamp. Hvis et samsvar ikke blir funnet for et tegn, vil matrisen inneholde en # I / T-feil.
Til slutt brukes COUNT-funksjonen til å telle tallene i resultatmatrisen, som sammenlignes med en telling av alle tegn i cellen beregnet med LEN-funksjonen. Når MATCH finner et samsvar for alle tegn, er antall teller like, formelen returnerer SANT, og datavalidering lykkes. Hvis MATCH ikke finner noe samsvar med et tegn, returnerer det # N / A i stedet for et tall. I så fall stemmer ikke tellerne sammen, og datavalidering mislykkes.
Merk: denne formelen er avhengig av brute force for å få jobben gjort. Hvis du har en bedre tilnærming, kan du legge igjen en kommentar nedenfor.
