Podczas pracy z danymi w programie Excel często występują problemy z obsługą wartości odstających w zestawie danych.
Posiadanie wartości odstających jest dość powszechne we wszystkich rodzajach danych i ważne jest, aby zidentyfikować i potraktować te wartości odstające, aby upewnić się, że twoja analiza jest poprawna i bardziej sensowna.
W tym samouczku pokażę ci jak znaleźć wartości odstające w Exceluoraz niektóre techniki, których używałem w swojej pracy, aby poradzić sobie z tymi wartościami odstającymi.
Co to są wartości odstające i dlaczego ważne jest, aby je znaleźć?
Wartość odstająca to punkt danych, który znajduje się daleko poza innymi punktami danych w zestawie danych. Jeśli masz w danych wartości odstające, może to zniekształcić twoje dane, co może prowadzić do błędnych wniosków.
Podam prosty przykład.
Załóżmy, że 30 osób podróżuje autobusem z miejsca docelowego A do miejsca docelowego B. Wszyscy ludzie są w podobnej grupie wagowej i grupie dochodowej. Na potrzeby tego samouczka rozważmy, że średnia waga wynosi 220 funtów, a średni roczny dochód to 70 000 USD.
Teraz gdzieś w połowie naszej trasy autobus się zatrzymuje i wskakuje Bill Gates.
A teraz, jak myślisz, co to wpłynie na średnią wagę i średni dochód ludzi w autobusie.
Chociaż średnia waga raczej się nie zmieni, średni dochód ludzi w autobusie znacznie wzrośnie.
To dlatego, że dochód Billa Gatesa jest wartością odstającą w naszej grupie, a to daje nam błędną interpretację danych. Przeciętny dochód na każdą osobę w autobusie wyniósłby kilka miliardów dolarów, co znacznie przekracza faktyczną wartość.
Podczas pracy z rzeczywistymi zestawami danych w programie Excel możesz mieć wartości odstające w dowolnym kierunku (tj. dodatnią wartość odstającą lub ujemną wartość odstającą).
Aby upewnić się, że Twoja analiza jest prawidłowa, musisz w jakiś sposób zidentyfikować te wartości odstające, a następnie zdecydować, jak najlepiej je leczyć.
Zobaczmy teraz kilka sposobów znajdowania wartości odstających w programie Excel.
Znajdź wartości odstające, sortując dane
W przypadku małych zestawów danych szybkim sposobem identyfikacji wartości odstających jest po prostu sortowanie danych i ręczne przeglądanie niektórych wartości u góry posortowanych danych.
A ponieważ mogą występować wartości odstające w obu kierunkach, upewnij się, że najpierw posortujesz dane w kolejności rosnącej, a następnie w kolejności malejącej, a następnie przejrzyj najwyższe wartości.
Pokażę ci przykład.
Poniżej mam zestaw danych, w którym mam czas trwania połączeń (w sekundach) dla 15 połączeń obsługi klienta.
Poniżej znajdują się kroki, aby posortować te dane, abyśmy mogli zidentyfikować wartości odstające w zbiorze danych:
- Wybierz nagłówek kolumny, którą chcesz posortować (komórka B1 w tym przykładzie)
- Kliknij kartę Strona główna
- W grupie Edycja kliknij ikonę Sortuj i filtruj.
- Kliknij opcję Sortowanie niestandardowe
- W oknie dialogowym Sortuj wybierz „Czas trwania” z listy rozwijanej Sortuj według i „Od największego do najmniejszego” z listy rozwijanej Kolejność
- Kliknij OK
Powyższe kroki posortują kolumnę czasu trwania połączenia z najwyższymi wartościami u góry. Teraz możesz ręcznie zeskanować dane i sprawdzić, czy są jakieś wartości odstające.
W naszym przykładzie widzę, że dwie pierwsze wartości są znacznie wyższe niż pozostałe (a dwie dolne są znacznie niższe).
Uwaga: Ta metoda działa z małymi zestawami danych, w których można ręcznie skanować dane. Nie jest to metoda naukowa, ale działa dobrze
Znajdowanie wartości odstających za pomocą funkcji kwartyla
Porozmawiajmy teraz o bardziej naukowym rozwiązaniu, które może pomóc w określeniu, czy istnieją jakieś wartości odstające, czy nie.
W statystyce kwartyl to jedna czwarta zbioru danych. Na przykład, jeśli masz 12 punktów danych, pierwszy kwartyl będzie trzema dolnymi punktami danych, drugi kwartyl będzie kolejnymi trzema punktami danych i tak dalej.
Poniżej znajduje się zestaw danych, w którym chcę znaleźć wartości odstające. Aby to zrobić, będę musiał obliczyć 1 i 3 kwartyl, a następnie za jego pomocą obliczyć górną i dolną granicę.
Poniżej znajduje się wzór do obliczenia pierwszego kwartyla w komórce E2:
=KWARTYL.PRZEDZ.$($2$:$B$15,1)
a oto ten do obliczenia trzeciego kwartyla w komórce E3:
=KWARTYL.PRZEDZ.$($2$:$B$15,3)
Teraz mogę użyć powyższych dwóch obliczeń, aby uzyskać Rozstęp międzykwartylowy (który stanowi 50% naszych danych w 1. i 3. kwartylu)
=F3-F2
Teraz użyjemy rozstępu międzykwartylowego, aby znaleźć dolną i górną granicę, która zawierałaby większość naszych danych.
Wszystko, co znajduje się poza tymi dolnymi i górnymi limitami, zostanie uznane za wartości odstające.
Poniżej znajduje się wzór do obliczenia dolnej granicy:
=Kwartyl1 - 1,5*(zakres między kwartylami)
co w naszym przykładzie staje się:
=F2-1,5*F4
A wzór do obliczenia górnej granicy to:
=Kwartyl3 + 1,5*(zakres między kwartylami)
co w naszym przykładzie staje się:
=F3+1,5*F4
Teraz, gdy mamy górną i dolną granicę w naszym zestawie danych, możemy wrócić do oryginalnych danych i szybko zidentyfikować te wartości, które nie mieszczą się w tym zakresie.
Szybkim sposobem na zrobienie tego byłoby sprawdzenie każdej wartości i zwrócenie wartości TRUE lub FALSE w nowej kolumnie.
Użyłem poniższej formuły OR, aby uzyskać TRUE dla tych wartości, które są wartościami odstającymi.
=LUB(B2$F$6)
Teraz możesz filtrować kolumnę Outlier i wyświetlać tylko te rekordy, których wartość to TRUE.
Alternatywnie możesz również użyć formatowania warunkowego, aby podświetlić wszystkie komórki, w których wartość jest PRAWDA
Notatka: Chociaż jest to bardziej akceptowana metoda znajdowania wartości odstających w statystykach. Uważam, że ta metoda jest trochę bezużyteczna w rzeczywistych scenariuszach. W powyższym przykładzie dolna granica obliczona przez formułę wynosi -103, podczas gdy zbiór danych, który posiadamy, może być tylko dodatni. Tak więc ta metoda może pomóc nam znaleźć wartości odstające w jednym kierunku (wysokie wartości), jest bezużyteczna w identyfikowaniu wartości odstających w drugim kierunku.Znajdowanie wartości odstających za pomocą funkcji DUŻY/MAŁY
Jeśli pracujesz z dużą ilością danych (wartości w wielu kolumnach), możesz wyodrębnić największe i najmniejsze wartości 5 lub 7 i sprawdzić, czy są w nich jakieś wartości odstające.
Jeśli istnieją jakieś wartości odstające, będziesz w stanie je zidentyfikować bez konieczności przeglądania wszystkich danych w obu kierunkach.
Załóżmy, że mamy poniższy zestaw danych i chcemy wiedzieć, czy istnieją jakieś wartości odstające.
Poniżej znajduje się formuła, która da Ci największą wartość w zbiorze danych:
=DUŻY($ B$2:$ B$16,1)
Podobnie druga największa wartość zostanie podana przez
=DUŻY($ B$2:$ B$16,1)
Jeśli nie korzystasz z platformy Microsoft 365, która ma tablice dynamiczne, możesz użyć poniższej formuły, która poda pięć największych wartości z zestawu danych za pomocą jednej formuły:
=DUŻY($B$2:$B$16;WIERSZ($1:5))
Podobnie, jeśli chcesz mieć najmniejsze 5 wartości, użyj poniższej formuły:
=MAŁY($B$2:$B$16;WIERSZ($1:5))
lub następujące w przypadku, gdy nie masz tablic dynamicznych:
=MAŁY($B$2:$B$16,1)
Po uzyskaniu tych wartości naprawdę łatwo jest znaleźć wszelkie wartości odstające w zbiorze danych.
Chociaż zdecydowałem się wyodrębnić największe i najmniejsze 5 wartości, możesz wybrać 7 lub 10 w zależności od tego, jak duży jest Twój zbiór danych.
Nie jestem pewien, czy jest to akceptowalna metoda wyszukiwania wartości odstających w Excelu, czy nie, ale jest to metoda, której używałem, gdy kilka lat temu musiałem pracować z dużą ilością danych finansowych w mojej pracy. W porównaniu ze wszystkimi innymi metodami opisanymi w tym samouczku, uznałem, że ta jest najskuteczniejsza.
Jak prawidłowo radzić sobie z wartościami odstającymi
Do tej pory widzieliśmy metody, które pomogą nam znaleźć wartości odstające w naszym zbiorze danych. Ale co zrobić, gdy wiesz, że istnieją wartości odstające.
Oto kilka metod, których możesz użyć do obsługi wartości odstających, aby analiza danych była poprawna.
Usuń wartości odstające
Najłatwiejszym sposobem usunięcia wartości odstających z zestawu danych jest po prostu ich usunięcie. W ten sposób nie zniekształci Twojej analizy.
Jest to bardziej opłacalne rozwiązanie, gdy masz duże zestawy danych, a usunięcie kilku wartości odstających nie wpłynie na ogólną analizę. I oczywiście przed usunięciem danych upewnij się, że utworzyłeś kopię i zagłębisz się w przyczyny tych wartości odstających.
Normalizuj wartości odstające (dostosuj wartość)
Normalizowanie wartości odstających to to, co robiłem, kiedy byłem w pracy na pełen etat. Dla wszystkich wartości odstających zmieniłbym je po prostu na wartość, która jest nieco wyższa niż maksymalna wartość w zestawie danych.
To upewniło mnie, że nie usuwam danych, ale jednocześnie nie pozwalam, aby przekrzywiało moje dane.
Aby dać ci rzeczywisty przykład, jeśli analizujesz marżę zysku netto firm, gdzie większość firm mieści się w przedziale od -10% do 30%, a istnieje kilka wartości, które są w górę o 100%, ja po prostu zmieniłby te wartości odstające do 30% lub 35%.
Oto niektóre z metod, których możesz użyć w Excel, aby znaleźć wartości odstające.
Po zidentyfikowaniu wartości odstających możesz zagłębić się w dane i poszukać ich przyczyny, jednocześnie wybierając jedną z technik radzenia sobie z tymi wartościami odstającymi (która może polegać na ich usunięciu lub normalizacji poprzez dostosowanie wartości).
Mam nadzieję, że ten samouczek okazał się przydatny.
