Generisk formel
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Sammanfattning
För att använda datavalidering för att endast tillåta en lista med specifika tecken kan du använda en ganska komplicerad matrisformel baserad på COUNT-, MATCH- och LEN-funktionerna. I det visade exemplet tillämpas datavalidering med denna formel:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
där "tillåtet" är det namngivna intervallet D5: D11.
Förklaring
Arbetar inifrån och ut, MID-funktionen används för att generera en matris från text in i B5 med detta utdrag:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
förklaras i detalj här. Resultatet är en array som denna:
("A";"A";"A";"-";"1";"1";"1")
som går in i MATCH som uppslagsvärde. För uppslagsmatris använder vi det namngivna området "tillåtet", sammanfogat till en tom sträng (""):
allowed&""
Sammankopplingen omvandlar alla tal till strängar, så att vi matchar äpplen till äpplen. Resultatet är en array som denna:
("A";"B";"C";"1";"2";"3";"-")
Det sista argumentet i MATCH, match_type är satt till noll för att tvinga en exakt matchning. Eftersom vi ger MATCH flera uppslagsvärden får vi tillbaka en matris med flera resultat:
(1;1;1;7;4;4;4)
Varje nummer i denna matris representerar en matchning. Om en matchning inte hittas för ett tecken, kommer matrisen att innehålla ett # N / A-fel.
Slutligen används COUNT-funktionen för att räkna siffrorna i resultatmatrisen, som jämförs med ett antal av alla tecken i cellen beräknat med LEN-funktionen. När MATCH hittar en matchning för alla tecken är räkningarna lika, formeln returnerar SANT och datavalidering lyckas. Om MATCH inte hittar någon matchning, returnerar den # N / A istället för ett nummer. I så fall matchar räkningarna inte och datavalidering misslyckas.
Obs: den här formeln är beroende av brute force för att få jobbet gjort. Om du har ett bättre tillvägagångssätt, vänligen lämna en kommentar nedan.
