Werte über oder unter dem Durchschnittswert in Excel hervorheben

Die bedingte Formatierung in Excel wird oft eingesetzt, um Werte in Zellen, die über oder unter dem Durchschnitt liegen, besonders zu kennzeichnen. In einigen Fällen sind nur die Werte interessant, die einen bestimmten Prozentsatz über oder unter dem Mittelwert einer Zahlenreihe liegen.

Als Beispiel sollen die Tageshöchsttemperaturen eines Monats dienen. Die Liste mit den Temperaturen sieht folgendermaßen aus:

Temperaturen

Es sollen im ersten Schritt die Tage und die Temperaturwerte optisch hervorgehoben werden, deren Werte einen bestimmten Prozentsatz
über der Durchschnittshöchsttemperatur des Monats liegen. Um den Prozentsatz variabel zu gestalten, wird er in einer Zelle eingegeben. In unserem Beispiel ist es die Zelle B35.

Gehen Sie bitte folgendermaßen vor, um die Zellen mit Hilfe der bedingten Formatierung hervorzuheben:

  1. Markieren Sie alle Zellen mit den Datumsangaben und den Temperaturen. In unserem Beispiel ist es der Zellbereich A2:A32.
  2. Klicken Sie im Menüband auf der Registerkarte Start in der Gruppe Formatvorlagen auf die Schaltfläche Bedingte Formatierung.
  3. Wählen Sie den Menüpunkt Neue Regel. Es erscheint das folgende Dialogbild:

    neue Excel-Formatierungsregel

  4. Klicken im Bereich Regeltyp auswählen auf den Eintrag Formel zur Ermittlung der zu formatierenden Zellen verwenden. Der untere Bereich des Dialogbilds sieht danach folgendermaßen aus:

    Excel-Formatierungsregel eingeben

  5. Geben Sie im Feld Werte formatieren, für die diese Formel wahr ist folgenden Ausdruck ein:

    =$B2>=Mittelwert($B$2:$B$32)*(1+$B$35)

    Achten Sie bitte darauf, dass in der Formel die Zelle $B2 so angegeben wird, dass die Spalte B absolut und die Zeile 2 relativ gesetzt sind.

  6. Klicken Sie danach auf die Schaltfläche Formatieren und wählen Sie ein passendes Zellformat für die Zellen aus, die die obige Bedingung erfüllen. Das Dialogbild sieht danach so aus:

    Excel-Formatierungsregel für Durchschnittswert eingeben

  7. Klicken Sie abschließend auf die Schaltfläche OK. Das Tabellenblatt hat folgendes Aussehen:

    Temperaturen mit bedingter Formatierung

Wenn Sie zusätzlich die Tage und Temperaturen hervorheben möchten, die 20%
unter dem Durchschnittswert aller Tageshöchsttemperaturen liegen, geben Sie analog eine zweite Regel ein. Die Formel lautet folgendermaßen:

=$B2<=MITTELWERT($B$2:$B$32)*(1-$B$35)

Das Dialogbild sieht so aus:

Excel-Formatierungsregel für Durchschnittswert eingeben

Das Tabellenblatt hat danach folgendes Aussehen:

Temperaturen mit bedingter Formatierung

Hinweise

  • Wenn Sie den Abweichungsprozentsatz z .B. von 20% auf 25% ändern möchten, brauchen Sie nur den Wert in der Zelle B35 ändern und nicht die Regeln für die bedingte Formatierung.
  • Wenn Sie die Werte, die einen bestimmten Prozentsatz über oder unter dem Durchschnittswert liegen, nicht unterschiedlich formatieren möchten wie in dem obigen Beispiel, benötigen Sie nicht 2 Regeln, sondern können mit der folgenden einen Regel auskommen:

    =ODER($B2>=MITTELWERT($B$2:$B$32)*(1+$B$35);$B2<=MITTELWERT($B$2:$B$32)*(1-$B$35))

Michael Richter

Michael Richter

Autor

Ich unterstütze unsere Kunden in erster Linie durch Programmierung von Datenbankanwendungen und Zusatzmodulen von Microsoft Office. In Seminaren und Tipps auf unserer Blogseite gebe ich mein Wissen weiter.

0 Kommentare

Einen Kommentar abschicken

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Pin It on Pinterest

Share This