Autofiltr Excel VBA: kompletny przewodnik z przykładami

Wiele funkcji Excela jest również dostępnych do wykorzystania w VBA - a Autofiltr metoda jest jedną z takich funkcji.

Jeśli masz zestaw danych i chcesz go filtrować za pomocą kryterium, możesz to łatwo zrobić za pomocą opcji Filtruj na wstążce Dane.

A jeśli chcesz mieć bardziej zaawansowaną wersję, w programie Excel jest również zaawansowany filtr.

Więc po co w ogóle używać autofiltru w VBA?

Jeśli potrzebujesz tylko filtrować dane i zrobić kilka podstawowych rzeczy, polecam trzymać się wbudowanej funkcji filtrowania, którą oferuje interfejs Excela.

Powinieneś używać VBA Autofilter, gdy chcesz filtrować dane w ramach automatyzacji (lub jeśli pomaga to zaoszczędzić czas, przyspieszając filtrowanie danych).

Załóżmy na przykład, że chcesz szybko przefiltrować dane na podstawie listy rozwijanej, a następnie skopiować te przefiltrowane dane do nowego arkusza.

Chociaż można to zrobić za pomocą wbudowanej funkcji filtra wraz z odrobiną kopiowania i wklejania, ręczne wykonanie tego może zająć dużo czasu.

W takim scenariuszu użycie VBA Autofilter może przyspieszyć działanie i zaoszczędzić czas.

Notatka: Omówię ten przykład (filtrowanie danych na podstawie listy rozwijanej i kopiowanie do nowego arkusza) w dalszej części tego samouczka.

Składnia autofiltra Excel VBA

Wyrażenie. Autofiltr ( _Pole_ , _Kryteria1_ , _Operator_ , _Kryteria2_ , _VisibleDropDown_ )

• Wyrażenie: Jest to zakres, w którym chcesz zastosować filtr automatyczny.
• Pole: [Argument opcjonalny] To jest numer kolumny, którą chcesz filtrować. Jest to liczone od lewej w zestawie danych. Jeśli więc chcesz filtrować dane na podstawie drugiej kolumny, ta wartość będzie wynosić 2.
• Kryteria1: [Argument opcjonalny] To są kryteria, na podstawie których chcesz filtrować zestaw danych.
• Operator: [Argument opcjonalny] Jeśli używasz również kryterium 2, możesz połączyć te dwa kryteria na podstawie Operatora. Dostępne są następujące operatory: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Kryteria2: [Argument opcjonalny] To jest drugie kryterium, według którego możesz filtrować zbiór danych.
• Widoczne DropDown: [Argument opcjonalny] Możesz określić, czy ikona listy rozwijanej filtru ma pojawiać się w filtrowanych kolumnach, czy nie. Ten argument może mieć wartość PRAWDA lub FAŁSZ.

Poza wyrażeniem wszystkie inne argumenty są opcjonalne.

Jeśli nie użyjesz żadnego argumentu, po prostu zastosuje lub usunie ikony filtrów do kolumn.

Sub FilterRows() Worksheets("Filtruj dane").Range("A1").AutoFilter End Sub

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

Oznacza to po prostu, że jeśli nie widzisz ikon filtrów w nagłówkach kolumn, zaczniesz je widzieć, gdy powyższy kod zostanie wykonany, a jeśli go widzisz, zostanie usunięty.

Jeśli masz jakieś przefiltrowane dane, usunie filtry i wyświetli pełny zestaw danych.

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

Przykład: Filtrowanie danych na podstawie warunku Tekst

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

Poniższy kod przefiltruje 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, aw nim do A1 (który jest komórką w zbiorze danych).

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

Teraz, jeśli myślisz - dlaczego muszę to robić za pomocą kodu VBA. Można to łatwo zrobić za pomocą wbudowanej funkcji filtra.

Masz rację!

Jeśli to wszystko, co chcesz zrobić, lepiej skorzystaj z wbudowanej funkcji filtrowania.

