Optimale Modellierung von Excel-Dateien

Tabellenstruktur

Der typische Tabellenaufbau in Excel-Dateien (Mischung aus Eingabe und Berechnung über zahlreiche (z.B. Datums-)Spalten auf einem Tabellenblatt führt dazu, dass solche Tabellen beispielsweise nicht mit einer Pivot-Tabelle analysiert werden können. Generell sollten Tabellen mehr in der Logik von Datenbanken erstellt werden, im sogenannten Long Format.

Wide-Format und Long-Format (auch ungestapelt und gestapelt genannt) sind Begriffe in der IT und der Statistik, die verwendet werden, um zwei verschiedene Darstellungen von Tabellendaten zu beschreiben.

Das Wide-Format eignet sich für die Darstellung von Querschnittsdaten oder Zeitreihendaten. Im Wide Format enthalten mehrere Spalten die Werte des gleichen Merkmals zu unterschiedlichen Zeitpunkten:

• Im Wide Format entspricht jede Zeile einer Beobachtungseinheit.
• Die Anzahl der Zeilen entspricht der Anzahl der Beobachtungseinheiten.
• Es gibt mehr Spalten und weniger Zeilen (als im Long Format).
• Werden aus führenden IT-Systemen (z.B. SAP BW) Reports exportiert, dann liegen diese meist in einem Wide Format vor.

Die meisten Excel-Modelle werden in einem Wide Format (s. Abb. 1 unten), bestehend aus Eingabe- und Verabeitungszellen (Formeln) erstellt und dienen gleichzeitig der Ausgabe bzw. Darstellung von Ergebnissen. Da solche Tabellen recht groß werden können und außerdem zahlreiche Neben- oder Hilfsberechnungen enthalten, werden teilweise Zeilen- und Spalten ausgeblendet, um eine gewisse Übersichtlichkeit zu erhalten. Die berüchtigten Excel-Tapeten kommen so zustande!

Abb. 1: Long Format und Wide Format in der Gegenüberstellung

Im Long-Format (s. Abb. 1 oben) hingegen liegen Messung von einer Beobachtungseinheit in mehreren Zeilen vor (z.B. Buchungssätze). Dabei sind die Werte einer Beobachtungseinheit, die in unterschiedlichen Modi (z.B. Merkmale) erhoben wurden, untereinander gelistet. Jedes Merkmal erhält eine eigene Spalte. Im Long Format gibt es weniger Spalten und dafür mehr Zeilen.

Im Sinne einer flexiblen Datenverarbeitung (z.B. mit der Pivot-Tabelle) sollten Daten in Excel-Modellen im Long Format importiert oder erfasst werden, während das Wide Format (in Form einer Kreuztabelle) eher für Berichte oder als Datenquelle für Diagramme geeignet ist.

Aufbau einer Excel-Datei

Im Sinne einer flexiblen und automatisierten Datenverarbeitung mit Excel und einer Integration in die Datenverarbeitungsprozesse des Controllings, sollte zum Aufbau von Excel-Dateien das EVA-Prinzip der IT umgesetzt werden. Das EVA-Prinzip beschreibt die Trennung der Eingabe von der Verarbeitung und der Ausgabe. Diese Modellierung ermöglicht es, Excel in die bestehende IT-Landschaft von Unternehmen zu integrieren, ohne das Prinzip der Datenkonsistenz zu verletzen oder Systembrüche zu begehen (s. Abb. 2). Excel-Modelle nach diesem Prinzip und mit den richtigen Techniken aufgebaut, importieren, verteilen, berechnen Daten automatisch, ohne dass eine Programmierung dafür notwendig ist!

Abb. 2: Das EVA-Prinzip in einer Excel-Datei umgesetzt

Eingabe

Die Eingabeebene besteht aus einem Tabellenblatt in einem Long Format, auf dem Daten aus anderen Systemen, wie z.B. SAP importiert werden. Auf diesem Tabellenblatt gibt es keinerlei Berechnungen, dieses Tabellenblatt dient nur als "Datenbank" für die zu lösende Aufgabe. Damit Excel nicht als redundante Datenbank oder IT-Schattensystem verwendet wird, umfasst der Datenbestand auf diesem Tabellenblatt nur die für die Aufgabenstellung notwendigen Daten. Nur die absolut notwendigen historischen Daten werden importiert, idealerweise bereits so stark verdichtet, wie es für die Aufgabenstellung notwendig ist. Die Struktur der Daten ist die einer klassischen Tabelle: Zeilen und Spalten mit Zahlen oder Text als Zellinhalt. Nach Möglichkeit soll im Modell selbst keine Verdichtung mit Exceltechniken vorgenommen werden. Ist das nicht vermeidbar, werden die Daten nach dem Prinzip "tidy data" angelegt: Jede Variable/jedes Merkmal, jede Dimension eine eigene Spalte, jeder Datensatz in eine Zeile.

Auf einem anderen Tabellenblatt (z.B. Werte) werden die für das Modell relevanten Steuerungsparameter zentral untergebracht. Auch diese können ggf. (falls notwendig täglich) importiert werden und es erfolgt keine Berechnung. Dies können Zielrenditen des Unternehmens, der Referenzkurse anderer Währungen, Zuschlagssätze für die Kalkulation, Kostenstelleninformationen, usw. sein. Handelt es sich um Planungs- und Simulationsmodelle, so können hier auch wichtige Treiber (z.B. BIP/GDP) für das Geschäftsmodell hinterlegt werden. Auch solche Daten können dynamisch und tagesaktuell importiert werden. Hier werden auch die für das Modell erforderlichen Listen für Schaltflächen und/oder Zeilen-Spaltenbeschriftungen hinterlegt und im gesamten Modell verteilt. Das hat den Vorteil, dass zentral "auf Knopfdruck" Beschriftungen im gesamten Modell einheitlich auf eine andere Sprache umgestellt werden können. Gerade für die Unternehmen, die international aufgestellt sind, ist das von Vorteil.

Verarbeitung

Auf den Verarbeitungsblättern erfolgen durch „Verknüpfungen“ aus den Basisdaten und den Werten die notwendigen Berechnungen, Die Tabellenblätter dieser Ebene bestehen nur aus Formelzellen. Notwendige Beschriftungen werden per Funktionen vom Tabellenblatt „Werte“ aus der Eingabeebene verteilt. Natürlich reicht in der Regel ein Verarbeitungsblatt nicht aus. Daher werden die Verarbeitungsblätter per „Verknüpfungen“ nach dem Prinzip der Einbahnstraße verbunden. D.h. die Verknüpfungen verlaufen immer von Blatt zu Blatt in eine Richtung, niemals zurück (Zirkelbezüge!). Da es sich außerdem um die Verarbeitungsebene handelt, sind die Tabellen nicht aufwändig formatiert. Denn sie dienen nur der Verarbeitung von Daten, nicht der Darstellung der Ergebnisse. Alle Zellen können mit dem Blattschutz von Excel nach einem Formelcheck vor unbeabsichtigten Änderungen geschützt werden. Tritt danach eine Fehlermeldung in einer Zelle auf, kann die Ursache direkt in der Eingabeebene gesucht werden. So ergibt sich eine Zeitersparnis beim Fehler-Management!

Ausgabe

In dieser Ebene erfolgt die Darstellung der wichtigsten Zahlen, im Sinne der Informationsvermittlung. Die Ausgabe kann als Frontend aus mehreren Tabellenblättern bestehen, die Diagramme, Tabellen, Kennzahlenbäume enthalten. Die Datenselektion kann dynamisch per Schaltflächen, die Benutzerführung per Hyperlinks erfolgen. Hier steht der Informationsbedarf der Empfangenden im Vordergrund. Daher sind diese Tabellenblätter optimal nach den Prinzipien des Informationsdesigns und – psychologie sowie der Benutzerführung gestaltet. Sie sollen einen schnellen Blick die wesentlichen Informationen ermöglichen und so zu den richtigen Managemententscheidungen führen.

Prinzipien dieser Modellierung

• Trennung von so genannten „Veränderbaren Zellen“ (Zellen ohne Formeln, Funktionen, Verknüpfungen = Eingabezellen) und „Formelzellen“ auf verschiedenen Tabellenblättern.
• Die verschiedenen Ebenen werden durch unterschiedliche Registerfarben erkennbar. Das ergibt eine einfache Form der Dokumentation (z.B. Basisdaten + Werte = schwarz, Verarbeitung = blau, Ausgabe = grün). Ein einheitlicher Farbcode sollte daher Bestandteil eines Standards sein.
• Einsatz von Namen für Zellen und Zellbereiche (Kommunikation mit dem Anwender!) sowie „Verknüpfungen“.
• Soll das Modell dynamisch sein, dann empfiehlt es sich mit Funktionen wie MTRANS(), INDEX(), VERGLEICH(), BEREICH.VERSCHIEBEN() bzw. deren Weiterentwicklung in Excel 365 (XVERWEIS, XVERGLEICH, SPALTENAUSWAHL, ÜBERNEHMEN u.a.) und ggf. Steuerelementen zu arbeiten.

Sind die Modelle durch den Aufbau und bestimmte verwendete Exceltechniken "standardisiert" und werden permanent verwendet, kann so Anwendungssicherheit in den Techniken erreicht werden. Mit der Anwendung der immer gleichen Techniken erzielen Sie bei allen Beteiligten eine große Zeitersparnis bei der Erstellung neuer Modelle.

Die Weiterbildung kann erheblich effizienter und zielgerichteter betrieben werden, da die für die Standard-Modellierung notwendigen Excel-Techniken zum Inhalt gemacht werden.

Die Excel-Dateien kann mit den Anforderungen wachsen und benötigt für die Steuerung in der Regel maximal zehn Funktionalitäten. Außerdem sie sehr klein (max. 2 MB), wenn Power Query eingesetzt wird!

Abb. 3: Das EVA-Prinzip mit Power-Query umgesetzt

Einmal erstellt, wird das Modell automatisch mit aktuellen Daten "gefüttert", berechnet und zeigt auf dem Frontend die aktuellen Ergebnisse an. Eine Nachbearbeitung der Daten und Formeln ist nicht mehr notwendig, eine Programmierung für eine Automatisierung auch nicht.

Für solch eine Modellierung haben sich nach meiner Meinung (abhängig von der Aufgabenstellung) folgende Excel-Techniken bewährt. Tabelle 1 bildet die Techniken für drei "typische" Aufgabenstellungen im Controlling ab:

Tab. 1.: Techniken für drei "typische" Aufgabenstellungen im Controlling. 

Ist Excel noch zeitgemäß? Aber ganz sicher! Mit den Prinzipien des Modern Excel!

Das könnte Sie auch interessieren:

Nicht Excel ist das Problem, sondern die Anwender!

Von Excel zu Power BI: Warum der Umstieg für Controller sinnvoll sein kann