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, criando um ciclo infinito de cálculos.
Por exemplo, você tem dois valores nas células A1 e A2. Quando a fórmula=A1 + A2 é inserida em A2, ela cria uma referência circular; a fórmula em A2 se recalcula repetidamente porque cada vez que ela calcula, o valor de A2 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 pasta de trabalho podem fazer com que sua fórmula seja calculada incorretamente.
Portanto, neste artigo, cobriremos tudo o que você precisa saber sobre referências circulares e também como encontrar, 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 se calcular.
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 refere-se diretamente à sua própria célula, o que não é possível. Portanto, você receberá o seguinte aviso de referência circular:
Depois de receber a mensagem de aviso acima, você pode clicar em ‘Ajuda’ para obter mais informações ou fechar a janela da mensagem clicando no botão ‘OK’ ou ‘X’ e obter ‘0’ como resultado.
Esses loops de referência circulares podem travar seu cálculo ou diminuir o desempenho da planilha. Eles também podem levar a uma série de outros problemas, que não são imediatamente aparentes. 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 é exibida quando a fórmula em uma célula se refere diretamente à sua própria célula.
Por exemplo, a fórmula na célula A2 abaixo se refere diretamente à sua própria célula (A2).
Assim que a mensagem de aviso for exibida, o usuário pode clicar em ‘OK’, mas isso resultará apenas em ‘0’.
Referência circular indireta
Uma referência circular indireta nos impostos do Excel ocorre quando um valor em uma fórmula se refere à sua própria célula, não diretamente, mas em algum nível. Em outras palavras, a referência circular pode ser estabelecida por duas células direcionadas uma à outra.
Vejamos um 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 em A1, ele criará 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’, 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, conforme mostrado na captura de tela abaixo. Podemos usar esse recurso para encontrar e corrigir facilmente a referência circular.
Como ativar/desativar referências circulares no Excel
Por padrão, os cálculos iterativos estão desabilitados no Excel. Cálculos iterativos são cálculos repetidos até que uma condição específica seja atendida. Quando desativado, o Excel retorna um prompt de Referência circular e retorna 0 como resultado.
No entanto, existem alguns casos raros em que referências circulares são necessárias para realizar um loop. Para usar a referência circular, você deve habilitar cálculos iterativos em sua pasta de trabalho do 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 versões anteriores, você precisa ir para Menu> Ferramentas> Opções> Cálculo guia.
Iterações máximas e parâmetros de alteração 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 -Especifica quantas vezes a fórmula deve ser recalculada antes de fornecer o resultado final. Como podemos ver aqui, o valor padrão é 100. Isso significa que o Excel repetirá os cálculos 100 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.
- Alteração máxima -determina a alteração máxima entre os resultados do cálculo. É sobre a precisão do resultado. Quanto menor o número, mais preciso será o resultado e mais demorado será o cálculo da planilha.
Depois disso, 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.
Encontrar referência circular no Excel
Suponha que você tenha um grande conjunto de dados e receba o aviso de referência circular; ainda assim, você precisará descobrir em qual célula o erro ocorreu para corrigi-lo. Para encontrar referências circulares no Excel, prossiga com as seguintes etapas:
Usando a ferramenta de verificação de erros
Primeiro, abra a planilha onde ocorreu a referência circular. Vá para a guia ‘Fórmula’, clique na seta ao lado da ferramenta ‘Verificação de erros’. Em seguida, passe 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 desejar e isso o levará a essa célula específica para solucionar o problema.
Usando a barra de status
Outra maneira fácil de encontrar a referência circular é olhando a barra de status. Na barra de status do Excel, ele mostrará o endereço de célula mais recente com uma referência circular, como ‘Referências circulares: B6’ (veja a captura de tela abaixo).
Existem algumas coisas que você deve saber ao trabalhar com 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, então você precisa desligá-la antes de começar a verificar se há referências circulares na pasta de trabalho.
- Em 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ê só receberá um aviso de referência circular uma vez e, depois de descartá-la, não mostre o prompt novamente na próxima vez.
- Se sua pasta de trabalho tiver referências circulares, ele mostrará o prompt toda vez que você abri-lo até que você resolva a referência circular ou até que você ative o cálculo iterativo.
Remova uma referência circular no Excel
Encontrar referências circulares é fácil, mas consertá-las não é tão simples. Infelizmente, não há nada 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 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 indica quais células são afetadas pela célula ativa.
Existem dois métodos de rastreamento que permitem excluir referências circulares, rastreando 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 está incluída, o que a torna uma referência circular e faz com que a fórmula retorne 0.
Isso pode ser facilmente corrigido substituindo B6 por B5 no argumento SUM:=SUM (B1: B5).
Rastrear dependentes
O recurso rastrear dependentes rastreia as células que são dependentes da célula ativa. Este recurso desenha linhas indicando quais células são afetadas pela célula selecionada. Em outras palavras, ele mostra 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 específicos em que você precisa de uma referência circular para obter o resultado desejado.
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 para seu benefício.
Suponhamos que você esteja comprando uma casa e tenha decidido dar ao seu agente uma comissão de 2% sobre o custo total. 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:
=SUM (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), portanto, 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 conforme mostrado acima.
O cálculo iterativo geralmente é desabilitado 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.
