Eine Excel Farbskala eignet sich sehr gut, um auf ihr einen Key Performance Indicator (KPI) mit einem schwarzen Marker darzustellen. Damit ist die Farbskala ein schönes Dashboard-Element, das auch nicht jeder hat.
Jedes Unternehmen definiert und überprüft im Rahmen des Controllings KPIs um den Unternehmenserfolg zu messen. Diese Form der Darstellung bietet sich für Kennzahlen an, die sich zwischen zwei definierten Schwellenwerten bewegen, also im Zeitablauf nicht linear steigen.
Während eine Farbskala zur Darstellung des Umsatzes (der im März höher liegt als im Februar) also ungeeignet ist, ist sie ideal für Dashboards zur Überwachung der Liquidität oder aller in Prozent ausgedrückten Quoten.
Übersicht
Für die Erstellung der Excel Farbskala mit Marker gehst du wie folgt vor:
1. Excel Farbskala erstellen
Im ersten Schritt erstellst du dir eine passende Datenreihe, an der sich dann die Farbskala orientieren und auf der anschließend der Marker für den KPI dargestellt werden kann.
1.1 Datenreihe aus Minimal- und Maximalwert erstellen
Eine Datenreihe kannst du ganz einfach erstellen, indem du nur den Minimal- und den Maximalwert sowie die Anzahl der zu befüllenden Zeilen vorgibst und die restlichen Werte einfach errechnest.
Das macht Sinn, da du bei einer Veränderung der Schwellenwerte nur zwei Zahlen anstatt der ganzen Datenreihe ändern musst. So kannst du die Minimal- und Maximalwerte z.B. einfach in einem separaten Tabellenblatt zentral verwalten.
In meinem Beispiel beginnt die Datenreihe in der Zelle C11 mit dem Maximalwert 10 und endet in der Zelle C76 mit dem Minimalwert 1. Meine Datenreihe erstreckt sich somit über 65 (76-11) Zeilen bzw. Zellen.
HINWEIS: Durch die Reduzierung der Zeilenhöhe für alle Zeilen der Datenreihe auf 2 Pixel werden die Zeilen am Ende sehr schmal. Das Optimum für eine von der Größe passende Farbskala liegt m.E. daher zwischen 65 und 90 Zeilen.
Um die anderen Werte der Datenreihe in den Zellen C12:C75 aufzufüllen, muss entweder der Maximalwert 10 in gleich großen Schritten bis 1 reduziert oder der Minimalwert 1 in gleich großen Schritten bis 10 erhöht werden. Ich starte in der Regel oben, reduziere also in diesem Fall den Maximalwert.
Die nach unten kopierbare Formel für die Zelle C12 lautet:
=C11-((C$11-C$76)/65)
Die innere Klammer berechnet zunächst die Differenz vom Maximalwert 10 in der Zelle C$11 zu dem Minimalwert 1 in der Zelle C$76. Das Ergebnis ist 9.
WICHTIG: Da die Formel nach unten kopiert werden soll, muss die Zeile 11 (Maximalwert) und die Zeile 76 (Minimalwert) als absoluter Zellbezug mit dem Dollarzeichen $ (also C$11 und C$76) festgeschrieben werden. Auf die Angabe der Spalte C als absoluten Zellbezug ($C$11 bzw. $C$76) erfolgt jedoch nicht, da die Formel für weitere Datenreihen/Farbskalen später nach rechts kopiert werden soll. Nach dem Auswählen der Zellen mit der Maus kannst du mit der Taste F4 die vier Kombinationen mit und ohne Dollarzeichen in der Formel durchschalten.
Im nächsten Schritt (äußere Klammer) wird die gerade berechnete Differenz 9 durch die Anzahl der oben berechneten Zeilen, also durch 65, geteilt. Das Ergebnis ist also die auf 65 Zeilen verteilte Differenz zwischen dem Maximal- und Minimalwert.
Diese auf 65 Zeilen verteilte Differenz wird nun von dem Maximalwert abgezogen. Nach dem Kopieren der Formel in den ganzen aufzufüllenden Zellbereich C12:C75 wird der Maximalwert in der Zelle einen Schritt weiter reduziert, bis der Minimalwert erreicht ist. Es entsteht eine Datenreihe mit gleichmäßigen Abständen zwischen den Werten.
WICHTIG: Der erste, links in der Formel stehende Zellbezug C11 wird nicht mit dem Dollarzeichen als absoluter Zellbezug (C$11) angegeben. Der Differenzbetrag soll schließlich immer von der oberen Zelle abgezogen werden, der Zellbezug muss also beim Kopieren der Formel in den Zellbereich C12:C75 nach unten wandern können.
Die Datenreihe gestaltet sich wie folgt:
1.2 Excel Farbskala über Bedingte Formatierung einfügen
Nachdem du die gleichmäßige Datenreihe erzeugt hast, markierst du den gesamten Zellbereich C11:C76 und wählst in der Registerkarte Start in der Rubrik Formatvorlagen über die SchaltflächeBedingte Formatierung und Farbskalen je nach Geschmack eine Farbskala aus:
Deine Datenreihe kommt jetzt bereits in dem gewählten farblichen Gewand daher. Wenn dir die vorkonfigurierten Farben nicht zusagen, kannst du über die Schaltfläche Bedingte Formatierung und die Option Regeln verwalten den Dialog Manager für Regeln zur bedingten Formatierung öffen:
Dann einfach die gerade erstellte Regel per Klick markieren und über die Schaltfläche Regel bearbeiten den Dialog Formatierungsregel bearbeiten öffnen:
Hier gibt es für eine Excel Farbskala zwei Formatstile: 2-Farben-Skala oder 3-Farben-Skala (meine Wahl). Außerdem kannst du die Farben für Minimum, Mittelpunkt und Maximum (nur bei Formatstil 3-Farben-Skala) entsprechend deinen Wünschen anpassen.
Die abgestufte Excel Farbskala auf Basis der Datenreihe ist fertig. Es fehlt noch der Marker.
2. Marker für den KPI hinzufügen
Markiert werden soll in der Datenreihe die Zelle, die einem zu suchenden KPI am nächsten kommt. Der zu suchende Wert muss dafür natürlich innerhalb der Minimal- und Maximalwerte, also zwischen 1 und 10, liegen. In meinem Beispiel wird der zu suchende Wert in der Zelle C2 angegeben. Er könnte aber auch aus einem anderen Tabellenblatt oder einer anderen Arbeitsmappe kommen.
Der Marker wird ebenfalls über eine Bedingte Formatierung auf Basis einer Formel hinzugefügt. Dazu markierst du zunächst wieder den Zellbereich der Datenreihe C11:C76.
WICHTIG: Hier ist es für die folgende Formatierungsregel entscheidend, dass du als erstes die Zelle C11 markierst und die Markierung dann nach unten bis zur Zelle C76 erweiterst und nicht genau umgekehrt vorgehst.
Um eine neue Formatierungsregel auf Basis einer Formel zu erstellen, öffnest du in der Registerkarte Start in der Rubrik Formatvorlagen über die Schaltfläche Bedingte Formatierung und einen Klick auf Neue Regel (siehe Screenshot oben) den Dialog Neue Formatierungsregel.
Als Regeltyp wählst du Formel zur Ermittelung der zu formatierenden Zellen verwenden. Die Eingabemaske gestaltet sich wie folgt:
Über die Schaltfläche Formatieren… legst du zunächst die Formatierung der Zelle fest. Da am Ende die Zeilenhöhe aller Zeilen der Datenreihe auf 2 Pixel reduziert wird und damit die Zeilen sehr schmal werden, habe ich im Reiter Ausfüllen einfach Schwarz als Hintergrundfarbe der ganzen Zelle gewählt anstatt einen Rahmen zu setzen.
Gesucht wird jetzt noch die richtige Formel, um die Zelle aus der Datenreihe mit dem gesuchten Wert schwarz einzufärben.
2.1 Formel zur Ermittelung der zu formatierenden Zellen
In dem Feld Werte formatieren, für die diese Formel wahr ist ist als Formel ein sogenannter Wahrheitstest, nicht aber die ganze WENN Funktion einzugeben.
Die Bedingte Formatierung ist mehr oder weniger selber eine WENN Funktion: WENN die Formel für eine Zelle oder mehrere Zellen WAHR ist, DANN wird die gewählte Formatierung auf diese Zelle bzw. Zellen angewendet, SONST eben nicht.
Weitergehende Informationen: Bedingten Formatierung vom Regeltyp Formel
Die Zelle mit dem gesuchten Wert ist C2, die Datenreihe läuft vom Maximalwert 10 in Zelle C11 bis zum Minimalwert 1 in Zelle C76.
Gesucht ist also eine Formel, die genau für die Zelle aus dem Zellbereich C11:C76 WAHR ergibt, die dem gesuchten Wert aus Zelle C2 am nächsten kommt und markiert werden soll.
Die folgende Formel erfüllt diese Anforderung:
=UND(C11>=C$2;NICHT(C12>=C$2))
Der Wahrheitstest verknüpft über die UND Funktion zwei Bedingungen miteinander:
- C11>=C$2
Die aktuelle Zelle der Datenreihe (hier die erste Zelle C11) muss größer oder gleich dem Suchkriterium sein C$2 sein. Die Bedingung schließt aus, dass Werte die kleiner als das Suchkriterium sind, markiert werden. Angenommen das Suchkriterium in Zelle C$2 wäre 7, so würden alle Werte die kleiner 7 sind nicht markiert. Andersherum würden alle Werte die größer oder gleich 7 sind markiert. Deshalb benötigen wir zur weiteren Einschränkung auch die zweite Bedingung: - NICHT(C12>=C$2)
Die nachfolgende Zelle der Datenreihe (hier die zweite Zelle, also die Zelle C12) darf nicht auch schon größer oder gleich dem Suchkriterium 7 in der Zelle C$2 sein. So wird der Wert nach und nach nach in der Datenreihe nach unten eingeschränkt, bis die Begrenzung der ersten Bedingung erreicht wird.
WICHTIG: Die Zeile der Zelle mit dem gesuchten Wert ist mit dem Dollarzeichen als absoluter Zellbezug (C$2) einzugeben, da die Formel für den gesamten Zellbereich C11:C76 gilt.
Der Dialog Neue Formatierungsregel mit mit dem Regeltyp Formel gestaltet sich also wie folgt:
Nach dem Klick auf OK wird die Bedingte Formatierung für den Marker eingefügt.
Schließlich bleibt nur noch, weitere Datenreihen, Farbskalen und Marker für die anderen KPIs zu ergänzen und vor allem die Zeilenhöhe für die Zeilen mit der Datenreihe auf 2 Pixel zu reduzieren.
Das Endergebnis gestaltet sich dann wie folgt:
2.2 Alternative Formeln
Wenn dir die von mir gerade in der Bedingten Formatierung verwendete Formel für den Marker nicht direkt einleuchtet, macht das gar nichts. An dieser Stelle denken wir alle unterschiedlich. Und das ist absolut nicht schlimm, denn viele Wege führen nach Rom und gerade in der Mathematik und Logik lassen sich wahre Aussagen auf ganz unterschiedliche Art darstellen.
Alle der folgenden Ausdrücke sind z.B. WAHR und haben im Kern die gleiche Aussage:
- X>=3
- 3<=X
- X>2,99999
- etc.
Deshalb möchte ich dir hier ein paar alternative Formeln anbieten, mit denen du den Marker über die Bedingte Formatierung ebenfalls erzeugen kannst. Die Formeln stammen von Studenten aus meinen Seminaren und beziehen sich wie oben immer auf die folgenden Zellen bzw. Zellbereiche:
- Suchwert: C2 bzw. absolut: C$2
- Datenreihe: C11:C76
Es geht los:
=UND(NICHT(C12>=C$2);C11>=C$2)
Die Reihenfolge der beiden Bedingungen in der UND Funktion spielen keine Rolle und können getauscht werden.
=UND(C11<C$2;NICHT(C12<C$2))
Auch die Argumente einer oder hier beider Bedingungen lassen sich in der Reihenfolge vertauschen, das >= Zeichen wird in dem Fall zum < Zeichen.
=UND(C11>=C$2;C12<C$2)
Denkbar ist auch, die NICHT Funktion wegzulassen und die Aussage umzudrehen. Aus dem darf-NICHT-größer-gleich-sein wird dann ein muss-kleiner-sein.
Garantiert sind zig weitere Lösungen möglich. Probiere es gerne aus und schreib es mir in die Kommentare weitere Formeln. Vielleicht kommst du ja sogar auf eine ganz andere Formel anstatt nur die Operatoren zu tauschen. 😉
3. Ressourcen
Hier findest du noch ein paar Artikel mit weiterführenden Informationen, die Beispieldatei zum Download und externe Links:
- Arbeitsmappe zum Download
- Artikel zum Wahrheitstest
- Artikel zur Bedingten Formatierung
- Artikel Key Performance Indicator auf Wikipedia
- Microsoft Office Hilfe
Die im Rahmen der Kommentarfunktion übermittelten Daten werden gespeichert. Weitere Informationen zur Datenverarbeitung findest du in der Datenschutzerklärung.