Como encontrar referências circulares no Excel

Ao trabalhar com o Microsoft Excel , você pode ocasionalmente encontrar o que é conhecido como referência circular. É quando uma fórmula em uma célula se refere a si mesma, causando um loop infinito no processo de cálculo.

Para encontrar referências circulares no Excel, use a ferramenta “Verificação de erros” na guia “Fórmulas” para identificar células com uma dependência circular.

Este artigo mostra como encontrar referências circulares no Excel e corrigi-las. Você também aprenderá como evitá-los ao trabalhar com planilhas grandes ou complexas.

Índice

• O que são referências circulares
• A Janela de Mensagem de Erro de Referência Circular
• Como encontrar referências circulares no Excel
• Como encontrar referências circulares com a barra de status do Excel
• Como encontrar referências circulares ocultas no Excel
  • Como usar a ferramenta Trace Dependent
  • Ferramenta Rastrear Precedentes
• 2 maneiras de remover referências circulares no Excel
  • 2. Use fórmulas alternativas
• 5 dicas para evitar referências circulares
  • 1. Tenha cuidado ao selecionar intervalos
  • 2. Planeje suas fórmulas com cuidado
  • 3. Use fórmulas alternativas
  • 4. Divida fórmulas complexas em partes menores
  • 5. Corrija os problemas à medida que avança
• Como ativar o cálculo iterativo e por que se preocupar?

O que são referências circulares

Referências circulares ocorrem no Excel quando um cálculo de fórmula em uma célula depende do próprio valor da célula. Isso cria um loop e leva a resultados incorretos ou imprevisíveis.

Isso fica mais fácil de entender com um exemplo simples.

Suponha que você tenha dois números nas células A1 e B1. Você deseja usar uma fórmula na célula A3 que some os dois números. A fórmula na célula A3 deve ficar assim:

=A1 + B1

No entanto, se você cometer um erro e incluir C1 nos cálculos, agora terá uma referência circular:

=A1 + B1 + C1

Isso pode ser um problema óbvio a ser evitado ao lidar com duas células. No entanto, quando você está trabalhando com grandes intervalos de células, os erros se tornam mais prováveis.

Felizmente, o Excel foi projetado para lidar com essas situações exibindo uma mensagem de aviso quando uma referência circular é detectada.

No entanto, em alguns casos, você pode encontrar uma referência circular sem receber nenhum alerta. Portanto, é importante saber como localizar e resolver esses problemas manualmente.

A Janela de Mensagem de Erro de Referência Circular

Por padrão, o Excel monitora referências circulares conforme você insere fórmulas nas células. Quando você pressiona enter em uma célula com um problema, uma janela pop-up exibe a mensagem de erro de referência circular.

Este é o texto explicativo:

Há uma ou mais referências circulares em que uma fórmula se refere à sua própria célula direta ou indiretamente. Isso pode fazer com que eles calculem incorretamente. Tente remover ou alterar essas referências ou mover as fórmulas para células diferentes.

Ao ver esta caixa de diálogo, clique em "OK" e use os métodos deste artigo para corrigir o problema.

No entanto, você não precisa corrigir o erro imediatamente. Você pode continuar trabalhando com a planilha com a intenção de resolver o problema posteriormente.

Nesse caso, você precisa saber como encontrar referências circulares no Excel depois de descartar a caixa de aviso.

Como encontrar referências circulares no Excel

O Excel possui uma ferramenta interna de verificação de erros que encontra vários tipos de erros, incluindo referências circulares. Você acessa a ferramenta na aba Fórmulas.

Esta imagem mostra a localização dentro do grupo de Auditoria de Fórmula.

Agora que você sabe como encontrar a ferramenta, aqui estão as etapas detalhadas para usá-la:

1. Vá para a guia Fórmulas.

1. Clique no menu suspenso Verificação de erros no grupo Auditoria de fórmulas.

2. Selecione "Referências circulares" na lista suspensa.

3. Clique no link para pular o cursor para a primeira célula com um problema.

A figura abaixo mostra que a ferramenta encontrou um erro circular na célula G1. A célula exibida é um hiperlink.

Quando você clica no link, o cursor se move para a célula. Isso é muito útil com um grande volume de dados.

