Z nami uczy się już 7400 osób. Czekamy na Ciebie

Strona główna » Blog » Narzędzia » Filtrowanie zaawansowane

Filtrowanie zaawansowane

Zastosowanie

Jeżeli często potrzebujemy filtrować dane używając wielu kryteriów na raz zwykle korzystamy z autofiltra. Jeżeli jednak z tych samych danych musimy wyszukać wiele różnych wariantów to zmusza nas to do wielokrotnego zaznaczania i odznaczania pozycji na listach rozwijanych. Wtedy pomocny się okaże filtr zaawansowany. Pozwoli nam na przygotowanie i zapisanie różnych kryteriów, które możemy wykorzystać kiedy są potrzebne.

Przykładowy problem do analizy

Załóżmy, że prowadzimy firmę oferującą usługi w wersji standardowej i rozszerzonej. Pobieramy opłaty za kwartał. Usługa standardowa kosztuje 100 zł, a usługa rozszerzona 200zł. Czas trwania usługi to kwartał i klient płacąc za kwartał decyduje, którą wersję wybiera. Nie mamy umów na każdy kwartał i do przypisania usług korzystamy z bilingu wpłat. Spróbujmy przeanalizować kto z jakiej usługi korzysta. Dodatkowo Jan i Stefan Kowalski prowadzą rodzinną firmę, z której usług korzystamy i zdecydowaliśmy się udzielić wersję rozszerzoną w cenie standardowej. Przykładowe wpłaty:

1. Przykładowy listing

Analiza przypadków

Na początek musimy ustalić co nam jest potrzebne i jakie kryteria muszą być spełnione. Każdy przypadek musi być rozpisany. W naszym przykładzie musimy ustalić komu przysługuje usługa standardowa, a komu rozszerzona.

  1. Każdy kto zapłacił 200zł ma usługę rozszerzoną.
  2. Jan i Stefan Kowalski mają usługę rozszerzoną jeżeli zapłacili 100 zł.
  3. Każdy kto nie jest Janem lub Stefanem Kowalskim i zapłacił 100 zł ma usługę standardową.

Docelowo chcemy połączyć przypadek 1 i 2 i mieć dwa filtry:

  1. Osoby z usługą rozszerzoną: Wszyscy, którzy zapłacili 200 zł oraz Jan i Stefan Kowalski jeżeli zapłacili 100 zł.
  2. Osoby z usługą standardową: Wszyscy, którzy zapłacili 100 zł i nie są Janem lub Stefanem Kowalskim.

Zasady konstrukcji filtra zaawansowanego

Żeby zastosować filtr zaawansowany to trzeba przygotować kryteria wyszukiwania w formie tabeli. Tabela musi mieć nagłówki takie same jak dane filtrowane. Pod nagłówkami tworzymy listę kryteriów do spełnienia. Na początek najlepiej dodać kilka pustych wierszy powyżej naszych danych i skopiować nagłówek tabeli do pierwszego wiersza.

2. Dodane wiersze

Teraz mamy miejsce na kryteria wyszukiwania. Mają one określoną składnię bardzo zbliżoną do formuł. Jeżeli chcemy znaleźć kwotę o wartości 200 zł to kryterium wygląda tak: =200. Jeżeli jednak wpiszemy to kryterium do komórki to Excel uzna to za formułę i w komórce zobaczymy samą liczbę 200. Dlatego musimy to kryterium wprowadzić jako tekst. Robimy to w ten sposób: =”=200”. Kryterium zamykamy w cudzysłów, dzięki czemu jest traktowany jak tekst. Jest on wprowadzony do komórki niezmieniony dzięki znakowi równości.

Oczywiście mamy do dyspozycji komplet operatorów:

  • = równe
  • > większe niż
  • < mniejsze niż
  • >= większe lub równe
  • <= mniejsze lub równe
  • <> różne

Teraz żeby wyfiltrować wszystkie osoby, które zapłaciły 200 zł wpiszmy formułę do komórki E2: =”=200”.

3. Równe 200 - zaznaczenie

Teraz już możemy zastosować nasz filtr i sprawdzić jak działa. Z menu „Dane” w grupie „Sortowanie i filtrowanie” wybieramy przycisk „Zaawansowane”.

4. Menu filtr zaawansowany - zaznaczenie

5. Okno filtra zaawansowanego

W grupie „Akcja” zostawiamy opcję „Filtruj listę na miejscu”. Zakres listy to nasza tabela z danymi zaznaczona razem z nagłówkami. W naszym przykładzie to zakres A5:E25. Zakres kryteriów to A1:E2. W tym miejscu musimy pamiętać, że nie możemy zaznaczyć więcej wierszy niż potrzebujemy. Zaznaczenie pustego wiersza oznacza, że chcemy widzieć wszystkie dane bez kryteriów. Klikamy przycisk „OK”. Teraz na liście widzimy wszystkich, którzy zapłacili 200 zł.

6. Filtr 200

Wszystko zadziałało tak jak chcieliśmy i zbudowaliśmy prosty filtr, który rozwiązuje przypadek 1 naszej analizy.

Filtr z wieloma kryteriami

Teraz krok po kroku spróbujmy dojść do rozwiązania kolejnych przypadków. Drugi przypadek to:

  • Jan i Stefan Kowalski mają usługę rozszerzoną jeżeli zapłacili 100 zł.

Najpierw wyszukajmy z listy wpłaty Jana Kowalskiego. Musimy w tym przypadku określić dwa kryteria. W kolumnie „Imię” chcemy wybrać „Jan”. W kolumnie „Nazwisko” chcemy wybrać „Kowalski”. Jest to tak samo proste jak poprzedni przypadek. W komórkę B2 wpisujemy formułę =”=Jan”, a w komórkę C2: =”=Kowalski”. Jeżeli podajemy kryteria w jednym wierszu to wszystkie muszą być spełnione. To znaczy, że nie zobaczymy listy wszystkich Janów oraz wszystkich Kowalskich. Zobaczymy tylko wpłaty Jana Kowalskiego. Włączmy filtr zaawansowany jak poprzednio i upewnijmy się, że nie zmieniły się parametry. Zakres listy to nadal A5:E25, a zakres kryteriów A1:E2. Kliknijmy przycisk „OK”. Teraz nasza tabela wygląda tak:

7. Jan Kowalski

Nasz filtr wykonał operację:

(Imię = Jan) i (Nazwisko = Kowalski)

Ponieważ kryteria były w jednym wierszu to został wykonany operator „i”. Żeby wykonać operator „lub” kryteria muszą być jednej kolumnie. Teraz jeżeli chcemy wyświetlić i Jana i Stefana Kowalskiego to operacja będzie wyglądała tak:

((Imię = Jan) i (Nazwisko = Kowalski)) lub ((Imię = Stefan) i (Nazwisko = Kowalski))

Wygląda trochę skomplikowanie, ale może pomóc osobom, które wolą zapis matematyczny. Możemy tą ideę zapamiętać też konstruując tabelkę. Mamy wybranego Jana Kowalskiego chcemy też Stefana Kowalskiego (operator „lub”) więc wpisujemy formułę w kolejnym wierszu. Do komórki B3 wpisujemy =”=Stefan”, a do komórki C3 wpisujemy =”=Kowalski”. Musimy za każdym razem podać warunek Nazwisko=Kowalski ponieważ bez tego mielibyśmy na liście wszystkich Janów i wszystkich Stefanów niezależnie od nazwiska. Włączmy filtrowanie zaawansowane i upewnijmy się, że zakres listy się nie zmienił (A5:E25) i zaznaczmy zakres kryteriów powiększając go o nowy wiersz (A1:E3). Kliknijmy „OK” i teraz nasza lista wygląda tak:

8. Jan Stefan Kowalski

Teraz interesują nas kwoty wpłat. Chcemy widzieć wpłaty od Jana i Stefana Kowalskich w wysokości 100 zł i wszystkie inne w wysokości 200 zł. Nic prostszego. W komórki E2 i E3 wpisujemy formułę =”=100”. To załatwia sprawę tych dwóch panów. Kolejne kryterium nie zależy od niczego innego niż kwoty więc je wpisujemy do kolejnego wiersza czyli do komórki E4. Formuła to oczywiście =”=200”. Włączając filtr zaawansowany znowu korygujemy zakres kryteriów i wpisujemy A1:E4. Teraz po kliknięciu „OK” nasza lista wygląda tak:

9. Jan Stefan 200

Właśnie otrzymaliśmy listę wszystkich osób, które wykupiły u nas usługę rozszerzoną. Jest to połączenie 1 i 2 przypadku. Zwróćcie uwagę, że nie da się osiągnąć takiego wyniku stosując zwykły filtr.

Filtr z wieloma kryteriami dla tej samej kolumny

Pozostało nam rozpatrzyć 3 przypadek czyli:

  • Każdy kto nie jest Janem lub Stefanem Kowalskim i zapłacił 100 zł ma usługę standardową.

Spróbujmy znów przeanalizować sprawę krok po kroku. Na początek spróbujmy usunąć z listy Jana Kowalskiego. Czyli chcemy Kowalskich, ale nie Jana.

(Imię <> Jan) i (Nazwisko = Kowalski)

Wpiszmy formuły do komórek B2: =”<>Jan”, C2: =”=Kowalski”. Poprawmy zakres kryteriów filtra zaawansowanego na A1:E2. Spójrzmy na efekt:

10 Bez Jana Kowalskiego

Mamy wszystkich Kowalskich oprócz Jana, ale co z pozostałymi? No cóż. W naszym filtrze nie ma mowy o innych więc ich nie ma, ale najpierw spróbujmy odfiltrować jeszcze Stefana. Jest to operacja tego typu:

(Imię <> Jan) i (Imię <> Stefan) i (Nazwisko = Kowalski)

Wygląda prosto… Tylko jak dodać dwa różne kryteria do jednej kolumny? Przecież na kryteria mamy tylko jedną komórkę… Wbrew pozorom jest to bardzo proste. Nasza tabela kryteriów nie musi mieć tyle kolumn co lista wartości. Kolumna jest rozpoznawana po nagłówku. Możemy zrobić więcej kolumn o tym samym nagłówku i przypisać więcej kryteriów. W naszym przykładzie w komórce F1 tworzymy drugi nagłówek „Imię” i w komórce F2 wpisujemy formułę =”<>Stefan”. W filtrze zaawansowanym zaznaczamy zakres kryteriów na A1:F2. Teraz nasza lista wygląda tak:

11. Bez Jana i Stefana Kowalskiego

Teraz mamy wszystkich Kowalskich bez Jana i Stefana. Chcemy listę tylko tych, którzy wpłacili 100 zł więc do komórki E2 wpisujemy =”=100”. Teraz chcemy wszystkich, którzy nie są Kowalskim i zapłacili 100 zł. Myślę, że już potraficie dodać ostatnie kryteria więc nie opisuję. Efekt końcowy wygląda tak:

12. Standard

Porządkowanie kryteriów filtra zaawansowanego

Mamy wypracowane 2 zestawy filtrów, nad którymi się napracowaliśmy. Warto je zapisać i użyć kiedy będą potrzebne. Jak już pisałem tabela kryteriów nie musi mieć tyle samo kolumn co lista wartości. Możemy pominąć kolumny, których wartości nie uwzględniamy. Kryteria nie muszą też być nad listą. Mogą być w innym arkuszu. Możemy je umieścić. gdzie chcemy. Nawet z boku:

13. Poukładane

Wyłączanie filtra zaawansowanego

Jeżeli potrzebujemy wyłączyć zastosowany filtr zaawansowany to Z menu „Dane”, grupy „Sortowanie i filtrowanie” klikamy przycisk „Wyczyść”.

14. Menu wyczyść filtr - zaznaczenie


Lekcja-1,99zł
  • Dostęp do wybranej listy

lub dostęp do całego serwisu przez

Rok-49zł
  • Dostęp do ponad 200 lekcji
  • Możliwość zdobycia certyfikatu
  • Udział w konferencjach on-line
  • Liczne pliki, ćwiczenia i testy
  • Co tydzień nowa lekcja
Firmy szkolące się na platformie www.iExcel.pl
CLOSE
CLOSE
Przeczytaj poprzedni wpis:
10_problemoww_milenijnych
10 problemów milenijnych w Excelu

Nagranie z konferencji z dnia  09.03.2016r. Były tam poruszane następujące kwestie: kasowanie pustych wierszy zaawansowane filtrowanie stosowanie nazw przechowywanie danych w...

Zamknij