Como localizar e alterar os dados de origem de uma tabela dinâmica do Excel. Para dados de origem ausentes, etapas para tentar recriá-los. Altere as opções para salvar os dados de origem com o arquivo de tabela dinâmica.

NOTA: Para macros que ajudam a gerenciar os dados de origem, vá para a página de macros de dados de origem da tabela dinâmica. Para obter uma macro para unpivot os dados de origem, vá para a página Unpivot Excel Data Macro

Localizar e corrigir os dados de origem

Neste vídeo, você verá como localizar a tabela dinâmica fonte de dados e, em seguida, verifique a fonte de dados para se certificar de que inclui todas as linhas e colunas de que necessita. Se necessário, ajuste a fonte de dados para incluir novas linhas ou colunas.

NOTA: Por um longo solução de prazo, use uma tabela nomeada do Excel, como uma fonte de dados. Ela se ajustará automaticamente, se novas linhas forem adicionadas.

As instruções escritas estão abaixo do vídeo, e você pode baixar o arquivo de amostra para acompanhar com o vídeo.

Localize os dados de origem

Após a criação de uma tabela dinâmica, você pode adicionar novos registros aos dados de origem ou alterar os registros existentes. pasta de trabalho grande, pode ser difícil localizar a fonte exata da tabela dinâmica, se houver várias tabelas ou listas.

NOTA: Se você comprou meu suplemento Pivot Power Premium, clique em Tabela Dinâmica Informações, depois clique Vá para os dados de origem.

Siga estas etapas para encontrar os dados de origem para uma tabela dinâmica:

1. Selecione qualquer célula na tabela dinâmica.
2. Na faixa de opções, na guia Ferramentas de tabela dinâmica, clique na guia Analisar (no Excel 2010, clique na guia Opções).
3. No grupo Dados, clique na seção superior do comando Alterar fonte de dados.

A caixa de diálogo Alterar fonte de dados da tabela dinâmica é aberta e você pode ver a tabela de origem ou intervalo em a caixa Tabela / Faixa. Pode ser uma referência a uma planilha e intervalo de células, como

• Pedidos! $ A $ 1: $ H $ 9

ou um nome de tabela, como

• Sales_East

Atrás da caixa de diálogo, você pode ver o intervalo de origem na planilha, cercado por uma borda móvel.

Na captura de tela abaixo, o intervalo de dados de origem termina na linha 9 e um novo registro foi adicionado na linha 10. Esse registro não aparecerá na tabela dinâmica, a menos que a Fonte de Dados seja ajustada.

Ajustar o intervalo de dados de origem

Se os dados de origem de uma tabela dinâmica forem uma referência estática a uma planilha e intervalo específicos, ele não se ajusta automaticamente quando novos dados são adicionados. Na captura de tela acima, o intervalo da fonte de dados se refere a pedidos! $ A $ 1: $ H $ 9, e pode ser ajustado manualmente para incluir a linha 10.

Para ajustar manualmente o intervalo da fonte estática:

Você pode ajustar o intervalo da fonte quando necessário, para que inclua quaisquer novos dados, mas uma solução melhor é criar uma fonte dinâmica e baseie a tabela dinâmica nisso. As instruções estão abaixo.

Alterar os dados de origem

Ocasionalmente, pode ser necessário alterar uma tabela dinâmica, para que ela use uma fonte de dados diferente. Neste exemplo, uma nova tabela foi criada, com apenas dados para o Leste r egion. A tabela dinâmica será alterada para usar essa fonte e, em seguida, pode ser enviada para um gerente na região Leste, sem revelar os dados da região Oeste.

NOTA: Após mudar para uma tabela de origem diferente , pode ser necessário renomear alguns campos ou adicionar campos diferentes à tabela dinâmica.

Para alterar os dados de origem de uma tabela dinâmica do Excel, siga estas etapas:

1. Selecione qualquer célula na tabela dinâmica.
2. Na faixa de opções, na guia Ferramentas de tabela dinâmica, clique na guia Analisar (no Excel 2010, clique na guia Opções).
3. Nos dados grupo, clique na seção superior do comando Alterar fonte de dados.
4. A caixa de diálogo Alterar fonte de dados da tabela dinâmica é aberta e você pode ver a tabela ou intervalo de origem na caixa Tabela / Intervalo. Nesta captura de tela abaixo, a fonte de dados atual é a tabela Sales_Data, que contém dados de ambas as regiões.

   

5. Exclua a fonte de dados existente e digite um novo nome de tabela ou selecione uma nova fonte de dados e gama. A tabela / intervalo é alterado para Sales_East, onde apenas os dados do leste são listados.

   

6. Clique em OK.

Fonte dinâmica para tabela dinâmica

Em vez de usar um intervalo estático como fonte de dados da tabela dinâmica, a melhor solução é criar um intervalo dinâmico, que se ajustará em tamanho automaticamente.

Criar fonte dinâmica – Tabela Excel

Fonte dinâmica – Faixa nomeada

Fonte dinâmica – Matriz dinâmica

Use uma fonte dinâmica

Fonte dinâmica – Tabela Excel

Em vez de usar um intervalo estático como fonte de dados da tabela dinâmica, uma solução melhor é criar um intervalo dinâmico, com base em uma tabela nomeada do Excel. Existem instruções escritas aqui ou assista ao vídeo abaixo.

Anote o nome da tabela, para que você possa usá-lo como a fonte da tabela dinâmica

Fonte dinâmica – intervalo nomeado

Se você não puder usar uma Tabela do Excel como dados de origem, você cria um intervalo nomeado dinâmico, com base em uma fórmula INDEX ou OFFSET. Há instruções escritas aqui ou assista ao vídeo abaixo.

O vídeo mostra como criar um intervalo dinâmico com a função OFFSET, no Excel 2007, e as etapas são semelhantes nas versões posteriores.

NOTA: Lembre-se do nome do intervalo, para que possa usá-lo como a fonte da tabela dinâmica

Fonte dinâmica – Matriz dinâmica

Se você tiver uma versão do Excel que suporte as novas funções, como SORT ou UNIQUE, poderá usar essas funções para criar matrizes dinâmicas. Esta técnica usa apenas fórmulas – sem macros .

Na seção Download, obtenha o arquivo de amostra Filtered Source Data. Ele mostra como configurar um intervalo nomeado apenas com as linhas visíveis de uma tabela Excel nomeada.

Aqui está o filtro d dados, em uma planilha diferente, com apenas 2 repetições e 3 categorias das linhas visíveis.

Então, você pode criar uma tabela dinâmica com base apenas nos dados filtrados.

Use a fonte dinâmica para a tabela dinâmica

Depois de criar uma fonte dinâmica que contém a tabela dinâmica dados, siga estas etapas para usar esses dados de origem:

1. Selecione qualquer célula na tabela dinâmica.
2. Na faixa de opções, na guia Ferramentas de tabela dinâmica, clique em Analisar (no Excel 2010, clique na guia Opções).
3. No grupo Dados, clique na seção superior do comando Alterar fonte de dados.
4. Na caixa de diálogo Alterar fonte de dados da tabela dinâmica, desmarque a caixa Tabela / intervalo
5. Na caixa Tabela / intervalo, digite o nome da tabela ou intervalo nomeado que você criou. Neste exemplo, a fonte dinâmica é uma tabela nomeada – tblOrders.
6. Clique em OK

Salvar dados de origem com tabela dinâmica

Quando você cria uma tabela dinâmica em sua pasta de trabalho, os registros de dados de origem são salvos em uma área de memória especial – um cache dinâmico. Em seguida, quando você fecha o arquivo, o Excel pode salvar os dados de origem neste cache dinâmico ou limpar essa memória.

Existem vantagens e desvantagens em ambas as opções:

Se você salvar os dados de origem:

• O arquivo ficará maior
• O arquivo pode abrir mais rapidamente

Se você não salvar os dados de origem:

• O preenchimento será menor
• O arquivo pode abrir lentamente enquanto o cache dinâmico é reconstruído
• A tabela dinâmica deve ser atualizada após abrir o arquivo

Como alterar a configuração

Para ativar o Salvar a configuração de dados de origem ativada ou desativada:

• Clique com o botão direito do mouse em uma célula na tabela dinâmica e clique em Opções de tabela dinâmica.
• Na guia Dados, na seção Dados de tabela dinâmica , adicione ou remova a marca de seleção em Salvar dados de origem com arquivo
• Clique em OK.

