Excel-TippAus einer Kreuztabelle eine Liste erstellen – entpivotieren
Wie sind Kreuztabellen aufgebaut?
In Excel haben Sie die Möglichkeit, dass Sie Listen mithilfe von Pivot-Tabellen in eine Kreuztabelle umwandeln. Sie können auf diese Weise (umfangreiche) Daten neu anordnen und so übersichtliche Aufstellungen erstellen und Zusammenhänge sichtbar machen.
In einer Kreuztabelle stehen die Daten (zum Beispiel Umsatzzahlen) in Beziehung zu den Einträgen in den Zeilen (zum Beispiel Vertriebsmitarbeiter) und in den Spalten (zum Beispiel Monate). So erkennen Sie, welcher Vertriebsmitarbeiter in welchem Monat welchen Umsatz erzielt hat.
Aus der Kreuztabelle eine Liste erstellen
Wie aber gehen Sie vor, wenn Sie den umgekehrten Weg gehen wollen? Wenn Sie eine Kreuztabelle in eine Liste umwandeln möchten, damit Sie die Filterfunktionen in Excel ausreizen können? Oder um die Daten für eine andere Pivot-Tabelle als Datenquelle zur Verfügung zu stellen?
Dafür brauchen Sie weder komplizierte Formeln und Funktionen noch ein manuelles Kopieren und Verschieben von Datenblöcken. Dank Power-Query gibt es hierfür ein Standardfeature, mit dem Sie diese Aufgabenstellungen in wenigen Sekunden ohne großen Aufwand lösen können.
Wie funktioniert das Entpivotieren?
Schauen wir uns die Funktionsweise an einem Beispiel an. In der folgenden Abbildung sehen Sie eine Kreuztabelle, in der die Umsätze der Vertriebsmitarbeiter und Zweigstelle dargestellt sind. Die Monatsumsätze werden hierbei spaltenweise dargestellt (horizontal).
Diese Aufstellung soll nun in eine Listendarstellung umgewandelt werden, sodass alle Umsätze in einer einzelnen Spalte aufgelistet sind. Der jeweilige Monatsname wird hierbei ebenfalls in einer Spalte dargestellt.
Kreuztabelle in intelligente Tabelle umwandeln
Damit Sie diese Aufstellung in Power-Query bearbeiten können, sollten Sie diese zuerst in eine intelligente Tabelle umwandeln. Hierdurch wird der Import und die anschließende Bearbeitung enorm vereinfacht.
Markieren Sie hierzu eine Zelle in der Kreuztabelle und drücken Sie anschließend die Tastenkombination Strg + T. Hierdurch wird das Dialogfeld Tabelle erstellen geöffnet.
Vergewissern Sie sich, dass der Tabellenbereich richtig erkannt worden ist und dass das Kontrollkästchen Tabelle hat Überschriften aktiviert ist. Bestätigen Sie die Einstellungen, indem Sie auf die Schaltfläche OK mit der linken Maustaste klicken.
Excel wandelt daraufhin die Tabelle in eine intelligente Tabelle um. Sie können dies visuell am hinzugefügten Tabellenformat erkennen. Des Weiteren können Sie eine intelligente Tabelle daran erkennen, wenn Sie eine Zelle in der potenziellen Tabelle aktivieren und gleichzeitig oben im Menüband die kontextbezogene Registerkarte Tabellenentwurf zur Verfügung steht.
Vergeben Sie als Nächstes einen sinnvollen Tabellennamen für die intelligente Tabelle. Markieren Sie hierzu eine Zelle in der Tabelle und aktivieren Sie dann die kontextbezogene Registerkarte Tabellenentwurf im Menüband.
Jetzt können Sie im Menüband unter der Gruppe Eigenschaften am linken Rand einen Tabellennamen manuell erfassen. Überschreiben Sie einfach den durch Excel vergebenen Tabellennamen durch das Wort „Umsatzaufstellung“ und bestätigen Sie Ihre Eingabe, indem Sie die Enter-Taste drücken.
Tabelle in Power-Query importieren
Als Nächstes importieren Sie die intelligente Tabelle in das Feature Power-Query von Excel. Markieren Sie hierzu eine Zelle in der intelligenten Tabelle und aktivieren Sie anschließend im Menüband die Befehlsfolge Registerkarte Daten > Befehlsgruppe Daten abrufen und transformieren > Befehl Aus Tabelle/Bereich.
Excel öffnet automatisch Power-Query und importiert die intelligente Tabelle. Die Überschriftszeile wird automatisch als Spaltenbeschriftung übernommen.
Da wir die horizontale Anordnung der Umsätze jetzt in eine vertikale Listendarstellung umwandeln möchten, markieren Sie bitte alle Spalten mit den Monatsbezeichnungen.
- Klicken Sie hierzu mit der linken Maustaste auf den Spaltennamen Januar. Hierdurch wird die ganze Spalte Januar markiert.
- Halten Sie jetzt die Umschalttaste gedrückt und drücken Sie so lange auf die Pfeiltaste nach rechts, bis Sie alle Spalten von Januar bis Dezember markiert haben.
Tabelle in Power-Query entpivotieren
Aktivieren Sie jetzt im Menüband von Power-Query die Befehlsfolge Registerkarte Transformieren > Befehlsgruppe Beliebige Spalte > Befehl Spalten entpivotieren.
Excel-Power-Query ordnet die Umsätze je Monat vertikal in einer Liste an. Die einzelnen Monatsbezeichnungen werden hierbei unter einer neuen Spalte mit dem Namen Attribut angeordnet, während die Umsätze in der Spalte Wert dargestellt werden.
Vergeben Sie als Nächstes für den Spaltennamen Attribut doch einen aussagekräftigeren Namen. Klicken Sie hierzu mit der linken Maustaste auf den Spaltennamen Attribut und wählen Sie im Kontextmenü den Eintrag Umbenennen… aus.
Sie können den Namen Attribut dann beispielsweise in „Monat“ überschreiben.
Bestätigen Sie Ihre Eingabe, indem Sie die Entertaste drücken. Wenn Sie den Namen der Spalte „Wert“ ebenfalls ändern wollen, dann gehen Sie bitte analog vor.
Neue Tabelle in Excel-Tabellenblatt übernehmen
Jetzt können Sie die transformierte Liste wieder in ein Tabellenblatt von Excel ausgeben, damit Sie diese wie gewohnt weiterverarbeiten können. Klicken Sie hierzu im Menüband von Power Query auf die Befehlsfolge Registerkarte Start > Befehlsgruppe Schließen > Befehl Schließen & laden.
Daraufhin wird Power-Query in Excel beendet und die transformierte Liste wird in ein neues Tabellenblatt ausgegeben. Das neue Tabellenblatt bekommt automatisch den Namen der jeweiligen Abfrage. In unserem Beispiel ist dies der Name Umsatzaufstellung. Sie können die transformierte Liste nun wie gewohnt in Excel weiterverarbeiten.