Depois de resolver a primeira referência circular, você pode voltar para a ferramenta de verificação de erros para encontrar a próxima na folha.

Use as etapas nas seções posteriores deste artigo para corrigir o problema e usar a ferramenta para ir para o próximo problema.

Se não houver mais problemas circulares, o item de menu “Referências circulares” no menu suspenso ficará esmaecido. Você não tem mais trabalho a fazer.

Como encontrar referências circulares com a barra de status do Excel

Outra maneira de identificar uma referência circular é observar a barra de status na parte inferior da planilha do Excel.

Quando há um ou mais problemas na planilha, a barra de status exibe o local de uma referência de célula com problema.

Por exemplo, se houver uma referência circular na célula C1 e não houver outros problemas com a planilha, a barra de status exibirá: “Referências circulares: C1”.

Esta imagem mostra onde ver o visor.

Como encontrar referências circulares ocultas no Excel

As referências circulares ocultas no Excel são referências circulares que não são detectadas imediatamente pelos recursos de erro integrados. Em outras palavras, o Excel não exibe o aviso padrão.

Eles acontecem quando uma célula faz referência a outra célula indiretamente, por meio de uma série de células intermediárias e, eventualmente, refere-se à célula original.

O Excel tem duas ferramentas que ajudam a rastrear relacionamentos e investigar esses problemas:

1. Ferramenta Rastrear Dependentes

2. Ferramenta Rastrear Precedentes

Como usar a ferramenta Trace Dependent

Usuários experientes do Excel aproveitam a ferramenta Trace Dependents para investigar esses problemas e corrigir referências circulares. Ao escolher uma ou mais células e usar esta ferramenta, o Excel mostrará as células dependentes.

A ferramenta está disponível na seção Auditoria de fórmulas na guia Fórmulas.

Para usar a ferramenta, siga estas etapas:

1. Clique na célula ou células para as quais deseja rastrear dependentes.

2. Vá para a guia 'Fórmulas' na faixa de opções do Excel.

3. Clique no botão 'Rastrear dependentes' no grupo 'Auditoria de fórmula'.

4. Revise a seta ou as setas do rastreador que mostram as dependências.

O Excel mostra setas do valor da célula ativa para todas as células que dependem dela. A seta azul na figura abaixo mostra que a célula D6 possui uma dependência da célula B2.

Ferramenta Rastrear Precedentes

Os precedentes são o oposto dos dependentes. A ferramenta Rastrear Precedentes permite que você escolha uma célula com uma fórmula e veja todas as células das quais ela depende, ou seja, os precedentes.

A ferramenta está disponível na seção Auditoria de fórmulas na guia Fórmulas.

Para usar a ferramenta, siga estas etapas:

1. Clique na célula que contém a fórmula que deseja investigar.

2. Vá para a guia Fórmula na faixa de opções do Excel.

3. Clique no botão 'Rastrear precedentes' no grupo 'Auditoria de fórmula'.

4. Revise as setas de rastreamento que o Excel exibe.

As setas azuis na imagem abaixo mostram que a célula D6 depende do intervalo de células A2:B5.

O uso de ambas as ferramentas ajuda a encontrar referências circulares do Excel que não são imediatamente aparentes. O próximo passo é corrigir o problema na célula selecionada. Você pode então continuar a procurar o mesmo problema em outras células.

2 maneiras de remover referências circulares no Excel

Existem dois métodos principais para resolver referências circulares:

• quebrando a referência circular

• usando fórmulas alternativas

Vamos explicar as duas maneiras.

1. Quebrando a Referência Circular

Quebrar a referência circular no Excel é uma forma eficiente de resolver o problema. Isso requer que você identifique as células envolvidas na referência circular e modifique suas fórmulas de acordo.

Para fazer isso, siga as etapas descritas anteriormente para localizar o endereço da célula.

Você deve então examinar a fórmula na barra de fórmulas. Procure uma referência à própria célula.

Em nosso primeiro exemplo de referência circular, a fórmula de adição na célula C1 incluía uma referência à própria célula:

=A1 + B1 + C1

Isso pode ser corrigido removendo a adição final. Uma vez resolvido esse problema, você passa a encontrar outras referências circulares e resolvê-las.

2. Use fórmulas alternativas

