Consolidar dados em várias folhas de cálculo
Para resumir e reportar resultados de folhas de trabalho separadas, pode consolidar os dados de cada folha numa folha de trabalho principal. As folhas podem estar no mesmo livro que a folha de trabalho principal ou noutros livros. Quando consolida os dados, reúne os dados para que possa atualizar e agregar mais facilmente conforme necessário.
Por exemplo, se tiver uma folha de cálculo de despesas para cada uma das suas filiais, poderá utilizar a consolidação para implementar estes números numa folha de cálculo de despesas empresariais principal. Esta folha de cálculo principal também poderá conter os números totais de vendas e médias, níveis de inventário atuais e os produtos mais vendidos de toda a empresa.
Sugestão: Se consolida dados com frequência, pode ser útil criar novas fichas a partir de um modelo de livro que utilize um esquema consistente. Para saber mais sobre modelos, consulte: Criar um modelo. Também é a altura ideal para configurar o seu modelo com tabelas do Excel.
Formas de consolidar dados
Existem duas formas de consolidar dados, quer 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. Utilize este método para consolidar dados de uma série de folhas de cálculo, tal como folhas de cálculo de orçamento departamental que foram criadas a partir do mesmo modelo.
Consolidação por categoria: quando os dados nas áreas de origem não estão dispostos na mesma ordem mas utilizam as mesmas etiquetas. Utilize este método para consolidar dados de uma série de folhas de cálculo que têm esquemas diferentes mas têm as mesmas etiquetas de dados.
Consolidar dados por categoria é semelhante a criar uma tabela dinâmica. No entanto, com uma tabela dinâmica pode reorganizar facilmente as categorias. Considere criar uma tabela dinâmica se precisar de uma consolidação por categoria mais flexível.
Nota: 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 formas de uma só vez:
Se ainda não o fez, defina os dados em cada folha de 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 haver linhas ou colunas em branco em qualquer parte da lista.
Coloque cada intervalo numa ficha separada, mas não introduza nada na mesma, na qual planeia consolidar os dados. Excel fazer isto por si.
Certifique-se de que cada intervalo tem o mesmo esquema.
Na folha de cálculo principal, clique na célula do canto superior esquerdo da área onde pretende que os dados consolidados sejam apresentados.
Nota: Para evitar a sobrecriação de dados existentes na mesma, certifique-se de que deixa células suficientes à direita e abaixo desta célula para os dados consolidados.
Clique em>Consolidar Dados (no grupo Ferramentas de Dados).
Na caixa Função, clique na função de resumo que pretende que o Excel utilize para consolidar os dados. A função predefinida é SOMA.
Eis um exemplo no qual foram selecionados três intervalos de uma desfeita de uma só vez:
Em seguida, na caixa Referência, clique no botão Fechar para encolher o painel e selecionar os dados na mesma.
Clique na folha de cálculo que contém os dados que pretende consolidar, selecione os dados e, em seguida, clique no botão Expandir Caixa de Diálogo à direita para voltar à caixa de diálogo Consolidar.
Se uma livro que contém dados que precisa de consolidar estiver noutro livro, clique em Procurar para localizar esse livro. Após localizar e clicar em OK,o Excel irá introduzir o caminho do ficheiro na caixa Referência e acrescenta 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 uma desnréia:
Na janela de pop-up Consolidar, clique em Adicionar. Repita este processo para adicionar todos os intervalos consolidados.
Atualizações automáticas vs. manuais: Se quiser que Excel a sua tabela de consolidação automaticamente quando os dados de origem são alterados, basta seletivar a caixa Criar ligações aos dados de origem. Se esta caixa permanecer desconsequeda, pode atualizar a consolidação manualmente.
Não pode criar ligações quando as áreas de destino e de origem estão na mesma folha.
Se precisar de alterar a extensão de um intervalo ou substituir um intervalo, clique no intervalo no pop-up Consolidar e atualize-o através dos passos acima. Esta ação irá criar uma nova referência de intervalo, por isso precisará de eliminar a anterior antes de consolidar novamente. Basta selecionar a referência antiga e premir a tecla Delete.
Clique em OKe o Excel irá gerar a consolidação automaticamente. Opcionalmente, pode aplicar formatação. Só é necessário formatar uma vez, a menos que voltar a fazer a consolidação.
Todos os rótulos que não corresponderem a rótulos nas áreas de origem transformam-se em linhas ou colunas separadas, na consolidação.
Certifique-se de que as categorias que não pretende consolidar têm etiquetas exclusivas que aparecem num único intervalo de origem.
Utilizar uma fórmula para consolidar dados
Se os dados a consolidar estiverem em células diferentes, em diferentes fichas:
Introduza uma fórmula com referências às células de outras folhas, uma por cada folha, separadamente. Por exemplo, para consolidar dados de folhas com nomes Vendas (na célula B4), HR (na célula F5) e Marketing (na célula B9), na célula A2 da folha de consolidação, introduziria o seguinte:
Sugestão: Para introduzir uma referência de célula, como Vendas! B4 – numa fórmula sem escrever, escreva a fórmula até ao ponto onde precisa da referência, clique no separador da mesma e, em seguida, clique na célula. Excel o nome da folha e o endereço da célula. NOTA: as fórmulas nesses casos podem ser com probabilidade de erro, uma vez que é muito fácil selecionar acidentalmente a célula errada. Também pode ser difícil detetar um erro após introduzir uma fórmula complexa.
Se os dados a consolidar estiverem nas mesmas células, em diferentes fichas:
Introduza a fórmula com uma referência 3-D que utilize uma referência a um intervalo de nomes de folha. Por exemplo, para consolidar dados nas células A2 de Vendas a Marketing inclusive, na célula E5 da ficha de trabalho principal teria de introduzir o seguinte: