Consolidar dados em várias planilhas

Para resumir e relatar os resultados de planilhas separadas, você pode consolidar dados de cada planilha separada em um planilha principal. As planilhas podem estar na mesma pasta de trabalho que a planilha principal ou em outras pastas de trabalho. Ao consolidar dados, você está montando dados para que possa atualizá-los e agregá-los com mais facilidade, em uma base regular ou ad hoc.

Por exemplo, se tiver uma planilha de valores de despesa de cada um dos escritórios regionais, você poderá usar uma consolidação para combinar esses valores em uma planilha de despesas corporativas. Essa planilha principal pode conter totais e médias de venda e os produtos mais vendidos em toda a empresa.

Para consolidar dados, utilize o comando Consolidar no grupo Ferramentas de Dados, na guia Dados.

O que você deseja fazer?

Se quiser Então…
Organizar os dados em todas as planilhas em ordem e local idênticos Consolide por posição
Organizar os dados de forma diferente nas planilhas separadas, mas usar os mesmos rótulos de linha e de coluna para que a planilha principal possa fazer a correspondência dos dados Consolide por categoria
Usar fórmulas com as referências de célula ou referências de 3-D (referência 3D: uma referência a um intervalo que abrange duas ou mais planilhas em uma pasta de trabalho.) a outras planilhas combinadas porque você não tem uma posição ou uma categoria consistente com a qual contar Consolide por fórmula
Usar um relatório de Tabela Dinâmica em vez de uma consolidação Usar um relatório de Tabela Dinâmica para consolidar dados

Consolide por posição

  1. Configure os dados para serem consolidados em cada planilha separada.

MostrarComo configurar os dados

  1. Clique na célula superior esquerda da área em que você deseja exibir os dados consolidados, na planilha principal.

 Observação    Certifique-se de que você deixou células suficientes à direita e abaixo dessa célula para os dados consolidados. O comando Consolidar preenche a área conforme necessário.

  1. Na guia Dados, no grupo Ferramentas de Dados, clique em Consolidar.

Imagem da faixa de opções do Excel

  1. Na caixa Função, clique na função de resumo (função resumo: um tipo de cálculo que combina os dados de origem em um relatório de tabela dinâmica ou em uma tabela de combinação ou quando você está inserindo subtotais automáticos em uma lista ou banco de dados. Os exemplos de função resumo incluem Soma, Contar e Média.) que você deseja que o Microsoft Office Excel utilize para consolidar os dados.
  2. Se a planilha for uma outra pasta de trabalho, clique em Procurar para localizar o arquivo e, em seguida, clique em OK para fechar a caixa de diálogo Procurar.

O caminho do arquivo é digitado na caixa Referência seguido por um ponto de exclamação.

  1. Digite o nome que você deu ao intervalo e clique em Adicionar. Repita essa etapa para cada intervalo.
  2. Decida como deseja atualizar a consolidação. Siga um destes procedimentos:
    • Para configurar a consolidação para que ela se atualize automaticamente quando os dados de origem forem alterados, marque a caixa de seleção Criar links para dados de origem.

 Importante   Você pode marcar apenas essa caixa de seleção se a planilha estiver em outra pasta de trabalho. Depois de marcar essa caixa de seleção, não será mais possível alterar as células e os intervalos que estão inclusos na consolidação.

  • Para configurar a consolidação para que você possa atualizá-la manualmente, alterando as células e os intervalos inclusos, desmarque a caixa de seleção Criar links para dados de origem.
  1. Deixe vazias as caixas abaixo de Usar rótulos na. O Excel não copia os rótulos de linha ou coluna dos intervalos de origem para a consolidação. Se você desejar rótulos para os dados consolidados, copie-os de um dos intervalos de origem ou insira-os manualmente.

Início da página Início da página

Consolide por categoria

  1. Configure os dados para serem consolidados em cada planilha separada.

MostrarComo configurar os dados

  • Certifique-se de que cada intervalo de dados esteja no formato de lista (lista: uma série de linhas que contêm dados relacionados ou que você determina que funcionará como uma folha de dados, usando o comando Criar Lista.): cada coluna tem um rótulo na primeira linha e contém fatos semelhantes, e não existem linhas ou colunas em branco dentro da lista.
  • Coloque cada intervalo em uma planilha separada. Não insira nenhum intervalo na planilha onde você planeja colocar a consolidação.
  • Certifique-se de que os rótulos para colunas ou linhas que deseja combinar tenham ortografia e capitalização idênticas; por exemplo, os rótulos Méd. Anual e Média Anual são diferentes e não serão consolidados.
  • Nomeie cada intervalo: selecione o intervalo inteiro e, em seguida, na guia Fórmulas, no grupo Células Nomeadas, clique na seta próxima a Nomear um Intervalo e digite um nome para o intervalo na caixa Nome.
  1. Clique na célula superior esquerda da área em que você deseja exibir os dados consolidados, na planilha principal.

 Observação    Certifique-se de que você deixou células suficientes à direita e abaixo dessa célula para os dados consolidados. O comando Consolidar preenche a área conforme necessário.

  1. Na guia Dados, no grupo Ferramentas de Dados, clique em Consolidar.

