Excel-Daten eingeben, prüfen und umformenAus einer Kreuztabelle eine Excel-Liste erstellen – entpivotieren

Wie sind Kreuztabellen aufgebaut?

In Excel können 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 Umsatz) 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.

Aufgabe: Aus einer Kreuztabelle eine Liste erstellen

Beispiel: In der folgenden Abbildung sehen Sie eine Kreuztabelle, in der die Umsätze der Vertriebsmitarbeitenden und Zweigstellen 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 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 werden 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 wurde und das Kontrollkästchen Tabelle hat Überschriften aktiviert ist. Bestätigen Sie die Einstellungen, indem Sie auf die Schaltfläche OK 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, dass 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 für Sie verständlichen 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 (links) 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 Sie die horizontale Anordnung der Umsätze jetzt in eine vertikale Listendarstellung umwandeln möchten, markieren Sie 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 noch 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 beispielsweise mit 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 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. Im Beispiel ist dies der Name Umsatzaufstellung.

Sie können die transformierte Liste nun wie gewohnt in Excel weiterverarbeiten.