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

Mehrzeilige Datensätze in einzeilige umwandeln: Zwei 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:

Die Ausgangslage

Zur Veranschaulichung habe ich wieder eine kleine Beispieltabelle vorbereitet, die du bei Bedarf hier herunterladen kannst.
Die Tabelle enthält eine Reihe von Adressdatensätzen. 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

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.

 

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.



Kommentar erstellen

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

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

  • 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