Bedingte Formatierung von Werten über oder unter dem Durchschnitt

Die bedingte Formatierung der oberen oder unteren fünf oder zehn Werte u.a. war Gegenstand eines früheren Beitrags.

In diesem Beitrag soll es darum gehen, die Werte einer Datenreihe hervorzuheben, die über oder unter dem Durchschnitt der Datenreihe liegen.

Das schließt ein, dass der Durchschnittswert nicht formatiert wird.

Der Begriff „Durchschnitt“ soll der Excel-Funktionbezeichnung „MITTELWERT“ entsprechen.

1. Der Wertebereich

Das Beispiel stellt ein Unternehmen mit drei Werken dar und zeigt die Produktionsmengen über zwölf Monate eines Jahres.

Um jetzt eine bedingte Formatierung vorzunehmen, ist es nicht notwendig, dass du den Mittelwert kennst.

Es kann aber nützlich sein, ihn zu berechnen.

Markiere den Bereich C3:E14 und schreibe diese Formel:

=MITTELWERT(C3:E14)

Als Ergebnis wird 6595,78 ausgegeben.

2. Werte über dem Durchschnitt formatieren

Dazu markierst du wiederum den Bereich C3:E14.

Klicke jetzt in der Menüleiste unter Formatvorlagen auf „Bedingte Formatierung“, wähle „Regeln für oberste/unterste Werte“ und schließlich in der rechten Spalte auf „Über dem Durchschnitt“.

Es öffnet sich dieses Dialogfeld. Excel schlägt dir vor, die entsprechenden Zellen mit „hellroter Füllung 2“ zu versehen.

Klickst du OK, geschieht das so.

Du könntest stattdessen mit der Drop-Down-Liste aber auch eine gelbe oder eine grüne Füllung, einen roten Text oder einen roten Rahmen auswählen oder etwas anderes benutzerdefiniert entwickeln.

Hier soll es bei der hellroten Füllung bleiben.

Klicke auf OK und deine Tabelle sieht so aus:

Schau dir die Tabelle nun an. Der berechnete Mittelwert belief sich auf 6595,78.

Kein Wert entspricht genau dem Mittelwert. Alle markierten Werte sind größer als der Durchschnitt.

3. Werte unter dem Durchschnitt formatieren

Dazu markierst du wiederum den Bereich C3:E14.

Klicke jetzt in der Menüleiste unter Formatvorlagen auf „Bedingte Formatierung“, wähle „Regeln für oberste/unterste Werte“ und schließlich in der rechten Spalte auf „Unter dem Durchschnitt“.

Es öffnet sich dieses Dialogfeld. Excel schlägt dir vor, die entsprechenden Zellen mit „hellroter Füllung 2“ zu versehen.

Klickst du OK, geschieht das so.

Hier wählst du z.B. „grüne Füllung“.

Klicke auf OK.

Kein Wert entspricht genau dem Mittelwert. Alle markierten Werte sind kleiner als der Durchschnitt.

4. Werte über und unter dem Durchschnitt formatieren

Da keiner der Werte, die über oder unter dem Durchschnitt liegen dem Mittelwert entspricht und keiner der Werte sowohl der einen als auch der anderen Seite entspricht, können beide Formatierungen auch in einer Darstellung gezeigt werden.

Dazu markierst du wiederum den Bereich C3:E14.

Wähle in der bedingten Formatierung zuerst die eine Regel, z.B. über dem Durchschnitt und in einem zweiten Anlauf die Regel unter dem Durchschnitt.

Formatiere die erste Regel wieder mit hellroter und die zweite Regel mit grüner Füllung.

Nun sieht deine Tabelle so aus:

Inwieweit die Zusammenfassung der Darstellung nach mehreren Regeln sinnvoll ist, musst du selbst oder der Informationsempfänger entscheiden.

Die Beispieldatei findest du hinter diesem Link: