Excel-Formel: Zelle enthält einige Wörter, andere jedoch nicht

Generische Formel

(=AND(COUNT(SEARCH(inc,A1))>0,COUNT(SEARCH(exc,A1))=0))

Zusammenfassung

Um eine Zelle zu testen, um festzustellen, ob sie bestimmte Wörter enthält, andere jedoch nicht, können Sie eine Array-Formel verwenden, die auf den Funktionen COUNT und SEARCH basiert und in die AND-Funktion eingeschlossen ist.

Im gezeigten Beispiel lautet die Formel in C5:

(=AND(COUNT(SEARCH(inc,B5))>0,COUNT(SEARCH(exc,B5))=0))

Diese Formel gibt TRUE zurück, wenn B5 eines der Wörter im benannten Bereich inc und keines der Wörter im benannten Bereich exc enthält .

Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabetaste eingegeben werden.

Erläuterung

Diese Formel basiert auf der UND-Funktion, um zwei Bedingungen gleichzeitig zu testen:

  1. Die Anzahl der Wörter aus dem benannten Bereich inc ist> 0
  2. Die Anzahl der Wörter aus dem benannten Bereich exc ist = 0

Wenn beide Bedingungen TRUE sind, gibt die Formel TRUE zurück. Wenn eine der Bedingungen FALSE ist, gibt die Formel FALSE zurück.

Der Test für mehrere Wörter wird mithilfe der SEARCH-Funktion mit Hilfe von COUNT durchgeführt.

Wenn SEARCH mehr als ein zu suchendes Element erhält, gibt es eine Reihe von Ergebnissen zurück, eines pro Element. Wenn eine Übereinstimmung gefunden wird, gibt SEARCH die Position der Übereinstimmung zurück. Wenn keine Übereinstimmung gefunden wird, gibt SEARCH den Fehler #VALUE zurück. Die COUNT-Funktion erhält eine Anzahl von Zahlen in dem von SEARCH zurückgegebenen Array. COUNT ignoriert standardmäßig Fehler.

In dem gezeigten Beispiel wird die Formel in solchen Schritten gelöst

=AND(COUNT((1;11;#VALUE!))>0,COUNT((#VALUE!;#VALUE!))=0) =AND(2>0,0=0) =AND(TRUE,TRUE) =TRUE

Mit fest codierten Werten

Es ist nicht erforderlich, dass Sie einen Bereich für Ihre Liste von Dingen verwenden. Wenn Sie nur nach einer kleinen Anzahl von Dingen suchen, können Sie eine Liste im Array-Format verwenden, die als Array-Konstante bezeichnet wird. Um beispielsweise auf Rot, Blau oder Grün zu testen, aber Rosa und Orange auszuschließen, können Sie Folgendes verwenden: 

=AND(COUNT(SEARCH(("red","blue","green"),B5))>0,COUNT(SEARCH(("orange","pink"),B5))=0)

Diese Version erfordert nicht die Syntax Steuerung + Umschalt + Eingabe der Array-Formel.

Interessante Beiträge...