FAQ — Welche Fragen werden in diesem Artikel beantwortet?

Wie lassen sich Daten aus mehreren Arbeitsblättern in Excel 365 am einfachsten zusammenführen?

A: Verwende die Funktion VSTAPELN. Die Stapel stapeln Arbeitsblätter nach Bereichen. Mit einer Formel wie zum Beispiel =VSTAPELN(Jan!A2:D50; Feb!A2:D50; Mar!A2:D50) können Sie drei Monatsblätter zu einer Tabelle zusammenführen. Wenn du eines der Quellblätter änderst, wird das Ergebnis automatisch aktualisiert.

Was wurde vor VSTAPELN verwendet? Und warum war das mühsam?

A: Meistens geht es um Kopieren und Einfügen, Power Query, VBA-Makros oder die INDIREKT-Funktion. Aber Vorsicht: Kopieren und Einfügen führt leicht zu Fehlern. Und das muss man auch jedes Mal wiederholen, wenn sich die Daten ändern. Die INDIRECT-Funktion funktioniert, ist aber etwas unbeständig. Jedes Mal, wenn du die Datei änderst, wird sie neu berechnet. Das kann bei großen Arbeitsmappen ziemlich lange dauern. Power Query ist echt super, aber man muss es aktualisieren. Und es dauert eine Weile, bis man sich eingearbeitet hat. VSTAPELN macht das alles überflüssig. Du brauchst nur eine Formel und schon bist du fertig. Wir raten davon ab, volatile Funktionen wie INDIREKT zu verwenden.

Kann ich das zusammengeführte Ergebnis filtern oder sortieren?

A: Ja, VSTAPELN liefert einen Überlaufbereich, den du dann in SORTIEREN oder FILTER einbinden kannst. Die Formel =SORTIEREN(VSTAPELN(Jan!A2:D50, Feb!A2:D50), 1) sortiert zum Beispiel alle Zeilen aus beiden Blättern nach Spalte 1 aus. Du kannst in einer Formel zusammenführen, filtern und sortieren.

Wird das Ergebnis aktualisiert, wenn ich einem Quellblatt Zeilen hinzufüge?

