Crie uma tabela dinâmica a partir de planilhas diferentes

Com uma tabela dinâmica, você resume seus dados em segundos. Você pode criar relatórios, analisar dados e compartilhá-los facilmente com outras pessoas.

Essa é a beleza das tabelas dinâmicas.

Mas aqui está uma reviravolta:

Normalmente, ao criar uma tabela dinâmica, você pode selecionar dados de origem de uma única tabela em uma planilha, porque o Excel não permite fazer referência a planilhas diferentes.

Mas às vezes precisamos usar dados de origem de várias planilhas para criar uma tabela dinâmica.

Então, hoje eu gostaria de compartilhar com vocês etapas simples para usar várias planilhas em uma tabela dinâmica.

O problema!

Digamos que você queira analisar os dados de vendas da sua empresa e extrair dados anuais dos últimos 4 anos.

É assim que você obtém o despejo de dados no Excel.

Observe que os dados nas 4 planilhas são diferentes, mas a estrutura é exatamente a mesma, ou seja, o mesmo número de colunas e cabeçalhos.

Agora, para analisar esses dados, você precisa criar um único relatório de tabela dinâmica a partir dessas múltiplas planilhas.

Alguns dos métodos comuns são:

1. Copie e cole manualmente os dados de cada planilha e crie um único conjunto de dados em uma nova planilha.
2. Use o código VBA para consolidar automaticamente dados de várias planilhas.
3. Ou você pode consolidar várias planilhas em uma única planilha usando a opção de consolidação do Excel.

Mas o fato é que esses métodos exigem codificação, cópia e colagem ou são repetitivos.

A solução

Aqui discutirei um novo método usando consulta da Microsoft que é dinâmico, robusto e simples.

Acredite, você vai adorar!

Usando o Microsoft Query, você pode criar uma tabela dinâmica a partir de várias planilhas.

Baixe este arquivo de dados para acompanhar.

Etapas para criar uma tabela dinâmica a partir de várias planilhas

Aqui temos passos simples que você pode seguir e antes disso baixe este arquivo aqui para seguir.

• Primeiro, selecione todos os dados em cada planilha e nomeie-os.
  • Dados de 2005 nomeados como – Ano 2005
  • Dados de 2006 nomeados – Ano de 2006
  • Dados de 2007 nomeados – Ano de 2007
  • Dados de 2007 nomeados – Ano de 2007

• Depois disso, aplique tabelas de dados a todos os dados em quatro planilhas.
  • Selecione qualquer célula no intervalo de dados.
  • Use Ctrl + T para converter os dados de cada planilha em uma tabela.
  • Certifique-se de que “Minha tabela tem cabeçalhos” esteja marcado sempre.
  • Repita esta operação durante os 4 anos (folhas).

• Vamos começar consolidando esses dados em uma nova planilha (atalho para adicionar uma nova planilha: Shift + F11).
• Na guia Dados, clique em ‘De outras fontes’ -> Escolha ‘Do Microsoft Query’.

• Na caixa Escolher fonte de dados:
  • Clique em Arquivos Excel e pressione OK.
  • Selecione o caminho do seu arquivo Excel e selecione o arquivo e clique em OK.
  • Os intervalos nomeados aparecerão na caixa ‘Assistente de consulta – Escolher colunas’.

• No assistente de consulta:
  • Arraste cada intervalo nomeado (usando o botão de seta) para “Colunas na sua caixa de consulta”.
  • Clique em Avançar – Esqueça o erro.
  • E clique em OK

• Agora que temos todos os dados em nosso editor de consultas, só falta combinar os dados de todos os intervalos nomeados. Para fazer isso, clique no pequeno botão SQL.

• Na caixa SQL, exclua todo o texto e escreva uma nova consulta e clique em OK.

Selecione * do ano 2005Union allSelecione * do ano 2006Union allSelect * do ano 2007Union allSelecione * do ano 2008

• Agora a tabela que aparece na tela contém os dados de todas as 4 planilhas.
• Basta ir até a aba Arquivo e importar esta tabela para o Excel.
• No final, importe os dados de volta para o Excel como uma tabela dinâmica.
  • No menu Arquivo -> clique em Retornar dados para o Microsoft Excel.
  • Você pode ver que no total (nas 4 planilhas) temos 592 registros.
  • Além disso, se você adicionar mais dados a qualquer uma das quatro planilhas, a tabela dinâmica será atualizada assim que você a atualizar.