Generische Formel
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Zusammenfassung
Um die Datenüberprüfung zu verwenden, um nur eine Liste bestimmter Zeichen zuzulassen, können Sie eine ziemlich komplizierte Arrayformel verwenden, die auf den Funktionen COUNT, MATCH und LEN basiert. Im gezeigten Beispiel wird die Datenvalidierung mit folgender Formel angewendet:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
wobei "erlaubt" der benannte Bereich D5: D11 ist.
Erläuterung
Mit der MID-Funktion wird von innen nach außen ein Array aus dem in B5 eingegebenen Text mit diesem Snippet generiert:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
hier ausführlich erklärt. Das Ergebnis ist ein Array wie folgt:
("A";"A";"A";"-";"1";"1";"1")
Das geht in MATCH als Suchwert. Für das Lookup-Array verwenden wir den benannten Bereich "erlaubt", der mit einer leeren Zeichenfolge ("") verknüpft ist:
allowed&""
Die Verkettung konvertiert beliebige Zahlen in Zeichenfolgen, sodass wir Äpfel für Äpfel abgleichen. Das Ergebnis ist ein Array wie folgt:
("A";"B";"C";"1";"2";"3";"-")
Das letzte Argument in MATCH, match_type, wird auf Null gesetzt, um eine genaue Übereinstimmung zu erzwingen. Da wir MATCH mehrere Suchwerte geben, erhalten wir ein Array mit mehreren Ergebnissen zurück:
(1;1;1;7;4;4;4)
Jede Zahl in diesem Array steht für eine Übereinstimmung. Falls für ein Zeichen keine Übereinstimmung gefunden wird, enthält das Array einen # N / A-Fehler.
Schließlich wird die COUNT-Funktion verwendet, um die Zahlen im Ergebnisarray zu zählen, die mit einer Anzahl aller Zeichen in der Zelle verglichen werden, die mit der LEN-Funktion berechnet wurden. Wenn MATCH eine Übereinstimmung für alle Zeichen findet, sind die Zählungen gleich, die Formel gibt TRUE zurück und die Datenüberprüfung ist erfolgreich. Wenn MATCH keine Übereinstimmung mit einem Zeichen findet, gibt es # N / A anstelle einer Zahl zurück. In diesem Fall stimmen die Zählungen nicht überein und die Datenüberprüfung schlägt fehl.
Hinweis: Diese Formel basiert auf roher Gewalt, um die Arbeit zu erledigen. Wenn Sie einen besseren Ansatz haben, hinterlassen Sie bitte unten einen Kommentar.
