Thermometer-Diagramm

Im Controlling werden häufig so genannte Dashboards verwendet, um schnell einen Überblick über die wichtigsten Kenngrößen zu erhalten. Hierbei werden in der Regel, Ampeldarstellungen, Tacho-Diagramme oder Thermometer-Diagramme verwendet. In diesem Excel-Tipp wird erläutert, wie ein Thermometer-Diagramm erzeugt wird. 

Bei einen Thermometer-Diagramm handelt es sich um ein Säulen-Diagramm, welches einen relativen oder absoluten Wert darstellt. In Excel kann dieses Diagramm vom Grundsatz her relativ leicht umgesetzt werden. Jedoch kann es durch Formatierungen in seiner Aussagekraft und Ästhetik gesteigert werden. Im Folgenden wird ein relativ einfaches Thermometer-Diagramm vorgestellt und ein etwas Erweitertes. 

1. Daten festlegen 

Das hier vorgestellte Diagramm soll den Stand der Liquidität 1. Grades in % zum aktuellen Monat wiedergeben. Hierfür wird folgende Grundtabelle angenommen. 

 
Abb. 1 Grundtabelle 

In diesem Beispiel enthält die Tabelle bereits für alle 12 Monate die Werte, sodass zu jeden Monat ein Liquiditätsgrad errechnet werden kann. In der Praxis werden diesen Daten in der Regel nur bis zum aktuellen Monat vorliegen. 

Um den richtigen Liquiditätsgrad zu wählen, wird der Wert mit folgender Formel aus der Tabelle entnommen. 

Formel: =INDEX(G9:G20;MONAT(HEUTE());1) 

Diese Formel bestimmt das heutige Datum mit der Funktion Heute. Durch die Einbindung in die Funktion Monat wird vom heutigen Datum der Monat als ganze Zahl ermittelt. Durch die Funktion Index wird die Position des Wertes ermittelt, der den Liquiditätsgrad des entsprechenden Monats enthält. Diese Kombination funktioniert nur bei aufsteigend sortierten Monaten. 
2. Datentabellen erstellen

Nachdem der Liquiditätsgrad ermittelt worden ist, können die beiden Datentabellen für das Diagramm angelegt werden. Die erste Tabelle enthält nur den Wert 1 für ein Kreisdiagramm. Dieses Diagramm soll später genutzt werden, um das Enddiagramm mehr wie ein klassisches Thermometer wirken zu lassen. Die zweite Datentabelle enthält folgende Formeln:

Formel:

Wert1 (Formel in D30): =1-D25
Wert2 (Formel in D31): =D25



3. Diagramm anlegen

Hiernach werden die Zellen B36 bis D63 markiert und mit einen Rahmen sowie einen grauen Hintergrund versehen. In diesen Bereich werden die Diagramme eingefügt. Als Erstes wird das Kreis Diagramm angelegt.

Excel 2003: Einfügen/Diagramm…/Kreis/Kreis/

Bei der Eingabe des Datenbereiches wird auf die Registerkarte „Reihe“ gewechselt und eine neue Reihe hinzugefügt. Für „Werte“ wird die Zelle D28 gewählt, welche die zuvor definierte 1 enthält.

Excel 2007: Einfügen/Kreis/Kreis/

Über den Rechtsklick wählen Sie das Menu „Daten auswählen“ und fügen eine Reihe ein. Der Wert wird die 1 aus der Zelle D28.

Im Anschluss wird die Diagrammfläche farblos formatiert und der Rahmen entfernt. Der Datenpunkt wird in einer beliebigen Farbe formatiert, in diesem Beispiel rot. Auch beim Datenpunkt wird kein Rahmen gewählt. Durch die Skalierung des Diagramms per Drag & Drop wird es an die gewünschte Form angepasst. Das Diagramm sollte jetzt ungefähr wie in Abbildung 2 aussehen.


Abb. 2 Thermometersockel

Im nächsten Schritt wird ein Säulendiagramm mit dem Untertyp „gestapelte Säule (100%)“ eingefügt. Der Datenbereich wird wieder über die Registerkarte „Reihe“ selbst definiert. Es werden zwei Reihen angelegt. Die erste Reihe enthält den Wert2 aus Zelle D31 und die andere den Wert1 aus D30. In Excel 2007 wird hierbei das Menü „Daten auswählen“ verwendet.

Das Diagramm wird analog zum Kreisdiagramm formatiert. Hierbei gilt es jedoch zu beachten, dass Reihe 1 die Farbe des Kreisdatenpunktes enthält und die Reihe 2 keine Farbe erhält. Das Diagramm wird im Anschluss auf die richtige Größe angepasst und möglichst mittig am Kreis ausgerichtet (Siehe Abbildung 3).


Abb. 3 Vorformatiertes Diagramm

4. Letzte Formatierungen

Im letzen Schritt werden Detailformatierungen durchgeführt. Wählen Sie im Menu „Diagrammflächen formatieren“ die Option „Von Zellposition und –größe unabhängig“ in der Registerkarte „Eigenschaften“.

Im Anschluss selektieren Sie die Achsen und entfernen diese mit Hilfe des Rechtsklickmenüs. Die Skalierung bleibt erhalten und deren Schriftgröße wird auf 12 erhöht. Im Anschluss wird bei den Skalierungsmarken ein Rahmen in die mittlere Spalte eingefügt. Durch Anpassung der Zeilenhöhe werden die Linien an die Werte des Diagramms angepasst. Das fertige Diagramm sollte jetzt wie in Abbildung 4 aussehen.


Abb. 4 fertiges Diagramm

Zusätzlich möchten wir Ihnen in diesen Tipp noch eine kleine Erweiterung dieses Diagramms vorstellen. Hierfür muss die Datentabelle des Diagramms für die Säule erweitert werden.

5. Erweiterung des Diagramms

Es werden in dieser Erweiterung Farbbereiche festgelegt, wodurch das Diagramm eine Wertung zur Kennzahl abgeben kann. Um ein korrektes Farbschema zu ermitteln, wurden mithilfe einer Wenn-Funktion die jeweiligen Werte für den Datenpunkt berechnet.

Formel:

Wert 1 (Zelle C99): =D25
Wert 2 (C100): =WENN(C99>0,15;0,15;C99)
Wert 3 (C101): =WENN(C99>0,3;0,15;WENN(C99<=0,15;0;C99-C100))
Wert 4 (C103): =WENN(C99>0,5;0,2;WENN(C99<=0,3;0;C99-SUMME(C100:C101)))
Wert 5 (C104): =WENN(C99>0,5;C99-SUMME(C100:C102);0)
Wert 6 (C105): =1-SUMME(C100:C103)

Das Säulendiagramm wird um die Anzahl der neuen Formeln erweitert. In diesem Beispiel werden 5 Reihen angelegt und mit den Ergebnissen von Wert 2 bis Wert 6 gefüllt. Wert 1 wird nicht benutzt, da dieser nur für die Berechnung benötigt wird.



Im Anschluss werden die Datenpunkte durch Fülleffekte zu einem Farbfluss kombiniert. In diesen Beispiel von rot zu grün. Es wurde je Datenpunkt ein Fülleffekt mit 2 Farben gewählt. Das fertige Diagramm kann wie in Abbildung 5 aussehen.



Abbildung 5 erweitertes Thermometer-Diagramm

Wie Oben bereits genannt, handelt es sich hierbei um ein Beispiel. Selbstverständlich können weitere Flächen, andere Werte etc. genutzt werden.

Die Beispieldatei Thermometer-Diagramm 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