Inhalte
Die Suche nach Schlüsselwörtern im Quelltext ist eine der häufigsten Aufgaben bei der Arbeit mit Daten. Betrachten wir die Lösung anhand des folgenden Beispiels auf verschiedene Weise:
Nehmen wir an, Sie und ich haben eine Liste mit Schlüsselwörtern – den Namen von Automarken – und eine große Tabelle aller Arten von Ersatzteilen, in der Beschreibungen manchmal eine oder mehrere solcher Marken gleichzeitig enthalten können, wenn das Ersatzteil zu mehr als einer passt Marke des Autos. Unsere Aufgabe besteht darin, alle erkannten Schlüsselwörter in benachbarten Zellen durch ein bestimmtes Trennzeichen (z. B. ein Komma) zu finden und anzuzeigen.
Methode 1. Power-Abfrage
Natürlich machen wir unsere Tabellen zuerst per Tastaturkürzel dynamisch („smart“) Ctrl+T oder Befehle Startseite – Als Tabelle formatieren (Home — Als Tabelle formatieren), geben Sie ihnen Namen (z Stempelи Ersatzteile) und einzeln in den Power Query-Editor laden, indem Sie auf der Registerkarte auswählen Daten – Aus Tabelle/Bereich (Daten – Aus Tabelle/Bereich). Wenn Sie ältere Versionen von Excel 2010-2013 haben, bei denen Power Query als separates Add-In installiert ist, befindet sich die gewünschte Schaltfläche auf der Registerkarte Power Query. Wenn Sie eine brandneue Version von Excel 365 haben, dann die Schaltfläche Aus Tabelle/Bereich habe jetzt dort angerufen Mit Blättern (Vom Blatt).
Nach dem Laden jeder Tabelle in Power Query kehren wir mit dem Befehl zu Excel zurück Startseite – Schließen und laden – Schließen und laden nach… – Nur Verbindung herstellen (Home — Schließen & Laden — Schließen & Laden nach… — Nur Verbindung herstellen).
Lassen Sie uns nun eine doppelte Anfrage erstellen Ersatzteileindem Sie mit der rechten Maustaste darauf klicken und auswählen Doppelte Anfrage (Doppelte Abfrage), und benennen Sie dann die resultierende Kopieranforderung in um Die Ergebnisse und wir werden weiter mit ihm zusammenarbeiten.
Die Logik der Aktionen ist die folgende:
- Auf der Registerkarte Erweitert Spalte hinzufügen wähle ein Team Benutzerdefinierte Spalte (Spalte hinzufügen – Benutzerdefinierte Spalte) und geben Sie die Formel ein = Marken. Nach dem Anklicken OK Wir erhalten eine neue Spalte, in der sich in jeder Zelle eine verschachtelte Tabelle mit einer Liste unserer Schlüsselwörter – Autoherstellermarken – befindet:
- Verwenden Sie die Schaltfläche mit den Doppelpfeilen in der Kopfzeile der hinzugefügten Spalte, um alle verschachtelten Tabellen zu erweitern. Gleichzeitig multiplizieren sich die Zeilen mit Beschreibungen der Ersatzteile mit einem Vielfachen der Anzahl der Marken, und wir erhalten alle möglichen Paar-Kombinationen von „Ersatzteil-Marke“:
- Auf der Registerkarte Erweitert Spalte hinzufügen wähle ein Team Bedingte Spalte (Bedingte Spalte) und eine Bedingung für die Überprüfung des Vorkommens eines Schlüsselworts (Marke) im Quelltext (Teilebeschreibung) festlegen:
- Um die Groß- und Kleinschreibung der Suche zu ignorieren, fügen Sie das dritte Argument manuell in die Bearbeitungsleiste ein Vergleichen.OrdinalIgnoreCase zur Vorkommensprüfungsfunktion Text.Enthält (Wenn die Bearbeitungsleiste nicht sichtbar ist, kann sie auf der Registerkarte aktiviert werden Bewertung):
- Wir filtern die resultierende Tabelle, lassen nur Einsen in der letzten Spalte, dh Übereinstimmungen, und entfernen die unnötige Spalte Vorkommen.
- Identische Beschreibungen mit dem Befehl gruppieren Gruppiere nach Tab Transformation (Umwandeln – Gruppieren nach). Als Aggregationsvorgang wählen Sie Alle Zeilen (Alle Reihen). Als Ausgabe erhalten wir eine Spalte mit Tabellen, die alle Details für jedes Ersatzteil enthält, einschließlich der Marken der von uns benötigten Autohersteller:
- Um Noten für jeden Teil zu extrahieren, fügen Sie eine weitere berechnete Spalte auf der Registerkarte hinzu Hinzufügen einer Spalte – Benutzerdefinierte Spalte (Spalte hinzufügen – Benutzerdefinierte Spalte) und verwenden Sie eine Formel, die aus einer Tabelle besteht (sie befinden sich in unserer Spalte Details) und den Namen der extrahierten Spalte:
- Wir klicken auf die Schaltfläche mit den Doppelpfeilen in der Kopfzeile der resultierenden Spalte und wählen den Befehl aus Werte extrahieren (Werte extrahieren)So geben Sie Stempel mit einem beliebigen Trennzeichen aus:
- Entfernen einer unnötigen Spalte Details.
- Um der resultierenden Tabelle die verschwundenen Teile hinzuzufügen, bei denen keine Marken in den Beschreibungen gefunden wurden, führen wir das Verfahren zum Kombinieren der Abfrage durch Ergebnis mit Originalanfrage Ersatzteile Taste im nun erscheinenden Bestätigungsfenster nun wieder los. Kombinieren Tab Startseite (Startseite — Abfragen zusammenführen). Verbindungstyp - Äußere Verbindung rechts (Rechter äußerer Join):
- Jetzt müssen wir nur noch die überzähligen Spalten entfernen und die restlichen umbenennen – und unsere Aufgabe ist gelöst:
Methode 2. Formeln
Wenn Sie eine Version von Excel 2016 oder höher haben, dann lässt sich unser Problem mit der neuen Funktion sehr kompakt und elegant lösen KOMBINIEREN (TEXTVERBINDUNG):
Die Logik hinter dieser Formel ist einfach:
- Funktion SUCHE (FINDEN) sucht nach dem Vorkommen jeder Marke der Reihe nach in der aktuellen Beschreibung des Teils und liefert entweder die Seriennummer des Symbols, ab dem die Marke gefunden wurde, oder den Fehler #WERT! wenn die Marke nicht in der Beschreibung steht.
- Dann mit der Funktion IF (WENN) и EOSCHIBKA (FEHLER) Wir ersetzen die Fehler durch eine leere Textzeichenfolge „“, und die Ordnungszahlen der Zeichen durch die Markennamen selbst.
- Das resultierende Array aus leeren Zellen und gefundenen Marken wird mithilfe der Funktion durch ein bestimmtes Trennzeichen zu einer einzelnen Zeichenfolge zusammengesetzt KOMBINIEREN (TEXTVERBINDUNG).
Leistungsvergleich und Power Query-Abfragepufferung zur Beschleunigung
Nehmen wir für den Leistungstest eine Tabelle mit 100 Ersatzteilbeschreibungen als Ausgangsdaten. Darauf erhalten wir folgende Ergebnisse:
- Neuberechnungszeit nach Formeln (Methode 2) – 9 Sek. beim ersten Kopieren der Formel auf die gesamte Spalte und 2 Sek. bei wiederholten (Puffereffekte, wahrscheinlich).
- Die Aktualisierungszeit der Power Query-Abfrage (Methode 1) ist viel schlechter – 110 Sekunden.
Natürlich hängt viel von der Iron eines bestimmten PCs und der installierten Version von Office und Updates ab, aber das Gesamtbild ist meiner Meinung nach klar.
Um eine Power Query-Abfrage zu beschleunigen, puffern wir die Nachschlagetabelle Stempel, da es sich im Verlauf der Abfrageausführung nicht ändert und nicht ständig neu berechnet werden muss (wie es Power Query de facto tut). Dazu verwenden wir die Funktion Tabelle.Puffer aus der integrierten Power Query-Sprache M.
Öffnen Sie dazu eine Abfrage Die Ergebnisse und auf der Registerkarte Bewertung Drücken Sie den Knopf Erweiterter Editor (Ansicht – Erweiterter Editor). Fügen Sie im sich öffnenden Fenster eine Zeile mit einer neuen Variablen hinzu Markus 2, die eine gepufferte Version unseres Autoherstellerverzeichnisses sein wird, und verwenden Sie diese neue Variable später im folgenden Abfragebefehl:
Nach einer solchen Verfeinerung erhöht sich die Aktualisierungsgeschwindigkeit unserer Anfrage um fast das Siebenfache – bis zu 7 Sekunden. Ganz was anderes 🙂
- Fuzzy-Textsuche in Power Query
- Massentextersetzung durch Formeln
- Massentextersetzung in Power Query mit List.Accumulate-Funktion