Consolidar dados em várias planilhas

Para resumir e relatar resultados de planilhas separadas, você pode consolidar dados de cada planilha em uma planilha master. As planilhas podem estar na mesma pasta de trabalho que a planilha master ou em outras pastas de trabalho. Ao consolidar dados, você monta dados para que possa atualizar e agregar 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, poderá ajudar a 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, 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 criar uma Tabela Dinâmica se você precisar de consolidação mais flexível por categoria.

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

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

Se você ainda não tiver, configure os dados em cada planilha constituinte fazendo o seguinte:
- Verifique se cada intervalo de dados está no 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 da lista.
- Coloque cada intervalo em uma planilha separada, mas não insira nada na planilha master em que você planeja consolidar os dados. O Excel fará isso por você.
- Verifique se cada intervalo tem o mesmo layout.
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 master, verifique se você deixa células suficientes à direita e abaixo dessa célula para os dados consolidados.
Clique em Dados>Consolidar (no grupo Ferramentas de Dados ).
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 planilhas foram escolhidos:
Escolha os dados.

Em seguida, na caixa Referência , clique no botão Desabar para reduzir o painel e selecionar os dados na planilha.

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 localizar essa pasta de trabalho. Depois de localizar e clicar em OK, o Excel inserirá o caminho do arquivo na caixa Referência e adicionará um ponto de exclamação a esse caminho. Em seguida, você pode continuar selecionando outros dados.

Aqui está um exemplo em que três intervalos de planilha foram selecionados:
Na janela Consolidar pop-up, clique em Adicionar. Repita isso para adicionar todos os intervalos que você consolida.
Atualizações automáticas vs. Atualizações manuais: Se você quiser que o Excel atualize sua tabela de consolidação automaticamente quando os dados de origem forem alterados, basta marcar a caixa criar links para a caixa de 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 Excluir.
Clique em OK e o Excel 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.
- Verifique se todas as categorias que você não deseja consolidar têm 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 planilha e clique na célula. O Excel concluirá o nome da planilha e o endereço da célula para você. OBSERVAÇÃO: as fórmulas nesses casos podem ser propensas a erros, pois é muito fácil selecionar acidentalmente a célula errada. Também pode ser difícil detectar um erro depois de inserir 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 por Marketing inclusivo, na célula E5 da planilha master você inseriria o seguinte:

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