Das Erstellen von Microsoft Excel-Dashboards ist für jeden, der mit Daten arbeitet, eine wichtige Fähigkeit. Excel-Dashboards ermöglichen es Entscheidungsträgern, Daten aus ihrem Betrieb zu bewerten und fundierte Entscheidungen zu treffen. Ich werde zeigen, wie man als Excel Anfänger interaktive Dashboards auf Basis von Dropdowns erstellt.
Um einen realistisches Dashboard zu erstellen habe ich mir überlegt anhand der Passagierzählung S-Bahn Hamburg, die uns die Deutsche Bahn in ihrem Open Data Portal bereitstellt (https://data.deutschebahn.com/dataset/passagierzahlung-s-bahn-hamburg), ein Dashboard über die wichtigsten Bahnhöfe in Hamburg zu erstellen.
Nachdem wir die Datei heruntergeladen und geöffnet haben, können wir uns schon einmal die Rohdaten ansehen.
Wir sehen Datensätze vom Zeitraum von 10.12.2016 bis zum 31.03.2017. Die gelieferten Daten sind nicht weiter behandelte Daten (Rohdaten), auch wurde kein Saldenausgleich vorgenommen worden, das bedeutet das am Ende der Fahrt nicht zwangsläufig die Summe der Ein-Summe der Aussteiger Null ergeben.
Spalten- oder Tabellenbeschreibung
Zugnummer
Station (klar)
Einsteiger (roh)
Aussteiger (roh)
Ist Ankunft mit Datum und Zeit
Ist Abfahrt mit Datum und Zeit
DS100 kurz (hier ist vorne das “A” weggelassen)
Linie
Ersteinmal sollten wir die Daten im XLSX Format speichern und eine neue Tabelle für das Dashboard hinzufügen. Die reinen Daten, bzw. das Datentabellenblatt werden wir nicht ändern. Wenn wir aktualisierte Daten im gleichen Format bekommen, können wir so diese Daten direkt neu einfügen und das Dashboard wiederverwenden
Tabellenblatt in RawData umbenennen
RawData
Für ein Dashboard benötigen wir alle eindeutigen Bezeichnungen für die Stationen. Hierfür können wir mit der Excel Funktion EINDEUTIG eine Liste mit allen eindeutigen Werten erstellen. Da die erste Zeile für den Header verwendet wird und wir nicht wissen wieviele Zeilen neue Rohdaten beinhalten, müssen wir das Ende der Liste dynamisch mit der INDEX und ANZAHL2 ermitteln
Hier die Formel für die eindeutigen Stationsnamen:
Dann müssen wir die Ein- und Aussteiger für die Einzelnen Monate summieren. Hier die Formel für die Einsteiger.
Neben dem Filter auf die im Dropdown ausgewählte Station ```DashboardStationen!$F$2```, filtern wir auch noch auf den in der Zeile 8 angegebenen Monat mit
```>"&DATUM(JAHR(DashboardStationen!E8)``` und ```"<"&DATUM(JAHR(DashboardStationen!E8);MONAT(DashboardStationen!E8)+1```
Über "Einfügen"->"Sparklines" erstellen wir jetzt noch ein kleines Diagram in einer Zelle um die Änderungen über den Monat zu visualisieren.
Sparkline
Als nächstes können wir noch die Linien die die ausgewählte Station anfahren ausgeben. Hier hilft wieder die Funktion [EINDEUTIG](https://support.microsoft.com/de-de/office/eindeutig-funktion-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e), hier in Kombination mit [FILTER](https://support.microsoft.com/de-de/office/filter-funktion-f4f7cb66-82eb-4767-8f7c-4877ad80c759). Hier selektieren wir alle Linien (RawData!H:H) die an der ausgewählten Station halten (RawData!B:B=DashboardStationen!$F$2), anschließend werden alle eindeutigen Linien ausgegeben.
Anschließend summieren wir wieder mit[SUMMEWENNS](https://support.microsoft.com/de-de/office/summewenns-funktion-c9e748f5-7ea7-455d-9406-611cebce642b)die Einsteiger, diesmal aber mit einem zweiten Kriterium für die Linie:
Auch hier können wir Sparklines zur Visualisierung hinzufügen.
Einsteiger nach Linien
Aussteiger können analog summiert werden.
Eine interessante Fragestellung wäre jetzt natürlich auch zu welchen Zeiten die Stationen frequentiert werden. Für die Analyse wäre alles in den Rohdatensätzen da, aber die Daten müssen noch hierfür noch aufbereitet werden da die [SUMMEWENN Funktion](hhttps://support.microsoft.com/de-de/office/summewenn-funktion-169b8c99-c05c-4483-a712-1697a653039b) den kompletten Timestamp für den Vergleich heranzieht, und nicht nur die Stunde.
Wir können aber in der RawData Tabelle eine neue Spalte einfügen und die Stunde aus dem dtmIstAnkunftDatum extrahieren.
=STUNDE(E2:E610671)
Anschließend können wir wieder mit [SUMMEWENNS](https://support.microsoft.com/de-de/office/summewenns-funktion-c9e748f5-7ea7-455d-9406-611cebce642b) alle Einsteiger einer Station zu einer bestimmten Stunde summieren.
Zum Visualisieren müssen wir uns aber etwas anderes überlegen, da Sparklines nur horizontal funktionieren. Wir können Werte nur in Säulen oder einer Linie darstellen. Vertikale Balken können wir aber mittels bedingter Formatierung realisiere, hierfür erstellen wir über "Bedingte Formatierung" ->"Datenbalken" -> "Weitere Regeln" eine neue Formatierungsregel.
Einsteiger nach Uhrzeit
Wenn beim anlegen der Regel "nur Datenbalken anz." angekreuzt wird, werden die Zahlen ausgeblendet.
Einsteiger nach Uhrzeit
Das Dashboard sieht nun so aus:
Finale Dashboard
Mir gefällt die Datapreperation direkt im Excel aber nicht, da diese bei jeder Aktualisierung des Datensatzes erneut erzeugt werden müsste.
Möglichkeiten zur Datapreperation mit Talend Pipeline Designer zeige ich in einem der folgenden Beiträge.
