Lekcja 2. Formuły i funkcje w analizie danych

Temat 2/ Klasa 2/ Temat 2

1. Formuły i funkcje arkusza kalkulacyjnego

Na poprzedniej lekcji poznaliśmy podstawowe operacje na danych oraz sposób wykonywania obliczeń w Excelu. W przypadku większej liczby danych wygodniejsze od ręcznego wykonywania działań jest korzystanie z gotowych funkcji.

Formuła to wyrażenie rozpoczynające się od znaku =, które wykonuje obliczenia, np.:

=B2*C2

Funkcja to wbudowane w Excel narzędzie realizujące określone działanie na wskazanych danych, np.:

=SUMA(B2:B7)

Zakres B2:B7 obejmuje wszystkie komórki od B2 do B7.

Podstawowe funkcje

Funkcja	Działanie	Przykład
SUMA	Dodaje wartości liczbowe	=SUMA(B2:B7)
ŚREDNIA	Oblicza średnią arytmetyczną	=ŚREDNIA(B2:B7)
MIN	Wskazuje najmniejszą liczbę	=MIN(B2:B7)
MAX	Wskazuje największą liczbę	=MAX(B2:B7)
ILE.LICZB	Zlicza komórki zawierające liczby	=ILE.LICZB(B2:B7)

Funkcja ŚREDNIA nie uwzględnia pustych komórek ani tekstu znajdującego się w zakresie. Uwzględnia natomiast wartość 0, ponieważ jest ona liczbą.

2. Przygotowanie danych do analizy

Utwórz nowy arkusz i wpisz następujące dane, rozpoczynając od komórki A1.

Tabela 1. Wyniki fikcyjnego sprawdzianu (maksymalnie 20 punktów)

Uczeń	Punkty
Ada	16
Bartek	12
Celina	19
Dawid	15
Ewa	18
Filip	10

Nagłówki powinny znajdować się w komórkach A1:B1, a wyniki w A2:B7.

W komórce E1 wpisz liczbę 20 – będzie to maksymalna liczba punktów możliwych do uzyskania.

3. Analiza wyników za pomocą funkcji

Pod tabelą, w komórkach A10, wpisz kolejno:

• Suma punktów
• Średnia punktów
• Najniższy wynik
• Najwyższy wynik
• Liczba wyników

W odpowiednich komórkach kolumny B zastosuj funkcje:

B10: =SUMA(B2:B7)

B11: =ŚREDNIA(B2:B7)

B12: =MIN(B2:B7)

B13: =MAX(B2:B7)

B14: =ILE.LICZB(B2:B7)

Wyniki powinny wynosić odpowiednio: 90, 15, 10, 19 i 6.

Zauważ, że niektóre funkcje zwracają wartości punktowe, a inne liczbę analizowanych danych.

4. Adresowanie względne i bezwzględne

Podczas kopiowania formuł Excel może automatycznie zmieniać adresy komórek.

Adres względny – np. B2 – dostosowuje się do nowego położenia formuły.

Adres bezwzględny – np. $E$1 – nie zmienia się podczas kopiowania formuły.

Załóżmy, że chcemy obliczyć procent punktów uzyskanych przez każdego ucznia.

W komórce C1 wpisz nagłówek Wynik procentowy.

W C2 wprowadź:

=B2/$E$1

Następnie:

1. Skopiuj formułę do komórek C3:C7.
2. Zastosuj format procentowy.
3. Sprawdź otrzymane wartości.

Uczeń	Punkty	Wynik procentowy
Ada	16	80%
Bartek	12	60%
Celina	19	95%
Dawid	15	75%
Ewa	18	90%
Filip	10	50%

Odwołanie do kolumny B zmienia się wraz z kolejnym wierszem, ale formuła zawsze dzieli wynik przez wartość z E1.

Dzięki temu po zmianie maksymalnej liczby punktów wystarczy zmienić jedną komórkę, a wszystkie wyniki procentowe przeliczą się automatycznie.

5. Ćwiczenia praktyczne

Ćwiczenie 1. Suma i średnia wyników

Oblicz sumę punktów wszystkich uczniów oraz średnią arytmetyczną.

Rozwiązanie:

=SUMA(B2:B7) → 90

