Fließtexte nach mehreren Begriffen durchsuchen

Freitextfelder sind ja praktisch und werden auch in Excel gerne und exzessiv genutzt. Bis man darin etwas Bestimmtes finden möchte. Zum Beispiel Hinweise auf Beschwerden, Fehler oder Reklamationen.

Wie man Excel mit Hilfe von Tabellenfunktionen dazu bringt, gleich nach mehreren möglichen Begriffen zu suchen, zeige ich dir in diesem Artikel.

Und so geht’s:

Beispieldatei herunterladen

Inhaltsverzeichnis

Das Beispielszenario

Im Rahmen einer Zufriedenheitsbefragung wurden unsere Kunden telefonisch befragt und ihr Feedback zu unseren Produkten in einer Tabelle erfasst. Die Antworten wurden als Freitext in einer Bemerkungsspalte eingetragen:

Rohdaten aus einer Zufriedenheitsbefragung

Neben viel Lob gab es leider auch negatives Feedback. Aber wie findet man in einer Spalte mit lauter Fließtext Begriffe wie „Beschwerde“, „Reklamation“, „Fehler“ etc.?

Suchfunktionen in Excel

Excel bietet eine ganze Reihe an Tabellenfunktionen, mit denen man Texte nach Suchbegriffen oder bestimmten Werten durchsuchen lassen kann:

=SUCHEN()
=FINDEN()
=VERGLEICH()

VERGLEICH (oder auch das neue XVERGLEICH) wird häufig in Kombination mit der INDEX-Funktion verwendet. Es durchsucht einen Bereich nach einem Wert und gibt beim ersten Treffer die Position innerhalb des Zellenbereichs zurück.

Die beiden Funktionen SUCHEN und FINDEN sind identisch in ihrer Syntax:
=FINDEN(Suchtext;Text;[ErstesZeichen]) =SUCHEN(Suchtext;Text;[ErstesZeichen])

Der Unterschied liegt darin, dass FINDEN zwischen Groß- und Kleinschreibung unterscheidet, während mit SUCHEN auch Platzhalter * und ? verwendet werden können, jedoch nicht in der Schreibweise unterschieden wird. Zurückgegeben wird bei einem Treffer die Position innerhalb des Textes. Also innerhalb einer einzelnen Zelle. Wird nichts gefunden, liefert die Funktion einen #WERT!-Fehler.

Falls es dich interessiert: In diesem Artikel habe ich die beiden Funktionen genauer miteinander verglichen.

In einer neuen Spalte „Reklamation“ nutze ich SUCHEN, um testweise nach dem Begriff „Beschwerde“ zu suchen:

Die SUCHEN-Funktion für einen Suchbegriff

