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.
Film dostępny pod adresem /preview/resource/RBOn95xQjnpdt
Nagranie filmowe dotyczące zliczania warunków - czyli Excel analizuje dla nas dane
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.
