So finden Sie Ausreißer in Excel (und wie Sie damit umgehen)

Bei der Arbeit mit Daten in Excel haben Sie häufig Probleme beim Umgang mit Ausreißern in Ihrem Datensatz.

Ausreißer sind bei allen Arten von Daten weit verbreitet, und es ist wichtig, diese Ausreißer zu identifizieren und zu behandeln, um sicherzustellen, dass Ihre Analyse korrekt und aussagekräftiger ist.

In diesem Tutorial zeige ich es dir So finden Sie Ausreißer in Excel, und einige der Techniken, die ich in meiner Arbeit verwendet habe, um mit diesen Ausreißern umzugehen.

Was sind Ausreißer und warum ist es wichtig, diese zu finden?

Ein Ausreißer ist ein Datenpunkt, der weit über den anderen Datenpunkten im Datensatz liegt. Wenn Sie einen Ausreißer in den Daten haben, können Ihre Daten verzerrt werden, was zu falschen Schlussfolgerungen führen kann.

Lassen Sie mich Ihnen ein einfaches Beispiel geben.

Nehmen wir an, 30 Personen reisen in einem Bus von Ziel A nach Ziel B. Alle Personen sind in einer ähnlichen Gewichts- und Einkommensgruppe. Nehmen wir für die Zwecke dieses Tutorials an, dass das Durchschnittsgewicht 220 Pfund und das durchschnittliche Jahreseinkommen 70.000 US-Dollar beträgt.

Jetzt hält irgendwo in der Mitte unserer Route der Bus und Bill Gates springt ein.

Nun, was denkst du würde das mit dem Durchschnittsgewicht und dem Durchschnittseinkommen der Leute im Bus machen?

Während sich das Durchschnittsgewicht kaum ändern wird, wird das Durchschnittseinkommen der Busfahrer stark in die Höhe schnellen.

Das liegt daran, dass das Einkommen von Bill Gates in unserer Gruppe ein Ausreißer ist, und das gibt uns eine falsche Interpretation der Daten. Das durchschnittliche Einkommen pro Person im Bus würde einige Milliarden Dollar betragen, was weit über dem tatsächlichen Wert liegt.

Wenn Sie mit tatsächlichen Datensätzen in Excel arbeiten, können Sie Ausreißer in jede Richtung haben (d. h. einen positiven Ausreißer oder einen negativen Ausreißer).

Und um sicherzustellen, dass Ihre Analyse korrekt ist, müssen Sie diese Ausreißer irgendwie identifizieren und dann entscheiden, wie Sie sie am besten behandeln.

Sehen wir uns nun einige Möglichkeiten zum Auffinden von Ausreißern in Excel an.

Finden Sie Ausreißer durch Sortieren der Daten

Bei kleinen Datensätzen besteht eine schnelle Möglichkeit zum Identifizieren von Ausreißern darin, die Daten einfach zu sortieren und einige der Werte oben in diesen sortierten Daten manuell durchzugehen.

Und da es in beide Richtungen Ausreißer geben kann, achten Sie darauf, dass Sie die Daten zuerst aufsteigend und dann absteigend sortieren und dann die Spitzenwerte durchgehen.

Lassen Sie mich Ihnen ein Beispiel zeigen.

Unten habe ich einen Datensatz, in dem ich Anrufdauern (in Sekunden) für 15 Kundendienstanrufe habe.

Im Folgenden sind die Schritte zum Sortieren dieser Daten aufgeführt, damit wir die Ausreißer im Datensatz identifizieren können:

1. Wählen Sie die Spaltenüberschrift der Spalte aus, die Sie sortieren möchten (Zelle B1 in diesem Beispiel).
2. Klicken Sie auf die Registerkarte Start
3. Klicken Sie in der Gruppe Bearbeiten auf das Symbol Sortieren & Filtern.
4. Klicken Sie auf Benutzerdefinierte Sortierung
5. Wählen Sie im Dialogfeld „Sortieren“ in der Dropdown-Liste „Sortieren nach“ die Option „Dauer“ und in der Dropdown-Liste „Reihenfolge“ die Option „Größte bis kleinste“ aus
6. OK klicken

Die obigen Schritte würden die Spalte für die Anrufdauer mit den höchsten Werten ganz oben sortieren. Jetzt können Sie die Daten manuell scannen und nach Ausreißern suchen.

In unserem Beispiel kann ich sehen, dass die ersten beiden Werte viel höher sind als die restlichen Werte (und die unteren beiden sind viel niedriger).

Hinweis: Diese Methode funktioniert mit kleinen Datensätzen, bei denen Sie die Daten manuell scannen können. Es ist keine wissenschaftliche Methode, funktioniert aber gut

Auffinden von Ausreißern mit den Quartilfunktionen

Lassen Sie uns nun über eine wissenschaftlichere Lösung sprechen, mit der Sie feststellen können, ob es Ausreißer gibt oder nicht.

In der Statistik ist ein Quartil ein Viertel des Datensatzes. Wenn Sie beispielsweise 12 Datenpunkte haben, sind das erste Quartil die unteren drei Datenpunkte, das zweite Quartil die nächsten drei Datenpunkte und so weiter.

Unten ist der Datensatz, in dem ich die Ausreißer finden möchte. Dazu muss ich das 1. und das 3. Quartil berechnen und dann damit die Ober- und Untergrenze berechnen.

Unten ist die Formel zur Berechnung des ersten Quartils in Zelle E2:

=QUARTILE.INC($B$2:$B$15,1)

und hier ist die Berechnung des dritten Quartils in Zelle E3:

=QUARTILE.INC($B$2:$B$15,3)

Jetzt kann ich die beiden obigen Berechnungen verwenden, um den Interquartilbereich zu erhalten (der 50% unserer Daten innerhalb des 1. und 3. Quartils ausmacht).

=F3-F2

Jetzt werden wir den Interquartilbereich verwenden, um die untere und obere Grenze zu finden, die die meisten unserer Daten enthalten würde.

Alles, was außerhalb dieser Unter- und Obergrenzen liegt, würde dann als Ausreißer gelten.

Unten ist die Formel zur Berechnung der unteren Grenze:

=Quartil1 - 1,5*(Interquartilbereich)

was in unserem Beispiel zu:

=F2-1,5*F4

Und die Formel zur Berechnung der Obergrenze lautet:

=Quartil3 + 1,5*(Interquartilbereich)

was in unserem Beispiel zu:

=F3+1,5*F4

Da wir nun die obere und untere Grenze in unserem Datensatz haben, können wir zu den ursprünglichen Daten zurückkehren und schnell diejenigen Werte identifizieren, die nicht in diesem Bereich liegen.

Eine schnelle Möglichkeit, dies zu tun, besteht darin, jeden Wert zu überprüfen und in einer neuen Spalte ein TRUE oder FALSE zurückzugeben.

Ich habe die folgende ODER-Formel verwendet, um WAHR für diese Werte zu erhalten, die Ausreißer sind.

=ODER(B2$F$6)

Jetzt können Sie die Ausreißerspalte filtern und nur die Datensätze anzeigen, bei denen der Wert WAHR ist.

Alternativ können Sie auch die bedingte Formatierung verwenden, um alle Zellen hervorzuheben, deren Wert WAHR ist

Notiz: Dies ist zwar eine akzeptiertere Methode, um Ausreißer in der Statistik zu finden. Ich finde diese Methode in realen Szenarien etwas unbrauchbar. Im obigen Beispiel beträgt die durch die Formel berechnete untere Grenze -103, während der Datensatz, den wir haben, nur positiv sein kann. Diese Methode kann uns also helfen, Ausreißer in eine Richtung zu finden (hohe Werte), sie ist nutzlos, um Ausreißer in die andere Richtung zu identifizieren.

Auffinden der Ausreißer mit den LARGE/SMALL-Funktionen

Wenn Sie mit vielen Daten arbeiten (Werte in mehreren Spalten), können Sie die größten und die kleinsten 5 oder 7 Werte extrahieren und prüfen, ob darin Ausreißer enthalten sind.

