Lekcja 2. Funkcje matematyczne i statystyczne w arkuszu — SUMA, ŚREDNIA, LICZ.JEŻELI
ŚredniPo co się tego uczymy?
Prawdziwa moc arkusza kalkulacyjnego ujawnia się dopiero, gdy zaczynasz UŻYWAĆ gotowych funkcji zamiast liczyć wszystko ręcznie — jedna funkcja potrafi w ułamku sekundy przeanalizować tysiące wierszy danych. To dokładnie umiejętność sprawdzana w zadaniach maturalnych z analizy danych.
Teoria
Podstawowe funkcje agregujące (działające na CAŁYM zakresie komórek):
SUMA(zakres)— suma wszystkich liczb w zakresie.ŚREDNIA(zakres)— średnia arytmetyczna.MAX(zakres)/MIN(zakres)— największa/najmniejsza wartość.ILE.LICZB(zakres)— liczba komórek zawierających LICZBY (pomija puste i tekstowe).
Funkcje warunkowe (liczące/sumujące TYLKO pasujące komórki):
LICZ.JEŻELI(zakres; kryterium)— zlicza, ile komórek w zakresie SPEŁNIA podany warunek, np.=LICZ.JEŻELI(B2:B20; ">50")policzy, ile ocen jest większych niż 50.SUMA.JEŻELI(zakres_warunku; kryterium; zakres_sumowania)— sumuje wartości z JEDNEGO zakresu, ale TYLKO dla wierszy, gdzie INNY zakres spełnia warunek, np.=SUMA.JEŻELI(A2:A20; "Matematyka"; B2:B20)zsumuje oceny TYLKO z matematyki.
Funkcje zaokrąglające:
ZAOKR(liczba; liczba_miejsc)— zaokrągla do podanej liczby miejsc po przecinku (klasyczne zaokrąglanie matematyczne).ZAOKR.GÓRA(liczba; liczba_miejsc)/ZAOKR.DÓŁ(liczba; liczba_miejsc)— zawsze zaokrągla w górę/w dół, niezależnie od cyfry.
MOD(liczba; dzielnik) — reszta z dzielenia (identyczna idea jak operator % w Pythonie/C#), przydatna np. do sprawdzania parzystości (=MOD(A1;2)=0 sprawdza, czy liczba w A1 jest parzysta).
Zagnieżdżanie funkcji — funkcje można WKŁADAĆ jedna w drugą: wynik jednej funkcji staje się ARGUMENTEM innej. Np. =ZAOKR(ŚREDNIA(B2:B20); 2) najpierw oblicza średnią, a POTEM zaokrągla wynik do 2 miejsc po przecinku. Excel wykonuje zawsze NAJPIERW funkcję "wewnętrzną" (bliżej środka nawiasów).
Schemat
ZAGNIEŻDŻANIE FUNKCJI - kolejność wykonania:
=ZAOKR( ŚREDNIA( B2:B20 ) ; 2 )
└──────┬───────┘
1) najpierw liczy się TO (średnia)
└───────────────┬───────────────┘
2) POTEM zaokrągla wynik średniej
PRZYKŁAD DANYCH (oceny z dwóch przedmiotów):
A B
1 Przedmiot Ocena
2 Matematyka 5
3 Fizyka 4
4 Matematyka 3
=SUMA.JEŻELI(A2:A4; "Matematyka"; B2:B4) → 5+3 = 8
=LICZ.JEŻELI(B2:B4; ">=4") → 2 (dwie oceny >= 4)
Przykład z życia
Nauczyciel prowadzący arkusz ocen całej klasy używa ŚREDNIA() do policzenia średniej klasowej, LICZ.JEŻELI(zakres; ">=4") do sprawdzenia, ilu uczniów ma ocenę co najmniej dobrą, i SUMA.JEŻELI() do zsumowania punktów TYLKO z jednego, konkretnego sprawdzianu spośród wielu kolumn — dokładnie te funkcje, których nauczysz się w tej lekcji, są używane codziennie w prawdziwych dziennikach elektronicznych.
funkcje-statystyczne.txt
' Podstawowe funkcje agregujące
=SUMA(B2:B20) ' suma wszystkich ocen
=ŚREDNIA(B2:B20) ' średnia ocen
=MAX(B2:B20) ' najwyższa ocena
=MIN(B2:B20) ' najniższa ocena
' Funkcje warunkowe
=LICZ.JEŻELI(B2:B20; ">=4") ' ile ocen >= 4
=SUMA.JEŻELI(A2:A20; "Matematyka"; B2:B20) ' suma ocen z Matematyki
' Zaokrąglanie
=ZAOKR(ŚREDNIA(B2:B20); 2) ' średnia zaokrąglona do 2 miejsc po przecinku
=MOD(A1; 2) ' reszta z dzielenia przez 2 (sprawdzenie parzystości)Komentarz i wyjaśnienie kodu
SUMA.JEŻELI(A2:A20; "Matematyka"; B2:B20) ma TRZY argumenty w konkretnej kolejności: NAJPIERW zakres, w którym SZUKAMY warunku (kolumna A z nazwami przedmiotów), POTEM sam warunek (tekst "Matematyka" w cudzysłowie), na KOŃCU zakres, z którego SUMUJEMY wartości (kolumna B z ocenami) — to inna kolejność niż mogłoby się wydawać intuicyjnie, warto ją zapamiętać.
W zagnieżdżeniu =ZAOKR(ŚREDNIA(B2:B20); 2) Excel oblicza WEWNĘTRZNĄ funkcję ŚREDNIA(B2:B20) jako pierwszy krok, a jej WYNIK (pojedynczą liczbę) przekazuje jako pierwszy argument do ZAOKR() — to dokładnie ten sam mechanizm co zagnieżdżanie wywołań funkcji w każdym języku programowania.
Ćwiczenie samodzielne
Utwórz arkusz z kolumną nazw przedmiotów i kolumną ocen (10 wierszy, kilka przedmiotów powtarzających się). Oblicz: średnią wszystkich ocen, liczbę ocen co najmniej dobrych (LICZ.JEŻELI), i sumę ocen z jednego, wybranego przedmiotu (SUMA.JEŻELI).
Zadania do pracy własnej
Utwórz listę 10 ocen w kolumnie A. Oblicz: sumę, średnią (zaokrągloną do 2 miejsc po przecinku), najwyższą i najniższą ocenę — każdą w osobnej komórce.
Utwórz arkusz z listą 15 uczniów (kolumna A) i ich wynikami testu w punktach (kolumna B, zakres 0-100). Policz funkcją
LICZ.JEŻELI, ilu uczniów zdało test (próg zaliczenia: 50 punktów), a funkcjąSUMA.JEŻELI— sumę punktów TYLKO tych, którzy zdali (wskazówka: warunek">=50"zastosowany do kolumny B jako zarówno zakresu warunku, jak i zakresu sumowania).Rozbuduj arkusz z zadania średniego o kolumnę C z płcią ucznia ("K"/"M"). Oblicz OSOBNO średni wynik testu dla dziewcząt i dla chłopców (funkcja
ŚREDNIA.JEŻELI, analogiczna składniowo doSUMA.JEŻELI). Dodatkowo sprawdź funkcjąMOD, czy numer w dzienniku (osobna kolumna) jest parzysty czy nieparzysty, i policzLICZ.JEŻELI, ile jest uczniów o parzystych numerach.
Typowe błędy
Zła kolejność argumentów w SUMA.JEŻELI — pomylenie zakresu warunku z zakresem sumowania da BŁĘDNY wynik (albo błąd), mimo że formuła "wygląda" poprawnie.
Zapominanie o cudzysłowach przy warunkach tekstowych — LICZ.JEŻELI(A2:A20; Matematyka) (bez cudzysłowów) da błąd, bo Excel potraktuje Matematyka jako nazwę (nieistniejącą) komórki/zakresu, a nie jako tekst do porównania.
Nieprawidłowe zaznaczenie zakresu przy kopiowaniu formuły z funkcją agregującą — jeśli zakres w SUMA() jest odwołaniem względnym, skopiowanie formuły w dół może przesunąć CAŁY zakres, dając błędne (przesunięte) wyniki zamiast tego samego zakresu dla każdego wiersza.
Nawiązanie do egzaminu zawodowego
Funkcje matematyczne i statystyczne to najczęściej sprawdzany element zadań maturalnych z arkusza kalkulacyjnego — analiza danych (średnie, sumy warunkowe, zliczanie) pojawia się niemal w KAŻDYM zadaniu z Arkusza II. W kolejnej lekcji poznasz funkcje logiczne i tekstowe, które pozwolą jeszcze precyzyjniej klasyfikować i przetwarzać dane.