Consolidação do Excel (Sumário)
- Introdução à consolidação no Excel
- Como consolidar dados em várias planilhas?
Consolidar dados no Excel
Consolidação em geral significa coletar ou reunir algumas coisas comuns em um só lugar. Pode ser qualquer tipo de dado ou qualquer coisa como informação ou número. A consolidação no Excel refere-se a uma função que combina informações ou dados de diferentes pastas de trabalho / planilhas em uma planilha / pasta de trabalho. Em outras palavras, essa função utiliza dados de diferentes planilhas para combiná-los em uma única planilha que pode ser facilmente atualizada.
Por exemplo, se você tiver quatro planilhas. Cada folha tem despesas para um quarto. Você pode precisar de uma única folha com as despesas dos quatro trimestres. É quando a função de consolidação entra em jogo. Quando os dados nas planilhas estão na mesma posição, o que significa sob os mesmos rótulos e na mesma ordem. Usamos "Consolidação por posição". E quando os dados nas planilhas não estão sob os mesmos rótulos e na mesma ordem. Em seguida, usamos "Consolidação por categoria". É como criar uma tabela dinâmica.
Como consolidar dados em várias planilhas?
Vamos entender como consolidar dados em várias planilhas com alguns exemplos.
Exemplo # 1 - Consolidar dados na mesma pasta de trabalho
Digamos que temos uma planilha na qual temos dados de vendas com quatro planilhas marcadas de acordo com sua região como Norte, Sul, Leste e Oeste. Agora, gostaríamos de consolidar os dados em um único local, em vez de em uma planilha na mesma pasta de trabalho. Há uma quinta folha denominada arquivo consolidado.
Este exemplo mostra as vendas consolidadas para todas as regiões. Aqui estão os arquivos de dados de amostra. Aqui você pode ver os diferentes nomes de planilhas e o último arquivo consolidado no qual temos que trabalhar.
Este é o nosso modelo na planilha “arquivo consolidado” e agora começaremos a consolidar os dados das planilhas.
Vamos agora clicar na célula B3.
Onde queremos que a função "Consolidar" insira os dados de outras folhas. Como podemos ver acima, a célula B3 está selecionada e agora vamos para a guia Dados na faixa de opções do Excel e iremos para Consolidar.
Depois de clicar em Consolidar, a janela abaixo será exibida:
A primeira coisa que olhamos é o menu suspenso "Função", que mostra muitas funções aritméticas como soma, contagem, máximo, média, etc. Como queremos uma soma de vendas, selecionaremos "Sum" no menu suspenso. Agora, iremos para a guia de referência para adicionar uma referência aos nossos dados de diferentes planilhas. Iremos então para a nossa primeira planilha que contém os dados de vendas da North. Só precisamos selecionar dados de vendas e não títulos e linhas. Isso é mostrado abaixo.
A referência é mostrada na caixa "Referência" como esta.
Clique em "Adicionar" e a referência será adicionada na caixa "Todas as referências".
Da mesma maneira, agora adicionaremos uma referência de todas as outras planilhas, como Norte, Sul, Leste e Oeste. Quando terminar de adicionar as referências, clique em "OK".
Agora, temos dados consolidados de vendas para os executivos mensalmente, em nível nacional.
Exemplo # 2 - Consolidar Produto de Vendas Anual Sábio
Nesse sentido, temos vendas trimestrais para os produtos A, B, C e D e queremos vendas anuais consolidadas em termos de produtos.
Aqui temos dados em quatro folhas, primeira folha com dados de vendas para o primeiro trimestre, depois temos dados para o segundo trimestre seguidos por dados para o terceiro e quarto trimestres. Iremos agora para a guia de referência para adicionar referências aos nossos dados de diferentes planilhas. Iremos para a nossa primeira planilha que contém os dados de vendas do primeiro trimestre. Selecionaremos os dados como mostrado abaixo.
Agora, iremos para a guia Dados e clique em Consolidar e a janela abaixo aparecerá.
Clique em "Adicionar" e a referência será adicionada na caixa "Todas as referências".
Clique em "Adicionar" e a referência será adicionada na caixa "Todas as referências".
Clique em "Adicionar" e a referência será adicionada na caixa "Todas as referências".
Abaixo está o nosso modelo para a folha de dados consolidada. Agora, selecionaremos a célula B2 para obter o total de dados de vendas de outras planilhas.
Selecionaremos a função "Soma" no menu suspenso. Da mesma forma, adicionaremos referências das folhas Q2, Q3 e Q4. Aparecerá assim. Todas as referências de todas as folhas agora foram adicionadas. Clique OK"
Agora, temos nossos dados de vendas anuais consolidados com a soma total de cada produto.
No caso, se quisermos inserir a tabela consolidada em uma planilha em branco, em vez de criar uma tabela de modelos. Nós podemos fazer isso também. Tomando nossos dados de amostra anteriores, faremos a consolidação abaixo.
Não temos um modelo para a tabela de consolidação e queremos obter dados consolidados em uma planilha em branco com rótulos de linha e coluna. Adicionaremos uma nova planilha, no nosso caso, é "Arquivo consolidado".
Agora, como antes, iremos para a guia Dados Agora, selecionaremos a referência em nossas planilhas de dados. Começaremos com a planilha “Norte” e, em seguida, continuaremos com as planilhas “Sul”, “Leste” e “Oeste”. Selecionaremos os dados conforme mostrado abaixo, que incluem rótulos de linha e coluna. Em seguida, adicionaremos a referência na caixa "Todas as referências": Agora adicione todas as referências da mesma maneira em todas as folhas de dados. Vamos agora verificar a “Linha superior” e a “Coluna esquerda” e pressionar OK. Agora, vemos dados de vendas consolidados com rótulos de linha e coluna. Se você deseja atualizar os dados consolidados quando a planilha individual é atualizada, clique na caixa "Criar links para criar dados". Se você deseja atualizar os dados manualmente, não marque a caixa e clique em OK. As células na tabela consolidada agora contêm a fórmula da soma, como mostrado abaixo. Isso o torna dinâmico por natureza. Esta é uma tabela de consolidação dinâmica. Se tivermos dados em diferentes pastas de trabalho e desejarmos consolidá-los em uma única pasta de trabalho. Nós podemos fazer isso também. Isso nos dá grande flexibilidade e facilidade. Temos dados trimestrais de vendas de vários produtos, como A, B, C e D em diferentes pastas de trabalho, como mostrado abaixo. Depois de abrir todas as pastas de trabalho, iremos para uma nova pasta de trabalho e clicaremos na célula B3. Adicione as referências de todas as planilhas abaixo. Consolidamos os dados de todas as pastas de trabalho em uma nova pasta de trabalho. Além disso, quaisquer alterações nos dados em qualquer pasta de trabalho também serão atualizadas na nova pasta de trabalho de dados consolidada. Então, aprendemos como usar a função de consolidação no Excel com a ajuda de exemplos. É uma função muito útil para mesclar ou coletar dados em uma planilha de diferentes planilhas ou pastas de trabalho. Este é um guia para consolidação no Excel. Aqui discutimos Como consolidar dados em várias planilhas, juntamente com exemplos práticos e modelo de excel para download. Você também pode consultar nossos outros artigos relacionados -
Exemplo # 3 - Consolidando dados de diferentes pastas de trabalho
Lembretes sobre a consolidação no Excel
Artigos recomendados