Lekcja 9: Arkusz kalkulacyjny - roznorodne funkcje, filtrowanie danych

Funkcje warunkowe

Funkcje warunkowe wykonuja obliczenia tylko na komorkach spelniajacych okreslone kryteria. Sa niezwykle przydatne do analizy duzych zbiorow danych.

LICZ.JEZELI (COUNTIF)

=LICZ.JEZELI(zakres; kryterium) - zlicza komorki w zakresie, ktore spelniaja podane kryterium.

• =LICZ.JEZELI(A1:A100;"Tak") - ile komorek zawiera tekst "Tak"
• =LICZ.JEZELI(B1:B100;">50") - ile komorek ma wartosc wieksza niz 50
• =LICZ.JEZELI(C1:C100;"Warszawa") - ile razy wystepuje "Warszawa"

LICZ.WARUNKI (COUNTIFS)

=LICZ.WARUNKI(zakres1; kryterium1; zakres2; kryterium2; ...) - zlicza komorki spelniajace wiele kryteriow jednoczesnie.

Przyklad: =LICZ.WARUNKI(A:A;"Warszawa";B:B;">18") - ile osob z Warszawy ma wiecej niz 18 lat.

SUMA.JEZELOW (SUMIFS)

=SUMA.JEZELOW(zakres_sumy; zakres_kryterium1; kryterium1; ...) - sumuje wartosci spelniajace warunki.

Przyklad: =SUMA.JEZELOW(D:D;A:A;"Jedzenie") - suma wydatkow w kategorii "Jedzenie".

SREDNIA.JEZELOW (AVERAGEIFS)

=SREDNIA.JEZELOW(zakres_sredniej; zakres_kryterium1; kryterium1; ...) - srednia arytmetyczna wartosci spelniajacych warunki.

WYSZUKAJ.PIONOWO (VLOOKUP)

=WYSZUKAJ.PIONOWO(szukana_wartosc; tabela; nr_kolumny; przyblizone_dopasowanie) - wyszukuje wartosc w pierwszej kolumnie tabeli i zwraca wartosc z innej kolumny tego samego wiersza.

• szukana_wartosc - co szukamy (np. numer ucznia)
• tabela - zakres danych do przeszukania
• nr_kolumny - z ktorej kolumny tabeli zwrocic wynik (1, 2, 3...)
• przyblizone_dopasowanie - FALSZ (dokladne) lub PRAWDA (przyblizone)

Przyklad: =WYSZUKAJ.PIONOWO(E2;A:C;3;FALSZ) - szuka wartosci z E2 w kolumnie A i zwraca odpowiednia wartosc z kolumny C.

INDEKS i PODAJ.POZYCJE (INDEX + MATCH)

Bardziej elastyczna alternatywa dla WYSZUKAJ.PIONOWO:

• =INDEKS(zakres; nr_wiersza; nr_kolumny) - zwraca wartosc z okreslonej pozycji
• =PODAJ.POZYCJE(szukana; zakres; typ) - zwraca pozycje (numer wiersza) szukanej wartosci
• Polaczenie: =INDEKS(C:C;PODAJ.POZYCJE(E2;A:A;0))

Funkcje tekstowe

• =ZLACZ.TEKSTY(A1;" ";B1) lub =A1&" "&B1 - laczenie tekstow
• =LEWY(A1;3) - pierwsze 3 znaki tekstu
• =PRAWY(A1;4) - ostatnie 4 znaki tekstu
• =DL(A1) - dlugosc tekstu (liczba znakow)
• =LITERY.WIELKIE(A1) - zamiana na wielkie litery
• =USUN.ZBEDNE.ODSTEPY(A1) - usuwanie nadmiarowych spacji

Filtrowanie danych

Autofiltr pozwala szybko wyswietlic tylko te wiersze, ktore spelniaja okreslone kryteria:

1. Zaznacz dowolna komorke w tabeli danych
2. Dane → Filtr (lub Ctrl+Shift+L)
3. Kliknij strzalke w naglowku kolumny
4. Wybierz kryteria: wartosci z listy, filtry numeryczne (>, <, miedzy), filtry tekstowe (zawiera, zaczyna sie od)

Filtr zaawansowany

Umozliwia bardziej zlożone kryteria filtrowania, w tym kryteria OR (lub) - autofiltr domyslnie laczy kryteria AND (i).

Sortowanie wielopoziomowe

Dane → Sortuj → Dodaj poziom. Przyklad: najpierw sortuj wedlug miasta (A-Z), potem wedlug nazwiska (A-Z) w obrebie kazdego miasta.