Jak zliczyć unikalną ilość elementów pod warunkiem — kolumna pomocnicza — widzowie #92

Jeśli potrzebujesz zliczyć unikalną ilość elementów z listy, ale pod warunkami uwzględniającymi inne kolumny danych, to możesz skorzystać z formuły, która wykorzystuje kolumnę pomocniczą. Jeśli np.  chcesz zliczyć unikalne numery WZ, pod warunkiem Klienta oraz wartości zamówienia:

Widzowie 92 - Jak obliczyć unikalną ilość elementów pod warunkiem - kolumna pomocnicza 01

To na początku musisz przygotować kryteria. Najlepiej, żebyś je miał wpisane w komórki. Dla tego przykładu wykorzystamy kryteria związane z klientem i wartością zamówienia. Od Ciebie zależy, czy kryteria będziesz wpisywać ręcznie, czy stworzysz sobie odpowiednie listy rozwijane lub temu podobne.

Widzowie 92 - Jak obliczyć unikalną ilość elementów pod warunkiem - kolumna pomocnicza 02

Teraz w kolumnie pomocniczej musisz napisać odpowiednią formułę. Na większości źródeł zobaczy funkcję LICZ.WARUNKI, która korzysta z rozrastających się zakresów. Żeby zbudować taki zakres musisz jedną część odwołania do zakresu zablokować, a drugą pozostawić względną ($A$2:A2). Ponieważ potrzebujemy sprawdzać kryterium unikalności, to w kolejnych wierszach patrzymy na kolejne numery WZ. Przy okazji patrzymy też na ustalone warunki, czyli przykładowo, że sprzedawca to Gloria i sprzedaż jest poniżej 5000zł. Wszystko to sprowadza się do formuły:

=LICZ.WARUNKI($A$2:A6;A6;$B$2:B6;$F$5;$C$2:C6;"<"&$G$5)

Excel - Jak obliczyć unikalną ilość elementów pod warunkiem - kolumna pomocnicza 03

W kolumnie pomocniczej interesują nas wartości 1 (=LICZ.JEŻELI($D$2:$D$31;1) – komórka G1). Ponieważ powinny one oznaczać pierwsze pokazanie się unikalnej wartości pod warunkiem. Niestety nie zawsze jest to prawdą, bo funkcja LICZ.WARUNKI zlicza wszystkie wartości do danego miejsca i może się okazać, że wartość 1 pokazuje się na bazie jednego z wcześniejszych wierszy i jest niepoprawnie zliczana do pierwszego wystąpienia unikalnej wartości.

Excel - Jak obliczyć unikalną ilość elementów pod warunkiem - kolumna pomocnicza 04

Dlatego do naszej formuły potrzebujemy jeszcze dopisać warunki sprawdzające, czy w danym wierszu pojawiają się wartości spełniające nasze warunki (Gloria i i wartości mniejsze od 5000zł). Chyba najprościej zrobić poza funkcją LICZ.WARUNKI, jako testy logiczne w nawiasach przemnożone przez wynik funkcji LICZ.WARUNKI:

=LICZ.WARUNKI($A$2:A2;A2;$B$2:B2;$F$5;$C$2:C2;"<"&$G$5)*(B2=$F$5)*(C2<$G$5)

Excel - Jak obliczyć unikalną ilość elementów pod warunkiem - kolumna pomocnicza 05

Teraz już unikalne wartości powinny się zliczać prawidłowo. Możemy to przetestować nakładając odpowiednie filtry na nasze przykładowe dane. Ilość widocznych unikalnych numerów WZ zgadza się z wynikiem funkcji (=LICZ.JEŻELI($D$2:$D$31;1)) w komórce G1.

Excel - Jak obliczyć unikalną ilość elementów pod warunkiem - kolumna pomocnicza 06

Pozdrawiam
Adam Kopeć
Miłośnik Excela