VSTAPELN, HSTAPELN, ZUSPALTE und ZUZEILE: Daten mit einer sich selbst aktualisierenden Formel kombinieren und umformen und wissen, wann Power Query besser ist

FAQ — Welche Fragen werden in diesem Artikel beantwortet?

Q: Welche Leistung erbringen diese vier Funktionen?

A: Sie teilen sich eine Aufgabe, das Kombinieren von Daten, in zwei auf. Die Befehle VSTAPELN und HSTAPELN machen zwei verschiedene Dinge: Der eine stapelt Blöcke übereinander, der andere setzt sie nebeneinander. Mit den Funktionen ZUSPALTE und ZUZEILE kannst du einen rechteckigen Block in eine einzelne Spalte oder Zeile umwandeln. Jede davon hat so einen dynamischen Bereich, der sich jedes Mal neu berechnet, wenn sich die Quelle ändert.

Q: Was ist der Unterschied zum Kopieren oder einer Hilfsspalte?

A: Wenn du etwas kopierst, entsteht eine statische Momentaufnahme. Die ist veraltet, sobald sich die Quelle ändert. Eine Hilfsspalte mit =Blatt2!B2-Bezügen ist auch nicht so toll. Sie ist anfällig und bricht, sobald du eine Zeile einfügst. Stattdessen werden die Live-Bereiche gelesen, sodass das Ergebnis immer aktuell ist und nie neu aufgebaut werden muss.

Q: Kann ich wirklich zwölf Monatsblätter mit einer Formel zusammenführen?

A: Ja, VSTAPELN(Jan; Feb; Mrz; …) stapelt jeden Monatsbereich zu einer langen Tabelle. Du musst nur das Blatt des nächsten Monats hinzufügen, die Formel erweitern und schon wächst die konsolidierte Tabelle von selbst, ganz ohne Kopieren, Einfügen oder Makro.

Q: Für welche Excel-Versionen sind die verfügbar?

A: Alle diese Funktionen gibt's in Microsoft 365, in Excel für das Web und, anders als GRUPPIERENNACH und PIVOTMIT, im dauerhaften Excel 2024 (Windows und Mac). In Excel 2021, 2019 und 2016 sind sie nicht enthalten. Stattdessen wird die Fehlermeldung #NAME? angezeigt.

Q: Wann sollte ich stattdessen zu Power Query greifen?

A: Wenn du viele einzelne Dateien oder einen ganzen Ordner zusammenführen willst, wenn die Quellen vor dem Verbinden bereinigt werden müssen, wenn die Daten groß sind oder planmäßig aktualisiert werden sollen, oder wenn du über einen Schlüssel verknüpfen statt nur zu stapeln willst. Teil 8 zieht die Grenze.

Jahrelang war das „Daten kombinieren" in Excel eine von wenigen manuellen Fleißaufgaben: einen Bereich kopieren, unter den letzten einfügen und das jeden Monat wiederholen; eine Hilfsspalte aus blattübergreifenden Bezügen bauen, um Werte nebeneinanderzustellen; oder einen rechteckigen Block von Hand zu einer einzelnen Liste umzuformen. Jede dieser Methoden funktioniert einmal. Keine davon hält, was sie verspricht. Die Kopie ist bei der nächsten Eingabe veraltet, die Hilfsspalte bricht, sobald eine Zeile verrutscht, und die manuelle Umformung muss bei jedem Wachstum der Quelle von Grund auf wiederholt werden.

Eine Funktionsfamilie, die 2022 eingeführt wurde, hat das jetzt geändert. Du kannst VSTAPELN, HSTAPELN, ZUSPALTE und ZUZEILE zusammen mit den Begleitfunktionen ÜBERNEHMEN, WEGLASSEN, ERWEITERN, ZEILENWAHL und SPALTENWAHL dazu verwenden, Formeln zu erstellen. Wie auch bei den Funktionen FILTER und SORTIEREN gibt es hier einen dynamischen Bereich, der sich automatisch aktualisiert. In dem Artikel geht es einerseits um eine veränderte Herangehensweise und andererseits um neun Funktionen. Und es ist auch wichtig zu wissen, wo die Herangehensweise aufhört und Power Query anfängt.

