Wie transponiert man mehrzeilige Datensätze in ein vernünftiges Tabellenformat? 34

Artikelbild-311-gross
Mehrzeilige Datensätze in einzeilige umwandeln: Drei mögliche Lösungen
 

Die Verarbeitung von aus externen Quellen stammenden Daten ist ein häufiger Anwendungsfall von Excel. Manchmal liegen diese Daten aber in einem unbrauchbaren oder zumindest unhandlichen Format vor.

Wer schon einmal mit Adresslisten (oder Artikellisten) zu tun hatte, bei denen jedes Datenfeld in einer neuen Zeile stand, weiß wovon ich spreche.

Wie man solche mehrzeiligen Datensätze in ein einzeiliges Tabellenformat verwandeln kann, zeigt der heutige Artikel.

Und so geht’s:

Beispieldatei herunterladen
Beispieldatei herunterladen

Die Ausgangslage

Zur Veranschaulichung habe ich wieder eine kleine Beispieltabelle mit einer Reihe von fiktiven Adressdatensätzen vorbereitet. Leider wurden diese aus irgendeinem steinzeitlichen Quellprogramm exportiert und haben daher ein sehr unpraktisches Format: Eine lange Liste, in der jeder einzelne Wert untereinander in einer eigenen Zeile steht:

Adressenliste im vertikalen Format

Adressenliste im vertikalen Format

Wie bringt man diese unhandliche Liste nun in ein wesentlich sinnvolleres Tabellenformat? Dazu gibt es verschiedene Möglichkeiten.

Variante 1: Kopieren und einfügen

Excel bietet im Rahmen der Kopierfunktion ein sehr praktisches Feature an, nämlich die sogenannte Transformation. Dazu markieren wir die erste Adresse unserer Liste und kopieren diese mit STRG+C in die Zwischenablage:

Die erste Adresse kopieren...

Die erste Adresse kopieren…

Als nächstes stellen wir die aktive Zelle in einen freien Bereich und rufen per Rechtsklick das Kontextmenü auf. Bei den Einfügeoptionen gibt es ein Symbol, auf dem Zeilen und Spalten vertauscht werden, das sogenannte Transponieren:

... und über Inhalte einfügen transponieren.

… und über Inhalte einfügen transponieren.

Falls du mit einer Excel-Version vor 2013 arbeitest, findest du die Transponieren-Funktion unter „Inhalte einfügen – Transponieren“:

Inhalte einfügen - Transponieren

Inhalte einfügen – Transponieren

Nach einem Klick darauf wird die eben markierte Adresse fein säuberlich auf einzelne Spalten verteilt:

Die transponierte Adressenliste

Die transponierte Adressenliste

Und so könnten wir jetzt Adresse für Adresse in ein vernünftiges Spaltenformat überführen. Und wenn sie nicht gestorben sind, dann leben sie noch heute…
Anders formuliert: Bei einer geringen Anzahl an Datensätzen ist das sicherlich eine Möglichkeit. Sobald es aber mehr als 15 oder 20 Adressen sind, ist diese Variante gelinde gesagt etwas ermüdend.

Variante 2: Die Formel-Lösung (für alle Excel-Versionen)

Der schlaue (= faule) Excel-Anwender wird hingegen versuchen, das Problem mit ein paar Formeln zu lösen.
Ein grundsätzliche Möglichkeit dazu wäre die sehr spezielle MTRANS-Funktion. Dazu empfehle ich dir diesen Artikel, den ich vor längerer Zeit dazu geschrieben habe.

Heute jedoch verlassen wir uns auf die beiden Funktionen INDIREKT und ZEILE.

Die INDIREKT-Funktion arbeitet – wie der Name schon vermuten lässt – etwas um die Ecke. Man übergibt an die Funktion die Adresse einer Zelle, in der wiederum die Adresse einer anderen Zelle steht, deren Inhalt dann ausgegeben wird. Beispiel:

So arbeitet die INDIREKT-Funktion

So arbeitet die INDIREKT-Funktion

Die Funktion schaut also den Wert in Zelle B1 an. Wenn es sich dabei um eine gültige Zelladresse (oder einen definierten Namen) handelt, dann wird diese Adresse genommen und der Inhalt ausgegeben, der sich in der betreffenden Zelle befindet.

Wird hingegen an die INDIREKT-Funktion übergebene Adresse in doppelte Anführungszeichen gesetzt, dann wird direkt der Inhalt dieser Zelle ausgegeben:

Und nochmal die INDIREKT-Funktion

Und nochmal die INDIREKT-Funktion

Und genau dieses Verhalten werden wir für unsere Zwecke ausnutzen.