A: Ja, wenn deine Quellbereiche Excel-Tabellen sind. Eine Tabellenreferenz wie Jan_Data[#Data] wächst automatisch mit, wenn du Zeilen hinzufügst. Wenn du aber einen festen Bereich wie A2:D50 verwendest, musst du diesen selbst erweitern oder einen größeren Bereich verwenden und die leeren Zellen herausfiltern.

Was ist der Unterschied zwischen VSTAPELN und HSTAPELN?

A: VSTAPELN hat einen Überlaufbereich, den du mit SORTIEREN oder FILTER nutzen kannst. Die Formel =SORTIEREN(VSTAPELN(Jan!A2:D50, Feb!A2:D50, 1)) zum Beispiel sortiert alle Zeilen aus beiden Blättern nach Spalte 1. Das ist praktisch, weil man so die Funktionen Filtern und Sortieren in einer einzigen Formel kombiniert.

Früher war das Zusammenführen von Daten aus mehreren Arbeitsblättern eine echte Qual. Dynamische Arrays haben dem ein Ende gesetzt.

Jedes Team hat das gleiche Problem. Die Daten für Januar findest du auf einem Blatt. Die Daten für Februar liegen auf einem anderen Blatt, die für März auf einem dritten und so weiter. Die Daten für März findest du auf einem weiteren Blatt. Das Übersichtsblatt braucht aber alle Daten an einem Ort. Jahrelang mussten wir uns damit abfinden, dass das Kopieren und Einfügen mühsam war, dass wir ein Makro verwenden oder eine unübersichtliche INDIREKT-Formel erstellen mussten. Keine dieser Lösungen wurde automatisch aktualisiert. Jedes Mal, wenn neue Daten dazu kamen, musste man manuell eingreifen.

Das hat sich mit VSTAPELN und HSTAPELN geändert. Die wurden in Excel 365 eingeführt. Zusammen mit dem Überlaufverhalten einer dynamischen Matrix können sie Daten aus beliebig vielen Arbeitsblättern in eine Live-Tabelle zusammenführen. Eine Zelle. Eine Formel. Du bleibst immer auf dem neuesten Stand.

In diesem Artikel erklären wir dir genau, wie es Schritt für Schritt geht. Er erklärt die alten Methoden und warum die nicht reichen. Hier erfährst du, wie VSTAPELN und HSTAPELN funktionieren. Außerdem wird erklärt, wie man sie mit FILTER und SORTIEREN kombinieren kann. Du erfährst, warum Excel-Tabellen der Trick sind, der dafür sorgt, dass alles von selbst läuft.

Warum reichen die alten Methoden nicht mehr aus?

Vor VSTACK stützte man sich auf drei Techniken.

Wenn du manuell kopierst und einfügst, bekommst du eine Momentaufnahme, was manchmal problematisch sein kann. Wenn sich die Quelldaten ändern oder eine neue Zeile dazukommt, ist deine Zusammenfassung nicht mehr aktuell. Es muss jemand den Vorgang noch mal neu durchführen.

3D-Verweise wie =SUMME(Jan:Mar!B2) können Werte von mehreren Blättern zusammenzählen. Du kannst aber keine kombinierte Tabelle Zeile für Zeile erstellen. Für Berechnungen über mehrere Blätter hinweg sind sie hingegen gut geeignet. Zum Zusammenführen von Rohdaten sind sie aber nicht geeignet.

Mit der Funktion INDIREKT" und einem Blattnamen kann man auf verschiedene Blätter verweisen. INDIREKT ist aber volatil. Jedes Mal, wenn sich eine Zelle in der Arbeitsmappe ändert, wird es neu berechnet. Das merkt man vor allem, wenn die Dateien groß sind. Außerdem gibt es einzelne Werte oder feste Bereiche zurück, aber keine saubere Ausbreitung. Lass das sein!

Dynamische Arrays machen Aktualisierungen vollständig überflüssig. Die Formel wird nur neu berechnet, wenn sich die relevanten Daten tatsächlich ändern. Die Ausgabe ist immer aktuell.

VSTAPELN: Arbeitsblätter vertikal stapeln

=VSTAPELN(array1, [array2], …)

VSTAPELN stapelt zwei oder mehr Bereiche von oben nach unten. Es entsteht ein einziger Ausgabebereich. Dieser enthält alle Zeilen aus jedem Quellbereich, die zusammengefügt wurden. Er ist zudem genauso breit wie der breiteste Quellbereich.

Einfaches Beispiel: Kombiniere einfach drei monatliche Umsatz-Tabellen.

=VSTAPELN(Jan!A2:D50, Feb!A2:D50, Mar!A2:D50)

Dadurch bekommst du eine Tabelle. Zuerst müssen wir alle Zeilen für Januar eintragen. Dann für Februar. Dann für März. Wenn der Januar 30 Zeilen, der Februar 28 Zeilen und der März 31 Zeilen hat, kommt man auf eine Gesamtzahl von 89 Zeilen.

Umgang mit unterschiedlicher Zeilenanzahl

Wenn du weniger als 49 Zeilen mit tatsächlichen Daten hast, erfasst ein fester Bereich wie A2:D50 auch leere Zeilen. Es gibt zwei einfache Lösungen:

**Option 1: Benutze am besten Excel-Tabellen. Du kannst die Daten jeden Monat mit Strg+T in eine Tabelle umwandeln. Nennen wir sie Jan_Data. Der Verweis Jan_Data[#Data] zeigt immer genau an, wie viele Zeilen mit Daten es gibt. Er passt sich automatisch an, das ist praktisch!.

=VSTAPELN(Jan_Data[#Data], Feb_Data[#Data], Mar_Data[#Data])

Option 2: Filter die leeren Zeilen heraus, indem du das Ergebnis in FILTER einbettest und alle Zeilen entfernst, in denen die Schlüsselspalte leer ist.

=FILTER(
    VSTAPELN(Jan!A2:D200, Feb!A2:D200, Mar!A2:D200),
    VSTAPELÖN(Jan!A2:A200, Feb!A2:A200, Mar!A2:A200) <> ""
)

Die FILTER-Bedingung ist einfach ein VSTAPELN der Schlüsselspalte aus allen drei Blättern. Jede Zeile, in der diese Spalte leer ist, wird ausgeschlossen.

HSTAPELN: Spalten nebeneinander anordnen

=HSTAPELN(array1, [array2], …)

HSTAPELN ordnet Bereiche von links nach rechts nebeneinander an. Verwende diese Funktion, wenn Daten zu denselben Zeilen auf Spalten verteilt sind, die nebeneinander stehen sollen.

Beispiel: Die Namen und Berufsbezeichnungen befinden sich in den Spalten A und B auf einem Arbeitsblatt. Die E-Mail-Adressen befinden sich in Spalte C auf einem anderen Blatt. Beide Listen umfassen dieselben 40 Personen in derselben Reihenfolge.

=HSTAPELN(People!A2:B41, Contacts!C2:C41)

Dadurch wird eine Tabelle mit 40 Zeilen und drei Spalten erstellt. Name, Titel und E-Mail-Adresse. Die Daten werden aus zwei Blättern zusammengefügt, ohne dass kopiert und eingefügt werden muss.

Du wirst HSTAPELN wohl seltener verwenden als VSTAPELN. Es löst aber ein echtes Problem, wenn Spalten aus Zugriffs- oder organisatorischen Gründen getrennt bleiben müssen.

Füge eine Spalte mit der Überschrift "Aus welchem Blatt stammt diese Zeile?" hinzu.

Wenn du Zeilen aus mehreren Blättern zusammenführst, ist es oft hilfreich, die Quelle jeder Zeile zu kennen. Am übersichtlichsten ist es, wenn du gleichzeitig mit HSTACK eine Beschriftungsspalte erstellst.

=VSTAPELN(
    HSTAPELN(WENN(Jan_Data[#Data]<>"", "January", ""), Jan_Data[#Data]),
    HSTAPELN(WENN(Feb_Data[#Data]<>"", "February", ""), Feb_Data[#Data]),
    HSTAPELN(WENN(Mar_Data[#Data]<>"", "March",    ""), Mar_Data[#Data])
)

HSTAPELN fügt für jeden Monat eine Spalte mit dem Blattnamen am Anfang hinzu. Anschließend stapelt VSTAPELN die drei beschrifteten Blöcke. Die erste Spalte gibt nun für jede Zeile den Ursprungsmonat an.

Wenn deine Blöcke immer vollständig sind, gibt es eine einfachere Variante.

=VSTAPELN(
    HSTAPELN(MATRIXERSTELLEN(ZEILEN(Jan_Data[#Data]),1,LAMBDA(r,c,"January")), Jan_Data[#Data]),
    HSTAPEN(MATRIXERSTELLEN(ZEILEN(Feb_Data[#Data]),1,LAMBDA(r,c,"February")), Feb_Data[#Data]),
    HSTAPELN(MATRIXERSTELLEN(ZEILEN(Mar_Data[#Data]),1,LAMBDA(r,c,"March")),    Mar_Data[#Data])
)

MATRIXERSTELLEN erstellt eine Spalte, die denselben Text enthält, wobei jede Zeile einer Quellzeile entspricht. Dies entspricht genau der Höhe der jeweiligen Tabelle.

Kombination von SORTIEREN und FILTER

VSTAPELN gibt einen Standard-Überlauf-Bereich zurück. Daher können alle anderen Funktionen für dynamische Arrays direkt darauf angewendet werden.

Sortieren des kombinierten Ergebnisses

=SORTIEREN(
    VSTAPELN(Jan_Data[#Data], Feb_Data[#Data], Mar_Data[#Data]),
    3, 1
)

Die Zahl 3 sortiert nach der dritten Spalte, z. B. einer Datumsspalte. Die Zahl 1 sortiert in aufsteigender Reihenfolge. Du bekommst eine zusammengefasste Tabelle, in der die Daten in einer bestimmten Reihenfolge aus allen drei Blättern stehen.

Das zusammengeführte Ergebnis filtern

=FILTER(
    VSTAPELN(Jan_Data[#Data], Feb_Data[#Data], Mar_Data[#Data]),
    VSTAPELN(Jan_Data[Region], Feb_Data[Region], Mar_Data[Region]) = "North"
)

Die FILTER-Bedingung ist ein VSTAPELN, der ausschließlich die Spalte „Region“ enthält. Er ist zeilenweise mit dem Haupt-VSTAPELN abgeglichen. Es werden nur die „North“-Zeilen angezeigt.

SORTIEREN und FILTER miteinander verketten

=SORTIEREN(
    FILTER(
        VSTAPELN(Jan_Data[#Data], Feb_Data[#Data], Mar_Data[#Data]),
        VSTAPELN(Jan_Data[Region], Feb_Data[Region], Mar_Data[Region]) = "North"
    ),
    4, -1
)

Das ist eine komplette Pipeline. Füge die drei Blätter zusammen. Behalte nur das Blatt „North“. Sortiere nach Spalte 4 (Revenue), vom höchsten zum niedrigsten Wert. Alles in einer Formel!

Warum Excel-Tabellen dafür sorgen, dass alles von selbst läuft

Die Definition der Quellbereiche ist die entscheidende Wahl, die darüber entscheidet, ob deine Einrichtung keinerlei Wartung erfordert.

Die richtige Lösung sind Excel-Tabellen. Wandle dazu jeden Quellbereich in eine Tabelle um. Gib jeder Tabelle einen eindeutigen Namen. Verweise darauf mit Tabellenname[#Daten]. Ab diesem Zeitpunkt wird durch das Hinzufügen einer Zeile in einem beliebigen Quellblatt die Tabellenreferenz erweitert und deine VSTAPELN-Formel bei der nächsten Berechnung aktualisiert. Ganz ohne extra Aufwand!

Was ist mit vielen Arbeitsblättern?

Nehmen wir zum Beispiel an, du hast zwölf Monatsblätter oder ein Blatt pro Region. Auch wenn das Schreiben von VSTAPELN mit zwölf Bereichen zeitaufwendig ist, ist es unkompliziert und effektiv.

Vielleicht bist du versucht, die Blattnamen mithilfe der INDIREKT-Funktion aus einer Liste zu beziehen. Bitte tue das nicht. INDIREKT ist volatil und verlangsamt somit deine Datei. Außerdem liefert es VSTAPELN keinen sauberen Überlaufbereich. Es sieht zwar clever aus, wird dir aber später Probleme bereiten.

Hier ist die einfache Regel:

Wenn die Arbeitsblätter bekannt sind und sich die Liste nicht wesentlich ändert, liste sie in VSTAPELN auf. Das ist die zuverlässigste Option und für jeden leicht zu lesen.

=VSTAPELN(
    Jan_Data[#Data], Feb_Data[#Data], Mar_Data[#Data],
    Apr_Data[#Data], May_Data[#Data], Jun_Data[#Data],
    Jul_Data[#Data], Aug_Data[#Data], Sep_Data[#Data],
    Oct_Data[#Data], Nov_Data[#Data], Dec_Data[#Data]
)

Wenn sich die Anzahl der Blätter erheblich ändert und du die Formel nicht jedes Mal anpassen möchtest, verwende stattdessen Power Query. Mit dieser Funktion kannst du alle Blätter der Datei auf einmal einlesen. Aktualisiere sie, wenn du aktuelle Daten benötigst. Da es sich weder um ein dynamisches Array noch um eine volatile Funktion handelt, bleibt deine Datei schnell.

Verwende also für bekannte Blätter die Funktion VSTAPELN. Für eine wachsende Anzahl unbekannter Blätter verwende Power Query. So oder so wirst du die Funktion INDIREKT nie benötigen.

Kurze Tipps und häufige Fehler

Warum ist das wichtig?

Der größte Vorteil von VSTAPELN ist nicht technischer Natur. Es geht darum, wie dein Team arbeitet.

Sobald die Formel eingerichtet ist und deine Quelldaten in Excel-Tabellen gespeichert sind, aktualisiert sich die kombinierte Ansicht automatisch. Niemand muss daran denken, sie manuell zu aktualisieren. Die Zusammenfassung ist nie älter als zwei Wochen, nur weil jemand im Urlaub war. Es ist auch nicht nötig, ein Makro anzupassen, wenn ein Blatt umbenannt wird.

Die Formel ist der Prozess. Wenn der Prozess eine Formel ist, wird er jedes Mal ausgeführt, wenn die Datei geöffnet wird und sich eine Quelltabelle ändert. Er kann nicht vergessen, verzögert oder durch menschliches Versagen unterbrochen werden, wie es bei einer manuellen Aufgabe der Fall sein kann.

Das ist das wahre Versprechen dynamischer Arrays. Nicht nur kürzere Formeln. Es sind Prozesse, die in der Tabelle leben und sich selbst konsistent halten.

Dieser Artikel ist Teil der Reihe helpme.safeoffice.de, die praktische Anleitungen zu Datenlösungen, der Modellierung von Arbeitsmappen und der Prozessdokumentation bietet. Die Reihe richtet sich an Unternehmen, die effektive, nachhaltige Tools suchen, die für alle verständlich sind.