Imagem da faixa de opções do Excel

  1. Na caixa Função, clique na função de resumo (função resumo: um tipo de cálculo que combina os dados de origem em um relatório de tabela dinâmica ou em uma tabela de combinação ou quando você está inserindo subtotais automáticos em uma lista ou banco de dados. Os exemplos de função resumo incluem Soma, Contar e Média.) que você deseja que o Excel utilize para consolidar os dados.
  2. Se a planilha for uma outra pasta de trabalho, clique em Procurar para localizar o arquivo e, em seguida, clique em OK para fechar a caixa de diálogo Procurar.

O caminho do arquivo é digitado na caixa Referência seguido por um ponto de exclamação.

  1. Digite o nome que você deu ao intervalo e clique em Adicionar. Repita essa etapa para cada intervalo.
  2. Decida como deseja atualizar a consolidação. Siga um destes procedimentos:
    • Para configurar a consolidação para que ela se atualize automaticamente quando os dados de origem forem alterados, marque a caixa de seleção Criar links para dados de origem.

 Importante   Você pode marcar apenas essa caixa de seleção se a planilha estiver em outra pasta de trabalho. Depois de marcar essa caixa de seleção, não será mais possível alterar as células e os intervalos que estão inclusos na consolidação.

  • Para configurar a consolidação para que você possa atualizá-la manualmente, alterando as células e os intervalos inclusos, desmarque a caixa de seleção Criar links para dados de origem.
  1. Marque as caixas de seleção em Usar rótulos em que indiquem onde o rótulos estão localizados nos intervalos de origem: a Linha superior, a Coluna à esquerda ou ambas.

 Observações 

  • Os rótulos que não corresponderem aos rótulos nas outras áreas resultarão em linhas ou colunas separadas na consolidação.
  • Certifique-se de que qualquer categoria que não deve ser consolidada tenha rótulos exclusivos e presentes somente em um intervalo de origem.

Início da página Início da página

Consolide por fórmula

  1. Na planilha principal, copie ou insira os rótulos da coluna ou da linha que você deseja para os dados consolidados.
  2. Clique em uma célula que você deseja que contenha os dados consolidados.
  3. Digite uma fórmula que inclua uma referência de célula às células de origem em cada planilha ou uma referência de 3-D que contenha dados que deseja consolidar. Com relação às referência de célula, execute um dos procedimentos a seguir:

Se os dados a serem consolidados estiverem em células diferente em outras planilhas    

  • Digite uma fórmula com as referências da célula a outras planilhas, um para cada planilha separada. Por exemplo, para consolidar dados das planilhas chamadas Vendas (na célula B4), RH (na célula F5) e Marketing (na célula B9), na célula A2 da planilha principal, você deve digitar o seguinte:

Fórmula para consolidar células de três planilhas que usam referências de célula

Dica    Para inserir uma referência de célula, como Vendas3!B4, em uma fórmula sem digitar, digite a fórmula até o ponto onde você precisa da referência, clique na guia da planilha e, em seguida, clique na célula.

Se os dados a serem consolidados estiverem na mesma célula em outras planilhas    

  • Digite uma fórmula com uma referência 3-D que usa uma referência a um intervalo de nomes de planilha. Por exemplo, para consolidar dados nas células A2 de Vendas por meio de Marketing inclusive, na célula A2 da planilha principal, você deve digitar o seguinte:

Fórmula para consolidar células de três planilhas que usam referências de célula 3-D

 Observação   Se a pasta de trabalho estiver definida para calcular automaticamente as fórmulas, um consolidação por fórmula sempre é atualizada automaticamente quando os dados em planilhas separadas é alterado.

Início da página Início da página

Usar um relatório de Tabela Dinâmica para consolidar dados

Você pode criar um relatório de Tabela Dinâmica (relatório de tabela dinâmica: um relatório de referências cruzadas interativo do Excel que resume e analisa dados, como registros de banco de dados, de várias fontes, incluindo fontes externas ao Excel.) a partir de vários intervalos de consolidação. Esse método é semelhante a consolidar por categoria, mas oferece mais flexibilidade para reorganizar as categorias. Para obter mais informações, consulte Consolidar várias planilhas em um relatório de Tabela Dinâmica.

Início da página Início da página

 
 
Aplica-se a:
Excel 2007