Ao trabalhar com fórmulas Excel, poderá, em algum momento, ver o seguinte aviso
Este aviso diz-lhe que existe uma referência circular na sua folha de trabalho e isto pode conduzir ou um cálculo incorrecto pelas fórmulas. Pede-lhe também que aborde esta questão da referência circular e a ordene.
Neste tutorial, vou cobrir tudo o que precisa de saber sobre referência circular, e também como encontrar e remover referências circulares no Excel.
Então vamos começar!
Este Tutorial Cobre:
O que é Referência Circular no Excel?
Em palavras simples, uma referência circular acontece quando se acaba por ter uma fórmula numa célula – que em si mesma utiliza a célula (na qual foi introduzida) para o cálculo.
Deixe-me tentar explicar isto através de um exemplo simples.
P>P>Ponha que tem o conjunto de dados na célula A1:A5 e usa a fórmula abaixo na célula A6:
=SUM(A1:A6)
Isto dar-lhe-á um aviso de referência circular.
Isto é porque quer somar os valores na célula A1:A6, e o resultado deve estar na célula A6.
Isto cria um loop, uma vez que o Excel continua a adicionar o novo valor na célula A6, que continua a mudar (daí, um loop de referência circular).
Como encontrar referências circulares no Excel?
Embora o aviso de referência circular seja suficientemente gentil para lhe dizer que existe na sua folha de trabalho, não lhe diz onde está a acontecer e que células de referência estão a causá-lo.
Então, se estiver a tentar encontrar e tratar referências circulares na folha de trabalho, precisa de saber uma forma de as encontrar de alguma forma.
Below são os passos para encontrar uma referência circular no Excel:
- Activar a folha de trabalho que tem a referência circular
- Clicar na tabulação Fórmulas
- No grupo Edição de Fórmulas, clicar no ícone de verificação de erros (pequena seta para baixo à direita)
- Passar o cursor sobre a opção Referências Circulares. Mostrar-lhe-á a célula que tem uma referência circular na folha de trabalho
- Clique no endereço da célula (que é mostrada) e levar-lhe-á a essa célula na folha de trabalho.
Após ter abordado o problema, pode novamente seguir os mesmos passos acima e mostrará mais referências de células que têm a referência circular. Se não houver nenhuma, não verá nenhuma referência de célula,
Outra forma rápida e fácil de encontrar a referência circular é olhando para a barra de estado. Na parte esquerda da mesma, mostrar-lhe-á o texto Referência Circular juntamente com o endereço da célula.
Há algumas coisas que precisa de saber ao trabalhar com referências circulares:
- Caso o cálculo iterativo esteja activado (abordado mais tarde neste tutorial), a barra de estado não mostrará o endereço da célula de referência circular
- Caso a referência circular não esteja na folha activa (mas noutras folhas da mesma pasta de trabalho), mostrará apenas a referência circular e não o endereço da célula
- Caso obtenha um aviso de referência circular uma vez e o rejeite, não mostrará novamente o aviso da próxima vez.
- Se abrir uma pasta de trabalho que tenha a referência circular, ela mostrar-lhe-á o aviso assim que a pasta de trabalho abrir.
Como remover uma referência circular no Excel?
Após ter identificado que existem referências circulares na sua folha, é altura de as remover (a menos que queira que elas estejam lá por uma razão).
Felizmente, não é tão simples como premir a tecla Delete. Uma vez que estas dependem de fórmulas e cada fórmula é diferente, é necessário analisar isto caso a caso.
No caso de ser apenas uma questão de ter a referência da célula que causa o problema por engano, pode simplesmente corrigir, ajustando a referência.
Mas por vezes, não é assim tão simples.
A referência circular também pode ser causada com base em múltiplas células que se alimentam umas às outras a muitos níveis.
Deixe-me mostrar-lhe um exemplo.
Below existe uma referência circular na célula C6, mas não é apenas um simples caso de auto-referência. É multi-nível onde as células que utiliza nos cálculos também se referem umas às outras.
- As fórmulas na célula A6 é =SUM(A1:A5)+C6
- A fórmula é a célula C1 é =A6*0.1
- A fórmula na célula C6 é =A6+C1
No exemplo acima, o resultado na célula C6 depende dos valores na célula A6 e C1, que por sua vez dependem da célula C6 (causando assim o erro circular de referência)
E mais uma vez, escolhi um exemplo realmente simples apenas para fins de demonstração. Na realidade, estes podem ser bastante difíceis de perceber e talvez muito distantes na mesma folha de trabalho ou mesmo espalhados por múltiplas folhas de trabalho.
Em tal caso, há uma forma de identificar as células que estão a causar referência circular e depois tratá-las.
É utilizando a opção Trace Precedents.
Below são os passos para utilizar os precedentes de rastreio para encontrar células que estão a alimentar a célula que tem a referência circular:
- Seleccionar a célula que tem a referência circular
- Clique na tabulação Fórmulas
- Clique em Trace Precedents
Os passos acima mostrariam setas azuis que lhe dirão que células estão a alimentar a fórmula na célula seleccionada. Desta forma, pode inspeccionar as fórmulas e as células e livrar-se da referência circular.
No caso de estar a trabalhar com modelos financeiros complexos, pode ser possível que estes precedentes também aprofundem vários níveis.
Isto funciona bem se tiver todas as fórmulas referentes a células na mesma folha de trabalho. Se estiver em múltiplas folhas de trabalho, este método não é eficaz.
Como Activar/Desactivar Cálculos Iteractivos no Excel
Quando tem uma referência circular numa célula, primeiro recebe o aviso de aviso como mostrado abaixo, e se fechar esta caixa de diálogo, dar-lhe-á 0 como resultado na célula.
Isto é porque quando há uma referência circular, é um loop interminável e o Excel não quer ficar preso nela. Assim, retorna um 0.
mas, em alguns casos, poderá querer que a referência circular esteja activa e fazer um par de iterações. Em tal caso, em vez de um loop infinito, e pode decidir quantas vezes o loop deve ser executado.
Este chama-se cálculo iterativo no Excel.
Below são os passos para activar e configurar cálculos iterativos no Excel:
- Clique no separador Ficheiro
- Clique em Opções. Isto abrirá a caixa de diálogo Opções do Excel
- Select Formula in the left pane
- Na secção Opções de Cálculo, marcar a caixa ‘Enable iterative calculation’. Aqui pode especificar as iterações máximas e o valor de alteração máximo
é isso! Os passos acima permitiriam o cálculo iterativo em Excel.
Deixe-me também explicar rapidamente as duas opções no cálculo iterativo:
- iterações máximas: Este é o número máximo de vezes que deseja que o Excel calcule antes de lhe dar o resultado final. Assim, se especificar isto como 100, o Excel executará o loop 100 vezes antes de lhe dar o resultado final.
- Alteração Máxima: Esta é a alteração máxima, que se não for conseguida entre iterações, o cálculo será interrompido. Por defeito, o valor é .001. Quanto menor este valor, mais exacto seria o resultado.
Lembra-te de que quanto mais vezes as iterações forem executadas, mais tempo e recursos são necessários para que o Excel o faça. No caso de manter o máximo de iterações elevado, pode levar a que o Excel abrande ou caia.
Usando Deliberadamente Referências Circulares
Na maioria dos casos, a presença de referência circular na sua folha de trabalho seria um erro. E é por isso que o Excel lhe mostra um prompt que diz – “Tente remover ou alterar estas referências ou mover as fórmulas para células diferentes”
Mas pode haver alguns casos específicos em que precise de uma referência circular para que possa obter o resultado desejado.
Um caso específico como este já escrevi sobre o mesmo obtendo o carimbo de tempo numa célula de uma célula do Excel.
Por exemplo, suponha que pretende criar uma fórmula para que cada entrada seja feita numa célula na coluna A, o carimbo de data/hora aparece na coluna B (como mostrado abaixo):
Enquanto pode inserir facilmente um carimbo de data/hora usando a fórmula abaixo:
=IF(A2<>"",IF(B2<>"",B2,NOW()),"")
A questão com a fórmula acima é que actualizaria todos os carimbos de data/hora assim que fosse feita qualquer alteração na folha de trabalho ou se a folha de trabalho fosse reaberta (pois a fórmula NOW é volátil)
Para contornar esta questão, pode usar um método de referência circular. Use a mesma fórmula, mas permita o cálculo iterativo.
Existem também alguns outros casos em que é desejável ter a capacidade de usar referência circular (pode encontrar um exemplo aqui).
Espero que tenham achado este tutorial útil!
Outros tutoriais Excel que possam achar úteis: