Como filtrar a tabela dinâmica com base em um valor de célula específico no Excel?

Normalmente, estamos filtrando dados em uma Tabela Dinâmica selecionando itens na lista suspensa, conforme mostrado na captura de tela abaixo. Na verdade, você pode filtrar uma tabela dinâmica com base no valor em uma célula específica. O método VBA neste artigo o ajudará a resolver o problema.

Filtrar Tabela Dinâmica com base em um valor de célula específico com código VBA

Filtrar Tabela Dinâmica com base em um valor de célula específico com código VBA

O código VBA a seguir pode ajudá-lo a filtrar uma Tabela Dinâmica com base em um valor de célula específico no Excel. Faça o seguinte.

1. Insira um valor com base no qual filtrará a Tabela Dinâmica em uma célula antecipadamente (aqui eu seleciono a célula H6).

2. Abra a planilha que contém a Tabela Dinâmica que você filtrará por valor de célula. Em seguida, clique com o botão direito na guia da planilha e selecione Exibir código no menu de contexto. Veja a imagem:

3. Na abertura Microsoft Visual Basic para Aplicações janela, copie o código do VBA abaixo para a janela de código.

Código VBA: Filtrar Tabela Dinâmica com base no valor da célula

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable2")
    Set xPFile = xPTable.PivotFields("Category")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub

Notas: No código,

1) "Sheet1”É o nome da planilha.

2) "Tabela Dinâmica 2”É o nome da Tabela Dinâmica.

3) O campo de filtragem na tabela dinâmica é denominado "Categoria".

4) O valor que você deseja filtrar na tabela dinâmica é colocado na célula H6.

Você pode alterar os valores das variáveis acima conforme necessário.

4. aperte o outro + Q chaves para fechar o Microsoft Visual Basic para Aplicações janela.

Em seguida, a Tabela Dinâmica é filtrada com base no valor na célula H6, conforme a captura de tela abaixo:

Você pode alterar o valor da célula para outros conforme necessário.

Nota: Os valores digitados na célula H6 devem corresponder exatamente aos valores da lista suspensa Categoria da Tabela Dinâmica.

As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2021 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Recursos completos de avaliação gratuita de 30 dias. Garantia de devolução do dinheiro em 60 dias.

Leia mais... Download grátis... Compra...

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!

Leia mais... Download grátis... Compra...

Comentários (23)

Ainda não há classificações. Seja o primeiro a avaliar!

Daniel

cerca de 4 anos atrás

#25756

Este comentário foi feito pelo moderador no site

Usando este código (atualizado para minhas variáveis, é claro), ao alterar o campo, o filtro muda momentaneamente para o correto e depois se limpa quase imediatamente. Tentando descobrir por que está fazendo isso (querendo saber se tem algo a ver com os ClearAllFilters no final do sub?)

Resposta

Justin

cerca de 4 anos atrás

#26177

Este comentário foi feito pelo moderador no site

Como você faria isso com um filtro de relatório que tem uma hierarquia?

Resposta

Ignacia

cerca de 3 anos atrás

#30976

Este comentário foi feito pelo moderador no site

Ei! Obrigado pela sua macro.

Eu estava tentando usá-lo para mais de uma tabela dinâmica na mesma página, mas não funciona. Eu escrevi assim:

Private Sub Worksheet_Change (ByVal Target As Range)
Dim xPTable1 como tabela dinâmica
Dim xPFile1 como PivotField
Dim xStr1 como string
On Error Resume Next
Se Intersect(Target, Range("D7")) não for nada, então Exit Sub
Application.ScreenUpdating = False
Set xPTable1 = Worksheets("BUSCADOR").PivotTables("PV_ETAPA1")
Definir xPFile1 = xPTable1.PivotFields("ETAPA1")
xStr1 = Alvo.Texto
xPFile1.ClearAllFilters
xPFile1.CurrentPage = xStr1
Application.ScreenUpdating = True

Dim xPTable2 como tabela dinâmica
Dim xPFile2 como PivotField
Dim xStr2 como string
On Error Resume Next
Se Intersect(Target, Range("G7")) não for nada, então Exit Sub
Application.ScreenUpdating = False
Set xPTable2 = Worksheets("BUSCADOR").PivotTables("PV_ETAPA2")
Definir xPFile2 = xPTable2.PivotFields("ETAPA2")
xStr2 = Alvo.Texto
xPFile2.ClearAllFilters
xPFile2.CurrentPage = xStr2
Application.ScreenUpdating = True

End Sub

Talvez você possa me ajudar!

Obrigado antecipadamente!

Resposta

michael Ignacia

