Ampel-Diagramm

Für Controller ist es wichtig immer schnell und auf einen Blick über aktuelle Entwicklungen und Ereignisse informiert zu sein. Daher nutzen sie häufig Dashboards auf denen ihre wichtigsten Kenngrößen, wie zum Beispiel Liquidität, Rentabilität oder Umsatz angezeigt werden. Um den Controller die Informationen optimal darzustellen, nutzen Dashboards häufig Tachodiagramme, Thermometerdiagramme oder Ampeldiagramme.

Ampeldiagramme zeigen durch eine Signalfarbe (Rot, Gelb, Grün) an, ob ein bestimmter Bereich oder eine bestimmte Kenngröße besonderer Aufmerksamkeit benötigt. Diese Diagramme können auf zwei verschiedene Arten dargestellt werden. Zum Einen besteht die Möglichkeit in der Darstellung einer „normalen“ Ampel, welche übereinander sortiert von Rot auf Grün wechselt. Zum Anderen kann die Signalgebung auch in einer einzigen „Leuchte“ erfolgen. In diesem Artikel wird die Erstellung einer klassischen Ampel beschrieben. Es werden jedoch ebenfalls Anmerkungen erfolgen, wie dies auch mit nur einer Leuchte umsetzbar ist. 
1. Schritt Grunddaten
In diesem Beispiel soll die aktuelle Situation des Liquiditätsgrad 1 dargestellt werden. Hierfür dient die in Abbildung 1 dargestellte Wertetabelle. In dieser sind die benötigten Werte für den Liquiditätsgrad für jeden Monat enthalten.


Abb. 1 Tabelle mit Ausgangswerten

2. Schritt Liquiditätsgrad ermitteln
Um den Liquiditätsgrad zu bestimmen, wird folgende Formel benutzt.

Formel: =INDEX(E5:E16;MONAT(HEUTE());1)

Diese Formel kann nur bei aufsteigend sortierten Monaten verwendet werden. Durch den Einsatz einer Verweis- oder Vergleich-Funktion kann die Sortierung umgangen werden. Die hier verwendete Funktion ermittelt anhand des aktuellen Monats die benötigte Zeile. Wobei die Monatszahl die Zeilennummer entspricht.


Abb. 2  Tabelle mit aktuellen Liquiditätsgrad 1

3. Ampeleffekte definieren
Damit das Diagramm weiß welche Farbe aufleuchten muss, müssen hierfür Definitionen angelegt werden. Die folgende Abbildung zeigt die hier verwendeten Werte. Diese Werte können natürlich nach belieben angepasst werden. Es ist jedoch wichtig, dass keine Lücken oder Überlappungen entstehen.


Abb. 3 Tabelle mit Lichtdefinitionen

4. Datentabelle definieren
Das Ampeldiagramm mit drei Kreisen benötigt hierbei drei Datensätze. In einem Ampeldiagramm mit nur einem Kreis wird nur ein Datensatz benötigt. Dieser benötigt jedoch drei Spalten. Die Folgenden Formeln wurden hierfür verwendet.

Formeln:
B30: =WENN(UND($B$20>=B24;$B$20<C24);1;0)
C30: =WENN(B30=0;1;0)
B31: =WENN(UND($B$20>=B25;$B$20<C25);1;0)
C31: =WENN(B31=0;1;0)
B32: =WENN(UND($B$20>=B26;$B$20<C26);1;0)
C32: =WENN(B32=0;1;0)

Diese Wenn-Funktionen prüfen, ob der Liquiditätsgrad in ihren Bereich fällt. Die jeweils zweite Funktion gibt das entgegengesetzte Ergebnis wieder. Dies kann genutzt werden um evtl. Schatteneffekte bei den Kreisdiagrammen mit einzubauen.


Abb. 4 Datentabelle für Diagramme

5. Diagramme einfügen
Nachdem alle Daten ermittelt worden sind, werden die Diagramme angelegt. Hierfür werden zunächst einige Zellen markiert und eingerahmt sowie deren Farbe auf Grau geändert. In diesen Zellen werden später die Diagramme eingefügt. Hiernach werden drei einfache Kreisdiagramme angelegt und von ihrer Größe her an den vorher angelegt Rahmen angepasst.


Abb. 5 Fläche mit nicht formatierten Diagrammen

6. Datenpunkte festlegen und formatieren
Im nächsten Schritt werden die Datenreihen in die Diagramme eingefügt. Hierfür öffnen Sie mit einem Rechtsklick das Menü „Datenquelle“ (in MS Excel 2007 Rechtsklick / „Daten auswählen“) und fügen jeweils zwei Datenreihen ein. Die Werte für die Datenreihe werden aus den zuvor angelegten Formeln entnommen. Das erste Diagramm erhält für die erste Reihe den Wert aus der Zelle B30 und die zweite Reihe den Wert aus C30. Für die anderen Diagramme wird analog vorgegangen.

Im Anschluss werden die Datenpunkte formatiert. Der Datenpunkt der ersten Reihe der jeweiligen Diagramme erhält die Signalfarbe. Zusätzlich werden die Zeichnungsfläche und sämtliche Rahmen transparent gesetzt oder entfernt. Das fertige Diagramm kann wie folgt aussehen.


Abb. 6 fertiges Diagramm


Hierbei handelt es natürlich nur um ein Beispiel, Sie können noch weitere Farben einfügen oder eine Kombination aus Kennzahlen verwenden.

Die Beispieldatei Ampeldiagramm können Sie hier herunterladen >>

Informieren Sie sich regelmäßig über neue Excel-Tipps  und Excel-Tools
in unserem monatlich erscheinenden >>Newsletter<<.

letzte Änderung Alexander Wildt am 09.09.2019

Autor:in

weitere Fachbeiträge des Autors | Forenbeiträge