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

Formuły
Automatyzacja pracy
Średniozaawansowany
0:00czas trwania

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!

Opublikowane: 15 stycznia 2020
Lekcję prowadzi: Kamil Skonecki
Wykonaj zadanie samodzielnie
Dostępne po opłaceniu
Pobierz szablon do dalszej pracy
Dostępne po opłaceniu

Zobacz krok po kroku jak wykonać lekcję
Wykonaj zadanie samodzielnie
Dostępne po opłaceniu
Pobierz szablon do dalszej pracy
Dostępne po opłaceniu

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!

Wykonaj zadanie samodzielnie
Dostępne po opłaceniu
Pobierz gotowy arkusz z lekcji
Dostępne po opłaceniu
Kolejne kroki dostępne dla użytkowników którzy wykupili pełny dostęp

Jeszcze się wahasz?

Zobacz, co zyskasz, mając pełny dostęp:

  • Dostęp do wszystkich lekcji video
  • Materiały do ćwiczeń
  • Dodatkowe artykuły z trikami
  • Formularze gotowe od razu do użycia
  • E-booki pogłębiające Twoją wiedzę
  • Certyfikat potwierdzający Twoje umiejętności
Sprawdź co oferuje serwis Sprytny Excel »

Masz już konto w serwisie?

Zaloguj się

Adres e-mail:

Hasło

Nie pamiętam hasła
- Twój pierwszy krok do lepszych wyników
Oglądaj pełne lekcje i wiedz więcej!
Masz już konto w serwisie?
Zaloguj się »

Certyfikat rzetelności Laur zaufania SMB logo Top firma