Generieke formule
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Samenvatting
Als u gegevensvalidatie wilt gebruiken om alleen een lijst met specifieke tekens toe te staan, kunt u een nogal gecompliceerde matrixformule gebruiken op basis van de functies COUNT, MATCH en LEN. In het getoonde voorbeeld wordt gegevensvalidatie toegepast met deze formule:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
waarbij "toegestaan" het benoemde bereik D5: D11 is.
Uitleg
Werkend van binnen naar buiten, wordt de MID-functie gebruikt om een array te genereren uit tekst die is ingevoerd in B5 met dit fragment:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
hier in detail uitgelegd. Het resultaat is een array als deze:
("A";"A";"A";"-";"1";"1";"1")
die in MATCH ingaat als de opzoekwaarde. Voor de opzoekmatrix gebruiken we het benoemde bereik "toegestaan", samengevoegd tot een lege tekenreeks (""):
allowed&""
De aaneenschakeling converteert alle getallen naar strings, zodat we appels met appels matchen. Het resultaat is een array als deze:
("A";"B";"C";"1";"2";"3";"-")
Het laatste argument in MATCH, match_type, wordt op nul gezet om een exacte match te forceren. Omdat we MATCH meerdere opzoekwaarden geven, krijgen we een array terug met meerdere resultaten:
(1;1;1;7;4;4;4)
Elk nummer in deze array vertegenwoordigt een overeenkomst. Als er geen overeenkomst wordt gevonden voor een teken, bevat de array een # N / A-fout.
Ten slotte wordt de COUNT-functie gebruikt om de getallen in de resultaatmatrix te tellen, die wordt vergeleken met een telling van alle tekens in de cel berekend met de LEN-functie. Wanneer MATCH een overeenkomst voor alle tekens vindt, is het aantal gelijk, retourneert de formule WAAR en slaagt de gegevensvalidatie. Als MATCH geen enkel teken vindt, wordt # N / A geretourneerd in plaats van een getal. In dat geval komen de tellingen niet overeen en mislukt de gegevensvalidatie.
Opmerking: deze formule is afhankelijk van brute kracht om de klus te klaren. Als je een betere aanpak hebt, laat dan hieronder een reactie achter.
