Tento tutoriál předvede, jak používat filtr kontingenční tabulky ve VBA.
Kontingenční tabulky jsou mimořádně výkonným datovým nástrojem Excelu. Kontingenční tabulky nám umožňují analyzovat a interpretovat velké množství dat seskupením a shrnutím polí a řádků. Na kontingenční tabulky můžeme použít filtry, které nám umožní rychle zobrazit data, která jsou pro nás relevantní.
Nejprve musíme pro naše data vytvořit kontingenční tabulku. (Kliknutím sem zobrazíte našeho průvodce kontingenční tabulkou VBA).
Vytvoření filtru na základě hodnoty buňky
V kontingenční tabulce můžete filtrovat pomocí jazyka VBA na základě údajů obsažených v hodnotě buňky - můžeme filtrovat buď v poli Stránka, nebo v poli Řádek (například v poli Dodavatel výše nebo v poli Oper, které je ve sloupci Popisky řádků ).
V prázdné buňce napravo od kontingenční tabulky vytvořte buňku pro uložení filtru a poté zadejte data do buňky, na které chcete filtrovat kontingenční tabulku.
Vytvořte následující makro VBA:
| 1234567 | Sub FilterPageValue ()Dim pvFld As PivotFieldDim strFilter jako řetězecNastavit pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Dodavatel")strFilter = ActiveWorkbook.Sheets ("Sheet1"). Range ("M4"). valuepvFld.CurrentPage = strFilterEnd Sub |
Spusťte makro a použijte filtr.
Chcete -li filtr vymazat, vytvořte následující makro:
| 12345 | Sub ClearFilter ()Dim pTbl as PivotTableNastavit pTbl = ActiveSheet.PivotTables ("PivotTable1")pTbl.ClearAllFiltersEnd Sub |
Poté bude filtr odstraněn.
Můžeme pak upravit kritéria filtrování tak, aby filtrovala na řádku v kontingenční tabulce, nikoli na aktuální stránce.
Zadání následujícího makra nám pak umožní filtrovat na řádku (všimněte si, že kontingenční pole, na kterém se má filtrovat, je nyní Operátor, nikoli Dodavatel).
| 1234567 | Sub FilterRowValue ()Dim pvFld As PivotFieldDim strFilter jako řetězecNastavit pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Oper")strFilter = ActiveWorkbook.Sheets ("Sheet1"). Range ("M4"). valuepvFld.PivotFilters.Add2 xlCaptionEquals,, strFilterEnd Sub |
Spusťte makro a použijte filtr.
Použití více kritérií v kontingenčním filtru
Do výše uvedeného filtru hodnot řádku můžeme přidat přidáním dalších kritérií.
Jelikož však standardní filtr skrývá řádky, které nejsou povinné, musíme projít kritérii a ukázat ty, které jsou požadovány, a skrýt ty, které nejsou povinné. To se provádí vytvořením proměnné Array a použitím několika smyček v kódu.
| 1234567891011121314151617181920212223 | Dílčí filtrMultipleRowItems ()Dim vArray As VariantDim i jako celé číslo, j jako celé čísloDim pvFld As PivotFieldNastavit pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Oper")vArray = Rozsah ("M4: M5")pvFld.ClearAllFiltersS pvFldPro i = 1 To pvFld.PivotItems.Countj = 1Do While j <= UBound (vArray, 1) - LBound (vArray, 1) + 1Pokud pvFld.PivotItems (i) .Name = vArray (j, 1) ThenpvFld.PivotItems (pvFld.PivotItems (i) .Name) .Visible = TrueUkončit DoJinýpvFld.PivotItems (pvFld.PivotItems (i) .Name) .Visible = FalseKonec Ifj = j + 1SmyčkaPříště jáKonec sEnd Sub |
Vytvoření filtru na základě proměnné
Stejné koncepty můžeme použít k vytváření filtrů založených na proměnných v našem kódu, nikoli na hodnotě v buňce. Tentokrát je proměnná filtru (strFilter) naplněna v samotném kódu (např .: pevně zakódováno do makra).
| 1234567 | Dílčí filtrTextValue ()Dim pvFld As PivotFieldDim strFilter jako řetězecNastavit pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Dodavatel")strFilter = "THOMAS S"pvFld.CurrentPage = strFilterEnd Sub |
