Lekcja 7 - Arkusz kalkulacyjny - zaawansowane funkcje

📚

Teoria

▼

Zaawansowane funkcje arkusza kalkulacyjnego

Arkusze kalkulacyjne (Excel, LibreOffice Calc, Google Sheets) to potezne narzedzia nie tylko do prostych obliczen. Zaawansowane funkcje pozwalaja na analize duzych zbiorow danych, automatyzacje raportow i rozwiazywanie zlozonych problemow.

Funkcja JEZELI (IF)

Funkcja warunkowa - zwraca rozne wartosci w zaleznosci od spelnienia warunku.

                    Skladnia: =JEZELI(warunek; wartosc_jesli_prawda; wartosc_jesli_falsz)

                    English: =IF(condition, value_if_true, value_if_false)

Przyklad: Ocena zaliczenia na podstawie punktow (komorka B2):
=JEZELI(B2>=50;"Zaliczony";"Niezaliczony")

Zagniezdzone JEZELI - przydzielanie ocen:
=JEZELI(B2>=90;"Celujacy";JEZELI(B2>=75;"Bardzo dobry";
 JEZELI(B2>=60;"Dobry";JEZELI(B2>=45;"Dostateczny";
 JEZELI(B2>=30;"Dopuszczajacy";"Niedostateczny")))))

LUB, I, NIE w warunkach:
=JEZELI(I(B2>=50;C2>=50);"Zaliczony";"Niezaliczony")
=JEZELI(LUB(B2>=90;C2>=90);"Wyroznienie";"Brak")

Funkcja WYSZUKAJ.PIONOWO (VLOOKUP)

Wyszukuje wartosc w pierwszej kolumnie tabeli i zwraca wartosc z innej kolumny tego samego wiersza. Niezwykle przydatna do laczenia danych z roznych tabel.

                    Skladnia: =WYSZUKAJ.PIONOWO(szukana_wartosc; tabela; nr_kolumny; dokladnosc)

                    English: =VLOOKUP(lookup_value, table_array, col_index, range_lookup)

                    Ostatni parametr: FALSZ (0) = dokladne dopasowanie, PRAWDA (1) = przyblizzone

Przyklad: Arkusz z cenami produktow (tabela A1:C100)
Kolumna A: Kod produktu
Kolumna B: Nazwa produktu
Kolumna C: Cena

W innym miejscu, wpisujac kod produktu w E1:
Nazwa: =WYSZUKAJ.PIONOWO(E1;A1:C100;2;FALSZ)
Cena:  =WYSZUKAJ.PIONOWO(E1;A1:C100;3;FALSZ)

Przyklad z ocenami (tabela ocen A1:B6):
A1: 0   B1: Niedostateczny
A2: 30  B2: Dopuszczajacy
A3: 45  B3: Dostateczny
A4: 60  B4: Dobry
A5: 75  B5: Bardzo dobry
A6: 90  B6: Celujacy

=WYSZUKAJ.PIONOWO(D1;A1:B6;2;PRAWDA)
(D1 = punkty ucznia, PRAWDA = przyblizzone dopasowanie)

Funkcje statystyczne z warunkami

LICZ.JEZELI - zlicza komorki spelniajace warunek:
=LICZ.JEZELI(B2:B100;">=50")        // ile ocen >= 50
=LICZ.JEZELI(A2:A100;"Kobieta")     // ile kobiet

LICZ.JEZELI (wiele warunkow):
=LICZ.JEZELI(A2:A100;"Kobieta";B2:B100;">=50")

SUMA.JEZELI - sumuje wartosci spelniajace warunek:
=SUMA.JEZELI(C2:C100;B2:B100;"Informatyka")
// sumuje kolumne C gdzie kolumna B = "Informatyka"

SREDNIA.JEZELI:
=SREDNIA.JEZELI(C2:C100;B2:B100;"3a")
// srednia z kolumny C dla klasy "3a"

Formatowanie warunkowe

Automatycznie zmienia wyglad komorek na podstawie ich wartosci. Np. oceny ponizej progu na czerwono, powyzej na zielono.

Zaznacz zakres komorek
Menu: Format > Formatowanie warunkowe
Ustaw reguly: np. "Wartosc komorki < 50" -> Czerwone tlo
Mozna dodac wiele regul z roznymi kolorami

Wprowadzenie do makr

