Consolidar no Excel – Como Juntar Várias Planilhas

Como consolidar no Excel? Se você está procurando por maneiras de combinar planilhas, consolidar dados de múltiplas planilhas ou simplesmente combinar várias planilhas em uma só, então você está no lugar certo!

Um dos problemas bastante comuns que os usuários de Excel enfrentam, é a possibilidade de mesclar várias planilhas em uma sem precisa copiar e colar.
Neste artigo, você irá conferir dois cenários mais comuns: consolidar dados numéricos (soma, contagem, média, etc.) e mesclar planilhas (ou seja, copiar dados de várias planilhas em uma). Confira a seguir as diferentes opções e veja qual delas poderá atender a sua necessidade.

Como consolidar dados de várias planilhas em uma única planilha

Provavelmente, a maneira mais simples e rápida de consolidar dados no Excel (que estão localizados em uma pasta de trabalho ou em várias pastas de trabalho) é usando o recurso de Consolidar, integrado ao Excel.

Vamos considerar o seguinte exemplo. Suponha que você tenha vários relatórios dos escritórios regionais de sua empresa e queira consolidar esses números em uma planilha mestre para ter um relatório resumido com os totais de vendas de todos os produtos.

Como você pode observar na imagem abaixo, as três planilhas a serem consolidadas possuem uma estrutura de dados semelhante, mas diferentes números de linhas e colunas:

Deste modo, para consolidar os dados em uma única planilha, execute as seguintes etapas:




1. Organize os dados de origem corretamente. Para que o recurso que o recurso Consolidar do Excel funcione corretamente. Para isto, certifique-se de que:

• Cada intervalo (conjunto de dados) que você deseja consolidar esteja localizado em uma planilha separada.
• Não haja nenhum dado na planilha onde você planeja criar os dados consolidados.
• Cada planilha deve o mesmo layout e cada coluna deve ter um cabeçalho que contenha dados semelhantes.
• Não existem linhas ou colunas em branco em nenhuma lista.

2. Na sua planilha mestre, (onde os dados serão consolidados) clique na célula superior esquerda onde deseja que os dados consolidados apareçam. Acesse a guia Dados > Consolidar.

Dica extra: Recomenda-se consolidar os dados em uma planilha em branco. Se sua planilha mestre já tiver alguns dados, certifique-se de que haja espaço suficiente (linhas e colunas em branco) para conter os dados mesclados.

3. Ao seguir os passos anteriores, o Excel irá abrir a caixa de diálogo Consolidar. Defina as seguintes configurações de consolidação:

• No campo Função, selecione uma das funções de resumo que deseja usar para consolidar seus dados (contagem, média, máximo, mínimo, etc.). Neste exemplo, selecionamos Soma.
• No campo Referência, clique no ícone Recolher caixa de diálogo e selecione o intervalo na primeira planilha.
• Em seguida, clique no botão Adicionar para que esse intervalo seja adicionado a Todas as referências. Repita esta etapa para todos os intervalos que deseja consolidar.

Se uma ou algumas das planilhas estiverem em outra pasta de trabalho, clique no botão Procurar para localizar a pasta de trabalho desejada.

4. Ainda na caixa de diálogo Consolidar, defina as configurações de atualização selecionando qualquer uma das seguintes opções:

• Marque as caixas Linha superior e / ou Coluna esquerda se quiser que os rótulos de linha e / ou coluna dos intervalos de origem sejam copiados para a consolidação.
• Selecione a caixa Criar vínculos com dados de origem se desejar que os dados consolidados sejam atualizados automaticamente sempre que os dados de origem forem alterados. Nesse caso, o Excel criará links para suas planilhas de origem, bem como um esboço conforme imagem abaixo.

Ao expandir um determinado grupo (clicando no símbolo de mais) e, em seguida, clicar na célula com um determinado valor, um link para os dados de origem será exibido na barra de fórmulas.

Como você pode observar, o recurso Consolidar do Excel é muito útil para reunir dados de várias planilhas. No entanto, ele tem algumas limitações. Em particular, ele funciona apenas para valores numéricos e sempre resume esses valores de uma forma ou de outra (soma, contagem, média, etc.)

Por outro lado, se você deseja mesclar planilhas no Excel copiando seus dados, a opção de consolidação não é a melhor opção. Para combinar apenas algumas folhas, você pode não precisar de mais nada além do bom e velho copiar / colar. Mas se você deseja mesclar dezenas de planilhas, os erros com a cópia e colagem manual podem ser inevitáveis. Neste caso, você poderá usar uma das seguintes opções para automatizar a consolidação dos dados.

Como juntar várias planilhas do Excel em uma

No geral, existem outras duas maneiras de mesclar planilhas do Excel em uma, sem copiar e colar manualmente os dados. São elas:

• Usando uma Macro no VBA
• Usando o Power Query

Consolidar no Excel usando um código VBA

Se você é um usuário avançado do Excel e se sente confortável com macros e VBA, então poderá combinar várias planilhas do Excel em uma usando algum script VBA.

Por exemplo, suponha que você tem três planilhas contendo dados que deseja consolidar em uma única Planilha conforme mostra imagem abaixo.

Uma opção seria desenvolver uma macro no VBA capaz de copiar os dados de todas as planilhas e colar em uma única planilha, mantendo apenas um único cabeçalho.

Existem diferentes maneiras de se fazer esse trabalho usando o VBA. Abaixo temos um código que você poderá adaptar para a sua necessidade.

Dica: Se você ainda não sabe como trabalhar com Macros no VBA, então confira o tutorial: Como Gravar Macros no Excel – Um Guia Passo a Passo

