Generische Formel
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Zusammenfassung
Um Eingaben zu verbieten, die eines von vielen Dingen enthalten, können Sie eine benutzerdefinierte Datenüberprüfungsregel verwenden, die auf der SEARCH-Funktion basiert.
In dem gezeigten Beispiel lautet die auf B5: B11 angewendete Datenvalidierung:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Erläuterung
Datenüberprüfungsregeln werden ausgelöst, wenn ein Benutzer einen Zellenwert hinzufügt oder ändert.
Diese Formel verwendet die SEARCH-Funktion, um Benutzereingaben für jeden Wert im benannten Bereich "Liste" zu testen. Die Suchlogik lautet "enthält". Wenn ein Wert aus "Liste" gefunden wird, gibt SEARCH die Position des Werts als Zahl zurück. Wenn nicht gefunden, gibt SEARCH einen Fehler zurück.
Die ISNUMBER-Funktion konvertiert dann Zahlen in TRUE und Fehler in FALSE, und der doppelt negative Operator ändert die TRUE FALSE-Werte in 1s und Nullen. Da der benannte Bereich "Liste" 5 Werte enthält, erhalten wir 5 Ergebnisse in einem Array wie diesem zurück:
(0; 0; 0; 0; 0)
SUMPRODUCT fasst dann die Elemente im Array zusammen und das Ergebnis wird gegen Null getestet. Solange alle Elemente Null sind, gibt SUMPRODUCT Null zurück und die Validierung ist erfolgreich. Wenn SUMPRODUCT eine andere Nummer zurückgibt (dh wenn ein Element in "Liste" gefunden wird), gibt die Formel FALSE zurück und die Validierung schlägt fehl.
Hinweis: Zellreferenzen in Datenvalidierungsformeln beziehen sich auf die obere linke Zelle in dem Bereich, der bei der Definition der Validierungsregel ausgewählt wurde, in diesem Fall B5.
