Wie (und warum) man die Ausreißer-Funktion in Excel verwendet

• Alan Murray

  @Computergaga1

• Aktualisiert am 12. Januar 2019, 5:03 Uhr EDT

Ein Ausreißer ist ein Wert, der deutlich höher oder niedriger ist als die meisten Werte in Ihren Daten. Wenn Sie Excel zum Analysieren von Daten verwenden, können Ausreißer die Ergebnisse verzerren. Zum Beispiel könnte der Mittelwert eines Datensatzes Ihre Werte richtig wiedergeben. Excel stellt einige nützliche Funktionen zur Verfügung, um mit Ausreißern umzugehen.

Ein kurzes Beispiel

In der folgenden Abbildung sind die Ausreißer relativ leicht zu erkennen – der Wert von 2, der Eric zugewiesen wurde, und der Wert von 173, der Ryan zugewiesen wurde. In einem Datensatz wie diesem ist es einfach genug, diese Ausreißer zu erkennen und manuell zu behandeln.

In einem größeren Datensatz wird das nicht der Fall sein. Es ist wichtig, die Ausreißer zu identifizieren und aus den statistischen Berechnungen zu entfernen – und genau das werden wir uns in diesem Artikel anschauen, wie man das macht.

Wie Sie Ausreißer in Ihren Daten finden

Um die Ausreißer in einem Datensatz zu finden, verwenden wir die folgenden Schritte:

1. Berechnen Sie das 1. und 3. Quartil (wir werden gleich darüber sprechen, was das ist).
2. Bewerten Sie den Interquartilsbereich (auch diesen werden wir weiter unten erklären).
3. Ermitteln Sie die obere und untere Grenze unseres Datenbereichs.
4. Verwenden Sie diese Grenzen, um die abweichenden Datenpunkte zu identifizieren.

Der Zellbereich auf der rechten Seite des Datensatzes, der im Bild unten zu sehen ist, wird zum Speichern dieser Werte verwendet.

Lassen Sie uns beginnen.

Schritt 1: Berechnen Sie die Quartile

Wenn Sie Ihre Daten in Quartile unterteilen, wird jeder dieser Sätze als Quartil bezeichnet. Die niedrigsten 25 % der Zahlen im Bereich bilden das 1. Quartil, die nächsten 25 % das 2. Quartil und so weiter. Wir machen diesen Schritt zuerst, weil die am weitesten verbreitete Definition eines Ausreißers ein Datenpunkt ist, der mehr als 1,5 Interquartilsbereiche (IQRs) unterhalb des 1. Um diese Werte zu bestimmen, müssen wir zunächst herausfinden, was die Quartile sind.

Excel bietet eine QUARTILE-Funktion zur Berechnung der Quartile. Sie benötigt zwei Informationen: das Array und das Quartil.

=QUARTILE(array, quart)

Werbung

Das Array ist der Bereich von Werten, den Sie auswerten. Und das Quartil ist eine Zahl, die das Quartil repräsentiert, das Sie zurückgeben möchten (z. B. 1 für das 1. Quartil, 2 für das 2. Quartil usw.).

Hinweis: In Excel 2010 hat Microsoft die Funktionen QUARTILE.INC und QUARTILE.EXC als Verbesserungen der Funktion QUARTILE veröffentlicht. QUARTILE ist abwärtskompatibler, wenn Sie über mehrere Excel-Versionen hinweg arbeiten.

Lassen Sie uns zu unserer Beispieltabelle zurückkehren.

Um das 1. Quartil zu berechnen, können wir die folgende Formel in Zelle F2 verwenden.

=QUARTILE(B2:B14,1)

Bei der Eingabe der Formel bietet Excel eine Liste mit Optionen für das Argument Quartil an.

Um das 3. Quartil zu berechnen, können wir eine Formel wie die vorherige in Zelle F3 eingeben, aber mit einer Drei statt einer Eins.

=QUARTILE(B2:B14,3)

