Este tutorial demonstrará como usar o Filtro de Tabela Dinâmica no VBA.
As tabelas dinâmicas são uma ferramenta de dados excepcionalmente poderosa do Excel. As tabelas dinâmicas nos permitem analisar e interpretar grandes quantidades de dados agrupando e resumindo campos e linhas. Podemos aplicar filtros às nossas tabelas dinâmicas para nos permitir ver rapidamente os dados que são relevantes para nós.
Em primeiro lugar, precisamos criar uma tabela dinâmica para nossos dados. (Clique aqui para ver nosso Guia de Tabela Dinâmica do VBA).
Criação de um filtro com base em um valor de célula
Você pode filtrar em uma Tabela Dinâmica usando VBA com base nos dados contidos em um valor de célula - podemos filtrar no campo Página ou no campo Linha (por exemplo no campo Fornecedor acima ou no campo Oper que está na coluna Rótulos de Linha )
Em uma célula vazia à direita da Tabela Dinâmica, crie uma célula para conter o filtro e digite os dados na célula em que deseja filtrar a Tabela Dinâmica.
Crie a seguinte macro VBA:
| 1234567 | Sub FilterPageValue ()Dim pvFld como PivotFieldDim strFilter As StringDefina pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Fornecedor")strFilter = ActiveWorkbook.Sheets ("Sheet1"). Range ("M4"). ValuepvFld.CurrentPage = strFilterEnd Sub |
Execute a macro para aplicar o filtro.
Para limpar o filtro, crie a seguinte macro:
| 12345 | Sub ClearFilter ()Dim pTbl como tabela dinâmicaDefinir pTbl = ActiveSheet.PivotTables ("PivotTable1")pTbl.ClearAllFiltersEnd Sub |
O filtro será removido.
Podemos então corrigir os critérios de filtro para filtrar em uma linha na Tabela Dinâmica em vez da Página Atual.
Digitar a macro a seguir nos permitirá filtrar na Linha (observe que o Campo Dinâmico para filtrar agora é o Oper em vez do Fornecedor).
| 1234567 | Sub FilterRowValue ()Dim pvFld como PivotFieldDim strFilter As StringDefinir pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Oper")strFilter = ActiveWorkbook.Sheets ("Sheet1"). Range ("M4"). ValuepvFld.PivotFilters.Add2 xlCaptionEquals,, strFilterEnd Sub |
Execute a macro para aplicar o filtro.
Usando vários critérios em um filtro dinâmico
Podemos adicionar ao filtro de valor de linha acima, adicionando critérios adicionais.
No entanto, como o filtro padrão oculta as linhas que não são obrigatórias, precisamos percorrer os critérios e mostrar as que são solicitadas, enquanto ocultamos as que não são obrigatórias. Isso é feito criando uma variável Array e usando alguns Loops no código.
| 1234567891011121314151617181920212223 | Sub FilterMultipleRowItems ()Dim vArray As VariantDim i As Integer, j As IntegerDim pvFld como PivotFieldDefinir pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Oper")vArray = intervalo ("M4: M5")pvFld.ClearAllFiltersCom pvFldPara i = 1 para pvFld.PivotItems.Countj = 1Do While j <= UBound (vArray, 1) - LBound (vArray, 1) + 1If pvFld.PivotItems (i) .Name = vArray (j, 1) ThenpvFld.PivotItems (pvFld.PivotItems (i) .Name) .Visible = TrueSair DoOutropvFld.PivotItems (pvFld.PivotItems (i) .Name) .Visible = FalseFim sej = j + 1CicloProximo euTerminar comEnd Sub |
Criação de um filtro com base em uma variável
Podemos usar os mesmos conceitos para criar filtros com base em variáveis em nosso código, em vez do valor em uma célula. Desta vez, a variável de filtro (strFilter) é preenchida no próprio código (por exemplo: codificado na macro).
| 1234567 | Sub FilterTextValue ()Dim pvFld como PivotFieldDim strFilter As StringDefina pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Fornecedor")strFilter = "THOMAS S"pvFld.CurrentPage = strFilterEnd Sub |
