Ilu unikalnych kupujących posiada każda placówka?

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:

• Tworzenie rankingów i zestawień za pomocą funkcji Pozycja!

• Zabezpieczenia w arkuszu kalkulacyjnym!