Excel VBA Autofiltr: A Complete Guide with Examples

Wiele funkcjonalności Excela jest również dostępnych do wykorzystania w VBA – a metoda Autofiltr jest jedną z takich funkcjonalności.

Jeśli masz zbiór danych i chcesz go przefiltrować za pomocą kryterium, możesz to łatwo zrobić za pomocą opcji Filtr na wstążce Dane.

A jeśli chcesz bardziej zaawansowaną wersję tego, w Excelu jest również filtr zaawansowany.

Dlaczego więc w ogóle używać Autofiltra w VBA?

Jeśli potrzebujesz tylko filtrować dane i robić podstawowe rzeczy, polecam trzymać się wbudowanej funkcji Filtruj, którą oferuje interfejs Excela.

Powinieneś używać Autofiltra VBA, gdy chcesz filtrować dane jako część automatyzacji (lub jeśli pomaga ci to zaoszczędzić czas poprzez przyspieszenie filtrowania danych).

Na przykład, załóżmy, że chcesz szybko przefiltrować dane w oparciu o wybór rozwijany, a następnie skopiować te przefiltrowane dane do nowego arkusza.

Mimo, że można to zrobić za pomocą wbudowanej funkcjonalności filtra wraz z wklejaniem kopiuj-wklej, ręczne wykonanie tego może zająć dużo czasu.

W takim przypadku użycie Autofiltra VBA może przyspieszyć pracę i zaoszczędzić czas.

Uwaga: Ten przykład (dotyczący filtrowania danych na podstawie wyboru z listy rozwijanej i kopiowania do nowego arkusza) omówię w dalszej części tego poradnika.

Składnia autofiltru Excel VBA

Expression. AutoFilter( _Field_ , _Criteria1_ , _Operator_ , _Criteria2_ , _VisibleDropDown_ )

• Wyrażenie: Jest to zakres, na którym chcesz zastosować filtr automatyczny.
• Pole: Jest to numer kolumny, którą chcesz filtrować. Jest to liczone od lewej strony w zbiorze danych. Jeśli więc chcesz filtrować dane na podstawie drugiej kolumny, wartość ta będzie wynosić 2.
• Kryteria1: Jest to kryterium, na podstawie którego chcesz filtrować zbiór danych.
• Operator: W przypadku, gdy używasz również kryterium 2, możesz połączyć te dwa kryteria w oparciu o Operator. Dostępne są następujące operatory: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Criteria2: Jest to drugie kryterium, na podstawie którego można filtrować zbiór danych.
• VisibleDropDown: Możesz określić, czy chcesz, aby ikona rozwijana filtra pojawiła się w filtrowanych kolumnach, czy nie. Ten argument może być TRUE lub FALSE.

Oprócz Expression, wszystkie inne argumenty są opcjonalne.

W przypadku, gdy nie użyjesz żadnego argumentu, po prostu zastosuje lub usunie ikony filtra do kolumn.

Sub FilterRows()Worksheets("Filter Data").Range("A1").AutoFilterEnd Sub

Powyższy kod po prostu zastosuje metodę Autofilter do kolumn (lub jeśli już jest zastosowana, usunie ją).

To po prostu oznacza, że jeśli nie widzisz ikony filtra w nagłówkach kolumn, zaczniesz ją widzieć, gdy powyższy kod zostanie wykonany, a jeśli ją widzisz, zostanie usunięta.

W przypadku, gdy masz jakiekolwiek przefiltrowane dane, usunie filtry i pokaże ci pełny zbiór danych.

Teraz zobaczmy kilka przykładów użycia Autofiltra Excel VBA, które sprawią, że jego użycie będzie jasne.

Przykład: Filtrowanie danych na podstawie warunku tekstowego

Załóżmy, że masz zbiór danych, jak pokazano poniżej, i chcesz go filtrować na podstawie kolumny „Pozycja”.

Poniższy kod odfiltrowałby wszystkie wiersze, w których pozycja to „Drukarka”.

Sub FilterRows()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer"End Sub

Powyższy kod odnosi się do Arkusza1, a w nim do A1 (który jest komórką w zbiorze danych).

Zauważ, że tutaj użyliśmy Pole:=2, ponieważ kolumna elementu jest drugą kolumną w naszym zbiorze danych od lewej strony.

Ale zanim ci to pokażę, pozwól, że najpierw omówię kilka przykładów, aby pokazać ci, co może zrobić metoda AutoFilter.

Kliknij tutaj, aby pobrać przykładowy plik i podążać za nim.

Przykład: Wiele kryteriów (AND/OR) w tej samej kolumnie

Załóżmy, że mam ten sam zbiór danych i tym razem chcę odfiltrować wszystkie rekordy, w których elementem jest 'Drukarka’ lub 'Projektor’.

Poniższy kod by to zrobił:

Sub FilterRowsOR()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer", Operator:=xlOr, Criteria2:="Projector"End Sub

Zauważ, że użyłem tutaj operatora xlOR.

To mówi VBA, aby użyć obu kryteriów i filtrować dane, jeśli którekolwiek z dwóch kryteriów jest spełnione.

Podobnie, możesz również użyć kryteriów AND.

Na przykład, jeśli chcesz filtrować wszystkie rekordy, w których ilość jest większa niż 10, ale mniejsza niż 20, możesz użyć poniższego kodu:

Sub FilterRowsAND()Worksheets("Sheet1").Range("A1").AutoFilter Field:=4, Criteria1:=">10", _ Operator:=xlAnd, Criteria2:="<20"End Sub

Przykład: Wiele kryteriów z różnymi kolumnami

Załóżmy, że masz następujący zbiór danych.

Dzięki funkcji Autofiltr możesz filtrować wiele kolumn jednocześnie.

Na przykład, jeśli chcesz odfiltrować wszystkie rekordy, w których element to „Drukarka”, a przedstawiciel handlowy to „Marek”, możesz użyć poniższego kodu:

Sub FilterRows()With Worksheets("Sheet1").Range("A1").AutoFilter field:=2, Criteria1:="Printer".AutoFilter field:=3, Criteria1:="Mark"End WithEnd Sub

Przykład: Filter Top 10 Records Using the AutoFilter Method

Załóżmy, że masz poniższy zbiór danych.

Poniżej znajduje się kod, który da ci 10 najlepszych rekordów (na podstawie kolumny ilość):

Sub FilterRowsTop10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10ItemsEnd Sub

W powyższym kodzie użyłem ActiveSheet. Możesz użyć nazwy arkusza, jeśli chcesz.

Zauważ, że w tym przykładzie, jeśli chcesz uzyskać 5 najlepszych elementów, po prostu zmień liczbę w Kryterium1:=”10″ z 10 na 5.

Więc dla 5 najlepszych elementów, kod będzie następujący:

Sub FilterRowsTop5()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="5", Operator:=xlTop10ItemsEnd Sub

Może to wyglądać dziwnie, ale bez względu na to, ile najlepszych elementów chcesz, wartość Operatora zawsze pozostaje xlTop10Items.

Podobnie, poniższy kod da ci 10 dolnych elementów:

Sub FilterRowsBottom10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlBottom10ItemsEnd Sub

A jeśli chcesz 5 dolnych elementów, zmień liczbę w Criteria1:=”10″ z 10 na 5.

Przykład: Filter Top 10 Percent Using the AutoFilter Method

Załóżmy, że masz taki sam zestaw danych (jak użyty w poprzednich przykładach).

Poniżej znajduje się kod, który da ci 10 najlepszych procent rekordów (na podstawie kolumny quantity):

Sub FilterRowsTop10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10PercentEnd Sub

W naszym zestawie danych, ponieważ mamy 20 rekordów, zwróci on 2 najlepsze rekordy (co stanowi 10% wszystkich rekordów).

Przykład: Using Wildcard Characters in Autofilter

Załóżmy, że masz zbiór danych, jak pokazano poniżej:

Jeśli chcesz odfiltrować wszystkie wiersze, w których nazwa elementu zawiera słowo 'Board’, możesz użyć poniższego kodu:

Sub FilterRowsWildcard()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="*Board*"End Sub

W powyższym kodzie użyłem znaku wieloznacznego * (gwiazdka) przed i po słowie 'Board’ (które jest kryterium).

Gwiazdka może reprezentować dowolną liczbę znaków. Tak więc filtrowany będzie każdy element zawierający słowo 'Board’.

Przykład: Kopiowanie przefiltrowanych wierszy do nowego arkusza

Jeśli chcesz nie tylko filtrować rekordy na podstawie kryteriów, ale także kopiować przefiltrowane wiersze, możesz użyć poniższego makra.

Kopiuje ono przefiltrowane wiersze, dodaje nowy arkusz, a następnie wkleja te skopiowane wiersze do nowego arkusza.

Sub CopyFilteredRows()Dim rng As RangeDim ws As WorksheetIf Worksheets("Sheet1").AutoFilterMode = False ThenMsgBox "There are no filtered rows"Exit SubEnd IfSet rng = Worksheets("Sheet1").AutoFilter.RangeSet ws = Worksheets.Addrng.Copy Range("A1")End Sub

Powyższy kod sprawdzi, czy w Arkuszu1 są jakieś przefiltrowane wiersze, czy nie.

Jeśli nie ma przefiltrowanych wierszy, wyświetli okno komunikatu z informacją o tym.

A jeśli są przefiltrowane wiersze, skopiuje je, wstawi nowy arkusz i wklei te wiersze do tego nowo wstawionego arkusza.

Przykład: Filter Data based on a Cell Value

Używając Autofiltra w VBA wraz z listą rozwijaną, możesz stworzyć funkcjonalność, w której gdy tylko wybierzesz element z listy rozwijanej, wszystkie rekordy dla tego elementu zostaną odfiltrowane.

Coś jak pokazano poniżej:

Kliknij tutaj, aby pobrać plik przykładu i podążać za nim.

Ten typ konstrukcji może być przydatny, gdy chcesz szybko przefiltrować dane, a następnie wykorzystać je w dalszej pracy.

Poniżej znajduje się kod, który to zrobi:

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Address = "$B" Then If Range("B2") = "All" Then Range("A5").AutoFilter Else Range("A5").AutoFilter Field:=2, Criteria1:=Range("B2") End IfEnd IfEnd Sub

Jest to kod zdarzenia arkusza, który zostanie wykonany tylko wtedy, gdy w arkuszu nastąpi zmiana, a komórką docelową jest B2 (gdzie mamy rozwijaną listę).

Jeszcze warunek If Then Else jest użyty do sprawdzenia, czy użytkownik wybrał 'All’ z listy rozwijanej. Jeśli wybrano All, pokazywany jest cały zestaw danych.

Tego kodu NIE umieszcza się w module.

Zamiast tego należy go umieścić w zapleczu arkusza, który posiada te dane.

Oto kroki, które należy wykonać, aby umieścić ten kod w oknie kodu arkusza:

1. Otwórz edytor VB (skrót klawiaturowy – ALT + F11).
2. W okienku Project Explorer kliknij dwukrotnie nazwę arkusza, w którym chcesz mieć tę funkcjonalność filtrowania.
3. W oknie kodu arkusza, skopiuj i wklej powyższy kod.
4. Zamknij edytor VB.

Teraz, gdy używasz listy rozwijanej, będzie ona automatycznie filtrować dane.

Jest to kod zdarzenia arkusza, który jest wykonywany tylko wtedy, gdy w arkuszu jest zmiana, a komórką docelową jest komórka B2 (w której mamy listę rozwijaną).

Jeszcze warunek If Then Else jest używany do sprawdzenia, czy użytkownik wybrał 'All’ z listy rozwijanej. Jeśli wybrano All, wyświetlany jest cały zestaw danych.

Włączanie i wyłączanie autofiltru Excela za pomocą VBA

Podczas stosowania autofiltru do zakresu komórek mogą już istnieć pewne filtry.

Możesz użyć poniższego kodu, aby wyłączyć wszelkie wstępnie zastosowane autofiltry:

Sub TurnOFFAutoFilter() Worksheets("Sheet1").AutoFilterMode = FalseEnd Sub

Ten kod sprawdza całe arkusze i usuwa wszelkie filtry, które zostały zastosowane.

Jeśli nie chcesz wyłączać filtrów z całego arkusza, ale tylko z określonego zestawu danych, użyj poniższego kodu:

Sub TurnOFFAutoFilter() If Worksheets("Sheet1").Range("A1").AutoFilter Then Worksheets("Sheet1").Range("A1").AutoFilter End IfEnd Sub

