Spis treści:
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 średnia zbioru danych może naprawdę odzwierciedlać twoje wartości. Program Excel udostępnia kilka przydatnych funkcji, które pomagają w zarządzaniu wartościami odstającymi, więc przyjrzyjmy się temu.
Szybki przykład
Na poniższym obrazku wartości odstające są dość łatwe do wykrycia – wartość dwa przypisana Ericowi i wartość 173 przypisana Ryanowi. W takim zestawie danych łatwo jest wykryć i zająć się tymi wartościami odstającymi ręcznie.
W przypadku większego zbioru danych tak się nie stanie. Możliwość zidentyfikowania wartości odstających i usunięcia ich z obliczeń statystycznych jest ważna – i właśnie tym przyjrzymy się, jak to zrobić w tym artykule.
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 (za chwilę porozmawiamy o tym, co to jest).
Oszacuj rozstęp międzykwartylowy (wyjaśnimy je również nieco dalej).
Zwróć górną i dolną granicę naszego zakresu danych.
Użyj tych granic, aby zidentyfikować odległe punkty danych.
Do przechowywania tych wartości zostanie użyty zakres komórek po prawej stronie zestawu danych widocznego na poniższym obrazku.
Zacznijmy.
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 i 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 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 udostępnił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, możemy wprowadzić formułę podobną do poprzedniej w komórce F3, ale 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
Przedział międzykwartylowy (lub 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, ponownie 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 odległe punkty danych – te, które są niższe niż dolna wartość graniczna lub wyższa niż górna granica wartości.
Użyjemy Funkcja 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$6)
Następnie skopiujemy tę wartość do naszych komórek C3-C14. Wartość PRAWDA wskazuje wartość odstającą i jak widać, mamy dwa w naszych danych.
Ignorowanie wartości odstających podczas obliczania średniej ś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%)
Tam masz dwie różne funkcje do obsługi wartości odstających. Niezależnie od tego, czy chcesz je zidentyfikować dla niektórych potrzeb raportowania, czy wykluczyć je z obliczeń, takich jak średnie, Excel ma funkcję dostosowaną do Twoich potrzeb.