Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung
Entpivotieren verständlich erklärt
Das Warum und das Wie
Beim Wort „entpivotieren” fällt Ihnen vielleicht auf, dass das Wort Pivot, welches ja auch stark an die „Pivot Tabelle” erinnert, enthalten ist. Und in der Tat ist es so, dass der Vorgang des entpivotierens recht oft genutzt wird, um aus einer Kreuztabelle eine „vernünftige” Vorlage für eine Pivot Tabelle zu erstellen. Eine der vielen Bedeutungen des Wortes Pivot ist „Gelenk”, welche wohl wegen der Möglichkeit des Kippens am ehesten den Kern der Sache trifft.
Als Arbeitsvorlage können Sie diese Arbeitsmappe von unserem Server herunterladen. Dort ist für jeden Tag des Jahres für fünf unterschiedliche Filialen einer Bäckerei der Umsatz eingetragen (wegen der Bäckerei auch die Sonntagsumsätze). Wenn sie aus solch einer Kreuztabelle (Blatt Ausgang) eine „vernünftige” Pivot Table Erstellen wollen, dann sollten, nein müssen die Daten in einer solchen Form vorliegen, wie im Tabellenblatt Ziel dargestellt. Sie erkennen, dass ausschließlich 3 Spalten existieren: Datum, Filiale und Betrag.
Kreuztabelle zu Datenliste
Wie üblich ist es wichtig, dass die zu importierenden Daten in Form einer „intelligenten”, also formatierten Tabelle vorliegen. Das ist hier der Fall, darum können Sie auch gleich loslegen. 😉 Sollte das einmal nicht der Fall sein, Klicken Sie irgendwo in die Daten und StrgL oder StrgT oder über das Menü Start, Gruppe Formatvorlagen und Als Tabelle formatieren. Klicken Sie dann irgendwo in die Daten der Tabelle und über das Menü Daten (Menü Power Query in 2010⁄2013) und wählen Sie die Möglichkeit Aus Tabelle/Bereich. Nach dem Import öffnet sich der Power Query-Editor und die Daten stellen sich nun so dar:
Der Power Query-Editor direkt nach dem Import der Daten
Prinzipiell ist alles sowie in den Rohdaten und, es gibt jedoch den ein oder anderen kleinen Unterschied. In Excel gibt es ja die Spaltenbezeichnungen A, B, C, …, hier im Editor gibt es aber nur die Überschriften oberhalb der Tabelle. Die Zeilennummern beginnen auch erst in der ersten Daten-Zeile. Und wenn sie sich in die Spalte Datum ansehen, dort ist automatisch die Uhrzeit 00:00 Uhr angefügt worden.
Im ersten Schritt werden sie in dieser Spalte aus der Kombination Datum/Uhrzeit ein einfaches Datum machen, wie es auch in den Quelldaten vorliegt. Klicken Sie dazu sicherheitshalber noch einmal in die Überschrift Datum, damit die komplette Spalte korrekt markiert ist.
Sie befinden sich auf der Registerkarte Home und dort ist im Menüband in der Gruppe Transformieren der Text (ohne Logo) Datentyp: Datum/Uhrzeit vermerkt. Ein Klick darauf und wählen Sie dann im DropDown den Eintrag Datum. Bestätigen Sie im Dialog, dass sie den Spaltentyp ändern wollen.
Stellen Sie nun folgende Überlegung an: Welche Daten sollen gekippt, transponiert werden? Oder anders gefragt: Welche Spalte oder Spalten und sollen so oft wiederholt/dupliziert werden, damit die zugehörigen Daten und Werte in jeweils eine Spalte geschrieben werden? Am Beispiel des 1. Januar stellt sich das dann später in Excel doch so dar:
A1 → 01.01.2016
B1 → Filiale 1
C1 → 1012,17
A2 → 01.01.2016
B2 → Filiale 2
C2 → 1016,67
…
A5 → 01.01.2016
B5 → Filiale 5
C5 → 1004,72
Möglichkeit 1: Sie markieren alle Spalten, die transponiert werden sollen. Dazu Klicken Sie in die Überschrift Filiale 1, Shift und anschließend ein Klick in die Überschrift Filiale 5. Nun sind alle Spalten, die entpivotiert werden sollen, markiert. Wechseln Sie zum Register Transformieren und erweitern Sie dort in der Gruppe Beliebige Spalte das Symbol Spalten entpivotieren und wählen im DropDown den obersten Punkt Spalten entpivotieren. Statt des Weges über das Menü können Sie auch in einer der markierten Überschriften einen Rechtsklick durchführen und dort im Kontextmenü die Auswahl Entpivotieren treffen.
Möglichkeit 2: Sie markieren ausschließlich die Spalte bzw die Spalten, die nicht gekippt sondern nur vervielfältigt werden sollen. Das ist in diesem Fall nur die Spalte Datum. Ich klicke dann per Rechtsklick in die Überschrift und wähle im Kontextmenü direkt den Punkt Andere spalten entpivotieren. Natürlich können Sie auch hier den Weg über das Register Transformieren gehen und die entsprechende Auswahl im Menüband treffen.
Das war’s auch schon. Im Editor erkennen Sie, dass die Daten in genau jener Form vorliegen, wie sie für eine Auswertung per Pivot Tabelle existieren sollten. Aus kosmetischen Gründen wäre es gewiss hilfreich, die Überschrift Attribut in Filiale zu ändern und Wert in Umsatz. Im Menü Home ein Klick auf und das Symbol Schließen & laden oder führen sie die Anweisung über in das Register Datei aus und die Abfrage wird in dieser Form in einem neu erstellten Tabellenblatt in eine intelligente Tabelle geschrieben.
Die Möglichkeit des Entpivotierens gehört zu den herausragenden Möglichkeiten des Power Query. Auch wenn dieser Vorgang per Formeln und natürlich auch per VBA durchgeführt werden kann, mit PQ geht es einfach, rasch und komfortabel.
Es gibt noch die Möglichkeit, entpivotierte Daten wieder in eine Kreuztabelle umzugestalten. Das gilt natürlich auch für Abfragen, wo die Daten bereits in dieser Form importiert worden sind. Der Vorgang nennt sich pivotieren, ist aber etwas (wenn nicht sogar deutlich) komplexer zu handhaben.
Es gibt in diesem Blog noch weitere Beiträge zum Thema entpivotieren. Schauen Sie gerne einmal hier nach:
- www.excel-ist-sexy.de/PQ-kreuztabelle-datum-zeit-numerische-werte-zu-Liste/
- www.excel-ist-sexy.de/mitarbeiterplanung-wer-ist-wann-verfuegbar/
- www.excel-ist-sexy.de/PQ-zahl-aus-text-extrahieren-separieren/ (anspruchsvoller)
- www.excel-ist-sexy.de/PQ-auswertung-pro-monat-je-mitarbeiter/
- www.excel-ist-sexy.de/vorbereitung-fuer-pivot-unvorteilhafte-quelldaten/
- … und verschiedene weitere Beiträge. Suchen Sie einfach im Suchfeld oben rechts nach dem Stichwort entpivotieren , eventuell auch nach entpivotieren Kreuztabelle und Sie werden sicher fündig. 😎
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,00 € freuen …