=ŚREDNIA(B2:B7) → 15

Ćwiczenie 2. Wyniki skrajne

Wskaż najniższy i najwyższy wynik oraz oblicz różnicę między nimi.

Rozwiązanie:

=MIN(B2:B7) → 10

=MAX(B2:B7) → 19

=MAX(B2:B7)-MIN(B2:B7) → 9 punktów.

Ćwiczenie 3. Wyniki procentowe

Oblicz procentowy wynik każdego ucznia, wykorzystując adres bezwzględny komórki E1.

Rozwiązanie:

W C2 wpisz =B2/$E$1 i skopiuj formułę do C7.

Otrzymane wartości: 80%, 60%, 95%, 75%, 90%, 50%.

Ćwiczenie 4. Aktualizacja danych

Filip poprawił wynik z 10 na 14 punktów. Zmień jego wynik i sprawdź nową średnią.

Rozwiązanie:

W komórce B7 wpisz 14.

Nowa suma wynosi 94, a średnia 15,67 punktu po zaokrągleniu do dwóch miejsc po przecinku.

6. Zadania dodatkowe ★

★ Zadanie 1. Zmiana maksymalnej liczby punktów (kontynuuj na danych zmienionych w poprzednim zadaniu)

Maksymalna liczba punktów za sprawdzian zostaje zmieniona z 20 na 25.

1. Zmień wartość E1 na 25.
2. Oblicz wyniki procentowe wszystkich uczniów.
3. Wskaż wynik procentowy Ady i Celiny.
4. Oblicz średnią wyników procentowych.

Rozwiązanie:

Ada: 64%, Celina: 76%.

Średnia wyników procentowych: 63%.

Można ją uzyskać za pomocą =ŚREDNIA(C2:C7).

★ Zadanie 2. Analiza poprawionych wyników

Celina uzyskała ostatecznie 17 punktów zamiast 19.

1. Zmień wynik Celiny.
2. Oblicz nową sumę punktów.
3. Ustal średnią i najwyższy wynik.
4. Wyjaśnij, dlaczego zmienił się wynik funkcji MAX.

Rozwiązanie:

Suma: 92 punktów.

Średnia: około 15,33 punktu.

Najwyższy wynik: 18 punktów (Ewa).

Funkcja MAX zwraca teraz 18, ponieważ wcześniejszy najwyższy wynik 19 został zastąpiony liczbą 17.

★ Zadanie 3. Brakujące wyniki

Do zestawienia należy dopisać dwóch uczniów.

1. W A8 wpisz „Gabriela”, w B8 tekst „nieobecna”.
2. W A9 wpisz „Hubert”, a B9 pozostaw pustą.
3. Oblicz liczbę wyników za pomocą ILE.LICZB.
4. Oblicz średnią i wyjaśnij, czy brakujące wyniki zmieniły obliczenia.

Rozwiązanie:

=ILE.LICZB(B2:B9) → 6

=ŚREDNIA(B2:B9) → 15,33

W zakresie jest nadal sześć wartości liczbowych. Tekst i pusta komórka nie są uwzględniane podczas obliczania średniej.

★ Zadanie 4. Ile punktów brakuje do maksimum?

1. W D1 wpisz „Brakujące punkty”.
2. Napisz formułę obliczającą, ile punktów brakuje każdemu uczniowi do 20.
3. Skopiuj formułę do kolejnych wierszy.
4. Następnie zmień E1 na 25 i sprawdź, jak zmieniły się wyniki.

Rozwiązanie:

W D2 wpisz:

=$E$1-B2

Przy maksimum 20 punktów Filipowi brakuje 6 punktów, a Celinie 3 punktu.

Po zmianie E1 na 25 Filipowi brakuje 11 punktów, a Celinie 8 punktów.

7. Podsumowanie

Funkcje arkusza kalkulacyjnego pozwalają szybko analizować większe zbiory danych i automatycznie aktualizować wyniki po zmianie wartości. Adresowanie względne umożliwia kopiowanie formuł, natomiast adresowanie bezwzględne pozwala odwoływać się do stałych parametrów, np. maksymalnej liczby punktów. Dobór funkcji zależy od tego, jaką informację chcemy uzyskać z analizowanego zbioru.