Generische Formel
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Zusammenfassung
Um eine Zelle zu testen, um festzustellen, ob sie eine von vielen Zeichenfolgen enthält, können Sie eine Formel verwenden, die auf den Funktionen SEARCH, ISNUMBER und SUMPRODUCT basiert. Die kopierte Formel in C5 lautet:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
wo Dinge ist der benannte Bereich E5: E9.
Erläuterung
Wir wollen jede Zelle in B5 testen: B11 zu sehen , ob es eine der Zeichenfolge in den benannten Bereich enthält Dinge (E5: E9). Die Formel, die wir in C5 verwenden, lautet:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Diese Formel basiert auf einer Formel (hier erklärt), die eine Zelle auf einen einzelnen Teilstring überprüft. Wenn die Zelle den Teilstring enthält, gibt die Formel TRUE zurück. Wenn nicht, gibt die Formel FALSE zurück:
ISNUMBER(SEARCH(things,B5))
In diesem Fall geben wir SEARCH jedoch eine Liste von Zeichenfolgen. Da die Dinge 5 Zeichenfolgen enthalten , gibt SEARCH 5 Ergebnisse in einem Array wie dem folgenden zurück:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Wenn SEARCH eine Zeichenfolge findet, wird die Position dieser Zeichenfolge zurückgegeben. Wenn SEARCH keinen String findet, wird ein #VALUE zurückgegeben! Error. Da "gelb" als erstes Wort in B5 angezeigt wird, wird eine 1 angezeigt. Da die anderen Zeichenfolgen nicht gefunden werden, sind die anderen 4 Elemente Fehler.
Dieses Array wird direkt an die ISNUMBER-Funktion zurückgegeben. ISNUMBER gibt dann ein Array von TRUE / FALSE-Werten zurück:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Wenn wir nur einen TRUE im Array haben, wissen wir, dass eine Zelle mindestens einen der gesuchten Strings enthält. Der einfachste Weg, um nach WAHR zu suchen, besteht darin, alle Werte zu addieren. Wir können das mit SUMPRODUCT machen, aber zuerst müssen wir die TRUE / FALSE-Werte mit einem doppelten Negativ (-) wie folgt auf 1s und 0s zwingen:
--ISNUMBER(SEARCH(things,B5))
Dies ergibt ein neues Array, das nur 1s und 0s enthält:
(1;0;0;0;0)
direkt an SUMPRODUCT geliefert:
=SUMPRODUCT((1;0;0;0;0))
Mit nur einem zu verarbeitenden Array fügt SUMPRODUCT die Elemente im Array hinzu und gibt ein Ergebnis zurück. Jedes Ergebnis ungleich Null bedeutet, dass wir einen "Treffer" haben. Wir addieren also> 0, um ein Endergebnis von TRUE oder FALSE zu erzwingen:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Mit einer fest codierten Liste
Es ist nicht erforderlich, einen Bereich für die Liste der zu suchenden Zeichenfolgen zu verwenden. Sie können auch eine Array-Konstante verwenden. Um beispielsweise nach "rot", "blau" und "grün" zu suchen, können Sie eine Formel wie die folgende verwenden:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Falsche Übereinstimmungen verhindern
Ein Problem bei diesem Ansatz besteht darin, dass Sie möglicherweise falsche Übereinstimmungen mit Teilzeichenfolgen erhalten, 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" Übereinstimmung ausführt.
Für einen schnellen Hack 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 mit Interpunktion angezeigt wird.
Wenn Sie eine genauere Lösung benötigen, können Sie den Text zuerst in einer Hilfsspalte normalisieren und dabei auch einen führenden und einen nachfolgenden Leerzeichen hinzufügen. Dann verwenden Sie die Formel auf dieser Seite für den resultierenden Text.