Ale kiedy przeczytasz pozostały samouczek, zobaczysz, że można to połączyć z dodatkowym kodem, aby stworzyć potężną automatyzację.

Ale zanim ci je pokażę, najpierw omówię kilka przykładów, aby pokazać, co może zrobić cała metoda Autofiltra.

Kliknij tutaj aby pobrać przykładowy plik i postępować dalej.

Przykład: wiele kryteriów (ORAZ/LUB) w tej samej kolumnie

Załóżmy, że mam ten sam zestaw danych i tym razem chcę filtrować wszystkie rekordy, w których elementem jest „Drukarka” lub „Projektor”.

Poniższy kod zrobi to:

Sub FilterRowsOR() Worksheets("Arkusz1").Range("A1").AutoFilter Field:=2, Criteria1:="Drukarka", Operator:=xlOr, Criteria2:="Projektor" End Sub

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

To mówi VBA, aby używał 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 przefiltrować 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 zestaw danych.

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

Na przykład, jeśli chcesz filtrować wszystkie rekordy, w których pozycja to „Drukarka”, a przedstawiciel handlowy to „Oznaczenie”, 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 With End Sub

Przykład: Filtruj 10 najlepszych rekordów za pomocą metody automatycznego filtrowania

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ści):

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

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

Zwróć uwagę, że w tym przykładzie, jeśli chcesz uzyskać 5 najlepszych pozycji, po prostu zmień liczbę w Kryterium1:=”10″ od 10 do 5.

Tak więc dla 5 najlepszych pozycji kod będzie wyglądał następująco:

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

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

Podobnie poniższy kod da ci 10 najniższych pozycji:

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

A jeśli chcesz 5 dolnych pozycji, zmień liczbę w Kryterium1:=”10″ od 10 do 5.

Przykład: Filtruj najlepsze 10 procent za pomocą metody autofiltrowania

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

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

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

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

Przykład: używanie symboli wieloznacznych w autofiltrze

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

Jeśli chcesz przefiltrować wszystkie wiersze, w których nazwa przedmiotu zawiera słowo „Tablica”, 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 symbolu wieloznacznego * (gwiazdka) przed i po słowie „Tablica” (co jest kryterium).

Gwiazdka może reprezentować dowolną liczbę znaków. Więc to odfiltruje każdy element, który zawiera słowo „tablica”.

Przykład: Kopiuj przefiltrowane wiersze 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 przefiltrowane wiersze, dodaje nowy arkusz, a następnie wkleja skopiowane wiersze do nowego arkusza.

Sub CopyFiltedRows() Dim rng As Range Dim ws As Worksheet If Worksheets("Sheet1").AutoFilterMode = False Then MsgBox "Brak filtrowanych wierszy" Exit Sub End If Set rng = Worksheets("Sheet1").AutoFilter.Range Set ws = Worksheets.Add rng.Copy Range("A1") End Sub

Powyższy kod sprawdziłby, czy w Arkuszu1 znajdują się jakieś filtrowane wiersze, czy nie.

Jeśli nie ma przefiltrowanych wierszy, wyświetli się komunikat informujący o tym.

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

Przykład: Filtruj dane na podstawie wartości komórki

Używając Autofiltru w VBA wraz z listą rozwijaną, możesz utworzyć funkcję, w której po wybraniu elementu z listy rozwijanej wszystkie rekordy dla tego elementu są filtrowane.

Coś, jak pokazano poniżej:

Kliknij tutaj aby pobrać przykładowy plik i postępować dalej.

Tego typu konstrukcja może być przydatna, 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$2" Then If Range("B2") = "All" Then Range("A5").AutoFilter Else Range("A5").AutoFilter Field :=2, Kryterium1:=Zakres("B2") Koniec jeśli Koniec jeśli Koniec Sub

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

Ponadto warunek If Then Else jest używany do sprawdzenia, czy użytkownik wybrał „Wszystko” z listy rozwijanej. Jeśli wybrana jest opcja Wszystkie, wyświetlany jest cały zestaw danych.

Ten kod NIE jest umieszczany w module.

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

Oto kroki, aby umieścić ten kod w oknie kodu arkusza roboczego:

1. Otwórz edytor VB (skrót klawiaturowy - ALT + F11).
2. W okienku Eksplorator projektu kliknij dwukrotnie nazwę arkusza roboczego, w którym chcesz skorzystać z tej funkcji filtrowania.
3. W oknie kodu arkusza skopiuj i wklej powyższy kod.
4. Zamknij edytor VB.

Teraz, gdy użyjesz listy rozwijanej, automatycznie przefiltruje dane.

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

Ponadto warunek If Then Else jest używany do sprawdzenia, czy użytkownik wybrał „Wszystko” z listy rozwijanej. W przypadku wybrania opcji Wszystkie wyświetlany jest cały zestaw danych.

Włącz/wyłącz Autofiltr programu Excel za pomocą VBA

Podczas stosowania Autofiltru do zakresu komórek mogą już istnieć niektóre filtry.

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

Sub TurnOFFAutoFilter() Worksheets("Arkusz1").AutoFilterMode = False End Sub

Ten kod sprawdza całe arkusze i usuwa wszystkie zastosowane filtry.

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

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

Powyższy kod sprawdza, czy istnieją już filtry, czy nie.

Jeśli filtry są już zastosowane, usuwa je, w przeciwnym razie nic nie robi.

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

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

Sprawdź, czy Autofiltr jest już zastosowany

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

Sub CheckforFilters() If ActiveSheet.AutoFilterMode = True Then MsgBox "Istnieją już filtry" Else MsgBox "Brak filtrów" End If End Sub

Ten kod wykorzystuje funkcję okna komunikatu, która wyświetla komunikat „Istnieją już filtry”, gdy znajdzie filtry w arkuszu, w przeciwnym razie wyświetla „Brak filtrów”.

Pokaż wszystkie dane

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

Sub ShowAllData() Jeśli ActiveSheet.FilterMode Następnie ActiveSheet.ShowAllData End Sub

Powyższy kod sprawdza, czy FilterMode ma wartość TRUE czy FALSE.

Jeśli to prawda, oznacza to, że zastosowano filtr i używa metody ShowAllData, aby wyświetlić wszystkie dane.

Pamiętaj, że nie powoduje to usunięcia filtrów. Ikony filtrów są nadal dostępne do użycia.

Korzystanie z autofiltru na chronionych arkuszach

Domyślnie, gdy chronisz arkusz, filtry nie działają.

Jeśli masz już filtry, możesz włączyć Autofiltr, aby upewnić się, że działa nawet na chronionych arkuszach.

Aby to zrobić, zaznacz opcję Użyj autofiltru podczas ochrony arkusza.

Chociaż działa to, gdy masz już filtry, jeśli spróbujesz dodać autofiltry za pomocą kodu VBA, nie zadziała.

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

Musisz więc użyć kodu, aby chronić arkusz i upewnić się, że są w nim włączone filtry automatyczne.

Może to być przydatne, gdy utworzyłeś filtr dynamiczny (coś, co omówiłem w przykładzie - „Filtruj dane na podstawie wartości komórki”).

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

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

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

Oto kroki, aby umieścić kod w oknie kodu ThisWorkbook:

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

Gdy tylko otworzysz skoroszyt i włączysz makra, automatycznie uruchomi to makro i ochroni Arkusz1.

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

Ponadto ustawia argument „UserInterfaceOnly” na „True”. Oznacza to, że gdy arkusz jest chroniony, kod makr VBA będzie nadal działał.

Możesz również polubić następujące samouczki VBA:

• Pętle VBA Excela.
• Filtruj komórki za pomocą pogrubionego formatowania czcionki.
• Nagrywanie makro.
• Sortuj dane za pomocą VBA.
• Sortuj karty arkusza roboczego w programie Excel.