Um dos avisos de erro mais comuns que os usuários encontram no Excel é a ‘Referência circular’. Milhares de usuários têm o mesmo problema, e isso ocorre quando uma fórmula faz referência a sua própria célula direta ou indiretamente, causando um loop infinito de cálculos.
Por exemplo, você tem dois valores nas células B1 e B2. Quando a fórmula = B1 + B2 é inserida em B2, ela cria uma referência circular; a fórmula em B2 se recalcula repetidamente porque cada vez que ela calcula, o valor de B2 muda.
A maioria das referências circulares são erros não intencionais; O Excel irá avisá-lo sobre isso. No entanto, também existem referências circulares pretendidas, que são usadas para fazer cálculos iterativos. Referências circulares não intencionais em sua planilha podem fazer com que sua fórmula seja calculada incorretamente.
Portanto, neste artigo, explicaremos tudo o que você precisa saber sobre referências circulares e também como localizar, corrigir, remover e usar referências circulares no Excel.
Como encontrar e lidar com referências circulares no Excel
Ao trabalhar com o Excel, às vezes encontramos erros de referência circular que acontecem quando você insere uma fórmula que inclui a célula onde sua fórmula reside. Basicamente, isso acontece quando sua fórmula está tentando calcular a si mesma.
Por exemplo, você tem uma coluna de números na célula A1: A4 e está usando a função SOMA (= SOMA (A1: A5)) na célula A5. A célula A5 se refere diretamente à sua própria célula, o que está errado. Portanto, você receberá o seguinte aviso de referência circular:
Depois de receber a mensagem de aviso acima, você pode clicar no botão ‘Ajuda’ para saber mais sobre o erro ou fechar a janela da mensagem de erro clicando no botão ‘OK’ ou ‘X’ e obter ‘0’ como resultado.
Às vezes, os loops de referência circulares podem causar falha no cálculo ou diminuir o desempenho da planilha. A referência circular também pode levar a uma série de outros problemas, que não serão evidentes imediatamente. Portanto, é melhor evitá-los.
Referências circulares diretas e indiretas
As referências circulares podem ser categorizadas em dois tipos: Referências circulares diretas e Referências circulares indiretas.
Referência Direta
Uma referência circular direta é muito simples. A mensagem de aviso de referência circular direta aparece quando a fórmula se refere diretamente à sua própria célula.
No exemplo abaixo, a fórmula na célula A2 se refere diretamente à sua própria célula (A2).
Assim que a mensagem de aviso aparecer, você pode clicar em ‘OK’, mas isso resultará apenas em ‘0’.
Referência Circular Indireta
Uma referência circular indireta no Excel ocorre quando um valor em uma fórmula se refere à sua própria célula, mas não diretamente. Em outras palavras, a referência circular pode ser formada por duas células que fazem referência uma à outra.
Vamos explicar com este exemplo simples.
Agora o valor está começando em A1, que tem o valor 20.
Em seguida, a célula C3 se refere à célula A1.
Então, a célula A5 se refere à célula C3.
Agora substitua o valor 20 na célula A1 pela fórmula conforme mostrado abaixo. Todas as outras células dependem da célula A1. Quando você usa uma referência de qualquer outra célula de fórmula anterior em A1, isso causará um aviso de referência circular. Porque a fórmula em A1 se refere à célula A5, que se refere a C3, e a célula C3 se refere novamente a A1, daí a referência circular.
Quando você clica em 'OK', isso resulta em um valor de 0 na célula A1 e o Excel cria uma linha vinculada mostrando os precedentes de rastreamento e os dependentes de rastreamento como mostrado abaixo. Podemos usar esse recurso para localizar e corrigir / remover facilmente referências circulares.
Como ativar / desativar referências circulares no Excel
Por padrão, os cálculos iterativos estão desligados (desabilitados) no Excel. Cálculos iterativos são cálculos repetitivos até que atenda a uma condição específica. Quando está desabilitado, o Excel mostra uma mensagem de Referência Circular e retorna um 0 como resultado.
No entanto, às vezes são necessárias referências circulares para calcular um loop. Para usar a referência circular, você deve habilitar cálculos iterativos em seu Excel e isso permitirá que você execute seus cálculos. Agora, vamos mostrar como você pode habilitar ou desabilitar cálculos iterativos.
No Excel 2010, Excel 2013, Excel 2016, Excel 2019 e Microsoft 365, vá para a guia ‘Arquivo’ no canto superior esquerdo do Excel e clique em ‘Opções’ no painel esquerdo.
Na janela Opções do Excel, vá para a guia "Fórmula" e marque a caixa de seleção "Ativar cálculo iterativo" na seção "Opções de cálculo". Em seguida, clique em ‘OK’ para salvar as alterações.
Isso habilitará o cálculo iterativo e, assim, permitirá a referência circular.
Para conseguir isso em versões anteriores do Excel, siga estas etapas:
- No Excel 2007, clique no botão Office> Opções do Excel> Fórmulas> Área de iteração.
- No Excel 2003 e em versões anteriores, você precisa ir para Menu> Ferramentas> Opções> guia Cálculo.
Iterações máximas e parâmetros de mudança máximos
Depois de habilitar os cálculos iterativos, você pode controlar os cálculos iterativos, especificando duas opções disponíveis na seção Habilitar cálculo iterativo, conforme mostrado na captura de tela abaixo.
- Iterações máximas - Este número especifica quantas vezes a fórmula deve ser recalculada antes de fornecer o resultado final. O valor padrão é 100. Se você alterar para '50', o Excel repetirá os cálculos 50 vezes antes de fornecer o resultado final. Lembre-se de que quanto maior o número de iterações, mais recursos e tempo leva para calcular.
- Mudança Máxima - Determina a variação máxima entre os resultados dos cálculos. Este valor determina a precisão do resultado. Quanto menor o número, mais preciso será o resultado e mais demorado será o cálculo da planilha.
Se a opção de cálculos iterativos estiver habilitada, você não receberá nenhum aviso sempre que houver uma referência circular em sua planilha. Habilite o cálculo interativo apenas quando for absolutamente necessário.
Encontre referência circular no Excel
Suponha que você tenha um grande conjunto de dados e tenha obtido o aviso de referência circular; ainda assim, você precisará descobrir onde (em qual célula) o erro ocorreu para corrigi-lo. Para encontrar referências circulares no Excel, siga estas etapas:
Usando ferramenta de verificação de erros
Primeiro, abra a planilha onde a referência circular aconteceu. Vá para a guia ‘Fórmula’, clique na seta ao lado da ferramenta ‘Verificação de erros’. Em seguida, basta passar o cursor sobre a opção ‘Referências circulares’, o Excel mostrará a lista de todas as células que estão envolvidas na referência circular, conforme mostrado abaixo.
Clique no endereço de qualquer célula que você deseja na lista e isso o levará a esse endereço de célula para resolver o problema.
Usando a barra de status
Você também pode encontrar a referência circular na barra de status. Na barra de status do Excel, ele mostrará o endereço da célula mais recente com uma referência circular, como ‘Referências circulares: B6’ (veja a captura de tela abaixo).
Existem certas coisas que você deve saber ao lidar com a referência circular:
- A barra de status não mostrará o endereço da célula de referência circular quando a opção Cálculo Iterativo estiver habilitada, portanto, você precisa desabilitá-la antes de começar a procurar referências circulares na pasta de trabalho.
- Caso a referência circular não seja encontrada na planilha ativa, a barra de status exibe apenas 'Referências circulares' sem endereço de célula.
- Você receberá um prompt de referência circular apenas uma vez e, depois de clicar em 'OK', ele não mostrará o prompt novamente na próxima vez.
- Se sua pasta de trabalho tiver referências circulares, ela mostrará o prompt toda vez que você abri-la, até resolver a referência circular ou até ativar o cálculo iterativo.
Remover uma referência circular no Excel
Encontrar referências circulares é fácil, mas corrigi-lo não é tão simples. Infelizmente, não há nenhuma opção no Excel que permita remover todas as referências circulares de uma vez.
Para corrigir referências circulares, você deve encontrar cada referência circular individualmente e tentar modificá-la, remover a fórmula circular por completo ou substituí-la por outra.
Às vezes, em fórmulas simples, tudo o que você precisa fazer é reajustar os parâmetros da fórmula para que ela não se refira a si mesma. Por exemplo, altere a fórmula em B6 para = SUM (B1: B5) * A5 (alterando B6 para B5).
Ele retornará o resultado do cálculo como '756'.
Nos casos em que uma referência circular do Excel é difícil de encontrar, você pode usar os recursos Rastrear precedentes e Rastrear dependentes para rastreá-la de volta à origem e resolvê-la um por um. A seta mostra quais células são afetadas pela célula ativa.
Existem dois métodos de rastreamento que podem ajudá-lo a excluir referências circulares, mostrando as relações entre fórmulas e células.
Para acessar os métodos de rastreamento, vá para a guia "Fórmulas" e clique em "Rastrear precedentes" ou "Rastrear dependentes" no grupo Auditoria de fórmula.
Rastrear precedentes
Quando você seleciona esta opção, ela rastreia as células que afetam o valor da célula ativa. Ele desenha uma linha azul indicando quais células afetam a célula atual. A tecla de atalho para usar precedentes de rastreamento é Alt + T U T.
No exemplo abaixo, a seta azul mostra que as células que afetam o valor B6 são B1: B6 e A5. Como você pode ver abaixo, a célula B6 também faz parte da fórmula, o que a torna uma referência circular e faz com que a fórmula retorne '0' como resultado.
Isso pode ser facilmente corrigido substituindo B6 por B5 no argumento de SUM: = SUM (B1: B5).
Dependentes de rastreamento
O recurso rastrear dependentes rastreia as células que são dependentes da célula selecionada. Este recurso desenha uma linha azul indicando quais células são afetadas pela célula selecionada. Ou seja, ele exibe quais células contêm fórmulas que fazem referência à célula ativa. A tecla de atalho para usar dependentes é Alt + T U D.
No exemplo a seguir, a célula D3 é afetada por B4. É dependente de B4 para que seu valor produza resultados. Portanto, o traço dependente desenha uma linha azul de B4 a D3, indicando que D3 é dependente de B4.
Usando deliberadamente referências circulares no Excel
Usar referências circulares deliberadamente não é recomendado, mas pode haver alguns casos raros em que você precisa de uma referência circular para obter a saída desejada.
Vamos explicar isso usando um exemplo.
Para começar, ative ‘Cálculo Iterativo’ em sua pasta de trabalho do Excel. Depois de habilitar o Cálculo iterativo, você pode começar a usar referências circulares a seu favor.
Suponhamos que você esteja comprando uma casa e queira dar uma comissão de 2% sobre o custo total da casa ao seu corretor. O custo total será calculado na célula B6 e o percentual de comissão (taxa do agente) será calculado na B4. A comissão é calculada a partir do custo total e o custo total inclui a comissão. Como as células B4 e B6 dependem umas das outras, ele cria uma referência circular.
Insira a fórmula para calcular o custo total na célula B6:
= SOMA (B1: B4)
Como o custo total inclui a taxa do agente, incluímos B4 na fórmula acima.
Para calcular a taxa do agente de 2%, insira esta fórmula em B4:
= B6 * 2%Agora, a fórmula na célula B4 depende do valor de B6 para calcular 2% da taxa total e a fórmula em B6 depende de B4 para calcular o custo total (incluindo a taxa do agente), daí a referência circular.
Se o cálculo iterativo estiver ativado, o Excel não fornecerá um aviso ou um 0 no resultado. Em vez disso, o resultado das células B6 e B4 será calculado como mostrado acima.
A opção de cálculos iterativos geralmente está desabilitada por padrão. Se você não ligou e quando você inserir a fórmula em B4, que criará uma referência circular. O Excel emitirá o aviso e quando você clicar em ‘OK’, a seta do rastreador será exibida.
É isso. Isso é tudo que você precisa saber sobre referências circulares no Excel.