Transponieren in Excel – drei Wege zur gespiegelten Tabelle
Bei der Anwendung von Excel stoßen Sie früher oder später auf ein wiederkehrendes Problem: Daten liegen in einer Struktur vor, die Sie eigentlich nicht wollen; beispielsweise Monate in den Zeilen und Kennzahlen in den Spalten. Sie benötigen jedoch genau die umgekehrte Ansicht.
Das manuelle Umkopieren ist fehleranfällig und zeitraubend. Die Lösung: Transponieren.
In der Mathematik versteht man unter Transponieren das Spiegeln einer Matrix entlang ihrer Diagonalen. Dabei werden Zeilen zu Spalten und Spalten zu Zeilen.
Excel bietet Ihnen gleich drei Methoden, mit denen Sie Ihre Daten entsprechend transponieren können:
- den klassischen Einfügeassistenten,
- die dynamische MTRANS()-Funktion und
- die leistungsstarke Power-Query-Transformation.
Transponieren mit dem Einfügeassistenten
Diese Methode ist besonders dann geeignet, wenn Sie eine einmalige Umstrukturierung Ihrer Daten benötigen – beispielsweise zur besseren Lesbarkeit oder zur Weiterverarbeitung in einer Präsentation.
Die folgende Abbildung soll für diese Methode als Beispiel dienen.
Markieren Sie zunächst den gesamten Bereich, den Sie transponieren möchten. Tipp: Mit Strg + A oder Shift + Pfeiltasten geht das blitzschnell.
Kopieren Sie die Daten, indem Sie die Tastenkombination Strg + C drücken.
Gehen Sie zu einer freien Zelle außerhalb des Originalbereichs – Transponieren funktioniert nicht an Ort und Stelle.
Klicken Sie mit der rechten Maustaste auf die Zielzelle und aktivieren Sie im Kontextmenü den Befehl Inhalte einfügen.
Es öffnet sich das Dialogfeld Inhalte einfügen. Aktivieren Sie hier unten das Kontrollkästchen bei Transponieren. Bestätigen Sie Ihre Einstellungen, indem Sie das Dialogfeld durch Klick auf OK schließen.
Die Daten erscheinen nun gespiegelt – was vorher in Zeilen stand, steht nun in Spalten und umgekehrt. Die Methode ist intuitiv und schnell. Aber: Sie ist statisch. Änderungen am Ursprungsbereich wirken sich nicht automatisch auf die transponierte Version aus.
Transponieren mit der Funktion MTRANS()
Wenn Sie dynamisch mit Ihren Daten arbeiten möchten, dann ist die Funktion MTRANS() die richtige Wahl. Sie macht das Transponieren einfach und smart, da sich Änderungen am Ursprung automatisch auf das transponierte Ergebnis auswirken.
Angenommen, Ihre Daten stehen im Bereich A1:D4, dann geben Sie in einer leeren Zelle die folgende Formel ein:
=MTRANS(A1:D4)
Excel erkennt automatisch, wie viele Zeilen und Spalten erforderlich sind, um das Ergebnis korrekt darzustellen. Dank der neuen dynamischen Array-Funktionalität (ab Excel 365 / Excel 2019) benötigen Sie kein Vorab-Markieren mehr – ein einfacher Druck auf die Enter-Taste genügt.
Vorteile:
- Automatische Aktualisierung bei Änderungen der Quelldaten
- Ideal für Dashboards, Berichte und verknüpfte Auswertungen
- Kein manueller Aufwand beim Aktualisieren
Hinweis:
Ältere Excel-Versionen vor 2019 benötigen bei der MTRANS()-Funktion eine Markierung der passenden Zellenanzahl vor der Eingabe und das Abschließen der Formel mit Strg + Shift + Enter.
Transponieren mit Power Query
Power Query ist ein mächtiges Tool, das sich perfekt für die automatisierte Datenaufbereitung eignet – ideal für Nutzer, die regelmäßig mit Rohdaten aus verschiedenen Quellen arbeiten.
Auch hier lässt sich eine Transponierung durchführen – sie heißt hier nur anders: Vertauschen.
Öffnen Sie den Power-Query-Editor, indem Sie Menüband die Befehlsfolge Registerkarte Daten > Befehlsgruppe Daten abrufen und transformieren > Befehl Daten abrufen > Befehl Aus Datei > Befehl Aus Excel-Arbeitsmappe anklicken.
Wählen Sie im nächsten Dialogfeld die Excel-Datei aus, in welcher sich die Daten befinden. Daraufhin öffnet sich das Dialogfeld Navigator.
Wählen Sie hier am linken Rand die Tabelle aus, in der sich die Daten befinden, und klicken Sie dann auf den Befehl Daten transformieren.
Es öffnet sich der Power-Query-Editor, und die Daten werden angezeigt.
Wichtig: Achten Sie darauf, dass die erste Zeile nicht als Überschrift erkannt wird, sondern als reguläre Datenzeile. Nur so bleibt sie beim Vertauschen erhalten.
Sollte die erste Zeile als Überschrift im Power-Query-Editor dargestellt werden, dann entfernen Sie am rechten Rand bei Angewendete Schritte den Eintrag Höher gestufte Header, indem Sie am linken Rand auf das X-Zeichen klicken.
Markieren Sie als Nächstes alle Spalten: Klicken Sie hierzu mit der linken Maustaste auf die erste Spalte und dann bei gehaltener Tastenkombination Shift + Klick mit der linken Maustaste auf die letzte Spalte.
Aktivieren Sie im Menüband die Befehlsfolge Registerkarte Transformieren > Befehl Vertauschen.
Ihre Tabelle ist nun transponiert: Spalten wurden zu Zeilen und umgekehrt. Sie sehen zum Beispiel die Monatsnamen jetzt in der ersten Spalte und die Kennzahlen (Umsatz, Kosten, Gewinn) in den Spalten; allerdings noch nicht als Überschriften.
Jetzt können Sie die erste Zeile als Überschrift definieren.
Klicken Sie hierzu im Menüband auf den Befehl Start > Erste Zeile als Überschriften verwenden. Hierdurch werden die Inhalte der ersten Zeile als Überschrift „heraufgestuft“.
Jetzt können Sie die Daten wieder nach Excel laden, indem Sie die Befehlsfolge Datei > Schließen und laden anklicken.
Die transponierten Daten werden daraufhin als intelligente Tabelle in ein Tabellenblatt geladen.
Vorteile:
- Ideal für große Datenmengen und wiederkehrende Aufgaben
- Transponierung als fester Schritt in einer Datenaufbereitungskette
- Kombinierbar mit Filtern, Gruppierungen und Berechnungen
Fazit: Welche Methode ist die richtige für Sie?
- Einfügeassistent: Einmaliges Umdrehen für Präsentation, Druck etc.; sehr einfach und schnell
- MTRANS(): Berichte, Dashboards, Excel-Vorlagen mit Verknüpfung; ist dynamisch bei Veränderungen
- Power Query: bei wiederkehrenden Datenimporten und -aufbereitungen
