Video ansehen - Der schnellste Weg zum Entpivotieren von Daten in Excel
Pivot-Tabellen eignen sich hervorragend, wenn Sie eine riesige Datenmenge in Sekundenschnelle analysieren möchten. Es ermöglicht Ihnen auch, schnell verschiedene Ansichten von Daten durch einfaches Ziehen und Ablegen zu erstellen.
Und um eine Pivot-Tabelle zu erstellen, müssen Sie die Daten in einem bestimmten Pivot-Tabellen-fähigen Format haben.
In vielen Fällen erhalten Sie die Daten wahrscheinlich in Formaten, die nicht für Pivot-Tabellen geeignet sind.
Dies ist oft der Fall, wenn jemand manuell Daten sammelt und ein Format erstellt, das für Menschen besser lesbar ist (keine Pivot-Tabellen).
Etwas wie unten gezeigt:
Das obige Datenformat ist etwas, das Sie als Ausgabe einer Pivot-Tabellenanalyse erwarten.
Nun, was ist, wenn Sie dieselben Daten analysieren und sehen möchten, wie hoch der Gesamtumsatz nach jeder Region oder nach jedem Monat war.
Obwohl dies mit Pivot-Tabellen leicht möglich ist, können Sie die oben genannten Daten leider nicht in eine Pivot-Tabelle einspeisen.
Sie müssen also Daten entpivotieren und sie Pivot-Table-freundlich machen.
Obwohl es einige Möglichkeiten gibt, dies mithilfe von Excel-Formeln oder VBA zu tun, ist Power Query (Abrufen und Transformieren in Excel 2016) das beste Tool zum Entpivotieren von Daten.
Entpivotieren von Daten mithilfe von Power Query
Hier sind die Schritte zum Entpivotieren von Daten mithilfe von Power Query:
(Wenn sich Ihre Daten bereits in einer Excel-Tabelle befinden, beginnen Sie ab Schritt 6.)
- Wählen Sie eine beliebige Zelle im Dataset aus.
- Gehen Sie zur Registerkarte Einfügen.
- Klicken Sie auf das Tabellensymbol.
- Stellen Sie im Dialogfeld „Tabelle erstellen“ sicher, dass der Bereich korrekt ist. Sie können den Bereich bei Bedarf ändern.
- OK klicken. Dadurch werden Ihre Tabellendaten in eine Excel-Tabelle umgewandelt.
- Klicken Sie bei einer in der Excel-Tabelle ausgewählten Zelle auf die Registerkarte Daten.
- Klicken Sie in der Gruppe Daten abrufen und transformieren auf das Symbol „Aus Tabelle/Bereich“.
- Klicken Sie im sich öffnenden Dialogfeld Tabelle erstellen (falls geöffnet) auf OK. Dadurch wird der Abfrage-Editor mit den Excel-Tabellendaten geöffnet.
- Klicken Sie im Abfrageeditor mit der rechten Maustaste auf die Spalte Region.
- Klicken Sie auf die Option "Andere Spalten aufheben". Dadurch werden Ihre Daten sofort entpivot.
- Ändern Sie den Namen der Spalte „Attribut“ in einen aussagekräftigeren Namen, z. B. „Monate“.
- Sobald Sie über die nicht pivotierten Daten verfügen, sollten Sie sicherstellen, dass alle Datentypen korrekt sind. Klicken Sie in diesem Beispiel auf eine Zelle für jede Spalte und sehen Sie sich den Datentyp auf der Registerkarte Transform an. Bei Bedarf können Sie auch den Datentyp ändern.
- (Optional) Ändern Sie den Namen Ihrer Abfrage in "Verkauf".
- Wechseln Sie zur Registerkarte Start (im Abfrageeditor).
- Klicken Sie auf Schließen und laden.
Die obigen Schritte würden Ihr Dataset mithilfe von Power Query aufheben und als Tabelle in einem neuen Arbeitsblatt wieder in Excel einfügen.
Jetzt können Sie diese Daten verwenden, um mithilfe einer Pivot-Tabelle verschiedene Ansichten zu erstellen. Sie können beispielsweise den Gesamtverkaufswert nach Monat oder nach Region überprüfen.
Aktualisieren der Abfrage, wenn neue Daten hinzugefügt werden
Dies alles funktioniert gut.
Aber was passiert, wenn neue Daten zu unserem ursprünglichen Datensatz hinzugefügt werden?
Nehmen wir an, Sie erhalten Daten für Juli, die das gleiche Format haben wie das, mit dem wir begonnen haben.
Muss ich alle Schritte noch einmal wiederholen, um diese Daten in mein unpivoziertes Dataset aufzunehmen?
Die Antwort ist nein.
Und das ist das Tolle an Power Query. Sie können weiterhin neue Daten hinzufügen (oder vorhandene Daten ändern), und Power Query würde sie sofort aktualisieren, sobald Sie sie aktualisieren.
Lassen Sie mich Ihnen zeigen, wie.
Angenommen, unten ist der neue Datensatz, den ich bekomme (der zusätzliche Daten für Juli enthält):
Hier sind die Schritte, um die bereits erstellte Abfrage zu aktualisieren und diese Daten aufzuheben:
- Fügen Sie diese neuen Daten zu Ihren ursprünglichen Daten hinzu, die Sie zum Erstellen der Abfrage verwendet haben.
- Da Sie der angrenzenden Spalte einer Excel-Tabelle Daten hinzufügen, wird die Excel-Tabelle erweitert, um diese Daten aufzunehmen. Wenn dies nicht zufällig der Fall ist, tun Sie es manuell, indem Sie das kleine umgekehrte „L“-Symbol unten rechts in der Excel-Tabelle ziehen.
- Gehen Sie zur Registerkarte Daten und klicken Sie auf Abfragen & Verbindungen. Dadurch wird ein Bereich mit allen vorhandenen Abfragen angezeigt.
- Klicken Sie mit der rechten Maustaste auf die Verkaufsabfrage im Bereich Abfragen.
- Klicken Sie auf Aktualisieren.
Das ist es! Ihre neuen Daten werden sofort entpivot und zu den bestehenden Daten hinzugefügt.
Sie werden feststellen, dass die Anzahl der in der Abfrage angezeigten Zeilen aktualisiert wird, um Ihnen die neuen Zahlen anzuzeigen. In diesem Beispiel waren es 24 vor der Aktualisierung und wurden 28 nach der Aktualisierung.
Dies bedeutet auch, dass, wenn Sie Pivot-Tabellen mit den Daten erstellt haben, die Sie aus Power Query erhalten haben, diese Pivot-Tabellen ebenfalls aktualisiert werden, um Ihnen die aktualisierten Ergebnisse anzuzeigen.
