Exceli VBA automaatne filter: täielik juhend koos näidetega

VBA -s kasutamiseks on saadaval ka palju Exceli funktsioone - ja Autofilter meetod on üks sellistest funktsioonidest.

Kui teil on andmekogum ja soovite seda kriteeriumi abil filtreerida, saate seda hõlpsalt teha, kasutades lindi Andmed valikut Filter.

Ja kui soovite sellest täpsemat versiooni, on Excelis ka täiustatud filter.

Miks siis isegi kasutada automaatset filtrit VBA -s?

Kui teil on vaja lihtsalt andmeid filtreerida ja teha mõningaid põhilisi asju, soovitaksin jääda sisseehitatud filtrite funktsioonile, mida Exceli liides pakub.

Kui soovite filtreerida andmeid automaatika osana (või kui see aitab teil aega kokku hoida, muutes andmete filtreerimise kiiremaks), peaksite kasutama VBA automaatfiltrit.

Oletame näiteks, et soovite andmed rippmenüüst kiiresti filtreerida ja kopeerida need filtreeritud andmed uuele töölehele.

Kuigi seda saab teha sisseehitatud filtrifunktsiooni ja mõne kopeerimispasta abil, võib selle käsitsi tegemine võtta palju aega.

Sellise stsenaariumi korral võib VBA automaatfiltri kasutamine kiirendada ja säästa aega.

Märge: Ma käsitlen seda näidet (andmete filtreerimisel rippmenüüst ja uuele lehele kopeerimise kohta) hiljem selles juhendis.

Exceli VBA automaatse filtri süntaks

Väljendus. Automaatne filter (_Field_, _Criteria1_, _Operator_, _Criteria2_, _VisibleDropDown_)

• Väljendus: See on vahemik, millele soovite automaatse filtri rakendada.
• Väli: [Valikuline argument] See on veeru number, mida soovite filtreerida. Seda loetakse andmekogumis vasakult. Seega, kui soovite andmeid filtreerida teise veeru alusel, oleks see väärtus 2.
• Kriteeriumid1: [Valikuline argument] See on kriteerium, mille alusel soovite andmestikku filtreerida.
• Operaator: [Valikuline argument] Kui kasutate ka 2. kriteeriumit, saate need kaks kriteeriumi operaatori põhjal kombineerida. Kasutamiseks on saadaval järgmised operaatorid: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Kriteeriumid2: [Valikuline argument] See on teine ​​kriteerium, mille alusel saate andmestikku filtreerida.
• VisibleDropDown: [Valikuline argument] Saate määrata, kas soovite filtri rippmenüü ikooni filtreeritud veergudes kuvada või mitte. See argument võib olla ÕIGE või VÄÄR.

Peale väljenduse on kõik muud argumendid vabatahtlikud.

Kui te ühtegi argumenti ei kasuta, rakendab või eemaldab see lihtsalt veergudele filtriikoonid.

Alamfiltri read () Töölehed ("Andmete filtreerimine"). Vahemik ("A1"). Automaatse filtri lõppalam

Ülaltoodud kood rakendaks veergudele lihtsalt automaatse filtri meetodit (või kui see on juba rakendatud, eemaldab see selle).

See tähendab lihtsalt seda, et kui te ei näe veerupäistes filtriikoone, näete seda ülaltoodud koodi käivitamisel ja kui näete seda, siis see eemaldatakse.

Kui teil on filtreeritud andmeid, eemaldab see filtrid ja kuvab teile täieliku andmestiku.

Nüüd vaatame mõningaid näiteid Exceli VBA automaatse filtri kasutamise kohta, mis muudab selle kasutamise selgeks.

Näide: andmete filtreerimine teksti tingimuse alusel

Oletame, et teil on allpool näidatud andmekogum ja soovite seda veeru „Üksus” alusel filtreerida.

Allolev kood filtreeriks kõik read, kus üksus on „Printer”.

Alamfiltri read () Töölehed ("leht 1"). Vahemik ("A1"). Automaatfiltri väli: = 2, kriteeriumid 1: = "Printer" Lõpu alam

Ülaltoodud kood viitab lehele1 ja selle sees viitab A1 -le (mis on andmestiku lahter).

Pange tähele, et siin oleme kasutanud välja: = 2, kuna üksuse veerg on meie andmestiku teine ​​veerg vasakult.

Kui nüüd mõelda - miks ma pean seda tegema VBA -koodi abil? Seda saab hõlpsasti teha, kasutades sisseehitatud filtrit.

Sul on õigus!

Kui see on kõik, mida soovite teha, kasutage paremini sisseehitatud filtri funktsioone.

Kuid ülejäänud õpetust lugedes näete, et seda saab kombineerida mõne lisakoodiga, et luua võimas automatiseerimine.

Aga enne kui ma teile neid näitan, lubage mul esmalt tuua mõned näited, et näidata teile, mida kõik automaatse filtri meetodid suudavad.

Kliki siia näidisfaili allalaadimiseks ja järgimiseks.

Näide: mitu veergu (JA/VÕI)

Oletame, et mul on sama andmekogum ja seekord tahan filtreerida kõik kirjed, kus üksus on kas „Printer” või „Projektor”.

Allolev kood teeks seda:

AlamfilterRowsOR () töölehed ("Sheet1"). Vahemik ("A1"). Automaatfiltri väli: = 2, Kriteeriumid 1: = "Printer", Operaator: = xlOr, Kriteeriumid 2: = "Projektor" Lõpualam

Pange tähele, et siin olen kasutanud xlOR operaator.

See käsib VBA -l kasutada nii kriteeriume kui ka filtreerida andmed, kui mõni neist kriteeriumidest on täidetud.

Samamoodi saate kasutada ka AND -kriteeriume.

Näiteks kui soovite filtreerida kõiki kirjeid, mille kogus on üle 10, kuid alla 20, võite kasutada järgmist koodi:

AlamfilterRowsAND () töölehed ("Sheet1"). Vahemik ("A1"). Automaatfiltri väli: = 4, Kriteeriumid 1: = "> 10", _ Operaator: = xlAnd, Kriteeriumid 2: = "<20" End Sub

Näide: mitu kriteeriumi erinevate veergudega

Oletame, et teil on järgmine andmekogum.

Autofiltri abil saate filtreerida mitu veergu korraga.

Näiteks kui soovite filtreerida kõik kirjed, kus üksus on „Printer” ja müügiesindaja on „Märgi”, võite kasutada järgmist koodi:

Alamfiltri read () Töölehtedega ("Sheet1"). Vahemik ("A1"). AutoFiltri väli: = 2, Kriteeriumid 1: = "Printer". AutoFiltri väli: = 3, Kriteeriumid 1: = "Märgi" Lõpu lõpuosaga

Näide: 10 parima kirje filtreerimine automaatfiltri meetodil

Oletame, et teil on allpool olev andmekogum.

Allpool on kood, mis annab teile 10 parimat kirjet (koguse veeru põhjal):

AlamfilterRowsTop10 () ActiveSheet.Range ("A1"). Automaatfiltri väli: = 4, Kriteeriumid 1: = "10", Operaator: = xlTop10Items End Sub

Ülaltoodud koodis olen kasutanud ActiveSheet. Soovi korral saate kasutada lehe nime.

Pange tähele, et selles näites, kui soovite saada 5 parimat üksust, muutke lihtsalt numbrit Kriteeriumid 1: = "10" 10 kuni 5.

Nii et viie parima toote puhul oleks kood järgmine:

AlamfilterRowsTop5 () ActiveSheet.Range ("A1"). Automaatfiltri väli: = 4, Kriteeriumid 1: = "5", Operaator: = xlTop10Items End Sub

See võib tunduda kummaline, kuid olenemata sellest, kui palju populaarseid esemeid soovite, jääb operaatori väärtus alati samaks xlTop10Items.

Samamoodi annaks allolev kood teile 10 viimast eset:

AlamfilterRowsBottom10 () ActiveSheet.Range ("A1"). Automaatfiltri väli: = 4, Kriteeriumid 1: = "10", Operaator: = xlBottom10Items End Sub

Ja kui soovite alumist 5 eset, muutke numbrit Kriteeriumid 1: = "10" 10 kuni 5.

Näide: filtreerige top 10 protsenti automaatfiltri meetodil

Oletame, et teil on sama andmekogum (nagu eelmistes näidetes kasutatud).

Allpool on kood, mis annab teile 10 % kirjeid (koguse veeru põhjal):

AlamfilterRowsTop10 () ActiveSheet.Range ("A1"). Automaatfiltri väli: = 4, Kriteeriumid 1: = "10", Operaator: = xlTop10Percent End Sub

Kuna meie andmekogus on 20 kirjet, tagastab see 2 parimat kirjet (mis moodustab 10% kogu kirjest).

Näide: metamärkide kasutamine automaatfiltris

Oletame, et teil on allpool näidatud andmekogum:

Kui soovite filtreerida kõik read, kus üksuse nimi sisaldab sõna „Board”, võite kasutada järgmist koodi:

AlamfilterRowsWildcard () töölehed ("Sheet1"). Vahemik ("A1"). Automaatfiltri väli: = 2, Kriteeriumid 1: = "*Board*" End Sub

Ülaltoodud koodis olen kasutanud metamärki * (tärn) enne ja pärast sõna „Board” (mis on kriteerium).

Tärn võib tähistada suvalist arvu tähemärke. Nii filtreeriks see iga üksuse, milles on sõna „tahvel”.

Näide: kopeerige filtreeritud read uuele lehele

Kui soovite kirjeid mitte ainult kriteeriumide alusel filtreerida, vaid ka filtreeritud ridu kopeerida, võite kasutada allolevat makro.

See kopeerib filtreeritud read, lisab uue töölehe ja kleebib need kopeeritud read uuele lehele.

Sub CopyFilteredRows () Dim rng kui vahemik Dim ws töölehena kui töölehed ("Sheet1"). AutoFilterMode = False then MsgBox "Filtreeritud ridu pole" Välju alamotsast, kui seatud rng = Töölehed ("Sheet1"). AutoFilter.Range Set ws = Töölehed. Lisage rng.Koopiavahemik ("A1") Lõpu alamosa

Ülaltoodud kood kontrollib, kas lehel 1 on filtreeritud ridu või mitte.

Kui filtreeritud ridu pole, kuvatakse selle kohta sõnumikast.

Ja kui filtreeritud ridu on, kopeerib see need, lisab uue töölehe ja kleepib need read sellele lisatud töölehele.

Näide: andmete filtreerimine lahtri väärtuse alusel

Kasutades VBA automaatfiltrit koos ripploendiga, saate luua funktsionaalsuse, kus kohe, kui valite rippmenüüst üksuse, filtreeritakse kõik selle kirjed.

Midagi, nagu allpool näidatud:

Kliki siia näidisfaili allalaadimiseks ja järgimiseks.

Seda tüüpi konstruktsioon võib olla kasulik, kui soovite andmeid kiiresti filtreerida ja seejärel oma töös edasi kasutada.

Allpool on kood, mis seda teeb:

Privaatne alamlehe_muutmine (ByVal -sihtmärk vahemikuna) kui Target.Address = "$ B $ 2" Then If Range ("B2") = "All" Then Range ("A5"). AutoFilter Else Range ("A5"). AutoFilter Field : = 2, Kriteeriumid 1: = Vahemik ("B2") Lõpp Kui lõpp Kui lõpp Alam

See on töölehe sündmuse kood, mis käivitatakse ainult siis, kui töölehel on muudatusi ja sihtlaht on B2 (kus meil on rippmenüü).

Samuti kasutatakse tingimust If If Else, et kontrollida, kas kasutaja on rippmenüüst valinud „All”. Kui valitud on Kõik, kuvatakse kogu andmekogum.

Seda koodi EI paigutata moodulisse.

Selle asemel tuleb see paigutada neid andmeid sisaldava töölehe taustaprogrammi.

Selle koodi sisestamiseks töölehe koodi aknasse toimige järgmiselt.

1. Avage VB redaktor (kiirklahv - ALT + F11).
2. Paanil Project Explorer topeltklõpsake töölehe nime, millel soovite seda filtreerimisfunktsiooni kasutada.
3. Töölehe koodi aknas kopeerige ja kleepige ülaltoodud kood.
4. Sulgege VB redaktor.

Nüüd, kui kasutate ripploendit, filtreerib see andmed automaatselt.

See on töölehe sündmuse kood, mis käivitatakse ainult siis, kui töölehel on muudatusi ja sihtlaht on B2 (kus meil on rippmenüü).

Samuti kasutatakse tingimust If If Else, et kontrollida, kas kasutaja on rippmenüüst valinud „All”. Kui valitud on Kõik, kuvatakse kogu andmekogum.

Lülitage Exceli automaatne filter sisse/välja VBA abil

Kui rakendate automaatfiltrit paljudele lahtritele, võib mõni filter juba olemas olla.

Alloleva koodi abil saate välja lülitada kõik eelnevalt rakendatud automaatsed filtrid.

Sub TurnOFFAutoFilter () töölehed ("Sheet1"). AutoFilterMode = False End Sub

See kood kontrollib kõiki lehti ja eemaldab kõik rakendatud filtrid.

Kui te ei soovi kogu lehe, vaid ainult konkreetse andmestiku filtreid välja lülitada, kasutage allolevat koodi:

Sub TurnOFFAutoFilter () Kui töölehed ("Sheet1"). Vahemik ("A1"). AutoFilter Seejärel töölehed ("Sheet1"). Vahemik ("A1"). AutoFilter End If End Sub

Ülaltoodud kood kontrollib, kas filtrid on juba paigas või mitte.

Kui filtrid on juba rakendatud, eemaldab see need, muidu ei tee see midagi.

Kui soovite automaatse filtri sisse lülitada, kasutage samamoodi järgmist koodi:

Sub TurnOnAutoFilter () Kui mitte töölehed ("Sheet1"). Vahemik ("A4"). AutoFilter Seejärel töölehed ("Sheet1"). Vahemik ("A4"). AutoFilter End If End Sub

Kontrollige, kas automaatne filter on juba rakendatud

Kui teil on mitme andmekogumiga leht ja soovite veenduda, et teate, et filtreid pole juba olemas, võite kasutada allolevat koodi.

Sub CheckforFilters () Kui ActiveSheet.AutoFilterMode = True Siis MsgBox "Filtrid on juba olemas" Muu MsgBox "Filtreid pole" End If End Sub

See kood kasutab sõnumikasti funktsiooni, mis kuvab lehelt filtrite leidmisel teate „Olemas on filtrid”, muidu kuvatakse teade „filtreid pole”.

Kuva kõik andmed

Kui olete andmestikule rakendanud filtreid ja soovite kõiki andmeid kuvada, kasutage allolevat koodi:

Sub ShowAllData () Kui ActiveSheet.FilterMode Siis ActiveSheet.ShowAllData End Sub

Ülaltoodud kood kontrollib, kas FilterMode on TRUE või FALSE.

Kui see on tõsi, tähendab see, et on rakendatud filter ja see kasutab kõigi andmete kuvamiseks ShowAllData meetodit.

Pange tähele, et see ei eemalda filtreid. Filtriikoonid on endiselt saadaval.

Automaatfiltri kasutamine kaitstud lehtedel

Kui kaitsete lehte, ei tööta filtrid vaikimisi.

Kui teil on filtrid juba olemas, saate lubada automaatse filtri, et veenduda selle toimimises isegi kaitstud lehtedel.

Selleks kontrollige lehe kaitsmisel suvandit Kasuta automaatfiltrit.

Kuigi see töötab, kui teil on juba filtrid paigas, ei tööta see juhul, kui proovite lisada automaatfiltreid VBA -koodi abil.

Kuna leht on kaitstud, ei luba see ühtegi makro käivitada ja automaatfiltrit muuta.

Seega peate töölehe kaitsmiseks kasutama koodi ja veenduma, et automaatsed filtrid on selles lubatud.

See võib olla kasulik, kui olete loonud dünaamilise filtri (mida ma näites käsitlesin - „Filtreeri andmed lahtri väärtuse alusel”).

Allpool on kood, mis kaitseb lehte, kuid võimaldab samal ajal kasutada nii filtreid kui ka VBA makrosid.

Privaatne alamtöövihik_Avatud () töölehtedega ("Sheet1") .EnableAutoFilter = True .Protect Password: = "password", Contents: = True, UserInterfaceOnly: = True End With End Sub

See kood tuleb sisestada ThisWorkbooki koodi aknasse.

Siin on sammud koodi sisestamiseks ThisWorkbooki koodi aknasse:

1. Avage VB redaktor (kiirklahv - ALT + F11).
2. Paanil Project Explorer topeltklõpsake objekti ThisWorkbook.
3. Avanevas koodiaknas kopeerige ja kleepige ülaltoodud kood.

Niipea kui avate töövihiku ja lubate makrod, käivitab see makro automaatselt ja kaitseb lehte 1.

Enne selle tegemist määrab see aga „EnableAutoFilter = True”, mis tähendab, et filtrid töötaksid ka kaitstud lehel.

Samuti seab see argumendi „UserInterfaceOnly” väärtuseks „Tõene”. See tähendab, et kuigi tööleht on kaitstud, jätkaks VBA makrode kood tööd.

Samuti võivad teile meeldida järgmised VBA õpetused:

• Exceli VBA silmused.
• Filtreerige lahtrid paksus kirjas.
• Makro salvestamine.
• Sorteeri andmed VBA abil.
• Sortige töölehe vahelehed Excelis.