Eine Kreuztabelle in eine für PivotTable auswertbare Liste umwandeln
Hinweis:
Die Arbeitsschritte dieses Beitrages sind in einem kleinen Video dokumentiert. Das unterstützende Begleit-Video finden Sie auf YouTube an dieser Stelle.
Aus den verschiedensten Gründen liegen Daten als Kreuztabelle vor. Für eine schnelle, statische Übersicht ist dieses Format auch gut geeignet. Verwenden Sie diese Mappe, um sich ein Bild zu machen und auch damit zu üben:
Die originale Kreuztabelle
Auf der Basis ist keine „vernünftiege” Pivot-Auswertung machbar. Das Ziel ist, die Daten so in einer Tabelle/Liste als Daten-Basis zur Verfügung zu stellen:
Der korrekte Daten-Aufbau
Also den Umsatz für jeden Monat und dort für jedes Produkt einzeln. Sprich 6 Zeilen für jeden Monat. Es gibt verschiedenen Wege zum Ziel, einige sind hier beschrieben (übrigens auch das hier gleich beschriebene Vorgehen mit etwas anderen Worten). Power Query ist aus meiner Sicht die eleganteste Methode, zum Ziel zu gelangen. Hier wird dieser Weg unter Verwendung des Excel 2016 beschrieben.
Sie haben die oben beschriebene Arbeitsmappe geöffnet. Idealerweise soll ja jede Spalte einer Tabelle eine „sprechende” Überschrift haben, darum schreiben Sie als erstes in F4 die Überschrift Monat. Die Formatierung spielt dabei keine Rolle. Damit sind erst einmal die grundlegenden Voraussetzungen für das Vorhaben geschaffen.
Wichtig ist nun, dass eine der Zellen im Datenbereich markiert ist. Wählen Sie das Menü Daten und im Menüband, Gruppe Abrufen und transformieren den Punkt Aus Tabelle. Wahrscheinlich wird der Bereich A4:I16 oder I17 vorgeschlagen. Beides wäre korrekt. In jedem Fall sollte aber das Kontrollkästchen Tabelle hat Überschriften mit einem Häkchen versehen sein. Nach einem Klick auf die Schaltfläche OK werden die Daten in den Abfrage-Editor eingeladen. Das Ganze stellt sich nun so dar:
Der Editor direkt nach dem Import
Ich habe für den Import ganz bewusst die Zeile 17 mit eingelesen, um Ihnen eine Methode aufzuzeigen, diese dann auch gleich elegant zu entfernen. Und das werden sie auch als erstes bewerkstelligen. Im Register Start Klicken Sie zu Beginn im Menüband auf das Symbol Zeilen verringern. Dann Zeilen entfernen und im Untermenü Untere Zeilen entfernen. Da sie ja nur eine Zeile am Ende der Liste entfernen wollen geben Sie bei Anzahl von Zeilen eine 1 ein. OK und die Zeile 13 ist aus der Liste verschwunden.
Auch die beiden letzten Spalten (Gesamt und Durchschnitt) gehören nicht in diese Tabelle. Darum markieren Sie beide Spalten durch einen Klick in die Überschriften dabei kann Ihnen die Taste Shift oder Strg hilfreich sein. Und im Kontextmenü wählen Sie dann den zweiten Eintrag Spalten entfernen. – Jetzt ist die Liste genau so, wie sie sein soll. – Wenn Sie mehr in Sachen Power Query geübt sind werden Sie zuvor vielleicht nur die relevanten Zeilen für den Import markieren.
Rechtsklick in die Überschrift der Spalte Monat und ziemlich weit unten finden Sie die Auswahl Andere Spalten entpivottieren. Ruckzuck wird die Tabelle umgebaut und liegt nun im exakt gewünschten Format vor. Die Überschrift der 2. und 3. Spalte werden sie mit passender dann Texten versehen. Dazu ein Klick in die Überschrift Attribut, F2 und sie schreiben Produkt. In der nächsten Spalte können Sie in der Überschrift einen Doppelklick durchführen, damit das Wort Wert komplett markiert ist und sie verwenden das Wort Umsatz als künftige Überschrift.
Fertig. Das warst. Schließen & laden im Menüband (ganz links) anklicken und sie haben eine neue Tabelle, die sich ideal für die Auswertung einer Pivottabelle eignet. Und so ganz nebenbei wurden auch die Daten der ursprünglichen Auflistung in eine Intelligente Tabelle umgewandelt.
Hinweis: Deutlich ausführlicher und weitere Möglichkeiten der Konvertierung werden in diesem Beitrag beschrieben.