Samodejni filter Excel VBA: Popoln vodnik s primeri

Na voljo je tudi veliko Excelovih funkcionalnosti za uporabo v VBA - in Avtofilter metoda je ena takih funkcionalnosti.

Če imate nabor podatkov in ga želite filtrirati po merilu, lahko to preprosto storite z možnostjo Filtriraj na traku Podatki.

In če želite njegovo naprednejšo različico, obstaja napreden filter tudi v Excelu.

Zakaj potem sploh uporabljati samodejni filter v VBA?

Če morate samo filtrirati podatke in narediti nekaj osnovnih stvari, priporočam, da se držite vgrajene funkcije filtriranja, ki jo ponuja vmesnik Excel.

Če želite filtrirati podatke kot del avtomatizacije (ali če vam pomaga prihraniti čas s hitrejšim filtriranjem podatkov), uporabite samodejni filter VBA.

Recimo, da želite hitro filtrirati podatke na podlagi spustnega seznama in nato te filtrirane podatke kopirati v nov delovni list.

Čeprav je to mogoče storiti z uporabo vgrajenega filtra skupaj z nekaj copy-paste, lahko za to ročno vzamete veliko časa.

V takem scenariju lahko uporaba samodejnega filtra VBA pospeši in prihrani čas.

Opomba: Ta primer (o filtriranju podatkov na podlagi izbire v spustnem meniju in kopiranju v nov list) bom obravnaval kasneje v tej vadnici.

Sintaksa samodejnega filtriranja Excel VBA

Izraz. Samodejni filter (_Field_, _Criteria1_, _Operator_, _Criteria2_, _VisibleDropDown_)

• Izražanje: To je območje, v katerem želite uporabiti samodejni filter.
• Polje: [Izbirni argument] To je številka stolpca, ki jo želite filtrirati. To se šteje z leve strani v naboru podatkov. Torej, če želite filtrirati podatke na podlagi drugega stolpca, bi bila ta vrednost 2.
• Merila 1: [Izbirni argument] To so kriteriji, na podlagi katerih želite filtrirati nabor podatkov.
• Operater: [Izbirni argument] Če uporabljate tudi merilo 2, lahko ta dva merila združite na podlagi operaterja. Za uporabo so na voljo naslednji operaterji: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Merila 2: [Izbirni argument] To je drugo merilo, po katerem lahko filtrirate nabor podatkov.
• VisibleDropDown: [Izbirni argument] Določite lahko, ali želite, da se spustna ikona filtra prikaže v filtriranih stolpcih ali ne. Ta argument je lahko TRUE ali FALSE.

Poleg izraza so vsi drugi argumenti neobvezni.

Če ne uporabite nobenega argumenta, bi preprosto uporabil ali odstranil ikone filtra za stolpce.

Sub FilterRows () Delovni listi ("Filter Data"). Obseg ("A1"). Auto Filter End Sub

Zgornja koda bi preprosto uporabila metodo samodejnega filtriranja za stolpce (ali če je že uporabljena, jo bo odstranila).

To preprosto pomeni, da če v glavah stolpcev ne vidite ikon filtra, jih boste videli, ko se izvede zgornja koda, in če jo vidite, bo odstranjena.

Če imate filtrirane podatke, bodo filtri odstranjeni in prikazan bo celoten nabor podatkov.

Zdaj pa poglejmo nekaj primerov uporabe samodejnega filtra Excel VBA, s katerimi bo njegova uporaba jasna.

Primer: Filtriranje podatkov na podlagi besedilnega pogoja

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in ga želite filtrirati na podlagi stolpca »Postavka«.

Spodnja koda bi filtrirala vse vrstice, kjer je postavka "tiskalnik".

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

Zgornja koda se nanaša na Sheet1 in znotraj nje se nanaša na A1 (ki je celica v naboru podatkov).

Upoštevajte, da smo tukaj uporabili polje: = 2, saj je stolpec postavke drugi stolpec v našem naboru podatkov na levi.

Zdaj, če razmišljate - zakaj moram to narediti s kodo VBA. To lahko preprosto storite z uporabo vgrajenega filtra.

Prav imaš!

Če je to vse, kar želite storiti, raje uporabite funkcijo vgrajenega filtra.

Toda ko boste prebrali preostalo vadnico, boste videli, da je to mogoče kombinirati z dodatno kodo za ustvarjanje zmogljive avtomatizacije.

Preden pa vam jih pokažem, naj najprej opišem nekaj primerov, ki vam pokažejo, kaj vse lahko naredi metoda samodejnega filtriranja.

Klikni tukaj Če želite prenesti primer datoteke in slediti.

Primer: Več meril (AND/OR) v istem stolpcu

Recimo, da imam isti nabor podatkov, tokrat pa želim filtrirati vse zapise, kjer je postavka „tiskalnik“ ali „projektor“.

Spodnja koda bi to naredila:

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

Upoštevajte, da sem tukaj uporabil xlOR operater.

To pove VBA, naj uporabi oba merila in filtrira podatke, če je katero od obeh meril izpolnjeno.

Podobno lahko uporabite tudi merila AND.

Na primer, če želite filtrirati vse zapise, kjer je količina večja od 10, vendar manjša od 20, lahko uporabite spodnjo kodo:

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

Primer: Več meril z različnimi stolpci

Recimo, da imate naslednji nabor podatkov.

S samodejnim filtriranjem lahko filtrirate več stolpcev hkrati.

Na primer, če želite filtrirati vse zapise, kjer je postavka „tiskalnik“, prodajni predstavnik pa „označi“, lahko uporabite spodnjo kodo:

Sub FilterRows () z delovnimi listi ("Sheet1"). Range ("A1") .AutoFilter field: = 2, Criteria1: = "Printer" .AutoFilter field: = 3, Criteria1: = "Mark" End with End Sub

Primer: Filtrirajte 10 najboljših zapisov z uporabo metode samodejnega filtriranja

Recimo, da imate spodnji nabor podatkov.

Spodaj je koda, ki vam bo dala 10 najboljših zapisov (glede na stolpec količine):

Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). Polje samodejnega filtriranja: = 4, Merila1: = "10", Operater: = xlTop10Inde End Sub

V zgornji kodi sem uporabil ActiveSheet. Če želite, lahko uporabite ime lista.

Upoštevajte, da v tem primeru, če želite dobiti prvih 5 elementov, preprosto spremenite številko Kriterij1: = ”10 ″ od 10 do 5.

Tako bi bila za prvih 5 elementov koda:

Sub FilterRowsTop5 () ActiveSheet.Range ("A1"). Polje samodejnega filtriranja: = 4, Merila1: = "5", Operater: = xlTop10Items End Sub

Morda bo videti čudno, vendar ne glede na to, koliko vrhunskih elementov želite, vrednost Operator vedno ostane xlTop10Items.

Podobno bi vam spodnja koda dala 10 spodnjih elementov:

Sub FilterRowsBottom10 () ActiveSheet.Range ("A1"). Polje samodejnega filtriranja: = 4, Merila1: = "10", Operator: = xlBottom10Inde End Sub

Če želite spodnjih 5 elementov, spremenite številko Kriterij1: = ”10 ″ od 10 do 5.

Primer: 10 odstotkov filtriranja z uporabo metode samodejnega filtriranja

Recimo, da imate isti nabor podatkov (kot je bil uporabljen v prejšnjih primerih).

Spodaj je koda, ki vam bo dala prvih 10 odstotkov zapisov (glede na stolpec količine):

Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). Polje samodejnega filtriranja: = 4, Merila1: = "10", Operater: = xlTop10Percent End Sub

V našem naboru podatkov, ker imamo 20 zapisov, bo vrnil prva dva zapisa (kar je 10% vseh zapisov).

Primer: uporaba nadomestnih znakov v samodejnem filtriranju

Recimo, da imate nabor podatkov, kot je prikazano spodaj:

Če želite filtrirati vse vrstice, v katerih ime elementa vsebuje besedo 'Board', lahko uporabite spodnjo kodo:

Sub FilterRowsWildcard () Delovni listi ("List1"). Obseg ("A1"). Polje samodejnega filtriranja: = 2, Merila1: = "*Board*" End Sub

V zgornji kodi sem uporabil nadomestni znak * (zvezdica) pred in za besedo 'Board' (kar je merilo).

