Jak obliczyć: średnią, medianę i dominantę - np. wynagrodzeń?

Z lekcji dowiesz się:

• Jak przygotowywać raporty statystyczne?

• Dlaczego wyliczenie średniej może zniekształcać obraz?

• Czym różni się mediana od dominanty?

Mediana to inaczej wartość w środku, natomiast dominanta to inaczej wartość występująca najczęściej.

Zagadnienie to omówimy na przykładowych danych przedstawionych na rys. 1.

(Rys. 1)

W Excelu wyliczenie średniej jest bardzo proste, mianowicie wystarczy wpisać nazwę funkcji ŚREDNIA, a następnie zaznaczyć zakres z którego chcemy tę średnią wartość wyznaczyć (rys. 2). Średnia to inaczej suma wszystkich wartości, podzielona przez ich ilość. Formuła funkcji dla danych Zarobki I powinna wyglądać następująco:

=ŚREDNIA(J2:J10)

(Rys. 2)

Analogicznie wyznaczamy wartość średnią dla danych z tabeli Zarobki II, czyli wpisujemy nazwę funkcji ŚREDNIA w komórce G2 i zaznaczamy zakres danych z tabelki Zarobki II. Formuła funkcji będzie wyglądać następująco:

=ŚREDNIA(M2:M11)

Otrzymamy obliczone średnie wartości danych dla dwóch tabel z zarobkami przedstawione na rys. 3.

(Rys. 3)

W raportach statystycznych najczęściej podawana jest wartość średnia, ale ona niekoniecznie pokazuje prawidłowy obraz danych. W naszych danych wejściowych w ostatnich wierszach celowo zostały podane wartości dużo większe od pozostałych - podciągają one wartość średniej do góry. Z tego właśnie powodu w statystyce stosuje się jeszcze medianę, czyli wartość w środku. Aby lepiej zrozumieć działanie mediany dane zostały posortowane od najmniejszej do największej wartości. Wpisujemy nazwę funkcji MEDIANA, a następnie zaznaczamy zakres, z którego chcemy tę wartość wyciągnąć. Formuła funkcji dla tabeli Zarobki I powinna wyglądać następująco:

=MEDIANA(J2:J10)

Dla pierwszego zestawu danych wartością w środku jest 1900 zł, znajduje się ona dokładnie w połowie (na 5-tym miejscu), co przedstawia rys. 4.

(Rys. 4)

W przypadku nieparzystej liczby elementów w danych wejściowych, bardzo łatwo wyznaczyć medianę, Excel sortuje dane, znajduje ich środek i zwraca wartość z tej komórki (ze środka danych). Natomiast jeśli w zbiorze występuje parzysta liczba elementów, to nie ma tam wartości w środku. Taka sytuacja ma miejsce w tabeli Zarobki II, środek danych został zaznaczony linią na rys. 5.

(Rys. 5)

Dla takiego zestawu danych zapis funkcji będzie wyglądał analogicznie, ale wartość mediany będzie wyznaczona jako średnia z dwóch środkowych komórek (dwóch wartości sąsiadujących z linią wyznaczającą środek danych).

Na dole w pasku formuły możemy podejrzeć, że suma tych dwóch komórek wynosi 4100 zł, które dzielimy przez 2 elementy (rys. 6).

(Rys. 6)

Tym razem funkcja wyznaczająca medianę będzie wyglądała następująco.

=MEDIANA(M2:M11)

Dla obu zakresów danych otrzymamy wyniki przedstawione na rys. 7.

(Rys. 7)

Następnie zajmiemy się wyznaczeniem dominanty, czyli wartości występującej najczęściej. Służy do tego funkcja WYST.NAJCZĘŚCIEJ. W Excelu znajdziemy trzy rodzaje tej funkcji (rys. 8).

(Rys. 8)

W Excelu z roku 2007 była tylko funkcja WYST.NAJCZĘŚCIEJ, która działa tak samo jak funkcja WYST.NAJCZĘŚCIEJ.WART. Funkcja ta zwraca najczęściej występującą w danych wartość. W danych wejściowych wpisaliśmy dwa razy te same wartości, np. dla danych Zarobki I mamy podwójne wartości 1400 zł i 1700 zł (pokolorowane na żółto i niebiesko). Funkcja ta zwróci nam tylko jedną z tych wartości. Zapis funkcji będzie wyglądał następująco:

=WYST.NAJCZĘŚCIEJ.WART(J2:J10)

Analogicznie wygląda sytuacja dla danych z zakresu Zarobki II (mamy powtórzone dane 1400 zł i 2200 zł). Zapis funkcji będzie wyglądał następująco:

=WYST.NAJCZĘŚCIEJ.WART(M2:M11)

Dla obu przypadków otrzymamy tylko jedną wartość (rys. 9).

(Rys. 9)

Zwrócona wartość będzie pierwszą wartością, która się powtórzy w danych. Właśnie z tego powodu Microsoft stworzył nową funkcję WYST.NAJCZĘŚCIEJ.TABL, czyli tablica.

Wpisujemy nazwę funkcji, zaznaczamy zakres danych. Formuła powinna wyglądać następująco:

=WYST.NAJCZĘŚCIEJ.TABL(J2:J10)

Kiedy podejrzymy wynik formuły w trybie edycji komórki klawiszem F9 to otrzymamy dwa wyniki (rys. 10).

(Rys. 10)

W zależności od tego jaką wersją Excela dysponujemy, można różnie obsługiwać te wyniki. Podgląd cofamy za pomocą skrótu klawiszowego Ctrl+Z.

Jeśli pracujemy na Excelu 2019 można posłużyć się funkcją POŁĄCZ.TEKSTY. Pierwszym argumentem funkcji jest ogranicznik, czyli znak rozdzielający dane (", "). Drugi argument to ignoruj_puste, czyli pytanie czy Excel ma zignorować puste komórki. Nie ma to jednak znaczenia, ponieważ funkcja WYST.NAJCZĘŚCIEJ.TABL zawsze zwróci wypełnione pola. Dlatego pomijamy ten argument. Trzeci argument funkcji to Tekst1, czyli wartości jaki zwraca nam funkcja WYST.NAJCZĘSCIEJ.TABL. Zapis funkcji powinien wyglądać następująco:

POŁĄCZ.TEKSTY(",";;WYST.NAJCZĘŚCIEJ.TABL(J2:J10))

Otrzymamy połączone wartości oddzielone ciągiem tekstowym ", " przecinkiem i spacją. Wynik połączenia przedstawiono na rys. 11.

(Rys. 11)

Analogicznie postępujemy dla danych Zarobki II. Zapis funkcji powinien wyglądać następująco:

=WYST.NAJCZĘŚCIEJ.TABL(M2:M11)

Jeśli mamy dostęp do niejawnych testów subskrypcji Excela 365 (programu Insider) to formuły obsługują już operacje na tablicach danych. Niejako wartości zaczynają się rozlewać, czyli automatycznie został dołożony wiersz z drugą powtórzoną wartością (rys. 12).

(Rys. 12)

Formuła jest tylko w komórce G5, a w komórce G6 widzimy wynik rozlania się formuły.

Jeśli w naszych danych wejściowych zmienimy jakieś wartości (np. wartość w komórce M7), to wszystkie formuły się przeliczą i otrzymamy zaktualizowane wyniki (rys. 13).

(Rys. 13)

Po omówieniu poszczególnych wartości statystycznych, można obliczyć wartości średniej, mediany i dominanty dla większego zestawu danych z tabeli z przychodem. Ponieważ jest to tablica danych, bardzo szybko możemy to obliczyć.

W komórce E2 wpisujemy polecenie funkcji ŚREDNIA i zaznaczamy zakres danych w tablicy (kolumna Przychód). Zapis funkcji powinien wyglądać następująco:

=ŚREDNIA(Zarobki[Przychód])

Otrzymamy wynik przedstawiony na rys. 14.

(Rys. 14)

Następnie wpisujemy nazwę funkcji MEDIANA w komórce E3 i zaznaczamy zakres z tablicy Zarobki (kolumna Przychód). Zapis funkcji powinien wyglądać następująco:

=MEDIANA(Zarobki[Przychód])

Otrzymamy medianę dla naszych danych przedstawioną na rys. 15.

(Rys. 15)

Analogicznie dla trzeciego przypadku wpisujemy nazwę funkcji WYST.NAJCZĘŚCIEJ.TABL w komórce E4 i zaznaczamy zakres z tablicy Zarobki (kolumna Przychód). Zapis funkcji powinien wyglądać następująco:

= WYST.NAJCZĘŚCIEJ.TABL(Zarobki[Przychód])

Jeśli zatwierdzimy taką funkcję przed operacjami tablicowymi obsługującymi operacje na tablicach to otrzymamy tylko jeden wynik – mimo, że jak (w trybie edycji komórki) podejrzymy wartość formuły za pomocą klawisza F9 - to widzimy dwie wartości (rys. 16).

(Rys. 16)

Z podglądu formuły wychodzi się za pomocą skrótu klawiszowego Ctrl+Z.

Jeżeli dysponujemy Excelem w starszej wersji (przed 2019), w której znajduje się funkcja POŁĄCZ.TEKSTY, trzeba radzić sobie w inny sposób. Należy wtedy zaznaczyć większy zakres danych (rys. 17).

(Rys. 17)

Następnie wystarczy zatwierdzić formułę - podobnie jak formułę tablicową - czyli skrótem klawiszowym Ctrl+Shift+Enter. Otrzymamy wtedy dwa wyniki przedstawione na rys. 18.

(Rys. 18)

W powyższej sytuacji Excel będzie wypisywał wyniki funkcji zwracającej tablicę danych bezpośrednio do komórek. Jeśli nie zrobi tego automatycznie to należy zapewnić aplikacji dodatkowy zakres danych. Wiąże się to jednak z ryzykiem, że jeśli będzie zbyt szeroki zakres danych, to Excel w dalszych komórkach będzie zwracał błędy (#N/D).

Polecamy także:

• Jak obliczyć kapitalizację odsetek?

• Dynamiczna lista unikatowych wartości