Consolidar dados no Excel

O que é consolidar dados no Excel?

Enquanto trabalhamos no excel, temos muitos dados disponíveis em diferentes planilhas e se quisermos mesclar ou consolidar dados em uma única planilha o excel tem uma função para fazê-lo após abrir todas as planilhas cujos dados precisam ser consolidados, o botão Consolidar é disponível na seção de ferramentas de dados da guia Dados.

Se você não conhece a ferramenta Consolidate no excel, este artigo irá ajudá-lo a entender a dinâmica da ferramenta Consolidate no excel.

Em comum, quando recebemos dados de pessoas diferentes, temos muitas planilhas para lidar. Consolidar tudo é uma tarefa hercúlea, especialmente para niveladores intermediários. No início de minha carreira, obtive dados de uma ampla gama de fontes.

Por exemplo, estava acostumado a obter dados de vendas de 4 zonas diferentes de cabeças zonais. Quatro zonas diferentes incluem 4 planilhas diferentes. No final dos dados, fui solicitado a agrupar tudo em uma planilha e criar um banco de dados para fins de análise. Eu estava fazendo isso manualmente, da maneira tradicional de copiar e colar.

Mas passei algum tempo aprendendo coisas novas e consolidar ferramentas é uma delas e me ajudou muito a agrupar os dados com o mínimo de esforço.

Como consolidar dados no Excel? (Passo a passo com exemplo)

Você pode baixar este modelo Excel para consolidar dados aqui - Modelo Excel para consolidar dados

Tenho dados de vendas de quatro regiões diferentes em quatro planilhas diferentes.

Cada um contém o mesmo formato e o mesmo número de linhas e colunas. Preciso consolidar todas as planilhas em uma única planilha chamada Resumo.

Criei o mesmo modelo das outras 4 folhas.

• Passo 1: Depois de criar o modelo acima no Resumo da planilha, selecione a célula B2 onde queremos consolidar todas as 4 regiões diferentes.

• Etapa 2: agora vá para Dados> Consolidar

• Etapa 3: Depois de selecionar a opção Consolidar, você verá a janela abaixo.

• Etapa 4: Esta é a janela de que precisamos para fazer a mágica. Em primeiro lugar, vemos a lista suspensa Função da lista suspensa, precisamos selecionar nosso tipo de consolidação. Aqui, precisamos selecionar a função SUM no Excel porque estamos consolidando todos os dados de vendas da zona e criando um relatório de resumo. Portanto, selecione SUM sob esta opção.
• Passo 5: O próximo passo é dar referência a todas as 4 folhas de regiões diferentes. Clique na caixa de referência e vá para a primeira planilha de todas as planilhas da zona, ou seja, sul. Após selecionar o intervalo, clique no botão Adicionar.

• Passo 6: Após clicar no botão Adicionar, ele adiciona as referências a todas as referências de células.

• Etapa 7: Da mesma forma, crie um link do Excel para as planilhas de todas as outras regiões. Finalmente, ficará assim.

• Passo 8: Agora clique no botão OK, seus dados consolidados estão prontos sem nenhum erro.

• Etapa 9: agora criamos um relatório de resumo, este não é um relatório dinâmico porque não criamos um link para as outras tabelas. Se houver alguma alteração, as tabelas permanecem as mesmas e nosso relatório dá errado neste caso. Precisamos vincular a tabela às outras planilhas para tornar o relatório dinâmico.
• Etapa 10: Na caixa de diálogo de consolidação, marque a caixa Criar links para dados de origem. Clique em OK.

Em seguida, ele conclui o processo.

• Etapa 11: Agora ele criou a função SUM. Há um ícone de mais (+) no lado direito da planilha, clique no ícone de mais (+) para ver a separação.

Outras opções em consolidar

1. Função: Esta é a lista de todas as funções disponíveis na ferramenta Consolidar. Você pode usar a função SUM, AVERAGE, COUNT, MAX, MIN no Excel, etc.
2. Referência: é aqui que precisamos selecionar os intervalos de diferentes planilhas para consolidar os dados em uma única planilha.
3. Navegar: essa é a beleza da opção de consolidação. Não apenas consolida a partir da mesma pasta de trabalho, mas também pode buscar os dados de outras pastas de trabalho.
4. Adicionar e Excluir: Aqui podemos adicionar e excluir todas as referências.
5. Linha superior: use a linha superior como títulos de linha
6. Coluna esquerda: use o lado esquerdo dos dados como cabeçalhos das colunas.
7. Criar links para dados de origem: isso tornará o relatório dinâmico. A opção consolidada cria a fórmula para todas as outras planilhas para tornar o relatório realmente dinâmico.

Coisas para lembrar

• Na consolidação, os intervalos de opções de todas as planilhas devem ser iguais em todas as planilhas.
• Podemos usar qualquer tipo de função na opção consolidar.