Filtr zaawansowany w Excelu

Dzisiaj skupimy się na filtrze zaawansowanym w Excelu. Zacznijmy jednak od początku. Co jest najpopularniejszym sposobem filtrowania w Excelu? Oczywiście użycie autofiltra, czyli strzałek, które pojawiają się w nagłówku filtrowanej tabeli. Jednak czasami opcje, które tam są dostępne, nie są wystarczające. W takiej sytuacji przydaje się filtr zaawansowany. Jeśli jednak szukać informacji o autofiltrze, to wejdź do naszego innego artykułu.

SPIS TREŚCI

Jak działa filtr zaawansowany w Excelu?

Kryteriów filtrowania w filtrze zaawansowania nie wybiera się w żadnym „okienku”, ani na żadnej liście rozwijanej, tylko zapisuje w dowolnych komórkach, w arkuszu. Oto schemat działania:

• Gdy filtrujesz tabele, to zazwyczaj nie korzystasz w filtrze ze wszystkich ale z kilku, a bardzo często tylko z jednej kolumny. W takim razie, nagłówki tych kolumn (albo jednej kolumny) skopiuj gdzieś do arkusza. Najlepiej umieścić je ponad filtrowaną tabelą, tak aby w czasie filtrowania nie ukryły się one – jeśli wkleisz je obok tabeli, to jeśli filtr schowa akurat te wiersze, to ciężko będzie taki filtr potem edytować.

Rysunek 1. Nagłówki kolumn, według których filtrujesz, wklej nad tabelę.

• Pod nagłówkami wstaw wartości, po których chcesz filtrować bazę danych. Jeśli szukasz np. osób o imieniu „Piotr”, to wpisz pod wklejonym nagłówkiem właśnie „Piotr”.
• Włącz filtr zaawansowany, czyli przejdź na wstążce do zakładki Dane i naciśnij (rysunek 2).
• W oknie, które się pojawi, wskaż Zakres listy czyli tabelę danych, która ma zostać przefiltrowana (rysunek 3).
• Wskaż zakres kryteriów, czyli zakres komórek, w których znajdują się nagłówki i dane, po których ma się odbyć filtrowanie (rysunek 3). Są to komórki, do których przed chwilą wklejaliśmy.
• Naciśnij OK.

Rysunek 2. Przycisk filtru zaawansowanego znajduje się w zakładce Dane programu Excel.

Rysunek 3. Okno filtrowania zaawansowanego w Excelu.

W tym momencie dane zostaną przefiltrowane, w taki sposób, żeby pokazywać tylko te wartości, które umieszczono w kryteriach (wklejono obok tabeli).

Dlaczego filtr zaawansowany zamiast zwykłego?

Zastanawiasz się po co tak wydziwiać z z filtrem zaawansowanym, skoro można użyć zwykłego filtru Excela? Na początku pewnie dużo osób ma takie myśli. Okazuje się, że jeśli Twoje potrzeby filtrowania są niestandardowe, to właśnie filtr zaawansowany może wypełnić luki zwykłego filtrowania. Oto najpopularniejsze zalety filtru zaawansowanego:

• Filtrowanie alternatywne – wyobraź sobie, że filtrujesz według dwóch kolumn. W normalnym filtrze, obydwa warunki, które dla tych kolumn wybierzesz muszą być spełnione jednocześnie, aby wiersz pozostał widoczny. Jeśli wybierzesz w kolumnie Imię „Piotr”, a w kolumnie Nazwisko „Kowalski”, to znajdziesz jedynie osoby, które jednocześnie mają na imię Piotr, a na nazwisko Kowalski. Nie znajdziesz np. Henryka Kowalskiego. Filtry ze wszystkich kolumn są traktowane łącznie w zwykłym filtrowaniu. Filtr zaawansowany pozwala znaleźć osoby, które mają na imię Piotr lub na nazwisko Kowalski. Więcej na ten temat w kolejnej sekcji.
• Zwiększa przejrzystość – jeśli dużo filtrujesz, według wielu kolumn, to używając filtru zwykłego, nie widzisz od razu jakie warunki filtrowania zostały użyte (chyba, że bardzo dobrze znasz swoje dane). Żeby to sprawdzić, musisz wejść do filtru w każdej z kolumn. Używając filtru zaawansowanego, wszystkie kryteria masz rozpisane
  w komórkach, w jednym miejscu.
