Excel für (Hoch)stapler 10

Nachdem ich im letzten Artikel mit DBSUMME eine schon lange verfügbare, aber eher unbekannte Tabellenfunktion vorgestellt habe, gibt es heute wieder mal etwas ziemlich Aktuelles. Etwa seit Mitte August 2022 gibt es in Excel aus Microsoft 365 eine ganze Reihe neuer und wirklich nützlicher Funktionen (14 Stück, um genau zu sein), von denen ich dir heute zwei vorstellen möchte:

• HSTAPELN
• VSTAPELN

Es geht also um das Stapeln 🙂

Was man damit genau anfangen kann, das zeige dir im folgenden Beitrag.

Um in den Genuss dieser neuen Funktionen zu kommen, benötigst du Excel aus Microsoft 365. Falls du mit einer anderen Version arbeitest (Excel 2021, 2019, 2016…) bleibt dir zumindest die Möglichkeit, das kostenlose Excel für das Web zu nutzen, um wenigstens einmal hineinschnuppern zu können. Denn dort gibt es diese tollen Funktionen nämlich auch.
 

Beispieldatei herunterladen

Aufeinander stapeln: VSTAPELN

Gehen wir es also an. Die VSTAPELN-Funktion macht, was der Name schon erahnen lässt: Es wird irgendetwas vertikal (daher das V) gestapelt. Hört sich erst einmal unspektakulär an, aber die Funktion liefert die Lösung für ein ganz häufiges Anwenderproblem. Denn oftmals müssen mehrere einzelne Tabellen zu einer gemeinsamen Tabelle zusammengefasst werden.

Nehmen wir ein winziges Beispiel. Die Umsätze für jedes Quartal liegen in einer separaten Tabelle:

Ausgangslage: Vier separate Quartalslisten

Auch wenn in meinem Fall alle Umsatztabellen nebeneinander liegen, muss das nicht zwingend der Fall sein. Sie könnten sich auch in vier unterschiedlichen Arbeitsblättern befinden.
In der Vergangenheit hat man in der Regel per Copy&Paste alles manuell in eine lange Liste zusammenkopiert. Damit ist jetzt Schluss!

Man übergibt an die VSTAPELN-Funktion einfach die Bezüge zu den Tabellen, die zusammengefasst werden sollen:
=VSTAPELN(A5:B7;D5:E7;G5:H7;J5:K7)

Vier Quartalslisten werden zu einer Liste zusammengefasst

Ich habe in meinem Beispiel für die Quellbezüge die reinen Datenbereiche angegeben, also ohne die Überschriften. Denn ansonsten würde auch diese viermal übernommen werden. Um trotzdem die Überschriften auch genau einmal in der Ausgabetabelle zu erhalten, gibt es zwei Möglichkeiten.

Entweder ich beginne nur den ersten Datenbereich bereits mit Zeile 4:
=VSTAPELN(A4:B7;D5:E7;G5:H7;J5:K7)

Nur das erste Array umfasst die Überschriftenzeile

Oder ich übergebe die gewünschten Bezeichnung als zusätzlichen Datenbereich in Form eines Textarrays an die Funktion:
=VSTAPELN({"Monat"."Umsatz"};A5:B7;D5:E7;G5:H7;J5:K7)

Überschriften werden als statische Texte übergeben

Dabei ist auf die besondere Schreibweise zu achten. Die einzelnen Texte sind in doppelte Anführungszeichen zu setzen. Da die Überschriften nebeneinander stehen sollen, werden die beiden Spaltennamen durch einen Punkt getrennt. Und schließlich muss alles mit geschweiften Klammern umfasst werden.

Besonderheiten bei VSTAPELN

Im Idealfall haben die Quelltabellen die gleiche Spaltenanzahl, so wie in meinem ersten Beispiel. Im folgenden Fall fehlt aber in der Q4-Tabelle noch die Umsatzspalte. Die Lücken werden jetzt durch einen unschönen #NV-Fehler aufgefüllt:

Fehler bei Quellbezügen unterschiedlicher Größe

Dieses Problem lässt sich aber einfach lösen. Man muss die Formel nur mit der WENNFEHLER-Funktion umschließen, um nicht vorhandene Daten zu unterdrücken:
=WENNFEHLER(VSTAPELN(A5:B7;D5:E7;G5:H7;J5:J7);"")

Einfache Fehlerkorrektur mit WENNFEHLER

Gemeinsam sind wir stark

Noch interessanter und mächtiger wird die VSTAPELN-Funktion, wenn man sie mit anderen Array-Funktionen kombiniert. Im folgenden Beispiel habe ich Teilnehmerlisten für drei verschiedene Kurse, die Listen sind als formatierte Tabellen mit den Namen tKurs1, tKurs2 und tKurs3 angelegt. Dabei sind manche Teilnehmer in verschiedenen Kursen anwesend:

Quelldaten liegen als formatierte Tabellen vor

Aus diesen drei Einzeltabellen soll nun eine um Mehrfachnennungen bereinigte und alphabetisch sortierte Gesamtliste entstehen. Zum Einsatz kommen dafür 3 dynamische Array-Funktionen: VSTAPELN, EINDEUTIG und SORTIEREN:
=SORTIEREN(EINDEUTIG(VSTAPELN(tKurs1[Teilnehmer Kurs 1];tKurs2[Teilnehmer Kurs 2];tKurs3[Teilnehmer Kurs 3])))

Zusammenfassen, Bereinigen und Sortieren in einem Rutsch

Und es kommt noch besser! Da es sich bei den drei Quelltabellen um formatierte („intelligente“) Tabellen handelt, berücksichtigt die sortierte Ausgabeliste automatisch etwaige später ergänzte Einträge in den Quelltabellen, ohne dass die Formel irgendwie angepasst werden müsste:

Dynamik durch formatierte Tabellen

Nebeneinander stapeln: HSTAPELN

Was übereinander geht, das geht auch horizontal. Dafür nutzt man dann einfach die Funktion HSTAPELN:

Horizontales Stapeln mit HSTAPELN

… und hier wieder mit den Datenbeschriftungen als Text-Array:

Zeilenbeschriftung als statische Texte übergeben

Da Syntax und Verhaltensweise ansonsten identisch mit VSTAPELN sind, verzichte ich hier auf weitere Beispiele.
 

Und: Wie findest du diese neuen Funktionen? Lass uns deine Gedanken und Ideen in den Kommentaren wissen!

Das könnte dich auch interessieren:

Wenig(er) bekannte Datumsfunktionen

Wieviele sind das eigentlich?

Das Schweizer Taschenmesser in Excel: SVERWEIS!

Links, rechts, vor, zurück…