Generische Formel
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Zusammenfassung
Um Text mithilfe von Schlüsselwörtern mit einer "enthält" -Match zu kategorisieren, können Sie die SEARCH-Funktion mithilfe von INDEX und MATCH verwenden. Im gezeigten Beispiel lautet die Formel in C5:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
Dabei sind Schlüsselwörter der benannte Bereich E5: E14 und Kategorien der benannte Bereich F5: F14.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.
Erläuterung
Im Kern ist dies eine INDEX- und MATCH-Funktion.
Innerhalb der MATCH - Funktion verwenden wir die Suchfunktion Zellen in Spalte B für jede gelistete Schlüsselwort in den genannten Bereich suchen Schlüsselwörter (E5: E14):
SEARCH(keywords,B5)
Weil wir für mehrere Artikel (in dem genannten Bereich suchen keywords ), werden wir mehrere Ergebnisse wie folgt zurück:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Der Wert! Fehler tritt auf, wenn SEARCH den Text nicht finden kann. Wenn SEARCH eine Übereinstimmung findet, wird eine Zahl zurückgegeben, die der Position des Textes in der Zelle entspricht.
Um diese Ergebnisse in ein benutzerfreundlicheres Format zu ändern, verwenden wir die ISNUMBER-Funktion, die alle Werte wie folgt in TRUE / FALSE konvertiert:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Dieses Array geht als lookup_array in die MATCH-Funktion, wobei der lookup_value als TRUE festgelegt ist. MATCH gibt die Position des ersten TRUE zurück, den es im Array findet (in diesem Fall 7), das der INDEX-Funktion als row_num bereitgestellt wird:
=INDEX(categories,7)
INDEX gibt als Endergebnis das 7. Element in den Kategorien "Auto" zurück.
Mit XLOOKUP
Mit der XLOOKUP-Funktion kann diese Formel etwas vereinfacht werden. XLOOKUP kann dieselbe Logik verwenden, die in der obigen MATCH-Funktion verwendet wurde. Die entsprechende Formel lautet also:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP sucht den ersten TRUE im Array und gibt den entsprechenden Wert aus Kategorien zurück .
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 usw. 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 können Sie nach ganzen Wörtern suchen, die von Leerzeichen umgeben sind.