Algumas das causas mais comuns de referências circulares são ao somar ou calcular a média de células. O erro é incluir a célula de resultados na fórmula.

Em nosso exemplo anterior, a fórmula usava operadores de adição para calcular a soma de um intervalo de células. O método alternativo é usar a função SOMA do Excel.

Para corrigir o exemplo, substitua a fórmula por:

=SOMA(A1:B1)

Da mesma forma, você pode calcular a média de um intervalo com uma fórmula como =(A1+B1)/2. No entanto, a função MÉDIA tem menos probabilidade de levar a um erro.

=MÉDIA(A1:B1)

5 dicas para evitar referências circulares

Para evitar referências circulares no Microsoft Excel, é essencial ter uma compreensão clara de como suas fórmulas interagem umas com as outras em sua planilha do Excel.

Ao identificar as relações entre as células e estar ciente de como essas conexões funcionam juntas, você pode evitar a criação de fórmulas circulares.

Aqui estão nossas cinco melhores dicas para ajudá-lo a evitar referências circulares.

1. Tenha cuidado ao selecionar intervalos

Na minha experiência, uma das causas mais comuns de referências circulares é quando os usuários selecionam rapidamente intervalos para incluir em suas fórmulas.

Ao deslizar vertical e/ou horizontalmente para agrupar células, é fácil selecionar mais uma célula adjacente ao intervalo correto. Se essa célula também for a célula de resultados, você criou inadvertidamente uma referência circular.

Esse erro geralmente é cometido ao trabalhar com grandes conjuntos de dados, como a execução de testes qui-quadrado. A solução é ter cuidado com sua seleção de intervalo.

2. Planeje suas fórmulas com cuidado

Antes de inserir fórmulas, pense na lógica por trás de seus cálculos e como eles devem fluir pelas células.

Você pode mapear os cálculos com antecedência usando ferramentas de diagramação ou um quadro branco.

Isso pode ajudá-lo a identificar possíveis loops que possam levar a referências circulares.

3. Use fórmulas alternativas

Certas funções, como INDIRECT ou OFFSET , podem criar referências circulares inadvertidamente quando usadas incorretamente.

Considere o uso de funções alternativas que fornecem funcionalidade semelhante sem o risco de referências circulares.

4. Divida fórmulas complexas em partes menores

Em vez de criar uma única fórmula complexa com várias funções aninhadas, considere dividir seus cálculos em partes menores e mais gerenciáveis.

Isso não apenas torna sua pasta de trabalho mais fácil de entender e manter, mas também reduz as chances de introduzir uma referência circular.

5. Corrija os problemas à medida que avança

Ao trabalhar com pastas de trabalho grandes ou complicadas, também é essencial monitorar as referências circulares. Em vez de ignorar os problemas até que um grande conjunto de dados e fórmulas tenha sido reunido, é melhor corrigir cada erro que surgir.

Você pode optar por ignorar uma mensagem circular de aviso de referência por um curto período. No entanto, você deve usar o recurso de verificação de erros para acompanhar as referências circulares existentes em breve.

Isso os torna mais fáceis de corrigir e prevenir seus efeitos em sua pasta de trabalho.

Como ativar o cálculo iterativo e por que se preocupar?

Você geralmente deseja evitar referências circulares em sua planilha. No entanto, existem alguns cenários em que eles são necessários.

Por exemplo, alguns cálculos de engenharia ou científicos precisam usar o resultado de um cálculo anterior. Isso é conhecido como uma solução iterativa.

Cada loop refina os resultados de um cálculo até que um limite ou destino específico seja alcançado.

Nesses casos, é essencial habilitar cálculos iterativos nas configurações do Excel para permitir referências circulares pretendidas. Isso permite que o software repita os cálculos um número especificado de vezes, convergindo para uma solução.

Siga estas etapas para fazer uma referência circular funcionar em seu arquivo do Excel:

1. Clique na guia Arquivo.

2. Selecione as opções.

3. Escolha a categoria Fórmulas.

4. Na seção 'Opções de cálculo', marque a caixa 'Ativar cálculo iterativo'.

5. Altere o número na caixa de iterações máximas ou deixe como padrão 100.

6. Use a caixa de alteração máxima para reduzir as iterações conforme sua necessidade.