Wie kann man in Excel schnell mehrere Spalten in eine einzige Spalte stapeln?

In Excel ist die Text in Spalten-Funktion dazu gedacht, Daten aus einer einzelnen Spalte mithilfe eines angegebenen Trennzeichens in mehrere Spalten aufzuteilen. Was aber, wenn Sie das Gegenteil tun möchten – Werte aus mehreren Spalten kombinieren oder in eine einzige Spalte stapeln, wie im folgenden Beispiel gezeigt? Dieses Szenario tritt häufig auf, wenn Datensätze zusammengeführt, Informationen für Analysen vorbereitet oder Berichte zur weiteren Verarbeitung formatiert werden. Leider gibt es keine direkte integrierte Excel-Funktion, um Spalten vertikal zu stapeln, aber es gibt mehrere praktische Lösungen, mit denen Sie diese Aufgabe effizient erledigen können.

Mehrere Spalten mit einer Formel in eine stapeln

Mehrere Spalten mit VBA in eine stapeln

Mehrere Spalten mit „Transform Range“ in eine stapeln

Mehrere Spalten mit Power Query in eine stapeln

Mehrere Spalten mit einer Formel in eine stapeln

Wenn Sie keine Makros oder Add-ins verwenden möchten, können Sie mehrere Spalten mit einer Array-Formel unter Verwendung der INDEX-Funktion in eine einzige Spalte stapeln. Dieser Ansatz eignet sich für dynamische Datensätze oder Situationen, in denen Sie manuelle Operationen vermeiden möchten. Ein Vorteil ist, dass die Formel die Ergebnisse automatisch aktualisiert, wenn sich Ihre Quelldaten ändern, aber Sie müssen bei Ihren Bereichsnamen präzise sein und vermeiden, Zellen innerhalb des referenzierten Bereichs einzufügen oder zu löschen.

1. Wählen Sie den Datenbereich aus, den Sie stapeln möchten (z. B. A1:C4), dann klicken Sie in die Namensbox (links von der Formelleiste), geben Sie einen aussagekräftigen Namen ein, z. B. MyData, und drücken Sie Eingabetaste. Dadurch benennen Sie Ihren Bereich für eine einfache Referenz.

2. Klicken Sie auf eine leere Zelle, wo die gestapelte Spalte beginnen soll – normalerweise unterhalb Ihrer Daten oder in einem anderen Tabellenblatt. Fügen Sie diese Formel in die ausgewählte Zelle ein:

=INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1)

3. Drücken Sie Eingabetaste zur Bestätigung, dann ziehen Sie den Ausfüllkursor nach unten, bis Sie einen #BEZUG! oder einen ähnlichen Fehler sehen, was darauf hinweist, dass alle Daten aufgelistet wurden. Löschen Sie die Fehlerzelle bei Bedarf.

In dieser Formel bezieht sich MyData auf den Bereich, den Sie in Schritt 1 definiert haben. COLUMNS(MyData) passt sich automatisch an die Anzahl der Spalten in Ihren Daten an. Stellen Sie sicher, dass Sie keine Spalten innerhalb von MyData einfügen oder löschen, nachdem Sie die Formel eingegeben haben, da dies die Ergebnisse beeinträchtigen kann. Wenn Ihre Daten leere Zellen enthalten, werden auch diese gestapelt – Sie können sie nachträglich herausfiltern, falls nötig. Bei großen Bereichen kann das Ziehen des Ausfüllkursors Zeit in Anspruch nehmen; erwägen Sie, den Ausfüllcursor doppelt zu klicken, wenn die Spalte links vollständig gefüllt ist.

Falls Ihre Excel-Version dynamische Arrays unterstützt (Excel365 oder Excel2021 und höher), können Sie versuchen:

=TOCOL(MyData,1)

Diese Formel ist nur in neueren Excel-Versionen verfügbar und stapelt die Spalten sofort in einer einzigen Spalte.

Mehrere Spalten mit VBA in eine stapeln

Wenn Sie regelmäßig Spalten stapeln müssen oder mehr Kontrolle wünschen, kann ein VBA-Makro diesen Prozess mit nur wenigen Klicks automatisieren. VBA ist ideal für fortgeschrittene Benutzer, die große Datensätze oder repetitive Aufgaben verarbeiten. Denken Sie jedoch daran, dass das Aktivieren von Makros ein Sicherheitsrisiko darstellen kann, wenn Sie sich über die Quelle des Makros nicht sicher sind. Speichern Sie Ihre Datei immer vorher und seien Sie vorsichtig beim Ausführen unbekannten Codes.

1. Drücken Sie Alt + F11, um das Microsoft Visual Basic for Applications-Fenster zu öffnen.

2. Klicken Sie im VBA-Fenster auf Einfügen > Modul. Kopieren und fügen Sie den folgenden Code in das neue Modul ein:

VBA: Spalten zu einer stapeln

Sub ConvertRangeToColumn()
'UpdatebyExtendoffice
Dim Range1 As Range, Range2 As Range, Rng As Range
Dim rowIndex As Integer
xTitleId = "KutoolsforExcel"
Set Range1 = Application.Selection
Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8)
Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8)
rowIndex = 0
Application.ScreenUpdating = False
For Each Rng In Range1.Rows
    Rng.Copy
    Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    rowIndex = rowIndex + Rng.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

3. Um das Makro auszuführen, drücken Sie F5, oder klicken Sie auf die Schaltfläche Ausführen. Nach dem Ausführen erscheint eine Eingabeaufforderung – wählen Sie den Datenbereich aus, den Sie stapeln möchten, und klicken Sie auf OK.