Zvezdica lahko predstavlja poljubno število znakov. Tako bi filtrirali vse elemente, ki vsebujejo besedo "deska".

Primer: Kopirajte filtrirane vrstice v nov list

Če želite ne samo filtrirati zapisov na podlagi meril, ampak tudi kopirati filtrirane vrstice, lahko uporabite spodnji makro.

Kopira filtrirane vrstice, doda nov delovni list in nato te kopirane vrstice prilepi v nov list.

Sub CopyFilteredRows () Zatemni rng kot obseg Zatemni kot delovni list Če delovni listi ("List1"). AutoFilterMode = False Potem MsgBox "Ni filtriranih vrstic" Zapusti podkončnico, če je nastavljeno rng = Delovni listi ("List1"). AutoFilter.Range Set ws = Delovni listi.Dodaj rng.Kopiraj obseg ("A1") Končni podv

Zgornja koda bi preverila, ali so v Sheet1 filtrirane vrstice ali ne.

Če ni filtriranih vrstic, bo prikazano polje s sporočilom.

Če obstajajo filtrirane vrstice, jih bo kopiral, vstavil nov delovni list in te vrstice prilepil na nov vstavljeni delovni list.

Primer: Filtrirajte podatke na podlagi vrednosti celice

Z uporabo samodejnega filtriranja v VBA skupaj s spustnim seznamom lahko ustvarite funkcijo, kjer se takoj, ko izberete element s spustnega menija, filtrirajo vsi zapisi za ta element.

Nekaj, kot je prikazano spodaj:

Klikni tukaj Če želite prenesti primer datoteke in slediti.

Ta vrsta konstrukcije je lahko uporabna, če želite hitro filtrirati podatke in jih nato uporabiti pri svojem delu.

Spodaj je koda, ki bo to naredila:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Address = "$ B $ 2" Then If Range ("B2") = "All" Potem Range ("A5"). AutoFilter Else Range ("A5"). : = 2, Merila1: = Obseg ("B2") Konec Če Konec Če Konec Pod

To je koda dogodka na delovnem listu, ki se izvede le, če pride do spremembe na delovnem listu in je ciljna celica B2 (kjer imamo spustni meni).

Prav tako se za preverjanje, ali je uporabnik na spustnem seznamu izbral »Vse«, uporabi pogoj Če je potem. Če izberete Vse, se prikaže celoten nabor podatkov.

Ta koda NI vstavljena v modul.

Namesto tega ga je treba postaviti v ozadje delovnega lista, ki vsebuje te podatke.

Tukaj je nekaj korakov za vnos te kode v okno kode delovnega lista:

1. Odprite urejevalnik VB (bližnjica na tipkovnici - ALT + F11).
2. V podoknu Raziskovalec projektov dvokliknite ime delovnega lista, v katerem želite to funkcijo filtriranja.
3. V oknu kode delovnega lista kopirajte in prilepite zgornjo kodo.
4. Zaprite urejevalnik VB.

Zdaj, ko uporabite spustni seznam, bo samodejno filtriral podatke.

To je koda dogodka na delovnem listu, ki se izvede le, če pride do spremembe na delovnem listu in je ciljna celica B2 (kjer imamo spustni meni).

Prav tako se za preverjanje, ali je uporabnik na spustnem seznamu izbral »Vse«, uporabi pogoj Če je potem. Če izberete Vse, se prikaže celoten nabor podatkov.

Z VBA vklopite/izklopite Excel AutoFilter

Pri uporabi samodejnega filtra za vrsto celic je morda že nameščenih nekaj filtrov.

S spodnjo kodo lahko izklopite vse vnaprej uporabljene samodejne filtre:

Sub TurnOFFAutoFilter () Delovni listi ("Sheet1"). AutoFilterMode = False End Sub

Ta koda preveri celotne liste in odstrani vse uporabljene filtre.

Če ne želite izklopiti filtrov iz celotnega lista, ampak le iz določenega nabora podatkov, uporabite spodnjo kodo:

Sub TurnOFFAutoFilter () Če delovni listi ("Sheet1"). Obseg ("A1"). AutoFilter Nato delovni listi ("Sheet1"). Obseg ("A1"). AutoFilter End If End Sub

