Dynamische Datenkonsolidierung mit VSTAPELN in Excel

Die Zusammenführung unterschiedlicher Daten oder Tabellen gehört im Rahmen von Datenanalysen und Reporting zu den Standardaufgaben in der Praxis von Controlling- und Finanzverantwortlichen. Dabei spielt es keine Rolle, ob es sich um Verkaufsdaten, Kundendaten oder um Finanz- und Buchhaltungsdaten handelt. In diesem Beitrag wird erläutert, wie in Excel eine dynamische Datenkonsolidierung (inklusive beliebiger Sortierungen) mit nur einer einzigen Excel-Formel gelingt.

Dazu nutzen wir weder VBA noch Power Query, sondern VSTAPELN, eine der neuen mächtigen Excel-Funktionen. Mit der hier vorgestellten, einfachen und eleganten Lösung können beliebig viele Tabellenblätter ohne weitere Anpassungen aggregiert werden, dabei kann auch die Anzahl der Datensätze je Tabellenblatt (= Zeilenanzahl) völlig unterschiedlich sein.

Eine neue Excel-Welt

Mit Office bzw. Excel 365 wurden dynamische Arrays (= dynamische Bereiche) eingeführt, dazu viele neue Funktionen, welche nicht nur Dinge möglich machen, die bislang nur umständlich mit vielen Hilfszellen umgesetzt werden konnten, sondern die insbesondere auch bei umfangreichen Datenbeständen erhebliche Performancegewinne gegenüber den klassischen Matrixfunktionen mit sich bringen.

Die Ausgangslage

Unser junges (natürlich fiktives) Start-up, BerlinBeerBuddies (BBB), hat sich auf den Direktvertrieb von ausgesuchten Bierspezialitäten fokussiert. Die Vertriebsmitarbeiter verkaufen im Telefonvertrieb an Kunden im gesamten Bundesgebiet, wobei das ERP-System dem Controller immer alle Verkaufsdaten je Bundesland ausgibt. Für eine bundesweite Auswertung in Excel müssen diese Daten zunächst in einer einzigen Tabelle zusammengeführt werden – eine Aufgabe, die in ähnlicher Form jedem Controller bekannt vorkommen sollte. Die Standardwege für eine solche Datenkonsolidierung:

1. Manuelles Kopieren und Einfügen der einzelnen Tabellen in eine kombinierte Tabelle 
2. Ein VBA-Makro programmieren, um das Zusammenfügen zu automatisieren. 
3. Power Query verwenden, um die Daten zusammenzuführen

Während Lösung 1 aufgrund des Zeitaufwandes und der Fehleranfälligkeit direkt ausscheiden sollte, erfordert die VBA-Lösung (2) vom Anwender Programmierkenntnisse und generiert auch eine in vielen Unternehmen aus Sicherheitsgründen nicht zulässige Makro-Datei. Lösungsansatz 3 setzt voraus, dass der Anwender bereits über Erfahrungen mit Power Query verfügt und bedeutet für unsere Aufgabe eigentlich mit "Kanonen auf Spatzen zu schießen".

Nutzen Sie Microsoft Office oder Excel 365, lässt sich eine solche dynamische Datenkonsolidierung ganz einfach mit einer einzigen Funktion, nämlich VSTAPELN (englisch: VSTACK) umsetzen. Die finale Excel-Datei unseres Beispiels steht kostenlos zum Download zur Verfügung. Die Lösung mit der VSTAPELN-Funktion Ganz allgemein ermöglicht die Funktion VSTAPELN

ein vertikales Stapeln von mehreren Matrizen / Arrays in ein neues kombiniertes Array. Es gibt in Excel auch noch die analoge Funktion HSTAPELN, die dasselbe horizontal verrichtet.

In unserem Beispiel haben wir verschiedene Blätter mit den Verkaufsdaten der einzelnen Bundesländer, wobei diese gleich aufgebaut sind (vgl. Abb. 1). Allerdings ist die Anzahl der Zeilen auf jedem Blatt unterschiedlich und auch handelt es sich nicht um intelligente Tabellen. Die Aufgabe lautet also, die Daten aller Blätter so in einer neuen Tabelle zusammenzuführen, dass später keine weiteren Anpassungen erforderlich sind, auch wenn noch weitere Bundesländer (also zusätzliche Tabellenblätter) hinzukommen (= dynamische Konsolidierung).


Abb. 1

Schritt 1: Vorbereitung

Im ersten Schritt fügen wir zwei neue Tabellenblätter (z. B. mit [Shift] + [F11]) ein, welche die zu aggregierenden Blätter begrenzen. Sie können diese beliebig benennen, die Blätter bleiben komplett leer. Die Konsolidierung werden wir auf dem neuen Tabellenblatt "Konsolidiert" durchführen (vgl. Abb. 2):


Abb. 2

Schritt 2: Zusammenführen

Auf dem neuen Blatt "Konsolidiert" haben wir die Spaltenüberschriften der Ausgangsblätter einfach einkopiert und fügen (nur) in Zelle A10 zunächst die folgende Formel ein:

Der Trick dabei ist, nicht einzelne Arrays von jedem Tabellenblatt manuell einzufügen, denn dann müsste die Formel später bei zusätzlichen Tabellenblättern angepasst werden, sondern für alle Tabellenblätter von "Start >>" bis "<< ENDE" (deswegen Doppelpunkt dazwischen) den Datenbereich einmalig vorzugeben. Da wir bewusst nicht mit dynamischen Tabellen arbeiten, ist es wichtig, dass die Zeilenanzahl so hoch gewählt wird, dass auch alle (zukünftigen) Daten enthalten sind. In unserem Beispiel haben wir pro Blatt maximal 150 Datensätze, wir wählen sicherheitshalber einfach 1.000 Zeilen und haben somit genügend Reserve. Das Ergebnis zeigt Abb. 3 und erschreckt uns zunächst wegen der Nullwerte. Haben wir etwa einen Fehler gemacht?


Abb. 3

Entwarnung: Da wir den Bereich in unserer VSTAPELN-Funktion jeweils bis Zeile 1.000 vorgegeben haben, werden natürlich auch die leeren Zeilen der Blätter in das neue, konsolidierte Array übernommen. In D7 haben wir eine Zählfunktion eingebaut, die zeigt, dass insgesamt 9.990 Zeilen konsolidiert wurden.

Schritt 3: Filtern

Schachteln wir unsere VSTAPELN-Funktion einfach in eine Filterfunktion, ist das Problem umgehend gelöst. Der Syntax dieser ebenfalls relativ neuen dynamischen Array-Funktion lautet:

Dabei definiert das Argument „einschließen“ die Bedingung, welche Werte aus der Matrix gefiltert, also angezeigt werden sollen. In unserem Fall verwenden wir die gleiche Formel wie für die Matrix und lassen uns nur Werte anzeigen, die ungleich Null sind (siehe Abb. 4):


Abb. 4

Das Ergebnis sieht schon ganz anders aus und liefert nur noch 1.043 Datensätze. Im Grunde sind wir damit eigentlich schon fertig, allerdings möchte ich ihnen die "neue" SORTIEREN-Funktion nicht vorenthalten, mit der man auch schon die Rohdaten etwas "aufräumen" kann.

Schritt 4: Sortieren

Die SORTIEREN-Funktion hat die folgende Syntax:

Wird nur die Matrix angegeben (das einzige zwingend erforderliche Argument), wird die Matrix einfach nach deren erster Spalte aufsteigend sortiert. Ist eine andere Sortierung gewünscht, müssen Sie die optionalen (in eckigen Klammern aufgelisteten) Argumente nutzen. Der Sortierindex ist dabei eine Zahl, die die Zeile oder Spalte angibt, nach der sortiert werden soll. Bei der Sortierreihenfolge steht eine "1" für aufsteigende Reihenfolge (Standard) und eine "–1" für absteigende Reihenfolge. Schließlich können Sie noch angeben, ob nach Zeile (Standard) oder nach Spalte sortiert werden soll.

EXPERTEN-TIPP: Nach mehreren beliebigen Kriterien auf- und absteigend sortieren

Mit diesem Trick können Sie eine Matrix auch nach mehreren Kriterien in beliebiger Reihenfolge sortieren lassen und dabei sogar für jedes Kriterium einzeln vorgeben, ob die Sortierung auf- oder absteigend erfolgen soll. Möchten wir beispielsweise nach Bundesländern (Spalte 5 unserer neuen Matrix) absteigend und anschließend nach dem Datum (Spalte 1 der Matrix) aufsteigend sortieren, dann können wir die SORTIEREN-Formel folgendermaßen anpassen:

Das Entscheidende (Neue) dabei ist der rot markierte Teil, eine spezielle Variante für die beiden Argumente Sortierindex und Sortierreihenfolge. Sortiert werden soll gemäß erster Klammer nach den Spalten 5 und 1 der Matrix. Dabei soll die Sortierreihenfolge für die Spalte 5 absteigend (–1) und für die Spalte 1 aufsteigend (1) sein. Wichtig: Beide Argumente müssen in geschweiften Klammern {…} stehen (Tastenbefehl zum Einfügen öffnende Klammer: [Alt Gr] + [7] sowie [Alt Gr] + [0] für schließende Klammer) und durch Punkte (nicht durch Kommata oder Semikolon) getrennt werden. Selbstverständlich können Sie auch noch nach einer 3. oder 4. Spalte sortieren lassen. Die Logik bleibt immer die Gleiche.

Neue Daten hinzufügen – "Let the magic happen" 

