Consolidar dados em várias planilhas

Para resumir e reportar resultados de folhas de cálculo separadas, pode consolidar dados de cada folha numa folha de cálculo principal. As folhas podem estar no mesmo livro que a folha de cálculo principal ou noutros livros. Quando consolida dados, reúne dados para que possa atualizar e agregar mais facilmente 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 consolidar dados frequentemente, poderá ajudar a criar novas folhas de cálculo a partir de um modelo de folha de cálculo que utilize um esquema consistente. Para saber mais sobre modelos, confira: Criar um modelo. Essa é a ocasião ideal para configurar um modelo usando tabelas do Excel.

Existem duas formas 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 utilizam as mesmas etiquetas. 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 precisar de uma consolidação mais flexível por categoria.

Observação: Os exemplos neste artigo foram criados com o Excel 2016. Embora a sua vista possa ser diferente se estiver a utilizar outra versão do Excel, os passos são os mesmos.

Siga estes passos para consolidar várias folhas de cálculo numa folha de cálculo principal:

Se ainda não o fez, configure os dados em cada folha constituinte ao fazer o seguinte:
- Certifique-se de que cada intervalo de dados está no formato de lista. Cada coluna tem de ter uma etiqueta (cabeçalho) na primeira linha e conter dados semelhantes. Não devem existir linhas ou colunas em branco na lista.
- Coloque cada intervalo numa folha de cálculo separada, mas não introduza nada na folha de cálculo principal onde planeia consolidar os dados. O Excel irá fazê-lo por si.
- Certifique-se de que cada intervalo tem o mesmo esquema.
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 folha de cálculo principal, certifique-se de que deixa células suficientes à direita e abaixo desta 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.

Eis um exemplo em que foram escolhidos três intervalos de folhas de cálculo:
Escolha os dados.

Em seguida, na caixa Referência , clique no botão Fechar para encolher o painel e selecionar os dados na folha de cálculo.

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 folha de cálculo que contém dados que precisa de consolidar estiver noutro livro, clique em Procurar para localizar esse livro. Depois de localizar e clicar em OK, o Excel irá introduzir o caminho do ficheiro na caixa Referência e acrescentar um ponto de exclamação a esse caminho. Em seguida, pode continuar a selecionar outros dados.

Eis um exemplo no qual foram selecionados três intervalos de folhas de cálculo:
Na janela de pop-up Consolidar , clique em Adicionar. Repita esta ação para adicionar todos os intervalos consolidados.
Atualizações automáticas vs. Atualizações manuais: Se quiser que o Excel atualize a tabela de consolidação automaticamente quando os dados de origem forem alterados, basta selecionar a caixa Criar ligações para dados de origem . Se esta caixa permanecer desmarcada, 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.
- Se precisar de alterar a extensão de um intervalo ou substituir um intervalo, clique no intervalo no pop-up Consolidar e atualize-o com os passos 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 premir a tecla Delete.
Clique em OK e o Excel irá gerar a consolidação por si. Opcionalmente, pode aplicar formatação. Só é necessário formatar uma vez, a menos que volte a executar 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 não pretende consolidar têm etiquetas exclusivas que aparecem apenas num intervalo de origem.

Se os dados a consolidar estiverem em células diferentes em folhas de cálculo 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 introduzir uma referência de célula, como Vendas! B4 — numa fórmula sem escrever, escreva a fórmula até ao ponto em que precisa da referência, clique no separador da folha de cálculo e, em seguida, clique na célula. O Excel irá preencher o nome da folha e o endereço da célula por si. NOTA: as fórmulas nestes casos podem ser propensas a erros, uma vez que é muito fácil selecionar acidentalmente a célula errada. Também pode ser difícil detetar um erro depois de introduzir uma fórmula complexa.

Se os dados a consolidar estiverem nas mesmas células em folhas de cálculo 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 até Marketing inclusive, na célula E5 da folha de cálculo principal, introduza o seguinte:

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