Das Erstellen von Microsoft Excel-Dashboards ist eine wichtige Fähigkeit für jeden, der mit Daten arbeitet. Mithilfe von Excel-Dashboards können Entscheidungsträger die Daten ihrer Organisation auswerten und fundierte Entscheidungen treffen. Ich werde zeigen, wie Excel-Anfänger interaktive Dashboards auf der Basis von Dropdowns erstellen können.
Um ein realistisches Dashboard zu erstellen, habe ich mir überlegt, auf Basis der Fahrgastzählung der S-Bahn Hamburg, die uns die Deutsche Bahn in ihrem Open Data Portal (https://data.deutschebahn.com/dataset/passagierzahlung-s-bahn-hamburg) zur Verfügung stellt, ein Dashboard über die wichtigsten Bahnhöfe in Hamburg zu erstellen.
Nach dem Herunterladen und Öffnen der Datei können die Rohdaten eingesehen werden. Wir sehen Datensätze für den Zeitraum vom 10.12.2016 bis zum 31.03.2017. Die gelieferten Daten sind nicht weiter aufbereitete Daten (Rohdaten), es wurde auch kein Saldenausgleich vorgenommen, d.h. am Ende der Fahrt ist die Summe der Eingänge nicht unbedingt gleich der Summe der Ausgänge.
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
Zuerst müssen wir die Daten im XLSX-Format speichern und eine neue Tabelle für das Dashboard hinzufügen. Die eigentlichen Daten bzw. das Datenblatt werden nicht verändert. Wenn wir aktualisierte Daten im gleichen Format erhalten, können wir diese direkt wieder einfügen und das Dashboard wieder verwenden.
Tabellenblatt in RawData umbenennen
Für eine Übersichtstabelle benötigen wir alle eindeutigen Bezeichnungen der Stationen. Dazu können wir mit der Excel-Funktion EINDEUTIG eine Liste mit allen eindeutigen Werten erstellen. Da die erste Zeile für die Kopfzeile verwendet wird und wir nicht wissen, wie viele Zeilen neue Rohdaten enthalten, müssen wir das Ende der Liste dynamisch mit INDEX und ANZAHL2 bestimmen.
Hier die Formel für die eindeutigen Stationsnamen:
=EINDEUTIG(RawData!B2:INDEX(RawData!B:B;ANZAHL2(RawData!B:B)))
Anhand der Stationsnamen können anschließend dann die Ein- und Aussteiger mit SUMMEWENN berechnet werden
=SUMMEWENN(RawData!$B:$B;Dashboard!$B7;RawData!C:C)
Summieren der Ein und Aussteiger
Da dieses Dashboard jedoch nur sehr aggregierte Informationen liefert, werden wir ein detaillierteres Dashboard erstellen und mit dem Design beginnen:
Um die Daten für die einzelnen Station auswählen zu können, erstellen wir zunächst ein Dropdown zur Datenauswahl
Und legen dann die eben erstellte Liste als Quelle fest.
Dropdown Liste festlegen
Anschließend können wir in dem Dropdown die Stationsnamen auswählen
Als erstes Summieren wir alle Ein- und Aussteiger über den gesamten Zeitraum mit SUMMEWENNS:
=SUMMEWENNS(RawData!$C:$C;RawData!$B:$B;DashboardStationen!$F$2)```
Einsteiger Summieren
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```
=SUMMEWENNS(RawData!$C:$C;RawData!$B:$B;DashboardStationen!$F$2;RawData!$E:$E;">"&DATUM(JAHR(DashboardStationen!E8);MONAT(DashboardStationen!E$8););RawData!$E:$E;"<"&DATUM(JAHR(DashboardStationen!E$8);MONAT(DashboardStationen!E$8)+1;))
Summieren per Monat
Über "Einfügen"->"Sparklines" erstellen wir jetzt noch ein kleines Diagram in einer Zelle um die Änderungen über den Monat zu visualisieren.
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.
=EINDEUTIG(FILTER(RawData!H:H;RawData!B:B=DashboardStationen!$F$2))
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:
=SUMMEWENNS(RawData!$C:$C;RawData!$B:$B;DashboardStationen!$F$2;RawData!H:H;DashboardStationen!$B15)
Für die einzelnen Monate fügen wir das zusätzliche Kriterium analog hinzu:
=SUMMEWENNS(RawData!$C:$C;RawData!$B:$B;DashboardStationen!$F$2;RawData!$H:$H;DashboardStationen!$B15;RawData!$E:$E;">"&DATUM(JAHR(DashboardStationen!E$8);MONAT(DashboardStationen!E$8););RawData!$E:$E;"<"&DATUM(JAHR(DashboardStationen!E$8);MONAT(DashboardStationen!E$8)+1;))
Auch hier können wir Sparklines zur Visualisierung hinzufügen.
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.
=SUMMEWENNS(RawData!$C:$C;RawData!$B:$B;DashboardStationen!$F$2;RawData!$I:$I;"="&STUNDE(DashboardStationen!$B37))
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.
Wenn beim anlegen der Regel "nur Datenbalken anz." angekreuzt wird, werden die Zahlen ausgeblendet.
Das Dashboard sieht nun so aus:
