Como Encontrar Referência Circular no Excel (Rápido e Fácil)

Ao trabalhar com fórmulas do Excel, às vezes você pode ver o seguinte prompt de aviso.

Este prompt informa que há uma referência circular em sua planilha e isso pode levar a um cálculo incorreto pelas fórmulas. Ele também pede que você trate deste problema de referência circular e o classifique.

Neste tutorial, vou cobrir tudo o que você precisa saber sobre referência circular, e também como encontrar e remover referências circulares no Excel.

Então vamos começar!

O que é referência circular no Excel?

Em palavras simples, uma referência circular acontece quando você acaba tendo uma fórmula em uma célula - que por si só usa a célula (na qual foi inserida) para o cálculo.

Deixe-me tentar explicar isso com um exemplo simples.

Suponha que você tenha o conjunto de dados na célula A1: A5 e use a fórmula abaixo na célula A6:

= SOMA (A1: A6)

Isso lhe dará um aviso de referência circular.

Isso ocorre porque você deseja somar os valores na célula A1: A6 e o ​​resultado deve estar na célula A6.

Isso cria um loop, pois o Excel continua adicionando o novo valor na célula A6, que continua mudando (portanto, um loop de referência circular).

Como encontrar referências circulares no Excel?

Embora o aviso de aviso de referência circular seja gentil o suficiente para dizer que ele existe em sua planilha, ele não diz onde está acontecendo e quais referências de célula estão causando isso.

Então, se você está tentando encontrar e lidar com referências circulares na planilha, você precisa saber uma maneira de encontrá-las de alguma forma.

Abaixo estão as etapas para encontrar uma referência circular no Excel:

1. Ative a planilha que contém a referência circular
2. Clique na guia Fórmulas
3. No grupo Formula Editing, clique no ícone suspenso Error Checking (pequena seta apontando para baixo à direita)
4. Passe o cursor sobre a opção Referências circulares. Irá mostrar-lhe a célula que tem uma referência circular na folha de trabalho
5. Clique no endereço da célula (que é exibido) e isso o levará para essa célula na planilha.

Depois de resolver o problema, você pode seguir novamente as mesmas etapas acima e ele mostrará mais referências de células que têm a referência circular. Se não houver nenhuma, você não verá nenhuma referência de célula,

Outra maneira rápida e fácil de encontrar a referência circular é olhando a barra de status. À esquerda, será mostrado o texto Referência circular junto com o endereço da célula.

Existem algumas coisas que você precisa saber ao trabalhar com referências circulares:

1. Caso o cálculo iterativo esteja habilitado (abordado posteriormente neste tutorial), a barra de status não mostrará o endereço da célula de referência circular
2. Caso a referência circular não esteja na planilha ativa (mas em outras planilhas da mesma pasta de trabalho), mostrará apenas a referência circular e não o endereço da célula
3. No caso de você receber um prompt de aviso de referência circular uma vez e descartá-lo, ele não mostrará o prompt novamente na próxima vez.
4. Se você abrir uma pasta de trabalho que tenha a referência circular, o prompt será exibido assim que a pasta de trabalho for aberta.

Como remover uma referência circular no Excel?

Depois de identificar que há referências circulares em sua planilha, é hora de removê-las (a menos que você queira que elas estejam lá por um motivo).

Infelizmente, não é tão simples quanto apertar a tecla delete. Como eles dependem de fórmulas e cada fórmula é diferente, você precisa analisar isso caso a caso.

Caso seja apenas uma questão de ter a referência de célula causando o problema por engano, você pode simplesmente corrigir ajustando a referência.

Mas às vezes não é tão simples.

A referência circular também pode ser causada com base em várias células que se alimentam em vários níveis.

Deixe-me mostrar-lhe um exemplo.

Abaixo, há uma referência circular na célula C6, mas não é apenas um caso simples de autorreferência. É multinível, onde as células que usa nos cálculos também fazem referência umas às outras.

• A fórmula na célula A6 é = SUM (A1: A5) + C6
• A fórmula da célula C1 é = A6 * 0,1
• A fórmula na célula C6 é = A6 + C1

No exemplo acima, o resultado na célula C6 é dependente dos valores na célula A6 e C1, que por sua vez são dependentes da célula C6 (causando assim o erro de referência circular)

E, novamente, escolhi um exemplo realmente simples apenas para fins de demonstração. Na realidade, eles podem ser muito difíceis de descobrir e podem estar distantes na mesma planilha ou até mesmo espalhados por várias planilhas.

Nesse caso, há uma maneira de identificar as células que estão causando a referência circular e, em seguida, tratá-las.

É usando a opção Rastrear precedentes.