Atualizar dados ao abrir

Se você optar por virar desativar a opção Salvar dados de origem com arquivo, você deve ativar a opção Atualizar dados ao abrir o arquivo. Essa configuração está logo abaixo da caixa de seleção Salvar dados de origem.

Caso contrário, você verá uma mensagem ao tentar filtrar os dados ou fazer qualquer outra alteração de layout.

• “O relatório de tabela dinâmica foi salvo sem os dados subjacentes. Use o comando Atualizar dados para atualizar o relatório. “

Se você vir essa mensagem, clique em OK e atualize manualmente a tabela dinâmica.

E para evitar o incômodo , ative a opção Atualizar dados ao abrir o arquivo.

Recriar dados de origem da tabela dinâmica

Se você acidentalmente excluir a planilha que contém os dados de origem para sua tabela dinâmica, ou se você recebeu um arquivo sem os dados da tabela dinâmica, pode ser capaz de usar o recurso Mostrar detalhes da tabela dinâmica para recriá-lo.

NOTA: Isso não funcionará para todas as tabelas dinâmicas, mas é vale a pena tentar recuperar os dados de origem.

Para tentar recriar os dados de origem para uma tabela dinâmica, siga estas etapas para usar o recurso Mostrar detalhes:

1. Certifique-se de que nenhum dos itens nos campos da tabela dinâmica esteja oculto – limpe todos os filtros e Segmentações de Dados que foram aplicados.
2. A tabela dinâmica não precisa conter todos os campos – apenas certifique-se de que haja é pelo menos um campo no Val área ues.
3. Mostre os totais gerais para linhas e colunas. Se os totais não estiverem visíveis, selecione uma célula na tabela dinâmica e, na faixa de opções, em Ferramentas de tabela dinâmica, clique na guia Analisar. No grupo Layout, clique em Totais gerais e em Ativado para Linhas e colunas.
4. Clique duas vezes na célula de total geral na parte inferior direita da tabela dinâmica. Isso deve criar uma nova planilha com os registros relacionados dos dados de origem originais.

Corrigir o extraído Dados

Se você recuperar os dados de origem, pode ser necessário fazer algumas alterações adicionais para restaurá-los à condição original.

• Renomeie a tabela, usando o nome dos dados da fonte original, se conhecido.
• Se os dados da fonte original contiverem fórmulas, você terá que recriá-los, porque o recurso Mostrar detalhes exporta apenas os dados.
• As colunas nos dados extraídos estarão na mesma ordem em que estavam nos dados de origem original.
• Os dados extraídos serão formatado com o formato de tabela padrão da pasta de trabalho. Você pode aplicar um formato de tabela diferente ou aplicar sua própria formatação.
• Observação: se você fez alterações nos dados de origem e não atualizou a tabela dinâmica, essas alterações não estarão nos dados extraídos.

Conecte-se aos dados extraídos

Se você renomear a tabela que foi criada durante o processo Mostrar detalhes e usar o mesmo nome da tabela que originalmente continha a fonte dados, a tabela dinâmica pode se conectar automaticamente aos novos dados de origem.

Caso contrário, você pode se conectar aos dados de origem recriados – siga as etapas na seção acima: Use a fonte dinâmica para a tabela dinâmica – use o nome da tabela que você deu para a nova tabela.

Baixe o arquivo de amostra

Exemplo de fonte dinâmica: Para acompanhar os tutoriais, você pode baixar o arquivo DateAmt.zip. O arquivo compactado está no formato xlsx e não contém macros.

Exemplo de dados de origem filtrada: Este exemplo é para versões do Excel que possuem as novas funções, como SORT e UNIQUE. Carregue este arquivo de amostra para ver uma tabela dinâmica criada apenas a partir das linhas visíveis, em uma tabela filtrada do Excel. O arquivo compactado está no formato xlsx e não contém macros.

Ferramentas de Tabela Dinâmica

Para economizar tempo ao construir, formatar e modificar suas tabelas dinâmicas, use as ferramentas em meu Pivot Suplemento Power Premium. Com apenas alguns cliques, você pode:

• copiar a formatação de uma tabela dinâmica e aplicá-la a outra.
• altere todos os valores de Contagem para Soma
• remova a “Soma de” de todos os títulos

e muito mais!

Não perca nossas dicas do Excel