Autofiltr i Filtr Zaawansowany

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Autofiltr i Filtr Zaawansowany.xls, tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji.

Przykłady dla wszystkich lekcji szkolenia Excel 2003:. ExcelSzkolenie.pl Cwiczenia Excel 2003.zip

Przykład 1.

(Arkusz: ‘Autofiltr’)

Jeśli chcemy włączyć filtr najlepiej jest zacząć od wybrania komórek, w których znajdują się nagłówki tabeli, która ma być filtrowana, można też wybrać cały wiersz, w którym znajdują się nagłówki naszej tabeli.

Po wybraniu nagłówków włączamy filtr klikając na ikonę lub wybierając z Menu: Dane → Filtr → Autofiltr.

(ikony autofiltr może nie być na pasku ikon.)

To że autofiltr jest włączony widać po pojawieniu się szarych kwadracików ze strzałkami w komórkach z nagłówkami tabeli.

Klikając na ten kwadracik otworzymy okienko, w którym możemy wybrać w tym przykładzie jeden z produktów.

Po wybraniu produktu A linie tabeli zawierające wszystkie inne produkty zostaną ukryte.

Po ukryciu poprzez filtr pozostałych produktów możemy wykonywać operacje tylko na produkcie A, np zsumować ilości produktów lub wykasować ten produkt zaznaczając wszystkie linie. Linie ukryte nie zostaną skasowane.

Aby skasować linie po ich zaznaczeniu należy kliknąć prawym klawiszem myszy na znaczniku wiersza w tym przypadku np. na cyferce 21 i wybrać usuń wiersz, skasowane zostaną 3 linie zawierające produkt A i dane dla niego, pozostałe linie nie zostaną zmienione. Będzie je można znowu zobaczyć po wybraniu w filtrze opcji ‘wszystkie’ bądź po wyłączeniu filtra.

Filtr działa tylko do pierwszej pustej linii, jeśli pusta linia występuje w środku tabeli, część tabeli za pustą linią nie jest filtrowana.

Jeśli z Menu filtru wybierzemy opcje ‘Inne’ pokaże się okienko Autofiltru niestandardowego, które daje dużo różnych opcji. W poniższym przykładzie można np. wybierając autofiltr niestandardowy dla kolumny ‘ilość’, można wyfiltrować te produkty, które są np. w ilości powyżej 150 (wybierając ‘jest większe niż’ i wpisując w okienko obok ‘150’).

Spośród wielu opcji bardzo użyteczna jest opcja ‘zawiera’ dzięki niej można np. wyfiltrować wszystkie produkty zawierające w nazwie słowo ‘próbka’, (słowo to należy wpisać w okienko obok).

Autofiltr wyłączamy wybierając z Menu: Dane → Filtr → Autofiltr.

Autofiltru nie można wyłączyć klikając jeszcze raz ikonę

Przykład 2.

(Arkusz: ‘Filtr Zaawansowany’)

Jeżeli chcielibyśmy przefiltrować naszą tabelę dla więcej niż 2 kryteriów dla tej samej kolumny nie będziemy tego w stanie zrobić używając Autofiltra.

W poniższym przykładzie chcielibyśmy wyfiltrować dane dla 3 produktów: ‘A’, ‘G’ i ‘H’.

Najpierw najlepiej nad tabelą z danymi musimy dodać wiersze, tyle, aby zmieściły się w nich:

- nagłówki tabeli, takie same jak w poniższej tabeli. W naszym przykładzie: ‘produkt’, ‘ilość’ w linii 1.

- kryteria - każde w osobnym wierszu. W naszym przykładzie: ‘produkt A’, ‘produkt G’, ‘produkt H’ w liniach 2, 3, 4.

- jedna pusta linia pomiędzy tabelą z kryteriami a tabelą z danymi. W naszym przykładzie linia 5.

Wybieramy z Menu: ‘Dane’ → ‘Filtr’ → ‘Filtr zaawansowany...’

Wprowadzamy zakres listy i zakres kryteriów, tak jak jest to pokazane na poniższym rysunku.

Filtr zaawansowany oferuje 2 opcje filtrowania:

1. Odfiltrowane dane zostaną ukryte, a te interesujące nas pozostawione. Z tej opcji skorzystamy w tym przykładzie.

