Consolidar dados em várias planilhas

Para resumir e relatar resultados de planilhas separadas, você pode consolidar os dados de cada uma delas em uma planilha mestra. As planilhas podem estar na mesma pasta de trabalho da planilha mestra ou em outras pastas de trabalho. Ao consolidar dados, você os reúne para poder atualizá-los e agregá-los com mais facilidade, conforme necessário.

Por exemplo, se tiver uma planilha de despesas para cada escritório regional, você deve usar a consolidação para lançar esses valores em uma planilha mestra de despesas corporativas. Essa planilha mestra pode incluir também as médias e os totais de vendas, os níveis de estoque atuais e os produtos mais vendidos de toda a empresa.

Dica : Se você consolidar dados com frequência, isso poderá ajudar a basear suas planilhas em um modelo de planilha que usa um layout consistente. Para saber mais sobre modelos, confira: Criar um modelo. Essa é a ocasião ideal para configurar um modelo usando tabelas do Excel.

Há duas maneiras de consolidar dados: por categoria ou posição.

Consolidação por posição: quando os dados das áreas de origem são organizados pela mesma ordem e usam os mesmos rótulos. Use este método para consolidar dados de uma série de planilhas, como planilhas de orçamento departamentais criadas com o mesmo modelo.

Consolidação por categoria: quando os dados das áreas de origem não são organizados pela mesma ordem, embora usem os mesmos rótulos. Use este método para consolidar dados de uma série de planilhas cujos layouts sejam diferentes, embora tenham os mesmos rótulos de dados.

  • A consolidação de dados por categoria é semelhante à criação de uma Tabela Dinâmica. No entanto, você pode reorganizar as categorias facilmente com uma Tabela Dinâmica. Caso pretenda realizar uma consolidação mais flexível por categoria, pense na possibilidade de criar uma Tabela Dinâmica.

Observação : Criamos os exemplos deste artigo com o Excel 2016, portanto sua exibição pode ser diferente, dependendo da versão que você está usando. No entanto, as etapas são exatamente iguais.

Etapas de consolidação

  1. Se ainda não consolidou, em cada planilha que contenha os dados que você deseja consolidar, configure-os fazendo o seguinte:

    • Verifique se cada intervalo de dados está no formato de lista, de modo que cada coluna tenha um rótulo na primeira linha e contenha dados semelhantes; e ainda que não haja linhas ou colunas em branco dentro da lista.

    • Coloque cada intervalo em uma planilha separada, mas não insira conteúdo na planilha mestra em que pretende estabelecer a consolidação. O Excel preencherá os dados para você.

    • Verifique se cada intervalo tem o mesmo layout.

  2. Na planilha mestre, clique na célula superior esquerda da área em que você deseja que os dados consolidados sejam exibidos.

    Observação : Para evitar substituir dados existentes na planilha de destino pelos dados que você está consolidando, verifique se você deixou células suficientes à esquerda e abaixo dessa célula para os dados consolidados.

  3. Na guia Dados, vá até o grupo Ferramentas de Dados e clique em Consolidar.

    grupo ferramentas de dados na guia dados

  4. Na caixa Função, clique na função resumo que deseja que o Excel utilize para consolidar os dados. A função padrão é SOMA.

    A seguir, veja um exemplo com três intervalos de planilha selecionados.

    Caixa de diálogo Consolidação de dados

  5. Escolha os dados

    • Se a planilha que contém os dados que você deseja consolidar estiver em outra pasta de trabalho, primeiro clique em Procurar para localizar essa pasta de trabalho e clique em OK para fechar a caixa de diálogo Procurar. O Excel vai inserir o caminho do arquivo na caixa Referência, seguido de um ponto de exclamação, e você pode continuar a escolher os dados.

    Em seguida, na caixa Referência, clique no botão Recolher caixa de diálogo para selecionar os dados na planilha.

    Caixa de diálogo Consolidação de dados recolhida

    Clique na planilha que contém os dados que deseja consolidar, selecione-os e clique no botão Expandir caixa de diálogo, à direita, para retornar à caixa de diálogo Consolidar.

  6. Na caixa de diálogo Consolidar, clique em Adicionar e repita para adicionar todos os intervalos desejados.

  7. Atualizações automáticas vs. Atualizações manuais: Se preferir que o Excel atualize a tabela de consolidação automaticamente quando os dados de origem forem alterados, marque a caixa de seleção Criar vínculos com dados de origem. Se a caixa de seleção estiver desmarcada, você pode atualizar a consolidação manualmente.

    Observações : 

    • Não é possível criar vínculos quando as áreas de origem e de destino estão na mesma planilha.

    • Caso precise alterar os intervalos após adicioná-los, clique em cada um deles na caixa de diálogo Consolidar e atualize-os quando eles forem exibidos na caixa Referência; em seguida, clique em Adicionar. Isso criará uma nova referência de intervalo, por isso você deve excluir a referência anterior, antes de fazer novamente a consolidação. Basta escolher a referência anterior e pressionar Excluir.

  8. Pressione OK e o Excel gerará a consolidação para você. Ela não estará formatada, portanto cabe a você formatá-la, mas só é necessário fazer isso uma vez, a menos que você execute novamente a consolidação.

    • Os rótulos que não corresponderem aos rótulos nas outras áreas resultarão em linhas ou colunas separadas na consolidação.

    • Verifique se qualquer categoria que não deve ser consolidada tem rótulos exclusivos e presentes somente em um intervalo de origem.

Usar uma fórmula para consolidar dados

  • Se os dados a serem consolidados estiverem em células diferentes, em outras planilhas

    Insira uma fórmula com referências de célula para outras planilhas, sendo uma 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 mestra, você deve inserir o seguinte:

    Referência de fórmula com várias planilhas do Excel

    Dica : Para inserir uma referência de célula, como Vendas!B4, em uma fórmula sem digitar, digite a fórmula até o ponto em que a referência é necessária, clique na guia da planilha e clique na célula. O Excel preencherá o endereço da célula e o nome da planilha para você. Observe que fórmulas como essa podem ser propensas a erro, porque é muito fácil selecionar a célula errada por engano. Além disso, pode ser difícil localizar um erro depois de inserir uma fórmula.

  • Se os dados a serem consolidados estiverem nas mesmas células, em outras planilhas

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

    Fórmula de referência de planilha 3D do Excel

Você sabia?

Se não tiver uma assinatura do Office 365 ou a versão mais recente do Office, você pode experimentá-la agora:

Experimente o Office 365 ou a versão mais recente do Excel

Você tem dúvida sobre uma função específica?

Postar uma pergunta no fórum da comunidade do Excel

Ajude-nos a melhorar o Excel

Você tem sugestões sobre como aprimorar a próxima versão do Excel? Em caso afirmativo, confira os tópicos no site UserVoice do Excel

Consulte Também

Visão geral de fórmulas no Excel

Como evitar fórmulas quebradas

Encontrar e corrigir erros em fórmulas

Atalhos de teclado e teclas de função do Excel

Funções do Excel (ordem alfabética)

Funções do Excel (por categoria)

Compartilhar Facebook Facebook Twitter Twitter Email Email

Essas informações foram úteis?

Ótimo! Outros comentários?

Como podemos melhorá-lo?

Obrigado por seus comentários!

×