Pivot-Tabellen sind eine der großartigsten Funktionen überhaupt in Excel. Mit ihrer Hilfe lassen sich umfangreiche Datentabellen mit nur wenigen Klicks aus allen Richtungen durchleuchten.
Was aber tun, wenn das Datenmaterial in einer für Pivot-Tabellen ungeeigneten Form vorliegt? Ein typisches Beispiel dafür sind kreuztabellenartige Monatsauswertungen, die schon für jeden Monat eine eigene Spalte enthalten.
Wie man solche Kreuztabellen entpivotiert, um sie für den Einsatz von echten Pivot-Tabellen tauglich zu machen, das zeige ich im heutigen Artikel.
Und so geht’s:
Die Ausgangslage
Klassisches Szenario: Irgendjemand hat sich einmal die Mühe gemacht und für eine Produktpalette eine Monatsauswertung erstellt. Die Produkte in den Zeilen, die Monate in den Spalten:
Die ursprüngliche Kreuztabelle
Dieses Datenmaterial soll nun aber auch nach bestimmten Kriterien ausgewertet oder gefiltert werden – also eigentlich ein Paradefall für eine Pivot-Tabelle. Dazu muss diese Kreuztabelle aber erst in eine flache Datentabelle zurückverwandelt werden.
(Falls du in das Thema Pivot-Tabellen mal richtig einsteigen möchtest, empfehle ich dir mein Buch: Excel Pivot-Tabellen für Dummies)
Das Mittel der Wahl ist der aus früheren Excel-Versionen bekannte PivotTable-Assistent.
Ja, wo isser denn?
In den aktuellen Excel-Versionen ist der PivotTable-Assistent ziemlich gut versteckt. Um ihn nutzen zu können, gibt es zwei Möglichkeiten:
Die Symbolleiste für den Schnellzugriff anpassen. Über die Option „Weitere Befehle…“ lassen sich auch nicht im Menüband aufgeführte Befehle anzeigen. In der Liste befindet sich auch der Eintrag „PivotTable- und PivotChart-Assistent“, der dann der Symbolleiste hinzugefügt und von dort mit einem Mausklick gestartet werden kann.
Symbolleiste für Schnellzugriff anpassen
Die deutlich schnellere Methode geht jedoch über eine Tastenkombination: Im Tabellenblatt zuerst die Tastenkombination Alt+N drücken und dann noch die Taste „P“. Damit wird der Assistent direkt gestartet.
Im ersten Schritt wählt man die Option „Mehrere Konsolidierungsbereiche“ und klickt dann auf „Weiter“:
PivotTable-Assistent Schritt 1
Im Schritt 2a wird die Option „Einfache Seitenerstellung“ bestätigt. Mit „Weiter“ gelangt man zu Schritt 3, wo man jetzt im oberen Eingabefeld den Bereich mit der vorliegenden Kreuztabelle markiert und mit „Hinzufügen“ der Liste der Bereiche hinzufügt. Wichtig: Die in meiner Beispieltabelle enthaltenen Zeilen- und Spaltensummen dürfen NICHT mit markiert werden.
Den Datenbereich markieren
Ein Klick auf die Schaltfläche „Fertigstellen“ erzeugt in einem neuen Arbeitsblatt eine Pivot-Tabelle, die große Ähnlichkeit mit der Ausgangstabelle hat.
Temporär benötigte Pivot-Tabelle
Diese Pivot-Tabelle ist aber nur eine temporäre Krücke, die wir für den nächsten Schritt benötigen, ganz am Ende aber auch wieder gelöscht werden kann.
Die Entpivotierung
Kommen wir also zum großen Finale, der Entpivotierung dieser Pivot-Tabelle. Dazu machen wir uns einfach die Drilldown-Funktion zunutze, die es standardmäßig in Pivot-Tabellen gibt.
Die entscheiden Zelle ist die Gesamtsumme in der rechten unteren Ecke der Pivot-Tabelle, dort wo sich die Spalten und die Zeilenergebnisse kreuzen.
Drilldown über das Gesamtergebnis
Ein Doppelklick auf diese Zelle führt den Drilldown aus und erstellt in einem neuen Arbeitsblatt eine flache, formatierte Tabelle mit allen Einzelwerten.
Die entpivotierte formatierte Datentabelle
Wir müssen eigentlich nur noch vernünftige Spaltenüberschriften vergeben und die Spalte mit dem Titel „Seite1“ kann komplett gelöscht werden. Dann können wir auf Basis dieser neuen Tabelle nach Herzenslust unsere neue Pivot-Tabelle erstellen.
Das Endergebnis mit Pivot-Tabelle
Einen kleinen Wermutstropfen gibt es:
Falls die ursprüngliche Kreuztabelle komplexer ist und mehrere Datenfelder enthält (z.B. zu den Produkten auch noch eine Spalte mit Vertriebsregionen), lässt sich mit dem oben gezeigten Weg die Tabelle leider nicht mehr entpivotieren.
Dieses Problem kann aber mit Power Query bzw. „Daten abrufen und transformieren“ gelöst werden. Wie das geht, zeige ich dir einmal in einem späteren Artikel – oder du liest mein Buch „Pivot-Tabellen für Dummies“ 🙂
Falls du immer noch nicht von Pivot-Tabellen überzeugt sein solltest, dann hätte ich noch etwas:
Mit dem Laden des Videos akzeptieren Sie die Datenschutzerklärung von YouTube.
Mehr erfahren
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
DAS ist mal ein richtiger Supertipp, ich bin BEGEISTERT 🙂
Ich habe eine Kreuztabelle mit Kostenarten in Zeilen und Kostenstellen in Spalten und für den Upload ins SAP brauche ich das als Liste: KST – KOART – Wert.
In einem Officeforum hatte ich mir Hilfe dazu geholt, habe einen Tip mit einer Formel bekommen die ich jetzt schon im 2. Jahr anwende, aber nicht wirklich verstanden habe was sie genau macht (sie funktioniert, das reicht und ich kann sie anpassen wenn sich Zeilen und Spaltenanzahl ändert.
Aber DAS hier, das kann ich alleine und verstehe ich auch 🙂
Vielen Dank dafür und Gruss
Annette
Hallo Annette,
vielen Dank für das schöne Feedback! Wie man wieder einmal sieht, gibt es in Excel immer verschiedene Wege zur Lösung 🙂
Schöne Grüße,
Martin
Richtig guten Tipp.
Ich wäre gar nicht auf die Idee gekommen, aus der Pivot eine Pivot zu machen, um sie dann zu entpivotisieren. Und das mit dem Doppelklick aufs Ergebnis war mir absolut neu. Und deshalb bestelle ich jetzt dein Buch.
Danke für all die Tipps!
-Tim
Hallo Tim,
sehr gern geschehen. In diesem Fall muss man tatsächlich etwas um die Ecke denken.
Und dann wünsche ich jetzt schon viel Spaß mit dem Buch!
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für deine vielen tollen Beiträge. Ob Index & Vegleich oder Dyn Datenbereich ausgeben. Die Artikel haben mir immer weiter geholfen.
Mit dem Beitrag „ent-pivotisieren“ dachte ich wäre der Lösung meines vermeintlich „einfachen“ Problems auf die Spur gekommen.
Leider war das nicht die Lösung und ich stehe hier völlig auf dem Schlauch und weis nicht weiter.
Mein „Problem“
Ich möchte aus 2 Listen eine dynamische fortlaufende Liste erstellen.
In Liste 1 stehen Namen „Max“, „Moritz“, in Liste 2 stehen Kostenstellen „10“; „20“; „30“
In der 3 Liste soll nun für jede Kombination eine Zeile erstellt werden.
So soll in der Neuen Liste stehen:
A2=Max,B2=10
A3=Max, B3=20
A4=Max, B4=30
A5=Moritz, B5=10
…
Ist das mit einer Formel lösbar und wenn, mit welcher Formel kann ich das lösen?
Vielen Dank schon mal im Voraus.
Viele Grüße
Werner
Hey Werner
anbei das Excel mit drei Hilfspalten A1, B1, C1, D1 und dann Ergebnisspalten E1, F2
A1 –> =+Tabelle1!A1
B1 –> =+Tabelle2!A1
C1 –> =ANZAHL(Tabelle2!A1:A57)
C 2 –> =WENN(C1=1;$C$1;C1-1)
D1 –> 1
D 2 –> =WENN(C2 =INDIREKT(ADRESSE((D1);(1)))
F1 –> =INDIREKT(ADRESSE((C1);(2)))
entsprechend die Zeile runterkopieren, fertig , bei mir funktioniert es prima !
Gruß Thomas
Vielen, vielen Dank für diesen Tipp. Ich habe schon einige Tage rumprobiert bis ich auf diesen Tipp gestoßen bin,
um einfach Kreuztabellen aufzulösen und auswertbar zu machen!
Viele Grüße,
Matthias
Hallo Matthias,
gern geschehen!
Schöne Grüße,
Martin