Abaixo estão as etapas para usar precedentes de rastreamento para encontrar células que estão alimentando a célula que tem a referência circular:

1. Selecione a célula que tem a referência circular
2. Clique na guia Fórmulas
3. Clique em Rastrear precedentes

As etapas acima mostram setas azuis que indicam quais células estão alimentando a fórmula na célula selecionada. Dessa forma, você pode inspecionar as fórmulas e as células e se livrar da referência circular.

Caso você esteja trabalhando com modelos financeiros complexos, pode ser possível que esses precedentes também alcancem vários níveis de profundidade.

Isso funciona bem se você tiver todas as fórmulas referentes às células na mesma planilha. Se estiver em várias planilhas, este método não é eficaz.

Como habilitar / desabilitar cálculos iterativos no Excel

Quando você tem uma referência circular em uma célula, primeiro obtém o aviso de aviso conforme mostrado abaixo e, se fechar esta caixa de diálogo, receberá 0 como resultado na célula.

Isso ocorre porque quando há uma referência circular, é um loop infinito e o Excel não quer se envolver nisso. Portanto, ele retorna um 0.

Mas, em alguns casos, você pode realmente querer que a referência circular esteja ativa e faça algumas iterações. Nesse caso, em vez de um loop infinito, você pode decidir quantas vezes o loop deve ser executado.

Isso é chamado cálculo iterativo no Excel.

Abaixo estão as etapas para habilitar e configurar cálculos iterativos no Excel:

1. Clique na guia Arquivo
2. Clique em Opções. Isso abrirá a caixa de diálogo Opções do Excel
3. Selecione Fórmula no painel esquerdo
4. Na seção Opções de cálculo, marque a caixa "Ativar cálculo iterativo". Aqui você pode especificar as iterações máximas e o valor máximo de mudança

É isso! As etapas acima permitiriam o cálculo iterativo no 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 você deseja que o Excel calcule antes de fornecer o resultado final. Portanto, se você especificar 100, o Excel executará o loop 100 vezes antes de fornecer o resultado final.
• Mudança Máxima: Esta é a alteração máxima que, se não for alcançada entre as iterações, o cálculo será interrompido. Por padrão, o valor é 0,001. Quanto menor for esse valor, mais preciso será o resultado.

Lembre-se de que quanto maior o número de vezes que as iterações são executadas, mais tempo e recursos o Excel leva para fazê-lo. Caso você mantenha o máximo de iterações altas, isso pode fazer com que seu Excel fique mais lento ou falhe.

Observação: quando os cálculos iterativos estão habilitados, o Excel não mostra o prompt de aviso de referência circular e agora também o mostra na barra de status.

Usando deliberadamente referências circulares

Na maioria dos casos, a presença de referência circular em sua planilha seria um erro. E é por isso que o Excel mostra um prompt que diz - “Tente remover ou alterar essas referências ou mover as fórmulas para células diferentes”.

Mas pode haver alguns casos específicos em que você precisa de uma referência circular para obter o resultado desejado.

Já escrevi um caso específico sobre ele obter o carimbo de hora em uma célula em uma célula do Excel.

Por exemplo, suponha que você deseja criar uma fórmula para que cada entrada seja feita em uma célula na Coluna A, o carimbo de data / hora apareça na Coluna B (conforme mostrado abaixo):

Embora você possa inserir facilmente um carimbo de data / hora usando a fórmula abaixo:

= SE (A2 "", SE (B2 "", B2, AGORA ()), "")

O problema com a fórmula acima é que ela atualizaria todos os carimbos de data / hora assim que qualquer alteração fosse feita na planilha ou se a planilha fosse reaberta (já que a fórmula AGORA é volátil)

Para contornar esse problema, você pode usar um método de referência circular. Use a mesma fórmula, mas habilite o cálculo iterativo.

Existem alguns outros casos em que é desejável ter a capacidade de usar a referência circular (você pode encontrar um exemplo aqui).

Observação: embora possa haver alguns casos em que você pode usar a referência circular, acho melhor evitá-la. As referências circulares também podem prejudicar o desempenho de sua pasta de trabalho e torná-la lenta. Em casos raros em que você precisa, sempre prefiro usar códigos VBA para fazer o trabalho.

Espero que você tenha achado este tutorial útil!

Outros tutoriais do Excel que você pode achar úteis:

• #REF! Erro no Excel; Como corrigir o erro de referência!
• Tratamento de erros do Excel VBA
• Use IFERROR com VLOOKUP para se livrar de erros # N / A
• Como fazer referência a outra planilha ou pasta de trabalho no Excel (com exemplos)
• Referências de células absolutas, relativas e mistas no Excel