Kreuztabellen ent-pivotisieren 4

Mit einem cleveren Trick lässt sich eine Kreuztabelle in eine flache Datentabelle umwandeln, um sie dadurch Pivot-tauglich zu machen.
 

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 ent-pivotisiert, 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

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

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

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

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

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 Ent-Pivotisierung

Kommen wir also zum großen Finale, der Ent-Pivotisierung 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

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 entpivotisierte formatierte Datentabelle

Die entpivotisierte 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

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 ent-pivotisieren.

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:

 

Bitte weitersagen, wenn dir der Artikel gefallen hat!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

4 Gedanken zu “Kreuztabellen ent-pivotisieren

  • Annette

    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

    • Martin Weiß Autor des Beitrags

      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

  • Tim

    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

    • Martin Weiß Autor des Beitrags

      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