Durch spezielles transponieren mit Power Query für Pivot-Tabelle vorbereiten
Hinweis: Power Query ist erst ab der Version Excel 2010 einsetzbar. Mehr dazu können Sie hier nachlesen. Wenn Sie eine frühere Version verwenden oder Excel für den Mac, dann zeigen wir Ihnen in diesem Beitrag einen Weg auf, wie Sie die Daten von einer Kreuztabelle in eine pivot-gerechte Form bringen können.
Der Stand der Dinge
Mehrere Abteilungen eines Unternehmens liefern Verkaufsdaten in eine einzige Excel-Tabelle. Das führt dazu, dass ein Kunde am gleichen Tag oder an verschiedenen Tagen mehrfach in der Monatsübersicht auftaucht. Der Kunde 4711 ist einer der „Kandidaten”. Die Tabelle kann hier herunter geladen werden und stellt sich etwa so dar:
Ausschnitt aus der Roh-Daten Tabelle
Sie erkennen, dass in Spalte A die Kundennummern eingetragen sind, in Zeile 1 ab Spalte B die Arbeitstage, hier für den Januar 2014. Unterhalb des Datums sind die verschiedenen Umsätze eingetragen.
Die Forderung
So, wie die Tabelle derzeit vorliegt, lässt sie sich nicht wirklich leicht auswerten. Darum wird folgende Forderung aufgestellt: Statt in einer Kreuztabelle sollen alle Kunden mit ihren jeweiligen Umsätzen und dem Datum in einer neuen Tabelle in drei Spalten erfasst werden. Jede Kundennummer (ist hier identisch mit der Zeile, auch wenn der Kunde mehrfach in Spalte A erscheint) soll in eine Zeile, daneben das Datum des Kaufs und der Umsatz. Für den ersten Kunden aus der Liste (KdNr. 4582) sähe das dann so aus:
Die ersten Zeilen des Wunsch-Ergebnisses
Der Übersicht wegen habe ich hier auch die erste Zeile des 2. Kunden mit eingefügt.
Die Lösung
Zugegeben, es geht mit reinen Excel-Funktionen. Aber die Formel ist nicht wirklich sehr übersichtlich und transparent. Und wenn sich dann einmal etwas ändert, ist extrem viel Aufmerksamkeit erforderlich. Darum wählen wir hier den Weg über das Add-In Power Query bzw. die entsprechende Funktionalität ab Excel 2016.
Apropos Formel: In Tabelle Monat steht in Zeile 1 ab Spalte C eine Formel. Vielleicht können auch Sie davon profitieren, auf die Schnelle nur die typischen Arbeitstage einzutragen. In B1 das erste Datum des Zeitraums, in C1 die Formel und dann einfach nur nach rechts rüberziehen. Spalten mit Feiertagen allerdings müssen von Hand gelöscht werden, aber das ist ja nicht ganz so aufwendig.
Öffnen Sie erforderlichenfalls die Datei mit den Umsätzen, welche in einer Kreuztabelle formatiert sind. Achten Sie bitte darauf, dass eine Zelle im Datenbereich aktiv, also markiert ist. Klicken Sie nun auf den Menüpunkt Power Query. Bei dieser Gelegenheit ein Hinweis: Alle Weisungen beziehen sich auf Excel 2013, in der 2016er Version ist es prinzipiell ähnlich, aber nicht gleich; hier sind die Funktionalitäten im Menü Daten untergebracht. Wie gewohnt zeigt sich ein neues Menüband, wo Sie in der Gruppe Excel-Daten auf die Schaltfläche Von Tabelle Klicken. Die Daten sollen ja von einer Tabelle „importiert” werden.
Es öffnet sich das Dialogfenster Von Tabelle, Excel markiert den zusammenhängenden Bereich der Daten und trägt die Werte in das Eingabefeld ein. Hier sollten Sie kontrollieren, ob die Daten korrekt sind. Achten Sie auch darauf, dass das Häkchen bei den Überschriften gesetzt ist. Danach mit OK bestätigen.
Ein neues Fenster, der Abfrage-Editor tut sich auf. Das ist der zentrale Arbeitsbereich für das, was Sie vorhaben. Die Daten werden sehr ähnlich wie im „normalen” Excel dargestellt, allerdings ist das Verhalten vielfach ein anderes. Das betrifft in erster Linie die Überschriften. Und Formeln können durch „Otto Normalanwender” auch nicht verwendet werden.
Die erste Spalte (KdNr.) ist markiert. Klicken Sie nun im Menü auf Transformieren. In der neuen Ribbon-Leiste Klicken Sie nun in der Gruppe Beliebige Spalte auf den DropDown-Pfeil bei Spalte entpivotieren. Die ausgewählte Spalte soll ja prinzipiell bestehen bleiben, nur die entsprechenden Werte sollen transponiert und dem entsprechenden Kunden zugeordnet werden. Darum wählen Sie den Menüpunkt Andere Spalten entpivotieren. Schneller als Sie hinsehen können, wird genau das geschehen, die Daten werden umgruppiert und die Leerzellen aufgefüllt.
Eigentlich ist das Ziel erreicht. Aber aus prinzipiellen Erwägungen sollten Sie an dieser Stelle bereits eine kleine Anpassung vornehmen: Spalte 2 und Spalte 3 wurden durch Power Query mit Standard-Überschriften versehen. Ein Doppelklick auf Attribut und ändern Sie den Namen auf Datum. In der Spalte Wert Klicken Sie ein Mal in die Zelle mit der Überschrift und dann F2, um den Namen auf Umsatz anzupassen. Bestätigen Sie beispielsweise mit Eingabe. – Eine Kleinigkeit können Sie noch erledigen: Die Spalte Umsatz ist als Datentyp: Beliebig formatiert. Ein Klick auf dieses Feld im Menüband und dann die Auswahl Währung, das bringt Sicherheit.
Last but not least sollen die Daten vom Editor in eine neue Tabelle geschrieben werden. Dazu wechseln Sie in das Menü Start und Klicken Sie auf das erste Symbol ganz links: Schliessen & laden. Umgehend schließt sich das Editor-Fenster und es wird ein neues Arbeitsblatt erstellt und mit den Daten gefüllt. Es empfiehlt sich nun, die Tabelle mit einen sinnvollen Namen zu versehen. Wenn bei den Tabellentools das Register Entwurf aktiviert ist, dann in der Gruppe Eigenschaften den Tabellennamen beispielsweise auf tbl_Umsätze ändern. Und wenn Sie schon dabei sind, können Sie auch gleich den Blattnamen auf einen „sprechenden” Namen ändern. – Das war es dann auch schon. Auf dieser Basis lässt sich hervorragend eine Pivot-Auswertung erstellen.
Siehe auch: Kreuztabelle pivot-fähig umgestalten, Tabelle zu PivotTable , Power Query Quickies