Składnia i przykłady funkcji ŚREDNIA, MEDIANA, MAX, MIN i WYST.NAJCZĘŚCIEJ.WART – puste komórki a zera, mediana przy parzystej liczbie danych, błąd #N/D!.
Funkcje statystyczne podsumowują cały zbiór liczb jedną wartością: średnią, wartością środkową, najmniejszą, największą albo najczęściej powtarzającą się. W zadaniach maturalnych z plikiem danych to zwykle ostatni krok – najpierw wczytujesz dane do arkusza i ewentualnie przygotowujesz kolumnę pomocniczą, a dopiero potem jedną formułą liczysz wynik, który wpisuje się do pliku z odpowiedziami.
Wszystkie działają tak samo: przyjmują liczby, komórki albo całe zakresy (do 255 argumentów) i zwracają jedną liczbę. Trzy zasady obowiązują w każdej z nich:
MIN. Pusta komórka i komórka z zerem to dla Excela dwie różne rzeczy – to najczęstsze źródło rozjazdu między wynikiem z arkusza a rachunkiem na kartce.#ARG! albo #DZIEL/0!, cała funkcja zwróci błąd. Znaczenie poszczególnych komunikatów opisuje hasło o błędach w Excelu.W polskiej wersji Excela argumenty oddzielamy średnikiem. Argumenty zapisane w nawiasach kwadratowych są opcjonalne.
=ŚREDNIA(liczba1; [liczba2]; …)
=MEDIANA(liczba1; [liczba2]; …)
=MAX(liczba1; [liczba2]; …)
=MIN(liczba1; [liczba2]; …)
=WYST.NAJCZĘŚCIEJ.WART(liczba1; [liczba2]; …)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | uczeń | punkty | poprawka | |
| 2 | Ala | 8 | 0 | |
| 3 | Bartek | 5 | 6 | |
| 4 | Celina | 8 | ||
| 5 | Dawid | 12 | 9 | |
| 6 | Ewa | 5 | 3 | |
| 7 | Filip | nb. | 1 | |
| 8 |
| Formuła | Wynik | Co się dzieje |
|---|---|---|
| =ŚREDNIA(B2:B7) | 7,6 | pięć liczb: 8+5+8+12+5 = 38, a 38 podzielone przez 5 daje 7,6 – tekst nb. z B7 jest pomijany |
| =ŚREDNIA(B1:B7) | 7,6 | nagłówek punkty jest tekstem, więc dołożenie wiersza 1 nic nie zmienia |
| =ŚREDNIA(C2:C7) | 3,8 | zero z C2 jest liczone: suma 19 podzielona przez 5 wartości |
| =ŚREDNIA(C3:C7) | 4,75 | ten sam zakres bez zera: 19 podzielone przez 4 – pusta C4 nie powiększa dzielnika |
| =ŚREDNIA(A2:A7) | #DZIEL/0! | w zakresie nie ma ani jednej liczby, więc nie ma czego dzielić |
| =MEDIANA(B2:B7) | 8 | po uporządkowaniu 5, 5, 8, 8, 12 – środkowa jest trzecia z pięciu wartości |
| =MEDIANA(C2:C7) | 3 | pięć liczb 0, 1, 3, 6, 9 – zero jest pełnoprawną wartością zbioru |
| =MEDIANA(C3:C7) | 4,5 | cztery liczby 1, 3, 6, 9 – parzysta liczba wartości, więc średnia dwóch środkowych: (3+6) podzielone przez 2 |
| =MAX(B2:B7) | 12 | największa liczba w kolumnie punktów |
| =MIN(B2:B7) | 5 | najmniejsza liczba; tekst nb. nie jest brany pod uwagę |
| =MIN(C2:C7) | 0 | zero jest liczbą i wygrywa z jedynką z C7 |
| =MAX(A2:A7) | 0 | zakres bez liczb: MAX i MIN zwracają zero, a nie błąd |
| =MAX(C4) | 0 | pojedyncza pusta komórka zachowuje się tak samo |
| =WYST.NAJCZĘŚCIEJ.WART(B2:B7) | 8 | remis: 8 i 5 powtarzają się po dwa razy, wygrywa ta wartość, która stoi wyżej w zakresie |
| =WYST.NAJCZĘŚCIEJ.WART(B3:B7) | 5 | od wiersza 3 ósemka występuje już tylko raz, więc powtarza się wyłącznie 5 |
| =WYST.NAJCZĘŚCIEJ.WART(C2:C7) | #N/D! | w kolumnie poprawek żadna wartość się nie powtarza |
| Polska nazwa | Angielska nazwa | Co robi |
|---|---|---|
| ŚREDNIA | AVERAGE | średnia arytmetyczna liczb z zakresu |
| MEDIANA | MEDIAN | wartość środkowa uporządkowanego zbioru |
| MAX | MAX | największa liczba w zakresie |
| MIN | MIN | najmniejsza liczba w zakresie |
| WYST.NAJCZĘŚCIEJ.WART | MODE.SNGL | wartość powtarzająca się najczęściej (dominanta) |
| WYST.NAJCZĘŚCIEJ.TABL | MODE.MULT | wszystkie wartości o tej samej najwyższej liczbie wystąpień naraz, jako tablica |
| WYST.NAJCZĘŚCIEJ | MODE | starsza nazwa dominanty, zostawiona dla zgodności ze starymi plikami |
| ILE.LICZB | COUNT | ile komórek zakresu zawiera liczby – tyle samo wartości uśrednia ŚREDNIA |
| ILE.NIEPUSTYCH | COUNTA | ile komórek zakresu jest niepustych, razem z tekstowymi |
| MAX.K | LARGE | k-ta co do wielkości wartość, np. druga albo trzecia najwyższa |
| MIN.K | SMALL | k-ta co do wielkości wartość licząc od najmniejszej |
| ODCH.STANDARD.PRÓBKI | STDEV.S | odchylenie standardowe z próby, liczone metodą n-1 |
| ODCH.STAND.POPUL | STDEV.P | odchylenie standardowe całej populacji, metodą n (starsza nazwa: ODCH.STANDARD.POPUL) |
| POZYCJA.NAJW | RANK.EQ | które miejsce w rankingu zajmuje dana liczba; POZYCJA (RANK) to jej starsza nazwa |
Pusta komórka mylona z zerem. Uczeń, który nie oddał pracy, ma w arkuszu pustą komórkę; uczeń, który oddał pustą kartkę, ma 0. ŚREDNIA policzy te dwie sytuacje zupełnie inaczej: pusta komórka nie powiększa dzielnika, zero powiększa i obniża wynik. Zanim wpiszesz formułę, ustal, jak treść zadania traktuje brak danych. Jeśli chcesz uśrednić tylko realne wyniki, a w kolumnie są wpisane zera, użyj ŚREDNIA.JEŻELI z kryterium "<>0" – patrz funkcje warunkowe.
Liczby zapisane jako tekst po imporcie. Po wczytaniu pliku z danymi kolumna liczb bywa tekstem – poznasz ją po wyrównaniu do lewej strony komórki. ŚREDNIA taki tekst pomija, więc formuła nie zgłosi błędu, tylko po cichu policzy średnią z części danych albo zwróci #DZIEL/0!. Kontrola zajmuje sekundę: =ILE.LICZB(B2:B1000) musi dać tyle samo, co =ILE.NIEPUSTYCH(B2:B1000). Częsta przyczyna to przecinek i kropka dziesiętna – w polskim Excelu separatorem jest przecinek, więc zapis 36.54 z pliku zostaje tekstem.
Nagłówek albo wiersz podsumowania w zakresie. =ŚREDNIA(B1:B7) z tekstowym nagłówkiem jest nieszkodliwe, ale gdy nagłówkiem jest liczba (rok 2008, numer czujnika 10), Excel wliczy ją do średniej i wynik będzie absurdalnie wysoki. Jeszcze groźniejszy jest zakres sięgający wiersza z sumą czy średnią pod tabelą – wtedy liczysz średnią z danych i z ich własnego podsumowania. Zakres zaczynaj od pierwszego wiersza danych i kończ na ostatnim.
Mylenie ŚREDNIA ze ŚREDNIA.JEŻELI. ŚREDNIA uśrednia wszystko, co jest w zakresie. Gdy zadanie mówi „średnia dla klasy 3A" albo „średnie zużycie w styczniu", potrzebna jest wersja warunkowa: =ŚREDNIA.JEŻELI(A2:A100;"3A";B2:B100). Sama ŚREDNIA z ręcznie zaznaczonym fragmentem zakresu też da wynik, ale przy tysiącu wierszy prędzej czy później zaznaczysz o kilka za mało. Opis kryteriów jest w funkcjach warunkowych.
Formatowanie mylone z zaokrągleniem. Ustawienie dwóch miejsc po przecinku zmienia tylko to, co widać – w komórce dalej siedzi pełna wartość, więc dalsze obliczenia idą z pełną dokładnością, a suma wyświetlanych liczb może się nie zgadzać z wyświetlaną sumą. Gdy zadanie każe podać wynik zaokrąglony, zaokrąglaj funkcją: =ZAOKR(ŚREDNIA(B2:B100);2) – patrz funkcje matematyczne.
Traktowanie WYST.NAJCZĘŚCIEJ.WART jak rankingu. Ta funkcja zwraca jedną wartość i nie mówi, ile razy ona wystąpiła. Gdy zadanie prosi o wartość najczęstszą i liczbę jej wystąpień, potrzebne są dwie formuły: =WYST.NAJCZĘŚCIEJ.WART(B2:B1000) oraz =LICZ.JEŻELI(B2:B1000;WYST.NAJCZĘŚCIEJ.WART(B2:B1000)). Gdy zadanie prosi o kilka najczęstszych wartości, jedna funkcja nie wystarczy – trzeba zestawienia z liczbą wystąpień i posortować je malejąco.
Angielska wersja arkusza. Nazwy funkcji zależą od języka programu: AVERAGE, MEDIAN, MAX, MIN i MODE.SNGL (pełna tabela wyżej), a argumenty oddziela się tam zwykle przecinkiem zamiast średnika. Nazwa MODE bez końcówki nadal działa, ale jest starą wersją funkcji.
Arkusz z temperaturami wygląda tak (wiersz 1 to nagłówki, w B6 jest tekst brak). Wpisz wynik każdej formuły.
| A | B | C | |
|---|---|---|---|
| 1 | dzień | temperatura | |
| 2 | pon | 21 | |
| 3 | wt | 19 | |
| 4 | śr | 21 | |
| 5 | czw | 0 | |
| 6 | pt | brak | |
| 7 | sob | 14 | |
| 8 |
| Formuła | Wynik |
|---|---|
| =ŚREDNIA(B2:B7) | |
| =MEDIANA(B2:B7) | |
| =MAX(B2:B7) | |
| =MIN(B2:B7) | |
| =WYST.NAJCZĘŚCIEJ.WART(B2:B7) | |
| =MEDIANA(B2:B5) | |
| =ŚREDNIA(B5:B7) | |
| =MAX(A2:A7) |
Formuła =WYST.NAJCZĘŚCIEJ.WART(B5:B7) nie znajdzie żadnego powtórzenia i zwróci . Gdyby wyczyścić zawartość komórki B5, wynik formuły =ŚREDNIA(B2:B7) wzrósłby do , ponieważ uśrednianych liczb byłoby wtedy , a nie 5.
Średnia to suma wartości podzielona przez ich liczbę, a mediana to wartość, która po uporządkowaniu zbioru stoi pośrodku. Średnią mocno zaburza jedna wartość odstająca: dla zbioru 1, 2, 3, 100 średnia wynosi 26,5, a mediana 2,5. Dlatego przy danych z pojedynczymi ekstremami mediana lepiej opisuje typową wartość.
Najczęściej dlatego, że zakres zawiera puste komórki albo tekst, których Excel nie liczy do dzielnika. Sprawdź, ile wartości naprawdę weszło do obliczeń: =ILE.LICZB(B2:B100). Druga typowa przyczyna to zera – pusta komórka jest pomijana, a zero obniża średnią.
Bierze dwie środkowe wartości uporządkowanego zbioru i zwraca ich średnią. Dla liczb 1, 3, 6, 9 mediana to (3+6)/2, czyli 4,5 – wartość, której w danych w ogóle nie ma. Danych nie trzeba wcześniej sortować, funkcja MEDIANA porządkuje je sama.
Bo w podanym zakresie żadna liczba nie powtarza się ani razu, więc nie ma wartości najczęstszej. To normalne przy danych ciągłych, na przykład przy cenach z dwoma miejscami po przecinku. Jeśli spodziewasz się powtórzeń, sprawdź, czy liczby nie są zapisane jako tekst – tekst jest pomijany i zakres może być dla funkcji faktycznie pusty.
WYST.NAJCZĘŚCIEJ.WART zwraca tę z najczęstszych wartości, która pojawia się w zakresie najwcześniej – nie największą i nie najmniejszą. Jeśli zadanie wymaga wszystkich takich wartości naraz, służy do tego WYST.NAJCZĘŚCIEJ.TABL, która zwraca je jako tablicę. Bezpieczniej jednak zbudować zestawienie z LICZ.JEŻELI i odczytać z niego wszystkie wartości o najwyższej liczbie wystąpień.
Funkcją MAX.K z drugim argumentem równym 2: =MAX.K(B2:B100;2). Analogicznie =MIN.K(B2:B100;3) zwraca trzecią wartość od dołu. Zagnieżdżanie MAX z wykluczaniem już znalezionej wartości nie jest potrzebne.
Sama MAX zwraca liczbę, bez etykiety z sąsiedniej kolumny. Nazwę albo rok odpowiadający tej liczbie wyciąga się osobną formułą: =INDEKS(A2:A100;PODAJ.POZYCJĘ(MAX(B2:B100);B2:B100;0)) albo funkcją WYSZUKAJ.PIONOWO, gdy kolumna z wartościami stoi po lewej stronie kolumny z etykietą.
Tak jest zdefiniowana ta funkcja: gdy argumenty nie zawierają żadnej liczby, MAX i MIN zwracają 0. To zdradliwe, bo wynik wygląda na poprawny – zero łatwo wziąć za prawdziwą wartość minimalną. ŚREDNIA w tej samej sytuacji jest uczciwsza i zwraca #DZIEL/0!.
Do tego hasła nie mamy jeszcze przypisanych pojedynczych zadań. Cały spis zadań maturalnych z tego działu jest tutaj:
Zagadnienia z tego samego obszaru matury – warto je powtórzyć razem: