5 Möglichkeiten, Daten in Excel zu transponieren (Schritt-für-Schritt-Anleitung)
Das Transponieren von Daten bedeutet, die Ausrichtung eines Arrays zu ändern, indem seine Zeilen in Spalten oder umgekehrt konvertiert werden. In diesem Tutorial teilen wir fünf verschiedene Methoden mit Ihnen, um die Ausrichtung eines Arrays zu wechseln und das gewünschte Ergebnis zu erzielen.
Video: Daten in Excel transponieren
Spalten mit der Funktion „Inhalte einfügen“ in Zeilen umwandeln
Schritt 1: Den zu transponierenden Bereich kopieren
Wählen Sie den Zellbereich aus, in dem Sie Zeilen und Spalten wechseln möchten, und drücken Sie dann „Strg“ + „C“, um den Bereich zu kopieren.
Schritt 2: Wählen Sie die Option „Transponieren einfügen“
Klicken Sie mit der rechten Maustaste auf die erste Zelle des Zielbereichs und klicken Sie dann auf das Symbol „Transponieren“ (unter „Einfügeoptionen“) im Rechtsklick-Menü.
Ergebnis
Voila! Der Bereich wird sofort transponiert!
(AD) Tabellendimensionen einfach mit Kutools transponieren
Das Konvertieren einer Kreuztabelle (zweidimensionale Tabelle) in eine flache Liste (eindimensionale Liste) oder umgekehrt in Excel nimmt immer viel Zeit und Mühe in Anspruch. Mit installiertem Kutools für Excel hilft Ihnen das Tool „Tabellendimensionen transponieren“ dabei, die Konvertierung schnell und einfach durchzuführen.
"Kutools für Excel" - Enthält mehr als 300 praktische Funktionen, die Ihre Arbeit wesentlich erleichtern. Holen Sie sich jetzt eine 30-tägige kostenlose Vollversion!
Daten transponieren und mit der Quelle verknüpfen
Um die Ausrichtung eines Bereichs dynamisch zu wechseln (Spalten in Zeilen und umgekehrt) und das gewechselte Ergebnis mit dem ursprünglichen Datensatz zu verbinden, können Sie eine der folgenden Methoden verwenden:
- "TRANSPOSE-Funktion" (Einfach zu verwenden, aber die Ergebnisse sind nicht editierbar)
- "INDIREKT, ADRESSE, SPALTE und ZEILE Funktionen" (Große Formel, aber ermöglicht Änderungen in den Ergebnissen)
- "Inhalte einfügen und Suchen & Ersetzen" (Komplex, aber leicht verständlich)
- "Power Query" (Einfach mit Ergebnissen formatiert in einer Tabelle)
Zeilen mit der TRANSPOSE-Funktion in Spalten ändern
Um Zeilen in Spalten und umgekehrt mit der „TRANSPOSE“-Funktion zu wechseln, gehen Sie bitte wie folgt vor.
Schritt 1: Wählen Sie dieselbe Anzahl leerer Zellen wie der ursprüngliche Zellensatz, aber in anderer Richtung
Tipp: Überspringen Sie diesen Schritt, wenn Sie Excel 365 oder Excel 2021 verwenden.
Angenommen, Ihre ursprüngliche Tabelle befindet sich im Bereich A1:C4, was bedeutet, dass die Tabelle 4 Zeilen und 3 Spalten hat. Daher hat die transponierte Tabelle 3 Zeilen und 4 Spalten. Das bedeutet, Sie sollten 3 Zeilen und 4 Spalten leerer Zellen auswählen.
Schritt 2: Geben Sie die TRANSPOSE-Formel ein
Geben Sie die folgende Formel in der Formelleiste ein und drücken Sie dann „Strg“ + „Umschalt“ + „Enter“, um das Ergebnis zu erhalten.
Tipp: Drücken Sie „Enter“, wenn Sie Excel 365 oder Excel 2021 verwenden.
=TRANSPOSE(A1:C4)
- Sie sollten A1:C4 in Ihren tatsächlichen Quellbereich ändern, den Sie transponieren möchten.
- Wenn es im ursprünglichen Bereich leere Zellen gibt, würde die „TRANSPOSE“-Funktion die leeren Zellen in Nullen (0) umwandeln. Um die Null-Ergebnisse loszuwerden und die leeren Zellen beim Transponieren beizubehalten, müssen Sie die „IF“-Funktion nutzen:
-
=TRANSPOSE(IF(A1:C4="","",A1:C4))
Ergebnis
Die Zeilen werden in Spalten umgewandelt und die Spalten werden in Zeilen konvertiert.
Daten mit den Funktionen INDIREKT, ADRESSE, SPALTE und ZEILE drehen
Obwohl die obige Formel ziemlich einfach zu verstehen und zu verwenden ist, besteht der Nachteil darin, dass Sie keine Zellen in der gedrehten Tabelle bearbeiten oder löschen können. Deshalb stelle ich eine Formel vor, die die Funktionen „INDIREKT“, „ADRESSE“, „SPALTE“ und „ZEILE“ verwendet. Angenommen, Ihre ursprüngliche Tabelle befindet sich im Bereich A1:C4, führen Sie zur Durchführung einer Spalten-zu-Zeilen-Transformation und um die gedrehten Daten mit dem Quelldatensatz zu verbinden, bitte die folgenden Schritte aus.
Schritt 1: Geben Sie die Formel ein
Geben Sie die folgende Formel in die oberste linke Zelle des Zielbereichs (A6 in unserem Fall) ein und drücken Sie „Enter“:
=INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))
- Sie sollten A1 in die oberste linke Zelle Ihres tatsächlichen Quellbereichs ändern, den Sie transponieren werden, und die Dollarzeichen so belassen, wie sie sind. Das Dollarzeichen ($) vor dem Spaltenbuchstaben und der Zeilennummer zeigt einen absoluten Bezug an, der den Spaltenbuchstaben und die Zeilennummer unverändert lässt, wenn Sie die Formel in andere Zellen verschieben oder kopieren.
- Wenn es im ursprünglichen Bereich leere Zellen gibt, würde die Formel die leeren Zellen in Nullen (0) umwandeln. Um die Null-Ergebnisse loszuwerden und die leeren Zellen beim Transponieren beizubehalten, müssen Sie die „IF“-Funktion nutzen:
-
=IF(INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))=0,"",INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1))))
Schritt 2: Kopieren Sie die Formel nach rechts und in die unteren Zellen
Wählen Sie die Formelzelle aus und ziehen Sie ihren Ausfüllkästchen (das kleine grüne Quadrat in der unteren rechten Ecke der Zelle) nach unten und dann nach rechts zu so vielen Zeilen und Spalten wie nötig.
Ergebnis
Die Spalten und Zeilen werden sofort getauscht.
Spalten mit „Inhalte einfügen“ und „Suchen & Ersetzen“ in Zeilen umwandeln
Ein paar weitere Schritte mit der Methode „Inhalte einfügen“ zusammen mit der Funktion „Suchen und Ersetzen“ ermöglichen es Ihnen, Daten zu transponieren, während Sie die Quellzellen mit den transponierten verknüpfen.
Schritt 1: Den zu transponierenden Bereich kopieren
Wählen Sie den Zellbereich aus, den Sie transponieren möchten, und drücken Sie dann „Strg“ + „C“, um den Bereich zu kopieren.
Schritt 2: Wenden Sie die Option „Verknüpfung einfügen“ an
Klicken Sie mit der rechten Maustaste auf eine leere Zelle und klicken Sie dann auf „Inhalte einfügen“.
Klicken Sie auf die Schaltfläche „Verknüpfung einfügen“.
Sie erhalten das Ergebnis wie unten gezeigt:
Schritt 3: Suchen und Ersetzen der Gleichheitszeichen (=) aus dem Ergebnis von „Verknüpfung einfügen“
Wählen Sie den Ergebnisbereich (A6:C9) aus und drücken Sie „Strg“ + „H“, und ersetzen Sie „=“ durch „@EO“ (oder beliebige Zeichen, die nicht im ausgewählten Bereich existieren) im Dialogfeld „Suchen und Ersetzen“.
Klicken Sie auf „Alle ersetzen“ und schließen Sie dann das Dialogfeld. Das Folgende zeigt, wie die Daten aussehen werden.
Schritt 4: Transponieren des ersetzten Ergebnisses von „Verknüpfung einfügen“
Wählen Sie den Bereich (A6:C9) aus und drücken Sie „Strg“ + „C“, um ihn zu kopieren. Klicken Sie mit der rechten Maustaste auf eine leere Zelle (hier habe ich A11 ausgewählt) und wählen Sie das Symbol „Transponieren“ aus den Einfügeoptionen, um das transponierte Ergebnis einzufügen.
Schritt 5: Gleichheitszeichen (=) zurückholen, um das transponierte Ergebnis mit den Originaldaten zu verknüpfen
Behalten Sie die transponierten Ergebnisdaten A11:D13 ausgewählt und drücken Sie dann „Strg“ + „H“, und ersetzen Sie „@EO“ durch „=“ (das Gegenteil von „Schritt 3“).
Klicken Sie auf „Alle ersetzen“ und schließen Sie dann das Dialogfeld.
Ergebnis
Die Daten sind transponiert und mit den ursprünglichen Zellen verknüpft.
Daten transponieren und mit Power Query mit der Quelle verknüpfen
Power Query ist ein leistungsstarkes Werkzeug zur Automatisierung von Daten, das es Ihnen ermöglicht, Daten in Excel einfach zu transponieren. Sie können die Aufgabe erledigen, indem Sie die folgenden Schritte ausführen:
Schritt 1: Wählen Sie den zu transponierenden Bereich aus und öffnen Sie den Power Query-Editor
Wählen Sie den zu transponierenden Datenbereich aus. Und dann, auf der Registerkarte „Daten“, in der Gruppe „Daten abrufen & transformieren“, klicken Sie auf „Aus Tabelle/Bereich“.
- Wenn der ausgewählte Datenbereich sich nicht in einer Tabelle befindet, wird ein Dialogfeld „Tabelle erstellen“ angezeigt; klicken Sie auf „OK“, um eine Tabelle dafür zu erstellen.
- Wenn Sie Excel 2013 oder 2010 verwenden und „Aus Tabelle/Bereich“ auf der Registerkarte „Daten“ nicht finden können, müssen Sie es von der „Microsoft Power Query für Excel-Seite“ herunterladen und installieren. Sobald es installiert ist, gehen Sie zur Registerkarte „Power Query“, klicken Sie in der Gruppe „Excel-Daten“ auf „Aus Tabelle“.
Schritt 2: Spalten mit Power Query in Zeilen umwandeln
Gehen Sie zur Registerkarte „Transformieren“. Wählen Sie im Dropdown-Menü „Erste Zeile als Überschriften verwenden“ die Option „Überschriften als erste Zeile verwenden“.
Klicken Sie auf „Transponieren“.
Schritt 3: Speichern Sie die transponierten Daten in einem Blatt
Klicken Sie auf der Registerkarte „Datei“ auf „Schließen & laden“, um das Fenster „Power Editor“ zu schließen und ein neues Arbeitsblatt zu erstellen, um die transponierten Daten zu laden.
Ergebnis
Die transponierten Daten werden in einer neu erstellten Tabelle in einem Arbeitsblatt konvertiert.
- Zusätzliche Spaltenüberschriften werden in der ersten Zeile wie oben gezeigt generiert. Um die erste Zeile unter den Überschriften zu Spaltenüberschriften zu machen, wählen Sie eine Zelle innerhalb der Daten aus und klicken Sie auf „Abfrage“ > „Bearbeiten“. Wählen Sie dann „Transformieren“ > „Erste Zeile als Überschriften verwenden“. Zum Schluss wählen Sie „Startseite“ > „Schließen & laden“.
- Wenn Änderungen am ursprünglichen Datensatz vorgenommen werden, können Sie die oben genannten transponierten Daten mit diesen Änderungen aktualisieren, indem Sie auf das Symbol „Aktualisieren“ neben der Tabelle im Bereich „Abfragen & Verbindungen“ klicken oder indem Sie auf die Schaltfläche „Aktualisieren“ auf der Registerkarte „Abfrage“ klicken.
(AD) Bereich mit Kutools in wenigen Klicks transformieren
Das Hilfsprogramm „Bereich transformieren“ in Kutools für Excel kann Ihnen helfen, eine vertikale Spalte einfach in mehrere Spalten oder umgekehrt zu transformieren (konvertieren), oder eine Zeile in mehrere Zeilen oder umgekehrt zu konvertieren.
"Kutools für Excel" - Enthält mehr als 300 praktische Funktionen, die Ihre Arbeit wesentlich erleichtern. Holen Sie sich jetzt eine 30-tägige kostenlose Vollversion!
Verwandte Artikel
- Wie kann man Bereiche schnell transponieren und leere Zellen überspringen in Excel?
- Wenn wir einen Bereich als transponiert einfügen, werden auch die leeren Zellen eingefügt. Manchmal möchten wir jedoch nur die Bereiche transponieren und dabei die leeren Zellen ignorieren, wie im folgenden Screenshot gezeigt. Gibt es Möglichkeiten, Bereiche schnell zu transponieren und Leerstellen in Excel zu überspringen?
- Wie transponiert man jede 5. oder n-te Zeile aus einer Spalte in mehrere Spalten?
- Angenommen, Sie haben lange Daten in Spalte A und möchten nun jede 5. Zeile aus Spalte A in mehrere Spalten transponieren, z.B. A1:A5 in C6:G6, A6:A10 in C7:G7 usw., wie im folgenden Screenshot gezeigt. Wie könnten Sie diese Aufgabe ohne wiederholtes Kopieren und Einfügen in Excel bewältigen?
- Wie transponiert/konvertiert man Spalten und Zeilen in eine einzelne Spalte?
- Wenn Sie Excel-Arbeitsblätter verwenden, stoßen Sie möglicherweise auf dieses Problem: Wie könnte man einen Bereich von Daten in eine einzelne Spalte konvertieren oder transponieren? (Siehe die folgenden Screenshots:) Nun, ich stelle Ihnen drei schnelle Tricks vor, um dieses Problem zu lösen.
- Wie transponiert/konvertiert man Spalten und Zeilen in eine einzelne Zeile?
- Wie kann man mehrere Zeilen und Spalten in eine einzige lange Zeile zusammenführen? Vielleicht scheint es Ihnen einfach, weil Sie sie einzeln kopieren und manuell in eine Zeile einfügen können. Allerdings kann dies zeitaufwendig und mühsam sein, wenn es Hunderte von Zeilen und Spalten gibt. Hier werde ich über einige schnelle Tricks sprechen, um dies zu lösen.
Beste Büroproduktivitätswerkzeuge
Verbessern Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor. Kutools für Excel bietet über300 erweiterte Funktionen zur Steigerung der Produktivität und Zeitersparnis. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt eine Registerkartenoberfläche zu Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie die Bearbeitung und das Lesen mit Registerkarten in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters, anstatt in neuen Fenstern.
- Steigert Ihre Produktivität um50 % und reduziert täglich hunderte von Mausklicks für Sie!
Inhaltsverzeichnis
- Video: Daten in Excel transponieren
- Spalten mit der Funktion „Inhalte einfügen“ in Zeilen umwandeln
- Daten transponieren und mit der Quelle verknüpfen
- Die TRANSPOSE-Funktion verwenden
- Die Funktionen INDIREKT, ADRESSE, SPALTE und ZEILE verwenden
- „Inhalte einfügen“ und „Suchen & Ersetzen“ verwenden
- Die Power Query-Funktion verwenden
- Verwandte Artikel
- Die besten Tools zur Produktivitätssteigerung im Büro
- Kommentare