Makro to nagrany lub napisany zestaw instrukcji automatyzujacy powtarzalne czynnosci. W LibreOffice Calc makra pisze sie w Basic, w Excelu - w VBA (Visual Basic for Applications).

' Przyklad makra VBA (Excel) / Basic (LibreOffice):
Sub FormatujOceny()
    Dim i As Integer
    For i = 2 To 100
        If Cells(i, 2).Value >= 50 Then
            Cells(i, 2).Interior.Color = RGB(144, 238, 144) ' zielony
        Else
            Cells(i, 2).Interior.Color = RGB(255, 182, 193) ' czerwony
        End If
    Next i
End Sub

✏️

Zadania

▼

Latwe

Zadanie 1: Dziennik ocen z funkcja JEZELI

Stworz arkusz z listą 15 uczniow i ich punktami z testu (0-100). Uzyj funkcji JEZELI do: a) okreslenia zaliczenia (>=50 pkt), b) przydzielenia oceny (skala 1-6), c) dodaj formatowanie warunkowe (czerwony < 50, zielony >= 50).

Pokaz rozwiazanie

Struktura arkusza:
A1: Lp.    B1: Imie        C1: Punkty    D1: Zaliczenie    E1: Ocena

A2: 1      B2: Anna        C2: 85
D2: =JEZELI(C2>=50;"Tak";"Nie")
E2: =JEZELI(C2>=90;6;JEZELI(C2>=75;5;JEZELI(C2>=60;4;
    JEZELI(C2>=45;3;JEZELI(C2>=30;2;1)))))

Skopiuj formuly D2 i E2 do wierszy 3-16.

Formatowanie warunkowe dla kolumny C:
- Regula 1: Wartosc < 50 -> Tlo czerwone, czcionka biala
- Regula 2: Wartosc >= 50 -> Tlo zielone

Dodatkowe statystyki na dole:
F18: Srednia:    =SREDNIA(C2:C16)
F19: Najwyzsza:  =MAX(C2:C16)
F20: Najnizsza:  =MIN(C2:C16)
F21: Zaliczonych: =LICZ.JEZELI(C2:C16;">="&50)
F22: % zaliczen:  =LICZ.JEZELI(C2:C16;">="&50)/ILE.NIEPUSTYCH(C2:C16)*100

Srednie

Zadanie 2: Cennik z WYSZUKAJ.PIONOWO

Stworz dwa arkusze: 1) Cennik (kod produktu, nazwa, cena) z 10 produktami, 2) Faktura - uzytkownik wpisuje kod produktu i ilosc, a nazwa i cena pobierana jest automatycznie przez WYSZUKAJ.PIONOWO. Dodaj sumowanie calej faktury.

Pokaz rozwiazanie

ARKUSZ 1 - "Cennik":
A1: Kod     B1: Nazwa           C1: Cena
A2: P001    B2: Zeszyt           C2: 3,50
A3: P002    B3: Dlugopis         C3: 2,00
A4: P003    B4: Olowek           C4: 1,50
A5: P004    B5: Gumka            C5: 1,00
A6: P005    B6: Linijka          C6: 4,00
...

ARKUSZ 2 - "Faktura":
A1: Kod     B1: Nazwa     C1: Cena    D1: Ilosc    E1: Wartosc

A2: (uzytkownik wpisuje kod, np. P001)
B2: =WYSZUKAJ.PIONOWO(A2;Cennik.A2:C11;2;FALSZ)
C2: =WYSZUKAJ.PIONOWO(A2;Cennik.A2:C11;3;FALSZ)
D2: (uzytkownik wpisuje ilosc)
E2: =C2*D2

Na dole:
E12: SUMA:  =SUMA(E2:E11)
E13: VAT 23%: =E12*0,23
E14: RAZEM: =E12+E13

Srednie

Zadanie 3: Analiza ankiety

Stworz arkusz z wynikami ankiety (20 odpowiedzi): imie, plec, wiek, ulubiony przedmiot, ocena ze szkoly (1-6). Uzyj funkcji LICZ.JEZELI, SREDNIA.JEZELI do analizy: ile kobiet/mezczyzn, sredni wiek wg plci, najpopularniejszy przedmiot, rozklad ocen.

Pokaz rozwiazanie

Dane w kolumnach A-E (wiersz 1 = naglowki, wiersze 2-21 = dane)

Analiza (np. od kolumny G):
G1: STATYSTYKI ANKIETY

