Excel VBA Autofilter: A Complete Guide with Examples

Paljon Excel-toiminnallisuuksia on käytettävissä myös VBA:ssa – ja Autofilter-menetelmä on yksi tällainen toiminnallisuus.

Jos sinulla on tietokokonaisuus ja haluat suodattaa sen jonkin kriteerin avulla, se onnistuu helposti Data-ribbon Filter-vaihtoehdolla.

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

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

Jos sinun tarvitsee vain suodattaa tietoja ja tehdä joitain perusjuttuja, suosittelen pitäytymään Excelin käyttöliittymän tarjoamassa sisäänrakennetussa suodatintoiminnallisuudessa.

Sinun kannattaa käyttää VBA:n automaattista suodatinta silloin, kun haluat suodattaa tietoja osana automatisointia (tai jos se auttaa säästämään aikaa nopeuttamalla tietojen suodattamista).

Esitetään esimerkiksi, että haluat nopeasti suodattaa tiedot pudotusvalinnan perusteella ja sitten kopioida nämä suodatetut tiedot uuteen laskentataulukkoon.

Vaikka tämä voidaan tehdä käyttämällä sisäänrakennettua suodatintoimintoa yhdessä jonkin verran copy-paste-toiminnon kanssa, manuaalinen tekeminen voi viedä paljon aikaa.

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

Huomautus: Käsittelen tätä esimerkkiä (tietojen suodattamista pudotusvalinnan perusteella ja kopiointia uuteen arkkiin) myöhemmin tässä opetusohjelmassa.

Excel VBA Autofilter -syntaksi

Expression. AutoFilter( _Field_ , _Criteria1_ , _Operator_ , _Criteria2_ , _VisibleDropDown_ )

• Ilmaisu: Tämä on alue, johon haluat soveltaa automaattista suodatusta.
• Kenttä: Tämä on sarakkeen numero, jota haluat suodattaa. Tämä lasketaan tietokokonaisuudessa vasemmalta. Jos siis haluat suodattaa tiedot toisen sarakkeen perusteella, tämä arvo on 2.
• Criteria1: Tämä on kriteeri, jonka perusteella haluat suodattaa tietokokonaisuuden.
• Operator: Jos käytät myös kriteeriä 2, voit yhdistää nämä kaksi kriteeriä Operatorin perusteella. Käytettävissä ovat seuraavat operaattorit: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Arvot
• Kriteeri2: Tämä on toinen kriteeri, jonka perusteella voit suodattaa tietokokonaisuuden.
• VisibleDropDown: Voit määrittää, haluatko suodattimen avattavan kuvakkeen näkyvän suodatetuissa sarakkeissa vai ei. Tämä argumentti voi olla TRUE tai FALSE.

Lisäksi Expression, kaikki muut argumentit ovat valinnaisia.

Jos et käytä yhtään argumenttia, se yksinkertaisesti soveltaisi tai poistaisi suodatinkuvakkeet sarakkeisiin.

Sub FilterRows()Worksheets("Filter Data").Range("A1").AutoFilterEnd Sub

Ylläoleva koodi yksinkertaisesti soveltaisi Autofilter-menetelmää sarakkeisiin (tai poistaisi sen, jos se on jo soveltaen).

Tämä tarkoittaa yksinkertaisesti sitä, että jos et näe suodatinkuvakkeita sarakkeiden otsikoissa, 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 tietokokonaisuuden.

Katsotaan nyt joitakin esimerkkejä Excel VBA Autofilterin käyttämisestä, jotka selventävät sen käyttöä.

Esimerkki:

Oletetaan, että sinulla on alla esitetyn kaltainen tietokokonaisuus ja haluat suodattaa sen sarakkeen ’Nimike’ perusteella.

Alla oleva koodi suodattaisi kaikki rivit, joissa nimike on ’Tulostin’.

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

Yllä oleva koodi viittaa Sheet1:een ja sen sisällä se viittaa A1:een (joka on tietokokonaisuuden solu).

Huomaa, että tässä olemme käyttäneet Field:=2:ta, koska Item-sarake on tietokokonaisuutemme toinen sarake vasemmalta.

Mutta ennen kuin näytän ne, käyn ensin läpi muutaman esimerkin, jotta näet, mitä kaikkea AutoFilter-metodilla voi tehdä.

Klikkaa tästä ladataksesi esimerkkitiedoston ja seurataksesi mukana.

Esimerkki: Useita kriteerejä (AND/OR) samassa sarakkeessa

Esitellään, että minulla on sama tietokokonaisuus, ja tällä kertaa haluan suodattaa kaikki tietueet, joissa kohde on joko ’Tulostin’ tai ’Projektori’.

Alhaalla oleva koodi tekisi tämän:

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

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

Tämä kertoo VBA:lle, että se käyttää molempia kriteerejä ja suodattaa tiedot, jos jompikumpi kriteereistä täyttyy.

Vaikka voit käyttää myös AND-kriteerejä.

Jos esimerkiksi haluat suodattaa kaikki tietueet, joissa määrä on suurempi kuin 10 mutta pienempi kuin 20, voit käyttää alla olevaa koodia:

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

Esim: Useita kriteerejä eri sarakkeilla

Yksi oletetaan, että sinulla on seuraava tietokokonaisuus.

Autosuodattimella voit suodattaa useita sarakkeita samanaikaisesti.

Jos haluat esimerkiksi suodattaa kaikki tietueet, joissa nimike on ’Tulostin’ ja myyntiedustaja on ’Mark’, voit käyttää alla olevaa koodia:

Sub FilterRows()With Worksheets("Sheet1").Range("A1").AutoFilter field:=2, Criteria1:="Printer".AutoFilter field:=3, Criteria1:="Mark"End WithEnd Sub

Esimerkki: Suodata 10 parasta tietuetta käyttämällä AutoFilter-menetelmää

Asettele, että sinulla on alla oleva tietokokonaisuus.

Alhaalla on koodi, joka antaa sinulle 10 parasta tietuetta (määrä-sarakkeen perusteella):

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

Yllä olevassa koodissa olen käyttänyt ActiveSheet. Voit halutessasi käyttää arkin nimeä.

Huomaa, että tässä esimerkissä, jos haluat saada 5 parasta nimikettä, vaihda vain Criteria1:=”10″-kohdassa oleva luku 10:stä 5:een.

Siten 5 parasta nimikettä varten koodi olisi:

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

Mahdollisesti näyttää oudolta, mutta riippumatta siitä, kuinka monta parasta nimikettä haluat, operaattorin arvo pysyy aina xlTop10Items.

Vastaavasti alla oleva koodi antaisi sinulle 10 alinta kohdetta:

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

Ja jos haluat 5 alinta kohdetta, vaihda Criteria1:=”10″-kohdassa oleva luku 10:stä 5:een.

Esimerkki: Suodata ylimmät 10 prosenttia käyttäen AutoFilter-menetelmää

Esitetään, että sinulla on sama tietokokonaisuus (jota käytettiin edellisissä esimerkeissä).

Alhaalla on koodi, joka antaa sinulle ylimmät 10 prosenttia tietueista (määrä-sarakkeen perusteella):

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

Meidän tietokokonaisuudessamme, koska meillä on 20 tietuetta, se palauttaa 2 ylimmät tietuetta (mikä on 10 % kaikista tietueista).

Esimerkki: Jokerimerkkien käyttäminen automaattisessa suodattimessa

Asettele, että sinulla on alla olevan kaltainen tietokokonaisuus:

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

Sub FilterRowsWildcard()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="*Board*"End Sub

Yllä olevassa koodissa olen käyttänyt jokerimerkkiä * (asterisk) ennen sanaa ’Board’ (joka on kriteeri) ja sanan ’Board’ (joka on kriteeri) jälkeen.

Tähti voi edustaa mitä tahansa merkkiä. Tämä suodattaisi siis kaikki kohteet, joissa on sana ’board’.

Esimerkki: Suodatettujen rivien kopioiminen uuteen arkistoon

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

Se kopioi suodatetut rivit, lisää uuden laskentataulukon ja liittää sitten nämä kopioidut rivit uuteen arkistoon.

Sub CopyFilteredRows()Dim rng As RangeDim ws As WorksheetIf Worksheets("Sheet1").AutoFilterMode = False ThenMsgBox "There are no filtered rows"Exit SubEnd IfSet rng = Worksheets("Sheet1").AutoFilter.RangeSet ws = Worksheets.Addrng.Copy Range("A1")End Sub

Yllä oleva koodi tarkistaa, onko Sheet1:ssä suodatettuja rivejä vai ei.

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

Jos suodatettuja rivejä on, se kopioi ne, lisää uuden laskentataulukon ja liittää nämä rivit tuohon hiljattain lisättyyn laskentataulukkoon.

Esimerkki: Suodata tietoja solun arvon perusteella

Käyttämällä VBA:n automaattista suodatusta yhdessä pudotusluettelon kanssa voit luoda toiminnallisuuden, jossa heti kun valitset kohteen pudotusluettelosta, kaikki kyseisen kohteen tietueet suodatetaan.

Jotain alla olevan kaltaista:

Klikkaa tästä ladataksesi esimerkkitiedoston ja seurataksesi mukana.

Tämmöinen konstruktio voi olla hyödyllinen, kun haluat nopeasti suodattaa tietoja ja käyttää niitä sitten edelleen työssäsi.

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

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Address = "$B" Then If Range("B2") = "All" Then Range("A5").AutoFilter Else Range("A5").AutoFilter Field:=2, Criteria1:=Range("B2") End IfEnd IfEnd Sub

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

Myös If Then Else -ehtoa käytetään tarkistamaan, onko käyttäjä valinnut pudotusvalikosta ’Kaikki’. Jos Kaikki on valittu, näytetään koko tietokokonaisuus.

Tätä koodia EI sijoiteta moduuliin.

Sen sijaan se on sijoitettava sen laskentataulukon takaosaan, jossa nämä tiedot ovat.

Tässä ovat vaiheet tämän koodin sijoittamiseksi työarkin koodiikkunaan:

1. Avaa VB-editori (pikanäppäinyhdistelmä – ALT + F11).
2. Kaksoisnapsauta projektinhallintaikkunassa sen työarkin nimeä, johon haluat tämän suodatustoiminnon.
3. Kopioi ja liitä yllä oleva koodi työarkin koodiikkunaan.
4. Sulje VB-editori.

Nyt kun käytät pudotusluetteloa, se suodattaa tiedot automaattisesti.

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

Myös If Then Else -ehtoa käytetään tarkistamaan, onko käyttäjä valinnut pudotusluettelosta ’Kaikki’. Jos Kaikki on valittu, näytetään koko tietokokonaisuus.

Excelin automaattisen suodattimen kytkeminen päälle/pois päältä VBA:n avulla

Kun sovellat automaattista suodatinta solualueeseen, joitakin suodattimia voi olla jo käytössä.

Voit käyttää alla olevaa koodia, jolla voit kytkeä pois päältä kaikki valmiiksi käytetyt automaattiset suodattimet:

Sub TurnOFFAutoFilter() Worksheets("Sheet1").AutoFilterMode = FalseEnd Sub

Tämä koodi tarkastaa koko arkit ja poistaa kaikki käytetyt suodattimet.

Jos et halua kytkeä suodattimia pois päältä koko arkilta vaan vain tietystä tietokokonaisuudesta, käytä alla olevaa koodia:

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

Yllä oleva koodi tarkistaa, onko suodattimia jo käytössä vai ei.

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

Jos haluat ottaa AutoFilterin käyttöön, käytä alla olevaa koodia:

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

Tarkista, onko AutoFilter jo käytössä

Jos sinulla on arkki, jossa on useita tietokokonaisuuksia, ja haluat varmistaa, ettei suodattimia ole jo käytössä, voit käyttää alla olevaa koodia.

Sub CheckforFilters()If ActiveSheet.AutoFilterMode = True ThenMsgBox "There are Filters already in place"ElseMsgBox "There are no filters"End IfEnd Sub

Tämä koodi käyttää viestiruutufunktiota, joka näyttää viestin ”Suodattimia on jo käytössä”, jos se löytää suodattimia arkilta, muuten se näyttää ”Suodattimia ei ole”.

Näytä kaikki tiedot

Jos tietokokonaisuuteen on sovellettu suodattimia ja haluat näyttää kaikki tiedot, käytä alla olevaa koodia:

Sub ShowAllData()If ActiveSheet.FilterMode Then ActiveSheet.ShowAllDataEnd Sub

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

Jos se on true, se tarkoittaa, että suodatinta on sovellettu, ja se käyttää ShowAllData-metodia 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, kun suojaat arkin, suodattimet eivät toimi.

Jos sinulla on jo suodattimet käytössä, voit ottaa automaattisen suodattimen käyttöön, jotta se toimii myös suojatuilla arkeilla.

Tehdäksesi tämän, merkitse Käytä automaattista suodatinta -valinta Käytä automaattista suodatinta -vaihtoehto, kun suojaat arkin.

Tämä toimii silloin, kun suodattimet ovat jo käytössä, mutta jos yrität lisätä Autofilttereitä VBA-koodilla, se ei toimi.

Sen vuoksi, että arkki on suojattu, mikään makro ei saisi suorittaa ja tehdä muutoksia Autofilteriin.

Siten sinun on käytettävä koodia työarkin suojaamiseen ja varmistettava, että automaattiset suodattimet ovat siinä käytössä.

Tämä voi olla hyödyllistä, kun olet luonut dynaamisen suodattimen (jotain, mitä käsittelin esimerkissä – ’Suodata tietoja solun arvon perusteella’).

Alhaalla on koodi, joka suojaa arkin, mutta antaa samalla mahdollisuuden käyttää siinä suodattimia sekä VBA-makroja.

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

Tämä koodi on sijoitettava ThisWorkbook-koodiikkunaan.

Koodin sijoittaminen ThisWorkbook-koodiikkunaan tapahtuu seuraavasti:

1. Avaa VB-editori (pikanäppäinyhdistelmä – ALT + F11).
2. Kaksoisnapsauta Project Explorer -ikkunassa ThisWorkbook-objektia.
3. Kopioi ja liitä avautuvaan koodiikkunaan yllä oleva koodi.

Heti kun avaat työkirjan ja otat makrot käyttöön, se suorittaa makron automaattisesti ja suojaa Sheet1.

Mutta ennen sitä se määrittää ’EnableAutoFilter = True’, mikä tarkoittaa, että suodattimet toimisivat myös suojatulla arkilla.

Se asettaa myös ’UserInterfaceOnly’-argumentin arvoksi ’True’. Tämä tarkoittaa, että vaikka työarkki on suojattu, VBA-makrojen koodi toimisi edelleen.

Voit pitää myös seuraavista VBA-oppaista:

• Excel VBA-silmukat.
• Suodata soluja lihavoidulla fontin muotoilulla.
• Makron tallentaminen.
• Lajittele tietoja VBA:n avulla.
• Lajittele työarkin välilehtiä Excelissä.