Autofilter

Vastaa

Tämän artikkelin esimerkit tiedostossa autofilter.xlsm

Yksinkertainen suodatus

Range.Autofilter menetelmällä voit suodattaa aineistosta osa-ainestoja tehokkaasti. Esimerkiksi lause

ActiveCell.AutoFilter Field:=5, Criteria1:=”Korkeakoulu”

suodattaa aktiivisen solun ympärillä olevan aineiston siten että näkyville jäävät vain rivit, joissa on viidennessä sarakkeessa teksti Korkeakoulu.

Seuraavassa ohjelmassa hyödynnän suodattamista. Ohjelma kopioi korkeakoulututkinnon suorittaneiden henkilöiden tiedot uuteen laskentataulukkoon.

Sub MyFilterCopy()
Dim MyRange As Range
Dim NewSheet As Worksheet
    Set MyRange = ActiveCell
    MyRange.AutoFilter Field:=5, Criteria1:="Korkeakoulu"
    Set NewSheet = Worksheets.Add
    MyRange.CurrentRegion.Copy Destination:=NewSheet.Range("A1")
    MyRange.AutoFilter
End Sub

Huomaa erityisesti, että Range-objektin CurrentRegion-ominaisuus tavoittaa suodatetusta aineistota näkyville jääneet rivit. Ohjelman viimeisenä rivinä on AutoFilter-menetelmä ilman argumentteja. Tämä poistaa aineiston suodatukset.

Seuraava ohjelmaa värjää korkeakoulun suorittaneiden rivit vihreiksi.

Sub MyFilterGreen()
Dim MyRange As Range
Dim MyRows As Long
    Set MyRange = ActiveCell
    MyRows = MyRange.CurrentRegion.Rows.Count
    MyRange.AutoFilter Field:=5, Criteria1:="Korkeakoulu"
    MyRange.CurrentRegion.Offset(1).Resize(MyRows - 1) _
        .Interior.Color = RGB(0, 255, 0)
    MyRange.AutoFilter
End Sub

Offset(1).Resize(MyRows-1) tarvitaan, jotta ostsikkoriviä ei värjätä.

Seuraava ohjelma poistaa korkeakoulun suorittaneet aineistosta.

Sub MyFilterRowsDelete()
Dim MyRange As Range
Dim MyRows As Long
    Set MyRange = ActiveCell
    MyRows = MyRange.CurrentRegion.Rows.Count
    MyRange.AutoFilter Field:=5, Criteria1:="Korkeakoulu"
    MyRange.CurrentRegion.Offset(1).Resize(MyRows - 1) _
        .EntireRow.Delete
    MyRange.AutoFilter
End Sub

Voit käyttää Criteria1-argumentin arvoissa jokerimerkkiä *. Esimerkiksi Criteria1:=”*koulu*” jättää näkyville kaikki rivit, joissa esiintyy viidennessä sarakkeessa sana koulu.

Jos haluat hakea tyhjiä soluja, niin käytä Criteria1:=”=”

Jos haluat hakea ei-tyhjiä soluja, niin käytä Criteria1:=”<>”

Operator-argumentti

AutoFilter-menetelmän Operator-argumentin avulla voit tehdä monipuolisempia suodatuksia.

ActiveCell.AutoFilter Field:=7, Criteria1:=”3″, Operator:=xlTop10Items jättää näkyville ne rivit, joissa on seitsemännen sarakkeen kolme suurinta arvoa.

  • Field-argumentti määrittää sarakkeen, jota ehto koskee.
  • Operator:=xlTop10Items tarkoittaa, että sarakkeesta haetaan suuripia arvoja. On toki hämmentävää, että argumentin arvossa esiintyy luku 10 vaikka välttämättä ei olla hakemassa kymmentä suurinta arvoa.
  • Criteria1-argumentin arvolla täsmennetään kuinka monta suurinta haetaan.

Vastaavalla tavalla voit käyttää Operator-argumentin arvoja xlBottom10Items, xlTop10Percent ja xlBottom10Percent. Voit jopa suodattaa värin perusteella käyttämällä argumentin arvoja xlFilterCellColor tai xlFilterFontColor. Värin voi ilmaista RGB-arvona, esimerkiksi Criteria1:=RGB(0,255,0).

Operator-argumentin xlAnd ja xlOr -arvoilla voit yhdistää ehtoja. Esimerkiksi

ActiveCell.AutoFilter Field:=8, Criteria1:=”1″, Operator:=xlOr, Criteria2:=”2″

jättää näkyville ne rivit, joissa on kahdeksannessa sarakkeessa arvo 1 tai arvo 2.

ActiveCell.AutoFilter Field:=7, Criteria1:=”>2000″, Operator:=xlAnd,  Criteria2:=”<3000″

jättää näkyville ne rivit, joissa on seitsemännessä sarakkeessa suurempi kuin 2000, mutta pienempi kuin 3000.

Jos haluat useampia kuin 2 ehtoa, niin voit antaa ehdot vektorina (Array). Tällöin Operator-argumentin arvona on xlFilterValues. Esimerkiksi lause

Range(”A1”).AutoFilter Field:=6, Criteria1:=Array(”1”, ”2”,  ”4”), Operator:=xlFilterValues

jättää näkyville ne rivit, joissa on kuudennessa sarakkeessa arvo 1, 2 tai 4.

Lisätietoa

Lisätietoa Range.AutoFilter -menetelmän ohjetoiminnosta.

Tämän artikkelin esimerkit tiedostossa autofilter.xlsm

Jätä kommentti