Excel VBA -autosuodatin: täydellinen opas esimerkkeineen

Monet Excel -toiminnot ovat myös käytettävissä käytettäväksi VBA: ssa - ja Automaattinen suodatin menetelmä on yksi tällainen toiminto.

Jos sinulla on tietojoukko ja haluat suodattaa sen kriteerin avulla, voit tehdä sen helposti käyttämällä Data -valintanauhan Suodata -vaihtoehtoa.

Ja jos haluat kehittyneemmän version siitä, myös Excelissä on edistynyt suodatin.

Miksi sitten edes käyttää automaattista suodatinta VBA: ssa?

Jos sinun tarvitsee vain suodattaa tietoja ja tehdä joitain perusasioita, suosittelen pitämään kiinni Excel -käyttöliittymän tarjoamasta sisäänrakennetusta suodatustoiminnosta.

Käytä VBA -automaattisuodatinta, kun haluat suodattaa tiedot osana automaatiota (tai jos se auttaa säästämään aikaa nopeuttamalla tietojen suodattamista).

Oletetaan esimerkiksi, että haluat suodattaa tiedot nopeasti avattavan valinnan perusteella ja kopioida sitten suodatetut tiedot uuteen laskentataulukkoon.

Vaikka tämä voidaan tehdä käyttämällä sisäänrakennettua suodatintoimintoa ja joitakin copy-paste-toimintoja, manuaalinen tekeminen voi viedä paljon aikaa.

Tällaisessa tilanteessa VBA -automaattisuodattimen käyttö voi nopeuttaa asioita ja säästää aikaa.

Merkintä: Käsittelen tätä esimerkkiä (tietojen suodattamisesta avattavan valinnan perusteella ja kopioimisesta uudelle arkille) myöhemmin tässä opetusohjelmassa.

Excel VBA -autosuodattimen syntaksi

Ilmaisu. Automaattinen suodatin (_Field_, _Criteria1_, _Operator_, _Criteria2_, _VisibleDropDown_)

• Ilmaisu: Tämä on alue, johon haluat käyttää automaattista suodatinta.
• Ala: [Valinnainen argumentti] Tämä on sarakkeen numero, jonka haluat suodattaa. Tämä lasketaan datajoukon vasemmalta puolelta. Joten jos haluat suodattaa tietoja toisen sarakkeen perusteella, tämä arvo olisi 2.
• Kriteerit 1: [Valinnainen argumentti] Tämä on kriteeri, jonka perusteella haluat suodattaa tietojoukon.
• Operaattori: [Valinnainen argumentti] Jos käytät myös ehtoja 2, voit yhdistää nämä kaksi ehtoa operaattorin perusteella. Seuraavat operaattorit ovat käytettävissä: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Kriteerit 2: [Valinnainen argumentti] Tämä on toinen kriteeri, jonka perusteella voit suodattaa tietojoukon.
• VisibleDropDown: [Valinnainen argumentti] Voit määrittää, haluatko avattavan suodattimen kuvakkeen näkyvän suodatetuissa sarakkeissa. Tämä argumentti voi olla TOSI tai EPÄTOSI.

Expressionin lisäksi kaikki muut argumentit ovat valinnaisia.

Jos et käytä mitään argumenttia, se yksinkertaisesti käyttää tai poistaa suodatinkuvakkeet sarakkeisiin.

Sub FilterRows () Laskentataulukot ("Suodata tiedot"). Alue ("A1"). AutoFilter End Sub

Yllä oleva koodi soveltaa yksinkertaisesti automaattisen suodattimen menetelmää sarakkeisiin (tai jos se on jo käytössä, se poistaa sen).

Tämä tarkoittaa yksinkertaisesti sitä, että jos et näe suodatinkuvakkeita sarakeotsikoissa, alat nähdä sen, kun tämä yllä oleva koodi suoritetaan, ja jos näet sen, se poistetaan.

