Kreuztabellen entpivotieren 8

Artikelbild-215
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 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

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

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

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

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:

YouTube

Mit dem Laden des Videos akzeptieren Sie die Datenschutzerklärung von YouTube.
Mehr erfahren

Video laden

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Martin Weiß Antworten abbrechen

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

8 Gedanken zu “Kreuztabellen entpivotieren

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Werner Stattelmann

    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

    • Avatar-Foto
      Thomas Panzer

      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

  • Avatar-Foto
    Matthias

    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