Kreuztabelle pivot-fähig umgestalten

Eine Kreuztabelle so umstrukturieren, dass eine sinnvolle Pivot-Auswertung gelingt

Vielfach liegen auszuwertende Daten in der Form vor, dass wir User einen raschen Überblick haben. Typischerweise ist das eine XY-Matrix mit den entsprechenden Bezeichnern links und oberhalb der eigentlichen Daten. Als Beispiel soll ein Arbeitsblatt dienen, welches ich schon in sehr vielen Excel-Schulungen eingesetzt habe, die Bäckerei Kleinbrot:

Die originale Kreuztabelle

Die originale Kreuztabelle

Die Arbeitsmappe liegt hier zum Download bereit. Sie sehen, in Zeile 4 sind die Produktbezeichnungen und in Spalte A des Datenbereichs die Monate. Es sind zwar schon verschiedene Auswertungen gemacht worden, aber in dieser Form ist das nun wirklich keine Grundlage für eine PivotTable. Da müssen die Daten in einer anderen Ordnung vorliegen. Jeweils drei relevante Daten in einer Zeile, beispielsweise so:

Monat Produkt Umsatz
Januar Brot 3287,30
Januar Brötchen 2655,28
Januar Gebäck 1388,20
Dezember Sonstiges 804,75

Sie erkennen, dass die Summenberechnungen sowie die anderen Auswertungen nicht mit eingeschlossen sind; das kann eine PivotTabelle viel besser!  😉 Das Ziel ist es also, in einem neuen Arbeitsblatt eine Tabelle zu erstellen, welche die eben genannten Kriterien erfüllt.

Es gibt mehrere Wege zum Ziel, dennoch sind einige grundlegende Hinweise zu den gesamten Daten wichtig:

  • Die beiden Überschrift-Zeilen sind nicht über die Schaltfläche zentriert. Diese Form der Zentrierung macht mehr Ärger als gut ist. Wie ich das gemacht habe, können Sie beispielsweise hier im Blog nachlesen.
  • Die Leerzeile (Zeile 18) ist nur OK, weil die Daten darunter nicht zu den auszuwertenden Daten gehören.
  • Falls auch Sie die auszuwendenden Daten mehrfach verwenden wollen, dann lohnt es sich, dem Bereich A4:G16 einen Bereichsnamen zu geben. Da dieser möglichst aussagekräftig sein sollte, verwende ich den Namen Daten und verwende ihn auch in den Beispielen.
  • Egal, welche der hier vorgestellten Varianten Sie wählen: Die Zelle A4 sollte nicht leer bleiben. Dort gehört eine Überschrift hinein. Ich wähle in diesem Fall Monat.

Dieser Beitrag steht im Kapitel „Es führen viele Wege nach Rom“, darum werde ich Ihnen hier auch die wichtigsten Möglichkeiten vorstellen. Sie können dann anhand Ihrer Fähigkeiten und Gegebenheiten entscheiden. Und für alle vorgestellten Möglichkeiten gilt: Ausschließlich die Daten im Blatt Tabelle1 werden zur Auswertung verwendet, die umgestellten Daten befinden sich in je einem neu durch Sie erstellten Blatt.

▲ nach oben …

Umstellung per Formel

Ein einfaches Transponieren geht (natürlich) nicht, das wäre ja zu einfach. 😛 Im Prinzip sind es drei unterschiedliche Formeln, die zum Ziel führen. Zumindest in meinem Lösungsvorschlag, es gibt gewiss noch weitere Wege. Diese Formeln finde ich (noch) einigermaßen nachvollziehbar …

  • Legen Sie erst einmal ein neues Arbeitsblatt (Register) an, geben Sie ihm beispielsweise den Namen Formel.
  • Tragen Sie in A1:C1 diese Überschriften ein: Monat | Produkt | Umsatz
  • Schreiben Sie in A2 diese Formel: =INDEX(Daten; AUFRUNDEN((ZEILE()-1)/6; 0)+1; 1)
  • In B2 kommt diese Formel: =INDEX(Daten; 1; REST(ZEILE(A2)-2; 6)+2)
  • Und in C2 wird mit dieser Formel versehen: =INDEX(Daten; AUFRUNDEN((ZEILE()-1)/6; 0)+1; REST(ZEILE(A2)-2; 6)+2)
  • Achten Sie darauf, dass eine beliebige Zelle im Bereich A1:C2 markiert ist und StrgL oder StrgT oder gehen Sie über das Menü, um die Daten Als Tabelle zu formatieren.

Jetzt ist etwas Rechenarbeit angesagt. Es ist nicht viel und auch nicht schwierig, also werden Sie gewiss auch ohne Excels Hilfe auskommen.  😉 Es sind 12 Monate zu je 6 Produkten. Dazu kommt noch die Überschrift. Ziehen Sie die Tabelle bis zu der eben berechneten Zeile nach unten. Und Sie sehen, dass das Wunschergebnis erreicht ist. In der letzten Zeile muss übrigens Dezember | Sonstiges | 804,75 stehen.

▲ nach oben …

Umstellung per VBA (Makro)

