Abonnenten von Microsoft 365 können neuerdings zwei oder mehrere Zellbereiche in einen Zellbereich unter- oder nebeneinander zusammenfassen. Da dieser Vorgang dem Stapeln ähnelt, heißen die beiden neuen Funktionen VSTAPELN und HSTAPELN. In diesem Tipp sehen Sie anhand einiger Beispiele, wie Sie diese beiden neuen Funktionen VSTAPELN und HSTAPELN einsetzen.

Sowohl die Funktion VSTAPELN für das vertikale als auch die Funktion HSTAPELN für das horizontale Stapeln von Zellbereichen erzeugen einen Überlaufbereich. Der Überlaufbereich ist erkennbar an dem schmalen blauen Rahmen um den neu erzeugen Zellbereich.

Beispiel 1: VSTAPELN mit gleicher Spaltenzahl

Im ersten Beispiel sollen zwei Zellbereiche mit Namen und Ort von Personen zu einem Zellbereich untereinander zusammengefasst werden. Im Tabellenblatt sehen die beiden Zellbereiche so aus:

Beispieldaten für VSTAPELN

Um die beiden Zellbereiche untereinander zu stapeln geben Sie in Zelle A7 folgende Formel ein:

=VSTAPELN(A1:B5;D2:E4)

Das Tabellenblatt mit dem neu erzeugten Zellbereich sieht so aus:

Funktion STAPELN

Die Anzahl der Zeilen im neuen Zellbereich entspricht der Summe der Zeilen der berücksichtigten Zellbereiche. Die Anzahl der Spalten entspricht der maximalen Spaltenanzahl aller Bereiche. Da in diesem Fall beide Zellbereiche zwei Spalten aufweisen, hat der neue Bereich ebenfalls zwei Spalten.

Anders sieht es aus, wenn sich die Spaltenzahl der einzelnen Zellbereiche unterscheidet.

Beispiel 2: VSTAPELN mit unterschiedlicher Spaltenzahl

Die beiden zusammenzufassenden Zellbereiche sehen im Tabellenblatt zunächst so aus:

Beispiel 2 für VSTAPELN

Im zusammengefassten Zellbereich erscheint in den Zeilen, die aus dem zweiten Zellbereich E1:F4 stammen, in der dritten Spalte der Fehlerhinweis #NV, weil der zweite Zellbereich nur 2 Spalten hat, d.h. die Werte für die dritte Spalte sind nicht vorhanden. Sie können den Fehlerhinweis #NV mit der Funktion WENNFEHLER durch ein Leerzeichen oder wie in diesem Fall durch den Standardwert Deutschland ersetzen: Ändern Sie die Formel in Zelle A8 wie folgt ab:

=WENNFEHLER(VSTAPELN(A1:C5;E2:F4);”Deutschland”)

Der Zellbereich hat nun folgendes Aussehen:

Stapeln von zwei Bereichen

Im zusammengefassten Zellbereich erscheint in den Zeilen, die aus dem zweiten Zellbereich E1:F4 stammen, in der dritten Spalte der Fehlerhinweis #NV, weil der zweite Zellbereich nur 2 Spalten hat, d.h. die Werte für die dritte Spalte sind nicht vorhanden. Sie können den Fehlerhinweis #NV mit der Funktion WENNFEHLER durch ein Leerzeichen oder wie in diesem Fall durch den Standardwert Deutschland ersetzen: Ändern Sie die Formel in Zelle A8 wie folgt ab:

=WENNFEHLER(VSTAPELN(A1:C5;E2:F4);”Deutschland”)

Der Zellbereich hat nun folgendes Aussehen:

Stapeln von zwei Bereichen

Beispiel 3: Zusammenfassen von Umsatzwerten aus mehreren Tabellenblättern

Im nächsten Beispiel sind die Umsatzwerte aus den 4 Quartalen eines Jahres auf 4 Tabellenblätter verteilt. Die Tabellenblätter haben die Namen Quartal 1, Quartal 2, Quartal 3 und Quartal 4. Alle Tabellenblätter haben die gleiche Spaltenzahl, aber unterschiedlich viele Zeilen. Das Tabellenblatt für das erste Quartal sieht so aus:

Beispiel für VSTAPELN und 3D-Bezug

Die Daten aus den 4 Tabellenblättern sollen in einem neuen Tabellenblatt zusammengefasst und nach dem Datum sortiert werden:

Im Unterscheid zum ersten Beispiel mit den Adressen werden die Spaltenüberschriften nicht in den Zellbereich für die Funktion VSTAPELN übernommen, sondern in der ersten Zeile vorher eingetragen. Geben Sie in der Zelle A2 folgende Formel ein:

=SORTIEREN(VSTAPELN(‘Quartal 1′!A2:D11;’Quartal 2′!A2:D9;’Quartal 3′!A2:D13;’Quartal 4’!A2:D10);1)

Sie sehen im Folgenden einen Ausschnitt des neuen Tabellenblatts.

Funktion VSTAPELN mit SORTIEREN

Mit der Funktion VSTAPELN werden die Zeilen mit den Umsätzen aus den 4 Quartalen zu einem Bereich zusammengefasst. Dieser neu erzeugte Zellbereich wird noch sortiert nach dem Datum. Dazu dient die Funktion SORTIEREN mit dem Parameter 1, der für die erste Spalte steht.

Es ist möglich, statt den 4 einzeln anzugebenden Zellbereichen in der Funktion VSTAPELN einen 3D-Bezug zu verwenden. Die Formel wird dadurch kürzer, insbesondere wenn die Anzahl der zu berücksichtigenden Tabellenblätter bzw. Zellbereiche noch größer wird. Achten Sie bei dem 3D-Bezug darauf, dass Sie keine Zeilen aus einem der Tabellenblätter vergessen. Maßgeblich ist hier das Tabellenblatt mit den meisten Zeilen. In unsrem Fall ist es das Tabellenblatt für dritte Quartal mit 12 Umsatzzeilen: Die Formel mit den 3D-Bezug lautet folgendermaßen:

=VSTAPELN(‘Quartal 1:Quartal 4’!A2:D13)

Der neu erzeugte Zellbereich berücksichtigt alle Zeilen von Zeile 2 bis Zeile 13 aus allen Tabellenblättern, die in der Arbeitsmappe zwischen den Tabellenblättern Quartal1 und Quartal 4 sich befinden.

Das Tabellenblatt sieht nach Eingabe der obigen der Formel in Zelle A2 so aus:

Funktion VSTAPELN mit 3D-Bezug

Es erscheinen Zeilen mit dem Datum 00.01.1900 und dem Wert 0.

Das sind die Leerzeilen, die aus den Tabellenblättern, die weniger als 12 Zeilen haben übernommen worden sind. Um diese Zeilen herauszufiltern, ändern Sie Formel in Zelle A2 so ab:

=FILTER(VSTAPELN(‘Quartal 1:Quartal 4’!A2:D13);VSTAPELN(‘Quartal 1:Quartal 4’!A2:A13)<>0)

Mit dieser Formel wird der neu erzeugte Zellbereich gefiltert. Das Kriterium lautet: Der Wert in der Spalte A muss ungleich 0 sein. Deshalb lautet der 3D-Bezug für das Filterkriterium so: VSTAPELN(‘Quartal 1:Quartal 4’!A2:A13)<>0.

Wenn Sie nach der Filterung noch eine Sortierung nach dem Datum vornehmen möchten, erweitern Sie die Formel in A2 wie folgt:

=SORTIEREN(FILTER(VSTAPELN(‘Quartal 1:Quartal 4’!A2:D13);VSTAPELN(‘Quartal 1:Quartal 4’!A2:A13)<>0);1)

Beispiel 4: Horizontales Zusammenfassen von Zellbereichen

Für das horizontale Zusammenführen von Zellbereichen bietet Excel die Funktion HSTAPELN an.

In unserem Beispiel sollen die Umsatzwerte aus 2 Halbjahren in einem neuen Tabellenblatt zusammengefasst werden, Die Tabellenblätter sehen so aus:

1. Halbjahr

Beispiel 1 für HSTAPELN

2. Halbjahr

Beispiel 2 für HSTAPELN

Im zweiten Halbjahr wurde das Produkt 5 neu in das Sortiment aufgenommen.

Im neuen Tabellenblatt werden nur die Zellen in Spalte A ausgefüllt.

Geben Sie danach in Zelle B1 folgende Formel en:

=WENNFEHLER(HSTAPELN(‘Produktumsatz Halbjahr 1′!B1:G5;’Produktumsatz Halbjahr 2’!B1:G6);””)

Das Tabellenblatt sieht danach so aus. Die Gesamtsummen wurden später in einem weiteren Arbeitsschritt berechnet.

Funktion HSTAPELN

In der obigen Formel wurde die Funktion WENNFEHLER eingesetzt, damit in der Zeile mit dem Produkt 5 in den Zellen für die ersten 6 Monate des Jahres kein Fehlerhinweis #NV erscheint.

Fazit

Mit den neuen Funktion VSTAPELN und HSTAPELN können Sie elegant beliebige Zellbereiche vertikal bzw. horizontal zusammenfassen und für weitere Berechnungen nutzen.