Zapraszamy do zapoznania się z ciekawym zastosowaniem funkcji licz warunki przy konstruowaniu tabeli przestawnych. Jej działanie sprawdzimy na przykładzie liczby klientów kupujących towary w danej sieci hurtowni. Funkcja licz warunki wskaże - ilu unikalnych kupujących posiada każda placówka. Sprawdź jak działa i zastanów się, jak możesz ją zastosować u siebie w firmie!
Z lekcji dowiesz się:
Jak policzyć np. ilu unikalnych klientów, kupuje produkty w każdej z hurtowni?
Ile występuje kombinacji w zbiorze spełniających dany warunek?
Przy jakich zadaniach używać funkcji „licz warunki”?
Z lekcji dowiesz się:
Jak policzyć np. ilu unikalnych klientów, kupuje produkty w każdej z hurtowni?
Ile występuje kombinacji w zbiorze spełniających dany warunek?
Przy jakich zadaniach używać funkcji „licz warunki”?
Mamy zbiór danych dotyczący sprzedaży w firmie posiadającej sieć hurtowni. W kolumnie A znajdują się hurtownie dostarczające produkty (kolumna C) do poszczególnych kupujących (kolumna B) w określonej cenie (kolumna D). Naszym zadaniem jest policzyć ilu unikalnych kupujących posiada każda z hurtowni.
Zobrazujmy ta sytuację na przykładzie (rys. 1).
(Rys. 1)
Kolumna unikalna wartość zwraca 1 kiedy kombinacja Dostawcy i Kupującego nie wystąpiła wcześniej. Po prześledzeniu tabeli widzimy, że faktycznie wszystkie cyfry 1 zostały przypisane poprawnie. Dlaczego zatem dla kombinacji Grossel – Diplo pojawiło się 0? Wystarczy, że spojrzymy 4 komórki wyżej i widzimy, że ten model dostawcy i kupującego już wystąpił. Dlatego funkcja poprawnie zwróciła wartość 0, ponieważ takiej kombinacji nie możemy uznać za unikalną i nie chcemy jej zliczać.
Kluczowa kwestia to jak napisać formułę, aby potrafiła w inteligentny sposób czytać tablice danych i zwracać pożądane wartości. Ta formuła będzie odpowiednio zmodyfikowaną funkcją „licz warunki”.
Jak jest zbudowana funkcja licz warunki?
LICZ.WARUNKI(zakres_kryterium1;kryterium1;[zakres_kryterium2;kryterium2]…)
Zakres_kryterium1 – zakres danych w których sprawdzamy występowanie zadanego kryterium.
Kryterium1 – wartość logiczna, która służy do liczenia ile wartości spełnia założone kryterium (warunek).
Każdy następny zakres i kryterium tworzymy na takiej samej zasadzie.
Znając już składnię funkcji licz warunki przechodzimy do skonstruowania formuły:
Zakres_kryterium1 – zaznaczamy obszar jednej komórki, blokując tylko wartość początkową, aby pomimo przeciągania funkcji w dół sukcesywnie zwiększać, a nie zmieniać jej zasięgu.
Kryterium1 – nazwa dostawcy, czyli komórka A2. Nie blokujemy jej ponieważ chcemy aby funkcja sukcesywnie przeszukiwała każdą komórkę w zakresie.
Zakres_kryterium1 – analogicznie jak w przypadku 1 zakresu, tylko teraz zaznaczamy komórkę B2, czyli tabelę z kupującymi.
Kryterium2 – tak samo jak w kryterium1, z tą różnicą że wybieramy nazwę kupującego.
Zatrzymajmy się w tym miejscu i przeanalizujmy sposób działania formuły.
Funkcja, którą napisaliśmy będzie liczyć ile jest kombinacji w zbiorze danych spełniających warunek. Jeżeli wartość jest unikalna, to funkcja zwróci wartość 1, co oznacza że wcześniej taka kombinacja nie występowała. To właśnie dlatego w dość nietypowy sposób stworzyliśmy zakres całego kryterium, gdybyśmy zaznaczali od razu pełny zakres, to funkcja zliczałaby ile danych kombinacji występuje w zbiorze danych, a to zupełnie nie jest celem zadania. Funkcja dynamicznie poszerza zakres tabeli i sprawdza warunki. Jeżeli zwróci wartość większą od 1, oznacza, że wcześniej w zbiorze danych taka kombinacja już występowała, i nie można jej uznać za unikalną (rys. 2).
(Rys. 2)
Przejdźmy do następnego fragmentu formuły, widzimy, że jej wynik jest przyrównywany do 0, a następnie mnożony przez 1. Tak jak wspomniano wcześniej, chcemy uzyskać wyniki 0 lub 1, dlatego najpierw uzyskujemy wartość logiczną (rys. 3):
1=1 – Prawda
Cokolwiek innego niż 1 = Fałsz
(Rys. 3)
Tę wartość logiczną zapisaną tekstowo zamieniamy na wymiar liczbowy przemnażając ją przez 1. To oczywiście praktyka stosowana nie tylko w przypadku tej formuły, ale ogólnie w arkuszu kalkulacyjnym (rys. 4).
(Rys. 4)
Tak przygotowaną formułę, przeciągamy w dół i tworzymy tabelę przestawną, która w przejrzysty sposób uszereguje dane (rys. 5).
(Rys. 5)
Zaprezentowana formuła jest niezwykle ciekawa, a dlaczego? Otóż, w omawianym przykładzie baza danych jest stosunkowo niewielka, ale kiedy mamy kilkaset tysięcy rekordów, to efekt końcowy robi ogromne wrażenie. Najważniejsze więc, że bardzo dobrze sprawdza się w praktyce!
Polecamy także:
© Sprytny Excel