cerca de 2 anos atrás

#30977

Este comentário foi feito pelo moderador no site

Hi

obrigado pela macro

Estou tentando a mesma coisa, mas não consigo fazê-lo funcionar em 2 tabelas. ambos estão olhando para a mesma célula apenas 2 tabelas dinâmicas diferentes

obrigado

Resposta

Oksana michael

cerca de 1 ano atrás

#30978

Este comentário foi feito pelo moderador no site

Você precisa alterar o nome da Tabela Dinâmica. Cada tabela dinâmica tem um nome diferente. para obter isso, clique com o botão direito do mouse no pivô e selecione as configurações da tabela dinâmica, o nome estará no topo

Resposta

Ambrose Oksana

cerca de 3 semanas atrás

#42326

Este comentário foi feito pelo moderador no site

Bom Dia,

Je ne ne compreende pas comment ajouter le nom du second TCD dans la macro pour que cela fonctionne sur les dois.
Pourriez-vous m'aider?

obrigado

Resposta

Aravind

cerca de 2 anos atrás

#32775

Este comentário foi feito pelo moderador no site

Olá, por algum motivo, esta macro depois de entrar na página básica visual, não aparece. Não consigo ativar/executar esta macro, verifiquei todas as configurações da central de confiança, mas nada acontece, por favor me ajude

Resposta

Urze

cerca de 2 anos atrás

#33165

Este comentário foi feito pelo moderador no site

Olá, não estou conseguindo fazer isso funcionar. A célula à qual quero me referir é extraída de uma fórmula - seria por isso que o filtro não pode encontrá-la, pois está olhando para a fórmula em vez do valor que a fórmula retorna?Agradecemos antecipadamenteHeather McDonagh

Resposta

Sabrina Urze

cerca de 1 ano atrás

#33166

Este comentário foi feito pelo moderador no site

Oi Heather, você encontrou uma solução. Eu tenho apenas o mesmo problema.

Resposta

AKTaylorGenericName

cerca de 2 anos atrás

#33770

Este comentário foi feito pelo moderador no site

Consegui modificar/filtrar 3 pivôs diferentes que estão na mesma aba. Também adicionei uma linha no meu conjunto de dados "Nenhum dado encontrado", caso contrário, deixei o filtro para "TODOS", o que eu não queria. O acima foi uma grande ajuda para me ganhar Kudos com a gestão, então eu queria compartilhar. Observe que (All) diferencia maiúsculas de minúsculas demorei um pouco para descobrir isso.
Private Sub Worksheet_Change (ByVal Target As Range)
'teste
Dim xPTable como tabela dinâmica
Dim xPFile como PivotField
Dim xStr As String

Dim x2PTable como tabela dinâmica
Dim x2PFarquivo como PivotField
Dim x2Str como string

Dim x3PTable como tabela dinâmica
Dim x3PFarquivo como PivotField
Dim x3Str como string

On Error Resume Next
If Intersect(Target, Range("a2:e2")) não é nada, então Exit Sub

Application.ScreenUpdating = False

'tbl-1
Set xPTable = Worksheets("Gráfico").PivotTables("PivotTable1")
Set xPFile = xPTable.PivotFields("Departamento MR - Departamento")
xStr = Alvo.Texto
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
If xPFile.CurrentPage = "(All)" Então xPFile.CurrentPage = "Nenhum dado encontrado"

'tbl-2
Set x2PTable = Worksheets("Graphical").PivotTables("PivotTable2")
Set x2PFile = x2PTable.PivotFields("Departamento MR - Departamento")
x2Str = Alvo.Texto
x2PFile.ClearAllFilters
x2PFile.CurrentPage = x2Str
Se x2PFile.CurrentPage = "(All)" Então x2PFile.CurrentPage = "Nenhum dado encontrado"

'tbl-3
Set x3PTable = Worksheets("Graphical").PivotTables("PivotTable3")
Set x3PFile = x3PTable.PivotFields("Departamento MR - Departamento")
x3Str = Alvo.Texto
x3PFile.ClearAllFilters
x3PFile.CurrentPage = x3Str
Se x3PFile.CurrentPage = "(All)" Então x3PFile.CurrentPage = "Nenhum dado encontrado"

Application.ScreenUpdating = True

End Sub

Resposta

Desenhou

cerca de 2 anos atrás

#34435

Este comentário foi feito pelo moderador no site

Isso é possível com o Google Sheets? Se sim, como?

Resposta

Vibhor Desenhou

cerca de 1 ano atrás

#34436

Este comentário foi feito pelo moderador no site

O Planilhas Google não exigirá nenhuma tabela dinâmica. você pode executar diretamente através da função de filtro

