Umgang mit Ausreißern in Excel: Eine umfassende Anleitung
Ein Ausreißer ist ein Datenpunkt, der sich deutlich von den anderen Werten in einem Datensatz unterscheidet. Bei der Datenanalyse in Excel können solche Ausreißer die Ergebnisse erheblich verfälschen. Beispielsweise kann der Durchschnitt eines Datensatzes durch Ausreißer verzerrt werden und somit ein falsches Bild der Daten vermitteln. Excel bietet jedoch verschiedene nützliche Funktionen, um Ausreißer zu identifizieren und zu handhaben. In diesem Artikel werden wir diese Funktionen genauer betrachten.
Einleitendes Beispiel
Betrachten wir ein einfaches Beispiel: In einer kleinen Datenmenge sind Ausreißer oft leicht zu erkennen. Im dargestellten Beispiel sind die Werte zwei und 173 klare Ausreißer. Bei kleineren Datensätzen können solche Ausreißer in der Regel manuell erkannt und entsprechend behandelt werden.
Bei umfangreicheren Datensätzen ist eine manuelle Identifizierung von Ausreißern jedoch nicht mehr praktikabel. Es ist daher wichtig, Ausreißer systematisch zu identifizieren und bei statistischen Berechnungen auszuschließen. Genau das werden wir in diesem Artikel erläutern.
Methoden zur Identifizierung von Ausreißern
Die Identifizierung von Ausreißern in einem Datensatz erfolgt in folgenden Schritten:
- Berechnung des ersten und dritten Quartils.
- Bestimmung des Interquartilsabstands (IQR).
- Ermittlung der oberen und unteren Grenzen für den Datenbereich.
- Identifizierung der Datenpunkte, die außerhalb dieser Grenzen liegen.
Zur Speicherung dieser Zwischenwerte verwenden wir den Zellbereich rechts neben dem Datensatz (siehe Abbildung).
Beginnen wir mit dem ersten Schritt.
Schritt 1: Die Berechnung der Quartile
Wenn man Daten in vier gleich große Teile unterteilt, wird jeder dieser Teile als Quartil bezeichnet. Das erste Quartil enthält die niedrigsten 25 % der Werte, das zweite die nächsten 25 % und so weiter. Die häufigste Definition eines Ausreißers ist ein Datenpunkt, der um mehr als 1,5 Interquartilsabstände (IQR) unter dem ersten oder über dem dritten Quartil liegt. Daher ist es wichtig, zunächst die Quartile zu bestimmen.
Excel stellt die Funktion QUARTILE zur Verfügung, um Quartile zu berechnen. Sie benötigt zwei Informationen: die Datenreihe (Array) und die Nummer des Quartils.
=QUARTILE(Datenreihe, Quartilnummer)
Die „Datenreihe“ bezieht sich auf den Bereich von Werten, die analysiert werden sollen, und „Quartilnummer“ ist eine Zahl, die das gewünschte Quartil angibt (z. B. 1 für das erste Quartil, 2 für das zweite usw.).
Hinweis: In Excel 2010 hat Microsoft die Funktionen QUARTILE.INC und QUARTILE.EXC als Erweiterungen der QUARTILE-Funktion eingeführt. QUARTILE bleibt jedoch aus Gründen der Abwärtskompatibilität relevant, wenn man mit verschiedenen Excel-Versionen arbeitet.
Kehren wir zu unserem Beispiel zurück.
Um das erste Quartil zu ermitteln, kann folgende Formel in Zelle F2 eingegeben werden:
=QUARTILE(B2:B14,1)
Bei Eingabe der Formel bietet Excel eine Liste der Optionen für das Quartilargument an.
Um das dritte Quartil zu berechnen, kann eine ähnliche Formel in Zelle F3 verwendet werden, jedoch mit der Zahl 3 anstelle von 1:
=QUARTILE(B2:B14,3)
Nun werden die Quartil-Datenpunkte in den entsprechenden Zellen angezeigt.
Schritt 2: Bestimmung des Interquartilsabstands
Der Interquartilsabstand (IQR) umfasst die mittleren 50 % der Datenwerte. Er wird als Differenz zwischen dem ersten und dritten Quartil berechnet.
Dazu verwenden wir die folgende einfache Formel in Zelle F4, welche das erste Quartil vom dritten Quartil subtrahiert:
=F3-F2
Der Interquartilsabstand wird nun angezeigt.
Schritt 3: Festlegung der oberen und unteren Grenze
Die untere und obere Grenze definieren den akzeptablen Wertebereich. Datenpunkte, die außerhalb dieses Bereichs liegen, werden als Ausreißer betrachtet.
Die untere Grenze berechnen wir in Zelle F5, indem wir den IQR-Wert mit 1,5 multiplizieren und das Ergebnis vom ersten Quartil subtrahieren:
=F2-(1.5*F4)
Hinweis: Die Klammern in der Formel sind zwar nicht notwendig, da die Multiplikation vor der Subtraktion ausgeführt wird, verbessern aber die Lesbarkeit.
Die obere Grenze wird in Zelle F6 berechnet, indem wir den IQR ebenfalls mit 1,5 multiplizieren, dieses Mal das Ergebnis jedoch zum dritten Quartil addieren:
=F3+(1.5*F4)
Schritt 4: Die Identifizierung der Ausreißer
Nachdem alle Grundlagen berechnet wurden, können wir nun die Ausreißer identifizieren – also alle Datenpunkte, die unter der unteren oder über der oberen Grenze liegen.
Dafür nutzen wir die ODER-Funktion, um den logischen Test durchzuführen und alle Werte anzuzeigen, die die entsprechenden Kriterien erfüllen. Geben Sie dazu folgende Formel in Zelle C2 ein:
=OR(B2$F$6)
Wir kopieren diese Formel dann in die Zellen C3 bis C14. Der Wert WAHR zeigt Ausreißer an. Wie man sieht, befinden sich zwei in unseren Daten.
Die Ignorierung von Ausreißern bei der Berechnung des Mittelwerts
Die QUARTILE-Funktion erlaubt es uns, den IQR zu berechnen und damit die gängigste Ausreißerdefinition zu nutzen. Wenn es jedoch primär darum geht, den Mittelwert eines Wertebereichs zu berechnen und dabei Ausreißer zu ignorieren, gibt es eine schnellere und einfachere Methode. Mit dieser Technik werden zwar keine Ausreißer wie oben identifiziert, sie erlaubt uns aber, flexibel zu definieren, welchen Anteil wir als Ausreißer betrachten.
Die benötigte Funktion ist TRIMMITTEL. Ihre Syntax ist wie folgt:
=TRIMMITTEL(Datenreihe, Prozent)
„Datenreihe“ bezieht sich auf den zu mittelnden Wertebereich, und „Prozent“ gibt den Anteil der Datenpunkte an, der am oberen und unteren Ende des Datensatzes ausgeschlossen werden soll (dies kann als Prozentsatz oder als Dezimalwert eingegeben werden).
In unserem Beispiel haben wir die folgende Formel in Zelle D3 eingegeben, um den Mittelwert zu berechnen, bei dem 20 % der Ausreißer ausgeschlossen werden:
=TRIMMITTEL(B2:B14, 20%)
Damit stehen Ihnen zwei verschiedene Methoden zur Handhabung von Ausreißern zur Verfügung. Ob Sie Ausreißer für Berichte identifizieren oder von Berechnungen wie Mittelwerten ausschließen möchten, Excel bietet passende Funktionen für Ihre Bedürfnisse.