KPI – Kennzahlendarstellung

Excel wird häufig eingesetzt um Kennzahlen zu berechnen und visuell ansprechend zu produzieren.
Das hier gezeigte Beispiel verwendet dazu das Werkzeug der Pivot Tabelle.

Dieses Beispiel ist nicht auf meinem Mist gewachsen, sondern das Original befindet sich auf der tolle Seite „Beat Excel“
Ich habe die Idee übernommen und die Beschreibung deutlich genauer abgefasst.

Das Original befindet sich hier:

Interactive Excel KPI Dashboard

Die Datengrundlage

Als Ausgangssituation haben wir vier Abteilungen der Firma „BURNT DUCK“ mit entsprechenden vier Kennzahlen für die zwölf Monate.Wir haben also 192 Datensätze für unsere Auswertung. Zur besseren Verwendung sind die Spalten mit einem Namen versehen.

Die erste Pivot Tabelle

Wir markieren die Daten und erzeugen eine Pivottabelle und packen die Ergebnisse in die Werte und die KPI in den Filter.
Benennen Sie das neue Blatt als Auswertung

Das berechnende Feld Rest

Nun erzeugen wir ein neues, berechnendes Feld und lassen uns eine Restberechnung erzeugen.

Dieses neue berechnende Feld packen wir in die Werte Box der Pivot Tabelle.

ACHTUNG: Es wird automatisch ein neues Feld „Werte“ in die Spalten gepackt. ziehe das in die Zeilen

Das Feld Summe von Ergebnis können Sie noch in ein Prozentformat verwandeln.

Die Kopien der Pivottabelle

Wir filtern nun nach der KPI „Ausfallzeit“ und bekommen eine nette Pivottabelle.

Lassen Sie sich nicht durch die zu großen Prozentzahlen verwirren, dazu kommen wir später.

Machen Sie nun für jede Kennzahl eine Kopie der Pivottabelle mittels Strg+c und Strg+v, als Ergebnis haben wir vier Pivot Tabellen.

Filtern Sie je Pivottabelle nach einem andren KPI. Über die Registerkarte Entwurf lassen sich die Farben einstellen.

Wir werden uns später auf die einzelnen Pivot Tabellen beziehen, aus diesem Grund vergeben wir für die vier Pivot Tabellen vernünftige Namen. Dies machen wir im Register Analysieren der Pivot Tabelle.

Nun erzeugen wir Donut Charts für jedes Pivot Blatt, hierbei verwenden wir ein normales Diagramm aus dem Register Einfügen und kein Pivotchart, durch die Selektion der Pivottabelle wird das Chart automatisch zum Pivot Chart.

Löschen Sie hierbei alle Elemente wie Legende, Diagrammüberschriften als auch die Wertfelder-Schaltflächen (rechter Mausklick)

Legen Sie ein neues Blatt „Dashboard“ an und schneiden / einfügen Sie die Diagramme auf das neue Blatt.

Achten Sie darauf, das die Lage und die Größe der Diagramme einheitlich sind.

Zur besseren Verwendung bekommen auch die Diagramme entsprechende Namen in den Eigenschaften des Pivot Charts

Das Ergebnis stellt sich wie folgt dar:

Die zweite Pivot Tabelle

Wir machen nun von der ersten Pivottabelle eine Kopie und richten die Felder nach folgendem Muster ein:

Das Ergebnis sieht erst einmal nicht so gut aus.

Wir vergeben für die Zweite Reihe der Pivot Tabellen wieder vernünftige Namen

Die Diagrammbeschriftung

Als nächstes holen wir uns die Prozentzahl der KPI der jeweiligen KPI und verbinden vier Zellen zu einem einheitlich großem Feld.
Dabei verwenden wir einen einfachen Zellbezug auf das Feld der Pivottabelle, also zum Beispiel =B4

Als nächstes holen wir uns die Prozentzahl der KPI der jeweiligen KPI und verbinden vier Zellen zu einem einheitlich großem Feld.
Dabei verwenden wir einen einfachen Zellbezug auf das Feld der Pivottabelle, also zum Beispiel =B4

Dieses Feld kopieren wir und fügen es über das Inhalte Einfügen als verknüpfte Grafik ein und positionieren es in der Mitte des Donut.

Die Statistik

Im Anschluß bauen wir uns eine kleine Statistik, die mittels SummewennS die Zahlen der Auswahl summiert. In der Spalte daneben holen wir uns die Werte der ausgewählten Spalte.
Benennen Sie dazu die beiden Spalten Ergebnis und Abteilung in der Datengrundlage mit entsprechenden Namen.

Die Spalten berechnen sich wie folgt

• Ergebnis:
  =SUMMEWENNS(Ergebnis;Abteilung;Auswertung!$A$13;KPI;$B$10;Monatsname;A30)
• Auswahl:
  =WENN(A30=$A$14;B30;NV())

Die Säulendiagramme

Auf Basis dieser Tabelle erstellen wir eine Säulengrafik.

Aus dieser Grafik werfen wir alle Schmuckelemente und die Y Achsen raus und packen die Datenreihe Auswahl auf die Sekundärachse.
Damit wird der selektierte Monat andersfarbig dargestellt, da er als Sekundärachsen Element vorne liegt.

Der Datenschnitt nach Monaten

Nun markieren wir das erste Donut Diagramm und erstellen einen Datenschnitt auf Basis des Monatsnamens

Der Slider sieht nun, trotz der geänderten Farbe noch etwas unschön aus.

Wir wechseln mit dem rechtem Mausklick in die Datenschnitteinstellungen

In den Datenschnitteinstellungen stellen wir ein, dass die Kopfzeile nicht angezeigt wird und blenden die Elemente ohne Daten aus.

Nun ric

hten wir unser Augenmerk auf die Eigenschaften des Sliders in der entsprechenden Registerkarte.

Dort ändern wir die Höhe und die Breite der Schaltflächen und des Sliders.

Damit werden vier Spalten angezeigt und eine Art Kalender simuliert, hübsch, oder ?
Die hier gezeigten Farben sind über eine neue Datenschnitt Formatvorlage erstellt worden.

Das wiederholen wir auch für die anderen drei Donutdiagramme und legen die Datenschnitte links neben die Diagramme

Die Verbindung des Datenschnitts

Kontrollieren Sie, ob die Auswahl eines Monats zur Veränderung der Kreisdiagramme führt. Falls das nicht der Fall sein sollte, so müssen Sie den Datenschnitt auswählen und die Verbindung kontrollieren.

Wählen Sie dazu den Datenschnitt aus und gehen in der Registerkarte Optionen auf die Berichtsverbindung. Dort muß0 der Haken bei der relevanten Pivot Tabelle vorhanden sein.

Der Datenschnitt nach Abteilungen

Wie gehen auf die Seite Dashboard und wählen ein beliebiges Kreisdiagramm aus und erzeugen einen neuen Datenschnitt nach den Abteilungen.

Genau wie bei dem Datenschnitt nach Monaten nehmen wir die Kopfzeilen raus und lassen leere Elemente nicht anzeigen. Wir stellen eine passende Breite und Höhe der Elemente ein.

Die Verbindung des Datenschnitts nach Abteilungen

Entscheiden sind nun die Berichtsverbindungen des Datenschnitts nach Abteilungen. Durch die Auswahl eines der Diagramme wird der Slider aktuell nur diese Eine Pivot Tabelle und damit nur ein Diagramm beeinflussen.

Wir rufen daher die Berichtsverbindungen des Abteilungssliders auf.

Hier müssen wir nun eine Verbindung auf alle acht Pivottabellen setzen.

In der Folge dürfen nun keine Prozentzahlen über 100% mehr auftauchen, da bei allen Pivottabellen eine Selektion nach Abteilung UND Monat vorliegt.

Das Ergebnis

Zum Abschluß haben wir eine nette Übersicht…..