Tento tutoriál ukáže, ako používať filter kontingenčnej tabuľky vo VBA.
Kontingenčné tabuľky sú mimoriadne výkonným dátovým nástrojom Excelu. Kontingenčné tabuľky nám umožňujú analyzovať a interpretovať veľké množstvo údajov zoskupovaním a sumarizáciou polí a riadkov. Na kontingenčné tabuľky môžeme použiť filtre, ktoré nám umožnia rýchlo vidieť údaje, ktoré sú pre nás relevantné.
Po prvé, musíme pre svoje údaje vytvoriť kontingenčnú tabuľku. (Kliknite sem a získajte nášho sprievodcu kontingenčnou tabuľkou VBA).
Vytvorenie filtra na základe hodnoty bunky
V kontingenčnej tabuľke môžete filtrovať pomocou jazyka VBA na základe údajov obsiahnutých v hodnote bunky - filtrovať môžeme buď v poli Stránka, alebo v riadku (napríklad v poli Dodávateľ vyššie alebo v poli Oper, ktoré je v stĺpci Riadky ).
V prázdnej bunke napravo od kontingenčnej tabuľky vytvorte bunku, do ktorej bude uložený filter, a potom zadajte údaje do bunky, v ktorej chcete filtrovať kontingenčnú tabuľku.
Vytvorte nasledujúce makro VBA:
| 1234567 | Sub FilterPageValue ()Dim pvFld as PivotFieldDim strFilter ako reťazecNastaviť pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("dodávateľ")strFilter = ActiveWorkbook.Sheets ("Sheet1"). Range ("M4"). valuepvFld.CurrentPage = strFilterKoniec pod |
Spustite makro, aby ste použili filter.
Ak chcete filter vymazať, vytvorte nasledujúce makro:
| 12345 | Sub ClearFilter ()Dim pTbl as PivotTableNastaviť pTbl = ActiveSheet.PivotTables ("PivotTable1")pTbl.ClearAllFiltersKoniec pod |
Potom bude filter odstránený.
Kritériá filtra potom môžeme zmeniť tak, aby sa filtrovali na riadku v kontingenčnej tabuľke a nie na aktuálnej stránke.
Zadanie nasledujúceho makra nám potom umožní filtrovať na riadku (všimnite si, že kontingenčné pole, na ktorom sa má filtrovať, je teraz Operátor a nie Dodávateľ).
| 1234567 | Sub FilterRowValue ()Dim pvFld as PivotFieldDim strFilter ako reťazecNastaviť pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Oper")strFilter = ActiveWorkbook.Sheets ("Sheet1"). Range ("M4"). valuepvFld.PivotFilters.Add2 xlCaptionEquals,, strFilterKoniec pod |
Spustite makro, aby ste použili filter.
Použitie viacerých kritérií v kontingenčnom filtri
Do vyššie uvedeného filtra hodnôt riadkov môžeme pridať pridaním ďalších kritérií.
Pretože však štandardný filter skrýva riadky, ktoré nie sú povinné, musíme prejsť kritériami a ukázať tie, ktoré sú požadované, a skryť tie, ktoré nie sú požadované. To sa dosiahne vytvorením premennej Array a použitím niekoľkých slučiek v kóde.
| 1234567891011121314151617181920212223 | Sub FilterMultipleRowItems ()Dim vArray As VariantDim i ako celé číslo, j ako celé čísloDim pvFld as PivotFieldNastaviť pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Oper")vArray = Rozsah ("M4: M5")pvFld.ClearAllFiltersS pvFldPre i = 1 To pvFld.PivotItems.Countj = 1Vykonajte j <= UBound (vArray, 1) - LBound (vArray, 1) + 1Ak pvFld.PivotItems (i) .Name = vArray (j, 1) ThenpvFld.PivotItems (pvFld.PivotItems (i) .Name) .Visible = TrueUkončiť DoInakpvFld.PivotItems (pvFld.PivotItems (i) .Name) .Visible = FalseKoniec Akj = j + 1SlučkaĎalej iUkončiť sKoniec pod |
Vytvorenie filtra na základe premennej
Rovnaké koncepty môžeme použiť aj na vytváranie filtrov na základe premenných v našom kóde, nie na základe hodnoty v bunke. Filtračná premenná (strFilter) je tentokrát vyplnená v samotnom kóde (napr .: napevno zakódované do makra).
| 1234567 | Sub FilterTextValue ()Dim pvFld as PivotFieldDim strFilter ako reťazecNastaviť pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("dodávateľ")strFilter = "THOMAS S"pvFld.CurrentPage = strFilterKoniec pod |
