Jak (i dlaczego) używać funkcji Outliers w Excelu
Odstęp jest wartością znacznie wyższą lub niższą niż większość wartości w danych. Podczas korzystania z programu Excel do analizy danych, wartości odstające mogą skosić wyniki. Na przykład średnia średnia z zestawu danych może rzeczywiście odzwierciedlać Twoje wartości. Excel udostępnia kilka przydatnych funkcji pomagających w zarządzaniu wartościami odstającymi, więc spójrzmy.
Szybki przykład
Na poniższym obrazku wartości odstające są dość łatwe do wykrycia - wartość dwóch przypisanych do Erica i wartość 173 przypisanych do Ryana. W takim zestawie danych łatwo jest wykryć i odnieść się do tych wartości odstających ręcznie.
W większym zestawie danych tak nie będzie. Możliwość zidentyfikowania wartości odstających i usunięcia ich z obliczeń statystycznych jest ważna - i właśnie tego będziemy szukać w tym artykule.
Jak znaleźć wartości odstające w danych
Aby znaleźć wartości odstające w zbiorze danych, używamy następujących kroków:
- Obliczyć pierwszy i trzeci kwartyl (będziemy mówić o tym, co jest w tym trochę).
- Oceń zakres międzykwartylowy (wyjaśnimy to nieco dalej).
- Zwróć górną i dolną granicę naszego zakresu danych.
- Użyj tych granic, aby zidentyfikować odległe punkty danych.
Zakres komórek po prawej stronie zestawu danych, pokazany na poniższym obrazku, zostanie wykorzystany do zapisania tych wartości.
Zacznijmy.
Krok pierwszy: Oblicz kwartyle
Jeśli podzielisz dane na ćwiartki, każdy z tych zestawów nazywany jest kwartylem. Najniższe 25% liczb z przedziału stanowi pierwszy kwartyl, następne 25% drugi kwartyl, i tak dalej. Najpierw robimy ten krok, ponieważ najszerzej stosowaną definicją odstający jest punkt danych, który ma więcej niż 1,5 przedziałów kwartylnych (IQR) poniżej pierwszego kwartyla i 1,5 odstępu międzykwartylnego powyżej trzeciego kwartylu. Aby określić te wartości, musimy najpierw ustalić, czym są kwartyle.
Program Excel udostępnia funkcję QUARTILE do obliczania kwartyli. Wymaga dwóch informacji: tablicy i kwarty.
= KWARTYL (tablica, kwarta)
The szyk jest zakresem wartości, które oceniasz. I kwarta to liczba reprezentująca kwartyl, który chcesz zwrócić (np. 1 dla 1ul kwartyl, 2 na drugi kwartyl i tak dalej).
Uwaga: W programie Excel 2010 firma Microsoft opublikowała funkcje QUARTILE.INC i QUARTILE.EXC jako ulepszenia funkcji QUARTILE. QUARTILE jest bardziej kompatybilny z poprzednimi wersjami podczas pracy z wieloma wersjami Excela.
Powróćmy do naszej przykładowej tabeli.
Aby obliczyć 1ul Kwartyle możemy użyć następującej formuły w komórce F2.
= KWARTALNY (B2: B14,1)
Po wprowadzeniu formuły program Excel udostępnia listę opcji argumentu kwarta.
Aby obliczyć 3r & D Kwartał, możemy wprowadzić formułę podobną do poprzedniej w komórce F3, ale używając trzy zamiast jednego.
= KWARTALNY (B2: B14,3)
Teraz mamy dane kwartylowe wyświetlane w komórkach.
Krok drugi: oceń zakres międzykwartylowy
Odstęp międzykwartylowy (lub IQR) jest środkowym 50% wartości w danych. Jest on obliczany jako różnica między pierwszą wartością kwartylową a trzecią wartością kwartylową.
Zamierzamy użyć prostej formuły w komórce F4, która odejmuje 1ul kwartyl od 3r & D kwartyl:
= F3-F2
Teraz możemy zobaczyć nasz rozstęp międzykwartylowy.
Krok trzeci: Zwróć dolną i górną granicę
Dolne i górne ograniczenia są najmniejszymi i największymi wartościami zakresu danych, które chcemy wykorzystać. Wszelkie wartości mniejsze lub większe od tych wartości związanych są wartościami odstającymi.
Obliczymy granicę dolnego ograniczenia 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 obliczy przed częścią odejmującą, ale sprawi, że formuła będzie łatwiejsza do odczytania.
Aby obliczyć górną granicę w komórce F6, pomnożymy IQR ponownie o 1,5, ale tym razem Dodaj do punktu danych Q3:
= F3 + (1,5 * F4)
Krok czwarty: Zidentyfikuj wartości odstające
Teraz, gdy mamy skonfigurowane wszystkie nasze 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 wartość graniczna.
Użyjemy funkcji OR do wykonania tego testu logicznego i pokażemy wartości, które spełniają 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 na odstąpienie, a jak widzisz, mamy dwa w naszych danych.
Ignorowanie wartości odstających podczas obliczania średniej średniej
Za pomocą funkcji QUARTILE obliczmy IQR i pracujemy z najczęściej używaną definicją odstępu. Jednak przy obliczaniu średniej średniej dla zakresu wartości i ignorowaniu wartości odstających istnieje szybsza i łatwiejsza funkcja. Technika ta nie pozwoli zidentyfikować odstający jak poprzednio, ale pozwoli nam być elastycznym z tym, co moglibyśmy uznać za naszą odstającą część.
Funkcja, której potrzebujemy, nazywa się TRIMMEAN i poniżej możesz zobaczyć jej składnię:
= TRIMMEAN (tablica, procent)
The szyk jest zakresem wartości, które chcesz uśrednić. The procent to procent punktów danych, które należy wykluczyć z góry i dołu zbioru danych (można wprowadzić go jako wartość procentową lub dziesiętną).
W poniższym przykładzie wprowadziliśmy poniższy wzór do komórki D3, aby obliczyć średnią i wykluczyć 20% wartości odstających.
= TRIMMEAN (B2: B14, 20%)
Istnieją dwie różne funkcje 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, program Excel ma funkcję dopasowaną do Twoich potrzeb.
