Excel: Abweichung von Werten einer Liste anzeigen – als Balken oder per Farbe

Foto des Autors

Bei langen Listen von Datenwerten will man oft vor allem die Ausreisser erkennen. In Excel können Sie dazu die Abweichungen recht einfach ermitteln und visualisieren.

So erkennen Sie in einer Liste schnell die Werte, die vom Durchschnitt abweichen

  1. Angenommen, Sie haben in den Feldern B2 bis B11 eine Liste von Werten eingetragen, wie etwa den tageweisen Stromverbrauch .

    Beispiel für eine Liste von Werten
  2. Schreiben Sie in das Feld C2 die Formel=B2-MITTELWERT(B$2:B$11). Die Zelle zeigt nun an, wie sehr der Wert in B2 vom Mittelwert aller Daten abweicht.
  3. Kopieren Sie die Formel durch Ziehen des kleinen Quadrats rechts unten in C2 nach unten in die Felder C3 bis C11. Die Liste sieht nun in etwa so aus:

    Liste mit Abweichungen vom Mittelwert
    Liste mit Abweichungen vom Mittelwert
  4. Markieren Sie die Zellen C2 bis C11
  5. Gehen Sie im Menüband Start auf das Symbol Bedingte Formatierung, wählen darin Datenbalken und beispielsweise die Variante Blaue Datenbalken.
  6. Nun sehen Sie über die Balken in der Spalte C, wie sehr der Wert in der Spalte B abweicht und hilft Ihnen so, die Ausreißer in den Daten zu finden.

    Excel Abweichungen in Werteliste finden
    Die Spalte C zeigt nun optisch die Abweichungen vom Mittelwert an.

Für den Zellbereich bei MITTELWERT() werden die Dollarzeichen genutzt, weil ansonsten beim Kopieren der Formel Excel den Wertebereich automatisch mit nach unten rutschen lassen würde – das wollen wir aber nicht. Mehr zu diesem Thema lesen Sie hier: Relative und absolute Adressierung von Zellen – das ist der Unterschied.

Wollen Sie wissen, wie Sie Einheiten, wie hier das „kWh“ im letzte Screenshot in Ihre eigenen Zellen einbauen? Dazu nutzen Sie die benutzerdefinierte Zellformatierung mit 0,0"kWh" als Inhalt. Mehr dazu finden Sie im Tipp So nutzen Sie Einheiten in Excel-Zellen.

Werbung

So können Sie die Anzeige der Abweichungen für Ihren Fall optimieren

  • Mit der oben beschriebenen Vorgehensweise sehen Sie Abweichungen nach unten und nach oben in verschiedener Farbe. Möchten Sie das nicht, weil Sie lediglich die Abweichung an sich interessiert – wie etwa bei Fertigungstoleranzen –  dann erweitern Sie die Formel aus 2) einfach um den Betrag der Abweichung: =ABS(B2-MITTELWERT(B$2:B$11)). Die Darstellung sieht dann so aus:

    So zeigt Excel nur die absoluten Werte der Abweichung an.
    So zeigt Excel nur die absoluten Werte der Abweichung an.
  • Vielleicht möchten  Sie lieber mittels Färbung anstelle der Balken anzeigen, wo sich die größten Abweichungen ergeben. Dann verwenden Sie bei der Bedingten Formatierung aus Schritt 5) nicht die Datenbanken sondern zum Beispiel Farbskalen – Rot-Gelb-Grün-Farbskala. Die zeigt minimale Abweichungen in Grün an und wechselt zu Gelb bzw. Rot, je größer der Wert abweicht.

    Excel - Rot-Gelb-Grün-Farbskala angewandt
    So sieht die Variante über die Farbskala aus.
  • Falls Sie die Abweichung nicht auf den Mittelwert aller Werte beziehen möchten, sondern auf einen festen Referenzwert, dann verwenden Sie  in der Formel in Spalte C diesen Zahlenwert statt dem Teil MITTELWERT(B$2:B$11).
  • Falls Sie der Zahlenwert in der Spalte C gar nicht so sehr interessiert, sondern eher die Balkenbreite, dann geben Sie den Zellen C2 bis C11 einfach ein helles Grau als Schriftfarbe.
  • Manchmal ist MITTELWERT() vielleicht gar nicht die passende Funktion zu Ihrem Problem. Lesen Sie hier weiter: Das ist der Unterschied zwischen MITTELWERT() und MEDIAN()
Werbung
Foto des Autors

Markus Schraudolph

Markus ist IT-Fachjournalist der ersten Stunde. Seine ersten Texte veröffentlichte er 1987 beim legendären Markt&Technik-Verlag. Seine Spezialität sind Datenbanken und Microsoft Excel. Als PHP-Experte programmierte Markus maßgeblich die erste Version des Tippscout und ist anerkannter Experte für PHP, MySQL und Wordpress.