Office-Blog

Excel 2010: Was bewirkt eigentlich die Regel ‚Obere 10%‘?

11.09.2011   Hildegard Hügemann

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.

Die Regel Obere 10% aufrufen

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.

Im Dialogfeld den Prozenwert auf 45 anheben

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

Szenario 2

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?

Szenario 3

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.

Szenario 4

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

Szenario 5

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.

Über den Autor

Hildegard Hügemann

- Dipl.-Informatikerin
- Zertifizierte Office-Trainerin mit den Schwerpunkten Excel, Access, Word
- Entwicklerin von Excel-Tools zur Projekt-, Produktions- und Verkaufssteuerung
- Coach bei der Datenaufbereitung mit Access, Power Query und Power Pivot
- Entwicklerin von Access-Datenbanken für Mittelständler und Behörden
- Bloggerin zu Excel und Access auf www.huegemann-informatik.de
- Autorin für Microsoft Press, dpunkt.verlag und das »Projekt Magazin«

2 Comments so far

Excel 2010: Mit Ampeln & Co. klare Zeichen setzen, aber nach welchem System werden die Farben zugeteilt? | Hügemann-InformatikPosted on  4:04 pm - Jul 22, 2012

[…] Gibt es Zahlen, die mehrfach vorkommen, so kann sich die Vergabe der verschiedenen Farbpfeile verschieben. Weitere Informationen dazu im Beitrag Was bewirkt eigentlich die Regel ‘Obere 10%’?. […]

Frank Arendt-TheilenPosted on  1:20 pm - Sep 12, 2011

Hallo,
für das erste Szenario benutzt Excel die folgende Formel für eine Zelle im Bereich B2:B13:

=WENN(GANZZAHL(ANZAHL($B$2:$B$13)*45%)>0;KGRÖSSTE($B$2:$B$13;GANZZAHL(ANZAHL($B$2:$B$13)*45%));MAX( $B$2:$B$13))<=BX

wobei für BX die jeweilige einzelne Zellenadresse von B2:B13 steht.

Dies lässt sich mit dem folgenden Verfahren ermitteln:
Szenario 1 mit Excel 2007/2010 im *.xls-Dateiformat speichern.
Anschließend diese Arbeitsmappe mit Excel 2003 öffnen.
Unter Format/Bedingte Formatierung ist die von Excel benutzte Formel zu finden.

Mit Freundlichem Gruß aus der Rattenfängerstadt Hameln

Frank Arendt-Theilen