So markieren Sie statistische Ausreißer in Excel

Bei der Datenanalyse gehen Sie normalerweise davon aus, dass sich Ihre Werte um einen zentralen Datenpunkt (einen Median) herum gruppieren. Aber manchmal fallen einige der Werte zu weit vom Mittelpunkt ab. Diese Werte werden als Ausreißer bezeichnet (sie liegen außerhalb des erwarteten Bereichs). Ausreißer können Ihre statistischen Analysen verfälschen und Sie zu falschen oder irreführenden Schlussfolgerungen über Ihre Daten führen.

Sie können einige einfache Formeln und bedingte Formatierung verwenden, um die Ausreißer in Ihren Daten hervorzuheben.

Der erste Schritt bei der Identifizierung von Ausreißern besteht darin, das statistische Zentrum des Bereichs zu bestimmen. Um diese Lokalisierung durchzuführen, beginnen Sie damit, das 1. und 3. Quartil zu finden. Ein Quartil ist eine statistische Aufteilung eines Datensatzes in vier gleiche Gruppen, wobei jede Gruppe 25 Prozent der Daten ausmacht. Die oberen 25 Prozent einer Sammlung werden als 1. Quartil betrachtet, während die unteren 25 Prozent als 4. Quartil betrachtet werden.

In Excel können Sie Quartilwerte einfach mithilfe der QUARTIL-Funktion abrufen. Diese Funktion erfordert zwei Argumente: einen Datenbereich und die gewünschte Quartilzahl.

Im gezeigten Beispiel sind die Werte in den Zellen E3 und E4 das 1. und 3. Quartil für die Daten im Bereich B3:B20.

Aus diesen beiden Quartilen können Sie die statistischen 50 Prozent des Datensatzes berechnen, indem Sie das 3. Quartil vom 1. Quartil subtrahieren. Diese statistischen 50 Prozent werden als Interquartilsabstand (IQR) bezeichnet. Abbildung 9-18 zeigt den IQR in Zelle E5.

Nun stellt sich die Frage, wie weit von den mittleren 50 Prozent entfernt ein Wert sitzen und dennoch als „angemessener“ Wert angesehen werden kann. Statistiker sind sich im Allgemeinen einig, dass IQR*1,5 verwendet werden kann, um einen vernünftigen oberen und unteren Zaun festzulegen:

Der untere Zaun entspricht dem 1. Quartil – IQR*1,5.

Der obere Zaun entspricht dem 3. Quartil + IQR*1,5.

Wie Sie sehen, berechnen die Zellen E7 und E8 die endgültigen oberen und unteren Zäune. Jeder Wert, der größer als der obere Zaun oder kleiner als der untere Zaun ist, wird als Ausreißer betrachtet.

An dieser Stelle ist die Regel zur bedingten Formatierung einfach zu implementieren.

Gehen Sie folgendermaßen vor, um diese grundlegende Formatierungsregel zu erstellen:

Wählen Sie die Datenzellen in Ihrem Zielbereich aus (in diesem Beispiel Zellen B3: B20), klicken Sie auf die Registerkarte Start des Excel-Menübands und wählen Sie dann Bedingte Formatierung→Neue Regel aus.

Dadurch wird das Dialogfeld Neue Formatierungsregel geöffnet.

Klicken Sie im Listenfeld oben im Dialogfeld auf die Option Mit einer Formel bestimmen, welche Zellen formatiert werden sollen.

Diese Auswahl wertet Werte basierend auf einer von Ihnen angegebenen Formel aus. Wenn ein bestimmter Wert als TRUE ausgewertet wird, wird die bedingte Formatierung auf diese Zelle angewendet.

Geben Sie im Formeleingabefeld die hier angezeigte Formel ein.

Beachten Sie, dass Sie die OR-Funktion verwenden, um den Wert in Ihrer Zielzelle (B3) mit dem oberen und unteren Zaun in den Zellen $E$7 bzw. $E$8 zu vergleichen. Wenn die Zielzelle größer als der obere Zaun oder kleiner als der untere Zaun ist, wird sie als Ausreißer betrachtet und wird daher als TRUE ausgewertet, wodurch die bedingte Formatierung ausgelöst wird.

=ODER(B3<$e$8,b3>$E$7)

Beachten Sie, dass Sie in der Formel die absoluten Referenzdollarsymbole ($) für die Zielzelle (B3) ausschließen. Wenn Sie auf Zelle B3 klicken, anstatt den Zellbezug einzugeben, macht Excel Ihren Zellbezug automatisch absolut. Es ist wichtig, dass Sie die absoluten Referenzdollarsymbole nicht in Ihre Zielzelle aufnehmen, da Sie Excel benötigen, um diese Formatierungsregel basierend auf dem eigenen Wert jeder Zelle anzuwenden.

Klicken Sie auf die Schaltfläche Formatieren.

Dadurch wird das Dialogfeld Zellen formatieren geöffnet, in dem Sie alle Optionen zum Formatieren von Schriftart, Rahmen und Füllung für Ihre Zielzelle haben. Nachdem Sie Ihre Formatierungsoptionen ausgewählt haben, klicken Sie auf die Schaltfläche OK, um Ihre Änderungen zu bestätigen und zum Dialogfeld Neue Formatierungsregel zurückzukehren.

Klicken Sie im Dialogfeld Neue Formatierungsregel auf die Schaltfläche OK, um Ihre Formatierungsregel zu bestätigen.

Wenn Sie Ihre bedingte Formatierungsregel bearbeiten müssen, platzieren Sie Ihren Cursor einfach in einer der Datenzellen innerhalb Ihres formatierten Bereichs und gehen Sie dann zur Registerkarte Start und wählen Sie Bedingte Formatierung→Regeln verwalten. Dadurch wird das Dialogfeld Manager für bedingte Formatierungsregeln geöffnet. Klicken Sie auf die Regel, die Sie bearbeiten möchten, und klicken Sie dann auf die Schaltfläche Regel bearbeiten.