Bedingte Formatierung in Excel mit 17 Symbolsätzen

Bedingte Formatierung ist immer dann sinnvoll, wenn Werte, die bestimmte Bedingungen erfüllen oder nicht erfüllen, hervorgehoben werden sollen.

Bisher habe ich in Beispielen gezeigt, wie sich dann die Schriftfarbe ändert oder wie sich der Zellhintergrund anders gefärbt wird.

In diesem Beitrag geht es um die Symbolsätze, von denen Excel 2007 exakt 17 Varianten anbietet.

Am Ende des Beitrags findest Du eine Ergänzung aufgrund neuer Erkenntnisse, geschrieben am 17.11.2018.

Rufst Du über das Menü Start die bedingte Formatierung auf und wählst dann Symbolsätze, wirst Du dieses Bild sehen:

Das ist die Auswahl, auf die Du zugreifen kannst.

Liegt Dir eine Tabelle vor, in der ein Bereich eben mit einem der Symbolsätze formatiert wurde, gehe zu „Regeln verwalten“ und Du wirst standardmäßig mit Prozenten (67% bzw. 33%) konfrontiert.

Was diese 67% bzw. 33% bedeuten und worauf sich die Werte beziehen, ist oft eine Fragestellung in Foren. Ich will die Frage in diesem Beitrag beantworten. Doch nun zu ein paar Beispielen:

1. Eine Zahlenreihe von 1 bis 10

Eine ganz einfache Zahlenreihe soll zuerst betrachtet werden:

Den Bereich B32:B41 habe ich markiert, bin dann im Menü Start zur bedingten Formatierung gegangen und habe Symbolsätze aufgerufen. Dort habe ich mich für „3 Symbole (ohne Kreis)“ entschieden. Dieses Symbol zählt zu den Indiaktorsymbolen.

Um zu ergründen, warum bei einigen Werte das rote Kreuz, bei einigen das gelbe Ausrufungszeichen und bei einigen der grüne Haken erscheint, habe ich „Regeln verwalten“ aufgrufen und sehe dieses Bild:

Was bedeuten jetzt die Prozente?

Die Reihe hat 10 Werte, 67% davon sind 6,7, gerundet 7. D.h., in einer aufsteigend sortierten Liste bekommen alle Werte nach dem 7.Wert von oben den grünen Haken.

Alles nach 33%, also 3,3 Werte, bekommt das gelbe Zeichen. Alles was unter 33% liegt, bekommt schließlich rot.

Hat die Datenreihe 100 Werte, erhalten alle Werte, die anzahlmäßig > 67% sind, den grünen Haken.

Im Übrigen kann die Symbolreihenfolge auch umgekehrt werden.

Verändere einfach mal die 67% in 50%, die bedingte Formatierung wird sich ändern.

Sind die Werte z.B. Plan-Ist-Abweichungen, sagt diese bedingte Formatierung nichts darüber aus, ob die Abweichung zu groß oder in Ordnung ist.

2. Die gleiche Zahlenreihe von 1 bis 10

Jetzt wähle ich statt Prozente den Typ Zahl aus und bestimme, dass

>=5 -grün
>=2 – gelb
<2 – rot

sein soll.

Die Zahlenreihe sieht nach der Formatierung so aus:

Aber auch hierbei erkenne ich, dass ich Plan-Ist-Abweichungen  nicht richtig bewerten kann. Deshalb mache ich einen weiteren Versuch:

3. Wieder die gleiche Zahlenreihe von 1 bis 10

Als Typ wähle ich diesmal das Quantil aus und treffe diese Festlegungen:

=85 – grün
>=35 – gelb
<35 – rot

Die Zahlenreihe ist so formatiert:

Nein, auch diese Variante bringt mich nicht weiter.

4. Plan, Ist und Abweichung

Mein Wunsch ist es immer noch, die Abweichungen zwischen Ist- und Planwerten vernünftig beding zu formatieren. Im Klartext heißt das, wenn eine Abweichung eine bestimmte Größe überschreitet, soll sie das rote Kreuz erhalten.

Ich bringe deshalb eine neue Datentabelle ins Spiel:

Die Abweichung abs. wurde so formatiert:

Mit dem Typ Formel wird ausgedrückt, dass der Wert das Ergebnis einer Berechnung ist, also, wenn in Zeile 134 Ist 2599 – Plan 2500 > oder = 20 ist, dann setze den grünen Haken. 48 ist >= 20.

Die Abweichung rel. wurde so formatiert:

Hier geht es darum, dass der Wert >= 2% sein muss, um den grünen Haken zu erhalten. In Zeile 134 ist das der Fall.

Dieser Typ kann z.B. im Risikomangement genuzt werden. Die Indikatoren können auf künftig positive Enwicklungen (Chancen) oder künftig negative Entwicklungen (Risiken) hinweisen.

Die Symbolsätze (hier: Indikatoren) werden zu Frühwarnindikatoren. Am nächsten Beispiel wird das recht deutlich.

5. Netzabgabe eines Wasserversorgers: Plan, Ist und Abweichung

Gezeigt wird die Netzabgabe eines Wasserversorgers an die Verbraucher.

Die bedingte Formatierung der Abweichung erfolgt auch hier über den Typ Formel.

Deutlich zu sehen ist, dass ab Monat August die Abweichung von Ist zu Plan unter dem gewählten Grenzwert liegt.

Die negative Entwicklung der Netzabgabe auf Dauer kann für den Versorger existenzbedrohend sein.

Daran hängen die technische Auslegung der Anlage, die Kalkulation und damit die Verbraucherpreise.

Das rote Kreuz ist hier ein Frühwarnindikator für ein mögliches kommendes Risiko.

Mit dem Typ Formel habe ich nun endlich die Aussagen, die ich über die bedingte Formatierung haben will.

Fazit

Meine Schlussfolgerung aus diesem Beitrag ist, dass ich mir stets überlegen muss, welche Aussage ich mit einer bedingten Formatierung erhalten will.

Ein Symbolsatz ist schnell ausgewählt. Wenn die Regel nicht vernünftig ausgewählt ist, habe ich zwar schöne bunte Zeichen, aber keine Antwort auf meine Fragen.

In den Beispielen habe ich mich auf einen Symbolsatz beschränkt. Die Regelverwaltung ist aber bei allen anderen ähnlich. Liegen fünf Symbole in einem Satz vor, geht die Standardeinstellung über 80%, 60%, 40% und 20%. Aber auch das kann entsprechend den obigen Ausführungen verändert werden.

Viel Spaß beim Ausprobieren.

Ergänzung (17.11.2018)

Kürzlich brachte ein Leser Zweifel an der grundsätzlichen Richtigkeit der Aussagen in Abschnitt 1 an. Dort hatte ich eine Zahlenreihe von 1 bis 10  mit einer bedingten Formatierung (Symbolsätze / 3 Symbole (ohne Kreis)) versehen. Bei dieser Art der Formatierung geht es zunächst nicht um Werte, sondern um die Anzahl der Werte. Werte >=67% sollten grün, >=33% gelb und alle anderen rot gefärbt werden.

Das Ergebnis sah so aus:

Alle Werte >=67%, gerundet 70%, also ab dem 8.Wert sind grün gekennzeichnet.

Alle Werte >=33%, gerundet 40%, und <67%, als 4. bis 7. Wert sind gelb gekennzeichnet.

Schließlich sind alle Werte <33%, also die Werte 1 bis 3 rot gekennzeichnet.

Der Leser brachte nun eine andere, sortierte Wertereihe ins Spiel und wendete die Regel wie oben an:

Das Ergebnis war ein anderes als in meinem Beispiel. Berechtigterweise entstanden so seine Zweifel. Seine Zweifel war für mich eine Aufforderung, der Sache nachzugehen. Warum wurden seine Werte anders bedingt formatiert als meine?

Irgendwann kam mir in den Sinn, seine Wertereihe doch einmal komplett abzubilden, also von 10 bis 33 und dann die bedingte Formatierung vorzunehmen.

Dies war das Ergebnis:

Die im Beispiel des Lesers sichtbaren Werte sind alle richtig bedingt formatiert, die 10 ist rot, 18 bis 25 sind gelb und die 33 ist grün.

Offenbar bildet Excel intern eine geschlossene Reihe vom kleinsten bis zu größten Wert, formatiert sie mit Symbolsätzen und zeigt schließlich nur die gewünschten Werte an. Ob das immer so ist, habe ich allerdings nicht weiter untersucht.