Da die nächste Zeile unserer Adresse in eine eigene Spalte soll, muss die in der INDIREKT-Funktion verwendete Adresse folglich A2 lauten, für das dritte Feld A3 usw.

Vereinfachte Formellösung

Vereinfachte Formellösung

Und für die nächste Adresse ginge es dann munter weiter mit A7, A8 usw. Damit wir aber nicht für jeden Datensatz die Formel manuell anpassen müssen, setzen wir die ZEILE-Funktion ein. Diese liefert einfach die Zeilennummer zu der angegebenen Adresse.
=ZEILE(A4)
liefert folglich den Wert 4.

In meiner Beispieltabelle besteht jeder Adressensatz aus 6 Zeilen, danach beginnt eine neue Adresse. Mit diesem Wissen bauen wir jetzt die fertige Formel zusammen, die für das erste Feld lautet:
=INDIREKT("A"&ZEILE($A1)*6-5)

Das Team aus INDIREKT und ZEILE

Das Team aus INDIREKT und ZEILE

Die Multiplikation der Zeilennummer mit 6 kommt eben daher, dass jede Adresse aus 6 Feldern besteht. Und davon ziehe ich noch eine festen Wert ab, um zum gewünschten Feld zu kommen: 6 – 5 = 1, also das erste Feld.

Zwar muss ich auch hier einmalig für jedes Feld meiner Adresse die Formel manuell anpassen. Also für das zweite Feld
=INDIREKT("A"&ZEILE($A1)*6-4)

… für das dritte …
=INDIREKT("A"&ZEILE($A1)*6-3)

… bis schließlich zum letzten Feld
=INDIREKT("A"&ZEILE($A1)*6)

Jetzt aber kann ich die Formeln einfach nach unten kopieren und erhalte damit meine fertige Adressenliste:

Die fertige Tabelle mit Formeln transponiert

Die fertige Tabelle mit Formeln transponiert

Ziemlich praktisch, wie ich finde!

Und falls deine Liste nicht in Zeile 1 beginnen sollte, musst du nur die Anzahl der leeren Zeilen in deiner Formel addieren:

Zusätzlicher Offset bei führenden Leerzeilen

Zusätzlicher Offset bei führenden Leerzeilen

Mit dieser Formel ist auch das Transponieren von mehreren 100 oder 1000 Datensätzen ruckzuck erledigt.

Variante 3: Die Formel-Lösung (für Excel aus Microsoft 365)

Das Beste kommt bekanntlich zum Schluss. Wenn du zu den Glücklichen gehörst, die Microsoft 365 einsetzen, dann geht es noch viel einfacher!

Hier hilft die neue Version ZEILENUMBRUCH, die es seit ungefähr August 2022 in Excel aus Microsoft 365 gibt. Und damit sieht die Lösung so aus:
=ZEILENUMBRUCH(A1:A42;6)

Einfachste Lösung mit der ZEILENUMBRUCH-Funktion

Einfachste Lösung mit der ZEILENUMBRUCH-Funktion

Die Funktion erwartet als ersten Parameter die zu bearbeitende Matrix, also unseren Tabellenbereich mit der Ausgangsliste. Der zweite Parameter (6) gibt an, dass immer nach 6 Spalten eine neue Zeile beginnen soll. Da es sich bei dieser Funktion um eine dynamische Array-Funktion handelt, wird sie nur in die erste Zelle (hier D1) eingetippt und läuft dann automatisch in so viele Zellen über, wie eben nötig sind. Einfacher geht’s kaum, oder?

Und wie so oft in Excel gilt: Die Lösung ist einfach. Man muss sie nur finden 🙂

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

