Power Query ist die bahnbrechende Excel-Funktion, die Sie nicht nutzen - hier sind 5 Möglichkeiten, sie einzusetzen
Wenn ich auf meine alten Excel-Tabellen zurückblicke, kann ich nicht glauben, dass ich ohne Power Query überlebt habe. Es verwandelt komplexe Automatisierungen in etwas überraschend benutzerfreundliches. Wenn Sie es bisher gemieden haben, weil es einschüchternd aussieht, ist dieser Leitfaden für Sie.
Ich werde Sie durch fünf Transformations-„Schritte“ mit demselben Datensatz führen - beginnend mit der Textextraktion und -bereinigung und dann weitergehend zur Zusammenfassung großer Datensätze ohne PivotTable.
Um beim Lesen dieses Leitfadens mitzugehen, laden Sie eine kostenlose Kopie der Excel-Arbeitsmappe herunter, die in den Beispielen verwendet wird. Nachdem Sie auf den Link geklickt haben, finden Sie die Schaltfläche zum Herunterladen in der oberen rechten Ecke Ihres Bildschirms.
Verfeinern und korrigieren Sie die Daten auf Ihrem aktuellen Arbeitsblatt
Unordentlichen Text umwandeln und Werte zusammenfassen
Power Query ist eines der mächtigsten Werkzeuge in Excel, um die schmutzigen Daten direkt vor Ihnen zu bereinigen. Anstatt lange, verschachtelte Formeln zu schreiben, die anfällig und schwer zu pflegen sind, können Sie den Power Query-Editor verwenden, um eine Reihe von Transformationsschritten aufzuzeichnen. Dies hält Ihre Bereinigung konsistent, wiederholbar und leicht prüfbar.
Angenommen, Sie haben diese formatierte Excel-Tabelle mit dem Namen T_Sales erhalten. Sie enthält eine unordentliche SKU-Spalte, die chirurgische Präzision erfordert, sowie eine lange Liste von Transaktionen, die Sie zusammenfassen möchten.
Laden Sie zunächst Ihre Tabelle in den Power Query-Editor. Wählen Sie eine beliebige Zelle in den Daten aus, und klicken Sie im Daten-Tab auf Aus Tabelle/Bereich.
Sie können jetzt Ihre Tabelle im neuen Fenster sehen.
Arbeitsablauf 1: Isolieren und Aufräumen bestimmter Datenpunkte
Daten extrahieren und bereinigen für bessere Berichterstattung
Ihr Ziel: Eine Rohzeichenkette wie TSHIRT_XL_RED_001 zu nehmen und sie in drei separate, saubere Spalten zu verwandeln: eine für den korrekt geschriebenen Artikelnamen ("T-shirt"), eine für die Größe ("XL") und eine für die Farbe ("Rot").
Das Extrahieren eines Artikelnamens oder einer Größe aus einer unordentlichen SKU erfordert normalerweise eine kopfschmerzverursachende Kombination aus LEFT, MID und FIND. In Power Query hingegen können Sie eine chirurgische Bereinigung in wenigen Sekunden durchführen.
Schritt 1: Teile deine Spalten und benenne sie um
Der erste Schritt besteht darin, die SKU-Spalte in Artikel, Größe, Farbe und ID aufzuteilen und ihnen klare Namen zu geben:
- Wählen Sie die SKU-Spalte aus.
- Klicken Sie auf der Registerkarte Start auf Spalte teilen > Nach Trennzeichen.
- Wählen Sie Benutzerdefiniert als Trennzeichenart, geben Sie einen Unterstrich (_) ein, wählen Sie Jedes Vorkommen des Trennzeichens aus und klicken Sie auf OK.
- Doppelklicken Sie auf die Überschrift jeder neuen Spalte und benennen Sie diese jeweils in Artikel, Größe, Farbe und ID um.
Während Sie diese Aktionen ausführen, schauen Sie auf den Bereich "Angewandte Schritte" auf der rechten Seite. Power Query zeichnet jeden Klick einzeln auf, sodass Sie, wenn Sie einen Fehler machen, einfach auf das X neben einem Schritt klicken können, um ihn rückgängig zu machen und es erneut zu versuchen.
Schritt 2: Entfernen Sie die unnötige ID-Spalte
Anstatt eine Standardlöschung durchzuführen, erstellen Sie eine „geschützte Liste“ von Spalten, die beibehalten werden sollen. Dies sagt Power Query, dass es in Zukunft nur nach diesen spezifischen Spalten suchen soll. Wenn später jemand eine Notizen-Spalte zu Ihren Quelldaten hinzufügt, wird Power Query das zusätzliche Rauschen ignorieren und Ihren Bericht schlank und schema-sicher halten.
- Halten Sie Strg gedrückt und klicken Sie auf alle Spaltenüberschriften, außer ID.
- Klicken Sie mit der rechten Maustaste auf einen der ausgewählten Kopfzeilen und klicken Sie auf Andere Spalten entfernen.
Schritt 3: Beheben Sie Ihre Formatierung
Jetzt, da die Struktur festgelegt ist, können Sie die Formatierung der Spalte Artikel korrigieren. Dies erfolgt in zwei Phasen: Die erste ist eine einfache Transformation, die blockgroße Buchstaben in Standardgroßschreibung umwandelt (zum Beispiel „TSHIRT“ zu „Tshirt“), und die zweite besteht darin, „Tshirt“ in „T-shirt“ zu ändern.
- Wählen Sie die Element-Spalte aus.
- Klicken Sie auf der Registerkarte Transformieren auf Format > Jedes Wort großschreiben.
- Klicken Sie mit der rechten Maustaste auf die Spaltenüberschrift Element und wählen Sie Werte ersetzen aus.
- Ersetze Tshirt durch T-shirt und klicke dann auf OK.
Schritt 4: Übersetze die Farbcode
Schließlich müssen Sie die dreibuchstabigen Farb-Codes in richtige Namen umwandeln. Sie könnten erneut das Werkzeug Werte ersetzen verwenden, aber dies wäre ein zeitaufwändiger Prozess, der mehrere Schritte erfordert. Stattdessen fügen Sie eine Bedingte Spalte hinzu:
- Klicken Sie im Tab Spalte hinzufügen auf Bedingte Spalte.
- Benennen Sie die neue Spalte Richtige Farbe.
- Fügen Sie Ihre erste Regel hinzu: Wenn Farbe gleich WHT ist, dann geben Sie Weiß aus.
- Klicken Sie auf Clause hinzufügen, wiederholen Sie diesen Vorgang für die anderen Farben, und klicken Sie auf OK.
- Klicken und ziehen Sie die neue Spalte nach links, damit sie die dritte Spalte ist.
- Klicken Sie mit der rechten Maustaste auf die Überschrift der ursprünglichen, unordentlichen Farbe-Spalte und wählen Sie Entfernen aus.
Im Gegensatz zum Standard-Excel-Raster führt das Entfernen einer Quellspalte in Power Query nicht zu Fehlern in späteren Schritten, solange diese Schritte sich nicht darauf beziehen. Dies liegt daran, dass Power Query Transformationen in einer definierten Reihenfolge verarbeitet. Da Sie bereits 'Andere Spalten entfernen' verwendet haben, ist ein einfaches Entfernen hier sicher.
Ihre Daten sind jetzt sauber, konsistent und standardisiert, und Sie sind bereit, mit Workflow 2 fortzufahren.
Arbeitsablauf 2: Zusammenfassung riesiger Datensätze ohne eine Pivot-Tabelle
Aggregation von Transaktionssummen für Berichte auf hoher Ebene
Ihr Ziel: Ihre Transaktionsliste zu aggregieren, sodass Sie anstelle jeder einzelnen Zeile einen Gesamtwert für "Verkaufte Einheiten" für jede Kategorie sehen.
Bevor Sie den letzten Schritt machen, müssen Sie Ihre Daten in zwei unabhängige Abfragen aufteilen. Dies bewahrt die bereits geleistete Bereinigung als Basisschicht und ermöglicht es Ihnen, die Zusammenfassung darauf aufzubauen.
- Klicken Sie im linken Abfragen-Bereich mit der rechten Maustaste auf Ihre bestehende Abfrage und wählen Sie Verweisen.
- Doppelklicken Sie auf diese neue Abfrage und benennen Sie sie in Summary_Report um.
- Mit dieser neu ausgewählten Abfrage sind Sie bereit, den letzten Schritt 5 auszuführen.
Schritt 5: Nach Kategorie gruppieren
Die Gruppieren nach-Funktion von Power Query ermöglicht es Ihnen, eine Aggregation durchzuführen, die Sie normalerweise für PivotTables reservieren würden. Während PivotTables großartig für interaktive Analysen sind, eignet sich Power Query besser für wiederholbare, aktualisierbare Berichts-Workflows und zur Vermeidung von duplizierten Datenstrukturen. Persönlich finde ich den Power Query-Editor einfacher zu verwenden als den Bereich PivotTable-Felder.
- Wählen Sie die Spalte Kategorie aus.
- Klicken Sie auf der Transformieren-Registerkarte (oder in einigen Versionen auf der Start-Registerkarte) auf Gruppieren nach.
- Stellen Sie im Dialogfeld sicher, dass Basis ausgewählt ist, und setzen Sie den Neuen Spaltennamen auf Gesamtverkaufte Einheiten.
- Stellen Sie die Operation auf Summe und die Spalte auf Verkaufte Einheiten ein.
- Klicken Sie auf OK.
Jetzt sind Ihre Daten übersichtlich in einer leicht lesbaren Tabelle zusammengefasst.
Klicken Sie schließlich auf die obere Hälfte der geteilten Schließen & Laden-Schaltfläche im Start-Tab, um Ihre neuen Tabellen auf getrennte Arbeitsblatt-Tabs zu laden. Wenn sich Ihre Quelldaten ändern, klicken Sie auf Daten > Alle aktualisieren , um beide Abfragen zu aktualisieren.
Die Verwendung von Power Query zur Automatisierung Ihres Workflows ist eine großartige Möglichkeit, Ihre Zeit in Excel zurückzugewinnen. Es geht darum, intelligenter zu arbeiten, indem Sie die Software die wiederholenden Aufgaben für Sie erledigen lassen. Sobald Sie diese Techniken beherrschen, werden Sie feststellen, dass die Verwendung von Python in Excel eine weitere ausgezeichnete Möglichkeit ist, Daten zu bereinigen und zu korrigieren, wenn Ihre Datensätze besonders komplex werden.