Samouczek I
Zliczanie danych spełniających wiele warunków, czyli analiza informacji
Prowadzenie szkolnego sklepiku to nie zawsze prosta sprawa. Z pomocą przychodzą arkusze kalkulacyjne, które pomogą podliczyć obroty w danym miesiącu, a nawet podpowiedzą, ile sztuk danego towaru zostało sprzedanych w zeszłym miesiącu.
Arkusz kalkulacyjny pomoże prowadzić ewidencję sprzedanych towarów. W poszczególnych kolumnach wprowadzamy informacje o: liczbie porządkowej towaru, jego nazwie, kupującym, dacie sprzedaży i cenie.
Plik z przykładowymi danymi do pobrania poniżej.
Załóżmy, że chcemy uzyskać z zestawienia następujące informacje:
ile osób kupiło towar X;
ile towarów było droższych od średniej ceny towarów;
ile towarów zostało zakupionych po 22 maja 2014 r.
Do rozwiązania tego problemu wykorzystamy funkcję LICZ.JEŻELI
. Funkcja LICZ.JEŻELI(zakres;kryterium)
przyjmuje jako argumenty zakres komórek, w których mają być prowadzone poszukiwania oraz kryteria (liczby, daty, wyrażenia), którym zliczane komórki mają odpowiadać.
Przydatna będzie także funkcja LICZ.WARUNKI(zakres1;kryterium1;zakres2;kryterium2;…)
.
Jest to niejako LICZ.JEŻELI
, ale z możliwością określenia różnych kryteriów dla różnych zakresów (np. kolumn). Użyjemy także funkcji ŚREDNIA
, która oblicza średnią wartość z zadanego zakresu komórek.
Ile osób kupiło towar X?
Na początek nazwiemy zakresy danych, tak aby nie musieć pisać np. C3:C12
, a tylko Kupujący
. W tym celu zaznaczamy zakres komórek A2:E2
i wciskamy [Ctrl]+[Shift]+[Strzałka w dół]
.
Dzięki temu zostanie zaznaczona cała zawartość tabeli – jest to przydatne w przypadku wielkich spisów liczących kilkaset, czy kilka tysięcy wierszy.
Teraz na karcie Formuły
w grupie Nazwy zdefiniowane
znajdujemy przycisk Utwórz z zaznaczenia
i go klikamy. Zaznaczamy tylko pozycję Górny wiersz
.
Klikając przycisk Menedżer nazw
(na lewo od Utwórz z zaznaczenia
) przekonamy się, że nazwaliśmy zakresy poszczególnych kolumn dokładnie tak, jak brzmią ich nagłówki.
Aby policzyć ile osób kupiło towar X, musimy w zakresie komórek Kupujący
(czyli C3:C12
) policzyć te, które mają wartość taką, jak np. komórka C3
(czyli towar X
). Gotowa formuła wygląda następująco: =LICZ.JEŻELI(Kupujący;C3)
.
Ile towarów było droższych od średniej ceny towarów?
Na początku musimy policzyć średnią cenę towarów. Zrobimy to za pomocą funkcji ŚREDNIA(zakres)
, czyli w naszym przypadku: =ŚREDNIA(Cena)
. Teraz za pomocą formuły LICZ.JEŻELI
policzymy w zakresie komórek Cena
(czyli te same komórki E3:E12
) tylko te komórki, których wartość przekracza średnią cenę. Posłuży do tego formuła =LICZ.JEŻELI(Cena;">"&E14)
. Aby odwołać się do innej komórki w kryterium, należy poprzedzić ją znakiem &
.
Ile towarów zostało zakupionych po 22 maja 2014 r.?
LICZ.JEŻELI
może także przyjąć jako kryterium datę i znajdować daty późniejsze lub wcześniej za pomocą znaków <
oraz >
.
Data późniejsza jest uznawana za większą od daty wcześniejszej, a data wcześniejsza za mniejszą od daty późniejszej. Dzięki temu formuła =LICZ.JEŻELI("Data sprzedaży";">2014‑05‑22")
policzy tylko te komórki, w których data sprzedaży to 23 maja 2014 r. lub później.
Pobierz przykładowe dane:
Należysz do szkolnego Koła Ekologicznego. Przeprowadzacie akcję, która polega na sadzeniu nowych drzew. Podzieliliście się na trzy zespoły. Twoim zadaniem jest prowadzenie raportu z całego projektu. Utwórz arkusz, który będzie zawierał numer dnia, numer zespołu oraz liczbę drzew, jaką udało się posadzić danego dnia. Oblicz, ile razy w ciągu tego okresu każdy z zespołów sadził drzewa. Który z nich posadził więcej sadzonek?
Zmodyfikuj arkusz z poprzedniego zadania. Oblicz, ile razy udało się posadzić więcej drzew niż wynosiła średnia.