Excel für (Hoch)stapler 6

Brandneue Textfunktionen bringen große Vereinfachungen mit sich
 

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.

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 Datei mit diesem und allen folgenden Beispielen kannst du dir hier herunterladen). Die Umsätze für jedes Quartal liegen in einer separaten Tabelle:

Ausgangslage: Vier separate Quartalslisten

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

Vier Quartalslisten werden zu einer Liste zusammengefasst


Da es sich um eine sogenannte dynamische Array-Funktion handelt, „läuft“ die Formel automatisch in so viele Zellen über, wie eben benötigt werden. Man erkennt den Bereich an dem blauen Rahmen, der um die Gesamttabelle herum angezeigt wird, sobald sich die aktive Zelle irgendwo innerhalb des Datenbereichs befindet.

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

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

Ü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

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

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

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

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

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

Horizontales Stapeln mit HSTAPELN

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

Zeilenbeschriftung als statische Texte übergeben

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!

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

6 Gedanken zu “Excel für (Hoch)stapler

  • Barbara

    Genial….einfach nur genial.

    Diese Funktionen hätte ich letztes Jahr für ein kompliziertes Bonus eines meiner Kunden haben sollen 🙂 – das Bonusmodell wurde gecancelt und die neue Funktion kommt 🙂
    Habe mir gerade von den vielen Bildchen Screenshots gemacht (Geheimtipp: SnagIt von TechSmith), kurz ins Word geschickt und gespeichert.

    Herzlichen Dank Martin!

    • Martin Weiß Autor des Beitrags

      Hallo Barbara,

      danke für dein Feedback! Ja, auf diese Funktionen haben vermutlich viele Anwender schon lange gewartet (mich eingeschlossen)…

      Liebe Grüße,
      Martin

  • Elle

    Hi Martin,

    wie immer: tolle Erklärung!

    Hier mal eine kleine Ergänzung:
    Falls man horizontal vorliegende Tabellen vertikal auswerten möchte (oder umgekehrt) dann kann man sich der MTRANS()-Funktion bedienen. Für Dein horizontales Beispiel sähe das dann so aus:
    =MTRANS(HSTAPELN({„Gesamtjahr“;“Umsatz“};B4:G5;B8:G9))

    Liebe Grüße
    Elle