Curso Grátis de Excel Avançado - Módulo 4 - Lição: Consolidação por categoria - um exemplo prático

Nessa lição vamos utilizar os mesmos dados do exercício da lição anterior, porém faremos uma consolidação por categoria. A única diferença é que, os rótulos da primeira linha e da primeira coluna também farão parte da faixa a ser selecionada para a consolidação. Com isso os rótulos serão copiados para a planilha Trim1. A vantagem desse método é que, mesmo que os dados sejam deslocados de posição, em uma ou mais planilhas, o Excel consegue manter a referência, com base nos rótulos. Não podem haver rótulos repetidos.

Nesse exemplo utilizaremos o arquivo Modulo 4 - Exercício 04.xls. Observe que na planilha Trim1 não tem rótulos, os quais serão copiados diretamente das planilhas Jan, Fev e Mar.

Exercício 05:

Abrir o arquivo C:\ExcelAvançado\Modulo 4 - Exercício 05.xls. Esse arquivo contém quatro planilhas, conforme indicado nas figuras da lição Anterior. Temos dados de vendas por produto para Janeiro, Fevereiro e Março. Na planilha Trim1 queremos que o Excel faça a consolidação dos dados de vendas por produto para o primeiro trimestre.

1. 1. Abra o Excel.
2. 2. Selecione o comando Arquivo -> Abrir.
3. 3. Navegue até a pasta C:\ExcelAvançado.
4. 4. Selecione o arquivo Modulo 4 - Exercício 05.xls.
5. 5. Clique em Abrir.
6. 6. Será aberto o arquivo com quatro planilhas: Jan, Fev, Mar e Trim1.
7. 7. Clique na guia Trim1 para ir para a planilha onde será feita a consolidação de dados. Observe que a planilha Trim1 não possui os rótulos dos dados, conforme indicado na Figura 4.25:

1. 8. Clique na célula A4 para indicar o local onde iniciarão os dados consolidados, incluindo os rótulos.
2. 9. Selecione o comando Dados -> Consolidar...
3. 10. Será exibida a janela Consolidar, indicada na Figura 4.26:

1. 11. Agora temos que informar ao Excel, as faixas onde estão os dados a serem consolidados.
2. 12. Clique no botão ( ), ao lado do campo Referência.
3. 13. A janela Consolidar será ocultada e a planilha é exibida. Você pode usar o mouse para indicar a primeira faixa de células. No nosso exemplo clique na planilha Jan e depois selecione a faixa A4:F9, conforme indicado na Figura 4.27:

1. 14. Clique no botão ( ), na janela Consolidar - Referência. Você estará de volta à janela Consolidar, com o endereço Jan!$A$A:$F$9. Observe que o Excel está utilizando endereços absolutos.
2. 15. Clique no botão Adicionar. A faixa Jan!$A$4:$F$9 será incluído na lista de faixas para consolidação, conforme indicado na Figura 4.28:

1. 16. Repita os passos de 12 a 15, para adicionar as faixas Fev!$A$4:$F$9 e Mar!$A$4:$F$9. A janela Consolidar deve estar conforme indicado na Figura 4.29:

1. 17. Certifique-se de que esteja selecionada a função Soma.
2. 18. Como estamos consolidando Por categoria, marque as opções Linha superior e Coluna esquerda. Marque a opção Criar vínculos com os dados de origem. Marcar essa opção faz com que o Excel atualize automaticamente os dados da área de consolidação quando os dados são alterados em qualquer uma das áreas de origem.
3. 19. Clique em OK. Rapidamente o Excel calcula os totais consolidados para o primeiro trimestre, sem que tenhamos que ter digitado uma única fórmula, conforme indicado na Figura 4.30:

1. 20. Observe que ao lado de cada região é exibido um sinal de +. Se você clicar nesse sinal de + serão exibidos os registros individuais, de cada planilha, que formam o total consolidado da região sul, conforme indicado na Figura 4.31:

1. 21. O sinal de + se transforma em um sinal de -. Ao clicar no sinal de - os detalhes são ocultados. Salve e feche a planilha.

Módulo 4 - Análise, Cenários e Consolidação de Dados:

• Lição 001 - Introdução e conteúdo do Módulo 4
• Lição 002 - Calculando SubTotais/Resumos
• Lição 003 - Criando Subtotais - Mais um exemplo simples
• Lição 004 - Configurações e alterações em dados consolidados
• Lição 005 - Exercícios com totalizações
• Lição 006 - Introdução à consolidação de dados
• Lição 007 - Consolidação de dados e um exemplo simples
• Lição 008 - Consolidação por categoria - um exemplo prático
• Lição 009 - Configurações relacionadas a consolidação de dados
• Lição 010 - Consolidação com dados de múltiplos arquivos
• Lição 011 - Análise de Dados com o Excel - Introdução e Conceitos
• Lição 012 - Análise/Simulações baseadas em uma Tabela de Dados
• Lição 013 - Análise/Simulações: Simulação com múltiplos valores
• Lição 014 - Análise/Simulações: Simulação com duas variáveis
• Lição 015 - Atingindo Objetivos - A Ferramenta Atingir Metas
• Lição 016 - Gerenciando Cenários - Introdução e Exemplo
• Lição 017 - Criando Cenários - Criação dos cenários
• Lição 018 - Utilizando os Cenários Criados na Lição Anterior
• Lição 019 - Gerando um Relatório Consolidado de Cenários
• Lição 020 - Resumo do Módulo 4