Odrzucenie wartości skrajnych przy wyliczaniu średniej

Przy wszelkiego rodzaju analizach statystycznych wyliczenie wartości średniej jest działaniem bardzo częstym i dającym w wyniku istotną informację o analizowanym materiale. W niektórych zbiorach danych występują wartości bardzo duże lub bardzo małe – znacznie odstające od pozostałych. Mogą one być przyczyną nadzwyczajnej sytuacji lub wręcz błędu/pomyłki. Zaleca się nieuwzględnianie takich ekstremalnych wartości w obliczaniu średniej, gdyż mogą one istotnie wpłynąć na otrzymany wynik, tym samym go wypaczając.

Zamiast posłużyć się prostą funkcją ŚREDNIA(), zastosuj w obliczeniach formułę o poniższej konstrukcji:

=(SUMA(zakres)-MAX(zakres)-MIN(zakres))/(ILE.LICZB(zakres)-2)

Formuła wykorzystuje tylko jeden argument, za to podany czterokrotnie: zakres komórek z analizowanymi wartościami.

Na filmie pokazujemy działanie następującej formuły:

=(SUMA(B3:B8)-MIN(B3:B8)-MAX(B3:B8))/(ILE.LICZB(B3:B8)-2)

W wierszu Średnia podane zostały wyniki obliczone z zastosowaniem funkcji ŚREDNIA(), natomiast w kolejnym – z zastosowaniem podanej formuły. Zauważ, że uwzględnienie skrajnych wartości przy obliczaniu średniej skutkuje znaczną zmianą wyniku, a tym samym pewnym jego przekłamaniem.

Jeśli wartości średnie obliczone na dwa podane sposoby znacznie od siebie odbiegają, należy przeanalizować wartości maksymalne i minimalne pod kątem zasadności ich wystąpienia.

Wyjaśnienie działania formuły:

Odrzucenie wartości skrajnych przy liczeniu wartości średniej przebiega na następujących zasadach:

• Suma wszystkich wartości komórek analizowanego zakresu wyliczana jest z użyciem funkcji SUMA().

• Od uzyskanego wyniku odejmuje się wartość najmniejszą, wyliczoną za pomocą funkcji MIN() oraz wartość największą – wyznaczoną z użyciem funkcji MAX().

• Funkcja ILE.LICZB() wylicza ilość liczb w zakresie, a otrzymany wynik pomniejsza się o 2, ponieważ z zakresu wyłączone zostały dwie wartości: MIN() i MAX().

• Sumę pomniejszoną o wartości skrajne dzieli się przez ilość liczb w zestawieniu pomniejszoną o 2 i otrzymuje się dzięki temu poszukiwaną wartość średnią.

R1O3hStvchFPf

Nagranie filmowe dotyczące odrzucenia wartości skrajnych przy wyliczaniu średniej.

Nagranie filmowe dotyczące odrzucenia wartości skrajnych przy wyliczaniu średniej.

Film dostępny pod adresem /preview/resource/R1O3hStvchFPf

Nagranie filmowe dotyczące odrzucenia wartości skrajnych przy wyliczaniu średniej.

Pobierz plik z danymi:

Ćwiczenie 1

Wyszukaj wyniki jednej z dyscyplin sportowych w których używa się not sędziowskich (np. skoków narciarskich). Używając poznanych funkcji oblicz dla kilku zawodników średnią, odrzucając wartości skrajne. Czy wyniki pokrywają się z rezultatami zawodów?

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

RfAG3Tp5nhfs3

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, B, C, D i E wprowadzono dane dotyczące zawodników i uzyskanych przez nich not sędziowskich. W arkuszu kolejno dodano opisy: w komórce A1 Nota, w komórce B1 Adam Małysz, w komórce C1 Simon Ammann, w komórce D1 Noriaki Kasai, w komórce e1 Thomas Morgenstern. W kolumnie A, w komórkach od A2 do A6 wpisano litery not. W kolumnie B, w komórkach od B2 do B6 wpisano liczbę punktów. W kolumnie C, w komórkach od C2 do C6 wpisano liczbę punktów. W kolumnie D, w komórkach od D2 do D6 wpisano liczbę punktów. W kolumnie E, w komórkach od E2 do E6 wpisano liczbę punktów. Dodatkowo zaprezentowano sposób obliczania średniej bez wartości skrajnych. Formułę obliczania należy wpisać w komórce B7. Brzmi ona następująco: =SUMA(B2:B6)-MAX(B2:B6)-MIN(B2:B6))/ILE.LICZB(B2:B6)-2).

Ćwiczenie 2

Zmodyfikuj przygotowany arkusz, oblicz średnią arytmetyczną wszystkich not dla zawodników, a następnie porównaj ją ze średnią bez wartości skrajnych.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

Rb6K0hytIFN2O

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, B, C, D i E wprowadzono dane dotyczące zawodników i uzyskanych przez nich not sędziowskich. W arkuszu kolejno dodano opisy: w komórce A1 Nota, w komórce A7 Średnia bez wartości skrajnych, w komórce B1 Adam Małysz, w komórce C1 Simon Ammann, w komórce D1 Noriaki Kasai, w komórce E1 Thomas Morgenstern. W kolumnie A, w komórkach od A2 do A6 wpisano litery not. W kolumnie B, w komórkach od B2 do B7 wpisano liczby. W kolumnie C, w komórkach od C2 do C7 wpisano liczby. W kolumnie D, w komórkach od D2 do D7 wpisano liczby. W kolumnie E, w komórkach od E2 do E7 wpisano liczby. W komórkach B7, C7, D7, E7 wpisano wartości liczbowe wyliczonej średniej bez wartości skrajnych. Dodatkowo zaprezentowano sposób obliczania średniej arytmetycznej. Formułę obliczania należy wpisać w komórce B8. Brzmi ona następująco: =ŚREDNIA(B2:B6).