Deze zelfstudie laat zien hoe u het draaitabelfilter in VBA kunt gebruiken.
Draaitabellen zijn een uitzonderlijk krachtig gegevenshulpmiddel van Excel. Met draaitabellen kunnen we grote hoeveelheden gegevens analyseren en interpreteren door velden en rijen te groeperen en samen te vatten. We kunnen filters toepassen op onze draaitabellen zodat we snel de gegevens kunnen zien die voor ons relevant zijn.
Ten eerste moeten we een draaitabel maken voor onze gegevens. (Klik hier voor onze VBA-draaitabelgids).
Een filter maken op basis van een celwaarde
U kunt filteren in een draaitabel met VBA op basis van gegevens in een celwaarde - we kunnen filteren op het veld Pagina of op een rijveld (bijvoorbeeld op het veld Leverancier hierboven of het veld Oper in de kolom Rijlabels ).
Maak in een lege cel rechts van de draaitabel een cel voor het filter en typ vervolgens de gegevens in de cel waarop u de draaitabel wilt filteren.
Maak de volgende VBA-macro:
| 1234567 | Subfilterpaginawaarde()Dim pvFld als draaiveldDim strFilter als stringStel pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Leverancier")strFilter = ActiveWorkbook.Sheets("Blad1").Bereik ("M4").WaardepvFld.CurrentPage = strFilterEinde sub |
Voer de macro uit om het filter toe te passen.
Maak de volgende macro om het filter te wissen:
| 12345 | Sub WissenFilter()Dim pTbl als draaitabelStel pTbl = ActiveSheet.PivotTables("PivotTable1")pTbl.ClearAllFiltersEinde sub |
Het filter wordt dan verwijderd.
We kunnen dan de filtercriteria aanpassen om te filteren op een rij in de draaitabel in plaats van op de huidige pagina.
Door de volgende macro te typen, kunnen we filteren op de rij (merk op dat het draaiveld om op te filteren nu de Oper is in plaats van de Leverancier).
| 1234567 | SubfilterRijWaarde()Dim pvFld als draaiveldDim strFilter als stringStel pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Oper")strFilter = ActiveWorkbook.Sheets("Blad1").Bereik ("M4").WaardepvFld.PivotFilters.Add2 xlCaptionEquals, , strFilterEinde sub |
Voer de macro uit om het filter toe te passen.
Meerdere criteria gebruiken in een draaifilter
We kunnen het filter Rijwaarde hierboven toevoegen door aanvullende criteria toe te voegen.
Omdat het standaardfilter echter de rijen verbergt die niet nodig zijn, moeten we de criteria doorlopen en de rijen tonen die wel nodig zijn, terwijl we de rijen verbergen die niet nodig zijn. Dit wordt gedaan door een array-variabele te maken en een aantal lussen in de code te gebruiken.
| 1234567891011121314151617181920212223 | SubfilterMultipleRowItems()Dim vArray als variantDim i als geheel getal, j als geheel getalDim pvFld als draaiveldStel pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Oper")vArray = Bereik ("M4:M5")pvFld.ClearAllFiltersMet pvFldVoor i = 1 Naar pvFld.PivotItems.Countj = 1Do While j <= UBound(vArray, 1) - LBound(vArray, 1) + 1Als pvFld.PivotItems(i).Name = vArray(j, 1) DanpvFld.PivotItems(pvFld.PivotItems(i).Name).Visible = TrueAfsluiten DoAnderspvFld.PivotItems(pvFld.PivotItems(i).Name).Visible = FalseStop alsj = j + 1Lusvolgende iEindigt metEinde sub |
Een filter maken op basis van een variabele
We kunnen dezelfde concepten gebruiken om filters te maken op basis van variabelen in onze code in plaats van de waarde in een cel. Deze keer wordt de filtervariabele (strFilter) ingevuld in de code zelf (bijv. Hard gecodeerd in de macro).
| 1234567 | SubfilterTextValue()Dim pvFld als draaiveldDim strFilter als stringStel pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Leverancier")strFilter = "THOMAS S"pvFld.CurrentPage = strFilterEinde sub |
