Pyyhkäise näyttääksesi valikon

Suodatus kaventaa näkyvissä olevaa tietoa koskematta taustalla olevaan dataan — olennainen raportin rakentamisessa, joka näyttää kerrallaan vain yhden kuukauden tai alueen.

Perus AutoFilter

tbl.Range.AutoFilter Field:=1, Criteria1:="January"
' Field:=1 means the 1st column of the table (Month)

AutoFilter ei poista tai siirrä mitään dataa — se piilottaa rivit, jotka eivät täsmää, aivan kuten olisit itse klikannut suodatusnuolta ja jättänyt valituksi vain tammikuun. Field:=1 laskee sarakkeet alkaen yhdestä taulukon sisällä (Month, Region, Sales, Expenses, Profit, Target — joten Region olisi Field:=2, Profit Field:=5), minkä vuoksi tämä rivi täytyy pitää synkronoituna, jos sarakkeiden järjestystä muutetaan.

Moniehtoisten suodattimien käyttö

Suodatus useampaan arvoon samassa sarakkeessa vaatii xlFilterValues ja kriteeritaulukon:

tbl.Range.AutoFilter Field:=2, _
    Criteria1:=Array("North", "Central"), _
    Operator:=xlFilterValues

Vertaa tätä yllä olevaan yhden arvon suodattimeen: Criteria1 sisältää nyt Array(...) hyväksyttäviä arvoja yhden merkkijonon sijaan, ja Operator:=xlFilterValues kertoo AutoFilterille, että taulukkoa käsitellään listana täsmäävistä arvoista eikä yhtenä kriteeri-ilmaisuna. Jos jätät Operator:=xlFilterValues pois, tämä rivi aiheuttaa virheen tai käyttäytyy odottamattomasti — tämän unohtaminen on helppoa ja kannattaa tarkistaa aina, kun Criteria1 on lista.

Suodatus numeerisella ehdolla — esimerkiksi vain rivit, joissa Profit ylittää Targetin reilusti — käyttää vertailuoperaattoreita:

tbl.Range.AutoFilter Field:=5, Criteria1:=">15000"

Huomaa, että ">15000" kirjoitetaan tekstinä lainausmerkkeihin, vaikka kyseessä on numeerinen vertailu — AutoFilter odottaa aina Criteria1:nä merkkijonoa ja tulkitsee >-merkin itse. Jos kirjoitat Criteria1:=15000 ilman >-merkkiä, suodatus hakee vain rivit, joissa arvo on täsmälleen 15000, ei suuremmat — tämä on yleinen ja helppo virhe.

Lajittelu

Sort-olio tukee useita avaimia, aivan kuten Data → Sort -valintaikkuna:

With tbl.Sort
    .SortFields.Clear
    .SortFields.Add2 Key:=tbl.ListColumns("Month").Range, _
        SortOn:=xlSortOnValues, Order:=xlAscending
    .SortFields.Add2 Key:=tbl.ListColumns("Profit").Range, _
        SortOn:=xlSortOnValues, Order:=xlDescending
    .Header = xlYes
    .Apply
End With

.SortFields.Clear suoritetaan ensin, jotta aiemman makron (tai käyttäjän manuaalisen lajittelun) jäljiltä jääneet lajitteluavaimet eivät yhdisty huomaamatta uusiin — tyhjennä aina ennen lisäämistä. Kahden .Add2-kutsun järjestyksellä on yhtä paljon merkitystä kuin niiden Order:=xlAscending/xlDescending -asetuksilla: ensimmäinen lisätty on ensisijainen lajitteluavain (Month), toinen toimii tasatilanteiden ratkaisijana ryhmän sisällä (Profit, suurin ensin kussakin kuukaudessa). .Header = xlYes kertoo Excelille, että rivi 1 on otsikko eikä sitä saa siirtää lajittelussa; .Apply suorittaa lajittelun — kaikki sitä ennen vain rakentaa ohjeet.

Suodattimien tyhjentäminen

Tyhjennä suodattimet aina raporttimakron alussa, jotta jokainen ajo alkaa tunnetusta, suodattamattomasta tilasta:

If tbl.AutoFilter.FilterMode Then
    tbl.AutoFilter.ShowAllData
End If