34 Gedanken zu “Wie transponiert man mehrzeilige Datensätze in ein vernünftiges Tabellenformat?

  • Avatar-Foto
    Jonny

    hallo
    vielleicht kannst du mir helfen
    bis jetzt hat mir noch keiner eine leichte und brauchbare lösung geben können.
    ich habe einen Spielplan mit 78 Paarungen und die Ergebnisse
    daraus möchte ich jetzt eine kreuztabelle erstellen
    für eine schnelle antwort wäre ich dankbar
    MfG Jonny

    • Avatar-Foto
      Julius

      Die Frage ist auch, wie sinnvoll eine Kreuztabelle hier ist. Die erste Frage, die sich mir nämlich stellt: Gibt es pro Paarung nur ein Spiel oder ein Hin- und ein Rückspiel? Wenn ersteres der Fall ist, ergibt sich eine Tabelle mit einer Art diagonalen Spiegelachse. Die Achse besteht aus Nicht-Ergebnissen, da Spieler 1 nicht gegen sich selbst spielt, Spieler 2 ebenfalls nicht usw. Wenn es jede Begegnung zwischen den 13 Spielern nur einmal gibt, müsste in A2 das selbe Ergebnis stehen wie in B1 (also Spieler 1 X-Achse Spieler 2 Y-Achse ist dieselbe Paarung wie Spieler 2 X-Achse wie Spieler 1 Y-Achse usw.)

      Andernfalls wären es 156 Begegnungen, da würde die Kreuztabelle schon mehr Sinn machen. Wenn es nicht um eine Übersicht geht sondern darumt, eine Paarung schnell zu prüfen, könnte man auch zwei Eingabefelder machen und sich das Ergebnis per Formel darstellen lassen. Kannst du vielleicht deine Daten irgendwo hochladen und sagen, was du brauchst? Ich würde dann eine Lösung suchen und erklären =)

  • Avatar-Foto
    Tutti

    Hallo , ich habe eine Pivot Tabelle die ich für ein Berichtswesen brauche. Jedoch ändert sich die Pivot Tabelle jeden Monat, dass heißt die Zeilen verschieben sich, so dass ich diese nicht verlinken kann. Gibt es eine Möglichkeit dies zu vermeiden?

    • Avatar-Foto
      Martin Weiß

      Hallo Tutti,

      für solche Fälle gibt es die Funktion PIVOTDATENZUORDNEN. Prüfe mal in den Excel-Optionen unter der Kategorie „Formeln“, dass das Häkchen bei „GetPivotData-Funktionen für PivotTable-Bezüge verwenden“ gesetzt ist. Und dann setze mal einen Bezug in die Pivot-Tabelle, indem Du einfach mit dem Gleichheitszeichen beginnst und auf den gewünschten Wert in der Pivot-Tabelle klickst. Dann sollte die PIVOTDATENZUORDNEN-Funktion eingefügt werden, die auch dann noch funktioniert, wenn sich die Tabelle ändert.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Patrick

    Ganz gut, wenn jeder Datensatz immer die gleiche Länge hat.
    Ich habe jedoch eine Steinzeitdatei bekommen mit 500+ Einträgen, die zwischen 4 und 6 Zeilen pro Kundendatenbank umfasst.
    Den einzigen Indikator den ich habe, ist eine fortlaufende Nummer zu beginn jedes Datensatzes.
    Ist es möglich, eine Formel dafür zu verwenden oder opfere ich ein Wochenende mit Copy&Paste?

    Gruß, Patrick

    • Avatar-Foto
      Martin Weiß

      Hallo Patrick,

      die Schwierigkeit in Deinem Fall sehe ich vor allem darin: Wenn es unterschiedlich viele Zeilen pro Datensatz gibt, dann existiert offensichtlich auch nicht jedes Feld in jedem Datensatz. Selbst wenn man das Ganze per Formel in eine Zeile mit mehreren Spalten bringen könnte (wofür ich jetzt spontan auch keine Lösung parat habe), hätte somit nicht jede Spalte die gleichen Dateninhalte und Du müsstest wieder von Hand kopieren. Schwierig…

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Heiko

    Hallo Martin,
    danke für das gute Beispiel mit der Variante 2!
    Ich habe nun versucht dieses für den „umgekehrten Weg“ umszubauen. Also Spalten mit INDIREKT in Zeilen dynamisch zu transponieren.
    Leider gelingt es mir nicht.
    Hast du einen Tipp für mich?
    Vielen Dank!
    Heiko

  • Avatar-Foto
    Michl

    hallo,
    ich habe noch ein Problem, meine Spalte hat eine variable Anzahl von Zeilen, in dem Beispiel sind es aber immer genau 6 Zeilen. In meinem Fall hat die Startzeile hat immer einen eindeutigen Teilstring (z.B. „Tel“). Wie wäre dann der Lösungsansatz?
    Ich würde mich über eine Antwort freuen.
    MfG
    Michl

    • Avatar-Foto
      Martin Weiß

      Hallo Michl,

      für Datensätze mit einer variablen Zeilenanzahl habe ich leider keine Lösung parat.

      Schöne Grüße,
      Martin

    • Avatar-Foto
      Julius

      Ich würde hier auf eine Lösung mittels Hilfsspalte gehen. Sinngemäß WENN(LINKS(„Zelladresse eins nach rechts“;3)=“Tel“;“Zelladresse eins drüber“+1;“Zelladresse eins drüber“). Dann bekommen die variabel umfangreichen Datensätze eine fortlaufende Nummerierung, nach der du in einer weiteren Liste abgreifen könntest, etwa mit einer Kombination aus FILTER; MTRANS und einer WENN-Funktion, die ein Array ausgibt, zb WENN(Spalte A=1;Spalte B) eingebettet in die FILTER-Funktion, die alle leeren Zellen rausfiltert (das krieg ich jetzt glaube ich nicht verständlich hin ohne echte Zelladressen 😉 )
      Bliebe aber das Problem, dass die variable Zeilenanzahl auch dafür sorgt, dass die daraus erzeugte Spaltenaufteilung sich von Datensatz zu Datensatz unterscheiden würde

  • Avatar-Foto
    Florian

    Hallo Martin,
    ich habe folgendes Problem.Ich müsste bei einer größeren Datei jeweils 5 Spalten in 5 Zeilen transponieren und in ein neues Tabellenblatt einfügen. Gibt es hierzu eine Möglichkeit z. B. die Funktion ZEILE in SPALTE zu ersetzen nur wie wird dies kopiert?
    Gruß,
    Florian

    • Avatar-Foto
      Martin Weiß

      Hi Jorge,

      Urlaub gerettet? Da soll noch mal einer sagen, Excel wäre zu nichts zu gebrauchen 🙂

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Steffen

    Hallo Martin,

    vielen Dank, da hatte mir der Denkansatz gefehlt.
    Hat super, mit etwas Anpassung da nicht 6 sondern 9 Zeilen 1 Datensatz sind, geklappt die 15471 Zeilen in 9 Spalten zu bringen.
    Werde mir Deine Seite merken und ab und an sicher stöbern wollen, oder müssen.

    Gruss Steffen

  • Avatar-Foto
    Kurt

    Hallo Martin
    Transponieren hat hervorragend geklappt.
    Für den Import ins AX, möchte ich ganze Tabellen transponieren.
    In den Anfragetabellen haben wir 5, 10, 25, 50 Stk und darunter die Preise pro Artikel.

    Nun möchte ich die ganze Tabelle transponieren, so dass ich zb. für die 5 Stk 5 Zeilen bekommen mit der Artikel-Nr und der Beschreibung.
    Gibt es da eine Möchlichkeit

    • Avatar-Foto
      Martin Weiß

      Hallo Kurt,

      ich würde für solche Transformationen mittlerweile Power Query empfehlen. Dort gibt es eine Funktion zum Entpivotieren von Spalten und damit sollte es klappen.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Anna

    DAS war die Lösung für mein obskures Problem (unsortierte Stablisten von Filmen mit je drei Einträgen pro Person sortierbar machen).
    Vielen Dank für das super Tutorial!

    Schöne Grüße,
    Anna

  • Avatar-Foto
    Leider nicht Bruno

    Top, einfach nur Top,
    genau die Lösung die ich gebraucht habe, und so super erklärt! Eine ähnliche Lösung habe ich schon woanders (auf Englisch) gefunden aber nicht verstanden, vorallem die -5 am Ende. Super!

    Vielen Dank!
    L

  • Avatar-Foto
    Jan

    Hallo Martin,

    vielen Dank für deine tollen Tipps!

    Noch faulere Excel-Anwender (wie ich) können auch diese Formel verwenden:
    =INDEX($A$1:$A$42;SPALTE(A$1)+6*ZEILE($A1)-6;)
    Einfach in Zelle D1 eintragen, nach unten und rechts kopieren.
    Hier muss keine Anpassung für die jeweiligen Felder vorgenommen werden.

    Viele Grüße
    Jan

  • Avatar-Foto
    Werner Herkenrath

    Hallo Martin,
    ich hatte in den letzten Jahrten schon mehrfach das gleiche Problem. Ich habe es wie folgt gelöst:
    Die Daten stenen in Spalte „A“. In „B1“ habe ich „=A1“, in C1 „=A2“ in „D1“ „=A3“ usw. geschrieben. Bei 6 Zeilen also bis „G1“ „=A6“. Danach habe ich Die Zellen „B1“ bis „G6“ markiert und nach unten kopiert. Dann einfach das gesamte Blatt kopiert und als Text engefügt, Spalte „A“ gelöscht und dann alles sortiert.

    Mit freundlichen Grüßen
    Werner

  • Avatar-Foto
    Bettina

    Danke! Der Zeilenumbruch löst ein großes Problem für mich, aber alleine hätte ich die Funktion nie gefunden.

  • Avatar-Foto
    Roger

    Guten Tag
    Ich möchte genau das umgekehrte erstellen.
    Aus den Zeilen alles in die Spalte A.
    Ist das auch machbar?
    Werde nächstens auf 365 wechseln!

    Freundliche Grüsse
    Roger

    • Avatar-Foto
      Martin Weiß

      Hallo Roger,

      das geht sogar noch einfacher: Die Tabelle nach Power Query laden, dort dann alle Spalten markieren und im Menü „Transformieren | Spalten entpivotieren“ auswählen. Am Ende kann die Spalte „Attribut“ gelöscht werden, die dann nur die Spaltenüberschriften enthält.

      Schöne Grüße,
      Martin