How (and Why) to Use the Outliers Function in Excel

• Alan Murray

  @Computergaga1

• Updated January 12, 2019, 5:03am EDT

Odstające to wartość, która jest znacznie wyższa lub niższa niż większość wartości w twoich danych. Podczas korzystania z programu Excel do analizowania danych, wartości odstające mogą przechylać wyniki. Na przykład, średnia średnia zestawu danych może naprawdę odzwierciedlać wartości użytkownika. Excel oferuje kilka przydatnych funkcji, które pomagają zarządzać wartościami odstającymi, więc spójrzmy na nie.

Szybki przykład

Na poniższym obrazku wartości odstające są dość łatwe do zauważenia – wartość 2 przypisana Ericowi i wartość 173 przypisana Ryanowi. W przypadku takiego zestawu danych, łatwo jest zauważyć te wartości odstające i poradzić sobie z nimi ręcznie.

W przypadku większego zestawu danych, tak nie będzie. Umiejętność identyfikacji wartości odstających i usunięcia ich z obliczeń statystycznych jest bardzo ważna – i właśnie temu będziemy się przyglądać w tym artykule.

Jak znaleźć wartości odstające w swoich danych

Aby znaleźć wartości odstające w zbiorze danych, wykonujemy następujące kroki:

1. Oblicz 1. i 3. kwartyl (o tym, co to jest, powiemy za chwilę).
2. Oszacuj przedział międzykwartylowy (to również wyjaśnimy za chwilę).
3. Przywróć górną i dolną granicę naszego zakresu danych.
4. Użyj tych granic, aby zidentyfikować odstające punkty danych.

Zakres komórek po prawej stronie zestawu danych widoczny na poniższym obrazku będzie używany do przechowywania tych wartości.

Zacznijmy.

Krok pierwszy: Obliczanie kwartyli

Jeśli podzielisz swoje dane na ćwiartki, każdy z tych zestawów nazywany jest kwartylem. Najniższe 25% liczb w przedziale tworzy pierwszy kwartyl, następne 25% – drugi kwartyl, i tak dalej. Ten krok wykonujemy jako pierwszy, ponieważ najczęściej stosowaną definicją wartości odstającej jest punkt danych, który znajduje się więcej niż 1,5 przedziału międzykwartylowego (IQR) poniżej pierwszego kwartyla i 1,5 przedziału międzykwartylowego powyżej trzeciego kwartyla. Aby określić te wartości, musimy najpierw dowiedzieć się, jakie są kwartyle.

Excel udostępnia funkcję QUARTILE do obliczania kwartyli. Wymaga ona dwóch informacji: tablicy i kwartyla.

=QUARTILE(array, quart)

Tablica to zakres wartości, które są oceniane. A kwartylem jest liczba, która reprezentuje kwartyl, który chcesz zwrócić (np. 1 dla pierwszego kwartyla, 2 dla drugiego kwartyla i tak dalej).

Uwaga: W programie Excel 2010 firma Microsoft udostępniła funkcje QUARTILE.INC i QUARTILE.EXC jako ulepszenia funkcji QUARTILE. Funkcja QUARTILE jest bardziej kompatybilna wstecz podczas pracy w wielu wersjach programu Excel.

Powróćmy do naszej przykładowej tabeli.

Aby obliczyć 1. kwartyl, możemy użyć następującej formuły w komórce F2.

=QUARTILE(B2:B14,1)

Podczas wprowadzania formuły Excel udostępnia listę opcji dla argumentu kwartyle.

Aby obliczyć trzeci kwartyl, możemy wprowadzić formułę taką jak poprzednio w komórce F3, ale z użyciem trójki zamiast jedynki.

=QUARTILE(B2:B14,3)

Teraz mamy punkty danych kwartylowych wyświetlone w komórkach.

Krok drugi: Oszacuj przedział międzykwartylowy

Rozstęp międzykwartylowy (lub IQR) to środkowe 50% wartości w danych. Jest on obliczany jako różnica pomiędzy wartością pierwszego kwartyla a wartością trzeciego kwartyla.

Wykorzystamy prostą formułę w komórce F4, która odejmuje 1. kwartyl od 3. kwartału:

=F3-F2

Teraz możemy zobaczyć wyświetlony zakres międzykwartylowy.

Krok trzeci: Return the Lower and Upper Bounds

Dolna i górna granica to najmniejsza i największa wartość zakresu danych, których chcemy użyć. Wszelkie wartości mniejsze lub większe od tych wartości granicznych są wartościami odstającymi.

Obliczymy dolną granicę graniczną w komórce F5, mnożąc wartość IQR przez 1.5, a następnie odjęcie jej od punktu danych Q1:

=F2-(1.5*F4)

Uwaga: Nawiasy w tym wzorze nie są konieczne, ponieważ część mnożenia zostanie obliczona przed częścią odejmowania, ale ułatwiają odczytanie wzoru.

Aby obliczyć górną granicę w komórce F6, pomnożymy IQR przez 1.5 ponownie, ale tym razem dodajemy ją do punktu danych Q3:

=F3+(1.5*F4)

Krok czwarty: Identify the Outliers

Teraz, gdy mamy już ustawione wszystkie nasze dane bazowe, nadszedł czas, aby zidentyfikować nasze odstające punkty danych – te, które są niższe niż dolna wartość graniczna lub wyższe niż górna wartość graniczna.

Wykorzystamy funkcję OR, aby wykonać ten test logiczny i wskazać wartości spełniające te kryteria, wpisując następującą formułę do komórki C2:

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

Następnie skopiujemy tę wartość do naszych komórek C3-C14. Wartość TRUE oznacza wartość odstającą, a jak widać, mamy dwie takie wartości w naszych danych.

Ignorowanie wartości odstających przy obliczaniu średniej

Użycie funkcji QUARTILE pozwala nam obliczyć IQR i pracować z najczęściej używaną definicją wartości odstającej. Jednakże, gdy obliczamy średnią dla zakresu wartości i ignorujemy wartości odstające, istnieje szybsza i łatwiejsza w użyciu funkcja. Ta technika nie zidentyfikuje wartości odstającej tak jak poprzednio, ale pozwoli nam być elastycznym z tym, co możemy uznać za naszą część odstającą.

Funkcja, której potrzebujemy, nazywa się TRIMMEAN i możesz zobaczyć składnię dla niej poniżej:

=TRIMMEAN(array, percent)

Tablica to zakres wartości, które chcesz uśrednić. Procent to procent punktów danych do wykluczenia z górnej i dolnej części zestawu danych (można go wprowadzić jako procent lub wartość dziesiętną).

W naszym przykładzie wprowadziliśmy poniższą formułę do komórki D3, aby obliczyć średnią i wykluczyć 20% wartości odstających.

=TRIMMEAN(B2:B14, 20%)

Tutaj masz dwie różne funkcje do obsługi wartości odstających. Niezależnie od tego, czy chcesz je zidentyfikować na potrzeby raportowania, czy też wykluczyć z obliczeń takich jak średnie, Excel ma funkcję, która spełni Twoje potrzeby.