Como consolidar dados de várias planilhas usando referências 3D no Excel
Referência 3D do Excel é uma fórmula curta e flexível que permite realizar cálculos referenciando a mesma célula ou um intervalo de células em várias planilhas. O uso de fórmulas 3D elimina a necessidade de fazer referência a cada célula, tornando os cálculos mais fáceis. Ao aplicar a fórmula 3D uma vez, ela incluirá dados de planilhas que você adicionar posteriormente.
Este artigo explorará como consolidar dados em uma planilha pode ajudar a reduzir o tempo que leva para calcular dados em múltiplas planilhas. Gerenciar grandes quantidades de informações em uma única planilha pode ser fácil e eficiente, levando a resultados precisos sob demandas mínimas.
Diferentes abordagens para somar dados no Excel
No Excel, a maneira tradicional de somar informações em várias planilhas é somar manualmente cada célula usando a Função Soma. Em contraste, adicioná-los manualmente pode se tornar cada vez mais problemático se você tiver um grande conjunto de dados de vendas mensais por um período prolongado.
Este também não é um método dinâmico, portanto, você terá que alterar as fórmulas novamente se adicionar uma planilha extra. Vamos ver rapidamente como a consolidação de dados, em várias planilhas, em uma única planilha com a ajuda do Excel 3D Reference ajuda a somar os dados de maneira rápida e precisa.
Sintaxe de referência 3D do Excel
Dê uma olhada na sintaxe de uma fórmula de referência 3D que soma os valores em Cells B4 para quatro folhas, denominadas janeiro, fevereiro, março e abril.
=SUM(January:April!B4)Conforme mostrado acima, existem dois elementos principais na Fórmula 3D do Excel. O primeiro refere-se aos nomes da primeira e da última planilha separados por ponto e vírgula. Na segunda parte, a mesma célula ou intervalo de células é referenciado em todas as quatro planilhas.
Nesse caso, SUM é usado. Você também pode usar qualquer outra fórmula durante o cálculo. No entanto, a sintaxe para referência 3D permanece a mesma. Para usar a referência 3D do Excel, os dados devem ser consistentes nas quatro planilhas.
Exemplo de referência 3D do Excel
Agora vamos entender a lógica por trás da função Excel 3D. Para avaliar isso, vamos colocar em prática usando um conjunto de dados.
Dê uma olhada nos dados de amostra que contêm vendas para os quatro meses, número do estoque, estoque vendido, estoque comprado e estoque final no final de cada mês.
Dados de janeiro:
Dados de fevereiro:
Dados de março:
Dados de abril:
Na primeira etapa, avalie os dados disponíveis em quatro planilhas para ver se são sequenciais. Só então as referências 3D podem ser implementadas.
Implementando a Referência 3D
Para calcular as vendas totais de quatro meses, selecione a célula onde a figura aparecerá.
Para este exemplo, o valor de venda reside na célula B3 para todas as quatro folhas; os dados de referência em quatro planilhas estarão na mesma célula. O intervalo aparecerá de janeiro a abril porque ambos são nomes definidos para as folhas inicial e final, respectivamente.
1. Adicione a célula B3 como uma célula de referência .
2. De janeiro a abril, escolha a faixa a ser usada.
3. Pressione Enter para executar a fórmula.
Para garantir que a fórmula foi aplicada corretamente, você pode somar manualmente os valores de um elemento específico no conjunto de dados. Depois de saber que está correto, você pode esperar que o restante dos cálculos estejam corretos, desde que os valores sejam consistentes.
A mesma abordagem para calcular o valor total de vendas para quatro meses se aplica ao estoque, estoque comprado, vendido e estoque final no final de cada mês.
O intervalo permanece o mesmo por quatro meses, enquanto a célula referenciada muda de acordo com sua localização em planilhas diferentes. Vamos somar os dados do estoque por quatro meses.
1. Adicione a célula B4 como uma célula de referência .
2. Escolha o intervalo a partir de janeiro: abril .
Arraste-o para implementar a fórmula de soma 3D para outras células na linha usando a sequência de preenchimento automático .
Da mesma forma, você pode aplicar referências 3D a um intervalo de células em planilhas diferentes. Nesse caso, a seleção das folhas permanece a mesma.
No entanto, você precisa escolher todo o intervalo em vez de uma célula como referência. Além disso, certifique-se de que os dados sejam consistentes em todas as planilhas.
Adicionando nova planilha na pasta de trabalho
A referência 3D do Excel atualiza automaticamente os dados ao adicionar uma ou mais novas planilhas, desde que essas planilhas estejam entre os intervalos selecionados na fórmula 3D. Vamos ver como isso funciona.
Conforme mostrado abaixo, as vendas totais para quatro meses são 250000 para os meses de janeiro a abril.
Vamos adicionar uma nova planilha contendo os dados do mês de meados de fevereiro.
Incorpore o valor de vendas da folha de meados de fevereiro. Os dados aparecerão automaticamente na planilha principal da análise quadrimestral.
Conforme mostrado acima, as vendas totais para todos os meses aumentam em 10.000. Assim, sem alterar a fórmula, o resultado foi atualizado automaticamente.
Essa flexibilidade é útil ao rastrear o registro de vendas de todo o ano em enormes conjuntos de dados. Vamos adicionar o estoque, a quantidade vendida, a quantidade comprada e os dados do estoque final na planilha de meados de fevereiro.
Dê uma olhada em como a adição acima afeta os dados na planilha de análise de quatro meses.
Portanto, a fórmula de referência atualizou com êxito todos os dados da planilha principal.
Torne os dados consistentes para aplicar referências 3D
Quando você planeja consolidar dados de várias planilhas em uma, certifique-se de que sejam consistentes no número de células. Se, por exemplo, o estoque comprado está localizado em uma determinada célula da folha 1, é necessário colocá-lo na mesma célula em todas as outras folhas. Além disso, ao adicionar uma nova planilha, insira os dados corretamente nas células.
Simplifique os cálculos com a referência 3D do Excel
A implementação de fórmulas 3D pode parecer desafiadora, mas é fácil mantê-las quando você as executa da maneira certa. Ele permite que você adicione novos dados todos os meses sem ter que fazer os cálculos desde o início.
A fórmula 3D também elimina as chances de duplicação ou omissão de vendas em um determinado mês. Essa funcionalidade será mais benéfica para contadores que atualizam pacotes de dados todos os meses.