Jos sinulla on suodatettuja tietoja, se poistaa suodattimet ja näyttää sinulle koko tietojoukon.

Katsotaan nyt joitain esimerkkejä Excel VBA -automaattisuodattimen käytöstä, mikä tekee sen käytöstä selvää.

Esimerkki: Tietojen suodattaminen tekstiehdon perusteella

Oletetaan, että sinulla on alla näkyvä tietojoukko ja haluat suodattaa sen "Tuote" -sarakkeen perusteella.

Alla oleva koodi suodattaa kaikki rivit, joissa kohde on "Tulostin".

Sub FilterRows () Työsivut ("Sheet1"). Alue ("A1"). AutoFilter Field: = 2, Criteria1: = "Printer" End Sub

Yllä oleva koodi viittaa taulukkoon 1 ja sen sisällä se viittaa A1: ään (joka on tietojoukon solu).

Huomaa, että tässä olemme käyttäneet kenttää: = 2, koska kohdesarake on tietojoukkomme toinen sarake vasemmalta.

Jos nyt ajattelet - miksi minun on tehtävä tämä käyttämällä VBA -koodia. Tämä voidaan tehdä helposti käyttämällä sisäänrakennettua suodatintoimintoa.

Olet oikeassa!

Jos tämä on kaikki mitä haluat tehdä, käytä paremmin sisäänrakennettua suodatintoimintoa.

Mutta kun luet jäljellä olevan opetusohjelman, huomaat, että tämä voidaan yhdistää johonkin ylimääräiseen koodiin tehokkaan automaation luomiseksi.

Mutta ennen kuin näytän sinulle nämä, haluan ensin käsitellä muutamia esimerkkejä, jotka osoittavat, mitä kaikki automaattisen suodattimen menetelmät voivat tehdä.

Klikkaa tästä ladataksesi esimerkkitiedoston ja seuraa sitä.

Esimerkki: Useita ehtoja (JA/TAI) samassa sarakkeessa

Oletetaan, että minulla on sama tietojoukko, ja tällä kertaa haluan suodattaa kaikki tietueet, joissa kohde on joko Tulostin tai Projektori.

Alla oleva koodi toimisi näin:

Sub FilterRowsOR () laskentataulukot ("Sheet1"). Alue ("A1"). Automaattisuodatinkenttä: = 2, Criteria1: = "Printer", Operator: = xlOr, Criteria2: = "Projector" End Sub

Huomaa, että tässä olen käyttänyt xlOR operaattori.

Tämä kehottaa VBA: ta käyttämään sekä ehtoja että suodattamaan tiedot, jos jokin kahdesta kriteeristä täyttyy.

Voit myös käyttää AND -ehtoja.

Jos esimerkiksi haluat suodattaa kaikki tietueet, joiden määrä on yli 10 mutta alle 20, voit käyttää alla olevaa koodia:

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

Esimerkki: Useita ehtoja eri sarakkeilla

Oletetaan, että sinulla on seuraava tietojoukko.

Automaattisuodattimen avulla voit suodattaa useita sarakkeita samanaikaisesti.

Jos esimerkiksi haluat suodattaa kaikki tietueet, joissa kohde on "Tulostin" ja myyntiedustaja on "Merkki", voit käyttää alla olevaa koodia:

Alisuodatinsarjat () laskentataulukoilla ("Sheet1"). Alue ("A1") .Automaattisuodatin -kenttä: = 2, kriteerit1: = "tulostin".

Esimerkki: 10 suosituimman tietueen suodattaminen automaattisen suodatusmenetelmän avulla

Oletetaan, että sinulla on alla oleva tietojoukko.

Alla on koodi, joka antaa sinulle kymmenen parasta tietuetta (määräsarakkeen perusteella):

Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). Automaattisuodatinkenttä: = 4, kriteerit 1: = "10", operaattori: = xlTop10Items End Sub

Yllä olevassa koodissa olen käyttänyt ActiveSheetia. Voit käyttää arkin nimeä, jos haluat.

Huomaa, että tässä esimerkissä, jos haluat saada 5 parasta kohdetta, muuta vain numero Kriteerit 1: = ”10” 10 - 5.

Joten 5 parhaan tuotteen koodi olisi:

Sub FilterRowsTop5 () ActiveSheet.Range ("A1"). Automaattisuodatinkenttä: = 4, kriteerit1: = "5", operaattori: = xlTop10Items End Sub

Se voi näyttää oudolta, mutta riippumatta siitä, kuinka monta suosituinta kohdetta haluat, Operaattorin arvo pysyy aina xlTop10Items.

Samoin alla oleva koodi antaisi sinulle 10 alinta kohdetta:

AlisuodatinRowsBottom10 () ActiveSheet.Range ("A1"). Automaattisuodatinkenttä: = 4, Kriteerit1: = "10", Operaattori: = xlBottom10Items End Sub

Ja jos haluat alimman 5 kohteen, muuta numero Kriteerit 1: = ”10” 10 - 5.

Esimerkki: Suodata Top 10 % käyttämällä automaattista suodatinmenetelmää

Oletetaan, että sinulla on sama tietojoukko (kuten edellisissä esimerkeissä).

Alla on koodi, joka antaa sinulle kymmenen parhaan tietueen (määräsarakkeen perusteella):

AlisuodatinRowsTop10 () ActiveSheet.Range ("A1"). Automaattisuodatinkenttä: = 4, Kriteerit1: = "10", Operaattori: = xlTop10Percent End Sub

Koska aineistossamme on 20 tietuetta, se palauttaa 2 parasta tietuetta (mikä on 10% kaikista tietueista).

Esimerkki: Yleismerkkien käyttäminen automaattisessa suodattimessa

Oletetaan, että sinulla on alla näkyvä tietojoukko:

Jos haluat suodattaa kaikki rivit, joissa kohteen nimi sisältää sanan "Board", voit käyttää alla olevaa koodia:

Sub FilterRowsWildcard () laskentataulukot ("Sheet1"). Alue ("A1"). Automaattisuodatinkenttä: = 2, Criteria1: = "*Board*" End Sub

Yllä olevassa koodissa olen käyttänyt jokerimerkkiä * (tähti) ennen ja jälkeen sanan ”Board” (mikä on kriteeri).

Tähti voi edustaa mitä tahansa määrää merkkejä. Tämä suodattaisi kaikki kohteet, joissa on sana "board".

Esimerkki: Kopioi suodatetut rivit uuteen taulukkoon

Jos haluat suodattaa tietueet ehtojen perusteella ja myös kopioida suodatetut rivit, voit käyttää alla olevaa makroa.

Se kopioi suodatetut rivit, lisää uuden laskentataulukon ja liittää sitten kopioidut rivit uuteen taulukkoon.

Sub CopyFilteredRows () Dim rng alueena Dim ws laskentataulukkona, jos laskentataulukot ("Sheet1"). AutoFilterMode = False Sitten MsgBox "Suodatettuja rivejä ei ole" Exit Sub End if Set rng = Worksheets ("Sheet1"). AutoFilter.Range Set ws = Laskentataulukot.Lisää rng.Kopioalue ("A1") Loppuosa

Yllä oleva koodi tarkistaisi, onko taulukossa 1 suodatettuja rivejä.

Jos suodatettuja rivejä ei ole, se näyttää viestiruudun.

Ja jos on suodatettuja rivejä, se kopioi ne, lisää uuden laskentataulukon ja liittää nämä rivit juuri lisätylle laskentataulukolle.

Esimerkki: Suodata tiedot solun arvon perusteella

Käyttämällä VBA: n automaattista suodatinta ja avattavaa luetteloa voit luoda toiminnon, jossa heti kun valitset kohteen avattavasta valikosta, kaikki kyseisen kohteen tietueet suodatetaan.

Jotain alla olevan kuvan mukaisesti:

Klikkaa tästä ladataksesi esimerkkitiedoston ja seuraa sitä.

Tämäntyyppinen rakenne voi olla hyödyllinen, kun haluat suodattaa tiedot nopeasti ja käyttää niitä edelleen työssäsi.

Alla on koodi, joka tekee tämän:

Yksityinen alityöarkin_muutos (ByVal -tavoite alueena) Jos Target.Address = "$ B $ 2" Then If Range ("B2") = "All" Then Range ("A5"). AutoFilter Else Range ("A5"). AutoFilter Field : = 2, Kriteerit1: = Alue ("B2") Lopeta Jos loppu Jos loppu Al

Tämä on laskentataulukon tapahtumakoodi, joka suoritetaan vain, kun laskentataulukossa tapahtuu muutos ja kohdesolu on B2 (jossa meillä on avattava valikko).

Lisäksi If If Else -ehtoa käytetään tarkistamaan, onko käyttäjä valinnut avattavasta valikosta Kaikki. Jos Kaikki on valittuna, koko tietojoukko näytetään.

Tätä koodia EI ole sijoitettu moduuliin.

Sen sijaan se on sijoitettava laskentataulukon taustaosaan, jossa on nämä tiedot.

Voit laittaa tämän koodin laskentataulukon koodi -ikkunaan seuraavasti:

1. Avaa VB Editor (pikanäppäin - ALT + F11).
2. Kaksoisnapsauta Project Explorer -ruudussa laskentataulukon nimeä, johon haluat tämän suodatustoiminnon.
3. Kopioi ja liitä yllä oleva koodi laskentataulukon koodi -ikkunassa.
4. Sulje VB Editor.

Kun käytät avattavaa luetteloa, se suodattaa tiedot automaattisesti.

Tämä on laskentataulukon tapahtumakoodi, joka suoritetaan vain, kun laskentataulukossa tapahtuu muutoksia ja kohdesolu on B2 (jossa meillä on avattava valikko).

Lisäksi If If Else -ehtoa käytetään tarkistamaan, onko käyttäjä valinnut avattavasta valikosta Kaikki. Jos Kaikki on valittuna, koko tietojoukko näytetään.

Kytke Excelin automaattinen suodatin päälle/pois VBA: n avulla

Kun käytät automaattista suodatinta useille soluille, suodattimia saattaa olla jo paikallaan.

Voit käyttää alla olevaa koodia sammuttaaksesi kaikki esiasennetut automaattiset suodattimet:

Sub TurnOFFAutoFilter () -työarkit ("Sheet1"). AutoFilterMode = False End Sub

Tämä koodi tarkistaa koko taulukot ja poistaa kaikki suodattimet.

Jos et halua poistaa suodattimia käytöstä koko taulukosta vaan vain tietystä tietojoukosta, käytä alla olevaa koodia:

Sub TurnOFFAutoFilter () Jos laskentataulukot ("Sheet1"). Alue ("A1"). AutoFilter then Worksheets ("Sheet1"). Range ("A1"). AutoFilter End If End Sub

Yllä oleva koodi tarkistaa, onko suodattimia jo olemassa vai ei.

Jos suodattimet ovat jo käytössä, se poistaa ne, muuten se ei tee mitään.

Jos haluat myös ottaa automaattisen suodattimen käyttöön, käytä alla olevaa koodia:

Sub TurnOnAutoFilter () Jos ei laskentataulukoita ("Sheet1"). Alue ("A4"). AutoFilter then Worksheets ("Sheet1"). Range ("A4"). AutoFilter End If End Sub

Tarkista, onko automaattinen suodatin jo käytössä

Jos sinulla on taulukko, jossa on useita tietojoukkoja ja haluat varmistaa, että tiedät, ettei suodattimia ole jo käytössä, voit käyttää alla olevaa koodia.

Sub CheckforFilters () Jos ActiveSheet.AutoFilterMode = True Sitten MsgBox "Suodattimia on jo olemassa" Muu MsgBox "Suodattimia ei ole" End If End Sub

Tämä koodi käyttää viestilaatikkotoimintoa, joka näyttää sanoman "Suodattimia on jo käytössä", kun se löytää suodattimia taulukosta, muuten se näyttää "Suodattimia ei ole".

Näytä kaikki tiedot

Jos olet käyttänyt suodattimia tietojoukkoon ja haluat näyttää kaikki tiedot, käytä alla olevaa koodia:

Sub ShowAllData () Jos ActiveSheet.FilterMode Sitten ActiveSheet.ShowAllData End Sub

Yllä oleva koodi tarkistaa, onko FilterMode TRUE vai FALSE.

Jos se on totta, se tarkoittaa, että suodatin on otettu käyttöön ja se käyttää ShowAllData -menetelmää kaikkien tietojen näyttämiseen.

Huomaa, että tämä ei poista suodattimia. Suodatinkuvakkeet ovat edelleen käytettävissä.

Automaattisen suodattimen käyttäminen suojatuilla arkeilla

Oletusarvoisesti suodattimet eivät toimi, kun suojaat arkkia.

Jos sinulla on jo suodattimia, voit ottaa automaattisen suodattimen käyttöön varmistaaksesi, että se toimii myös suojatuilla arkeilla.

Voit tehdä tämän valitsemalla Käytä automaattista suodatinta -vaihtoehdon samalla, kun suojaat arkkia.

Vaikka tämä toimii, kun sinulla on jo suodattimia, se ei toimi, jos yrität lisätä automaattisia suodattimia VBA -koodin avulla.

Koska arkki on suojattu, se ei salli makrojen suorittamista ja muutoksia automaattiseen suodattimeen.

Joten sinun on suojattava laskentataulukko koodilla ja varmistettava, että automaattiset suodattimet ovat käytössä.

Tästä voi olla hyötyä, kun olet luonut dynaamisen suodattimen (mitä käsitin esimerkissä - "Suodata tiedot solun arvon perusteella").

Alla on koodi, joka suojaa taulukon, mutta samalla voit käyttää siinä suodattimia ja VBA -makroja.

Yksityinen alityökirja_Open () laskentataulukoilla ("Sheet1") .EnableAutoFilter = True .Protect Password: = "password", Contents: = True, UserInterfaceOnly: = True End With End Sub

Tämä koodi on sijoitettava ThisWorkbook -koodi -ikkunaan.

Voit laittaa koodin ThisWorkbook -koodi -ikkunaan seuraavasti:

1. Avaa VB Editor (pikanäppäin - ALT + F11).
2. Kaksoisnapsauta Project Explorer -ruudussa ThisWorkbook-objektia.
3. Kopioi ja liitä avautuvaan koodi -ikkunaan yllä oleva koodi.

Heti kun avaat työkirjan ja otat makroja käyttöön, se suorittaa makron automaattisesti ja suojaa taulukkoa 1.

Ennen kuin teet sen, se määrittää kuitenkin "EnableAutoFilter = True", mikä tarkoittaa, että suodattimet toimivat myös suojatussa taulukossa.

Lisäksi se asettaa UserInterfaceOnly -argumentiksi arvon True. Tämä tarkoittaa, että vaikka laskentataulukko on suojattu, VBA -makrojen koodi toimii edelleen.

Saatat pitää myös seuraavista VBA -opetusohjelmista:

• Excel VBA -silmukat.
• Suodata solut lihavoidulla fontilla.
• Makron tallentaminen.
• Lajittele tiedot VBA: n avulla.
• Lajittele laskentataulukon välilehdet Excelissä.