Jak korzystać z funkcji wartości odstających w programie Excel

Podczas analizowania danych wartości odstające mogą przekrzywiać wyniki i zmniejszać ich wiarygodność. Na przykład, jeśli co najmniej jedna z Twoich wartości jest znacznie wyższa niż większość danych, zwiększają one średnią, co może nie odzwierciedlać danych jako całości. Usunięcie tych odstających punktów danych jest zatem ważnym krokiem w wykonywaniu wiarygodnych obliczeń statystycznych. Bez dedykowanej funkcji wartości odstających najłatwiejszym sposobem przeprowadzenia testu wartości odstających w programie Excel jest użycie zakres międzykwartylowy do identyfikacji wartości odstających, ale możesz również użyć funkcji Trimmean, aby aproksymować to samo wynik.

Zakres danych międzykwartylowych to zakres objęty przez „pudełko” na wykresie pudełkowo-wąsowym lub więcej dokładnie wynik odjęcia wartości pierwszego kwartyla danych od wartości trzeciego kwartyl. Excel zawiera wbudowaną funkcję obliczania dowolnego kwartyla dla Twoich danych. Znajdź wolną komórkę i wpisz „= KWARTYL ([zakres danych], [liczba kwartyla])” i umieść zakres komórek dla Twoich danych, gdzie jest napisane „[zakres danych]” i żądany kwartyl, gdzie jest napisane „[kwartyl numer]."

Na przykład jeśli masz dane w komórkach od A2 do A101 i chcesz otrzymać wartość z pierwszego kwartyla, wpisz „=KWARTYL(A2:A101, 1)”, aby znaleźć wartość. W pierwszej części argumentu możesz podświetlić odpowiednie komórki za pomocą myszy, ale po przecinku musisz wpisać liczbę żądanego kwartyla. W przypadku trzeciego kwartyla z tymi samymi danymi wpisz „=KWARTYL(A2:A101, 3)”, aby uzyskać wynik.

Używając innej pustej komórki, odejmij wartość komórki pierwszego kwartyla od wartości komórki trzeciego kwartyla. Jeśli pierwszy kwartyl znajduje się w komórce C2, a trzeci kwartyl w komórce D2, wpisz „=D2-C2”, aby uzyskać wynik. To jest przedział międzykwartylowy.

Aby znaleźć wartości odstające, możesz teraz użyć przedziału międzykwartylowego we wzorze na wartości odstające, który mówi, że górną granicą danych jest wartość trzeci kwartyl plus 1,5-krotność przedziału międzykwartylowego, a dolna granica to wartość pierwszego kwartyla minus 1,5-krotność przedziału międzykwartylowego zakres.

Jeśli wartość pierwszego kwartyla znajduje się w C2, wartość trzeciego kwartyla znajduje się w komórce D2, a zakres międzykwartylowy to w komórce E2 należy wpisać „= C2-(1,5 * E2)”, aby znaleźć dolny limit i „= D2+(1,5 * E2)”, aby znaleźć górny limit. Zasadniczo wpisujesz „=[pierwszy kwartyl] – (1,5 * [zakres międzykwartylowy])”, aby znaleźć dolną granicę i „=[trzeci kwartyl] + (1,5 * [zakres międzykwartylowy])”, aby znaleźć górną granicę.

Wszystko poniżej dolnej granicy lub powyżej górnej granicy jest wartością odstającą.

Aby zakończyć test wartości odstających w programie Excel, użyj funkcji logicznej „LUB”, aby w wydajny sposób określić, które wartości w klasie danych są wartościami odstającymi. Wprowadź „=LUB([komórka danych]>[górny limit], [komórka danych] $ F $ 2, A2 < $ G $ 2)”, aby użyć funkcji (gdzie znaki dolara przed „F”, „G” i „2” informują program Excel, że nie powinno się to zmienić po przeciągnięciu formuła w dół).

Jeśli wartość w A2 jest powyżej górnej granicy lub poniżej dolnej granicy, wyświetla się „PRAWDA”, wskazując, że wartość jest wartością odstającą. Możesz przeciągnąć tę formułę w dół, klikając prawy dolny róg komórki z formułą i przeciągając go w dół, aby zakończył się obok ostatniej komórki danych, aby wykonać te same obliczenia na każdym punkt danych.

Możesz także podświetlić dane i przejść do „Formatowania warunkowego” w sekcji „Style” na karcie „Strona główna”, jeśli chcesz zmienić formatowanie wartości odstających. Wybierz „Nowa reguła” i zaznacz opcję „Użyj formuły, aby określić, które komórki należy sformatować”. Wpisz tę samą formułę, co w poprzednim akapicie, a następnie kliknij opcję „Format”, aby wybrać unikatowe formatowanie wartości odstających.

Funkcja „Trimmean” to prostszy sposób identyfikowania wartości odstających. Wpisz „=TRIMMEAN([zakres danych], [proporcja do przycięcia])”, aby użyć funkcji, z zakresem komórek zawierających dane zamiast „[zakres danych]” i liczbą dziesiętną procent, który chcesz przyciąć w miejscu, w którym jest napisane „[proporcja do przycięcia]”. To usuwa skrajne wartości na górze i na dole, a następnie oblicza średnią na podstawie tych pozostały. Tak więc, jeśli zmniejszysz 10 procent, usunie to górne 5 procent i dolne 5 procent przed obliczeniem średniej.

Jeśli dane biegną od A2 do A101 i chcesz przyciąć skrajne 5 procent wartości, wprowadź „=TRIMMEAN(A2:A101, 0,05)”, aby znaleźć skorygowaną średnią. Możesz przyciąć 15 procent, pisząc zamiast tego „=TRIMMEAN(A2:A101, 0.15)”.