Consolidar dados em várias planilhas

Para resumir e relatar resultados de planilhas separadas, você pode consolidar dados de cada planilha em uma planilha mestre. As planilhas podem estar na mesma pasta de trabalho que a planilha mestre ou em outras pastas de trabalho. Ao consolidar dados, você reúne dados para que você possa atualizar e agregar mais facilmente quando 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, pode ser útil criar novas planilhas a partir de 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, seja por posição ou categoria.

Consolidação por posição: os dados nas áreas de origem têm a 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. Considere a criação de uma tabela dinâmica se precisar de uma consolidação mais flexível por categoria.

Observação: Os exemplos neste artigo foram criados com o Excel 2016. Embora a exibição possa ser diferente se você estiver usando outra versão do Excel, as etapas são as mesmas.

Siga estas etapas para consolidar várias planilhas em uma planilha mestra:

  1. Se ainda não configurou os dados em cada folha de componentes, faça o seguinte:

    • Verifique se cada intervalo de dados está em formato de lista. Cada coluna deve ter um rótulo (cabeçalho) na primeira linha e conter dados semelhantes. Não deve haver linhas ou colunas em branco em qualquer lugar na lista.

    • Coloque cada intervalo em uma planilha separada, mas não insira nada na planilha mestre na qual você planeja consolidar os dados. O Excel fará isso 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 a substituição de dados existentes na planilha mestra, certifique-se de deixar células suficientes para a direita e abaixo desta célula para os dados consolidados.

  3. Clique em dados>consolidar (no grupo ferramentas de dados ).

    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.

    Aqui está um exemplo em que três intervalos de planilha foram escolhidos:

    Caixa de diálogo Consolidação de dados

  5. Escolha os dados.

    Em seguida, na caixa referência , clique no botão recolher para reduzir o painel e 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.

    Se uma planilha que contém dados que você precisa consolidar estiver em outra pasta de trabalho, clique em procurar para localizá-la. Depois de localizar e clicar em OK, o Excel inserirá o caminho do arquivo na caixa referência e acrescentará um ponto de exclamação ao caminho. Em seguida, você pode continuar a selecionar outros dados.

    Aqui está um exemplo em que três intervalos de planilha foram escolhidos selecionados:

    Caixa de diálogo Consolidação de dados

  6. Na janela pop-up consolidar , clique em Adicionar. Repita este procedimento para adicionar todos os intervalos consolidados.

  7. Atualizações automáticas vs. Atualizações manuais: Se você quiser que o Excel atualize a tabela de consolidação automaticamente quando os dados de origem forem alterados, basta marcar a caixa criar links com dados de origem . Se essa caixa permanecer desmarcada, você poderá 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.

    • Se você precisar alterar a extensão de um intervalo (ou substituir um intervalo), clique no intervalo no pop-up consolidar e atualize-o usando as etapas acima. 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 antiga e pressionar a tecla Delete.

  8. Clique em OK, e o Excel irá gerar a consolidação para você. Opcionalmente, você pode aplicar a formatação. Só é necessário formatar 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.

    • Certifique-se de que todas as categorias que você não deseja consolidar tenham rótulos exclusivos que aparecem em apenas um intervalo de origem.

Se os dados a serem consolidados estiverem em células diferentes em planilhas diferentes:

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:

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 você precisa da referência, clique na guia da planilha e, em seguida, clique na célula. O Excel irá completar o nome da planilha e o endereço da célula para você. Observação: as fórmulas em tais casos podem ser sujeitas a erros, pois é muito fácil selecionar acidentalmente a célula errada. Também pode ser difícil detectar um erro após a inserção de uma fórmula complexa.

Se os dados a serem consolidados estiverem nas mesmas células em planilhas diferentes:

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 através de marketing, na célula e5 da planilha mestre você deve digitar o seguinte:

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

Precisa de mais ajuda?

Você pode sempre consultar um especialista na Excel Tech Community, obter suporte na Comunidade de respostas ou sugerir um novo recurso ou melhoria no 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)

Precisa de mais ajuda?

Expanda suas habilidades no Office
Explore o treinamento
Obtenha novos recursos primeiro
Ingressar no Office Insider

Essas informações foram úteis?

Obrigado por seus comentários!

Agradecemos pelos seus comentários! Parece que pode ser útil conectar você a um de nossos agentes de suporte do Office.

×