Haben Sie schon einmal versucht ein dynamisches Reporting-Modell mit Excel aufzubauen? Nach meiner Erfahrung entstehen dabei meist sehr große, kaum handhabbare Dateien. Dass es auch anders geht, möchte ich mit diesem Beispiel zeigen. Dabei sollen mehr die Vorgehensweise und Prämissen beschrieben werden, denn konkrete Excel-Techniken.
Unserer Philosophie folgend, setzen wir Excel ausschließlich als Frontend für ein Backend ein. Das Frontend ist dann z.B. ein Reporting-Modell, das Backend eine Datenbank, die die für das Frontend notwendigen Daten aggregiert und in der notwendigen Form verdichtet in das Frontend hoch lädt. Da so nur relative wenige Datensätze in das Frontend gelangen, ist die Frontend-Datei in der Regel recht klein, meist max. 1-2 MB groß.
Ein Beispiel für solch ein Frontend finden Sie hier. Das Beispiel stammt aus unserem Seminar Dashboards mit Excel erstellen und ist stark vereinfacht und reduziert.
Modellierung
Zunächst einmal ist es nach unserem Modellierungsprinzipien aufgebaut. Dies ist an den verschiedenfarbigen Registern erkennbar. Diese Modellierungsprinzipien haben wir vor etwa 20 Jahren entwickelt, als wir regelmäßig einen größeren Änderungsaufwand bei der Weiterentwicklung von Excel-Dateien hatten. Seither nicht mehr!
Funktionen
Dann wird in diesem Modell mit benannten Funktionen gearbeitet, was u.a. die Anzahl der Zeichen in den einzelnen Zellen reduziert und so für eine geringe Dateigröße sorgt. Das Tabellenblatt „Liste der Namen“ dient als Dokumentation. Wir arbeiten sehr viel mit Matrix-Funktionen, meist mit BEREICH.VERSCHIEBEN, INDEX, MTRANS, VERGLEICH; ZEILE.
Was wird dargestellt?
In dem vollständigen Modell ist eine wahlweise Darstellung von Monats- und Quartalszahlen möglich, ergänzt um eine optionale Darstellung der Vorjahreswerte.
Hinzu kommen (nicht in diesem Beispiel)! eine umfangreiche Abweichungsanalyse sowie ein Absatzprognosemodell auf der Basis einer Regressionsanalyse. Das Tabellenblatt „Abweichungen“ stellt nicht die wahre Abweichungsanalyse dar. Beobachten Sie bitte hier, welche Zeilen hervorgehoben sind. Dies ist die auf dem Tabellenblatt „Dashboard“ ausgewählte Vertriebsregion! Diese Art der Benutzerführung erzielen Sie mit Benutzerdefinierter Formatierung und habe ich im Rahmen dieses BLOGS schon einmal vorgestellt (hier…).
Steuerung
Gesteuert wird das Modell über Hyperlinks und „Schaltflächen“. Damit werden die Berichtsempfänger zu bestimmten Tabellenblättern „gelotst“. Auf dem Tabellenblatt „Menü“ kann über die Schaltfläche „Mail an das Controlling“ Kontakt mit dem Berichtsersteller aufgenommen werden. Sei es, um Optimierungsvorschläge zu machen, sei es um Fragen zu stellen. Auf jeden Fall entsteht so ein Kontaktangebot. Beachten Sie den Kommentar direkt daneben!
Kommentare werden genutzt, um dem Anwender wichtige Informationen „bei Bedarf“ zu vermitteln. Hier in diesem Modell sind es in erster Linie für die Seminarteilnehmer die einzutragenden Funktionen. Aber auf dem Tabellenblatt „Menü“ finden Sie in zwei Kommentaren grafische Abbildungen vor, nach der Devise: „Ein Bild sagt mehr als 1.000 Worte!“
Konzept
Insgesamt ist das Modell „vom Ziel“ her gedacht:
- Welche Information/Aussage/Handlungsempfehlung/Aufforderung soll vermittelt werden?
- Soll das mit einer Tabelle oder einem Diagramm geschehen.
- In welcher Form müssen dafür Daten vorliegen?
- Genau in diesem Form und Umfang werden die Daten aus dem Backend hoch geladen und eventuell in geringem Umfang ergänzt (z.B. Tabellenblatt „Diagrammdaten“).
Mit dieser Methodik erhalten auch Sie kleine, schlanke, hoch performante Modelle. Eine umfangreiche Dokumentation, eine Beschreibung der verwendeten Excel-Techniken, des Datenflusses im Modell ist dabei selbstverständlich und ansatzweise für vorgenommene Änderungen auf dem Tabellenblatt „Dokumentation“ aufgelistet.
Verschiedene Ansichten => Bearbeiter/Betrachter
Über das Menü Ansicht • Arbeitsmappenansicht • Benutzerdefinierte Ansichten können Sie zwischen einer „Arbeitsansicht“ und einer „Berichtsansicht“ umschalten. Mit einer Ansicht kann eine Datei gespeichert werden und wird auch so wieder geöffnet.
Soweit einmal dieses kleine Beispiel, das Ihnen hoffentlich ein paar Anregungen liefert. Über ein Feedback würde ich mich freuen, auch über eine Empfehlung dieses BLOG-Beitrages. Wenn Sie bereits Ähnliches entwickelt haben, nur auf einem anderen Weg, mit anderen Techniken, freue ich mich über einen Erfahrungsaustausch.
Wie sollten Sie insgesamt vorgehen? In einem anderen Beitrag ist dies in 10 Schritten beschrieben.
