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.

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

Symbole1

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:

Symbole2

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:

Symbole3

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.

Symbole5

Die Zahlenreihe sieht nach der Formatierung so aus:

Symbole4

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

Symbole7

Die Zahlenreihe ist so formatiert:

Symbole6

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:

Symbole8

Die Abweichung abs. wurde so formatiert:

Symbole9

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:

Symbole10

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.

Symbole11

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

Symbole12

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 GFragen.

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.

Advertisements

8 Kommentare zu „Bedingte Formatierung in Excel mit 17 Symbolsätzen“

  1. Guten Morgen Gerhard,

    auch von mir ein Lob für die gelungenen Beispiele. Gerade bei Frühwarnindikatoren gefällt es mir besonders gut, dass du hier nicht auf die absoluten sondern die relativen Veränderungen eingegangen bist :-).

    Dein Fazit „dass du dir stets überlegen musst, welche Aussage du mit einer bedingten Formatierung erhalten wilst“ würde ich beinahe als Credo für jeden Bericht nehmen und empfinde ich als ein universell einsetzbares Fazit, dass dir aber auch bei deinen übrigen Beispielen immer wieder gut gelingt.

    Leider hat es Excel, soweit mir bekannt, noch nicht geschafft, dass man sich eigene Symbolsätze definieren kann. Ich hatte damals eine Rückfrage zur bedingten Formatierung, wie man einen roten Pfeil nach oben und grünen nach unten erhalten kann (ging um Energieverbrauch) und konnte damals auch nur auf die Verwendung von eingefärbten Unicodezeichen ▲ und ▼ verweisen… ist dir bei einer aktuelleren (> Excel 2013) bekannt, ob mittlerweile die Symbole in der bedingten Formatierung ebenfalls ausgetauscht werden können?

    Viele Grüße
    Andreas

    Gefällt mir

    1. Guten Morgen Andreas,
      vielen Dank für dein positives Feedback, es tut gut, Reaktionen auf das eigene Tun zu erfahren. Microsoft hat sicherlich keinen Kummerkasten, in den man seine Vorschläge und Ideen hinein packen kann. Es wäre aber gut.
      Gut wäre auch eine Formatierungsmöglichkeit

      Gefällt mir

    2. Hallo Andreas, es ist einfach kein gutes Arbeiten auf dem Smartphone, ich war noch gar nicht fertig mit der Antwort, da wurde schon gesendet. Ich wollte noch sagen, eine Formatierungsmöglichkeit wie bei den Formen könnte schon helfen

      Gefällt mir

  2. Hallo Gerhard, suche schon im gesamten Netz eine Formel für bedingte Formatierung Symbole. In Punkt:
    4. Plan, Ist und Abweichung hast du einen Wert(Formel) eingestellt.
    Zeile 134 Plan 2.500 IST 2.599
    Du hast hier ein Wert =20 Formel bzw. Wert=-70 Formel eingestellt.
    Meine Frage? warum hast du hier keine Formel, bezogen auf die Zellen eingestellt. Ich verstehe das nicht(Excel Dumm)
    Ich möchte eine Tabelle in der Art formatieren. Kannst du mir helfen

    Gefällt mir

    1. Hallo Günter, danke für deinen Kommentar.
      Mein Beitrag enthält eine falsche Abbildung, wollte ich schon längst korrigieren.
      Zu deinem Anliegen:
      Wenn du Symbolsätze zeigen willst, kannst du keine Formeln verwenden, das geht einfach nicht.
      Wählst du in in der Maske „Formatierungsregel bearbeiten“ den Typ Formel, musst du als Wert „=20“ bzw. „=-70“ eingeben.
      Willst du den Typ Zahl, gibst du 20 bzw. -70 ein.
      Willst du einen Bezug auf die Datenzellen in den Spalten A und B,kannst du nur die Regel „Formel zur Ermittlung der zu formatierenden Zellen verwenden. Dann schreibst du:
      =B134-A134>=20
      und eine zweite Regel mit >=-70.
      Dann kannst du aber keine Symbolsätze, sondern nur Farben verwenden.
      Wenn du magst, kannst du mir gern eine Beispieldatei mit deinen Wünschen senden, ich will gern helfen.
      Viele Grüße
      Gerhard

      Gefällt mir

  3. Hallo Gerhard,
    ich habe das Problem gelöst, hätte dir gerne ein Teil der Tabelle geschickt, nur leider sehe ich keine Möglichkeit die Datei hier einzufügen.
    Trotzdem vielen Dank für deine Bemühungen..
    nochmals. merci

    Gefällt mir

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s