Czyli magiczne właściwości filtru zaawansowanego

Na liczne prośby uczestników webinaru, którzy nie mają dostępu (i nie chcą mieć!) do Fecebooka, zamieszczam jedno z rozwiązań zadania samodzielnego, które zadałam podczas webinaru Filtr zaawansowany – zapomniane narzędzie Excela.

Chodziło o to, aby z zestawienia transakcji wybrać takie, które dotyczą batonów i wafli, ale tylko takich, których wartość transakcji była mniejsza niż średnia w ich kategorii. Czyli batony z wartością mniejszą niż średnia dla batonów, wafle – ze średnią mniejsza niż średnia wafli:

Autofiltr w prosty sposób sobie z tym nie poradzi, czas zatem na filtr zaawansowany!

Budowanie tabeli kryteriów dla filtru zaawansowanego

W tabeli kryteriów musi znaleźć się informacja o kategorii produktów, czyli o batonach i waflach, oraz o tym, jakie wartości nas interesują.

A interesują nas mniejsze niż średnia wartość produktów z określonej kategorii. Tej średniej nie znamy, zatem będziemy ją sobie liczyli dla każdej kategorii. Zrobię to funkcją SUMA.JEŻELI (można też oczywiście użyć funkcji ŚREDNIA.WARUNKÓW).

Ale po kolei.

Załóżmy, że tabelę kryteriów umieszczę w arkuszu nad danymi. Potrzebuję 2 nagłówków: Kategoria, Wartość.

Kategorie interesują mnie dwie: Batony, Wafle. Aby zachować kryterium LUB między nimi – wpiszę je pod sobą:

Tabela kryteriów – kategorie

Teraz trudniejsza sprawa: wartości.

Dla naszych danych, dla batonów – średnia wartość transakcji wynosi ok. 399,29, a dla wafli – ok. 462,57. Interesują nas zatem odpowiednie transakcje o wartościach niższych.

Ręcznie, moglibyśmy zapisać te kryteria tak:

Tabela kryteriów – zapis ręczny

Jednak uzyskamy średnie wartości za pomocą funkcji ŚREDNIA.JEŻELI. Dla batonów, formuła wygląda ona tak:

="<"&ŚREDNIA.JEŻELI($G$7:$G$182;G3;$I$7:$I$182)

Dla wafli – analogicznie. Wystarczy skopiować powyższą formułę w tabeli kryteriów:

Tabela kryteriów w podglądzie widoku formuł

Efekt jest taki:

Tabela kryteriów

Stosowanie filtru zaawansowanego

Jak już mamy tabelę kryteriów – wystarczy tylko zastosować sam filtr.

1. Ustaw się w danych
2. Dane/Zaawansowane
3. W okienku filtru wskaż odpowiednie zakresy (filtruję listę na miejscu):

Stosowanie filtru

Zatwierdź OK i efekt mamy taki (po posortowaniu):

Wynik po posortowaniu

Tadam! Wszystko!

W artykule przedstawiłam jedną metodę na rozwiązanie tego zadania – moim zdaniem najbardziej czytelną.

Korzystaj śmiało!

Powiązane produkty

• Filtr zaawansowany – zapomniane narzędzie Excela – pełna lekcja o filtrze zaawansowanym, podczas której pokazuję jak go stosować, jakie ma przewagi nad autofiltrem, jak budować zaawansowane kryteria (określane formułą!) i, dla dociekliwych, jak cudne rzeczy można uzyskać przy jego pomocy w makrach (bez konieczności pisania kodu!)
• Kurs Excel w codziennej pracy – mający na celu uporządkować Twoją wiedzę na temat Excela. Jest świetny zarówno dla osób, które chcą uporządkować swoją wiedzę, zdobywaną do tej pory „po omacku”, jak i takich, które dopiero zaczynają swoją przygodę z Excelem.
• Funkcje baz danych – prezentuje rzadko używane (a szkoda!) funkcje z grupy BD, czyli baz danych. Dzięki tym funkcjom będziesz w stanie odpowiedzieć na skomplikowane pytania, zawierające więcej niż jedno kryterium, połączone słowem LUB, a nie tylko ORAZ, jak to umieją funkcje SUMA.WARUNKÓW czy LICZ.WARUNKI.

Plik do pobrania:

MalinowyExcel rozw z Webinaru Filtr zaawansowany DW.xlsx

I film na Youtubie, gdzie pokazuję powyższe rozwiązanie: