Arkusze google: oblicz średnią z wyłączeniem wartości odstających

Istnieją dwa sposoby obliczania średniej z wyłączeniem wartości odstających w Arkuszach Google:

1. Użyj TRIMMEAN, aby wykluczyć wartości odstające

2. Użyj rozstępu międzykwartylowego, aby wykluczyć wartości odstające

Aby zilustrować, jak korzystać z obu metod, użyjemy następującego zbioru danych w Arkuszach Google:

Metoda 1: Użyj TRIMMEAN, aby wykluczyć wartości odstające

Funkcja TRIMMEAN oblicza ś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 zaokrągla się do 1.

Zatem ta formuła obliczy średnią wartości z zakresu z wyłączeniem najmniejszej wartości i największej wartości:

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

Metoda 2: 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 Arkuszach Google możemy użyć następującego 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:

Jedyną wartością, która okazuje się wartością odstającą, jest 164 .

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 Arkuszach Google:

Jak obliczyć średnią według grupy w Arkuszach Google
Jak obliczyć średni procent w Arkuszach Google
Jak obliczyć średnią ważoną w Arkuszach Google