Dashboard erstellen in Excel – Tutorial [Teil 2 von 3]

Dashboard Tutorial 2 - Dynamische Bereiche

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

image

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

image

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.

image

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.

SNAGHTML316f40e

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!

Dashboard Tutorial 2 - Dynamische Bereiche[4]

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

Comments

  • August 6, 2012

    […] den nächsten Teil dieses Artikels: Teil 2 – Dynamische-Bereiche und -Formatierungen Empfehlen:FacebookE-MailDruckenRelated Posts via CategoriesWie man ein Scrollbares Dashboard […]

  • zu schwer und nicht ausführlich genug erklärt für jemanden, der mit diesen Funktionen noch nie zuvor gearbeitet hat!

    InesJanuary 13, 2014
  • 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

    Lukas RohrJanuary 13, 2014
  • 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.

    TomasSeptember 7, 2014
  • 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.

    ChristianJanuary 13, 2015
    • Hi Christian, Du musst auch einen Benannten Bereich (eine Zelle) erstellen die “OnClick” heisst und welche mit den Steuerungselementen verbunden ist.

      Lukas RohrJanuary 13, 2015
  • 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.

    ChristianJanuary 13, 2015
  • 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.

    MelanieSeptember 29, 2016
  • 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

    NinaOctober 27, 2016

Leave a Reply