Es gibt verschiedene Gründe, warum Sie statt der Formeln lieber mit einem Makro arbeiten. Vielleicht haben Sie (wie auch ich) eine Abneigung gegen Formeln, die länger sind als 50 Zeichen. Die Formel in Spalte C hat übrigens 67 Zeichen, ohne das führende Gleichheitszeichen. 😉 Oder aber Sie wollen dieses Procedere auf verschiedene Daten anwenden und nicht jedes Mal die Formeln neu eintragen. Oder Sie finden, dass ein VBA-Code einfach flexibler ist, weil Änderungen rascher und leichter durchzuführen sind. – Na dann wird Ihnen dieser Beispielcode gewiss helfen. In einem neuen Tabellenblatt, dessen Name beispielsweise VBA oder Makro ist, tragen Sie in das Modul DieseArbeitsmappe folgenden Code ein:

Ich habe in dem Code ganz bewusst verschiedene Stile verwendet. So haben Sie die Möglichkeit, einen für sich selber passenden Code-Stil herauszuarbeiten und dann insgesamt zu verwenden. – Als Namen der Liste/Intelligenten Tabelle habe ich tbl_Daten_2 genommen, damit Sie die Datenquellen bei einer Pivot-Auswertung klar definieren können (auch wenn es eigentlich egal ist, denn die Daten sind ja identisch).

▲ nach oben …

Ab Excel 2010: Power Query

Wenn Sie eine neueres Excel haben, dann können Sie auch Power Query einsetzen, sofern es sich um ein Windows-Excel handelt. Hier im Blog bekommen Sie mehr Informationen zu dem Add-In. Sie müssen in jedem Fall in den 2010er/2013er-Version das Add-In installiert haben, in der 2016er-Version ist die Funktionalität bereits integriert. Die Anweisungen hier beziehen sich auf Excel 2013, in der 2016er Version ist nach dem Aufruf über das Menü Daten vieles gleich. – Ach ja, die eine oder andere Anweisung wird hier wiederholt; ich kann nicht sicher sein, dass Sie den oberen Teil auch so durchgearbeitet haben oder vielleicht für diese Aufgabe wiederum die leere Mappe geladen haben.

Wählen Sie zuerst das Menü Power Query, um das dazu gehörige Menüband angezeigt zu bekommen. Falls Sie Excel 2016 verwenden, mehr dazu hier. Sie brauchen kein neues Arbeitsblatt anzulegen, das erledigt Power Query für Sie. Wechseln Sie erforderlichenfalls zu Tabelle1 und achten Sie darauf, dass eine beliebige Zelle der Daten markiert ist. Hier nun in Stichworten das weitere Vorgehen:

  • Markieren Sie A4:G16 und formatieren Sie den Bereich als (Intelligente) Tabelle (geht auch mit StrgT oder StrgL).
  • Ändern Sie in A4 die Überschrift auf Monat.
  • Achten Sie darauf, dass eine beliebige Zelle (und zwar nur 1) der auszuwertenden Daten (Liste) oder der gesamte auszuwertende Bereich markiert ist.
  • Gruppe Excel-Daten, Symbol Von Tabelle..
  • Im Abfrage-Editor die Markierung der ersten Spalte belassen, erforderlichenfalls nur diese Spalte durch Klick in die Überschrift markieren.
  • Menüpunkt Transformieren auswählen.
  • In der Gruppe Beliebige Spalte auf den DropDown-Pfeil DropDownPfeil beim Punkt Spalten entpivotieren klicken.
  • Andere Spalten entpivotieren auswählen.
  • Rechtsklick in die Überschrift Attribut und im Kontextmenü Umbenennen… dann Produkt schreiben (die Markierung wird automatisch ersetzt).
  • Klick in die Überschrift Wert, F2 und die Bezeichnung auf Umsatz ändern.
  • Menüpunkt Start, dann in der Gruppe Schließen auf Schließen und laden klicken oder via Menü | Datei Schließen und laden.

Automatisch wird ein neues Tabellenblatt erstellt und die Daten sind in exakt der gewünschten Form als Liste eingetragen worden. Zur besseren Identifikation benenne auch die Tabelle tbl_PowerQuery und das Arbeitsblatt beispielsweise Power Query. In dieser Muster-Mappe (mit dem Ergebnis des Power Query) habe ich die Tabelle bereits im Abfrage-Editor umbenannt.

Diese Methode hat mindestens einen großen Vorteil: Da keine einzige Zeile VBA-Code enthalten ist, kann sie auch bei hohen Sicherheitseinstellungen angewendet werden. Und ein einziger Klick auf die Aktualisieren-Schaltfläche wird stets den neuesten Stand der Dinge erzeugen und anzeigen.

Epilog

Wenn Sie anschließend eine PivotTable aus den Daten erstellen, dann werden Sie unter Umständen eine Ernüchterung erfahren. Wollen Sie beispielsweise die Zeiträume nach Quartalen berechnen, so gelingt Ihnen die Gruppierung nicht. Der Grund ist ganz simpel: In den Basisdaten sind die Monate als Text eingetragen. Und Pivot kann nicht erkennen, dass da eigentlich ein Datum dahinter steckt. Wollen Sie die Möglichkeit der Gruppierung nutzen, dann muss in Spalte A der Rohdaten ein Datum stehen. Beispielsweise der 1.1.2014. Die Formatierung, das Zahlenformat können Sie dann gerne auf MMMM einstellen. Das Aussehen ist dann wie vorher, Pivot „sieht“ dann aber das „echte“ Datum und kann eine Gruppierung erstellen.

▲ nach oben …

Dieser Beitrag wurde unter Datum und Zeit, Mit VBA/Makro, Musterlösungen, Ohne Makro/VBA, Pivot, Power Query, Tabelle und Zelle, Transponieren, Wege nach Rom abgelegt und mit , , , , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.