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
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
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…
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.
Falls du mit einer Excel-Version vor 2013 arbeitest, findest du die Transponieren-Funktion unter „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
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
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 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
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
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
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
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
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 🙂
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.
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
Hallo Jonny,
Kreuztabelle klingt nach Pivot-Tabelle. Hast Du es damit schon mal versucht? Falls Dir Pivot-Tabellen nicht geheuer sind, empfehle ich Dir meinen Einführungsartikel dazu:
https://www.tabellenexperte.de/pivot-tabellen/
Vielleicht hilft das ja weiter.
Schöne Grüße,
Martin
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 =)
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?
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
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
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
…sehr eleganter Trick!
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
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
Hallo Michl,
für Datensätze mit einer variablen Zeilenanzahl habe ich leider keine Lösung parat.
Schöne Grüße,
Martin
Nicht einfach mit einer Formel, dafür aber prädestiniert für VBA (Repeat … until), würde ich meinen …
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
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
topp wertvoll – hat meinen Urlaub gerettet!!
Hi Jorge,
Urlaub gerettet? Da soll noch mal einer sagen, Excel wäre zu nichts zu gebrauchen 🙂
Schöne Grüße,
Martin
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
Hallo Steffen,
freut mich, wenn der Tipp weitergeholfen hat.
Schöne Grüße,
Martin
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
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
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
Hallo Anna,
freut mich sehr zu hören, dass der Artikel weitergeholfen hat!
Schöne Grüße,
Martin
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
Vielen Dank für’s Feedback, freut mich zu hören!
Schöne Grüße,
Martin
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
Hallo Jan,
vielen Dank für die alternative Lösung!
Schöne Grüße,
Martin
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
Hallo Werner,
klar, das ist auch eine praktikable Lösung. Vielen Dank dafür!
Schöne Grüße,
Martin
Hallo Martin, interessant wäre auch eine Variante mit Power Query gewesen 🙂
Hallo Marcel,
gute Idee, vielleicht kommt da mal ein Artikel 😉
Schöne Grüße,
Martin
Danke! Der Zeilenumbruch löst ein großes Problem für mich, aber alleine hätte ich die Funktion nie gefunden.
Hi Bettina,
freut mich zu hören!
Schöne Grüße,
Martin
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
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
Hallo Martin,
ich bin so begeistert von deiner Variante 3 zum Transponieren, dass ich dir das unbedingt mitteilen möchte. Ich hatte schon befürchet, ich müsse mehrere Funktionen ineinander schachteln, um genau so eine Tabelle, wie du sie als Vorlage hattest, vernünftig dargestellt zu kriegen. Dabei sind es tatsächlich nur wenige Mausklicks. Echt super, ganz herzlichen Dank für deine Beschreibung.
Viele Grüße
Annette
Hallo Annette,
vielen Dank für dein tolles Feedback, es freut mich immer ganz besonders, wenn meine Tipps konkret weiterhelfen können.
Schöne Grüße,
Martin