Dashboard erstellen in Microsoft Excel ist ein 3 teiliger Artikel in dem ich die Erstellung eines dynamischen Dashboards als Tutorial vorstelle. Dies ist Teil 3 des Artikels. Wenn Ihr den ersten oder zweiten Teil noch nicht gelesen habt, einfach unten auf den Link klicken!

Teil 1: Ein scrollbaren Listenbereich mit Optionsfelder erstellen

Teil 2: Dynamische-Bereiche und -Formatierungen

Teil 3: Dynamische Diagramme erstellen

Dynamische Diagramme

Im dritten und letzten Teil dieses Artikels schauen wir an wie man Diagramme mit den ausgewählten Daten verbindet und diese zeitgleich automatisch analysiert. Wir setzen auf dem bereits implementierten Dynamischen Dashboard von Teil 2 auf.

Die Aufgabe

Jeweils die 5 grössten und 5 kleinsten Werte in einer Datenreihe hervorheben und den Gesamtverbrauch nach beliebigem Jahr mit dem heutigen Status per anklicken vergleichen.

Die Lösung

Wir benutzen Matrix Formeln

um die ausgewählten Datenreihen dynamisch in ein Diagramm einzubinden.

5 größten und kleinsten Werte in einem Diagramm

Wie immer könnt Ihr Euch die Datei mit dem Dashboard hier anschauen: Dashboard Tutorial – Teil 3 (Fertig)

Auf der Tabelle “Daten” wurde drei neue benannte Bereiche im Namens-Manager erstellt. Graph2, GraphMax, und GraphMin. Diese dienen uns um die Werte in der Rubrik welche wir auswählen (z.B. Fernwärme) in Kategorien einzuteilen. Die Kategorien heißen: 5 grössten Werte (GraphMax), 5 kleinste Werte (GraphMin), oder ein normaler Wert (Graph2).

Das Ziel ist, dass wir die Werte in der Rubrik in verschiedene Datenreihen aufteilen, denn nur so können wir die Maximum Werte und Minimum Werte mit einer anderen Farbe anzeigen im Diagramm (plus eine Reihe für die “normalen” Werte).

Im Bereich Graph1 haben wir die Datenserie der Rubrik welche angeklickt wurde.

Wir benutzen eine Matrix Formel um die 5 grössten Werte im Bereich GraphMax auszuwerten. Hier die Formel aus Zelle X9:

{=WENN(V9>=KGRÖSSTE(Graph1;5);V9;0)}

Die Auswertung für die 5 kleinsten Werte werden ebenfalls mit einer Matrix Formel ermittelt. Hier Zelle Y9:

{=WENN(V9<=KKLEINSTE(Graph1;5);V9;0)}

Zuletzt benötigen wir alle Werte welche nicht zu den 5 grössten oder kleinsten Werte in der Datenreihe gehören. Diese erstellen wir unter Graph2 mit einer einfachen WENN Formel. Hier aus Zelle W9:

=WENN(SUMME(X9:Y9)=0;V9;0)

Somit haben wir drei Datenreihen welche zusammen alle Werte aus Graph1 wiedergeben: 5 grössten Werte (GraphMax), 5 kleinste Werte (GraphMin), oder ein normaler Wert (Graph2).

Wir führen die drei Kategorien in einem normalen Balkendiagramm zusammen und stellen die Reihenachsenüberlappung im Datenreihen formatieren Fenster auf 100% . Die Farbe der verschiedenen Datenreihen ist dann noch Geschmackssache!

So sieht der Leser schnell dass der Energieverbrauch für Fernwärme in den letzten Jahren jeweils immer wieder neue Rekorde gebrochen hat. Man könnte daraus den Rückschluss ziehen dass Fernwärme das nächste riesen Ding wird!

Um dies zu bestätigen wäre aber ein Vergleich aller Energietypen im selben Jahr interessant! Ansonsten könnte die einfach die Macht der kleinen Zahlen wiederspiegeln!

Gesamtverbrauch nach beliebigem Jahr vergleichen

Um den Lesern unseres Dashboards den Gesamtverbrauch zu präsentieren fügen wir ein Vergleichsdiagramm ein, welches die Werte aller Energietypen in einem ausgewählten Jahr anzeigt und mit dem aktuellsten Jahr 2010 vergleicht. So bekommt der Leser schneller einen Kontext für die Daten!

Die Werte welche wir vergleichen werden wiederum per anklicken ausgewählt.

Mit einem weiteren Bereich auf dem Tabellenblatt “Daten” erstellen wir zwei Datenreihen welche dem Diagramm zugeordnet werden können.

Während die Datenreihe für 2010 statisch ist, müssen wir die zweite dynamisch mit einer Auswahl verbinden. Die Formel in Zelle A4 ist verbunden mit dem OnClick Bereich. Die Formel in Zelle B4 heißt:

=INDEX($A$9:$H$39;VERGLEICH($A4;Zeit;0);SPALTE(B2))

Somit werden jeweils die richtigen Jahreswerte in das neue Diagramm eingespeist.

Das Fertige Dashboard

Unser Dashboard ist fertig erstellt und somit haben wir das Ende dieses Tutorials erreicht!

Wenn Ihr mehr Tipps und Tricks zu Dashboards, Dynamischen Bereichen, oder automatisierten Berichten lesen möchtet, könnt Ihr den Newsletter abonnieren damit Ihr zukünftige Artikel zum Thema nicht verpasst!