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 Zusammenführung unterschiedlicher Daten bzw. 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.
Im Folgenden wird erläutert, wie in Excel eine dynamische Datenkonsolidierung (inkl. beliebiger Sortierungen) mit nur einer einzigen Excel-Formel gelingt. Dazu nutzen wir weder VBA noch Power Query, sondern VSTAPELN (Englisch: VSTACK).
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.
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:
- Manuelles Kopieren und Einfügen der einzelnen Tabellen in eine kombinierte Tabelle
- Ein VBA-Makro programmieren, um das Zusammenfügen zu automatisieren
- Power Query verwenden, um die Daten zusammenzuführen
Während 1 aufgrund des Zeitaufwandes und der Fehleranfälligkeit direkt ausscheiden sollte, erfordert die VBA-Lösung 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 schießen“.
Nutzen Sie Microsoft bzw. 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 ihnen 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).
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)
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. Dabei ist wichtig, da wir bewusst nicht mit dynamischen Tabellen arbeiten, 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?
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)
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 den folgenden Synthax:
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 bspw. 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).
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 BEREICH.VERSCHIEBEN-Funktion dynamisch definieren (vgl. Abb. 6).
D.h. wir gehen 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 max. Zeile 15.000).
Schließlich ist für die Breite (hier manuell) eine 5 einzutragen, da wir 5 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, von denen wir nur einen ganz kleinen Ausschnitt kennengelernt haben, begeistern. Damit sind Dinge (schnell und einfach) umsetzbar, die vorher selbst erfahrene Excel-Anwender zur Verzweiflung gebracht haben.
Ungewohnt sein dürfte für die meisten aber das Arbeiten mit und das Verhalten der dynamischen Überlaufbereiche (= Spill Range) sein. Zwei Dinge die Sie hier beachten sollten:
- Da derartige Arrays bzw. Bereiche dynamisch sind, sollten Sie diese (ähnlich wie bei Pivot-Tabellen) auf eigenen Tabellenblättern anlegen bzw. darunter oder daneben keine anderen Dinge positionieren. Ansonsten erhalten Sie schnell einen #ÜBERLAUF!-Fehler
- 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 bzw. Spalten, so dass auch größere Arrays wie gewünscht dargestellt werden (z.B. Datumswerte, Währungswerte etc.).
Wie üblich haben wir auch zu diesem Tutorial eine kostenlose Excel-Download-Datei vorbereitet. Diese erleichtert ihnen das Beispiel nachzuvollziehen und kann als Ausgangsvorlage für eigene Ideen verwendet werden.
