Sortering og filtrering af data

Filtrering indsnævrer det synlige uden at ændre de underliggende data — afgørende for at opbygge en rapport, der kun viser én måned eller én region ad gangen.

Grundlæggende AutoFilter

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

AutoFilter sletter eller flytter ikke nogen data — den skjuler rækker, der ikke matcher, præcis som hvis du havde klikket på dropdown-pilen og fjernet markeringen for alt undtagen January manuelt. Field:=1 tæller kolonner startende fra 1 inden for selve tabellen (Month, Region, Sales, Expenses, Profit, Target — så Region vil være Field:=2, Profit Field:=5), hvilket er grunden til, at denne linje skal holdes opdateret, hvis kolonnerne nogensinde omarrangeres.

Filtrering med flere betingelser

Filtrering på mere end én værdi i samme kolonne kræver xlFilterValues og et array af kriterier:

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

Sammenlign dette med filteret for én værdi ovenfor: Criteria1 indeholder nu et Array(...) af acceptable værdier i stedet for én enkelt streng, og Operator:=xlFilterValues fortæller AutoFilter at behandle dette array som en liste af matchende værdier i stedet for at forsøge at tolke det som et enkelt kriterieudtryk. Hvis du udelader Operator:=xlFilterValues, vil denne linje enten give fejl eller opføre sig uventet — det er let at overse og værd at dobbelttjekke, når Criteria1 er en liste.

Filtrering på en numerisk betingelse — for eksempel kun rækker hvor Profit overstiger Target med en god margin — bruger i stedet sammenligningsoperatorer:

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

Bemærk at ">15000" er skrevet som tekst i anførselstegn, selvom det er en numerisk sammenligning — AutoFilter forventer altid Criteria1 som en streng og fortolker selv det indledende >. Hvis du skriver Criteria1:=15000 uden >, vil det filtrere efter rækker, der præcis er lig med 15000 i stedet for større end, hvilket er en almindelig og let fejl.

Sortering

Sort-objektet understøtter flere nøgler, præcis som dialogboksen Data → Sorter:

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 køres først, så resterende sorteringsnøgler fra et tidligere makrokørsel (eller fra en bruger, der manuelt har sorteret tabellen tidligere) ikke kombineres med de nye — ryd altid før du tilføjer. Rækkefølgen af de to .Add2-kald er lige så vigtig som deres Order:=xlAscending/xlDescending-indstillinger: den første, der tilføjes, bliver den primære sorteringsnøgle (Month), og den anden bliver afgørende for sortering inden for hver gruppe (Profit, højest først inden for hver måned). .Header = xlYes fortæller Excel, at række 1 er en overskrift og aldrig må flyttes ved sortering; .Apply er det, der faktisk udfører sorteringen — alt før det opbygger blot instruktionerne.

Rydning af filtre

Ryd altid filtre i starten af et rapportmakro, så hver kørsel starter fra en kendt, ufiltreret tilstand:

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

FilterMode er en boolesk værdi, der er sand, når et filter aktuelt indsnævrer tabellens synlige rækker — at tjekke det først undgår en kørselsfejl, da et kald til ShowAllData, når intet faktisk er filtreret, giver en fejl i stedet for blot at gøre ingenting.

Opgave

1. Skriv et makro, der filtrerer tblReports til kun februar ved at bruge AutoFilter Field:=1.
2. Udvid det til at filtrere Region (Field:=2) til kun "East" og "West" samtidig ved at bruge xlFilterValues.
3. Ryd begge filtre, og sorter derefter tabellen efter Region stigende og derefter efter Profit faldende ved hjælp af Sort-objektet vist ovenfor.