Excel für (Hoch)stapler 10

Artikelbild-303
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.
 
Beispieldatei herunterladen
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

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!

 

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

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

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

  • Avatar-Foto
    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!

    • Avatar-Foto
      Martin Weiß

      Hallo Barbara,

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

      Liebe Grüße,
      Martin

  • Avatar-Foto
    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

  • Avatar-Foto
    Stephan Böhm

    Coole Funktionen, obwohl ich das wahrscheinlich mit PowerQuery lösen würde. „EINDEUTIG“ kannte ich auch noch nicht.

  • Avatar-Foto
    Christoph

    Hallo Martin,
    ich weiß nicht, ob sich das mit diesen Funktionen bewerkstelligen lässt. Ich habe eine Liste, im Prinzip in folgender Form (nur als Beispiel)

    Stadt Land Fluss
    Frankfurt Hessen Main
    München Bayern Isar
    Stuttgart BW Neckar

    und möchte für den Ausdruck die in folgende Form überführen:
    Frankfurt
    Hessen
    Main

    München
    Bayern
    Isar

    Köln
    NRW
    Rhein

    Lässt sich das ohne Monster-Formeln bewerkstelligen?

    Danke im Voraus