• Ułatwia zarządzanie filtrami – jeśli wiele filtrujesz, np. według 5 kolumn i musisz nagle ustawić kryteria w tych 5 kolumnach, to musisz albo wejść do każdej z nich, sprawdzić jakie kryterium było wcześniej i je ewentualnie zmienić, albo możesz wyczyścić wszystkie filtry i wstawić je od początku. Mając wszystkie filtry w komórkach, możesz wygodnie zmieniać ich kryteria i w ten sposób odświeżać filtr.
• Więcej niż dwa warunki w jednej kolumnie – w zwykłym filtrze, możesz w ramach jednej kolumny wstawić maksymalnie dwa warunki. Tutaj, możesz wstawić ich dowolnie dużo. Więcej w kolejnej sekcji (rysunek 4).
• Półautomatyzacja – kryteria filtrowania, w zaawansowanym filtrze, wcale nie muszą być wklejane ani wpisywane ręcznie. Może się tam znajdować formuła, która na podstawie tego co się dzieje w arkuszu, sama zaciąga odpowiednie wartości kryterium.
• Filtrowanie z użyciem formuł – jeśli Twoje kryterium filtrowania jest bardzo skomplikowane albo proste, ale uwzględnia specyficzne warunki (np. chcesz filtrować tak, żeby pozostały widoczne w tabeli tylko daty od poniedziałku do czwartku), to możesz stworzyć to kryterium, używając dowolnej funkcji dostępnej w Excelu.
• Kopiowanie danych – opcja przydatna, gdy pracujesz na bardzo dużych tabelach. Po przefiltrowaniu danych, zamiast ukrywać odpowiednie wiersze, to te wartości, które normalnie pozostałyby widoczne, zostają skopiowane obok, w miejsce, które wskażesz. Masz wtedy oryginalną tabelę, plus tabelę z wynikami filtrowania (rysunek 5).
• Unikatowe wartości – umożliwia łatwe znalezienie wszystkich wartości, które chociaż raz się pojawiają w tabeli i umieszcza je w formie listy (rysunek 6).

Więcej o filtrze zaawansowanym

Sprawdź za darmo calculatic.pl gdzie znajdziesz wiele więcej przykładowych zadań!

SPRAWDŹ ZA DARMO!

 

Rysunek 4. Filtr niestandardowy w zwykłym autofiltrze pozwala na maksymalnie dwa warunki w jednej kolumnie.

Rysunek 5. Opcje dotyczące kopiowania danych w oknie filtru zaawansowanego.

Rysunek 6. Opcja unikatowych wartości w filtrze zaawansowanym

Zasady używania filtru zaawansowanego

Zanim zaczniesz go używać, musisz poznać kilka ważnych zasad, które pomogą uniknąć Ci nieprzyjemnych niespodzianek:

• Nie można jednocześnie używać filtru zwykłego i zaawansowanego – niestety, jeśli masz włączony filtr zwykły i wtedy spróbujesz użyć zaawansowanego, to zwykły filtr jest czyszczony. Analogicznie działa to w drugą stronę. Jeden filtr „nadpisuje” wyniki filtrowania drugiego. Jeśli chcesz sprawdzić, czy filtr zaawansowany dobrze działa, często pojawia się chęć do tego, żeby użyć zwykłego filtru do przetestowania – niestety samo wstawienie strzałek filtrowania powoduje już wyłączenie filtru zaawansowanego. Jeśli nie masz zaufania do zaawansowanego, to potestuj go na prostych tabelach, gdzie rezultat widać gołym okiem.
• Wynik filtru zwykłego i zaawansowanego jest taki sam – obojętnie jak filtrujesz, Excel ukrywa odpowiednie wiersze – efekt w obu przypadkach jest identyczny.
• Nagłówki muszą się zgadzać – nagłówki kolumn, które przepisujesz/kopiujesz, muszą być dokładnie takie same, jak te w tabeli, bo według nich Excel rozpoznaje kolumny. Dlatego najlepiej kopiować – przy przepisywaniu łatwo o literówkę (rysunek 7).
• Nagłówki muszą być pierwsze – gdy kopiujesz nagłówki, a pod nimi kryteria, to pamiętaj, że gdy wstawiając filtr, zaznaczasz zakres kryteriów, to pierwszy zaznaczony wiersz musi zawierać właśnie te nagłówki (nie zaznaczaj np. jednego, pustego wiersza powyżej) (rysunek 8).
• Excel zapamiętuje ostatnio wybrane zakresy – w wielu narzędziach Excela (np. tabele przestawne, zwykły filtr, wykresy i wiele innych) jest tak, że jeśli zaznaczysz jedną komórkę w tabeli i uruchomisz narzędzie, to ono automatycznie rozpoznaje całą tabelę. W przypadku filtru zaawansowanego jest inaczej. Jeśli raz zaznaczysz zakres, czy to w zakresie listy, czy w zakresie kryteriów, to Excel go później pamięta. Czyli jeśli ponownie uruchomisz filtr zaawansowany, to na pewno zobaczysz zakresy takie same, jakie były wcześniej wybrane. Zostało to w ten sposób przygotowane, żeby łatwo było można odświeżać filtr – jeśli cokolwiek zmieni się w kryteriach, to wystarczy odpalić filtr zaawansowany i nacisnąć ok, nie martwiąc się o zakresy.

Rysunek 7. Nagłówki kolumn muszą się zgadzać.

Rysunek 8. Nagłówki w kryteriach muszą być pierwsze.

Filtr zaawansowany poznasz też na szkoleniach z Excela organizowanych przez naszą firmę.