Excel: jak obliczyć średnią z wyłączeniem wartości odstających

Istnieją dwa sposoby obliczenia średniej z wyłączeniem wartości odstających w programie Excel:

1. Oblicz średnią i użyj TRIMMEAN, aby wykluczyć wartości odstające

2. Oblicz średnią i użyj rozstępu międzykwartylowego, aby wykluczyć wartości odstające

Aby zilustrować sposób użycia obu metod, użyjemy następującego zestawu danych w programie Excel:

Metoda 1: Oblicz średnią i użyj TRIMMEAN, aby wykluczyć wartości odstające

Za pomocą funkcji TRIMMEAN w programie Excel można obliczyć średnią z zakresu wartości, wykluczając jednocześnie określony procent obserwacji z góry i z dołu zbioru danych.

Na przykład możemy użyć poniższego wzoru, aby obliczyć średnią wartość w kolumnie A z wyłączeniem łącznie 20% obserwacji (górne 10% i dolne 10%):

 =TRIMMEAN( A2:A16 , 20%)

Ponieważ w naszym zbiorze danych mamy 15 wartości, 10% równa się 1,5, co jest zaokrąglane do 1. Zatem ta formuła obliczy średnią wartości w zakresie, wykluczając najmniejszą i największą wartość:

Średnia, wyłączając wartości odstające, okazuje się wynosić 58,30769 .

Metoda 2: Oblicz średnią i użyj rozstępu międzykwartylowego, aby wykluczyć wartości odstające

Rozstęp międzykwartylowy (IQR) to różnica między 75. percentylem (Q3) a 25. percentylem (Q1) w zbiorze danych. Mierzy rozkład średnich 50% wartości.

Możemy zdefiniować obserwację jako wartość odstającą, jeśli jest 1,5-krotnością rozstępu międzykwartylowego powyżej trzeciego kwartyla (Q3) lub 1,5-krotności rozstępu międzykwartylowego poniżej pierwszego kwartyla (Q1).

Do obliczenia rozstępu międzykwartylowego naszego zbioru danych w Excelu możemy użyć poniższego wzoru:

 =QUARTILE( A2:A16,3 )-QUARTILE( A2:A16,1 )

Poniższy zrzut ekranu pokazuje, jak używać tej formuły:

Następnie możemy użyć następującego wzoru, aby użyć IQR do zidentyfikowania wartości odstających i przypisać „1” dowolnej wartości, która jest wartością odstającą w zbiorze danych:

 =IF(OR( A2 <QUARTILE( $A$2:$A$16 ,1)-1.5* $B$18 , A2 >QUARTILE( $A$2:$A$16 ,3)+1.5* $B$18 ),1, 0)

Poniższy zrzut ekranu pokazuje, jak używać tej formuły:

Widzimy, że tylko jedna wartość – 164 – okazuje się wartością odstającą w tym zbiorze danych.

Na koniec możemy skorzystać z poniższego wzoru, aby obliczyć średnią wszystkich wartości w zbiorze danych, które nie są wartościami odstającymi:

 =AVERAGEIF( B2:B16 , 0, A2:A16 )

Poniższy zrzut ekranu pokazuje, jak używać tej formuły:

Średnia, z wyłączeniem wartości odstających, okazuje się wynosić 55,42857 .

Dodatkowe zasoby

Poniższe samouczki wyjaśniają, jak wykonywać inne typowe zadania w programie Excel:

Jak obliczyć rozstęp międzykwartylowy (IQR) w programie Excel
Jak obliczyć średnią według grupy w programie Excel
Jak obliczyć ważone średnie kroczące w programie Excel