Zliczanie warunków, czyli Excel analizuje dla nas dane

Autor: Marcin Sarna
Data: 07-10-2014 r.

Prosta tabela, w której podliczasz obroty firmy w danym miesiącu, nie zawsze wystarczy. Co zrobić, jeżeli potrzebujesz wiedzieć ile sztuk danego towaru zostało sprzedanych w zeszłym miesiącu?

Załóżmy, że prowadzimy w Excelu ewidencję sprzedanych towarów. W poszczególnych kolumnach mamy informacje o: liczbie porządkowej towaru, jego nazwie, kupującym, dacie sprzedaży i cenie.

 

Rysunek 1. Przykładowy skoroszyt

Załóżmy, że chcemy wydobyć z zestawienia następujące informacje:

  • ile towarów kupiło AERO sp. z o.o.;

  • ile towarów było droższych od średniej ceny towarów;

  • ile towarów zakupili kupujący, których nazwy zaczynają się od „A”;

  • ile towarów zostało zakupionych po 22 maja 2014 roku.

Służy do tego przede wszystkim funkcja LICZ.JEŻELI.

Funkcja LICZ.JEŻELI (zakres; kryterium) przyjmuje 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 LICZ.WARUNKI (zakres1; kryterium1; zakres2; kryterium2;…), która zlicza wiersze, dla których zakres1 spełnia kryteria zakres2 itd. – czyli 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 towarów kupiło AERO sp. z o.o.?

Na początek nazwiemy sobie zakresy danych, tak aby nie musieć pisać np. C3:C12, a tylko Kupujący. W tym celu zaznaczmy zakres komórek A2:E2 i wciśnijmy [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. Zaznaczymy tylko pozycję Górny wiersz. Teraz, klikając na 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.

Rysunek 2. Nazwane kolumny w naszym skoroszycie

Aby więc policzyć, ile towarów kupiło AERO sp. z o.o., musimy w zakresie komórek Kupujący (czyli C3:C12) policzyć te, które mają wartość taką jak np. komórka C3 (czyli AERO sp. z o.o.). Gotowa formuła brzmi następująco: =LICZ.JEŻELI(Kupujący;C3).

Rysunek 3. Formuła i jej wynik: AERO sp. z o.o. kupiła 3 towary

Ile towarów było droższych od średniej ceny towarów?

Najpierw 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 nam do tego formuła =LICZ.JEŻELI(Cena;">"&E14). Aby odwołać się do innej komórki w kryterium, należy poprzedzić ją znakiem &.

Rysunek 4. Średnia cena towarów oraz ilość towarów powyżej tej ceny (są trzy takie towary: jeden po cenie 2500 oraz dwa po cenie 1700).

Ile kupiono towarów, których nazwy zaczynają się od „A”?

Aby odpowiedzieć na to pytanie posłużymy się tzw. symbolem wieloznacznym (*). Taka gwiazdka reprezentuje ciąg dowolnych znaków o dowolnej długości. Czyli jeżeli odpytamy Excela formułą =LICZ.JEŻELI(Kupujący;”A*”), to w odpowiedzi otrzymamy 5 – bo tylu kupujących zaczyna się na literę A. Wielkość litery nie ma znaczenia.

Ile towarów zostało zakupionych po 22 maja 2014 roku?

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 lub później.

Marcin Sarna

Tagi: excel

Zaloguj się, aby dodać komentarz

Nie masz konta? Zarejestruj się »

Zobacz także

Jak zatrzeć ślady po przeglądaniu Internetu?

pobierz

Wzór Polityki Bezpieczeństwa w ochronie danych osobowych

pobierz

Konfiguracja bezpieczeństwa. Windows 10

pobierz

10 sprytnych trików na szybkie obliczenia w Excelu

pobierz

Polecane artykuły

Array ( [docId] => 35833 )
Array ( [docId] => 35833 )