Dashboard erstellen in Microsoft Excel ist ein 3 teiliger Artikel in dem ich die Erstellung eines dynamischen Dashboards als Tutorial vorstelle.

Teil 1: Ein scrollbaren Listenbereich mit Optionsfelder erstellen

Teil 2: Dynamische-Bereiche und -Formatierungen

Teil 3: Dynamische Diagramme erstellen

Für dieses Dashboard habe ich Daten vom Schweizerischen Bundesamt für Statistiken genommen für die Endverbraucher-Ausgaben für Energie in der Schweiz zwischen 1980-2010.

Was ist ein Dashboard?

Das Wort Dashboard kommt aus dem Englischen und steht für Armaturenbrett. Heute wird das Wort jedoch sehr oft benutzt um einen Bericht zu beschreiben der verschiedenste Betriebsindikatoren auf einem einzigen Bildschirm zusammenfasst. Idealerweise bietet ein Dashboard auch gewisse Möglichkeiten an Daten interaktiv / dynamisch zu analysieren.

Der scrollbare Listenbereich

Das Problem

Man hat mehr Daten als auf einem Bildschirm platz haben. Meistens werden im Berichtswesen dann die Daten zusammengefasst, es wird nur ein Teil der Informationen angezeigt, oder die Font wird so klein gemacht dass die Informationen kaum lesbar sind.

Die Lösung

Die Lösung zu diesem Problem ist einen scrollbaren Listenbereich im Dashboard zu erstellen. Die Datei mit dem Dashboard-Tutorial-1-Teil-1 kann man hier herunterladen.

Die Tabelle ist gut lesbar ohne dass viel Platz auf dem Bildschirm verloren geht oder die Informationen für den Leser stark eingeschränkt werden.

Wie man einen scrollbaren Listenbereich erstellt

Über das Entwicklertools Menü wird eine Leiste zum Verschieben eingefügt (befindet sich unter Formularsteuer Elemente).

Die Zellverknüpfung verweist auf ein Hilfsdatenblatt welches im Beispiel nur benutzt wird für die Steuerelemente. Die Zelle Stage!C2 wurde im Namens-Manager als Scrollbar definiert.

Die Jahreswerte wie auch den Datenbereich werden mit einer BEREICH.VERSCHIEBEN Formel nun an die Zellverknüpfung des Steuerelementes gebunden.

Die Formel in der Zelle B8 sieht so aus…

=BEREICH.VERSCHIEBEN(Daten!A9;Scrollbar;0)

Wer detailliertere Angaben braucht, kann sich auch noch diesen Artikel anschauen in dem das erstellen eines Scrollbereiches Schritt für Schritt erklärt wird.

Wie man Optionsfelder erstellt und verknüpft

Die zwei Optionsfelder mit denen wir die Daten entweder als Nominalwerte oder als Prozentwerte anzeigen wollen, werden ebenfalls via Entwicklertools Menü und Formularsteuer Elemente eingefügt.

Damit wir die Original Werte, welche alle Nominal Werte waren, auch als Prozent sehen, müssen wir diese zuerst berechnen. Im Arbeitsblatt Daten wurde deshalb eine Hilfstabelle parallel zur Originalliste erstellt, welche die Prozente berechnet. Diese zweite Tabelle ist um exakt 11 Spalten vom Original nach Links verschoben.

Anmerkung: Natürlich hätte man dies auch ohne Hilfstabelle machen können und einfach per Formel alle Prozentwerte direkt kalkulieren lassen. Ich bin jedoch der Auffassung dass Hilfstabellen dazu beitragen die Übersicht zu bewahren. Dies insbesondere in größeren Excel Modellen oder in Erklärungsbeispielen so wie diese Datei.

Die Zellverknüpfung der Optionsfelder verweisen wiederum auf das Arbeitsblatt “Stage” auf die Zelle C3. Die Zelle D3 enthält eine WENN Formel die prüft ob das erste oder das zweite Optionsfeld ausgewählt ist. Wenn das zweite Optionsfeld (also die Prozentansicht) ausgewählt wurde so gibt die Formel 11 zurück. Wieso 11? Weil dies den Anzahl Spalten entspricht um welche die Hilfstabelle die erstellt wurde gegenüber der Originaltabelle verschoben ist!

Die Zelle D3 habe ich mit dem Namens-Manager benannt: OptionButtonMultiplier

Wenn wir nun zurück auf das Dashboard gehen und die Formel in der Zelle D8 anschauen, so steht dort:

=BEREICH.VERSCHIEBEN(Daten!I9;Scrollbar;OptionButtonMultiplier)

Diese BEREICH.VERSCHIEBEN Formel können wir nun eins zu eins kopieren und auf den Rest des Datenbereiches einfügen. So können wir jeweils die Daten welche im Optionsfelds ausgewählt wurden anzeigen!

Somit haben wir den scrollbaren Listenbereich und die Optionsfelder erfolgreich eingefügt!

Was kommt als nächstes?

Im nächsten Teil dieses Artikel schauen wir uns an wie Dynamische-Bereiche und Dynamische-Formatierungen ein Dashboard viel interaktiver machen können!

Lies den nächsten Teil dieses Artikels: Teil 2 – Dynamische-Bereiche und -Formatierungen