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:
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:
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:
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:
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:
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:
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.
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:
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
2. Halbjahr
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.
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.
0 Kommentare