Technikum Klasa I 45 minut PP: II.3c | s. 344

Lekcja 39: Arkusz kalkulacyjny - roznorodne funkcje wg rodzaju danych

SUMA, SREDNIA, LICZ.JEZELI, JEZELI, WYSZUKAJ.PIONOWO i inne

📋 Podstawa programowa: II.3c
JEZELISUMAarkusz kalkulacyjnyfunkcje arkusza
00:00
Wprowadzenie
5 min
00:05
Teoria
15 min
00:20
Cwiczenia
15 min
00:35
Podsumowanie
10 min
📚

Teoria

▼

Kategorie funkcji w arkuszu kalkulacyjnym

Arkusz kalkulacyjny oferuje setki wbudowanych funkcji podzielonych na kategorie. Poznamy najwazniejsze z kazdej grupy.

1. Funkcje matematyczne i statystyczne

  • =SUMA(zakres) - sumuje wartosci, np. =SUMA(A1:A20)
  • =SREDNIA(zakres) - srednia arytmetyczna
  • =MEDIANA(zakres) - wartosc srodkowa (mediana)
  • =MIN(zakres) / =MAX(zakres) - minimum / maksimum
  • =ILE.LICZB(zakres) - liczba komorek zawierajacych liczby
  • =ZAOKR(liczba;n) - zaokraglenie do n miejsc dziesietnych
  • =POTEGA(baza;wykladnik) - potegowanie
  • =PIERWIASTEK(liczba) - pierwiastek kwadratowy
  • =MODUL(liczba) - wartosc bezwzgledna
  • =ZAOKR.DO.CALKOWITEJ(liczba) - zaokraglenie w dol do calkowitej

2. Funkcje logiczne

  • =JEZELI(warunek;wartosc_prawda;wartosc_falsz) - instrukcja warunkowa
    Np. =JEZELI(A1>=3;"Zaliczyl";"Nie zaliczyl")
  • =ORAZ(warunek1;warunek2;...) - PRAWDA gdy WSZYSTKIE warunki spelnione
    Np. =JEZELI(ORAZ(A1>=3;A1<=5);"OK";"Blad")
  • =LUB(warunek1;warunek2;...) - PRAWDA gdy PRZYNAJMNIEJ JEDEN warunek spelniony
  • =NIE(warunek) - negacja logiczna
  • =JEZELI.BLAD(formula;wartosc_bledu) - obsluga bledow
    Np. =JEZELI.BLAD(A1/B1;"Dzielenie przez 0")
Zagniezdzanie JEZELI: Mozna wstawic JEZELI w JEZELI, np.:
=JEZELI(A1>=5;"Bardzo dobry";JEZELI(A1>=4;"Dobry";JEZELI(A1>=3;"Dostateczny";"Niedostateczny")))
Nowsze wersje Excela oferuja tez =IFS() - prostsza skladnia dla wielu warunkow.

3. Funkcje zliczajace z warunkiem

  • =LICZ.JEZELI(zakres;kryterium) - zlicza komorki spelniajace warunek
    Np. =LICZ.JEZELI(B2:B20;">3") - ile ocen powyzej 3
  • =LICZ.WARUNKI(zakres1;kryt1;zakres2;kryt2;...) - zlicza z wieloma warunkami
  • =SUMA.JEZELOW(zakres_sumy;zakres_kryt;kryterium) - sumuje z warunkiem
    Np. =SUMA.JEZELOW(C2:C20;A2:A20;"Krakow") - suma sprzedazy z Krakowa
  • =SUMA.WARUNKOW(zakres_sumy;zakres_kryt1;kryt1;...) - sumuje z wieloma warunkami
  • =SREDNIA.JEZELOW(zakres_sredniej;zakres_kryt;kryterium) - srednia z warunkiem

4. Funkcje tekstowe

  • =DL(tekst) - dlugosc tekstu (ilosc znakow)
  • =LEWY(tekst;n) - n pierwszych znakow
  • =PRAWY(tekst;n) - n ostatnich znakow
  • =FRAGMENT.TEKSTU(tekst;start;n) - n znakow od pozycji start
  • =ZLACZ.TEKSTY(tekst1;tekst2;...) - laczenie tekstow (lub operator &)
  • =WIELKIE(tekst) - zamiana na wielkie litery
  • =MALE(tekst) - zamiana na male litery
  • =Z.WIELKIEJ.LITERY(tekst) - pierwsza litera kazdego slowa wielka
  • =ZNAJDZ(szukany;tekst) - pozycja szukanego tekstu w tekscie
  • =PODSTAW(tekst;stary;nowy) - zamiana fragmentu tekstu

5. Funkcje wyszukiwania

  • =WYSZUKAJ.PIONOWO(wartosc;tabela;nr_kolumny;dokladne) - szuka wartosci w pierwszej kolumnie tabeli i zwraca wartosc z wybranej kolumny tego wiersza
    Np. =WYSZUKAJ.PIONOWO("Kowalski";A2:D20;3;0) - znajdz Kowalskiego i zwroc wartosc z 3. kolumny
  • =WYSZUKAJ.POZIOMO(wartosc;tabela;nr_wiersza;dokladne) - analogicznie, ale szuka w pierwszym wierszu
  • =INDEKS(tabela;wiersz;kolumna) - zwraca wartosc z podanej pozycji
  • =PODAJ.POZYCJE(wartosc;zakres;typ) - zwraca pozycje wartosci w zakresie

6. Funkcje daty i czasu

  • =DZIS() - dzisiejsza data
  • =TERAZ() - biezaca data i godzina
  • =ROK(data) / =MIESIAC(data) / =DZIEN(data) - wyodrebnianie
  • =DNI(data_konc;data_pocz) - roznica w dniach
  • =DZIEN.TYG(data) - dzien tygodnia (1=niedziela, 2=poniedzialek...)
Uwaga na separator! W polskiej wersji Excela argumenty funkcji oddziela sie srednikiem (;) a nie przecinkiem. W Google Sheets i angielskiej wersji Excela uzywamy przecinka (,).
✏️

Zadania

▼
Latwe

Zadanie 1: Analiza wynikow sportowych

Utworz tabele z wynikami 15 uczniow z biegu na 100m (czas w sekundach). Oblicz: srednia, mediane, najszybszy i najwolniejszy czas, ilosc uczniow ponizej 15 sekund (LICZ.JEZELI), sredni czas dla dziewczat i chlopcow osobno (SREDNIA.JEZELOW).

Pokaz rozwiazanie 
     A           B      C
1   Imie        Czas   Plec
2   Jan         14.2   M
3   Anna        15.8   K
4   Piotr       13.5   M
... (15 wierszy)

Obliczenia:
E1: Srednia:    F1: =SREDNIA(B2:B16)
E2: Mediana:    F2: =MEDIANA(B2:B16)
E3: Najszybszy: F3: =MIN(B2:B16)
E4: Najwolniej: F4: =MAX(B2:B16)
E5: Ponizej 15s: F5: =LICZ.JEZELI(B2:B16;"<15")
E6: Sr. chlopcy: F6: =SREDNIA.JEZELOW(B2:B16;C2:C16;"M")
E7: Sr. dziewcz: F7: =SREDNIA.JEZELOW(B2:B16;C2:C16;"K")
Srednie

Zadanie 2: System oceniania z JEZELI

Utworz tabele z punktami 20 uczniow z testu (0-100 pkt). Uzyj zagniezdzonych JEZELI do przypisania ocen: 0-39=ndst, 40-54=dop, 55-69=dst, 70-84=db, 85-94=bdb, 95-100=cel. Policz ile jest kazdej oceny (LICZ.JEZELI). Dodaj kolumne "Zaliczyl?" (JEZELI, co najmniej dop).

Pokaz rozwiazanie 
     A          B       C                                    D
1   Uczen      Punkty  Ocena                                Zaliczyl?
2   Kowalski   78      =JEZELI(B2>=95;"cel";JEZELI(B2>=85;  =JEZELI(B2>=40;
                        "bdb";JEZELI(B2>=70;"db";JEZELI(     "Tak";"Nie")
                        B2>=55;"dst";JEZELI(B2>=40;"dop";
                        "ndst")))))
(skopiuj formule w dol)

Podsumowanie:
F1: Ocena     G1: Ilosc
F2: cel       G2: =LICZ.JEZELI(C2:C21;"cel")
F3: bdb       G3: =LICZ.JEZELI(C2:C21;"bdb")
F4: db        G4: =LICZ.JEZELI(C2:C21;"db")
F5: dst       G5: =LICZ.JEZELI(C2:C21;"dst")
F6: dop       G6: =LICZ.JEZELI(C2:C21;"dop")
F7: ndst      G7: =LICZ.JEZELI(C2:C21;"ndst")
F8: Razem     G8: =SUMA(G2:G7)  // powinno byc 20
Srednie

Zadanie 3: Faktura z WYSZUKAJ.PIONOWO

Utworz dwa arkusze: (1) "Cennik" - tabela z produktami (Kod, Nazwa, Cena_netto, VAT%). (2) "Faktura" - uzytkownik wpisuje kod produktu i ilosc, a formula automatycznie pobiera nazwe i cene z cennika (WYSZUKAJ.PIONOWO), oblicza wartosc netto, VAT i brutto.

Pokaz rozwiazanie 
ARKUSZ "Cennik":
     A      B              C        D
1   Kod    Nazwa          Netto    VAT%
2   P001   Zeszyt A5      3.50     23%
3   P002   Dlugopis       2.00     23%
4   P003   Plecak         89.00    23%
5   P004   Linijka 30cm   4.50     23%

ARKUSZ "Faktura":
     A      B       C           D          E         F         G
1   Kod    Ilosc   Nazwa       Cena       Netto     VAT       Brutto
2   P001   10      =WYSZUKAJ.  =WYSZUKAJ. =B2*D2   =E2*      =E2+F2
                   PIONOWO(    PIONOWO(             WYSZUKAJ.
                   A2;Cennik!  A2;Cennik!           PIONOWO(
                   A:B;2;0)    A:C;3;0)             A2;Cennik!
                                                    A:D;4;0)

Uproszczony zapis formul:
C2: =WYSZUKAJ.PIONOWO(A2;Cennik!$A:$B;2;0)
D2: =WYSZUKAJ.PIONOWO(A2;Cennik!$A:$C;3;0)
E2: =B2*D2
F2: =E2*WYSZUKAJ.PIONOWO(A2;Cennik!$A:$D;4;0)
G2: =E2+F2

Skopiuj formuly w dol dla kolejnych pozycji.
Trudne

Zadanie 4: Analiza tekstu z funkcjami tekstowymi

Masz kolumne z pelnym imieniem i nazwiskiem (np. "Jan Kowalski"). Uzyj funkcji tekstowych, aby: (a) wyodrebnic imie do osobnej kolumny, (b) wyodrebnic nazwisko, (c) utworzyc adres email w formacie j.kowalski@szkola.pl (mala litera imienia + kropka + nazwisko malymi), (d) utworzyc inicjaly (np. "J.K.").

Pokaz rozwiazanie 
A1: Jan Kowalski
A2: Anna Maria Nowak
A3: Piotr Wisniewski

Imie (B):
B1: =LEWY(A1;ZNAJDZ(" ";A1)-1)
    Wyjasnienie: ZNAJDZ szuka spacji, LEWY bierze
    znaki do spacji

Nazwisko (C):
C1: =PRAWY(A1;DL(A1)-ZNAJDZ(" ";A1))
    Wyjasnienie: DL - dlugosc tekstu, odejmuje pozycje
    spacji, PRAWY bierze reszte

Email (D):
D1: =MALE(LEWY(B1;1))&"."&MALE(C1)&"@szkola.pl"
    Wynik: j.kowalski@szkola.pl

Inicjaly (E):
E1: =WIELKIE(LEWY(B1;1))&"."&WIELKIE(LEWY(C1;1))&"."
    Wynik: J.K.

Uwaga: Dla imion zlozonych (Anna Maria)
potrzeba bardziej zaawansowanych formul
lub funkcji PODSTAW/ZNAJDZ.
🎥

Materialy wideo

▼
Excel - funkcja JEZELI (IF) i zagniezdzanie
Excel z Pasja
WYSZUKAJ.PIONOWO (VLOOKUP) - kompletny poradnik
Kurs Excel PL
🎧

Podcasty

▼
✔️

Quiz - sprawdz sie!

▼
📜

Podstawa programowa

▼
← Lekcja 38: Wprowadzenie do arkusza Lekcja 40: Filtrowanie danych →