Der Artikel Wachstums Analyse Dashboard ist ein 2 teiliger Artikel in dem ich die Erstellung eines dynamischen Dashboards in Excel als Tutorial erkläre. Dies ist Teil 2 des Artikels. Wenn Ihr Teil 1 lesen möchtet könnt Ihr auf den Link unten klicken.

Teil 1: Erstellung des Grafischen Elements mit der dynamischen Orts Anzeige

Teil 2: Wie man eine Top Ten Liste mit Auswahlkriterium aus einer Datenquelle erstellt

Top und Flop Liste erstellen

Top und Flop Listen sind in Management Berichten sehr beliebt. Sie geben dem Leser schnell Auskunft darüber welche Kunden, Produkte, etc. die Renner sind, und welche aus dem Rennen genommen werden sollten.

In einem Dashboard können wir die sonst statische Liste jedoch ein wenig aufpeppen in dem wir eine Auswahl zur Verfügung stellen nach welcher Kennzahl ausgewertet werden soll.

Die Auswahl in unserem Dashboard wird durch ein ActiveX-Steuerelement angeboten.

Schritt 1 – ListBox Einfügen

Das ActiveX-Steuerelement fügen wir über das Entwicklertools Menü>Einfügen>Listenfeld in das Dashboard ein.

Da wir jeweils nach nur einem Kriterium Sortieren können, darf auch nur eine Kennzahl ausgewählt werden. Aus dem Grund setzen wir unter ListStyle 1-fmListStyleOption. Weitere Einstellungen könnt Ihr dem Screenshot entnehmen.

Somit haben wir nun eine Zelle (B82) in der wir die Auswahl des Users angezeigt bekommen. Nun geht es darum diese mit den Daten zu verknüpfen!

Schritt 2 – Rang Liste erstellen

Die Top Ten Rang Liste ist hier einfach gehalten. Wir gehen davon aus dass wir immer mindestens 10 Datensätze haben. Somit ist die Top Ten Liste einfach bis 10 durchnummeriert.

Weil wir vielleicht in Zukunft Ortschaften hinzufügen oder löschen müssen (z.B. wegen Gemeindezusammenschlüssen) wollen wir die Bottom Five Rangliste dynamisch gestalten.

Dies machen wir in dem wir die Anzahl Gemeinden in der Datenliste ermitteln. Im Beispiel tue ich dies mit der Formel:

=ANZAHL2(tblAargau[Gemeinde])

Mit der Maximalen Anzahl an Gemeinden ermittelt, wissen wir auch das der letzte Rang dieser Zahl entsprechen sollte. Ich sage sollte, denn wenn ein Rang doppelt vergeben wird von Excel (wenn der gleiche Wert zwei mal oder mehr auftaucht), dann wird dies nicht zutreffen.

Schritt 3 – Den Rang mit der richtigen Kennzahl verbinden

Wir haben sämtliche Daten in einer Tabelle, deshalb können wir auch mit einer Formel sämtliche Informationen abfragen. Mit einer kleinen Referenztabelle in der wir die Position der abgefragten Werte festhalten bauen wir uns dazu eine Brücke.

Die Referenztabelle (nfRefTabelle)

Die Referenztabelle besteht selber aus Formeln. z.B.

=SPALTE(tblAargau[[#Kopfzeilen];[2011]])

So wird sichergestellt dass alle Werte in der Referenztabelle korrekt bleiben, selbst wenn neue Kennzahlen eingefügt werden (sprich neue Spalten).

Nun verbinden wir den Rang der Kennzahl mit dem Namen der Ortschaft. Dafür benutzen wir die INDEX, VERGLEICH, BEREICH.VERSCHIEBEN und SVERWEIS Formeln.

=INDEX(tblAargau[Gemeinde];VERGLEICH(F55;BEREICH.VERSCHIEBEN(tblAargau[Gemeinde];0;SVERWEIS($B$82;nfRefTabelle;3;0)-1);0))

Schritt 4 – Daten Anzapfen

Die Kennzahlen werden dann mit der immer gleichen verschachtelten SVERWEIS Formel abgefragt. Hier die Formel welche den Wert für die Einwohnerzahl des ersten Ranges und Ortschaft berechnet (in Zelle H42):

=SVERWEIS($G42;tblAargau;SVERWEIS(H$40;nfRefTabelle;2;0);0)

In diesem Fall bezieht sich $G42 welches im ersten SVERWEIS steht auf den Namen der Ortschaft, und H$40 im zweiten SERWEIS auf die Kennzahl.

Fertig!

Das fertige Wachstums Dashboard könnt Ihr Euch hier herunterladen:

Newsletter

Wenn Euch dieses Dashboard Tutorial gefallen hat so könnt Ihr Euch für den Newsletter einschreiben!