0
0 Produkty w koszyku

No products in the cart.

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

utworzone przez | maj 4, 2016 | Excel | 0 komentarzy

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

Jak obliczyć unikalną ilość elementów pod warunkiem — Tabela Przestawna — Widzowie #91

utworzone przez | kwi 29, 2016 | Excel 2013 | 0 komentarzy

Potrzebujesz obliczyć unikalną ilość elementów z listy, ale pod warunkami uwzględniającymi inne kolumny danych? Przykładowo chcesz policzyć unikalne numery WZ, pod warunkiem Klienta oraz tygodnia:

Widzowie 91 - Jak obliczyć unikalną ilość elementów pod warunkiem - tabela przestawna Excel 2013 01

Od Excela 2013 możesz wykorzystać do tego Tabele Przestawne.

Wystarczy, że na podstawie danych stworzysz tabelę przestawną. Musisz pamiętać tylko, żeby zaznaczyć pole wyboru dostępne od Excela 2013 – Dodaj te dane do modelu danych.

Widzowie 91 - Jak obliczyć unikalną ilość elementów pod warunkiem - tabela przestawna Excel 2013 02

Dzięki temu będziesz miał dostępną dodatkową opcję podsumowywania danych. Teraz wystarczy, że przeciągniesz interesujące Cię pole (w tym przykładzie WZ) do obszaru wartości. Na razie będzie pokazywał domyślne podsumowanie numerów WZ, ale wystarczy, że klikniesz prawym przyciskiem myszy na to podsumowanie i z podręcznego menu rozwiniesz listę Podsumuj wartości według i z niej wybierzesz pozycję Więcej opcji.

Widzowie 91 - Jak obliczyć unikalną ilość elementów pod warunkiem - tabela przestawna Excel 2013 03

W oknie ustawień pola wartości, które się pokaże musisz wybrać ostatnią z możliwych opcji – Liczba wartości odrębnych. Będzie ona dostępne tylko wtedy, kiedy dodasz tabelę przestawną do modelu danych. Lepszą nazwą dla tego podsumowania byłoby Unikalne wartości, dlatego odpowiednio zmienimy nazwę podsumowania.

Widzowie 91 - Jak obliczyć unikalną ilość elementów pod warunkiem - tabela przestawna Excel 2013 04

Teraz Excel powinien Ci wyświetlić ilość unikalnych numerów WZ w całości danych zostało tylko pokazanie unikalnej ilości po warunkach.

Widzowie 91 - Jak obliczyć unikalną ilość elementów pod warunkiem - tabela przestawna Excel 2013 05

Do tego wystarczy, że przeciągniesz odpowiednie pola do obszaru etykiet wierszy np.: pole Klient.

Widzowie 91 - Jak obliczyć unikalną ilość elementów pod warunkiem - tabela przestawna Excel 2013 06

I teraz możesz zobaczyć ilość unikalnych numerów WZ dla poszczególnych klientów. Zwróć uwagę, że suma unikalnych WZ dla poszczególnych klientów nie jest równa sumie wszystkich unikalnych numerów WZ (jest większa). Wynika to z tego, że w przykładzie zdarzają się sytuacje, gdzie niektóre numery WZ występują przy różnych klientach. Stąd bierze się ta różnica.

Możesz w ten sposób obliczać unikalne elementy nawet przy większej ilości pól w obszarze etykiet wierszy (lub kolumn).

Widzowie 91 - Jak obliczyć unikalną ilość elementów pod warunkiem - tabela przestawna Excel 2013 07