Teil 1: Eine Familie mit zwei Aufgaben — zusammenfügen und umformen

Es ist hilfreich, sich die ganze Familie vorzustellen, denn jede Funktion ist wie ein kleines Werkzeug mit einem Zweck, das man mit den anderen kombinieren kann.

Es gibt zwei Funktionen, die einzelne Bereiche zu einem größeren zusammenfügen: VSTAPELN stapelt sie senkrecht und HSTAPELN setzt sie waagerecht nebeneinander. Es gibt zwei Funktionen, mit denen du einen Block in eine Linie umformen kannst. Mit ZUSPALTE wird er in eine Spalte gefüllt und mit ZUZEILE als Zeile ausgelegt. Die anderen fünf kürzen und wählen aus. Sie verfeinern, was die ersten vier erzeugen. Sie behalten oder entfernen den Rand, füllen einen Block auf eine feste Größe auf oder wählen bestimmte Zeilen und Spalten aus und ordnen sie neu.

Die Bedeutung ist derselbe rote Faden, der sich durch das moderne Excel-Werkzeug zieht. Ein kombinierter Datensatz kann jetzt ein lebendiges Formelergebnis sein, statt nur eine eingefügte Momentaufnahme. Genau diese eine Änderung macht die Funktionen lernenswert und sie ist auch der Grund, warum die abschließende Frage dieses Artikels nicht „wie", sondern „wann" lautet, denn eine Formel ist nicht immer das richtige Werkzeug für die Aufgabe.

Teil 2: Die Daten, mit denen wir arbeiten

Die folgenden Beispiele verwenden drei kleine Monatsbereiche, jeweils als Excel-Tabelle mit dem Namen Jan, Feb und Mrz definiert.

Es gibt noch zwei weitere Bereiche, die die waagerechten Beispiele unterstützen: eine einzelne Spalte mit den Region-Bezeichnungen und zwei getrennte Spalten mit Jahreswerten, J2025 (30.000; 18.000; 12.000) und J2026 (36.600; 20.550; 22.200). Letztere ist der Dreimonatswert jeder Region. Die drei Monatstabellen summieren sich auf 79.350. Jedes Ergebnis unten stimmt mit diesem Wert überein.

💡 Tipp: Wandle jeden Bereich mit Strg+T in eine Tabelle um und gib ihm einen klaren Namen. VSTAPELN(Jan; Feb; Mrz) ist viel lesbarer und wartbarer als eine Formel voller A2:B4-Adressen. Außerdem nimmt jede Tabelle neue Zeilen automatisch auf. Die Begründung findest du unter Warum jeder Datensatz eine Excel-Tabelle sein sollte.

Teil 3: VSTAPELN, Monatsdaten mit einer Formel konsolidieren

Das ist der Anwendungsfall, der die ganze Familie rechtfertigt: die wiederkehrende Monatskonsolidierung, die so viele Arbeitsmappen noch von Hand erledigen.

Die Syntax ist so einfach, wie sie aussieht. Du bekommst eine Liste der Bereiche, die du stapeln musst.

=VSTAPELN(Matrix1; [Matrix2]; ...)

Um die drei Monate zu einer Tabelle zusammenzuführen:

=VSTAPELN(Jan; Feb; Mrz)

Am Ende ist ein einzelner Bereich mit neun Zeilen, der in das Gitter überläuft. Du kannst nichts kopieren oder einfügen, und das ist echt wichtig, er ist lebendig. Wenn du im Februar den Süd-Umsatz hinzufügst, aktualisiert sich die kombinierte Tabelle sofort. Wenn du nächsten Monat die Tabelle Apr hinzufügst, erweiterst du die Formel einfach zu =VSTAPELN(Jan; Feb; Mrz; Apr). Das ist der moderne Ersatz für die Kopieren-und-Einfügen-Konsolidierung aus Daten aus mehreren Arbeitsblättern in einer zusammenführen.

Wenn du eine Kopfzeile einmalig oben einschließen willst, dann stapel einfach den Kopfbereich mit den Datenkörpern. Da ein Tabellenbezug seine Kopfzeile zurückgeben kann, liest sich das sauber:

