Tutorials

Excel Trick 56: Mehrere Spalten und Zeilen in eine einzige Spalte umwandeln

Melden

Hier ist ein ausführliches Tutorial im Stil von dptutorials, das dir zeigt, wie du Daten aus mehreren Zeilen und Spalten effizient in einer einzigen Spalte zusammenfasst.

In der Datenverarbeitung kommt es oft vor, dass Daten in einer Matrix (über mehrere Zeilen und Spalten verteilt) vorliegen, man sie aber für eine Pivot-Tabelle oder eine saubere Liste in einer einzigen vertikalen Spalte benötigt.

Hier sind die drei besten Methoden, um dies zu erreichen – von der modernsten Formel bis hin zur klassischen Lösung für ältere Excel-Versionen.

Methode 1: Die „ZUSPALTE“-Funktion (Der schnellste Weg)

Verfügbar in Microsoft 365 und Excel 2021.

Dies ist die modernste und einfachste Methode. Excel hat hierfür eine spezielle Funktion namens ZUSPALTE (englisch: TOCOL).

Schritt-für-Schritt:

  1. Klicke in die Zelle, in der deine neue Spalte beginnen soll.
  2. Gib folgende Formel ein:
    =ZUSPALTE(A2:D10)
    (Ersetze „A2:D10“ durch deinen tatsächlichen Datenbereich).
  3. Drücke Enter.

Zusatz-Tipp: Wenn dein Bereich leere Zellen enthält, kannst du diese ignorieren, indem du ein weiteres Argument hinzufügst:
=ZUSPALTE(A2:D10; 1) (Die "1" steht für "Leere Zellen ignorieren").

Methode 2: Power Query (Die Profi-Lösung für große Datenmengen)

Diese Methode ist ideal, wenn sich deine Quelldaten ständig ändern, da sie auf Knopfdruck aktualisiert werden kann.

  1. Markiere deinen Datenbereich.
  2. Gehe im Menüband auf den Tab Daten und wähle Aus Tabelle/Bereich. (Bestätige die Erstellung einer Tabelle).
  3. Der Power Query-Editor öffnet sich.
  4. Markiere alle Spalten (Halte Strg gedrückt und klicke auf die Spaltenköpfe).
  5. Klicke mit der rechten Maustaste auf einen der Spaltenköpfe und wähle Entpivotieren von Spalten (Unpivot Columns).
  6. Du erhältst nun zwei Spalten: „Attribut“ (den alten Spaltenkopf) und „Wert“.
  7. Klicke mit der rechten Maustaste auf die Spalte „Attribut“ und wähle Entfernen, falls du nur die Werte benötigst.
  8. Klicke oben links auf Schließen & Laden. Deine Daten erscheinen nun in einem neuen Tabellenblatt in einer einzigen Spalte.

Methode 3: Der klassische Formel-Trick (Für ältere Excel-Versionen)

Falls du kein Microsoft 365 nutzt, hilft diese Kombination aus INDEX, KÜRZEN, ZEILE und SPALTE.

Angenommen, deine Daten liegen im Bereich A2:C4 (3 Spalten, 3 Zeilen).

  1. Nutze die folgende Formel in deiner Zielzelle:
    =INDEX($A$2:$C$4; KÜRZEN((ZEILE(A1)-1)/SPALTEN($A$2:$C$4))+1; REST(ZEILE(A1)-1; SPALTEN($A$2:$C$4))+1)
  2. Ziehe die Formel nach unten, bis alle Daten angezeigt werden.

Erklärung der Logik:

  • INDEX: Greift auf den Datenbereich zu.
  • KÜRZEN & ZEILE: Berechnet mathematisch, in welcher Zeile der Matrix Excel gerade suchen muss.
  • REST: Berechnet, in welcher Spalte der Wert steht, und springt nach der letzten Spalte wieder zurück zur ersten.

Zusammenfassung: Welche Methode soll ich wählen?

  • Microsoft 365: Nutze immer ZUSPALTE. Es ist extrem schnell und dynamisch.
  • Wiederkehrende Aufgaben: Nutze Power Query, da du hier nur auf „Aktualisieren“ klicken musst, wenn neue Daten hinzukommen.
  • Alte Excel-Versionen: Nutze die INDEX-Formel, wenn du keine Add-ins oder modernen Funktionen zur Verfügung hast.

Viel Erfolg beim Ausprobieren dieses Excel-Tricks!

0