Wie Sie mithilfe der Power Methode und dem Stern-Schema Ihr Excel-Berichtswesen automatisieren

Haben Sie es auch schon gehört? Business Intelligence oder kurz BI ist im Controlling und Rechnungswesen in aller Munde. Besonders die Vielfalt der BI-Systeme u.a PowerBI, machen es dem Anwender schwer die richtige Auswahl zu treffen.

In diesem Beitrag erfahren Sie:

• wie Sie OHNE BI ihr Excel- Berichtswesen automatisieren
• was sich hinter dem Stern-Schema verbirgt?
• und wie Sie gleichzeitig die Grundlagen von PowerBI kennenlernen können.

Im Grunde ist die Reise in Buchhaltung und Controlling von den (Roh-)Daten zum fertigen Bericht immer die gleiche und lässt sich in folgende Fragen untergliedern, die zu Beginn geklärt werden müssen:

1. DATENQUELLEN:

• Wo kommen die Daten her? D.h. welche Systeme liefern die relevanten Daten? 

2. AUFBEREITUNG:

• Wie gut sind die Daten?
• D.h. in welcher Form und Anordnung liegen die Daten vor und wie müssen diese noch aufbereitet werden?
• Hierbei sind die Begriffe "Daten-Liste" und "Daten-Tabelle" zu hinterfragen. Ferner kommt in diesem Punkt das Stern-Schema zum tragen.

3. VISUALISIERUNG:

• Wie soll der Bericht visualisiert werden?
• Als klassische Tabelle?
• als visuelle Tabelle?
• als Chart?
• oder lieber gleich ein Dashboard?

4. TEILEN:

• Wer soll den Bericht bekommen (Empfänger)?
• wie oft? (monatlich, wöchentlich, quartalsweise) 
• und in welcher Form soll das geschehen? (PDF, Excel, Mail, Sharepoint)

Im Folgenden werde ich genauer auf die vier Fragen eingehen und diese beantworten.

Um nun erfolgreich sein Excel-Berichtswesen automatisieren zu können benötigen Sie im besten Fall lediglich zwei Excel Werkzeuge, die Sie miteinander kombinieren.

• PowerQuery und Pivot.

Das bedeutet auch, dass Sie sich langfristig von Excel-Formeln wie:

• Sverweis,
• Verknüpfungen
• und manuellen Kopieren verabschieden müssen.

Und damit kommen wir zu Punkt 1 DATENQUELLEN:

• Achten Sie darauf, dass Sie für den Anfang mindestens Excel-Exporte oder CSV-Exporte aus Ihren Vorsystemen als "Daten-Tabelle" bekommen. Das erleichtert Ihnen die Übersicht und das Handling der Daten.
• Um sich nicht zu verzetteln und nur die relevanten Daten empfängergerecht aufzubereiten, empfehle ich den sogenannten "goldenen 3 Klang -die Power Methode", der im Folgenden erklärt wird:

1. Festlegen wie der fertige Bericht aussehen soll.

• Fragen Sie bewusst am Anfang den Empfänger welche Daten er im Bericht sehen will und zeigen Sie auch nur diese als Pivottabelle oder Pivot Chart.
• Pivot ist somit Ihr einziges Visualisierungstool.
• Verzichten Sie auf zusätzliche Kennzahlen, die "händisch" hart daneben geschrieben werden.
• Mit diesem Wissen können Sie Ihr Datenmodell schlank halten, da Sie nur die relevanten Datenquellen anzapfen (und NICHT ALLES! ;))

2. Datenquellen bestimmen.

• Mit dem Wissen aus 1 zapfen Sie nun Excel-Exporte oder CSV-Exporte als benannte "Daten-Tabelle" aus Ihren Vorsystemen an.
• Hierbei legen Sie gleich fest, um welche Art von Daten es sich handelt. Sind es Bewegungsdaten, also Daten die von Monat zu Monat mehr werden? Dann spricht man in diesem Fall von Faktentabellen (z.B Umsatz pro Mitarbeiter, Monat, Rechnungsnummer etc).
• Möchten Sie die Faktentabelle noch um zusätzliche Informationen anreichern, dann sind diese Informationen als Dimensionstabellen zu führen.
• Dimensionstabellen sind im Grunde Stammdaten, die sich im Gegensatz zu Faktentabellen wenig bis gar nicht ändern (z.B. Personalnummer, Kostenstelle oder Region). 

3. Aufbereitung der daten mit PowerQuery oder PowerPivot.

• Wenn es Ihnen gelingt Ihre Datenquellen klar als Fakten- und Dimensionstabellen abzurufen, ist die Aufbereitung der Daten nach dem klassischen Stern-Schema in PowerQuery oder und PowerPivot möglich.
• In der folgenden Abbildung wird das Stern-Schema erklärt.