Powyższy kod sprawdza, czy są już nałożone filtry, czy nie.

Jeśli filtry są już nałożone, usuwa je, w przeciwnym razie nie robi nic.

Podobnie, jeśli chcesz włączyć AutoFilter, użyj poniższego kodu:

Sub TurnOnAutoFilter() If Not Worksheets("Sheet1").Range("A4").AutoFilter Then Worksheets("Sheet1").Range("A4").AutoFilter End IfEnd Sub

Sprawdź, czy AutoFilter jest już zastosowany

Jeśli masz arkusz z wieloma zbiorami danych i chcesz się upewnić, że wiesz, że nie ma już filtrów, możesz użyć poniższego kodu.

Sub CheckforFilters()If ActiveSheet.AutoFilterMode = True ThenMsgBox "There are Filters already in place"ElseMsgBox "There are no filters"End IfEnd Sub

Ten kod używa funkcji okna komunikatów, która wyświetla komunikat „Istnieją już filtry”, gdy znajdzie filtry na arkuszu, w przeciwnym razie wyświetla komunikat „Nie ma żadnych filtrów”.

Pokaż wszystkie dane

Jeśli masz filtry zastosowane do zbioru danych i chcesz pokazać wszystkie dane, użyj poniższego kodu:

Sub ShowAllData()If ActiveSheet.FilterMode Then ActiveSheet.ShowAllDataEnd Sub

Powyższy kod sprawdza, czy FilterMode jest TRUE lub FALSE.

Jeśli ma wartość true, oznacza to, że filtr został zastosowany i używa metody ShowAllData, aby pokazać wszystkie dane.

Zauważ, że nie usuwa to filtrów. Ikony filtrów są nadal dostępne do użycia.

Używanie AutoFiltra na chronionych arkuszach

Domyślnie, gdy chronisz arkusz, filtry nie będą działać.

W przypadku, gdy masz już zastosowane filtry, możesz włączyć AutoFiltr, aby działał nawet na chronionych arkuszach.

Aby to zrobić, zaznacz opcję Użyj Autofiltra podczas chronienia arkusza.

Choć to działa, gdy masz już filtry na miejscu, w przypadku próby dodania Autofiltrów przy użyciu kodu VBA, nie będzie to działać.

Ponieważ arkusz jest chroniony, nie pozwoli na uruchomienie żadnego makra i dokonanie zmian w Autofiltrze.

Więc musisz użyć kodu, aby chronić arkusz i upewnić się, że autofiltry są w nim włączone.

To może być przydatne, gdy utworzyłeś dynamiczny filtr (coś, co omówiłem w przykładzie – „Filtrowanie danych na podstawie wartości komórki”).

Poniżej znajduje się kod, który zabezpieczy arkusz, ale jednocześnie pozwoli na używanie w nim filtrów oraz makr VBA.

Private Sub Workbook_Open()With Worksheets("Sheet1").EnableAutoFilter = True.Protect Password:="password", Contents:=True, UserInterfaceOnly:=TrueEnd WithEnd Sub

Ten kod należy umieścić w oknie kodu ThisWorkbook.

Oto kroki, które należy wykonać, aby umieścić kod w oknie kodu ThisWorkbook:

1. Otwórz edytor VB (skrót klawiaturowy – ALT + F11).
2. W okienku Project Explorer kliknij dwukrotnie obiekt ThisWorkbook.
3. W otwartym oknie kodu, skopiuj i wklej powyższy kod.

Jak tylko otworzysz skoroszyt i włączysz makra, makro zostanie uruchomione automatycznie i zabezpieczy Arkusz1.

Jednakże, zanim to zrobi, określi 'EnableAutoFilter = True’, co oznacza, że filtry będą działać również w chronionym arkuszu.

Ponadto, ustawi argument 'UserInterfaceOnly’ na 'True’. Oznacza to, że gdy arkusz jest chroniony, kod makra VBA będzie nadal działał.

You May Also Like the Following VBA Tutorials:

• Excel VBA Loops.
• Filter Cells with Bold Font Formatting.
• Recording a Macro.
• Sort Data Using VBA.
• Sortowanie zakładek arkusza w Excelu.

.