Se o seu dia a dia envolve lidar com várias planilhas com diferentes tipos de dados, você provavelmente procura formas rápidas e eficientes de reunir dados de várias fontes. Por exemplo, se você trabalha com dados financeiros, montar um orçamento anual exige combinar as contas de vários departamentos de acordo com uma estrutura predefinida. Felizmente, o Excel tem o recurso Consolidar, que ajuda você a fazer isso sem nenhum trabalho manual, ou seja, com menos tempo e esforço da sua parte.
Neste artigo, você vai encontrar uma explicação completa do que é a consolidação de dados no Excel e um guia passo a passo de como consolidar dados de várias planilhas do Excel em uma única planilha. Como alternativa, as automações com IA da Sheetgo podem consolidar para você dados de várias fontes, incluindo Excel e Google Sheets.
O que é consolidação de dados no Excel?
O recurso Consolidar do Excel permite reunir dados de diferentes planilhas e combiná-los em uma só. Essa planilha pode então ser usada como planilha mestre, o mesmo sistema usado pela maioria das plataformas de automação. A planilha mestre fica vinculada às planilhas de origem, então sempre que uma planilha de origem muda, a planilha consolidada é atualizada.
Esse tipo de ferramenta de consolidação é muito usado por profissionais de Finanças, principalmente pelos responsáveis por planejamento e análise. Como todos os dados ficam estruturados em uma única tabela, fica muito mais fácil para qualquer pessoa da sua organização fazer a análise de que precisa.
Para uma explicação geral do conceito fora do Excel, leia o que é consolidação de dados.
Como consolidar dados no Excel?
O recurso Consolidar pode ser usado para combinar várias colunas do Excel em uma e também para consolidar linhas em uma mesma planilha do Excel. Além disso, você pode consolidar dados de diferentes planilhas em uma única planilha.
Consolidar dados de várias planilhas em uma única planilha
Digamos que você ou seu gestor precisem analisar as despesas anuais. Isso significa reunir os custos de diferentes departamentos, como RH, Marketing, Vendas etc. Cada departamento salva os dados em uma planilha diferente. Reunir todos esses dados para uma análise geral levaria tempo demais, então um recurso como o Consolidar do Excel é necessário. Veja como consolidar no Excel dados de várias planilhas em uma única planilha.
1. Crie uma planilha separada e renomeie-a como "Resumo" ou algo parecido. É nela que o Excel vai criar a tabela de resumo.
2. Clique em uma célula vazia, de preferência a do canto superior esquerdo, para indicar onde a tabela de resumo começa.
3. Acesse Dados > Consolidar.
4. Clique no menu suspenso "Função" e depois em "Soma" para somar os valores de todas as planilhas.
5. Em "Referência", clique no botão de seta para cima para selecionar os dados de origem que serão consolidados.
6. Vai aparecer a janela "Consolidar - Referência". Como os dados de origem estão na mesma pasta de trabalho, basta navegar pelas planilhas e selecionar o intervalo arrastando o cursor.
7. Como mostrado abaixo, o Excel insere automaticamente o intervalo dos dados de origem. Ele mostra o nome da planilha seguido de um ponto de exclamação, as linhas e as colunas selecionadas. Depois de selecionar, clique no botão de seta para voltar à janela principal de configurações de "Consolidar".
8. Clique em "Adicionar" para incluí-lo na lista "Todas as referências".
9. Repita as etapas 5, 6, 7 e 8 para as demais planilhas. Quando terminar, você verá a lista completa de referências, como a de baixo.
10. Marque as caixas "Linha superior" e "Coluna esquerda" para incluir os rótulos da primeira linha e da coluna mais à esquerda. Com essas caixas marcadas, o Excel combina os valores pelos rótulos, e não pela posição em cada intervalo.
11. Marque a opção "Criar vínculos com dados de origem" para que a tabela de resumo seja atualizada sempre que outro usuário alterar a planilha de origem. Caso contrário, você terá que fazer isso manualmente. Quando terminar, clique em "OK".
12. Clique nos botões "+" à esquerda para ver os dados originais a partir da nova planilha mestre.
13. Se quiser exibir essa informação original em todas as linhas, clique no botão com o número "2", logo acima do primeiro botão "+".
Os dados da coluna C se referem ao nome da pasta de trabalho que contém seus dados. Se preferir ocultá-la, basta clicar com o botão direito nela e selecionar "Ocultar". Isso só indica que essas células fazem parte de um grupo que está recolhido no momento.
Além disso, o Sheetgo oferece total transparência e controle sobre suas pastas de trabalho do Excel. Enquanto você se concentra em inserir os dados, as automações com IA do Sheetgo os consolidam no seu arquivo principal. Assim, você economiza o tempo de criar novas planilhas ou pastas de trabalho para as tabelas de resumo. Confira nosso artigo sobre como mesclar arquivos do Excel com o Sheetgo.
Como consolidar dados de várias pastas de trabalho do Excel
As etapas acima partem do princípio de que cada departamento mantém sua planilha na mesma pasta de trabalho. Se cada equipe envia o próprio arquivo, você ainda pode usar a ferramenta Consolidar. Só muda a forma de escolher cada referência.
1. Abra a pasta de trabalho de resumo e todas as pastas de trabalho de origem.
2. Na pasta de trabalho de resumo, clique na célula onde a tabela deve começar e vá em Dados > Consolidar.
3. Clique na caixa Referência, vá até a janela de uma pasta de trabalho de origem e selecione o intervalo. O Excel escreve o nome do arquivo entre colchetes antes do nome da planilha. Se um arquivo estiver fechado, clique em Procurar e selecione-o. O Excel insere o caminho dele, e você adiciona a planilha e o intervalo depois.
4. Clique em Adicionar e repita a etapa 3 para cada pasta de trabalho.
5. Marque Linha superior e Coluna esquerda se os arquivos listarem os rótulos em ordens diferentes. Marque Criar vínculos com dados de origem e clique em OK.
Com os vínculos ativados, o Excel atualiza o resumo a partir dos arquivos de origem quando você o abre e permite a atualização. Mantenha os arquivos de origem onde estão e não os renomeie, senão os vínculos se rompem.
Quando chegam arquivos novos todo mês, adicionar cada um manualmente fica demorado. Você pode consolidar arquivos do Excel automaticamente para que os novos dados cheguem ao resumo sem repetir estas etapas.
Como consolidar dados de várias linhas no Excel
As duas caixas de rótulos definem como o Excel combina seus dados. Com as duas desmarcadas, o Excel consolida por posição: combina o que estiver na mesma célula de cada intervalo, então todos os intervalos precisam ter o mesmo layout. Com Linha superior ou Coluna esquerda marcada, ele consolida por categoria. Os valores são combinados pelos rótulos, então os intervalos podem listar os itens em qualquer ordem e ter tamanhos diferentes.
A consolidação por categoria também funciona dentro de uma única planilha. Digamos que você tenha um registro de vendas em que cada vendedor aparece em várias linhas, uma por negócio, e queira uma linha por vendedor com os negócios somados.
1. Clique em uma célula vazia longe dos dados de origem.
2. Vá em Dados > Consolidar e escolha Soma no menu Função.
3. Em Referência, selecione a lista inteira, começando pela coluna com os nomes dos vendedores e incluindo a linha de cabeçalho. Clique em Adicionar.
4. Marque Linha superior e Coluna esquerda e clique em OK.
O Excel retorna uma linha por nome com os valores somados. A primeira coluna do intervalo contém os rótulos, então ela precisa ser a coluna pela qual você quer agrupar. O Consolidar só funciona com números, e as outras colunas de texto ficam vazias. A opção Criar vínculos com dados de origem não está disponível aqui, porque o Excel não consegue vincular um resultado a uma origem na mesma planilha.
Como consolidar dados de várias colunas no Excel
A Linha superior faz pelas colunas o que a Coluna esquerda faz pelas linhas. Digamos que a planilha Norte tenha colunas para janeiro, fevereiro e março, e a planilha Sul tenha janeiro, março e abril. Adicione os dois intervalos, marque Linha superior e Coluna esquerda, e o Excel monta uma única tabela com uma coluna para cada mês. Os valores sob o mesmo cabeçalho são somados. Abril aparece só em Sul e, mesmo assim, ganha sua própria coluna.
Se você deixar Linha superior desmarcada, o Excel combina as colunas por posição. A segunda coluna de Norte seria somada à segunda coluna de Sul, mesmo que uma contenha fevereiro e a outra março. Dispense os rótulos só quando todos os intervalos tiverem os mesmos cabeçalhos na mesma ordem.
Exemplos de consolidação de dados no Excel
O menu Função da caixa de diálogo Consolidar define o que o Excel faz com os valores correspondentes. Alguns usos comuns:
- Um orçamento anual, em que cada departamento mantém seus custos na própria planilha e Soma totaliza por linha de custo, como no passo a passo acima.
- Vendas regionais, em que cada região vende produtos diferentes. Soma com Linha superior e Coluna esquerda gera uma linha por produto com o total entre as regiões.
- Cotações de fornecedores, uma planilha por fornecedor, em que Mín retorna o menor preço oferecido para cada item.
- Resultados de pesquisas, uma planilha por escritório, em que Média dá a pontuação média de cada pergunta.
- Registros de pedidos, em que Contagem retorna quantas entradas numéricas cada cliente tem nos intervalos.
Depois que os dados estiverem em uma única tabela, uma tabela dinâmica no Excel permite agrupá-los e filtrá-los ainda mais.
Conclusão
Consolidar dados costuma ser uma das tarefas mais comuns dos analistas de dados. O motivo é que os dados são mais bem interpretados quando vistos em contexto. No entanto, como é provável que cada conjunto de dados siga uma estrutura ou categorização própria, não basta copiar todos os dados para uma única planilha.
Agora você já sabe como o recurso Consolidar do Excel funciona com dados na mesma pasta de trabalho e em pastas de trabalho diferentes. No entanto, como é muito provável que seus dados estejam espalhados por várias pastas de trabalho, as automações com IA da Sheetgo são uma solução prática para consolidar dados de diferentes arquivos em uma única planilha. O melhor é que o Sheetgo faz todo o trabalho por você.
Se você tem interesse em outros métodos para mesclar e combinar dados nas suas planilhas, estes artigos vão ser úteis:
English 
Español 
Português do Brasil 
Français