Ten samouczek wyjaśni, jak korzystać z metody Advanced Filter w VBA
Zaawansowane filtrowanie w Excelu jest bardzo przydatne, gdy mamy do czynienia z dużymi ilościami danych, gdy chcemy jednocześnie zastosować różne filtry. Może być również używany do usuwania duplikatów z Twoich danych. Musisz zapoznać się z tworzeniem filtra zaawansowanego w programie Excel, zanim spróbujesz utworzyć filtr zaawansowany z poziomu VBA.
Rozważ poniższy arkusz.
Na pierwszy rzut oka widać, że istnieją duplikaty, które możesz chcieć usunąć. Rodzaj konta to połączenie oszczędności, pożyczki terminowej i czeku.
Najpierw musisz skonfigurować sekcję kryteriów dla filtra zaawansowanego. Możesz to zrobić na osobnym arkuszu.
Dla ułatwienia nazwałem mój arkusz danych „Baza danych” i arkusz kryteriów „Kryteria”.
Zaawansowana składnia filtra
Expression.AdvancedFilter Action, CriteriaRange, CopyToRange, Unique
- ten Wyrażenie reprezentuje obiekt zakresu - i może być ustawiony jako Zakres (np. Zakres("A1:A50") - lub Zakres może być przypisany do zmiennej i ta zmienna może być używana.
- ten Akcja argument jest wymagany i będzie to xlFilterInPlace lub xlFilterCopy
- ten Zakres kryteriów Argument jest miejscem, z którego otrzymujesz kryteria do filtrowania (nasz arkusz kryteriów powyżej). Jest to opcjonalne, ponieważ nie potrzebujesz kryteriów, jeśli filtrujesz na przykład pod kątem unikalnych wartości.
- ten Kopiuj do zakresu argument to miejsce, w którym zamierzasz umieścić wyniki filtra - możesz filtrować w miejscu lub możesz skopiować wyniki filtra do alternatywnej lokalizacji. Jest to również argument opcjonalny.
- ten Jedyny w swoim rodzaju argument jest również opcjonalny - Prawdziwe jest filtrowanie tylko według unikalnych rekordów, Fałszywe jest filtrowanie według wszystkich rekordów spełniających kryteria - jeśli to pominiesz, domyślnie będzie Fałszywe.
Filtrowanie danych w miejscu
Korzystając z kryteriów przedstawionych powyżej w arkuszu kryteriów - chcemy znaleźć wszystkie rachunki z typem „Oszczędności” i „Bieżące”. Filtrujemy w miejscu.
| 123456789 | Sub UtwórzZaawansowanyFiltr()Dim rngDatabase jako zakresDim rngCriteria As Range„zdefiniuj bazę danych i zakresy kryteriów”Ustaw rngDatabase = Sheets("Baza danych").Range("A1:H50")Ustaw rngCriteria = Arkusze("Kryteria").Range("A1:H3")„przefiltruj bazę danych według kryteriów”rngDatabase.AdvancedFilter xlFilterInPlace, rngCriteriaNapis końcowy |
Kod ukryje wiersze, które nie spełniają kryteriów.
W powyższej procedurze VBA nie uwzględniliśmy argumentów CopyToRange ani Unique.
Resetowanie danych
Zanim uruchomimy kolejny filtr, musimy wyczyścić obecny. To zadziała tylko wtedy, gdy przefiltrujesz swoje dane na miejscu.
| 12345 | Wyczyść filtr pomocniczy()Przy błędzie Wznów Dalej'zresetuj filtr, aby wyświetlić wszystkie daneActiveSheet.ShowAllDataNapis końcowy |
Filtrowanie unikalnych wartości
W poniższej procedurze uwzględniłem argument Unique, ale pominąłem argument CopyToRange. Jeśli pominiesz ten argument, ty ALBO muszę umieścić przecinek jako miejsce na argument
| 123456789 | Podrzędny filtr unikalnych wartości1()Dim rngDatabase jako zakresDim rngCriteria As Range„zdefiniuj bazę danych i zakresy kryteriów”Ustaw rngDatabase = Sheets("Baza danych").Range("A1:H50")Ustaw rngCriteria = Arkusze("Kryteria").Range("A1:H3")„przefiltruj bazę danych według kryteriów”rngDatabase.AdvancedFilter xlFilterInPlace, rngCriteria,,TrueNapis końcowy |
LUB musisz użyć nazwanych argumentów, jak pokazano poniżej.
| 123456789 | Podrzędny filtr wartości unikalnych2()Dim rngDatabase jako zakresDim rngCriteria As Range„zdefiniuj bazę danych i zakresy kryteriów”Ustaw rngDatabase = Sheets("Baza danych").Range("A1:H50")Ustaw rngCriteria = Arkusze("Kryteria").Range("A1:H3")„przefiltruj bazę danych według kryteriów”rngDatabase.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rngCriteria, Unique:=TrueNapis końcowy |
Oba powyższe przykłady kodu uruchomią ten sam filtr, jak pokazano poniżej - dane zawierające tylko unikalne wartości.
Korzystanie z argumentu CopyTo
| 123456789 | Sub CopyToFilter()Dim rngDatabase jako zakresDim rngCriteria As Range„zdefiniuj bazę danych i zakresy kryteriów”Ustaw rngDatabase = Sheets("Baza danych").Range("A1:H50")Ustaw rngCriteria = Arkusze("Kryteria").Range("A1:H3")'skopiuj przefiltrowane dane do alternatywnej lokalizacjirngDatabase.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCriteria, CopyToRange:=Range("N1:U1"), Unique:=TrueNapis końcowy |
Zauważ, że mogliśmy pominąć nazwy argumentów w wierszu kodu Filtra zaawansowanego, ale użycie nazwanych argumentów sprawia, że kod jest łatwiejszy do odczytania i zrozumienia.
Ten wiersz poniżej jest identyczny z wierszem w powyższej procedurze.
| 1 | rngDatabase.AdvancedFilter xlFilterCopy, rngCriteria, zakres("N1:U1"), prawda |
Po uruchomieniu kodu oryginalne dane są nadal wyświetlane z przefiltrowanymi danymi wyświetlanymi w lokalizacji docelowej określonej w procedurze.
Usuwanie duplikatów z danych
Możemy usunąć duplikaty z danych, pomijając argument Criteria i kopiując dane do nowej lokalizacji.
| 1234567 | Sub UsuńDuplikaty()Dim rngDatabase jako zakres'zdefiniuj bazę danychUstaw rngDatabase = Sheets("Baza danych").Range("A1:H50")'filtruj bazę danych do nowego zakresu z unikalnym ustawieniem na truerngDatabase.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("N1:U1"), Unique:=TrueNapis końcowy |
