Video ansehen – Wie man Daten in Excel transponiert
Wenn Sie einen Datensatz haben und diesen in Excel transponieren wollen (d.h. Zeilen in Spalten und Spalten in Zeilen umwandeln), ist es ein absolutes NEIN, dies manuell zu tun!
Daten mit Einfügen Spezial transponieren
Einfügen Spezial kann eine Menge erstaunlicher Dinge tun, und eines dieser Dinge ist, Daten in Excel zu transponieren.
Angenommen, Sie haben einen Datensatz wie unten gezeigt:
Diese Daten haben die Regionen in einer Spalte und die Viertel in einer Zeile.
Wenn Sie diese Daten nun aus irgendeinem Grund transponieren müssen, können Sie dies mit Hilfe von Einfügen Spezial tun:
- Markieren Sie den Datensatz (in diesem Fall A1:E5).
- Kopieren Sie den Datensatz (Strg + C) oder klicken Sie mit der rechten Maustaste und wählen Sie Kopieren.
- Nun können Sie die transponierten Daten an einer neuen Stelle einfügen. In diesem Beispiel möchte ich in G1:K5 kopieren, also klicken Sie mit der rechten Maustaste auf die Zelle G1 und wählen Einfügen spezial.
- Im Dialogfeld Einfügen spezial aktivieren Sie unten rechts die Option Transponieren.
- Klicken Sie auf „OK“.
Damit werden die Daten sofort kopiert und eingefügt, aber so, dass sie transponiert wurden. Unten sehen Sie eine Demo, die den gesamten Prozess zeigt.
Die oben gezeigten Schritte kopieren den Wert, die Formel (falls vorhanden) sowie das Format. Wenn Sie nur den Wert kopieren möchten, wählen Sie im Dialogfeld „Einfügen spezial“ die Option „Wert“.
Beachten Sie, dass die kopierten Daten statisch sind, und wenn Sie Änderungen im ursprünglichen Datensatz vornehmen, werden diese Änderungen nicht in den transponierten Daten wiedergegeben.
Wenn Sie möchten, dass diese transponierten Zellen mit den Originalzellen verknüpft werden, können Sie die Leistung von Suchen & Ersetzen mit Einfügen Spezial kombinieren.
Daten transponieren mit Einfügen Spezial und Suchen & Ersetzen
Bei der Verwendung von Einfügen Spezial allein erhalten Sie statische Daten. Das heißt, wenn sich Ihre Originaldaten ändern und Sie möchten, dass die transponierten Daten ebenfalls aktualisiert werden, müssen Sie erneut Einfügen Spezial verwenden, um sie zu transponieren.
Hier ist ein cooler Trick, den Sie verwenden können, um die Daten zu transponieren und sie trotzdem mit den Originalzellen zu verknüpfen.
Angenommen, Sie haben einen Datensatz wie unten gezeigt:
Hier sind die Schritte, um die Daten zu transponieren, aber die Verknüpfungen intakt zu lassen:
- Wählen Sie den Datensatz (A1:E5).
- Kopieren Sie ihn (Strg + C oder Rechtsklick und Kopieren wählen).
- Jetzt können Sie die transponierten Daten an einer neuen Stelle einfügen. In diesem Beispiel möchte ich in G1:K5 kopieren, also klicken Sie mit der rechten Maustaste auf die Zelle G1 und wählen Einfügen spezial.
- Im Dialogfeld Einfügen spezial klicken Sie auf die Schaltfläche Verbindung einfügen. Dadurch erhalten Sie denselben Datensatz, aber hier sind die Zellen mit dem ursprünglichen Datensatz verknüpft (z. B. G1 ist mit A1 verknüpft, und G2 ist mit A2 verknüpft, usw.).
- Mit diesen neu kopierten Daten ausgewählt, Drücken Sie Strg + H (oder gehen Sie auf Start -> Bearbeiten -> Suchen & Auswählen -> Ersetzen). Dadurch wird das Dialogfeld Suchen & Ersetzen geöffnet.
- Im Dialogfeld Suchen & Ersetzen verwenden Sie Folgendes:
- In Suchen nach: =
- In Ersetzen durch: !@# (beachten Sie, dass ich !@# verwende, da es sich um eine eindeutige Zeichenkombination handelt, die wahrscheinlich nicht in Ihren Daten vorkommt. Sie können eine beliebige solche eindeutige Zeichenkombination verwenden).
- Klicken Sie auf Alle ersetzen. Dadurch wird das Gleichheitszeichen aus der Formel ersetzt und Sie haben in jeder Zelle !@# gefolgt vom Zellbezug.
- Klicken Sie mit der rechten Maustaste und kopieren Sie den Datensatz (oder verwenden Sie Control + C).
- Wählen Sie eine neue Position, klicken Sie mit der rechten Maustaste und wählen Sie Einfügen spezial. In diesem Beispiel füge ich es in Zelle G7 ein. Sie können es an beliebiger Stelle einfügen.
- Wählen Sie im Dialogfeld „Spezial einfügen“ die Option „Transponieren“ aus und klicken Sie auf „OK“.
- Kopieren Sie die neu erstellten transponierten Daten in die Zelle, aus der sie erstellt wurden.
- Nun öffnen Sie erneut das Dialogfeld „Suchen und Ersetzen“ und ersetzen !@# durch =.
So erhalten Sie verknüpfte Daten, die transponiert wurden. Wenn Sie Änderungen im Originaldatensatz vornehmen, werden die transponierten Daten automatisch aktualisiert.
Hinweis: Da unsere Originaldaten A1 als leer haben, müssten Sie die 0 in G1 manuell löschen. Die 0 erscheint beim Einfügen von Verknüpfungen, da eine Verknüpfung mit einer leeren Zelle immer noch eine 0 zurückliefern würde. Außerdem müssten Sie den neuen Datensatz formatieren (Sie können das Format einfach nur aus dem Originaldatensatz kopieren).
Daten mit der Excel-Funktion TRANSPOSE transponieren
Mit der Excel-Funktion TRANSPOSE können Sie – wie der Name schon sagt – Daten in Excel transponieren.
Angenommen, Sie haben einen Datensatz wie unten gezeigt:
Hier sind die Schritte zum Transponieren:
- Wählen Sie die Zellen aus, in die Sie den Datensatz transponieren möchten. Beachten Sie, dass Sie genau die gleiche Anzahl von Zellen auswählen müssen wie die Originaldaten. Wenn Sie also z. B. 2 Zeilen und 3 Spalten haben, müssen Sie 3 Zeilen und 2 Spalten der Zellen auswählen, in denen Sie die transponierten Daten haben möchten. In diesem Fall, da es 5 Zeilen und 5 Spalten gibt, müssen Sie 5 Zeilen und 5 Spalten markieren.
- Geben Sie =TRANSPOSE(A1:E5) in die aktive Zelle ein (das sollte die obere linke Zelle der Markierung sein und drücken Sie Strg-Umschalt-Eingabe.
Damit würden Sie den Datensatz transponieren.
Hier sind ein paar Dinge, die Sie über die TRANSPOSE-Funktion wissen müssen:
- Es ist eine Array-Funktion, daher müssen Sie Control-Shift-Enter verwenden und nicht nur Enter.
- Sie können nicht einen Teil des Ergebnisses löschen. Sie müssen das gesamte Array von Werten löschen, das die TRANSPOSE-Funktion zurückgibt.
- Die Transpose-Funktion kopiert nur die Werte, nicht die Formatierung.
Daten mit Power Query transponieren
Power Query ist ein leistungsstarkes Werkzeug, mit dem Sie Daten in Excel schnell transponieren können.
Power Query ist Bestandteil von Excel 2016 (Holen Sie sich & Transform in der Registerkarte Daten), aber wenn Sie Excel 2013 oder 2010 verwenden, müssen Sie es als Add-in installieren.
Angenommen, Sie haben den unten abgebildeten Datensatz:
Hier sind die Schritte, um diese Daten mit Power Query zu transponieren:
In Excel 2016
- Markieren Sie die Daten und gehen Sie auf Daten -> Holen Sie & Transformieren -> Aus Tabelle.
- Im Dialogfeld „Tabelle erstellen“ stellen Sie sicher, dass der Bereich korrekt ist, und klicken Sie auf OK. Daraufhin wird das Dialogfeld Abfrage-Editor geöffnet.
- Im Dialogfeld Abfrage-Editor wählen Sie die Registerkarte ‚Transformieren‘.
- In der Registerkarte ‚Transformieren‘ gehen Sie auf ‚Tabelle‘ -> Erste Zeile als Überschrift verwenden -> Überschriften als erste Zeile verwenden. Dadurch wird sichergestellt, dass die erste Zeile (die die Überschriften Q1, Q2, Q3 und Q4 enthält) auch als Daten behandelt und transponiert wird.
- Klicken Sie auf Transponieren. Dies würde die Daten sofort transponieren.
- Klicken Sie auf Use First Row as Headers. Dies würde nun die erste Zeile der transponierten Daten als Überschriften machen.
- Go to File -> Close and Load. Dadurch wird das Power-Editor-Fenster geschlossen und ein neues Blatt erstellt, das die transponierten Daten enthält.
Beachten Sie, dass die linke obere Zelle unseres Datensatzes leer war und daher einen generischen Namen Spalte1 erhält (wie unten gezeigt). Sie können diese Zelle aus den transponierten Daten löschen.
In Excel 2013/2010
In Excel 2013/10 müssen Sie Power Query als Add-In installieren.
Klicken Sie hier, um das Plugin herunterzuladen und eine Installationsanleitung zu erhalten.
Nachdem Sie Power Query installiert haben, gehen Sie zu Power Query -> Excel Daten -> Aus Tabelle.
Dadurch öffnet sich das Dialogfeld Tabelle erstellen. Folgen Sie nun den gleichen Schritten wie in Excel 2016.
Die folgenden Excel-Tutorials könnten Ihnen auch gefallen:
- Multiplizieren Sie in Excel mit Einfügen Spezial.
- Kombinieren Sie Excel-Bereiche (mit und ohne Trennzeichen).
- Daten in Excel-Arbeitsblättern bereinigen.
- Spalten in Excel vergleichen
- Excel Text in Spalten einfügen
- Excel TRIM-Funktion
- Tabellen in Excel zusammenführen