Como criar uma tabela dinâmica com várias planilhas?

Uma tabela dinâmica é uma das melhores maneiras de resumir dados.

Você pode criar um relatório resumido em segundos a partir de milhares de linhas de dados. Mas quando você trabalha com dados do mundo real, nem tudo é perfeito.

E hoje quero compartilhar com vocês um problema comum que todos enfrentamos.

Antes de começarmos, gostaria de agradecer a Raman Girdhar pela sua pergunta. Ele está me seguindo há muito tempo.

Às vezes, recebemos ou capturamos nossos dados em pastas de trabalho diferentes e, nesse caso, criar uma tabela dinâmica requer um esforço extra para combinar essas várias pastas de trabalho em uma.

Só depois disso poderemos criar uma tabela dinâmica.

Portanto, neste artigo, gostaria de mostrar um processo de três etapas para criar uma tabela dinâmica usando dados de várias pastas de trabalho.

Etapas para criar uma tabela dinâmica usando dados de várias pastas de trabalho

Importante: Para isso precisamos usar o power query, portanto certifique-se de ter o power query na sua versão do Excel. Para Excel 2016, está localizado na aba Dados, e para as demais versões (2010 e 2013), é necessário instalar o complemento.

Aqui tenho quatro pastas de trabalho diferentes com dados de vendas para áreas diferentes. Você pode baixar esses arquivos aqui para acompanhar .

E certifique-se de ter todos esses arquivos em uma pasta.

Passo 1 – Combine arquivos usando o Power Query

Primeiro, precisamos combinar todos os arquivos em um único arquivo com o PowerQuery.

Vá para a guia Dados -> Obter Transformação -> Nova Consulta -> Do Arquivo -> Da Pasta.
Agora, na janela de seleção de pasta, clique em “Navegar” e selecione a pasta onde estão todos os arquivos.
Clique OK.
A partir daí você verá uma janela “Combinar Arquivos”.
E nesta janela, selecione a planilha com seus dados de todas as pastas de trabalho. (Dica: certifique-se de ter o mesmo nome de planilha em todas as pastas de trabalho).
Depois de clicar em OK, todos os dados nas pastas de trabalho serão editados no Power Query Editor.

Passo 2 – Preparar dados para tabela dinâmica

Agora precisamos fazer pequenas alterações em nossos dados para prepará-los para uma tabela dinâmica.

Se você observar os dados, aqui temos uma coluna adicional com o nome do arquivo de origem.
Clique com o botão direito nesta coluna e selecione Dividir coluna -> Por delimitador.
Na janela do delimitador, selecione personalizado, adicione “-” como delimitador e selecione “Delimitador mais à esquerda”.
Clique OK.
Depois disso, exclua a segunda coluna.
Agora renomeie a primeira coluna.
Mais uma coisa, clique com o botão direito na primeira coluna novamente e vá em Transformar -> Capitalizar cada palavra.
E agora seus dados estão prontos. Então, clique em fechar e carregar.

Passo 3 – Insira a Tabela Dinâmica

Neste ponto, temos uma nova planilha em nossa pasta de trabalho com os dados combinados de todos os quatro arquivos.

Agora é hora de criar uma tabela dinâmica a partir dela.

Selecione a tabela e vá para Inserir guia e clique no botão Tabela Dinâmica.
Aqui você obterá uma janela de inserção de tabela dinâmica.
Clique em OK na janela Inserir tabela dinâmica e você obterá uma nova tabela dinâmica em sua pasta de trabalho.

Parabéns! Você criou com sucesso uma nova tabela dinâmica a partir de arquivos diferentes.

Atualizar tabela dinâmica

Tenho certeza de que você está pensando em como atualizar sua tabela dinâmica após atualizar os arquivos de origem.

É simples. Para atualizar sua tabela dinâmica, você deve atualizar a consulta para que os dados no arquivo de origem possam ser atualizados na tabela de origem criada com o PowerQuery. E depois disso você pode atualizar sua tabela dinâmica.

Porém, a maneira mais fácil e melhor é usar o botão atualizar na guia de dados. Ele se referirá a ambos (tabela dinâmica + consulta).

Pontos importantes

Aqui listei alguns pontos que você precisa lembrar ao usar a consulta avançada para uma tabela dinâmica.

Os arquivos devem estar em uma única pasta.
Os dados devem estar no mesmo formato em todos os arquivos. Caso contrário, você precisará torná-lo utilizável após combiná-lo.
Os nomes das planilhas devem ser iguais em todos os arquivos.
Certifique-se de que não haja erros em um arquivo. Ou você pode usar a opção “Ignorar arquivos de erro”.
Não renomeie nenhuma pasta de trabalho.