Sub Consolidar_Dados()

'Copia os dados da primeira Planilha
Plan1.Select
Range("A1").CurrentRegion.Select
Selection.Copy

'Cola os dados na Planilha Matriz a partir da célula A1
Plan4.Select
Range("A1").Select
ActiveSheet.Paste

'Copia os dados da segunda Planilha a partir da célula A2 para ignorar o cabeçalho
Plan2.Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

'Identifica a primeira linha vazia na Planilha Matriz e cola os dados copiados da Segunda Planilha
Plan4.Select
linha = Sheets("Plan4").Range("A1048576").End(xlUp).Row + 1
Range("A" & linha).Select
ActiveSheet.Paste

'Copia os dados da Terceira Planilha a partir da célula A2 para ignorar o cabeçalho
Plan3.Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

'Identifica a primeira linha vazia na Planilha Matriz e cola os dados copiados da Terceira Planilha
Plan4.Select
linha = Sheets("Plan4").Range("A1048576").End(xlUp).Row + 1
Range("A" & linha).Select
ActiveSheet.Paste

'Desenvolvido por exceleasy.com.br
End Sub

Lembre-se de que, para que o código VBA funcione corretamente, todas as planilhas de origem devem ter a mesma estrutura, os mesmos títulos de coluna e a mesma ordem de coluna.

Consolidar no Excel usando o Power Query

O Power Query é uma tecnologia muito poderosa para combinar e refinar dados no Excel.

Importante: O Power Query pode ser usado como um suplemento no Excel 2010 e 2013 e é um recurso embutido a partir do Excel 2016. Com base na sua versão, algumas imagens podem parecer diferentes (as imagens usadas neste tutorial são do Excel 2019).

Para combinar dados de planilhas diferentes usando o Power Query, é necessário ter os dados em formato de tabela do Excel (ou pelo menos em intervalos nomeados). Se os dados não estiverem em uma tabela do Excel, o método mostrado a seguir, não irá funcionar.

Então, suponha que você tenha quatro planilhas diferentes – Janeiro, Fevereiro, Março e Abril.

Cada uma dessas planilhas contém os dados estruturados em formato de tabela de modo consistente (ou seja, os cabeçalhos são os mesmos).

Agora, siga as etapas abaixo para combinar várias planilhas com tabelas do Excel usando o Power Query:

1. Acesse a guia Dados > Grupo Obter e Transformar Dados > Obter Dados.

2. Escolha a opção De Outras Fontes > Consulta em Branco.

3. No editor de consultas, digite a seguinte fórmula na barra de fórmulas: =Excel.CurrentWorkbook().

Note que as fórmulas do Power Query diferenciam maiúsculas de minúsculas, portanto, é necessário usar a fórmula exata conforme mencionado (caso contrário, você receberá um erro).

4. Pressione a tecla Enter. Isso mostrará todos os nomes das tabelas em toda a pasta de trabalho (também mostrará os intervalos nomeados e / ou conexões, caso existam na pasta de trabalho).

Opcional: Neste exemplo, desejamos combinar todas as tabelas. Se você deseja combinar apenas tabelas específicas, basta clicar no ícone suspenso no cabeçalho do nome e selecionar aquelas que deseja combinar. Da mesma forma, se você nomeou intervalos ou conexões, e deseja apenas combinar tabelas, você pode remover esses intervalos nomeados também.

5. Na célula do cabeçalho Content, clique na seta de duas pontas conforme mostra imagem abaixo:

6. Selecione as colunas que você deseja combinar. Se você deseja combinar todas as colunas, certifique-se de que (Selecionar todas as colunas) esteja marcado.

7. Desmarque a opção ‘Use o nome da coluna original como prefixo’. Para finalizar, clique em OK.

Como resultado, você terá as tabelas combinadas em uma única tabela.

Observe que a última coluna (mais à direita) tem o nome das tabelas do Excel (Tabela 1, Tabela 2, Tabela 3, Tabela 4). Este é um identificador que nos informa qual registro veio de qual Tabela do Excel. Caso você tenha nomeado suas tabelas para um nome personalizado, esses nomes irão aparecer neste respectivo campo.

Exportando os dados do Power Query para a Planilha

Agora que você tem os dados combinados de todas as planilhas no Power Query, pode carregá-los no Excel – como uma nova tabela em uma nova planilha.

Para fazer isso, basta seguir as etapas abaixo:

1. Tela caixa de diálogo do Power Query, acesse a guia Arquivo  e selecione:

• Fechar e carregar: Caso prefira usar a mesma Pasta de Trabalho
• Fecha e carregar para: Caso prefira transferir os dados consolidados para outra Pasta de Trabalho.

Para este exemplo, optamos por usar a opção Fechar e carregar. Note que o Excel transfere os dados consolidados para uma nova planilha na mesma Pasta de Trabalho.

Conforme dito anteriormente, o Power Query é uma ferramenta muito poderosa para combinar e refinar dados no Excel. Com isso, é bastante complexo e requer uma longa curva de aprendizado.

Caso tenha interesse em se aprofundar no assunto, recomendamos a leitura do tutorial: Combinar dados de várias fontes de dados (Power Query).

Confira também as seguintes dicas de Excel:

• Como Converter Texto em Número no Excel
• Banco de Dados no Excel – Como Criar?
• Filtros no Excel – Tutorial Completo

E você, conhece alguma outra maneira de consolidar dados no Excel? Deixe o seu comentário abaixo e continue acompanhando mais dicas de Excel em nosso site!