G3: Liczba kobiet:     =LICZ.JEZELI(B2:B21;"K")
G4: Liczba mezczyzn:   =LICZ.JEZELI(B2:B21;"M")

G6: Sredni wiek (K):   =SREDNIA.JEZELI(C2:C21;B2:B21;"K")
G7: Sredni wiek (M):   =SREDNIA.JEZELI(C2:C21;B2:B21;"M")

G9: Informatyka:       =LICZ.JEZELI(D2:D21;"Informatyka")
G10: Matematyka:       =LICZ.JEZELI(D2:D21;"Matematyka")
G11: Fizyka:           =LICZ.JEZELI(D2:D21;"Fizyka")
G12: Polski:           =LICZ.JEZELI(D2:D21;"Polski")

G14: Rozklad ocen:
G15: Ocena 1: =LICZ.JEZELI(E2:E21;1)
G16: Ocena 2: =LICZ.JEZELI(E2:E21;2)
G17: Ocena 3: =LICZ.JEZELI(E2:E21;3)
G18: Ocena 4: =LICZ.JEZELI(E2:E21;4)
G19: Ocena 5: =LICZ.JEZELI(E2:E21;5)
G20: Ocena 6: =LICZ.JEZELI(E2:E21;6)

G22: Srednia ocen: =SREDNIA(E2:E21)

+ Wykres kolowy "Ulubiony przedmiot"
+ Wykres slupkowy "Rozklad ocen"

Trudne

Zadanie 4: Proste makro

Napisz makro (VBA/Basic), ktore przechodzi przez kolumne z ocenami i automatycznie koloruje komorki: 1 - czerwony, 2 - pomaranczowy, 3 - zolty, 4 - jasno-zielony, 5 - zielony, 6 - ciemno-zielony. Dodaj przycisk uruchamiajacy makro.

Pokaz rozwiazanie

' Makro VBA / LibreOffice Basic
Sub KolorujOceny()
    Dim i As Integer
    Dim ocena As Integer

    For i = 2 To 21   ' wiersze z danymi
        ocena = Cells(i, 5).Value  ' kolumna E = oceny

        Select Case ocena
            Case 1
                Cells(i, 5).Interior.Color = RGB(255, 0, 0)      ' czerwony
            Case 2
                Cells(i, 5).Interior.Color = RGB(255, 165, 0)    ' pomaranczowy
            Case 3
                Cells(i, 5).Interior.Color = RGB(255, 255, 0)    ' zolty
            Case 4
                Cells(i, 5).Interior.Color = RGB(144, 238, 144)  ' jasno-zielony
            Case 5
                Cells(i, 5).Interior.Color = RGB(0, 200, 0)      ' zielony
            Case 6
                Cells(i, 5).Interior.Color = RGB(0, 128, 0)      ' ciemno-zielony
        End Select
    Next i

    MsgBox "Oceny zostaly pokolorowane!"
End Sub

' Aby dodac przycisk:
' 1. Wstaw > Kontrolka formularza > Przycisk
' 2. Przypisz makro "KolorujOceny"

🎥

Materialy wideo

▼

Excel - podstawy. Sortowanie i filtrowanie danych.

Mastering Excel

EXCEL - Funkcja JEŻELI

PMSOCHO Piotr Majcher

🎧

Podcasty

▼

✔️

Quiz - sprawdz sie!

▼

📜

Podstawa programowa

▼

Lekcja 7: Arkusz kalkulacyjny - zaawansowane funkcje i analiza danych

Teoria

Zaawansowane funkcje arkusza kalkulacyjnego

Funkcja JEZELI (IF)

Funkcja WYSZUKAJ.PIONOWO (VLOOKUP)

Funkcje statystyczne z warunkami

Formatowanie warunkowe

Wprowadzenie do makr

Zadania

Zadanie 1: Dziennik ocen z funkcja JEZELI

Zadanie 2: Cennik z WYSZUKAJ.PIONOWO

Zadanie 3: Analiza ankiety

Zadanie 4: Proste makro

Materialy wideo

Podcasty

Porozmawiajmy o IT - Jak zaczac programowac?

Just Join IT - Sciezki kariery programisty

Quiz - sprawdz sie!

Pytanie 1: Funkcja LICZ.JEZELI sluzy do:

Pytanie 2: WYSZUKAJ.PIONOWO (VLOOKUP) szuka wartosci:

Pytanie 3: Autofiltr pozwala na:

Podstawa programowa