Wenn man sich mit der Analyse
und/ oder Aufbereitung von Daten und einem anschließenden Reporting
beschäftigt, dann ist man in den meisten Fällen nicht der Erzeuger der Daten,
sondern bekommt diese zugeliefert bzw. zieht sie aus einem bestehenden System
(bspw. Hyperion Essbase, SAP BW etc.) ab. Dabei hängt der Aufwand, der mit der
Weiterverarbeitung dieser Daten in Verbindung steht, stark von deren Struktur
ab.
Der Wunschzustand ist eine Tabelle, wie sie in Abbildung 1 zu sehen ist. Die Daten liegen Spaltenweise (in
Datensatzform) vor, und haben Überschriften.
 |
Abbildung
1: Der Idealzustand für die
Weiterverarbeitung von Daten: Eine flache Tabelle
|
Ausgehend
von diesen Daten können weiterführende Analysen vorgenommen werden.
Beispielsweise wäre es hier möglich eine Pivottabelle auf diese Daten zu setzen
und über Slicing, Dicing, Drill-Through und Drill-Down auf die Suche nach
Abweichungen, zusammenhängen und dergleichen zu gehen.
Kreuztabellen mit einschichtige Spaltenüberschriften
Leider werden die Daten nicht
immer in diesem Idealzustand geliefert. Häufig liegt eine Matrix vor, wie sie
in Abbildung
2
zu sehen ist.
 |
Abbildung
2: Matrix mit einschichtiger
Spaltenbeschriftung
|
Hier liegt eine Kreuztabelle vor,
die das Jahr (rot hervorgehoben), in die horizontale aufspannt und als
Spaltenbeschriftung nutzt. Dies kann für Berichte sinnvoll sein, weil es die
Lesbarkeit verbessert, ist jedoch für weiterführende Analysen extrem
hinderlich.
Um Daten dieser Struktur in eine
Form zu bringen, wie sie in Abbildung 1
dargestellt ist, bietet Power Query
eine tolle Funktionalität, die sich in der deutschen Version im Reiter Transformieren hinter Spalten entpivotieren verbirgt. Wie dies inhaltlich funktioniert ist im
Video-Tutorial von Excel MVP Ken Puls bestens beschrieben (Link).
Kreuztabellen mit zweischichtige Spaltenüberschriften
Schwieriger wird es jedoch, wenn
man eine Matrize erhält, die nicht eine, sondern zwei Spaltenüberschriften hat.
Abbildung
3
zeigt ein Beispiel.
 |
Abbildung
3. Matrix mit zweischichtiger
Spaltenbeschriftung
|
Um eine solche Struktur
dahingehend zu transformieren, dass ein Aufbau wie in Abbildung
1
erreicht wird, bedarf es schon einiger Tricks. Auch dies ist mit Power Query
machbar und wird in einem - nach meinem
Geschmack – sehr guten Post erläutert (Link).
Hierbei reicht die reine Nutzung der GUI von Power Query leider nicht mehr aus,
sondern es wird vertieft mit M
gearbeitet.
So weit, so gut. Was ist jedoch
mit Matrizen, die mehr als zwei Spaltenüberschriften aufweisen?
Kreuztabellen mit mehrschichtigen Spaltenüberschriften
Nach meinem Kenntnisstand ist
Power Query nicht in der Lage Datenstrukturen, wie von mir gewünscht, zu
transformieren, sofern diese mehr als zwei Spaltenüberschriften aufweisen. Innerhalb
von Excel kenne ich keinen weiteren Lösungsansatz für diese Problematik, so
dass ich mir eine eigene Lösung gebaut habe: Mit VBA.
Ausgangspunkt ist eine
Datenstruktur, wie sie in Abbildung 4
dargestellt ist.
 |
Abbildung
4: Matrix mit mehrschichtiger
Spaltenbeschriftung
|
Das folgende Video zeigt die
Nutzung meines Programms.
Die Beispieldatei, inklusive VBA-Skript, könnt Ihr hier herunter laden: Download
Viel Spaß damit und bis zum nächsten Mal,
Lars
