Jak filtrować wybrane kolumny w Excelu?

Z lekcji dowiesz się:

• Jak wyciągać kolumny z danych źródłowych pod warunkiem?

• Do czego służą filtry zaawansowane?

• W jakich sytuacjach wykorzystać filtrowanie kolumn?

Zadanie to wykonamy na podstawie przykładowych danych z rys. 1.

(Rys. 1) Przykładowe dane

Załóżmy, że interesują nas kolumny Data, Produkt i Przychód, ale nie potrzebujemy takich danych jak Województwo i Sprzedawca, ponieważ to będą nasze kryteria. Chcemy otrzymać dane dla konkretnego województwa i konkretnego sprzedawcy, więc powtarzanie tych danych w każdym wierszu jest zbędne. Możemy to zrobić w prosty sposób za pomocą filtrów zaawansowanych. Wystarczy że zaznaczymy dowolną komórkę w naszych danych źródłowych, następnie wybierzemy polecenie Zaawansowane z karty Dane (rys. 2).

(Rys. 2) Polecenie Zaawansowane z karty Dane

Pojawi się okno Filtru zaawansowanego, gdzie w pierwszej kolejności sprawdzamy, czy w polu Zakres listy Excel prawidłowo rozpoznał nasz zakres danych. W grupie poleceń Akcja zaznaczamy checkbox przy poleceniu Kopiuj w inne miejsce, wybieramy tą opcję ponieważ zależy nam na skopiowaniu danych, a nie filtrowaniu danych źródłowych.

W polu Zakres kryteriów zaznaczamy cały wiersz kryteriów, włącznie z nagłówkami, przygotowany nad danymi źródłowymi. Istotne jest, że jeśli mamy podane kryteria w jednym wierszu to działają one na zasadzie „i” (wszystkie kryteria muszą być spełnione jednocześnie). Jeśli natomiast mielibyśmy więcej wierszy z kryteriami, działałyby na zasadzie „lub”, czyli muszą być spełnione warunki z pierwszego wiersza lub z kolejnego.

W polu Kopiuj do zaznaczmy zakres nagłówków kolumn, gdzie chcemy otrzymać nasze przefiltrowane dane. Istotne jest, że jeśli interesuje nas tylko kilka kolumn (wybrane kolumny) to musimy sobie wcześniej przygotować ich nagłówki. Tak ustawione parametry filtra zaawansowanego zatwierdzamy przyciskiem OK (rys. 3).

(Rys. 3) Okno Filtra zaawansowanego

Otrzymamy skopiowane i przefiltrowane dane przedstawione na rys. 4.

(Rys. 4) Przefiltrowane dane

Zakres czasu

W kolejnym etapie pokażemy jak wyciągnąć dane, gdzie kryterium obejmuje zakres czasu pomiędzy konkretnymi datami. Przy takim działaniu w kryteriach musimy dwukrotnie wstawić nagłówek kolumny Data. W tym przykładzie będą nas interesować dane od daty 2019-01-15 do 2019-02-16, czyli jeden miesiąc. W pierwszej kolumnie Data musimy zapisać, że interesują nas daty większe bądź równe 2019-01-15 (>=2019-01-15), a w drugiej daty mniejsze od 2019-02-16 (>2019-02-16). Kryteria zostały pokazane na rys. 5.

(Rys. 5) Kryteria do filtra zaawansowanego

Jeśli raz nałożyliśmy filtry zawansowane w danym arkuszu, to możemy aktywną komórkę ustawić w dowolnym miejscu, a po użyciu polecenia Zaawansowane z karty Narzędzia główne, Excel otworzy okno Filtra zawansowanego z zapamiętanymi zakresami. W otwartym okienku musimy pamiętać o zmianie Akcji, czyli zaznaczeniu checkboxa przy poleceniu Kopiuj w inne miejsce. Tak ustawione parametry filtra zatwierdzamy przyciskiem OK (rys. 6).

(Rys. 6) Parametry filtra zaawansowanego

Otrzymamy przefiltrowane dane (rys. 7), gdzie możemy zauważyć, że zmienił się zakres dat (zostały pominięte dane spoza dat ustawionych jako kryterium).

(Rys. 7) Przefiltrowane dane obejmujące jeden miesiąc

Jeśli zależy nam na jeszcze większym zautomatyzowaniu naszego filtru, możemy zarejestrować makro i podpiąć pod event zmiany danych w arkuszu. W tym celu wybieramy polecenie Zarejestruj makro z karty Deweloper (rys. 8).

(Rys. 8) Polecenie Zarejestruj makro

Otworzy się okno Rejestrowania makra, które zatwierdzamy przyciskiem OK (rys. 9).

(Rys. 9) Okno Rejestrowania makra

Po zatwierdzeniu rejestrowania makra, możemy wybrać polecenie Zaawansowane z karty Dane (jak na rys. 2). Otworzy się okno Filtra zaawansowanego, gdzie musimy pamiętać jedynie o zmianie Akcji, czyli zaznaczeniu checkboxa przy poleceniu Kopiuj w inne miejsce. Powyższą zmianę zatwierdzamy przyciskiem OK. To jest całe makro, które chcieliśmy zarejestrować, więc wybieramy polecenie Zatrzymaj rejestrowanie z karty Deweloper (rys. 10).

