Generische Formel
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Zusammenfassung
Um eine Zelle auf eine von vielen Zeichenfolgen zu testen und andere auszuschließen, können Sie eine Formel verwenden, die auf den Funktionen SEARCH, ISNUMBER und SUMPRODUCT basiert. In dem gezeigten Beispiel lautet die Formel in C5:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
Dabei ist "Einschließen" der benannte Bereich E5: E9 und "Ausschließen" der benannte Bereich G5: G6.
Erläuterung
Im Kern verwendet diese Formel die SEARCH-Funktion, um nach mehreren Zeichenfolgen in einer Zelle zu suchen. Im linken SUMPRODUCT sucht SEARCH nach allen Zeichenfolgen im genannten Bereich "include".
Im rechten SUMPRODUCT sucht SEARCH nach allen Zeichenfolgen im benannten Bereich "exclude".
In beiden Teilen der Formel gibt SEARCH numerische Positionen zurück, wenn Zeichenfolgen gefunden werden, und Fehler, wenn dies nicht der Fall ist. Die ISNUMBER-Funktion konvertiert die Zahlen in TRUE und Fehler in FALSE, und das doppelte Negativ konvertiert die TRUE FALSE-Werte in 1 und 0.
Das Ergebnis an dieser Stelle sieht folgendermaßen aus:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Dann:
=(1>0)*(0=0) =TRUE*TRUE =1
Hinweis: Diese Formel gibt entweder 1 oder Null zurück, die in Formeln, bedingter Formatierung oder Datenüberprüfung wie TRUE und FALSE behandelt werden.