Zgornja koda preveri, ali že obstajajo filtri ali ne.

Če so filtri že uporabljeni, jih odstrani, drugače ne naredi ničesar.

Podobno, če želite vklopiti samodejni filter, uporabite spodnjo kodo:

Sub TurnOnAutoFilter () Če ni delovnih listov ("Sheet1"). Obseg ("A4"). AutoFilter Nato delovni listi ("Sheet1"). Range ("A4"). AutoFilter End If End Sub

Preverite, ali je samodejni filter že uporabljen

Če imate list z več nabori podatkov in se želite prepričati, da filtri niso že vzpostavljeni, lahko uporabite spodnjo kodo.

Sub CheckforFilters () Če je ActiveSheet.AutoFilterMode = True Potem je MsgBox "Obstajajo filtri že nameščeni" Sicer MsgBox "Ni filtrov" End If End Sub

Ta koda uporablja funkcijo polja s sporočilom, ki prikaže sporočilo "Obstajajo filtri", ko najde filtre na listu, sicer pa prikaže "Ni filtrov".

Pokaži vse podatke

Če imate za nabor podatkov uporabljene filtre in želite prikazati vse podatke, uporabite spodnjo kodo:

Sub ShowAllData () Če ActiveSheet.FilterMode Nato ActiveSheet.ShowAllData End Sub

Zgornja koda preveri, ali je FilterMode TRUE ali FALSE.

Če drži, pomeni, da je bil uporabljen filter in za prikaz vseh podatkov uporablja metodo ShowAllData.

Upoštevajte, da s tem ne odstranite filtrov. Ikone filtrov so še vedno na voljo za uporabo.

Uporaba samodejnega filtriranja na zaščitenih listih

Ko zaščitite list, filtri privzeto ne delujejo.

Če imate že nameščene filtre, lahko omogočite samodejni filter, da se prepričate, da deluje tudi na zaščitenih listih.

Če želite to narediti, potrdite možnost Uporabi samodejni filter in hkrati zaščitite list.

Čeprav to deluje, če že imate nameščene filtre, v primeru, da poskušate dodati samodejne filtre s kodo VBA, ne bo delovalo.

Ker je list zaščiten, ne dovoljuje izvajanja nobenega makra in spreminjanja samodejnega filtra.

Zato morate za zaščito delovnega lista uporabiti kodo in se prepričati, da so v njem omogočeni samodejni filtri.

To je lahko koristno, če ste ustvarili dinamični filter (nekaj, kar sem obravnaval v primeru - "Filtriraj podatke na podlagi vrednosti celice").

Spodaj je koda, ki bo zaščitila list, hkrati pa vam bo omogočila uporabo filtrov in makrov VBA.

Private Sub Workbook_Open () z delovnimi listi ("Sheet1") .EnableAutoFilter = True. Geslo za zaščito: = "password", Vsebina: = True, UserInterfaceOnly: = True End With End Sub

To kodo je treba vstaviti v okno s kodo tega delovnega zvezka.

Tu so koraki za vnos kode v okno s kodo tega delovnega zvezka:

1. Odprite urejevalnik VB (bližnjica na tipkovnici - ALT + F11).
2. V podoknu Raziskovalec projektov dvokliknite predmet ThisWorkbook.
3. V oknu s kodo, ki se odpre, kopirajte in prilepite zgornjo kodo.

Takoj, ko odprete delovni zvezek in omogočite makre, se bo makro samodejno zagnal in zaščitil Sheet1.

Pred tem pa bo določil „EnableAutoFilter = True“, kar pomeni, da bi filtri delovali tudi na zaščitenem listu.

Prav tako nastavi argument »UserInterfaceOnly« na »True«. To pomeni, da bi koda makrov VBA delovala, medtem ko je delovni list zaščiten.

Morda vam bodo všeč tudi naslednji VBA vodiči:

• Zanke Excel VBA.
• Filtrirajte celice s krepkim oblikovanjem pisave.
• Snemanje makra.
• Razvrstite podatke z uporabo VBA.
• Razvrsti zavihke delovnega lista v Excelu.