Wenn es Ausreißer gibt, können Sie diese identifizieren, ohne alle Daten in beide Richtungen durchgehen zu müssen.

Angenommen, wir haben den folgenden Datensatz und möchten wissen, ob es Ausreißer gibt.

Unten ist die Formel, die Ihnen den größten Wert im Datensatz liefert:

=GROSS($B$2:$B$16,1)

In ähnlicher Weise wird der zweitgrößte Wert durch

=GROSS($B$2:$B$16,1)

Wenn Sie Microsoft 365 mit dynamischen Arrays nicht verwenden, können Sie die folgende Formel verwenden und Sie erhalten die fünf größten Werte aus dem Dataset mit einer einzigen Formel:

=GROSS($B$2:$B$16,REIHE($1:5))

Wenn Sie die kleinsten 5 Werte wünschen, verwenden Sie die folgende Formel:

=KLEIN($B$2:$B$16,REIHE($1:5))

oder Folgendes, falls Sie keine dynamischen Arrays haben:

=KLEIN($B$2:$B$16,1)

Sobald Sie diese Werte haben, ist es wirklich einfach, Ausreißer im Dataset zu finden.

Obwohl ich mich dafür entschieden habe, die größten und kleinsten 5 Werte zu extrahieren, können Sie 7 oder 10 auswählen, je nachdem, wie groß Ihr Dataset ist.

Ich bin mir nicht sicher, ob dies eine akzeptable Methode zum Auffinden von Ausreißern in Excel ist oder nicht, aber diese Methode habe ich verwendet, als ich vor einigen Jahren in meinem Job mit vielen Finanzdaten arbeiten musste. Im Vergleich zu allen anderen in diesem Tutorial behandelten Methoden fand ich diese am effektivsten.

Der richtige Umgang mit Ausreißern

Bisher haben wir die Methoden gesehen, die uns helfen, die Ausreißer in unserem Datensatz zu finden. Aber was tun, wenn Sie wissen, dass es Ausreißer gibt?

Hier sind einige Methoden, mit denen Sie Ausreißer behandeln können, damit Ihre Datenanalyse korrekt ist.

Löschen Sie die Ausreißer

Der einfachste Weg, Ausreißer aus Ihrem Datensatz zu entfernen, besteht darin, sie einfach zu löschen. Auf diese Weise wird Ihre Analyse nicht verzerrt.

Dies ist eine praktikablere Lösung, wenn Sie über große Datensätze verfügen und das Löschen einiger Ausreißer keinen Einfluss auf die Gesamtanalyse hat. Und natürlich, bevor Sie die Daten löschen, stellen Sie sicher, dass Sie eine Kopie erstellen und untersuchen, was diese Ausreißer verursacht.

Ausreißer normalisieren (Wert anpassen)

Die Normalisierung der Ausreißer habe ich früher in meinem Vollzeitjob gemacht. Für alle Ausreißerwerte würde ich sie einfach auf einen Wert ändern, der etwas höher als der Maximalwert im Datensatz ist.

Dies stellte sicher, dass ich die Daten nicht lösche, aber gleichzeitig meine Daten nicht verzerren lasse.

Um Ihnen ein Beispiel aus der Praxis zu geben, wenn Sie die Nettogewinnmarge von Unternehmen analysieren, bei denen die meisten Unternehmen zwischen -10% und 30% liegen und es einige Werte gibt, die über 100% liegen, I würde diese Ausreißerwerte einfach auf 30 % oder 35 % ändern.

Dies sind einige der Methoden, die Sie in Excel, um Ausreißer zu finden.

Sobald Sie die Ausreißer identifiziert haben, können Sie in die Daten eintauchen und nach deren Ursachen suchen. Gleichzeitig können Sie eine der Techniken auswählen, um diese Ausreißer zu behandeln (was darin bestehen könnte, diese zu entfernen oder durch Anpassen des Werts zu normalisieren).

Ich hoffe, Sie fanden dieses Tutorial nützlich.