Você criou uma Tabela Dinâmica no Excel, adicionou novas linhas de dados ao intervalo de origem, atualizou a Tabela Dinâmica, mas os novos dados não aparecem. O intervalo de origem diminuiu ou permaneceu do mesmo tamanho em vez de se expandir para incluir as novas linhas. Isso acontece porque o Excel armazena o intervalo de origem como uma referência estática, a menos que você use uma Tabela ou um intervalo nomeado dinâmico. Este artigo explica por que o intervalo diminui, como corrigir com uma Tabela e como evitar que isso aconteça novamente.
Principais Conclusões: Corrigindo um Intervalo de Origem de Tabela Dinâmica que Diminui
- Converter para Tabela do Excel (Ctrl+T): Uma Tabela se expande automaticamente quando você adiciona novas linhas, então sua Tabela Dinâmica sempre incluirá novos dados após uma atualização.
- Tabela Dinâmica > Alterar Fonte de Dados: Use para expandir manualmente o intervalo de origem se não puder usar uma Tabela.
- Intervalo nomeado com OFFSET ou INDIRECT: Um intervalo nomeado dinâmico cresce com seus dados e pode ser usado como fonte da Tabela Dinâmica.
Por que o Intervalo de Origem da Tabela Dinâmica Diminui
Quando você cria uma Tabela Dinâmica a partir de um intervalo normal de células, o Excel armazena as referências exatas de células que você selecionou. Por exemplo, você seleciona Sheet1!$A$1:$D$100. Depois, adiciona linhas 101 a 150 aos seus dados. A Tabela Dinâmica ainda aponta para $A$1:$D$100 porque o Excel não expande automaticamente um intervalo estático. Quando você atualiza a Tabela Dinâmica, ela lê apenas as 100 linhas originais. As novas linhas parecem estar faltando e o intervalo de origem efetivamente diminuiu em relação aos seus dados crescentes.
Esse comportamento é proposital. O Excel trata um intervalo normal como fixo. A única maneira de tornar o intervalo de origem dinâmico é usar um dos três métodos: converter os dados em uma Tabela do Excel, usar um intervalo nomeado dinâmico com OFFSET ou INDIRECT, ou atualizar manualmente o intervalo de origem cada vez que adicionar dados. O método da Tabela é o mais confiável e não requer manutenção de fórmulas.
Passos para Corrigir um Intervalo de Origem de Tabela Dinâmica que Diminui
- Converta seu intervalo de dados em uma Tabela do Excel
Selecione qualquer célula dentro do seu intervalo de dados. Pressione Ctrl+T. Na caixa de diálogo Criar Tabela, verifique se o intervalo está correto e marque a opção “Minha tabela tem cabeçalhos”. Clique em OK. Seu intervalo agora é um objeto Tabela com um nome padrão como Tabela1. - Crie uma nova Tabela Dinâmica a partir da Tabela
Com qualquer célula da Tabela selecionada, vá em Inserir > Tabela Dinâmica. O campo Tabela/intervalo mostrará o nome da Tabela (por exemplo, Tabela1). Escolha onde colocar a Tabela Dinâmica e clique em OK. A Tabela Dinâmica agora referencia a Tabela, que se expande automaticamente quando você adiciona novas linhas. - Adicione novos dados à Tabela
Digite ou cole novas linhas diretamente abaixo da última linha da Tabela. O Excel expande a borda e a formatação da Tabela para incluir as novas linhas. Nenhuma etapa manual é necessária. - Atualize a Tabela Dinâmica
Clique com o botão direito em qualquer lugar dentro da Tabela Dinâmica e selecione Atualizar. Os novos dados aparecem na Tabela Dinâmica. O intervalo de origem não diminui mais porque a Tabela cresce dinamicamente.
Alternativa: Atualizar Manualmente o Intervalo de Origem
Se você não puder usar uma Tabela, pode expandir manualmente o intervalo de origem cada vez que adicionar dados.
- Abra a caixa de diálogo Alterar Fonte de Dados
Selecione qualquer célula na Tabela Dinâmica. Vá em Analisar Tabela Dinâmica > Alterar Fonte de Dados. - Ajuste o intervalo
Na caixa Tabela/intervalo, altere a referência de linha para incluir as novas linhas. Por exemplo, mude $A$1:$D$100 para $A$1:$D$150. Clique em OK. - Atualize a Tabela Dinâmica
Clique com o botão direito na Tabela Dinâmica e selecione Atualizar para carregar os novos dados.
Este método funciona, mas é propenso a erros se você esquecer de atualizar o intervalo. O método da Tabela é recomendado para uso contínuo.
Alternativa: Usar um Intervalo Nomeado Dinâmico
Um intervalo nomeado dinâmico usa a função OFFSET ou INDIRECT para ajustar automaticamente o tamanho do intervalo.
- Crie um intervalo nomeado dinâmico
Vá em Fórmulas > Gerenciador de Nomes. Clique em Novo. Na caixa Nome, digite IntervaloDados. Na caixa Refere-se a, insira esta fórmula: =OFFSET(Sheet1!$A$1;0;0;CONT.NÚM(Sheet1!$A:$A);CONT.NÚM(Sheet1!$1:$1)). Esta fórmula conta células não vazias na coluna A para linhas e na linha 1 para colunas. Ajuste o nome da planilha e as colunas conforme seus dados. - Crie uma Tabela Dinâmica usando o intervalo nomeado
Vá em Inserir > Tabela Dinâmica. Na caixa Tabela/intervalo, digite IntervaloDados. Clique em OK. A Tabela Dinâmica usa o intervalo dinâmico. - Adicione dados e atualize
Adicione novas linhas aos seus dados. Clique com o botão direito na Tabela Dinâmica e selecione Atualizar. O intervalo dinâmico se expande para incluir as novas linhas.
A função OFFSET é volátil e recalcula sempre que o Excel recalcula. Para conjuntos de dados grandes, isso pode tornar sua pasta de trabalho mais lenta. O método da Tabela evita esse custo de desempenho.
Se a Tabela Dinâmica Ainda Mostrar o Intervalo Errado
O intervalo de origem da Tabela Dinâmica ainda é estático após converter para Tabela
Se você converteu seus dados em uma Tabela após criar a Tabela Dinâmica, a Tabela Dinâmica ainda referencia o intervalo estático original. Você deve apontar a Tabela Dinâmica para o nome da Tabela. Selecione qualquer célula na Tabela Dinâmica, vá em Analisar Tabela Dinâmica > Alterar Fonte de Dados e digite o nome da Tabela (por exemplo, Tabela1) na caixa Tabela/intervalo. Clique em OK e depois atualize.
Novas linhas estão fora do intervalo da Tabela
Se você colar dados abaixo da Tabela, mas deixar uma linha em branco entre a Tabela e os novos dados, o Excel não expande a Tabela. Exclua a linha em branco para que os novos dados toquem a Tabela. A borda da Tabela se expandirá para incluir as novas linhas.
A fórmula do intervalo nomeado retorna um intervalo errado
Se a fórmula CONT.NÚM no seu intervalo nomeado dinâmico contar células em branco ou incluir linhas extras, o intervalo pode ser muito grande ou muito pequeno. Verifique se a coluna A não tem lacunas nos dados. Se a coluna A contiver espaços em branco, use uma coluna diferente que tenha dados em todas as linhas.
Intervalo Estático vs Tabela do Excel vs Intervalo Nomeado Dinâmico
| Item | Intervalo Estático | Tabela do Excel | Intervalo Nomeado Dinâmico |
|---|---|---|---|
| Esforço de configuração | Nenhum (padrão) | Ctrl+T uma vez | Requer fórmula no Gerenciador de Nomes |
| Expansão automática com novas linhas | Não | Sim | Sim |
| Atualização necessária após adicionar dados | Sim | Sim | Sim |
| Impacto no desempenho | Nenhum | Mínimo | OFFSET volátil pode tornar pastas grandes mais lentas |
| Melhor para | Dados que nunca mudam de tamanho | Maioria dos cenários com dados crescentes | Quando Tabelas não são permitidas na pasta de trabalho |
O método da Tabela do Excel é a correção mais simples e confiável para um intervalo de origem de Tabela Dinâmica que diminui. Não requer fórmulas nem atualizações manuais de intervalo.
Agora você pode evitar que o intervalo de origem da sua Tabela Dinâmica diminua convertendo seus dados em uma Tabela do Excel antes de criar a Tabela Dinâmica. Se você já tem uma Tabela Dinâmica, use Alterar Fonte de Dados para apontá-la para o nome da Tabela. Para pastas de trabalho que não podem usar Tabelas, um intervalo nomeado dinâmico com OFFSET ou INDIRECT funciona como alternativa. Como dica avançada, você pode nomear sua Tabela (Design da Tabela > Nome da Tabela) e referenciar esse nome em qualquer fórmula ou gráfico para tornar todos os objetos da sua pasta de trabalho dinâmicos.