Wartość odstająca w analizie danych
Wartość odstająca 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ą wypaczyć wyniki, na przykład średnia zbioru danych może nieodzwierciedlać rzeczywistych wartości. Program Excel oferuje kilka przydatnych funkcji, które pomagają w zarządzaniu wartościami odstającymi, więc przyjrzyjmy się temu bliżej.
Szybki przykład
Na poniższym obrazku wartości odstające są dość łatwe do wykrycia – wartość 2 przypisana Ericowi oraz wartość 173 przypisana Ryanowi. W takim zestawie danych łatwo jest zidentyfikować i zająć się tymi wartościami odstającymi ręcznie.
W przypadku większego zbioru danych sytuacja może być bardziej skomplikowana. Możliwość zidentyfikowania wartości odstających i usunięcia ich z obliczeń statystycznych jest kluczowa, dlatego w tym artykule przyjrzymy się, jak to zrobić.
Jak znaleźć wartości odstające w danych
Aby znaleźć wartości odstające w zbiorze danych, wykonujemy następujące kroki:
- Oblicz kwartyl 1 i 3.
- Osestimate rozstęp międzykwartylowy (IQR).
- Zwróć górną i dolną granicę naszego zakresu danych.
- Użyj tych granic, aby zidentyfikować odległe punkty danych.
Do przechowywania tych wartości użyjemy zakresu komórek po prawej stronie zestawu danych widocznego na poniższym obrazku.
Krok pierwszy: oblicz kwartyle
Jeśli podzielisz dane na ćwiartki, każdy z tych zestawów nazywany jest kwartylem. Najniższe 25% liczb w zakresie tworzy pierwszy kwartyl, następne 25% – drugi kwartyl i tak dalej. Podejmujemy ten krok jako pierwszy, ponieważ najczęściej stosowaną definicją wartości odstającej jest punkt danych, który znajduje się więcej niż 1,5 przedziałów międzykwartylowych (IQR) poniżej pierwszego kwartylu oraz 1,5 przedziałów międzykwartylowych powyżej trzeciego kwartylu. Aby określić te wartości, musimy najpierw dowiedzieć się, jakie są kwartyle.
Program Excel udostępnia funkcję KWARTYL do obliczania kwartyli. Wymaga ona dwóch informacji: tablicy i kwarty.
=QUARTILE(array, quart)
Tablica to zakres wartości, które oceniasz. Kwarta to liczba reprezentująca kwartyl, który chcesz zwrócić (np. 1 dla pierwszego kwartylu, 2 dla drugiego kwartylu itd.).
Uwaga: W programie Excel 2010 firma Microsoft wprowadziła funkcje KWARTYL.ODW. i KWARTYL.PRZEDZ.EXC jako udoskonalenia funkcji KWARTYL. KWARTYL jest bardziej kompatybilny wstecz podczas pracy z wieloma wersjami programu Excel.
Wróćmy do naszej przykładowej tabeli.
Aby obliczyć pierwszy kwartyl, możemy użyć następującego wzoru w komórce F2.
=QUARTILE(B2:B14,1)
Podczas wpisywania formuły program Excel udostępnia listę opcji dla argumentu kwarta.
Aby obliczyć trzeci kwartyl, wprowadzamy formułę podobną do poprzedniej w komórce F3, używając trójki zamiast jedynki.
=QUARTILE(B2:B14,3)
Teraz mamy kwartylowe punkty danych wyświetlane w komórkach.
Krok drugi: oceń rozstęp międzykwartylowy
Rozstęp międzykwartylowy (IQR) to środkowe 50% wartości danych. Oblicza się go jako różnicę między wartością z pierwszego i trzeciego kwartylu.
Zamierzamy użyć prostej formuły w komórce F4, która odejmuje pierwszy kwartyl od trzeciego kwartylu:
=F3-F2
Teraz możemy zobaczyć nasz przedział międzykwartylowy.
Krok trzeci: Przywróć dolną i górną granicę
Dolna i górna granica to najmniejsze i największe wartości 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ę w komórce F5, mnożąc wartość IQR przez 1,5, a następnie odejmując ją 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, znowu pomnożymy IQR przez 1,5, ale tym razem dodamy go do punktu danych z trzeciego kwartału:
=F3+(1.5*F4)
Krok czwarty: zidentyfikuj wartości odstające
Teraz, gdy mamy już skonfigurowane wszystkie podstawowe dane, nadszedł czas, aby zidentyfikować nasze wartości odstające – te, które są niższe niż dolna wartość graniczna lub wyższe niż górna granica wartości.
Użyjemy Funkcji LUB, aby wykonać ten test logiczny i pokazać wartości spełniające te kryteria, wprowadzają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ść PRAWDA oznacza wartość odstającą i jak widać, mamy dwie w naszych danych.
Ignorowanie wartości odstających podczas obliczania średniej
Używając funkcji KWARTYL, możemy obliczyć IQR i pracować z najczęściej używaną definicją wartości odstającej. Jednak podczas obliczania średniej średniej dla zakresu wartości i ignorowania wartości odstających, istnieje szybsza i łatwiejsza w użyciu funkcja. Ta technika nie pozwoli zidentyfikować wartości odstającej, jak poprzednio, ale pozwoli nam być elastycznym w odniesieniu do tego, co możemy uznać za naszą część odstającą.
Funkcja, której potrzebujemy, nazywa się TRIMMEAN, a jej składnię można zobaczyć poniżej:
=TRIMMEAN(array, percent)
Tablica to zakres wartości, które chcesz uśrednić. Procent to procent punktów danych, które mają być wykluczone z góry i dołu zestawu danych (można wprowadzić wartość procentową lub 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%)
Podsumowując, masz dwie różne funkcje do obsługi wartości odstających. Niezależnie od tego, czy chcesz je zidentyfikować dla potrzeb raportowania, czy wykluczyć je z obliczeń, takich jak średnie, Excel ma funkcję dostosowaną do Twoich potrzeb.