Note: The other languages of the website are Google-translated. Back to English

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.


Artigos relacionados:


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.
guia kte 201905

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!
parte inferior da aba do escritório
Comentários (23)
Ainda não há classificações. Seja o primeiro a avaliar!
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?)
Este comentário foi feito pelo moderador no site
Como você faria isso com um filtro de relatório que tem uma hierarquia?
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!
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
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
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
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
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
Este comentário foi feito pelo moderador no site
Oi Heather, você encontrou uma solução. Eu tenho apenas o mesmo problema.
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
Este comentário foi feito pelo moderador no site
Isso é possível com o Google Sheets? Se sim, como?
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
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
Este comentário foi feito pelo moderador no site
Olá, gostaria de saber se filtrar mais de uma categoria como poderia ser?
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
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
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
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.
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.
Este comentário foi feito pelo moderador no site
Olá DK,
O método não funciona para Power Pivot. Desculpe pela inconveniência.
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
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
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?
Não há comentários postados aqui ainda
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL