Inhaltsverzeichnis
Ein Ausreißer ist ein Wert, der deutlich höher oder niedriger als die meisten Werte in Ihren Daten ist. Bei der Verwendung von Excel zum Analysieren von Daten können Ausreißer die Ergebnisse verzerren. Beispielsweise kann der mittlere Durchschnitt eines Datensatzes Ihre Werte wirklich widerspiegeln. Excel bietet einige nützliche Funktionen, um Ihre Ausreißer zu verwalten, also schauen wir uns das an.
Ein kurzes Beispiel
In der Abbildung unten sind die Ausreißer relativ leicht zu erkennen – der Wert von zwei wird Eric zugewiesen und der Wert von 173 wird Ryan zugewiesen. In einem Datensatz wie diesem ist es einfach, diese Ausreißer manuell zu erkennen und zu behandeln.
Bei einem größeren Datensatz wird dies nicht der Fall sein. Es ist wichtig, die Ausreißer identifizieren und aus statistischen Berechnungen entfernen zu können – und genau das werden wir in diesem Artikel untersuchen.
So finden Sie Ausreißer in Ihren Daten
Um die Ausreißer in einem Datensatz zu finden, verwenden wir die folgenden Schritte:
Berechnen Sie das 1. und 3. Quartil (wir werden gleich darüber sprechen).
Bewerten Sie den Interquartilsabstand (wir werden diese auch etwas weiter unten erklären).
Gibt die obere und untere Grenze unseres Datenbereichs zurück.
Verwenden Sie diese Grenzen, um die abgelegenen Datenpunkte zu identifizieren.
Der Zellbereich auf der rechten Seite des Datensatzes in der Abbildung unten wird verwendet, um diese Werte zu speichern.
Lass uns anfangen.
Schritt eins: Berechnen Sie die Quartile
Wenn Sie Ihre Daten in Quartale 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 gehen diesen Schritt zuerst, weil die am weitesten verbreitete Definition eines Ausreißers ein Datenpunkt ist, der mehr als 1,5 Interquartilsbereiche (IQRs) unter dem 1. Quartil und 1,5 Interquartilsbereiche über dem 3. Quartil liegt. Um diese Werte zu bestimmen, müssen wir zuerst herausfinden, was die Quartile sind.
Excel bietet eine QUARTILE-Funktion zum Berechnen von Quartilen. Es erfordert zwei Informationen: das Array und das Quart.
=QUARTILE(array, quart)
Das Array ist der Wertebereich, den Sie auswerten. Und das Quart ist eine Zahl, die das Quartil darstellt, das Sie zurückgeben möchten (zB 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 QUARTILE-Funktion freigegeben. QUARTILE ist abwärtskompatibel, wenn Sie mit mehreren Excel-Versionen arbeiten.
Kehren wir zu unserer Beispieltabelle zurück.
Um das 1. Quartil zu berechnen, können wir die folgende Formel in Zelle F2 verwenden.
=QUARTILE(B2:B14,1)
Wenn Sie die Formel eingeben, bietet Excel eine Liste mit Optionen für das Quart-Argument.
Um das 3. Quartil zu berechnen, können wir in Zelle F3 eine Formel wie die vorherige eingeben, jedoch eine Drei anstelle einer Eins verwenden.
=QUARTILE(B2:B14,3)
Jetzt haben wir die Quartil-Datenpunkte in den Zellen angezeigt.
Schritt 2: Bewerten Sie den Interquartilbereich
Der Interquartilbereich (oder IQR) sind die mittleren 50 % der Werte in Ihren Daten. Er wird als Differenz zwischen dem 1. Quartil-Wert und dem 3. Quartil-Wert berechnet.
Wir werden eine einfache Formel in Zelle F4 verwenden, die das 1. Quartil vom 3. Quartil subtrahiert:
=F3-F2
Jetzt können wir unseren Interquartilsabstand angezeigt sehen.
Schritt 3: Geben Sie die untere und obere Grenze zurück
Die untere und obere Grenze sind die kleinsten und größten Werte des Datenbereichs, den wir verwenden möchten. Alle Werte, die kleiner oder größer als diese gebundenen Werte sind, sind die Ausreißer.
Wir berechnen die untere Grenze in Zelle F5, indem wir den IQR-Wert mit 1,5 multiplizieren und ihn dann vom Q1-Datenpunkt subtrahieren:
=F2-(1.5*F4)
Hinweis: Die Klammern in dieser Formel sind nicht erforderlich, da der Multiplikationsteil vor dem Subtraktionsteil berechnet wird, aber sie erleichtern die Lesbarkeit der Formel.
Um die obere Grenze in Zelle F6 zu berechnen, multiplizieren wir den IQR erneut mit 1,5, fügen ihn dieses Mal jedoch zum Q3-Datenpunkt hinzu:
=F3+(1.5*F4)
Schritt 4: Identifizieren Sie die Ausreißer
Nachdem wir nun alle unsere zugrunde liegenden Daten eingerichtet haben, ist es an der Zeit, unsere abgelegenen Datenpunkte zu identifizieren – diejenigen, die niedriger als der untere Grenzwert oder höher als der obere Grenzwert sind.
Wir werden die verwenden ODER-Funktion um diesen logischen Test durchzuführen und die Werte anzuzeigen, die diese Kriterien erfüllen, indem Sie die folgende Formel in Zelle C2 eingeben:
=OR(B2$F$6)
Wir kopieren diesen Wert dann in unsere C3-C14-Zellen. Ein TRUE-Wert zeigt einen Ausreißer an, und wie Sie sehen, haben wir zwei in unseren Daten.
Ignorieren der Ausreißer bei der Berechnung des Mittelwerts
Mit der QUARTILE-Funktion können wir den IQR berechnen und mit der am weitesten verbreiteten Definition eines Ausreißers arbeiten. Wenn Sie jedoch den Mittelwert für einen Bereich von Werten berechnen und Ausreißer ignorieren, gibt es eine schnellere und einfachere Funktion. Diese Technik wird keinen Ausreißer wie zuvor identifizieren, aber es ermöglicht uns, flexibel zu sein, was wir als unseren Ausreißeranteil bezeichnen könnten.
Die von uns benötigte Funktion heißt TRIMMEAN, und Sie können die Syntax dafür unten sehen:
=TRIMMEAN(array, percent)
Das Array ist der Wertebereich, der gemittelt werden soll. Der Prozentsatz ist der Prozentsatz der Datenpunkte, die vom oberen und unteren Rand des Datensatzes ausgeschlossen werden sollen (Sie können ihn als Prozentsatz oder als Dezimalwert eingeben).
Wir haben in unserem Beispiel die folgende Formel in Zelle D3 eingegeben, um den Durchschnitt zu berechnen und 20 % der Ausreißer auszuschließen.
=TRIMMEAN(B2:B14, 20%)
Dort stehen Ihnen zwei verschiedene Funktionen zur Behandlung von Ausreißern zur Verfügung. Ob Sie sie für einige Berichtsanforderungen identifizieren oder von Berechnungen wie Durchschnittswerten ausschließen möchten, Excel bietet eine Funktion, die Ihren Anforderungen entspricht.