Inhalte
Eines der meistgesehenen Videos auf meinem YouTube-Kanal ist ein Video über Flash Fill in Microsoft Excel. Das Wesentliche an diesem Tool ist, dass Sie, wenn Sie Ihre Quelldaten irgendwie transformieren müssen, einfach damit beginnen müssen, das gewünschte Ergebnis in die angrenzende Spalte einzugeben. Nach mehreren manuell eingegebenen Zellen (normalerweise reichen 2-3 aus), „versteht“ Excel die Logik der von Ihnen benötigten Transformationen und setzt automatisch fort, was Sie eingegeben haben, und erledigt die monotone Arbeit für Sie:
Die Quintessenz der Effizienz. Der magische „Mach es richtig“-Button, den wir alle so sehr lieben, oder?
Tatsächlich gibt es ein Analogon eines solchen Tools in Power Query – dort heißt es Spalte von Beispielen (Spalte aus Beispielen). Tatsächlich ist dies eine kleine künstliche Intelligenz, die in Power Query integriert ist und schnell aus Ihren Daten lernen und sie dann transformieren kann. Schauen wir uns seine Fähigkeiten in mehreren praktischen Szenarien genauer an, um zu verstehen, wo er uns bei realen Aufgaben nützlich sein kann.
Beispiel 1. Text kleben/schneiden
Nehmen wir an, wir haben eine solche „intelligente“ Tabelle in Excel mit Daten zu Mitarbeitern:
Laden Sie es ganz normal in Power Query – mit der Schaltfläche Aus Tabelle/Bereich Tab Datum (Daten – Aus Tabelle/Bereich).
Angenommen, wir müssen für jeden Mitarbeiter eine Spalte mit Nachnamen und Initialen hinzufügen (Ivanov SV für den ersten Mitarbeiter usw.). Um dieses Problem zu lösen, können Sie eine von zwei Methoden verwenden:
- Klicken Sie mit der rechten Maustaste auf die Spaltenüberschrift mit den Quelldaten und wählen Sie den Befehl aus Spalte aus Beispielen hinzufügen (Spalte aus Beispielen hinzufügen);
- Wählen Sie eine oder mehrere Spalten mit Daten und auf der Registerkarte aus Spalte hinzufügen wähle ein Team Spalte von Beispielen. Hier können Sie in der Dropdown-Liste festlegen, ob alle oder nur ausgewählte Spalten analysiert werden sollen.
Dann ist alles einfach – in der Spalte, die rechts erscheint, beginnen wir, Beispiele für die gewünschten Ergebnisse einzugeben, und die in Power Query eingebaute künstliche Intelligenz versucht, unsere Transformationslogik zu verstehen und von alleine weiterzumachen:
Übrigens können Sie die richtigen Optionen in beliebige Zellen dieser Spalte eingeben, also nicht zwingend von oben nach unten und hintereinander. Außerdem können Sie mithilfe der Kontrollkästchen in der Titelleiste später problemlos Spalten zur Analyse hinzufügen oder daraus entfernen.
Achten Sie auf die Formel oben im Fenster – diese erstellt Smart Power Query, um die von uns benötigten Ergebnisse zu erhalten. Dies ist übrigens der grundlegende Unterschied zwischen diesem Tool und Sofortige Füllung im Excel-Format. Instant Filling funktioniert wie eine „Black Box“ – sie zeigen uns nicht die Logik der Transformationen, sondern liefern einfach vorgefertigte Ergebnisse, die wir als selbstverständlich ansehen. Hier ist alles transparent und Sie können jederzeit absolut nachvollziehen, was genau mit den Daten passiert.
Wenn Sie sehen, dass Power Query „die Idee erfasst hat“, können Sie getrost auf die Schaltfläche klicken OK oder Tastenkürzel Ctrl+Enter – Eine benutzerdefinierte Spalte mit einer von Power Query erfundenen Formel wird erstellt. Übrigens kann sie später ganz einfach als normale manuell erstellte Spalte bearbeitet werden (mit dem Befehl Hinzufügen einer Spalte – Benutzerdefinierte Spalte), indem Sie auf das Zahnradsymbol rechts neben dem Schrittnamen klicken:
Beispiel 2: Fall wie in Sätzen
Wenn Sie mit der rechten Maustaste auf die Spaltenüberschrift mit Text klicken und den Befehl auswählen Transformation (Verwandeln), dann sehen Sie drei Befehle, die für die Änderung des Registers verantwortlich sind:
Praktisch und cool, aber in dieser Liste hat mir persönlich zum Beispiel immer eine weitere Option gefehlt – Fall wie in Sätzen, wenn die Großschreibung (Großschreibung) nicht der erste Buchstabe in jedem Wort, sondern nur der erste Buchstabe in der Zelle wird, und der Rest des Textes, wenn Dies wird in Kleinbuchstaben (Kleinbuchstaben) angezeigt.
Dieses fehlende Feature ist mit künstlicher Intelligenz einfach zu implementieren Spalten aus Beispielen – Geben Sie einfach ein paar Optionen ein, damit Power Query im gleichen Sinne fortfahren kann:
Als Formel verwendet Power Query hier eine Reihe von Funktionen Text.Upper и Text.Lower, Umwandlung von Text in Groß- bzw. Kleinbuchstaben und Funktionen Text.Start и Text.Mitte – Analoga der Excel-Funktionen LEFT und PSTR, die eine Teilzeichenfolge aus dem Text von links und von der Mitte extrahieren können.
Beispiel 3. Permutation von Wörtern
Manchmal ist es bei der Verarbeitung der empfangenen Daten erforderlich, die Wörter in den Zellen in einer bestimmten Reihenfolge neu anzuordnen. Natürlich können Sie die Spalte durch das Trennzeichen in einzelne Wortspalten unterteilen und dann in der angegebenen Reihenfolge wieder zusammenkleben (Leerzeichen nicht vergessen), aber mit Hilfe des Tools Spalte von Beispielen alles wird viel einfacher:
Beispiel 4: Nur Zahlen
Eine weitere sehr wichtige Aufgabe besteht darin, nur Zahlen (Zahlen) aus dem Inhalt der Zelle herauszuziehen. Wechseln Sie wie zuvor nach dem Laden von Daten in Power Query zur Registerkarte Spalte hinzufügen – Spalte aus Beispielen und füllen Sie ein paar Zellen manuell aus, damit das Programm versteht, was genau wir bekommen wollen:
Bingo!
Auch hier lohnt sich ein Blick auf den oberen Rand des Fensters, um sicherzustellen, dass Query die Formel korrekt generiert hat – in diesem Fall enthält sie eine Funktion Text. Auswählen, das, wie Sie sich vorstellen können, die angegebenen Zeichen gemäß der Liste aus dem Quelltext extrahiert. Anschließend kann diese Liste bei Bedarf natürlich einfach in der Bearbeitungsleiste bearbeitet werden.
Beispiel 5: Nur Text
Ähnlich wie im vorherigen Beispiel können Sie nur den Text herausziehen und umgekehrt – alle Zahlen, Satzzeichen usw. löschen.
In diesem Fall wird eine bereits entgegengesetzte Funktion verwendet – Text.Remove, die gemäß einer vorgegebenen Liste Zeichen aus dem ursprünglichen String entfernt.
Beispiel 6: Extrahieren von Daten aus einem alphanumerischen Brei
Power Query kann auch in schwierigeren Fällen helfen, wenn Sie nützliche Informationen aus dem alphanumerischen Brei in einer Zelle extrahieren müssen, z. B. die Kontonummer aus der Beschreibung des Zahlungszwecks auf einem Kontoauszug erhalten:
Beachten Sie, dass die von Power Query generierte Konvertierungsformel recht komplex sein kann:
Zur Erleichterung des Lesens und Verstehens kann es mithilfe eines kostenlosen Online-Dienstes in eine viel vernünftigere Form umgewandelt werden. Power Query-Formatierer:
Sehr praktische Sache – Respekt an die Macher!
Beispiel 7: Konvertieren von Datumsangaben
Werkzeug Spalte von Beispielen kann auch auf date- oder datetime-Spalten angewendet werden. Wenn Sie die ersten Ziffern eines Datums eingeben, zeigt Power Query hilfreicherweise eine Liste aller möglichen Konvertierungsoptionen an:
So können Sie das ursprüngliche Datum ganz einfach in ein beliebiges exotisches Format umwandeln, z. B. „Jahr-Monat-Tag“:
Beispiel 8: Kategorisierung
Wenn wir das Tool verwenden Spalte von Beispielen zu einer Spalte mit numerischen Daten funktioniert es anders. Angenommen, wir haben Testergebnisse von Mitarbeitern in Power Query geladen (bedingte Punktzahlen im Bereich 0-100) und wir verwenden die folgende bedingte Abstufung:
- Meister – diejenigen, die mehr als 90 Punkte erzielt haben
- Experten – Punkte von 70 bis 90
- Benutzer – von 30 bis 70
- Anfänger – diejenigen, die weniger als 30 Punkte erzielt haben
Wenn wir der Liste eine Spalte aus den Beispielen hinzufügen und anfangen, diese Abstufungen manuell anzuordnen, dann wird Power Query sehr bald unsere Idee aufgreifen und eine Spalte mit einer Formel hinzufügen, in der Operatoren ineinander verschachtelt sind if Logik wird implementiert, sehr ähnlich zu dem, was wir brauchen:
Auch hier kann man die Situation nicht zu Ende drücken, sondern anklicken OK und dann die Schwellwerte bereits in der Formel korrigieren – so geht's schneller:
Schlussfolgerungen
Sicherlich ein Werkzeug Spalte von Beispielen ist keine „Wunderpille“ und früher oder später wird es in den Daten ungewöhnliche Situationen oder besonders vernachlässigte Fälle einer „Kollektivfarm“ geben, bei denen Power Query versagt und nicht in der Lage sein wird, das auszuarbeiten, was wir wollen richtig für uns. Als Hilfsmittel ist es aber sehr gut geeignet. Außerdem können Sie durch das Studium der von ihm generierten Formeln Ihr Wissen über die Funktionen der M-Sprache erweitern, was sich in Zukunft immer als nützlich erweisen wird.
- Analysieren von Text mit regulären Ausdrücken (RegExp) in Power Query
- Fuzzy-Textsuche in Power Query
- Flash Fill in Microsoft Excel