4. Sie werden dann aufgefordert, eine Zielzelle für das Ergebnis auszuwählen. Klicken Sie OK erneut. Die Spalten werden ab der Zielzelle in einer einzigen Spalte gestapelt.

Wenn Sie einen Fehler erhalten, überprüfen Sie den ausgewählten Bereich erneut und stellen Sie sicher, dass keine geschützten Blätter oder verbundene Zellen vorhanden sind. Für weitere fortgeschrittene Anpassungen, wie das Überspringen leerer Zellen oder das Stapeln bestimmter Spalten, kann der Code weiter modifiziert werden. Denken Sie daran, Ihre Arbeit vor dem Ausführen des VBA-Codes zu speichern, um unerwünschten Datenverlust zu vermeiden.

Mehrere Spalten mit „Transform Range“ in eine stapeln

Wenn Sie eine schnelle und benutzerfreundliche Lösung bevorzugen, ohne Formeln oder Code schreiben zu müssen, können Sie die Transform Range-Funktion von Kutools für Excel verwenden. Diese Funktion ist besonders hilfreich für Benutzer, die Daten schnell über eine intuitive Oberfläche verarbeiten möchten, wie Büroadministratoren oder solche, die routinemäßig multidimensionale Daten verarbeiten. Mit Kutools wird das Risiko von Formelfehlern minimiert und viel Zeit gespart, obwohl die Installation des Add-ins erforderlich ist.

Nach der kostenlosen Installation von Kutools für Excel gehen Sie wie folgt vor:

1. Wählen Sie die Spalten oder den Datenbereich aus, den Sie in eine einzige Spalte stapeln möchten. Dann klicken Sie auf Kutools > Bereich > Transform Range um den Dialog zu öffnen.

2. Wählen Sie im Transform Range Dialogfeld die Option Bereich zu einzelner Spalte und klicken Sie auf OK. Wählen Sie als Nächstes eine Zelle aus, in der das Ergebnis erscheinen soll.

3. Klicken Sie abschließend auf OK. Die ausgewählten Spalten werden sofort in einer einzigen Spalte an Ihrem gewählten Ort gestapelt.

Diese Methode ist einfach und fehlerfrei. Denken Sie daran, dass Sie die Änderung leicht rückgängig machen können (Strg + Z), falls etwas nicht richtig aussieht, oder Transform Range bei Bedarf für andere Datensätze erneut ausführen können. Wenn Ihre Daten verbundene Zellen enthalten, ist es am besten, diese vor der Verwendung dieser Funktion aufzuteilen, um unerwartete Ergebnisse zu vermeiden.

Mehrere Spalten mit Power Query in eine stapeln

Power Query (auch Get & Transform genannt in einigen Excel-Versionen) ist eine weitere praktische Möglichkeit, mehrere Spalten in eine einzige zu stapeln, insbesondere für Benutzer, die mit großen Datenmengen arbeiten oder wiederkehrende Datentransformationsaufgaben automatisieren müssen. Power Query ist in Excel 2016 und höher integriert und als kostenloses Add-in für Excel 2010 und 2013 verfügbar. Es ist am nützlichsten für fortgeschrittene Datenaufbereitung und kann komplexe Umstrukturierungsoperationen mit einer einfachen Benutzeroberfläche problemlos verarbeiten. Allerdings gibt es eine kurze Lernkurve für Erstanwender.

Laden Sie Ihre Quelltabelle in Power Query. Wählen Sie Ihre Daten in Excel aus. Gehen Sie zu Daten > Aus Tabelle/Bereich, um den Power Query-Editor zu öffnen.

2. Wählen Sie im Abfragevorschau alle Spalten aus. Gehen Sie zur Registerkarte Transformieren, finden Sie die Tabellengruppe und klicken Sie auf Transponieren. Dies vertauscht die Zeilen und Spalten.

3. Wählen Sie erneut alle transponierten Spalten aus. Klicken Sie auf der Registerkarte Transformieren unter der Gruppe Jede Spalte auf Entpivotieren von Spalten.

4. Entfernen Sie die Spalte „Attribut“, falls sie nicht benötigt wird. Klicken Sie mit der rechten Maustaste auf die Spaltenüberschrift „Attribut“ > Wählen Sie Entfernen.

5. Klicken Sie auf Schließen & Laden, um das Ergebnis als neues Arbeitsblatt oder als Tabelle in Excel zu laden. Sie können die Transformation jederzeit aktualisieren oder erneut anwenden, wenn sich Ihre Quelldaten ändern.

Mit Power Query können Sie diesen Stapelprozess als wiederverwendbare Abfrage speichern und die Ergebnisse schnell aktualisieren, wenn sich Ihre Daten in Zukunft ändern.

Hinweis: Mit diesem Ansatz werden möglicherweise die ursprünglichen Spaltenüberschriften aus den Quelldaten nicht erhalten.

Zusammenfassend hat jede dieser Methoden ihre Stärken: Formeln eignen sich gut für dynamisch aktualisierte Daten, VBA ist hervorragend zur Automatisierung repetitiver Aufgaben, Kutools für Excel ermöglicht per Mausklick bedienbare Operationen mit wenig technischem Wissen, und Power Query ist ideal für fortgeschrittene oder wiederkehrende Transformationen. Überprüfen Sie Ihre gestapelten Daten immer auf verbleibende Leerstellen, verbundene Zellen oder Formatierungsfehler nach der Verarbeitung. Wenn Fehler oder unerwartete Ergebnisse auftreten, überprüfen Sie Ihre definierten Bereiche oder Schrittauswahlen. Die Kombination dieser Methoden hilft Ihnen, Ihre Excel-Daten effizient für Berichts- und Analysezwecke zu verwalten und umzugestalten.