Resposta

sdas34

cerca de 2 anos atrás

#34609

Este comentário foi feito pelo moderador no site

Gostaria de usar vários códigos de alteração de planilha na mesma planilha. Como fazer isso? Meu código está como abaixo:
Private Sub Worksheet_Change (ByVal Target As Range)
'Filtro de tabela dinâmica baseado no valor da célula
Dim xPTable como tabela dinâmica
Dim xPFile como PivotField
Dim xStr As String
On Error Resume Next
If Intersect(Target, Range("D20:D21")) não é nada, então Exit Sub
Application.ScreenUpdating = False
Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable2")
Definir xPFile = xPTable.PivotFields("Designação")
xStr = Alvo.Texto
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True
End Sub

Sub-Planilha Privada_Change2(ByVal Target As Range)
'Filtro de tabela dinâmica baseado no valor da célula 2
Dim xPTable como tabela dinâmica
Dim xPFile como PivotField
Dim xStr As String
On Error Resume Next
If Intersect(Target, Range("H20:H21")) não é nada, então Exit Sub
Application.ScreenUpdating = False
Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable2")
Definir xPFile = xPTable.PivotFields("Oferta")
xStr = Alvo.Texto
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True
End Sub

Resposta

Report

Fernando

cerca de 1 ano atrás

#35024

Este comentário foi feito pelo moderador no site

Olá, gostaria de saber se filtrar mais de uma categoria como poderia ser?

Resposta

Oksana

cerca de 1 ano atrás

#35785

Este comentário foi feito pelo moderador no site

E se eu quiser vincular a célula de seleção a uma guia diferente? Este é o meu código até agora
Private Sub Worksheet_Change (ByVal Target As Range)
Dim xPTable1 como tabela dinâmica
Dim xPFile1 como PivotField
Dim xStr1 como string
On Error Resume Next
Se Intersect(Target, Range("B1")) não for nada, então Exit Sub
Application.ScreenUpdating = False
Set xPTable1 = Worksheets("SM_SKU PIVOTS").PivotTables("PivotTable1")
Definir xPFile1 = xPTable1.PivotFields("Geografia")
xStr1 = Alvo.Texto
xPFile1.ClearAllFilters
xPFile1.CurrentPage = xStr1
Application.ScreenUpdating = True

Dim xPTable2 como tabela dinâmica
Dim xPFile2 como PivotField
Dim xStr2 como string
On Error Resume Next
Se Intersect(Target, Range("B1")) não for nada, então Exit Sub
Application.ScreenUpdating = False
Set xPTable2 = Worksheets("SM_SKU PIVOTS").PivotTables("PivotTable4")
Definir xPFile2 = xPTable2.PivotFields("Geografia")
xStr2 = Alvo.Texto
xPFile2.ClearAllFilters
xPFile2.CurrentPage = xStr2
Application.ScreenUpdating = True

Dim xPTable3 como tabela dinâmica
Dim xPFile3 como PivotField
Dim xStr3 como string
On Error Resume Next
Se Intersect(Target, Range("B1")) não for nada, então Exit Sub
Application.ScreenUpdating = False
Set xPTable3 = Worksheets("SM_SKU PIVOTS").PivotTables("PivotTable8")
Definir xPFile3 = xPTable3.PivotFields("Geografia")
xStr3 = Alvo.Texto
xPFile3.ClearAllFilters
xPFile3.CurrentPage = xStr3
Application.ScreenUpdating = True

End Sub

Resposta

Alex

cerca de mês 7 atrás

#38505

Este comentário foi feito pelo moderador no site

Olá!

Sou novo com VBA e gostaria de ter um código para selecionar um filtro pivô baseado em um intervalo de células.
Como posso alterar "CurrentPage" para ser um valor de intervalo?
Obrigado!!
-------------------------------------------------- -----------------------------------------
Sub ImprimirTour()

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Bereich 1].[Tour].[Tour]"). _
Limpar todos os filtros
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Bereich 1].[Tour].[Tour]"). _
CurrentPage = "[Bereich 1].[Tour lt. Anlieferungstag].&[4001-01]"
End Sub

Resposta

Lança

cerca de mês 6 atrás

#38840

Este comentário foi feito pelo moderador no site

Muito obrigado por este código! Consegui funcionar depois de ajustar para atender meus campos, mas depois de formatar algumas alterações na minha planilha agora não funciona! Mudei de A1 para B1, mudei a formatação de algumas células para destacar, etc. Nada muito louco, mas agora não atualiza quando mudo o texto em B1. Alguém tem alguma ideia?

