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
zu schwer und nicht ausführlich genug erklärt für jemanden, der mit diesen Funktionen noch nie zuvor gearbeitet hat!
Hi Ines, da hast Du sicher recht. Für jemand der neu Anfängt ist dies sicher nicht das beste Tutorial. Schau Dir doch einfach mal ein paar der anderen Artikel an auf der Homepage. Da findest Du zu allem was in diesem Tutorial vorkommt einzelne Artikel.
Gruss, Lukas
Ich verzweifle an diesem Tutorial. Für einen nicht-absoluten-Excel-Crack ist dies nicht zu knacken.
Ich habe den Eindruck, dass nicht alles beschrieben ist, was es zu beachten gibt. Auch finde ich nicht die Einzelerklärungen auf dieser Seite, wie Lukas das empfiehlt.
Ich finde es gut beschrieben und anhand der Beispieldatei kann man die Schritte auch selber nachschauen. Allerdings ergibt sich bei mir das Problem, dass scheinbar der OnClick Code nicht richtig funktioniert. Wenn ich es in deiner Beispieldatei nachbaue (neues Tabellenblatt, Code einfügen, etc.) klappt es einwandfrei. In einer neuen Datei klappt es aber leider nicht mehr.
Hi Christian, Du musst auch einen Benannten Bereich (eine Zelle) erstellen die “OnClick” heisst und welche mit den Steuerungselementen verbunden ist.
Hallo Lukas, danke für den Tip! Zehn Minuten nach meinem Kommentar habe ich es rausgefunden. Bin aber noch nicht dazu gekommen hier zu Antworten.
Ich finde das Tutorial bisher verständlich. Doch nun scheitere ich auch am VBA. Den Code habe ich in VBA hinterlegt. Eine Zelle die OnClick benamt ist auch. Doch wenn ich auf meine Spaltenüberschriften klicke erscheint jedoch im Stage Reiter nicht der Name und die Formatierung greift dann nicht.
Hallo Lukas,
Ich bin zum ersten Mal mit dem Thema VBA in Berührung gekommen und bleibe an der OnClick-Funktion hängen.
Ich habe den Code ([…]OnClick=Target […]) in meine Excel kopiert und ebenso die Formatierungsregeln “abgeschrieben” (eben mit meiner Zeilen-/Spaltenzahl) und es passiert…nichts. Wo kann ich nach dem fehlenden Argument suchen?
Vielen Dank für eine baldige Antwort,
Nina
Vielen Dank! Einwandfrei!