Dashboard erstellen in Microsoft Excel ist ein 3 teiliger Artikel in dem ich die Erstellung eines dynamischen Dashboards als Tutorial vorstelle. Dies ist Teil 2 des Artikels. Wenn Ihr den ersten 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-Bereiche

Ein Dashboard ohne interaktive oder dynamische Elemente, ist nur ein Bericht. Im ersten Teil dieses Artikels haben wir unserem Dashboard bereits ein dynamisches Element gegeben mit dem scrollbaren Listenbereich und den Optionsfelder. Wir wollen dem User nun auch noch die Möglichkeit geben gewisse Informationen genauer anzuschauen und die Daten besser zu verstehen.

Die Aufgabe

Einzelne Datenreihen oder Rubriken per einfachem anklicken hervorheben und dem User zur genaueren Analyse präsentieren.

Die Lösung

In diesem Fall heißt die Lösung dynamische Bereiche und dynamische Formatierungen verbunden mit einer einzigen Zeile VBA Code!

Wie man Daten dynamisch Formatiert

Zum selber anschauen, ausprobieren, und studieren könnt Ihr das Dashboard-Tutorial-2-Teil-2 hier herunterladen.

Auswählen per Anklicken

Wir brauchen also eine Möglichkeit eine Zelle durch einfaches anklicken zu verändern. Diesen Wunsch erfüllt uns die VBA Prozedur Worksheet_SelectionChange und der Namens-Manager in Kombination mit den bedingten Formatierungen.

Der Code muss auf dem gleichen Tabellenblatt auf dem es angewandt werden soll eingegeben werden:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
[OnClick] = Target
End Sub

Na schön… es sind insgesamt vier Zeilen Code, aber das Herzstück ist nur eine Zeile!

[OnClick] = Target

Wo [OnClick] eine Zelle darstellt welche im Namens-Manager mit eben diesem Namen erfasst wurde.

Dynamische Formatierung erstellen

Die Bedingte Formatierung ist natürlich schon von Haus aus dynamisch, da sie ja wie der Name sagt, eine Formatierung vornimmt, basierend auf einer Bedingung. Unsere Bedingung soll sich aber per einfachem anklicken ändern können.

Damit wir die Bedingung an ein Zellenauswahl knüpfen können, müssen wir eine Formel einsetzen die auf OnClick Bezug nimmt.

Für die Zelle D7 habe wir das mit der einfachen Gleichung =D7=OnClick gemacht. Nun wird die Formatierung jedes mal aktiviert wenn der Wert von OnClick gleich ist wie derjenige von der Aktiven Zelle.

Mit demselben Verfahren bearbeiten wir anschliessend alle Rubriken mit einer farblichen Markierung und fetter Schrift so dass sie hervorgehoben werden wenn diese ausgewählt werden.

Dynamische Bereiche erstellen

Wir möchten ein Diagramm erstellen welches dem User den Energieverbrauch der letzten 30 Jahre auch graphisch aufzeigt. Da ein statisches Diagramm einem normalen Bericht gegenüber keinen Mehrwert bietet, soll es auch mit der Rubriken Auswahl des Users verknüpft werden und so gezielt Daten anzeigen.

Diese wird gemacht in dem wir auf dem Daten Tabellen Blatt die Zelle V8 im Namens-Manager Graph1Titel Bennen und folgende Formel einfügen…

=WENN(ISTNV(VERGLEICH(OnClick;EnergyTypes;0));”Total”;OnClick)

In Worte gefasst sagt die Formel folgendes: Wenn eine Zelle mit dem Titel einer Rubrik ausgewählt ist, dann liefere dessen Namen, ansonsten schreib einfach Total.

Der Datenbereich, welchen wir nun Graph1 nennen werden in Namens-Manager, wird per Formel erstellt. Als Beispiel steht in Zelle V9:

=INDEX(INDIREKT(Graph1Title);ZEILE(A1))

Damit die korrekten Daten über die Indirekt und Index Formeln auch zurückgegeben werden, müssen sämtliche Datensätze mit der jeweiligen Rubriken Überschrift im Namens-Manager erfasst sein.

Sind diese Schritte gemacht ist es nicht mehr sehr viel Arbeit ein Diagramm zu erstellen und den Bereich Graph1 als Datenbereich auszuwählen. Graph1Titel kann wiederum auf gleiche Weise mit dem Diagramm Titel verbunden werden. Wichtig ist bei der Erstellung des Diagrammes lediglich dass man bei der Eingabe des benannten Bereiches zuerst noch das Datenblatt angibt.

in unserem Beispiel:  =Daten!Graph1

Jetzt haben wir bereits ein anschauliches dynamisches Dashboard mit Diagramm erstellt!

Was kommt als nächstes?

Im dritten und letzten Teil dieses Artikels schauen wir an wie man Diagramme mit den ausgewählten Daten verbindet und diese zeitgleich automatisch analysiert.

Lies den nächsten Teil dieses Artikels: Teil 3