Xtract: Prüfen, ob ein Begriff aus einer Liste in einem String (Zell-Inhalt) enthalten ist. Groß- / Kleinschreibung wird beachtet.
Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ
Ist in einem Text (Spalte) ein beliebiger Schlüsselbegriff aus einer Liste vorhanden?
In einem Forum wurde in einem anderen Zusammenhang die Frage aufgeworfen, wie aus einem beliebigen String (Text) einer Zelle ein Stichwort gefunden werden und dann in der Nebenspalte der gleichen Zeile wiedergegeben werden kann. Das Stichwort/Suchwort ist aber irgendeines aus einer beliebig langen Liste. Eine kleine Beispiel-Datei mit (der Überschaubarkeit wegen) wenigen Datensätzen soll Ihnen die Situation klar machen:
Je eine Tabelle/Liste mit Texten und Keywords
In Spalte A sind verschiedene Texte, in Spalte C sind die Stichworte aufgeführt, die in Spalte A gesucht und bei einem Fund in Spalte B ausgegeben werden sollen. Zugegeben, diese 7 Keywords wären noch gut mit einem WENN bzw- if – Konstrukt beherrschbar aber bei schon wenig mehr Suchbegriffen kann es ganz schön „eng” werden. 🙄 Da sind rasch die Grenzen des Plain Excel (also Formeln) erreicht (insbesondere auch in Sachen Performance); VBA wäre in solchen Fällen schon über Arrays oder select case wesentlich leistungsfähiger. Power Query verzichtet hier auch nicht vollkommen auf Programm-Code, aber der Code-Block ist wirklich überschaubar. 😉
Laden Sie zu Beginn erst einmal unsere Muster-Datei herunter und öffnen Sie diese natürlich. Wenn Sie auf die Tabelle mit der Überschrift Texte Klicken, dann werden Sie erkennen, dass diese auch den internen Namen Texte hat, und die Keywords haben ebenfalls den identischen Namen für die Tabelle. Importieren Sie nun die Tabelle Texte und speichern Sie diese gleich „ganz normal” per Schließen & laden. Damit wird zwar erst einmal die unveränderte Tabelle als Ergebnis des Power Query in einem neuen Blatt gespeichert, aber das ist im derzeitigen Stadium gut so. Importieren Sie auch die Tabelle Keywords und hier sollten Sie diese Liste gleich per Schließen & laden in… als Nur Verbindung speichern.
Öffnen Sie nun beispielsweise durch Doppelklick im rechten Seitenfenster die Abfrage Keywords. Wechseln Sie zum Register Transformieren und Klicken in der Gruppe Beliebige Spalte auf das Symbol In Liste konvertieren. Dadurch ändert sich die Überschrift der Spalte zu Liste, der Name der Abfrage bleibt aber bestehen; beachten Sie aber, dass sich das Symbol dieser Query von „Tabelle” zu „Liste” geändert hat:
Unterschiedliche Symbole der beiden Abfragen im linken Seitenfenster
Nun kommt der wichtigste Schritt, das Erstellen des kleinen Programms in der Sprache M. Aber ich mache es Ihnen leicht(er) und stelle Ihnen gleich den kompletten Code zur Verfügung, den Sie dann per copy/paste in das Projekt übernehmen können.
Beginnen Sie damit, das Menü-Register Start zu aktivieren. Ganz rechts im Menüband sehen Sie die Gruppe Neue Abfrage und dort Klicken Sie auf Neue Quelle. Wählen Sie im DropDown Andere Quellen und dort wiederum Leere Abfrage. Power Query erstellt eine neue Abfrage, die sich erst einmal ausgesprochen spartanisch darstellt:
Eine absolut leere, neue Abfrage
Wechseln Sie zum Register Ansicht und wählen in der Gruppe Weitere das einzige Symbol: Erweiterter Editor. Es öffnet sich ein Dialog, wo das Grundgerüst einer leeren Abfrage in der Sprache M bereits vorgegeben ist:
Das Grundgerüst einer leeren Abfrage in der Sprache M
Markieren Sie den kompletten Text im großen Kasten (alle 4 Zeilen) und löschen alles. Schreiben Sie nun in exakt dieser Schreibweise den hierunter aufgeführten Code oder kopieren Sie ihn vorzugsweise hier aus dem Blog…
(TextSpalte) =>
let
//Prüfung, ob ein Element der Keywords-Liste in 'Texte' enthalten ist
KeyWordGefunden = List.Transform(List.Buffer(Keywords),
each Text.Contains(TextSpalte, _, Comparer.OrdinalIgnoreCase)),
//Zeile/Position bei Treffer
Position = List.PositionOf(KeyWordGefunden, true),
//Falls kein Treffer in Zeile => null, sonst das KeyWord ausgeben
RC = if Position < 0 then null else Keywords{Position}
in
RC
… und fügen den Inhalt der Zwischenablage in das eben geleerte Textfeld ein:
Die neue Abfrage mit dem eingetragenen M‑Code
Nach einem Kick auf Fertig sieht das alles erst einmal recht ominös aus, aber das hat seine Richtigkeit:
Vielleicht etwas verwirrend, aber Sie werden es in dieser Form nicht brauchen …
Im linken Seitenfenster erkennen Sie den Namen Abfrage1 mit dem Symbol für eine Funktion links des Namens. Und da es sich ja auch um eine Funktion handelt und der Name sowieso nicht gerade aussagekräftig ist, ändern Sie den Namen beispielsweise auf fn_KeyWordSuche (oder einen Ihnen genehmen Begriff nach dem „fn_”). Das führende fn_ ist aus der Sicht des Power Query nicht erforderlich dient aber der besseren Unterscheidbarkeit.
Damit ist die eigentliche Arbeit getan, zumindest was die entscheidende Vorarbeit betrifft. Bleibt „nur” noch, die erstellte Funktion zu nutzen. Dazu wechseln Sie zum Register Spalte hinzufügen | Benutzerdefinierte Funktion aufrufen und tragen zuerst bei Neuer Spaltenname beispielsweise Gefunden ein. Anschließend erweitern Sie das Feld Funktionsabfrage und wählen die gewünschte Abfrage:
Auswahl der (einzigen) Benutzerdefinierten Funktion
Sofort danach wird für die Variable TextSpalte das optionale Argument in einen neuen Block unterhalb der bisherigen Anzeige dargestellt:
Das optionale Argument für die Variable TextSpalte wird bereits vorgegeben
Jetzt noch OK und das Ergebnis zeigt sich in voller Pracht:
Das Ergebnis der Benutzerdefinierten Funktions-Anwendung
Hinweis: Das gleiche Ergebnis hätten Sie erreichen können, wenn Sie statt des Symbols für die Benutzerdefinierte Funktion den Punkt Benutzerdefinierte Spalte aufgerufen und dort nach dem anpassen der Überschrift diese Formel geschrieben hätten:
= fn_KeyWordSuche([Texte])
Das war’s dann auch. Schließen & laden (in…) und die Arbeit ist getan. Wenn Sie meine Lösung sehen wollen, dann laden Sie diese hier herunter.
Epilog
Verschiedene Punkte bedürfen noch einer Erwähnung, damit beispielsweise der Sinn und Zweck der Übung klarer wird. Die ursprüngliche Zielsetzung des Fragestellers im Forum war, die Spalte Texte nach den Schlüsselbegriffen zu gruppieren bzw. zu ordnen. Das ist jetzt natürlich problemlos möglich.
Mehrere Suchbegriffe in 1 Zeile
Die Zeile 3 wird Ihnen vielleicht aufgefallen sein. Hier steht im Text das Wort Brandenburg vor Berlin und dennoch wird in der Spalte Gefunden Berlin als Keyword ausgegeben. Der Grund dafür: In der Abfrage/Liste Keywords steht Berlin an früherer Position als Brandenburg. Und wenn ein Suchbegriff gefunden wurde, wird das Wort als Ergebnis der Funktion zurückgegeben und die Funktion beendet ihre Arbeit für diese Zeile der Abfrage. – Mit etwas Eigeninitiative wird Ihnen gewiss ein Weg einfallen, wenn Sie auch den zweiten Begriff angezeigt bekommen wollen oder müssen.
Zum Code der Funktion
Nein, an dieser Stelle ist keine detaillierte Erklärung des Codes vorgesehen. Ein einziger Punkt ist mir aber wichtig: Damit das Suchwort in Groß- als auch Kleinschreibung gefunden wird, ist im Code Comparer.OrdinalIgnoreCase vermerkt. Ohne diese Anweisung würde ausschließlich jene Schreibweise gefunden werden, wie sie in der Liste Keywords geschrieben worden ist; in Zeile 10 würde dann null (im Editor) erscheinen, da das Suchwort „Berlin” in der Liste ja in Großschreibung vorliegt und in der Spalte Texte nur in Kleinbuchstaben, da es ja ein Teil des Wortes „Ostberlin” ist.
Ein kleiner Gag
Last but not least die Anmerkung, dass die Zeile 5 keineswegs politisch aber sehr wohl geografisch gemeint ist. Bei „Sibirien” wird wohl fast jeder an die riesige Region im Osten unseres Kontinents denken, aber schauen Sie gerne einmal in eine Landkarte Ihrer Wahl und suchen in Schleswig-Holstein die Stadt Elmshorn. Es ist praktisch ein Stadtteil und auch eine Straße hat dort diesen Namen. 😎
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag (z.B. 5,00€) Ihrerseits freuen …