Generische Formel
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,A1)))
Zusammenfassung
Um die Anzahl bestimmter Wörter oder Schlüsselwörter zu zählen, die in einer bestimmten Zelle vorkommen, können Sie eine Formel verwenden, die auf den Funktionen SEARCH, ISNUMBER und SUMPRODUCT basiert. Im gezeigten Beispiel lautet die Formel in C5:
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,B5)))
Dabei ist "Schlüsselwörter" der benannte Bereich E5: E9.
Erläuterung
Hinweis: Wenn ein Schlüsselwort in einer bestimmten Zelle mehrmals vorkommt, wird es nur einmal gezählt. Mit anderen Worten, die Formel zählt nur Instanzen verschiedener Schlüsselwörter.
Der Kern dieser Formel ist der ISNUMBER + SEARCH-Ansatz zum Suchen von Text in einer Zelle, der hier ausführlicher erläutert wird. In diesem Fall suchen wir in jeder Zelle nach allen Wörtern im benannten Bereich "Schlüsselwörter" (E5: E9). Dazu übergeben wir den Bereich als find_text-Argument an SEARCH. Weil wir ein Array von 5 Elementen übergeben:
("green";"orange";"white";"blue";"pink")
Als Ergebnis erhalten wir eine Reihe von 5 Elementen zurück:
(#VALUE!;#VALUE!;1;#VALUE!;14)
Zahlen entsprechen Übereinstimmungen und der #WERT! Fehler bedeutet, dass keine Übereinstimmung gefunden wurde. In diesem Fall verwenden wir ISNUMBER, um das Array in TRUE- und FALSE-Werte zu konvertieren, da es uns egal ist, wo der Text in der Zelle gefunden wurde:
(FALSE;FALSE;TRUE;FALSE;TRUE)
Und das doppelte Negativ (-), um diese in Einsen und Nullen zu ändern:
(0;0;1;0;1)
Die SUMPRODUCT-Funktion gibt dann einfach die Summe des Arrays zurück, in diesem Fall 2.
Umgang mit leeren Schlüsselwörtern
Wenn der Schlüsselwortbereich leere Zellen enthält, funktioniert die Formel nicht richtig, da die SEARCH-Funktion bei der Suche nach einer leeren Zeichenfolge ("") Null zurückgibt. Um alle leeren Zellen im Schlüsselwortbereich zu filtern, können Sie die folgende Variation verwenden:
(=SUMPRODUCT(--ISNUMBER(SEARCH(IF(keywords"",keywords),B5))))
Hinweis: Diese Version ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.
