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
Angenommen, Sie haben in den Feldern B2 bis B11 eine Liste von Werten eingetragen, wie etwa den tageweisen Stromverbrauch .
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.
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:
Markieren Sie die Zellen C2 bis C11
Gehen Sie im Menüband Start auf das Symbol Bedingte Formatierung, wählen darin Datenbalken und beispielsweise die Variante Blaue Datenbalken.
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.
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.
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:
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.
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()