Private Sub Worksheet_Change (ByVal Target As Range)
'teste
Dim xPTable como tabela dinâmica
Dim xPFile como PivotField
Dim xStr As String

Dim x2PTable como tabela dinâmica
Dim x2PFarquivo como PivotField
Dim x2Str como string

Dim x3PTable como tabela dinâmica
Dim x3PFarquivo como PivotField
Dim x3Str como string

On Error Resume Next
Se Intersect(Target, Range("b1")) não for nada, então Exit Sub

Application.ScreenUpdating = False

'tbl-1
Set xPTable = Worksheets("Relatório de Linha").PivotTables("PivotTable7")
Set xPFile = xPTable.PivotFields("Fonte Utopia")
xStr = Alvo.Texto
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr

'tbl-2
Set x2PTable = Worksheets("Relatório de Linha").PivotTables("PivotTable2")
Set x2PFile = x2PTable.PivotFields("Utopia Source")
x2Str = Alvo.Texto
x2PFile.ClearAllFilters
x2PFile.CurrentPage = x2Str

'tbl-3
Set x3PTable = Worksheets("Relatório de Linha").PivotTables("PivotTable3")
Set x3PFile = x3PTable.PivotFields("Utopia Source")
x3Str = Alvo.Texto
x3PFile.ClearAllFilters
x3PFile.CurrentPage = x3Str

Application.ScreenUpdating = True

End Sub

Resposta

cristal Lança

cerca de mês 6 atrás

#38901

Este comentário foi feito pelo moderador no site

Olá Lance,
Eu testei seu código e funcionou bem no meu caso. Alterar o formato da célula não afeta a operação do código.

Resposta

Report

cerca de mês 5 atrás

#39652

Este comentário foi feito pelo moderador no site

Como funciona com o Power Pivot, ao usar várias tabelas? Gravei macro alterando o valor no filtro. Fez algumas alterações para fazer o código acima funcionar. Mas lança erro de incompatibilidade de tipo. Não importa o que eu faça.

Resposta

cristal DK

cerca de mês 5 atrás

#39718

Este comentário foi feito pelo moderador no site

Olá DK,
O método não funciona para Power Pivot. Desculpe pela inconveniência.

Resposta

Report

Ambrose

cerca de 3 semanas atrás

#42309

Este comentário foi feito pelo moderador no site

Bom Dia,
Muito obrigado por essas explicações.

J'aimerai use un filtre (1 cellule) en F4 par exemple qui filtrerait deux TCD qui sont sur la même feuille.

Ele funciona muito bem com um TCD mais dès que j'ensaye de combiner le segundo, ça ne marche pas.
Você poderia me ajudar?

Muito obrigado
Ambrose

Resposta

Ambrose

cerca de 3 semanas atrás

#42324

Este comentário foi feito pelo moderador no site

Bom Dia,

Merci beaucoup pour cette explication qui marche parfaitement.
Em revanche, j'aimerais pouvoir pouvoir use ce code pour pouvoir filtrer deux tableaux croisés dynamiques en meme tempoqui sont sur la meme feuille. La seule petite différence between les deux, c'est qu'ils n'utilisent pas les memes sources. Ao revanche, o filtro no lequel se basear neste TDC é o mesmo.

Pourriez-vous m'aider à faire évoluer ce code afin que cela fonctionne ?

Voici le code usado quand il marcha avec un TCD :

Private Sub Worksheet_Change (ByVal Target As Range)
'Atualizar por Extendoffice 20180702
Dim xPTable como tabela dinâmica
Dim xPFile como PivotField
Dim xStr As String
On Error Resume Next
Se Intersect(Target, Range("G4")) não for nada, então Exit Sub
Application.ScreenUpdating = False
Set xPTable = Worksheets("Cadrage").PivotTables("Tableau croisé dynamique7")
Set xPFile = xPTable.PivotFields("N°PROJET")
xStr = Alvo.Texto
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True
End Sub

Muito obrigado

Resposta

cristal Ambrose

cerca de 3 semanas atrás

#42387

Este comentário foi feito pelo moderador no site

Olá Ambroise,

Desculpe, é difícil alterar este código para atender às suas necessidades. Se você deseja filtrar várias tabelas dinâmicas com um único filtro, os métodos deste artigo abaixo podem ser úteis:
Como conectar um único slicer a várias tabelas dinâmicas no Excel?

Resposta

Report

Não há comentários postados aqui ainda

Deixe o seu comentário

Postando como convidado

Entrar

Nome (obrigatório)

Email (obrigatório)

Avalie esta postagem:

Limpar

0 Personagens

Eu concordo com o termos e condições