Bildquelle: Bildausschnitt aus Mircosoft Excel 365 Power Pivot, eigene Datenbasis

• Das Stern-Schema beschreibt die Anordnung der Datentabellen Ihres Datenmodells und sorgt am Ende dafür, das Ihr Excel Bericht auch schnell und performant erstellt werden kann.
• Das Stern-Schema wird auch als Stern-Modell bezeichnet und hat seinen Namen von der sternförmigen Anordnung der Dimensionstabellen (im Bild Region und Kostenstelle) um die Faktentabelle in der Mitte (Im Bild Umsatz).
• Im Bild sehen Sie ein sehr kleines Datenmodell, was in PowerPivot mit nur 3 Tabellen erstellt wurde.
• Bei zunehmenden Dimensionstabellen zeigt das Stern-Schema seine wahre Kraft, nämlich die gute Übersicht.
• Das machte das Stern-Model bzw. Stern-Schema zu einem Klassiker in der Datenmodellierung.

Wie soll der Bericht visualisiert werden?

• Als klassische Tabelle,
• als visuelle Tabelle?
• als Chart?
• oder lieber gleich ein Dashboard?

Wenn Sie sich an den Anfang des "goldenen 3 Klangs" erinnern, dann stellen Sie fest, dass das Layout und die Optik des Excel-Berichtes ja zu Beginn festgelegt werden musste.

Das wichtige ist bei der Visualisierung Ihres Excel-Berichtes (egal ob Tabelle, Chart oder Dashboard), dass Sie wirklich AUSSCHLIESSLICH PIVOT verwenden.

Das ist nämlich das große Geheimnis und ermöglicht Ihnen klassische Formelfehler zu vermeiden und die Automatisierung Ihres Excel Berichtes.

• Mit Power Query erzeugte Datentabellen lassen sich wunderbar mit Pivot kombinieren. Somit nutzen Sie Power Query, um saubere relevante Datengrundlagen zu erzeugen und diese auf Knopfdruck zu aktualisieren.
• Pivot (oder PowerPivot bei besonders großen Datengrundlagen über 1 Mio. Zeilen) greift im Anschluss auf diese Daten zu und führt für Sie letzte Berechnungen oder Gruppierungen durch.
• Der fertige Bericht entsteht.

Wer soll den Bericht wie oft bekommen und in welcher Form soll das geschehen?

• Hier trennt sich die Spreu vom Weizen. Bis hier hin konnten Sie alles in Excel machen, indem Sie die Power Methode umsetzen und PowerQuery mit Pivot kombinieren.
• Der fertige Pivot-Bericht liegt nun in Excel vor und muss verteilt werden.
• Dies ist das Ende der Automatisierung Ihres Excel Berichtes. Denn Excel bleibt Excel!

• So schön Ihre Pivot-Tabellen mit dem performanten PowerQuery Datenmodell nach dem Stern-Schema auch sind, am Ende kommt immer eine Excel-Arbeitsmappe raus.
• Das muss Ihnen klar sein. Diese könnten Sie direkt oder als PDF per Mail verschicken, auf Sharepoint und Co ablegen oder ähnliches.

Für viele Unternehmen die gerade mit der Automatisierung oder Optimierung ihres Excel-Berichtswesens anfangen, ist das schon klasse und ein guter Anfang.

• Einfacher und professioneller geht das Teilen mit PowerBI.
• Überlegen Sie also vorher wie wichtig für Sie das Teilen des Berichtes ist.
• Bei Berichten die nur einmal monatlich an wenige Empfängergruppen gehen, reicht Excel völlig aus, bei steigender Berichtshäufigkeit und Empfängerzahl wäre BI besser. Das muss man ehrlich sagen.

• Prüfen Sie, ob Sie überhaupt eine Standard BI-Software („Taube auf dem Dach“) benötigen
• Prüfen Sie, ob Kompetenzen für eine BI-Software im Unternehmen vorliegen und ob ggf. ein selbsterstelltes pivotbasiertes Excel-Datenmodell ausreicht („Spatz in der Hand“)
• Erlangen Sie Kompetenzen über PowerQuery und Pivot durch passgenaue Excel Trainings wie z.B folgende: https://www.ralf-greiner.com/ , dann haben Sie es später viel leichter mit BI.
• Sammeln Sie mit Ihren aktuellen Excel PowerQuery und Pivot-Kenntnissen, Erfahrungen welche Berichte Ihnen wirklich helfen und satteln Sie dann erst auf BI um.

Viel Erfolg wünscht Ihnen

Ralf Greiner

Excel Experte und Geschäftsführer ralf-greiner.com