Wie (und warum) wird die Ausreißerfunktion in Excel verwendet?

Ein Ausreißer ist ein Wert, der deutlich höher oder niedriger ist als die meisten Werte in Ihren Daten. Bei Verwendung von Excel zur Datenanalyse können Ausreißer die Ergebnisse verzerren. Beispielsweise kann der mittlere Durchschnitt eines Datensatzes Ihre Werte wirklich widerspiegeln. Excel bietet einige nützliche Funktionen zur Verwaltung Ihrer Ausreißer. Schauen wir uns das an.

Ein kurzes Beispiel

In der Abbildung unten sind die Ausreißer relativ leicht zu erkennen - der Wert von zwei, der Eric zugewiesen wurde, und der Wert von 173, der Ryan zugewiesen wurde. In einem solchen Datensatz ist es einfach genug, diese Ausreißer manuell zu erkennen und zu behandeln.

In einem größeren Datensatz ist dies nicht der Fall. Es ist wichtig, die Ausreißer identifizieren und aus statistischen Berechnungen entfernen zu können - und genau darum geht es in diesem Artikel.

So finden Sie Ausreißer in Ihren Daten

Um die Ausreißer in einem Datensatz zu finden, führen wir die folgenden Schritte aus:

  1. Berechnen Sie das 1. und 3. Quartil (wir werden gleich darüber sprechen, was diese sind).
  2. Bewerten Sie den Interquartilbereich (wir werden diese auch etwas weiter unten erläutern).
  3. Geben Sie die oberen und unteren Grenzen unseres Datenbereichs zurück.
  4. Verwenden Sie diese Grenzen, um die abgelegenen Datenpunkte zu identifizieren.

Der Zellbereich rechts neben dem im Bild unten gezeigten Datensatz wird zum Speichern dieser Werte verwendet.

Lass uns anfangen.

Erster Schritt: Berechnen Sie die Quartile

Wenn Sie Ihre Daten in Viertel aufteilen, 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 Interquartilbereiche (IQRs) unterhalb des 1. Quartils und 1,5 Interquartilbereiche oberhalb des 3. Quartils liegt. Um diese Werte zu bestimmen, müssen wir zuerst herausfinden, was die Quartile sind.

Excel bietet eine QUARTILE-Funktion zur Berechnung 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 (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 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.

= QUARTIL (B2: B14,1)

Während 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.

= QUARTIL (B2: B14,3)

Jetzt werden die Quartildatenpunkte in den Zellen angezeigt.

Schritt zwei: Bewerten Sie den Interquartilbereich

Der Interquartilbereich (oder IQR) entspricht den mittleren 50% der Werte in Ihren Daten. Sie wird als Differenz zwischen dem 1. Quartilwert und dem 3. Quartilwert 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 Interquartilbereich sehen.

Schritt drei: Geben Sie die unteren und oberen Grenzen zurück

Die Unter- und Obergrenze 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 Untergrenze 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. Sie erleichtern jedoch das Lesen der Formel.

Um die Obergrenze in Zelle F6 zu berechnen, multiplizieren wir den IQR erneut mit 1,5, fügen ihn diesmal jedoch zum Q3-Datenpunkt hinzu:

= F3 + (1,5 · F4)

Schritt vier: Identifizieren Sie die Ausreißer

Nachdem wir alle 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 ODER-Funktion verwenden, um diesen logischen Test durchzuführen und die Werte anzuzeigen, die diese Kriterien erfüllen, indem wir die folgende Formel in Zelle C2 eingeben:

= ODER (B2 $ F $ 6)

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

Ignorieren der Ausreißer bei der Berechnung des Durchschnitts

Mit der QUARTILE-Funktion können wir den IQR berechnen und mit der am häufigsten verwendeten Definition eines Ausreißers arbeiten. Bei der Berechnung des Durchschnitts für einen Wertebereich und beim Ignorieren von Ausreißern ist jedoch eine schnellere und einfachere Funktion zu verwenden. Diese Technik identifiziert keinen Ausreißer wie zuvor, ermöglicht es uns jedoch, flexibel mit dem umzugehen, was wir als unseren Ausreißeranteil betrachten könnten.

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

= TRIMMEAN (Array, Prozent)

Das Array ist der Wertebereich, den Sie mitteln möchten. Die Prozent ist der Prozentsatz der Datenpunkte von der Ober- und Unterseite des Datensatzes ausgeschlossen werden (man es als ein Prozentsatz oder einen Dezimalwert eingeben kann).

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

= TRIMMEAN (B2: B14, 20%)

Dort haben Sie zwei verschiedene Funktionen zur Behandlung von Ausreißern. Unabhängig davon, ob Sie sie für bestimmte Berichtsanforderungen identifizieren oder von Berechnungen wie Durchschnittswerten ausschließen möchten, bietet Excel eine Funktion, die Ihren Anforderungen entspricht.