In den beiden markierten Zeilen wurde der Begriff gefunden und die jeweilige Position innerhalb des Textes angezeigt. Da uns die genaue Position aber nicht interessiert, können wir zusätzlich noch mit der ISTZAHL-Funktion arbeiten. Gibt es einen Treffer und somit eine Positionsnummer, liefert die Funktion jetzt einen WAHR-Wert. Bei keinem Treffer (also einem #WERT-Fehler) wird ein logisches FALSCH zurückgeliefert:

Mit ISTZAHL wird nur noch WAHR und FALSCH geliefert

Damit kann man in der Spalte „Reklamation“ bequem auf WAHR filtern und erhält alle Datensätze, in denen der Begriff „Beschwerde“ vorkommt.

Besonders komfortabel geht das über einen Datenschnitt:
Aktive Zelle irgendwo in der Tabelle positionieren, dann über das Menü „Einfügen | Datenschnitt“ einen Datenschnitt für die Spalte „Reklamation“ einfügen und nach Herzenslust filtern:

Bequemes Filtern mit einem Datenschnitt

Gar nicht mal so schlecht!

Die Sache hat nur einen entscheidenden Haken:
Wenn ich nach weiteren möglichen Begriffen wie „Reklamation“, „Fehler“ oder gar „Rückerstattung“ suchen möchte, muss ich die Formel jedes Mal anpassen…

Lösung Teil 1: Eine Tabelle mit Suchbegriffen

Aus dem besagten Grund lege ich nun eine kleine formatierte („intelligente“) Tabelle mit dem Namen „t_Suchbegriffe“ an, in dich ich alle mir relevant erscheinenden Begriffe eintrage:

Eine Hilfstabelle mit Suchbegriffen

Jetzt muss noch die Formel angepasst werden, damit die Suchbegriffe aus dieser Hilfstabelle verwendet werden. Wer nun aber glaubt, den statischen Text einfach durch einen Verweis auf die neue Tabelle ersetzen zu können, wird leider enttäuscht sein:

Die Formel liefert leider nicht das gewünschte Ergebnis

Die erste Reklamation wird zwar noch gefunden, aber bei allen anderen wird ein FALSCH angezeigt. Was stimmt hier nicht?

Das Problem entsteht bereits bei der Eingabe der Formel. Ich möchte ja eigentlich die gesamte Tabelle mit den Suchbegriffen verwenden und gebe daher folgende Formel ein:
=SUCHEN(t_Suchbegriffe[Suchbegriffe];[@Bemerkungen])

Fehlermeldung bei der Formeleingabe

Beim Drücken der Eingabetaste kommt jedoch eine Fehlermeldung. Wenn ich nun leichtsinnigerweise den Vorschlag mit einem Klick auf „Ja“ annehme, wird die Formel entsprechend angepasst und ein @-Zeichen beim Bezug auf die Spalte „Suchbegriff“ eingefügt:

Excel passt die Formel automatisch an

Das @-Zeichen bedeutet, dass immer nur auf den Wert in der aktuellen Zeile der betreffenden Spalte zugegriffen wird. Das folgende Bild soll das verdeutlichen:

Darum werden falsche Ergebnisse geliefert

Die erste Bemerkung wird nur mit dem Begriff „Beschwerde“ abgeglichen und liefert daher ein FALSCH, weil dieser Begriff nicht vorkommt.

Die zweite Bemerkung sucht nur nach dem Begriff „Reklamation“ in der zweiten Zeile der Suchbegriffe-Tabelle und kommt daher ebenfalls zum Ergebnis FALSCH, genauso in der dritten Bemerkungszeile.

In der vierten Bemerkung wird nur nach dem vierten Begriff „Rückerstattung“ gesucht, was einen Treffer liefert. Die anderen, ebenfalls in der Bemerkung ebenfalls vorkommenden Begriffe „Fehler“ und „Reklamation“ werden überhaupt nicht berücksichtigt.

Und ab der fünften Bemerkung werden nur noch FALSCH-Werte geliefert, da es in der Suchbegriffetabelle keine weiteren Zeilen mehr gibt. Ein ziemlicher Mist, oder?

Aber das lässt sich lösen, wir müssen nur ein paar kleine Schritte zurückgehen.

Lösung Teil 2: Die Suchformel muss angepasst werden

Um zu demonstrieren, wie die korrekte Formel eigentlich funktionieren sollte, schreibe ich sie neben die Tabelle und spreche dabei nur eine einzelne Zelle an, in der sich die gesuchten Begriffe in der Bemerkung finden lassen:
=SUCHEN(t_Suchbegriffe[Suchbegriffe];t_KundenserviceTickets[@Bemerkungen])

Die Auswertung der Suchbegriffe für eine einzelne Zelle

Die Formel aus Zelle F7 läuft im Bild oben in insgesamt 4 Zeilen über, da es 4 Begriffe in meiner Suchbegriffetabelle gibt.

WICHTIG:
Falls du eine ältere Excel-Version als Excel 2021 im Einsatz hast (Excel 2019, Excel 2016) musst du im Beispiel oben zuerst den Bereich F7 bis F10 markieren und die Formel mit Strg+Umschalt+Eingabe abschließen. Dann wird eine Matrix-Formel daraus und du bekommst die gewünschte Ausgabe.
Anwender in aktuelleren Excel-Versionen geben die Formel ganz normal ohne vorherige Markierung und „Affengriff“ ein.

Der erste Suchbegriff wird nicht gefunden, daher ein #WERT!-Fehler. Für die anderen drei Begriffe liefert die Funktion die jeweilige Position in der Bemerkungsspalte. Wenn die Tabelle mit den Suchbegriffen irgendwann länger wird, werden pro Bemerkungseintrag auch entsprechend mehr Ergebnisse erzeugt.

Um nun eine potenzielle Reklamation zu erkennen, würde es schon ausreichen, wenn nur ein Ergebniswert eine Zahl liefert. Damit man die momentan 4 Ergebnisse auf einen Wert verdichten kann, muss die Formel angepasst werden.

Im ersten Schritt kommt wieder ISTZAHL zum Einsatz:
=ISTZAHL(SUCHEN(t_Suchbegriffe[Suchbegriffe];t_KundenserviceTickets[@Bemerkungen]))

Erweiterung der Formel um ISTZAHL

Durch zwei vorangestellte Minuszeichen werden aus WAHR und FALSCH die Werte 0 und 1:
=ISTZAHL(SUCHEN(t_Suchbegriffe[Suchbegriffe];t_KundenserviceTickets[@Bemerkungen]))

Zwei Minuszeichen wandeln WAHR und FALSCH in 1 und 0 um

Und jetzt nur noch die Summe darüber bilden und übrig bleibt ein einzelner Wert mit der Anzahl der gefundenen Suchbegriffe in der betreffenden Bemerkung:
=SUMME(--ISTZAHL(SUCHEN(t_Suchbegriffe[Suchbegriffe];t_KundenserviceTickets[@Bemerkungen])))

Die SUMME verdichtet die Ergebnisse auf einen Wert

Diese Formel kann nun in der Spalte „Reklamationen“ verwendet werden. Die wir uns somit innerhalb der Tabelle befinden, kann in der Formel auf den Namen t_KundenserviceTickets verzichtet werden:
=SUMME(--ISTZAHL(SUCHEN(t_Suchbegriffe[Suchbegriffe];[@Bemerkungen])))

Die Formel funktioniert auch in der Gesamttabelle

Da es nun viele unterschiedliche Werte geben kann, wird das Filtern im Datenschnitt etwas unpraktisch. Stattdessen sollen wieder nur WAHR- und FALSCH-Angaben gezeigt werden. Daher also eine letzte Anpassung der Formel. Damit wird einfach geprüft, ob der Wert größer als 0 ist:
=SUMME(--ISTZAHL(SUCHEN(t_Suchbegriffe[Suchbegriffe];[@Bemerkungen])))>0

Feinschliff: Vergleich gegen den Wert 0

Nun kannst du die Tabelle mit den Suchbegriffen um weitere relevante Einträge ergänzen, um etwaige Reklamationen noch treffsicherer zu filtern.

Übrigens:
Meine Excel-Kollegin Hildegard Hügemann hat kürzlich eine Lösung für ein ähnlich gelagertes Poblem mit Power Query vorgestellt. Bei Interesse findest du den Artikel auf dem Blog von office-kompetenz

Fazit

Die Verwendung von mehreren Suchbegriffen ist in den klassischen Tabellenfunktionen nicht vorgesehen. Mit dem Trick über eine separate Tabelle und ein paar geschickte kombinierte Funktionen kommt man aber doch zum gewünschten Ziel.

Auch Anwender Excel 2019 und älter können die vorgestellte Lösung nutzen, sofern sie die Formeln als Matrix-Formeln eingeben (Tastenkombination Strg+Umschalt+Eingabe)

Hast du vielleicht schon konkrete Anwendungsfälle in deinem Bereich gefunden? Oder kennst du noch bessere Lösungen? Dann lass uns in den Kommentaren wissen!