(Rys. 10) Polecenie Zatrzymaj rejestrowanie

Po zarejestrowaniu makra przechodzimy do VBA, wybierając polecenie Visual Basic z karty Deweloper (rys. 11).

(Rys. 11) Polecenie Visual Basic

Otworzy się okno VBA z kodem makra (rys. 12).

(Rys. 12) Okno VBA z kodem zarejestrowanego makra

W tym kodzie wprowadzimy zmiany, między innymi usuniemy zbędne informacje takie jak Aplication.CutCopyMode=False (wyłączanie trybu kopiowania). Istotne są tylko dwie linijki kodu przedstawione na rys. 13. Zapis makra powinien wyglądać następująco:

Sub Makro2 ( )

Range ("A5:G19088").AdvancedFilter Action:=xlFilterCopy, CriteriaRange := _

Range ("A1:G2"), CopyToRange := Range ("I5:K5"), Unique := False

End Sub

(Rys. 13) Kod makra zapisany w VBA

Podsumowując mamy kod, który nakłada filtr zaawansowany i kopiuje dane we wskazane miejsce. Naszym celem jest napisanie kodu, który sprawi, że makro będzie się uruchamiało za każdym razem, kiedy zostaną zmienione wskazane kryteria, czyli komórki z zakresu danych A2:G2.

W tym celu kopiujemy kod za pomocą skrótu klawiszowego Ctrl+C, następnie w okienku Project Explorer wybieramy moduł Arkusza(koniec) i klikamy na niego dwukrotnie. Otworzy się moduł dla Arkusza (koniec), gdzie na liście rozwijanej wybieramy Event dla Worksheetu (rys. 14).

(Rys. 14) Lista rozwijana z wyborem eventu dla Worksheetu

Następnie na liście rozwijanej obok wybieramy opcję Change, czyli uruchomienie makra przy każdej zmianie w arkuszu (jak na rys. 15).

(Rys. 15) Lista rozwijana z wyborem rodzaju eventu (Change)

Wklejamy wcześniej skopiowany kod za pomocą skrótu klawiszowego Ctrl+V w miejscu zaznaczonym na rys. 16.

(Rys. 16) Miejsce wklejenia kodu

Ponadto dolną część kodu możemy skasować za pomocą klawisza Delete. Interesuje nas tylko następujący kod:

Private Sub Worksheet_Change (ByVal Target As Range)

Range ("A5:G19088").AdvancedFilter Action:=xlFilterCopy, CriteriaRange := _

Range ("A1:G2"), CopyToRange := Range ("I5:K5"), Unique := False

End Sub

Podsumowując przy tak napisanym kodzie event uruchamia się za każdym razem, kiedy następuje zmiana w arkuszu, nawet jeśli jest to poza zakresem kryteriów. Musimy wstawić tutaj odpowiedni warunek If, żeby makro uruchamiało się tylko wtedy, kiedy zostają zmienione kryteria. Zmiana następuje, kiedy mamy przecięcie między argumentem Target, czyli zakresem, który się zmienia, a zakresem, który nas interesuje.

Użyjemy tutaj funkcji Intersect. Pierwszym argumentem funkcji będzie Target (zakres, który się zmienia), natomiast drugi argument to Range (A2:G2), czyli zakres kryteriów. Funkcja Intersect zwraca zakres przecięcia, czyli komórki z zakresu. Dlatego w Internecie można znaleźć odwrotny sposób, czyli sprawdzanie czy funkcja Intersect nie znalazła przecięcia. Dlatego korzystamy z następującego kodu:

If Not Intersect (Target, Range ("A2:G2")) Is Nothing Then

Podsumowując - cały kod dla eventu powinien wyglądać następująco (rys. 17):

Private Sub Worksheet_Change (ByVal Target As Range)

If Not Intersect (Target, Range ("A2:G2")) Is Nothing Then

Range ("A5:G19088").AdvancedFilter Action:=xlFilterCopy, CriteriaRange := _

Range ("A1:G2"), CopyToRange := Range ("I5:K5"), Unique := False

End If

End Sub

(Rys. 17) Kod dla eventu

Teraz po przejściu do Excela, jeśli zmienimy dane w kryteriach, np. jako sprzedawcę wybierzemy Henryka, to otrzymamy zmienione dane dla wybranych kryteriów (rys. 18).

(Rys. 18) Zaktualizowane dane po zmianie sprzedawcy w kryteriach

Analogicznie możemy zmieniać inne kryteria, takie jak: Województwo, czy Daty. Dzięki napisaniu kodu z rys. 17 każda zmiana w kryteriach, spowoduje aktualizację danych (przefiltrowanie danych według nowych kryteriów). Taka automatyzacja filtrowania zaawansowanego spowoduje skrócenie czasu pracy, ponieważ Excel sam przefiltruje dane.

Polecamy także:

• Jak sprytnie przeklejać wartości w puste pola?

• Jak wyszukać auto funkcjami: ORAZ, LUB i formułą JEŻELI?

Adam Kopeć