fbpx
Malinowy Excel
  • O mnie
  • SKLEP
  • Oferta
    • Oferta
    • Platforma kursów
      • Zaloguj
  • Blog
  • Kontakt
Zaznacz stronę

Wybieranie wartości niższych niż średnia dla 2 różnych kategorii

07.10.2020 | Narzędzia

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

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

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ł

Tabela kryteriów w podglądzie widoku formuł

Efekt jest taki:

Tabela kryteriów

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

Stosowanie filtru

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

Wynik 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:

 

 

 

← Usuwanie co drugiej kolumny z zakresu VBA: Termin płatności VAT-u, PIT-u i ZUS-u →

Zapisz się na bezpłatny mailing!

Zapisz się na bezpłatny mailing i otrzymaj bezpłatny e-book „10 najprzydatniejszych trików w Excelu”!

OK, zapisuję się!
Newsletter Malinowy Excel | Bezpłatny e-book
Przeczytaj podobne wpisy

Kategorie

Rozdzielanie danych z wierszy komórki do osobnych kolumn

Rozdzielanie danych z wierszy komórki do osobnych kolumn

04.02.2022 | Narzędzia, Triki

0 komentarzy

Wyślij komentarz Anuluj pisanie odpowiedzi

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Zapisz się na bezpłatny mailing!


Zapisując się, wyrażasz zgodę na przetwarzanie Twoich danych osobowych w celu wysyłki newslettera, informacji handlowych i/lub marketingowych o nowościach, promocjach, produktach i usługach MalinowyExcel.pl. Szczegóły związane z przetwarzaniem Twoich danych osobowych znajdziesz w polityce prywatności.

Świetnie! Jeszcze tylko potwierdź zapis w otrzymanym mailu!

.

Obserwuj na Facebooku!

Polityka prywatności

Pin It on Pinterest