FilterMode on totuusarvo, joka on True aina, kun jokin suodatin kaventaa taulukon näkyviä rivejä — sen tarkistaminen ensin estää ajoaikaisen virheen, sillä ShowAllData aiheuttaa virheen, jos mitään ei ole suodatettu, eikä vain tee mitään hiljaisesti.

Tehtävä

Kirjoita makro, joka suodattaa tblReports-taulukon vain helmikuuhun käyttämällä AutoFilter Field:=1.
Laajenna makro suodattamaan myös Alue (Field:=2) vain "East" ja "West" -arvoihin samanaikaisesti käyttämällä xlFilterValues.
Tyhjennä molemmat suodattimet ja lajittele taulukko ensin Alueen mukaan nousevasti ja sitten Tuoton mukaan laskevasti käyttämällä yllä esitettyä Sort-objektia.

Vihje

1. Suodatus helmikuuhun

Field:=1 viittaa taulukon ensimmäiseen sarakkeeseen, ei laskentataulukon sarakkeeseen — Kuukausi on sarake 1 tblReports-taulukossa riippumatta siitä, missä laskentataulukon sarakkeessa se fyysisesti sijaitsee.
Criteria1 saa arvoksi tarkan tekstin, johon suodatetaan, lainausmerkeissä.
AutoFilter kutsutaan tbl.Range-alueelle, ei suoraan laskentataulukkoon.

2. Alue-suodattimen lisääminen

Alue on taulukon toinen sarake, joten se vaatii eri Field:=-numeron kuin Kuukausi-suodatin.
Suodatettaessa kahta arvoa samassa sarakkeessa käytetään Criteria1:=Array(...) molempien arvojen kanssa sekä Operator:=xlFilterValues — tämän operaattorin pois jättäminen on yleisin virhe tässä.
Molemmat suodattimet (Kuukausi ja Alue) voivat olla aktiivisia samanaikaisesti — kutsu AutoFilter-toimintoa kahdesti, kerran per sarake.

3. Suodattimien tyhjennys ja lajittelu

Tarkista tbl.AutoFilter.FilterMode ennen kuin kutsut ShowAllData — jos kutsut sitä, kun mitään ei ole suodatettu, tulee virhe.
Sort-objektille täytyy ensin tehdä .SortFields.Clear, sitten yksi .SortFields.Add2 jokaista lajittelutasoa kohden — lisäysjärjestys määrittää, mikä on ensisijainen avain ja mikä ratkaisee tasatilanteet, ei taulukon sarakejärjestys.
Alue nousevassa järjestyksessä tulee lisätä ennen Tuottoa laskevassa järjestyksessä, koska Alue on tarkoitettu ensisijaiseksi lajitteluperusteeksi.

Ratkaisu

Option Explicit

Sub FilterToFebruary()
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Worksheets("Reports").ListObjects("tblReports")

    tbl.Range.AutoFilter Field:=1, Criteria1:="February"
End Sub

Sub FilterFebruaryEastWest()
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Worksheets("Reports").ListObjects("tblReports")

    tbl.Range.AutoFilter Field:=1, Criteria1:="February"
    tbl.Range.AutoFilter Field:=2, _
        Criteria1:=Array("East", "West"), _
        Operator:=xlFilterValues
End Sub

Sub ClearFiltersAndSort()
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Worksheets("Reports").ListObjects("tblReports")

    ' Clear any active filters
    If tbl.AutoFilter.FilterMode Then
        tbl.AutoFilter.ShowAllData
    End If

    ' Sort by Region ascending, then Profit descending
    With tbl.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=tbl.ListColumns("Region").Range, _
            SortOn:=xlSortOnValues, Order:=xlAscending
        .SortFields.Add2 Key:=tbl.ListColumns("Profit").Range, _
            SortOn:=xlSortOnValues, Order:=xlDescending
        .Header = xlYes
        .Apply
    End With
End Sub

Suorita FilterFebruaryEastWest ja näet vain helmikuun rivit, joissa Alue on East tai West. Suorita sitten ClearFiltersAndSort — kaikki rivit tulevat näkyviin, lajiteltuna ensin Alueen mukaan (aakkosjärjestyksessä) ja kunkin alueen sisällä suurin Tuotto ensin.

Oliko kaikki selvää?

Kiitos palautteestasi!

Osio 4. Luku 2

Kysy tekoälyä

Kysy mitä tahansa tai kokeile jotakin ehdotetuista kysymyksistä aloittaaksesi keskustelumme

Datan lajittelu ja suodatus