Tabela Dinâmica do Excel Mostra Itens Excluídos no Filtro: Como Corrigir

Ao excluir dados da tabela de origem e atualizar uma Tabela Dinâmica, a lista suspensa de filtro pode ainda exibir itens antigos que não existem mais. Isso acontece porque o Excel armazena em cache os valores exclusivos do intervalo de dados original dentro do cache da Tabela Dinâmica. Os itens obsoletos podem poluir sua lista de filtros, dificultando encontrar entradas atuais. Este artigo explica por que o cache retém itens excluídos e fornece dois métodos confiáveis para limpá-los completamente.

Por Que Itens Antigos Permanecem no Filtro da Tabela Dinâmica Após Excluir Dados de Origem

O Excel armazena um instantâneo de todos os valores exclusivos dos dados de origem dentro do cache da Tabela Dinâmica ao criar ou atualizar o relatório. Esse cache é separado da tabela de origem. Ao excluir linhas ou alterar valores na origem, o cache não atualiza automaticamente sua lista de itens armazenados. A próxima atualização só renova os números agregados, não a lista de itens exibidos na lista suspensa de filtro.

A causa raiz é a forma como o Excel gerencia o cache da Tabela Dinâmica. O cache retém todo valor distinto que já viu para um campo, a menos que você force uma reconstrução completa do cache. O Excel faz isso para melhorar o desempenho em grandes conjuntos de dados, evitando uma varredura completa da origem toda vez que você filtra. No entanto, esse design faz com que itens excluídos permaneçam visíveis na lista de filtros indefinidamente.

Outro fator contribuinte é o uso de intervalos nomeados ou referências de tabela que encolhem após a exclusão de dados. Ao excluir linhas de um intervalo nomeado, a definição do intervalo não se contrai automaticamente. A Tabela Dinâmica ainda referencia o intervalo original, que pode agora incluir células vazias. O Excel armazena strings vazias como itens válidos, adicionando mais poluição à lista de filtros.

Passos para Remover Itens Excluídos da Lista de Filtros da Tabela Dinâmica

Dois métodos podem limpar itens de filtro obsoletos. O primeiro método usa uma opção interna do Excel que força uma atualização de layout adiada. O segundo método usa uma macro VBA simples para redefinição completa do cache. Escolha o método que se adequa ao seu fluxo de trabalho e nível de conforto técnico.

Método 1: Forçar uma Reconstrução do Cache Usando Atualização de Layout Adiada

1. Selecione qualquer célula dentro da Tabela Dinâmica
   Clique em uma célula da Tabela Dinâmica para ativar as guias Analisar e Design da Tabela Dinâmica na faixa de opções.
2. Abra a caixa de diálogo Opções da Tabela Dinâmica
   Clique com o botão direito na Tabela Dinâmica e escolha Opções da Tabela Dinâmica. Alternativamente, vá em Analisar Tabela Dinâmica > Opções no extremo esquerdo da faixa de opções.
3. Ative Adiar Atualização de Layout
   Na caixa de diálogo Opções da Tabela Dinâmica, vá para a guia Dados. Em Dados da Tabela Dinâmica, marque a caixa rotulada Adiar Atualização de Layout. Isso instrui o Excel a esperar antes de aplicar quaisquer alterações de layout.
4. Altere ligeiramente a fonte de dados
   Na mesma caixa de diálogo, vá para a seção Fonte de Dados. Clique no botão Alterar Fonte de Dados. Na caixa de diálogo Alterar Fonte de Dados da Tabela Dinâmica, não altere o intervalo. Em vez disso, clique dentro da caixa Tabela/Intervalo e pressione Enter. Essa ação força o Excel a reavaliar o intervalo de origem e reconstruir o cache.
5. Desmarque Adiar Atualização de Layout
   Volte para a guia Analisar Tabela Dinâmica. No grupo Tabela Dinâmica, desmarque a caixa de seleção Adiar Atualização de Layout. O Excel agora atualiza a Tabela Dinâmica com um cache limpo que contém apenas os itens atuais dos dados de origem.

Método 2: Limpar o Cache da Tabela Dinâmica com uma Macro VBA

1. Abra o Editor do Visual Basic
   Pressione Alt + F11 para abrir o editor VBA. No menu, vá em Inserir > Módulo para criar um novo módulo de código.
2. Cole o código da macro
   Copie e cole o seguinte código na janela do módulo:

   Sub LimparCacheTP()
       Dim tp As PivotTable
       For Each tp In ActiveSheet.PivotTables
           tp.PivotCache.MissingItemsLimit = xlMissingItemsNone
           tp.RefreshTable
       Next tp
   End Sub

3. Execute a macro
   Pressione F5 para executar a macro enquanto o cursor está dentro do código. A macro define a propriedade MissingItemsLimit como xlMissingItemsNone, que instrui o Excel a descartar todos os itens em cache que não existem mais na origem. Em seguida, atualiza cada Tabela Dinâmica na planilha ativa.
4. Salve a pasta de trabalho como um arquivo habilitado para macro
   Pressione Ctrl + S. Na caixa de diálogo Salvar Como, defina o tipo de arquivo como Pasta de Trabalho Habilitada para Macro do Excel (.xlsm). Clique em Salvar.

Se o Filtro Ainda Mostrar Itens Antigos Após a Correção Principal

Tabela Dinâmica Está Conectada a uma Fonte de Dados Externa

Quando sua Tabela Dinâmica usa uma conexão externa, como SQL Server ou Power Pivot, o comportamento do cache difere. A fonte externa pode reter itens históricos em seu próprio cache de consulta. Abra as propriedades da conexão em Dados > Consultas e Conexões. Clique com o botão direito na conexão e selecione Propriedades. Na guia Uso, marque a caixa Atualizar dados ao abrir o arquivo. Em seguida, vá para a guia Definição e clique em Editar Consulta. Adicione uma cláusula WHERE para excluir linhas excluídas no nível da fonte.

Tabela Dinâmica Baseada em OLAP de um Cubo

Tabelas Dinâmicas criadas em um cubo OLAP não armazenam itens localmente. A lista de filtros reflete os membros de dimensão atuais do cubo. Se itens excluídos ainda aparecerem, o cubo em si não foi processado. Entre em contato com seu administrador de banco de dados para processar o cubo e remover os membros obsoletos. Você não pode corrigir isso apenas no Excel.

Várias Tabelas Dinâmicas Compartilham o Mesmo Cache

Se você criou várias Tabelas Dinâmicas a partir do mesmo intervalo de origem, elas compartilham um único PivotCache. Excluir itens em uma Tabela Dinâmica não limpa o cache para as outras. Use a macro VBA do Método 2 e aplique-a a todas as planilhas. Alternativamente, clique com o botão direito em cada Tabela Dinâmica e escolha Opções da Tabela Dinâmica > guia Dados. Defina Número de itens a reter por campo como Nenhum. Essa configuração força cada Tabela Dinâmica a descartar itens órfãos ao ser atualizada.

Comparação Rápida: Atualização de Layout Adiada vs. Limpeza de Cache com VBA

Item	Atualização de Layout Adiada	Macro VBA (MissingItemsLimit)
Nível de habilidade necessário	Iniciante	Intermediário
Tempo de execução	30 segundos	10 segundos após configuração
Afeta todas as Tabelas Dinâmicas na planilha	Uma de cada vez	Todas de uma vez
Requer arquivo habilitado para macro	Não	Sim (.xlsm)
Impede permanentemente itens obsoletos	Não, deve repetir após cada alteração de dados	Não, deve executar macro após cada alteração de dados

Prevenindo Itens de Filtro Obsoletos em Tabelas Dinâmicas Futuras

Para impedir que itens antigos reapareçam, você pode ajustar a estrutura dos dados de origem. Converta seu intervalo de origem em uma tabela do Excel selecionando o intervalo e pressionando Ctrl + T. As tabelas se expandem e contraem automaticamente conforme você adiciona ou exclui linhas. Ao atualizar a Tabela Dinâmica, o Excel lê o conjunto atual de linhas da tabela e armazena em cache apenas os itens que existem. Isso elimina a necessidade de limpar manualmente o cache após cada alteração de dados.

Outra medida preventiva é definir a opção da Tabela Dinâmica Número de itens a reter por campo como Nenhum. Clique com o botão direito na Tabela Dinâmica, escolha Opções da Tabela Dinâmica, vá para a guia Dados e defina a lista suspensa como Nenhum. Isso instrui o Excel a não armazenar itens históricos. Combinado com uma fonte baseada em tabela, essa configuração mantém a lista de filtros limpa automaticamente.

Por fim, sempre atualize a Tabela Dinâmica usando o botão Atualizar Tudo na guia Dados, em vez do atalho Ctrl + Alt + F5. Atualizar Tudo força o Excel a reconstruir o cache para todas as Tabelas Dinâmicas na pasta de trabalho, não apenas a ativa. Isso reduz a chance de itens obsoletos permanecerem em caches compartilhados.