Seit Version 2007 bietet Excel bei der Bedingten Formatierung die Regel ‚Obere 10%‘. Doch welche Art von Auswertung führt Excel im Hintergrund durch? Geht es wirklich um die Größe der Werte? Oder erfolgt die Auswertung über die Anzahl der Werte?
Daraus folgt: Ist diese Regel überhaupt geeignet zum Formatieren meiner Daten?
Um dies herauszufinden, habe ich einige Tests durchgeführt.
Praxistest an einem konkreten Beispiel
Zum Analysieren der Besucherzahlen einer Webseite verwende ich die ‚Obere 10%‘-Regel. Und zwar sollen die Ergebnisse farbig gekennzeichnet werden, die im Bereich der oberen 45% liegen.
- Dazu markiere ich die Zellen mit den Besucherzahlen, klicke in der Registerkarte Start auf Bedingte Formatierung, wähle Obere/untere Regeln – Obere 10%.
- In dem nun eingeblendeten kleinen Dialogfeld (nächste Abbildung) ändere ich den Prozentwert von 10 auf 45 und bestätige mit OK.
In der folgenden Abbildung ist links das Ergebnis zu sehen: Die 5 größten Zahlen werden eingefärbt. Zur besseren Veranschaulichung rechts die bildhafte Darstellung per Liniendiagramm.
Szenario 1
Szenario 2
Zum Vergleich verkleinere ich alle nicht eingefärbten Zahlen extrem, indem ich diesen die 100.000 er Stelle weggenehme und auch kleinere gleiche Zahlen darunter mische.
Die Folge: Die Einfärbung der Zahlen bleibt gleich. Die Grenze ändert sich ebenfalls nicht.
Szenario 3
Was passiert aber, wenn ich eine der nicht eingefärbten Zahlen nur minimal größer mache als die kleinste eingefärbte Zahl?
Die Folge: Es bleibt bei 5 eingefärbten Zahlen. Die leicht größere Zahl wird nun stattdessen eingefärbt. Die Grenze verschiebt sich leicht nach oben.
Szenario 4
Ich ändere bei den Zahlen aus Szenario 3 zwei der nicht eingefärbten Zahlen und setze sie mit der größten Zahl (hier 620.000) gleich.
Die Folge: Es bleibt bei 5 eingefärbten Zahlen. Die Grenze hat sich wie in Szenario 3 nach oben verschoben.
Szenario 5
Ich ändere von den Zahlen aus Szenario 3 zwei der nicht eingefärbten Zahlen in die kleinste eingefärbte Zahl (hier 517.000).
Die Folge: Nun werden 7 Zahlen eingefärbt statt 5. Die Grenze hat sich nicht verschoben.
FAZIT
Bei der ‚Oberen 10%‘-Regel der bedingten Formatierung werden nicht die Werte selbst in die Berechnung miteinbezogen. Stattdessen bildet Excel die Anzahl und von dieser wird der prozentuale Anteil ermittelt und gerundet. Dabei ergibt sich eine Zahl k. Alle Zahlen, die größer oder gleich der k-größten Zahl sind, werden nun formatiert.
Anders gesagt:
Die Werte der Zahlen spielen KEINE Rolle. Der gewählte Prozentsatz gilt für die ANZAHL der Werte.
Ein Beispiel:
- Wird für 10 Zahlen die Variante ‘Obere 30%’ gewählt, wird auf drei Werte – also 30% von 10 – die Bedingte Formatierung angewandt.
- Kommt einer dieser oberen drei Werte doppelt vor, erhalten vier Zellen das bedingte Format.
- Gibt es einen der oberen drei Werte in der Liste viermal, werden sechs Zellen von der Bedingten Formatierung gekennzeichnet usw.