Jetzt haben wir die Quartilsdatenpunkte in den Zellen angezeigt bekommen.

Schritt 2: Bewerten Sie den Interquartilsbereich

Der Interquartilsbereich (oder IQR) ist die mittleren 50% der Werte in Ihren Daten. Er wird als die Differenz zwischen dem 1. Quartilswert und dem 3. Quartilswert berechnet.

Wir verwenden eine einfache Formel in Zelle F4, die das 1. Quartil vom 3. Quartil subtrahiert:

=F3-F2

Jetzt können wir unseren Interquartilsbereich anzeigen lassen.

Schritt 3: Liefern Sie die untere und obere Grenze

Die untere und obere Grenze sind der kleinste und größte Wert des Datenbereichs, den wir verwenden wollen. Alle Werte, die kleiner oder größer als diese Grenzwerte sind, sind die Ausreißer.

Wir berechnen die untere Grenze in Zelle F5, indem wir den IQR-Wert mit 1 multiplizieren.5 multipliziert und dann vom Datenpunkt Q1 subtrahiert wird:

=F2-(1.5*F4)

Hinweis: Die Klammern in dieser Formel sind nicht notwendig, da der Multiplikationsteil vor dem Subtraktionsteil berechnet wird, aber sie machen die Formel einfacher zu lesen.

Um die obere Grenze in Zelle F6 zu berechnen, multiplizieren wir den IQR wieder mit 1.5, aber dieses Mal addieren wir ihn zum Q3-Datenpunkt:

=F3+(1.5*F4)

Schritt 4: Identifizieren Sie die Ausreißer

Nun, da wir alle unsere zugrundeliegenden Daten eingerichtet haben, ist es an der Zeit, unsere ausreißenden Datenpunkte zu identifizieren – diejenigen, die niedriger als der untere Grenzwert oder höher als der obere Grenzwert sind.

Wir verwenden die ODER-Funktion, um diesen logischen Test durchzuführen und die Werte anzuzeigen, die diese Kriterien erfüllen, indem wir die folgende Formel in Zelle C2 eingeben:

=OR(B2<$F$5,B2>$F$6)

Wir kopieren diesen Wert dann in unsere Zellen C3-C14. Ein TRUE-Wert zeigt einen Ausreißer an, und wie Sie sehen können, haben wir zwei in unseren Daten.

Ausreißer bei der Berechnung des Mittelwerts ignorieren

Durch die Verwendung der QUARTILE-Funktion können wir den IQR berechnen und mit der am häufigsten verwendeten Definition eines Ausreißers arbeiten. Wenn Sie jedoch den mittleren Durchschnitt für einen Wertebereich berechnen und Ausreißer ignorieren möchten, gibt es eine schnellere und einfachere Funktion, die Sie verwenden können. Diese Technik wird keinen Ausreißer identifizieren, aber sie erlaubt uns, flexibel zu sein, was wir als Ausreißeranteil betrachten.

Die Funktion, die wir brauchen, heißt TRIMMEAN, und Sie können die Syntax dafür unten sehen:

=TRIMMEAN(array, percent)

Das Array ist der Bereich der Werte, die Sie mitteln wollen. Der Prozentsatz ist der Prozentsatz der Datenpunkte, die vom oberen und unteren Ende des Datensatzes ausgeschlossen werden sollen (Sie können ihn als Prozentsatz oder Dezimalwert eingeben).

In unserem Beispiel haben wir die folgende Formel in Zelle D3 eingegeben, um den Durchschnitt zu berechnen und 20 % der Ausreißer auszuschließen.

=TRIMMEAN(B2:B14, 20%)

Hier haben Sie zwei verschiedene Funktionen für den Umgang mit Ausreißern. Egal, ob Sie sie für bestimmte Berichtszwecke identifizieren oder von Berechnungen wie Durchschnittswerten ausschließen wollen, Excel hat eine Funktion, die Ihren Bedürfnissen entspricht.