Nun kommt der Lackmustest für unsere soeben mit nur einer Formel in einer Zelle aufgebaute Konsolidierungslösung. Wir schieben einfach die Daten eines weiteren Bundeslandes, nämlich Thüringen (= TH) irgendwo zwischen die Tabellenblätter "Start >>" und "<< ENDE". Auf unserem Konsolidierungsblatt aktualisiert sich alles ohne weitere Anpassungen wie von Geisterhand (vgl. Abb. 5).


Abb. 5

Da auf dem Blatt "TH" nur drei Datensätze hinterlegt waren und wir nach Bundesland absteigend sortieren, stehen diese direkt oben in unserer konsolidierten Tabelle. Auch die Gesamtanzahl der Datensätze ist erwartungsgemäß von 1.043 um drei auf 1.046 angestiegen (It’s magic).

Auswertungen – aber wie?

Die von uns verwendeten dynamischen Array-Funktionen FILTER und SORTIEREN lassen sich leider nicht in intelligenten Tabellen verwenden. Aus diesem Grunde müssen wir z. B. für Pivot-Analysen unseren dynamischen neuen Array mit einer Formel flexibel definieren. Dazu legen wir einfach im Namensmanager einen neuen Bereich/Namen an (hier: Data), den wir dann z. B. mit der Funktion BEREICH.VERSCHIEBEN dynamisch definieren (vgl. Abb. 6).


Abb. 6

Die fertige Formel lautet:

Wir gehen also von der oberen linken Ecke der Tabelle aus (Zelle $A$9, da wir die Überschriften auch mitnehmen möchten), die nächsten beiden Argumente (Zeilen und Spalten) bleiben auf 0, da wir nicht verschieben möchten. Allerdings muss dann für die Höhe des Bereiches die Anzahl der Zeilen eingeben werden. Das machen wir dynamisch mit der ANZAHL2-Funktion (oben rot markiert).

Das ist übrigens genau die gleiche Formel, die wir für unser Feld "Anzahl Datensätze" verwendet haben. Denken Sie daran, auch hier vorsorglich einen hohen Maximalwert vorzugeben (bei uns bis maximal Zeile 15.000). Schließlich ist für die Breite (hier manuell) eine 5 einzutragen, da wir fünf Spalten haben. Konsolidieren Sie später Ausgangsdaten mit einer anderen Spaltenanzahl, dann ist das natürlich einmalig anzupassen. In der Beispiel-Datei haben wir Ihnen zusätzlich auf dem Blatt „Auswertung“ eine einfache Pivot-Tabelle eingefügt, was hier aber nicht weiter vertieft werden kann und soll. Nur der Hinweis, dass bei Pivot-Tabellen im Fall von Datenänderungen immer manuell aktualisiert werden muss (z. B. via [STRG] + [ALT] + [F5]).

Fazit und einige Hinweise in eigener Sache

Hoffentlich konnte ich Sie für die neuen Excel-365-Funktionen begeistern, von denen wir nur einen ganz kleinen Ausschnitt kennengelernt haben. Damit sind Dinge (schnell und einfach) umsetzbar, die vorher selbst erfahrene Excel-Anwender zur Verzweiflung gebracht haben. Ungewohnt dürfte für die meisten aber das Arbeiten mit dynamischen Überlaufbereichen (= Spill Range) sein und deren Verhalten. Zwei Dinge sollten Sie hier beachten:

1. Da derartige Arrays oder Bereiche dynamisch sind, sollten Sie diese (ähnlich wie bei Pivot-Tabellen) auf eigenen Tabellenblättern anlegen bzw. darunter oder daneben keine anderen Dinge positionieren. Andernfalls erhalten Sie schnell einen #ÜBERLAUF!-Fehler. 
2. Da die Formel nur in einer einzigen Zelle eingegeben wird, werden die anderen Zellen des Überlaufbereiches auch nicht automatisch formatiert. Formatieren Sie also vorausschauend eine genügend große Anzahl von Zeilen und Spalten, so dass auch größere Arrays wie gewünscht dargestellt werden (z. B. Datumswerte, Währungswerte etc.). 

Die kostenlose Excel-Datei spart ihnen viel Zeit und kann als Ausgangsvorlage oder "Steinbruch" genutzt werden >>



Download des vollständigen Beitrages: Download

Quelle: fimovi GmbH letzte Änderung ).D.G. am 31.03.2023 Autor:  Dirk Gostomski

Autor:in

	Herr Dirk Gostomski (fimovi.de) Dirk Gostomski ist seit mehr als 20 Jahren selbständiger Berater und Trainer im Bereich Financial Modelling mit Excel. Mit www.financial-modelling-videos.de bietet er auch Intensiv-Video-Workshops an, in denen Schritt für Schritt die Erstellung von professionellen Finanzplanungs, Projektfinanzierungs- und Cashflow-Modellen in Excel erläutert wird.
Homepage | weitere Fachbeiträge des Autors | Forenbeiträge