Inhalte
Formulierung des Problems
Als Eingabedaten haben wir eine Excel-Datei, in der eines der Blätter mehrere Tabellen mit Verkaufsdaten in folgender Form enthält:
Beachten Sie, dass:
- Tabellen unterschiedlicher Größe und mit unterschiedlichen Produktgruppen und Regionen in Zeilen und Spalten ohne jegliche Sortierung.
- Zwischen Tabellen können Leerzeilen eingefügt werden.
- Die Anzahl der Tische kann beliebig sein.
Zwei wichtige Annahmen. Es wird angenommen dass:
- Über jeder Tabelle steht in der ersten Spalte der Name des Managers, dessen Umsatz die Tabelle darstellt (Ivanov, Petrov, Sidorov usw.)
- Die Waren- und Regionsnamen sind in allen Tabellen gleich geschrieben – und zwar fallgenau.
Das ultimative Ziel ist es, Daten aus allen Tabellen in einer flachen normalisierten Tabelle zu sammeln, die für die spätere Analyse und das Erstellen einer Zusammenfassung geeignet ist, dh in dieser:
Schritt 1. Verbinden Sie sich mit der Datei
Erstellen wir eine neue leere Excel-Datei und wählen Sie sie auf der Registerkarte aus Datum Befehl Daten abrufen – Aus Datei – Aus Buch (Daten — Aus Datei — Aus Arbeitsmappe). Geben Sie den Speicherort der Quelldatei mit den Verkaufsdaten an und wählen Sie dann im Navigationsfenster das gewünschte Blatt aus und klicken Sie auf die Schaltfläche Daten konvertieren (Daten transformieren):
Als Ergebnis sollten alle Daten daraus in den Power Query-Editor geladen werden:
Schritt 2. Bereinigen Sie den Papierkorb
Automatisch generierte Schritte löschen modifizierter Typ (Geänderter Typ) и Erhöhte Kopfzeilen (Hochgestufte Header) und entfernen Sie leere Zeilen und Zeilen mit Summen mithilfe eines Filters null и INSGESAMT durch die erste Spalte. Als Ergebnis erhalten wir folgendes Bild:
Schritt 3. Manager hinzufügen
Um später zu verstehen, wo wessen Umsatz ist, muss unserer Tabelle eine Spalte hinzugefügt werden, in der in jeder Zeile ein entsprechender Nachname steht. Dafür:
1. Lassen Sie uns mit dem Befehl eine Hilfsspalte mit Zeilennummern hinzufügen Spalte hinzufügen – Indexspalte – Von 0 (Spalte hinzufügen – Indexspalte – Von 0).
2. Fügen Sie mit dem Befehl eine Spalte mit einer Formel hinzu Hinzufügen einer Spalte – Benutzerdefinierte Spalte (Spalte hinzufügen – Benutzerdefinierte Spalte) und führe dort folgende Konstruktion ein:
Die Logik dieser Formel ist einfach – wenn der Wert der nächsten Zelle in der ersten Spalte „Produkt“ ist, dann bedeutet das, dass wir auf den Anfang einer neuen Tabelle gestoßen sind, also zeigen wir den Wert der vorherigen Zelle mit an Name des Managers. Andernfalls zeigen wir nichts an, dh null.
Um die übergeordnete Zelle mit dem Nachnamen zu erhalten, beziehen wir uns zunächst auf die Tabelle aus dem vorherigen Schritt #"Index hinzugefügt", und geben Sie dann den Namen der benötigten Spalte an [Spalte1] in eckigen Klammern und die Zellennummer in dieser Spalte in geschweiften Klammern. Die Zellennummer ist um eins kleiner als die aktuelle, die wir aus der Spalte entnehmen Index, Bzw.
3. Es bleibt, die leeren Zellen mit auszufüllen null Namen aus höheren Zellen mit dem Befehl Transformieren – Füllen – Runter (Transformieren – Füllen – Unten) und löschen Sie die nicht mehr benötigte Spalte mit Indizes und Zeilen mit Nachnamen in der ersten Spalte. Als Ergebnis erhalten wir:
Schritt 4. Gruppierung in separate Tabellen durch Manager
Der nächste Schritt besteht darin, die Zeilen für jeden Manager in separaten Tabellen zu gruppieren. Verwenden Sie dazu auf der Registerkarte Umwandlung den Befehl Gruppieren nach (Umwandeln – Gruppieren nach) und wählen Sie im sich öffnenden Fenster die Spalte Manager und die Operation Alle Zeilen (Alle Zeilen), um einfach Daten zu sammeln, ohne eine Aggregationsfunktion anzuwenden sie (Summe, Durchschnitt usw.). P.):
Als Ergebnis erhalten wir separate Tabellen für jeden Manager:
Schritt 5: Transformieren Sie verschachtelte Tabellen
Jetzt geben wir die Tabellen an, die in jeder Zelle der resultierenden Spalte liegen Alle Daten in anständiger Form.
Löschen Sie zunächst in jeder Tabelle eine nicht mehr benötigte Spalte Geschäftsführer. Wir verwenden wieder Benutzerdefinierte Spalte Tab Transformation (Umwandeln – benutzerdefinierte Spalte) und die folgende Formel:
Dann erhöhen wir mit einer weiteren berechneten Spalte die erste Zeile in jeder Tabelle auf die Überschriften:
Und schließlich führen wir die Haupttransformation durch – das Entfalten jeder Tabelle mit der M-Funktion Table.UnpivotOtherColumns:
Die Namen der Regionen aus der Kopfzeile kommen in eine neue Spalte und wir erhalten eine schmalere, aber gleichzeitig längere normalisierte Tabelle. Leere Zellen mit null werden ignoriert.
Um unnötige Zwischensäulen loszuwerden, haben wir:
Schritt 6 Erweitern Sie verschachtelte Tabellen
Es bleibt, alle normalisierten verschachtelten Tabellen mithilfe der Schaltfläche mit den Doppelpfeilen in der Spaltenüberschrift zu einer einzigen Liste zu erweitern:
… und wir bekommen endlich, was wir wollten:
Sie können die resultierende Tabelle mit dem Befehl zurück nach Excel exportieren Home — Schließen und laden — Schließen und laden in… (Home — Schließen&Laden — Schließen&Laden nach…).
- Erstellen Sie Tabellen mit unterschiedlichen Überschriften aus mehreren Büchern
- Sammeln von Daten aus allen Dateien in einem bestimmten Ordner
- Sammeln von Daten aus allen Blättern des Buches in einer Tabelle