Generische Formel
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Zusammenfassung
Um Zellen hervorzuheben, die eine von vielen Textzeichenfolgen enthalten, können Sie eine Formel verwenden, die auf den Funktionen ISNUMBER und SEARCH zusammen mit der Funktion SUMPRODUCT basiert. In dem gezeigten Beispiel basiert die auf B4: B11 angewendete bedingte Formatierung auf dieser Formel:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Erläuterung
Dieser Teil der Formel arbeitet von innen nach außen und durchsucht jede Zelle in B4: B11 nach allen Werten im genannten Bereich "Dinge":
--ISNUMBER(SEARCH(things,B4)
Die SEARCH-Funktion gibt die Position des Werts zurück, wenn er gefunden wurde, und den Fehler #VALUE, wenn er nicht gefunden wurde. Für B4 werden die Ergebnisse in einem Array wie dem folgenden zurückgegeben:
(8;#VALUE!;#VALUE!)
Die ISNUMBER-Funktion ändert alle Ergebnisse in TRUE oder FALSE:
(TRUE;FALSE;FALSE)
Das doppelte Negativ vor ISNUMBER erzwingt TRUE / FALSE auf 1/0:
(1;0;0)
Die SUMPRODUCT-Funktion addiert dann die Ergebnisse, die gegen Null getestet werden:
=SUMPRODUCT((1;0;0))>0
Jedes Ergebnis ungleich Null bedeutet, dass mindestens ein Wert gefunden wurde. Die Formel gibt also TRUE zurück und löst die Regel aus.
Leere Dinge ignorieren
Um leere Zellen im benannten Bereich "Dinge" zu ignorieren, können Sie eine modifizierte Formel wie folgt versuchen:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Dies funktioniert so lange, wie die zu testenden Textwerte nicht die Zeichenfolge "FALSE" enthalten. In diesem Fall können Sie die IF-Funktion um einen Wert erweitern, wenn bekannt ist, dass false nicht im Text vorkommt (z. B. "zzzz", "####" usw.).
Groß- und Kleinschreibung beachten
SEARCH unterscheidet nicht zwischen Groß- und Kleinschreibung. Um auch den Fall zu überprüfen, ersetzen Sie SEARCH durch FIND wie folgt:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Falsche Übereinstimmungen verhindern
Ein Problem bei diesem Ansatz besteht darin, dass möglicherweise falsche Übereinstimmungen auftreten, die durch Teilzeichenfolgen verursacht werden, die in längeren Wörtern vorkommen. Wenn Sie beispielsweise versuchen, "dr" zuzuordnen, finden Sie möglicherweise auch "Andrea", "drink", "dry" usw., da "dr" in diesen Wörtern vorkommt. Dies geschieht, weil SEARCH automatisch eine "enthält" -Match ausführt.
Für eine teilweise Korrektur können Sie Leerzeichen um die Suchwörter (dh "dr" oder "dr") hinzufügen, um zu vermeiden, dass "dr" in einem anderen Wort abgefangen wird. Dies schlägt jedoch fehl, wenn "dr" zuerst oder zuletzt in einer Zelle oder neben der Interpunktion angezeigt wird. Dies kann teilweise behoben werden, indem auch um den Originaltext herum Platz hinzugefügt wird. Um dem Anfang und dem Ende von beiden gleichzeitig Platz zu geben, können Sie eine Formel wie die folgende ausprobieren:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Dies behebt jedoch keine Probleme, die durch Interpunktion verursacht werden.
Wenn Sie eine vollständigere Lösung benötigen, besteht eine Option darin, den Text zuerst in einer Hilfsspalte zu normalisieren und dabei auch einen führenden und einen nachfolgenden Leerzeichen hinzuzufügen. Dann können Sie nach ganzen Wörtern suchen, die von Leerzeichen umgeben sind.