=VSTAPELN(Jan[#Alle]; Feb; Mrz)     ' Kopf und Daten von Jan, dann die Datenkörper von Feb und Mrz

Es ist wichtig, dass die Bereiche dieselbe Spaltenzahl haben. Wenn das nicht so ist, macht VSTAPELN die schmaleren mit #NV auf die größte Breite auf, anstatt einen Fehler zu liefern. Das ist gut zu wissen und ein Hinweis darauf, dass Ihre Quellformen auseinandergedriftet sind.

Teil 4: HSTAPELN, Spalten ohne Hilfsspalte verbinden

Wenn VSTAPELN nach unten gebaut wird, dann wird HSTAPELN in die Breite gebaut. Mit diesem Tool kannst du aus einzelnen Spalten, die auf dem Blatt verteilt sind, eine Tabelle erstellen. Das geht ganz ohne die Hilfsspalte, die du früher brauchst.

=HSTAPELN(Matrix1; [Matrix2]; ...)

Um die Regionsbezeichnungen und die beiden Jahreswerte nebeneinanderzustellen:

=HSTAPELN(Region; J2025; J2026)

Aus den drei Bereichen wird eine lebendige, dreispaltige Tabelle. Der alte Weg, eine neben den Daten heruntergezogene Hilfsspalte aus =Blatt2!B2-Bezügen oder ein manuelles Einfügen, funktioniert nicht mehr. Es gibt nichts mehr zu ziehen und nichts, was man synchron halten müsste.

HSTAPELN füllt auf die höchste Zeilenzahl auf. Wenn die Teile unterschiedlich hoch sind, werden die kürzeren mit #NV gefüllt. Damit die Lücken sauber dargestellt werden, schreib einfach WENNNV um die Formel.

=WENNNV(HSTAPELN(Region; J2025; J2026); "")

Die Mechanik, mit der wir diese Fehlerwerte aufräumen, wird hier erklärt. Umgang mit Fehlermeldungen mit WENNFEHLER und WENNNV.

Teil 5: ZUSPALTE und ZUZEILE, einen Block in eine Linie umformen

Die zweite Aufgabe ist das Umformen. Ein rechteckiger Wertblock, zum Beispiel eine Region-mal-Monat-Matrix, muss oft zu einer einzelnen Liste werden. Das kann zum Beispiel sein, um EINDEUTIG zu speisen, zu sortieren oder ein Diagramm anzutreiben. Das können ZUSPALTE und ZUZEILE genau.

=ZUSPALTE(Matrix; [ignorieren]; [nach_Spalte_scannen])
=ZUZEILE(Matrix; [ignorieren]; [nach_Spalte_scannen])

Für die Neun-Zellen-Matrix der Monatswerte:

=ZUSPALTE(Matrix)     ' alle neun Werte in einer Spalte, zeilenweise gelesen
=ZUZEILE(Matrix)      ' dieselben neun Werte in einer Zeile

Zwei optionale Argumente machen sie ziemlich präzise. Mit der Funktion ignorieren kannst du festlegen, welche Informationen übersprungen werden. Wenn du 0 verwendest, wird alles behalten (Standard). Mit 1 werden Leerzellen ignoriert und mit 2 werden Fehler ignoriert. Mit 3 werden beides ignoriert. Das ist echt praktisch, wenn du einen Block mit Lücken umformst. Die Funktion nach_Spalte_scannen bestimmt, in welche Richtung gescannt wird. Standardmäßig werden beide Zeilen gelesen, aber mit WAHR wird spaltenweise gelesen.

Der Vorteil im Alltag liegt in der Verschachtelung. Wenn du aus einem rechteckigen Block in einer Formel eine saubere, sortierte Liste eindeutiger Werte machen willst:

=SORTIEREN(EINDEUTIG(ZUSPALTE(Matrix; 1)))     ' umformen, Leerzellen ignorieren, entdoppeln, sortieren

Wenn du eine Spalte mit Einträgen hast, kannst du sie ganz einfach in eine Kopfzeile verwandeln. Dafür benutzt du die Funktion ZUZEILE. Die Funktionen ZEILENUMBRUCH und SPALTENUMBRUCH sind dafür da, eine einzelne Liste wieder in einen rechteckigen Block mit einer bestimmten Breite oder Höhe zu falten. Das Überlaufverhalten, auf das das alles aufbaut, wird hier erklärt Dynamische Bereiche mit FILTER, SORTIEREN, EINDEUTIG und SEQUENZ.

Teil 6: Die Begleitfunktionen zum Kürzen und Auswählen

Wenn du etwas zusammenfügst und umformst, ist das erst die halbe Arbeit. Die andere Hälfte ist, genau die Zeilen und Spalten zu zeigen, die du haben möchtest. Das machen fünf Begleitfunktionen, sie machen aus einer groben Kombination eine fertige Tabelle.

Zwei davon verdienen ein Wort. Wenn du eine störende Summenzeile vor dem Stapeln entfernen willst, verwende die negative Zahl für WEGLASSEN. Wenn du also die Januar-Tabelle ohne ihre letzte Zeile zurückhaben willst, tippe einfach =WEGLASSEN(Jan; -1). Wenn die Daten uneinheitlich sind, hilft das Befehl ERWEITERN. Wenn in einem Monat eine Region fehlt, sorgt die feste Form mit ERWEITERN(Block; 3; 2; 0) beim Stapeln dafür, dass alles bündig bleibt. Und weil SPALTENWAHL und ZEILENWAHL Indizes in beliebiger Reihenfolge annehmen, auch negative, die vom Ende zählen, dienen sie zugleich als Werkzeug zum Umordnen, nicht nur zum Filtern.

Teil 7: Alles zusammen eine sich selbst aktualisierende Konsolidierung

Die Funktionen sind so gestaltet, dass sie aufeinander aufbauen: Die innere Funktion gibt ihr Ergebnis an die äußere Funktion weiter. Das erledigt jetzt eine einzige Zeile Code, die früher eine ganze Reihe manueller Schritte erforderte.

Nehmen wir mal an, du willst die drei Monate konsolidiert, aber mit den Spalten in der Reihenfolge Umsatz; Region statt Region; Umsatz.

=SPALTENWAHL(VSTAPELN(Jan; Feb; Mrz); 2; 1)

Wenn du von innen nach außen liest, sieht das so aus: VSTAPELN stapelt die drei Monate zu einem Bereich zusammen. Dann gibt es SPALTENWAHL, und die Spalte 2 wird zurückgegeben, gefolgt von Spalte 1. Oder, um die fünf größten Umsätze des ganzen Quartals sortiert auszugeben:

=ÜBERNEHMEN(SORTIEREN(VSTAPELN(Jan; Feb; Mrz); 2; -1); 5)

Hier konsolidiert VSTAPELN, SORTIEREN ordnet den kombinierten Bereich absteigend nach Umsatz und ÜBERNEHMEN behält die fünf obersten. Alles daran ist lebendig: Wenn du einen Wert in irgendeiner Monatstabelle änderst, ordnet sich die Top-Fünf-Liste neu. Wenn ein verschachtelter Ausdruck so lang wird, dass er schwer lesbar ist, kannst du mit LET jede Stufe benennen. Weitere Infos dazu findest du unter LET: übersichtlichere, schnellere Formeln..

Teil 8: Die richtige Technik wählen

Das ist die Entscheidung, auf die der Artikel hingearbeitet hat. Die Funktionen sind echt super, aber sie lösen nicht jedes Kombinationsproblem. Man muss auch wissen, wann man sie einsetzen sollte..

Wenn die Quellen in derselben Arbeitsmappe liegen, das Ergebnis live bleiben und sich selbst aktualisieren soll, es eine weitere Formel speist, eine Dashboard-Kennzahl, ein Diagramm, eine weitere Berechnung und die Formen einfach und einheitlich sind, dann ist der Formelweg die richtige Wahl. Das Ergebnis ist eine Formel in einer Zelle, die sich in eine geprüfte und nachvollziehbare Arbeitsmappe einfügt.

Power Query ist die echte Alternative und das richtige Werkzeug, wenn die Aufgabe einer Formel entwächst. Damit kannst du zum Beispiel viele einzelne Dateien oder einen ganzen Ordner mit Monatsexporten zusammenführen. Du kannst auch Quellen vor dem Verbinden bereinigen oder umformen, mit großen Daten oder einer planmäßigen Aktualisierung arbeiten oder Tabellen über einen Schlüssel verknüpfen statt sie nur zu stapeln. Wenn diese Formeln schon saubere, geöffnete Bereiche zusammenfügen, dann macht Power Query daraus eine wiederholbare Pipeline. Die zieht die unorganisierten externen Daten zuerst ein und bereinigt sie. Auf der Seite "Vorbereitungsseite" erklären wir, wie man dreckige Datenbestände bereinigt.Wie man schmutzige Datenbestände bereinigt, die Skalierungsseite Power Pivot und das Datenmodell. Und Kopieren und Einfügen bleibt für einen echten Einzelfall völlig in Ordnung: Eine Handvoll Zeilen, die sich nie wieder ändern, braucht überhaupt keine Formel.

Was die Verfügbarkeit angeht, hat diese Familie eine erfreulichere Geschichte als GRUPPIERENNACH und PIVOTMIT. Alle neun Funktionen findest du in Microsoft 365, in Excel für das Web und im dauerhaften Excel 2024 unter Windows und Mac. Die fehlen nur in Excel 2021, 2019 und 2016. Da liefern sie #NAME?. Wenn du deine Arbeitsmappe in einer älteren Version öffnen musst, kannst du entweder Power Query benutzen oder alles manuell konsolidieren. Die vollständige Versionsbild findest du unter Excel 365 im Vergleich zu älteren Versionen.

Teil 9: Häufige Fehlerbilder

Zusammenfassung

Diese neun Funktionen machen das Kombinieren und Umformen von Daten zu einer Formelaufgabe statt zu einer manuellen und darin liegt ihr eigentlicher Wert.

• VSTAPELN stapelt Bereiche senkrecht; HSTAPELN verbindet sie waagerecht. Beide liefern einen lebendigen, überlaufenden Bereich, der mit der Quelle wächst.
• ZUSPALTE und ZUZEILE formen einen Block in eine einzelne Spalte oder Zeile um; die Argumente ignorieren und nach_Spalte_scannen machen das Ergebnis präzise.
• ÜBERNEHMEN, WEGLASSEN, ERWEITERN, ZEILENWAHL und SPALTENWAHL kürzen, füllen, wählen und ordnen — sie machen aus einer groben Kombination eine fertige Tabelle.
• Verschachtelt ersetzen sie die Kopieren-Konsolidierung, Hilfsspalten und manuelles Umformen durch einen einzigen, sich selbst aktualisierenden Ausdruck.
• Greifen Sie zu diesen Formeln, wenn die Quellen in der Mappe liegen und das Ergebnis live bleiben soll; greifen Sie zu Power Query, wenn die Daten extern sind, bereinigt werden müssen, groß sind oder über einen Schlüssel verknüpft werden.
• Verfügbar sind sie in Microsoft 365, Excel für das Web und dem dauerhaften Excel 2024, aber nicht in Excel 2021 oder älter, wo sie #NAME? liefern.

Das Prinzip ist easy: Nimm das Werkzeug, das das Ergebnis korrekt und selbsttragend macht.Entscheide bewusst, ab welchem Punkt Umfang oder Unsauberkeit der Daten für Power Query sprechen.

Siehe auch: Daten aus mehreren Arbeitsblättern in einer zusammenführen für das Konsolidierungsproblem, das diese Funktionen lösen, Dynamische Bereiche mit FILTER, SORTIEREN, EINDEUTIG und SEQUENZ für das Überlaufverhalten, auf das sie aufbauen, Excel-Berichte mit dynamischen Matrizen und Tabellen automatisieren für das Muster des sich selbst aktualisierenden Berichts, Umgang mit Fehlermeldungen mit WENNFEHLER und WENNNV zum Aufräumen der #NV-Auffüllung, Warum jeder Datensatz eine Excel-Tabelle sein sollte für die strukturierten Bezüge, die diese Formeln stabil halten, und Excel 365 im Vergleich zu älteren Versionen für das vollständige Verfügbarkeitsbild.

Dieser Artikel ist Teil der helpme.safeoffice.de-Reihe, die praxisnahe Anleitungen zu Excel-Funktionen, Arbeitsmappen-Modellierung und Datenlösungen bietet. Die Reihe richtet sich an Unternehmen, die wirksame, wartbare Werkzeuge möchten, die jeder verstehen kann.