2. Interesujące nas dane zostaną skopiowane we wskazane przez nas miejsce.

Po wybraniu ‘Filtruj listę na miejscu’, klikamy przycisk ‘OK’.

W efekcie uzyskamy arkusz taki jak poniżej.

To, że Filtr zawansowany jest włączony nie jest w żaden widoczny sposób oznaczone na arkuszu.

Wiersze odfiltrowane są ukryte, jednak nie ma możliwości pokazania ukrytych wierszy poprzez polecenie ‘Odkryj’. Filtr zaawansowany blokuje opcję ‘Odkryj’ dla całego arkusza, w tym wierszy nie objętych filtrowaniem.

Jeżeli więc widzimy arkusz z ukrytymi wierszami, których nie udaje się odkryć należy sprawdzić czy Filtr zaawansowany jest włączony.

Aby wyłączyć Filtr zawansowany należy wybrać z Menu: ‘Dane’ → ‘Filtr’ → ‘Pokaż wszystko’.

Innym przykładem użycia filtra zaawansowanego może być wyfiltrowanie z naszej listy unikatowych nazw produktów.

Aby to uzyskać zaznaczamy opcję ‘Kopiuj w inne miejsce’, zmieniamy ‘Zakres listy’ tak aby obejmował tylko kolumnę z nazwami produktów, ‘Zakres kryteriów’ pozostawiamy pusty, w ‘Kopiuj do:’ wpisujemy adres pierwszej komórki w której ma zacząć się lista i zaznaczamy opcję ‘Tylko unikatowe rekordy’. Wciskamy przycisk OK.

W rezultacie uzyskujemy listę, dzięki której możemy łatwo określić ilość rodzajów produktów, zsumować ilości tych produktów lub przeprowadzić wiele innych operacji.

Dodamy teraz drugi warunek, niech ilość będzie powyżej 300. Do określania tego typu warunków możemy używać znaków =, <, >, >=, =<.

Wprowadzamy warunek do komórki C2 i korzystamy z filtra zaawansowanego.

W wyniku został odfiltrowany produkt A o ilości 123 (zaznaczony w oryginalnej tabeli czerwoną obwódką, jednak pokazane zostały 2 produkty G których ilości są poniżej 300.

Stało się tak ponieważ warunek >300 wpisany w komórkę C2 odnosi się tylko do produktu A (bo jest w tym samym wierszu co warunek ‘produkt A’).

Jeżeli warunek >300 ma się odnosić do wszystkich produktów musi zostać skopiowany na wszystkie wiersze w których są warunki odnoszące się do nazw produktów.

W ten sposób wyfiltrujemy wyłącznie produkty A, G i H których ilości są większe niż 300.

Przykład 3.

(Arkusz: ‘Górne Dolne’)

Autofiltr oprócz omówionych wcześniej opcji ma jeszcze możliwość wybrania polecenia (10 pierwszych…), która wbrew swojej nazwie ma znacznie więcej zastosowań, niż tylko pokazywania 10 pierwszych (największych) rekordów.

Wybierzmy polecenie (10 pierwszych…) dla kolumny ‘Sprzedaż’.

Wyświetlone zostanie okno ‘Autofiltr 10 pierwszych’, które umożliwia pokazanie wybranej liczby największych lub najmniejszych rekordów, wg danych z kolumny w której kliknięto na (10 pierwszych…)

Może zostać wyświetlonych określona liczba rekordów (elementów) lub wybrany przez nas procent rekordów o najwyższych/najniższych wartościach określonej danej.

Wybierzmy 5 rekordów z najwyższą sprzedażą.

Rekordy nie spełniające wyżej opisanego warunku zostały ukryte.

Polecenie to nie działa dla danych tekstowych.

Ten sam problem możemy też rozwiązać używając funkcji POZYCJA opisanej w lekcji ‘Funkcje dla Zaawansowanych’ lub też sortując dane.

Użyteczność Autofiltra i Filtra Zaawansowanego staje się tym bardziej widoczna im większe i bardziej skomplikowane są tabele danych, na których pracujemy.

Więcej opcji filtrowania oferują Tabele Przestawne.

Szkolenie Excel 2003 Szkolenie Excel 2007