Como encontrar referências circulares no Excel de 4 maneiras

Você viu o erro de referência circular em uma planilha do Excel e se perguntou se a planilha está quebrada? Confie em mim! Você não é o primeiro a enfrentar esse erro do Excel. Centenas e milhares de usuários do Excel experimentam erros de referência circular todos os dias. Não se preocupe! Veja como encontrar referências circulares no Excel e resolvê-las.

Sendo um ávido usuário do Excel, você raramente digita os mesmos dados em muitas células. Em vez disso, você importa um conjunto de dados da origem e usa a referência de célula para criar fórmulas. Assim, você cria uma planilha dinâmica.

Quando alguém altera os valores das células das referências de célula, a planilha é atualizada automaticamente. Isso é chamado de automação de análise de dados e os analistas de dados adoram brincar com referências de células.

No entanto, o problema surge quando você digita intencionalmente ou acidentalmente o endereço da célula onde está realizando um cálculo no momento. Ou, quando você está criando muitas fórmulas em toda a planilha, uma célula contendo uma fórmula aparece em outra fórmula indiretamente.

Em tais e em muitas outras situações semelhantes, você obtém o erro de referência circular no Excel. Continue lendo para saber o que é referência circular, seus motivos, por que evitá-la e, o mais importante, como encontrar referências circulares no Excel para se livrar dessas anomalias para sempre.

O Excel encontra um erro de referência circular quando vê que uma ou mais funções da planilha fazem referência ao endereço da célula onde suas funções foram criadas.

Pode parecer confuso para você, então pense em como isso pode ser grave para o aplicativo Excel. Resumindo, você nunca deseja colocar o endereço da célula em uma fórmula onde a está criando. Veja o exemplo a seguir para um esclarecimento visual:

Você está tentando multiplicar 10 da cela B1 e coloque o resultado na célula B1 em si.

Agora, no cenário acima, se você apenas pressionar OK, o Excel fechará o erro e mostrará zero como resultado calculado. Este é apenas um exemplo direto.

Agora, imagine que existem centenas de fórmulas em uma planilha de contabilidade, planilha de planejamento financeiro, folha de pagamento de funcionários, planilha de pagamento de fornecedores e assim por diante.

Você confiará nos dados incorretos calculados pelo Excel devido ao erro de referência circular e isso, por sua vez, afetará sua empresa ou tomada de decisão profissional.

Além disso, a referência circular pode reduzir drasticamente a velocidade dos cálculos no aplicativo Excel. Em casos graves, o próprio erro pode causar desempenho extremamente lento no PC também se não for tratado imediatamente.

No entanto, se você estiver trabalhando em fórmulas avançadas do Excel em que seu modelo de dados exige entradas intermediárias da referência circular para continuar com uma função longa, talvez seja necessário usá-la. Na verdade, o Excel permite que você use esse método de cálculo quando necessário.

Por exemplo, em unidades de produção de energia, como moinhos de vento, você precisa calcular a energia de saída reciclada a partir de uma fração da energia de entrada. Alternativamente, em uma planta química, os engenheiros usam a haste de saída como entrada para a mesma ou outra caldeira ou vaso de processo. Esses cálculos de engenharia de alto nível realmente requerem referências circulares para cálculos iterativos.

Claramente falando, o pop-up de referência circular não é uma mensagem de erro. É uma forma do Excel informar que encontrou uma referência circular em uma ou várias fórmulas e que pode influenciar seu algoritmo de cálculo e desempenho.

Para evitar danos causados ​​por referências circulares em fórmulas do Excel, você deve saber quando elas aparecem e quando não aparecem. Encontre abaixo as instâncias:

• Em qualquer planilha ou pasta de trabalho aberta, você cria uma fórmula e insere a referência circular pela primeira vez.
• Você ou alguém removeu todas as referências circulares do arquivo Excel e o salvou. Agora, novamente você ou outro usuário insere uma referência circular no mesmo arquivo.
• Você fechou todas as pastas de trabalho abertas. Agora, abra uma pasta de trabalho diferente e digite uma fórmula contendo uma referência circular.

• Você acabou de abrir uma pasta de trabalho que continha anteriormente uma ou várias referências circulares. Você verá o aviso.
• Depois que o Excel mostrar a mensagem de aviso de referência circular uma vez e você clicar OK, ele não mostrará mais o mesmo pop-up na mesma sessão, mesmo se você continuar criando fórmulas referenciadas circulares.

Portanto, você deve procurar cuidadosamente pelas instâncias acima para detectar avisos de referência circular e resolvê-los se não estiver procurando por cálculos iterativos.

Se você quer aprender a encontrar referências circulares no Excel, você deve entender seus tipos. Porque localizar e eliminar referências circulares no Excel não é uma tarefa automatizada. Encontre abaixo os tipos comuns deste erro:

• As referências circulares pretendidas ocorrem quando você as usa intencionalmente para fins de cálculo iterativo. Por exemplo, você precisa inserir um carimbo de data/hora em outra célula quando alguém insere um valor em uma célula referenciada usando funções voláteis e lógicas como IF, OR, SUMIF, etc.

• Referência circular não intencional quando você tenta trabalhar em uma fórmula do Excel mais rapidamente e se refere à célula que contém a fórmula por engano. Por exemplo, você precisa aplicar o SOMA função da célula A1 para A9 e buscar o resultado na célula A10. Mas por engano, você digita a fórmula assim na célula A10:

=SOMA(A2:A10)

• As referências circulares ocultas são aquelas que não mostram nenhuma mensagem de aviso quando você cria uma fórmula ou abre a planilha pela primeira vez. Isso acontece porque alguém permitiu o cálculo iterativo no arquivo do Excel.

• Referências circulares indiretas também são populares e ocorrem quando você cria uma nova fórmula em qualquer célula, por exemplo, célula A1, e essa nova fórmula usaria outra função em outra célula, como célula B1, que já está usando o celular A1 como referência.

Agora que você entendeu o básico sobre referências circulares no Excel, vamos descobrir como encontrar referências circulares no Excel abaixo:

Desde que o recurso de cálculo iterativo esteja desativado, você pode ver as seguintes notificações sobre referências circulares na barra de status do Excel:

• Referências circulares: C3 (ou qualquer outra referência de célula) quando você estiver em uma planilha contendo uma fórmula circular na pasta de trabalho.

• Você só verá Referências circulares se você estiver em uma planilha que não contém nenhuma, mas a pasta de trabalho tiver algumas dessas fórmulas circulares.

É a melhor maneira de verificar referências circulares em uma pasta de trabalho ou planilha. Aqui estão os passos que você deve saber:

• Abra uma planilha que você suspeita conter fórmulas circulares.
• Clique no Fórmulas menu na faixa de opções do Excel.

• No Auditoria de Fórmula grupo de comandos, clique no Verificação de erroslista suspensa.
• Passe o cursor sobre o Referências circulares opção para ver todas as instâncias disponíveis em todas as planilhas.
• Selecione cada instância e o Excel identificará essas células automaticamente.

Se você não achar o método de verificação de erros eficiente o suficiente para detectar um grande número de fórmulas com referências circulares, tente estas etapas para usar o método de rastreamento de fórmula:

• Selecione qualquer célula referenciada ou a célula que contém a fórmula.
• Vou ao Fórmulas guia no menu da faixa de opções do Excel e, em seguida, clique em Rastrear precedentes.
• O Excel destacará as células que afetam o resultado da fórmula na célula atual com setas.

• Você também pode clicar Rastrear Dependentes para descobrir células afetadas pelo valor da referência de célula selecionada.
• Se o Excel não mostrar nenhum erro depois de clicar nos comandos acima, não há referências circulares em sua planilha.
• Repita as etapas em todas as planilhas para verificar toda a pasta de trabalho.

Esse método é particularmente importante quando os recursos de cálculo iterativo estão ativos na planilha do Excel.

• Clique no Arquivo guia na faixa de opções do Excel e, em seguida, abra Opções do Excel na barra lateral que aparece.
• No Opções do Excel caixa de diálogo, selecione o Fórmulas menu na barra lateral e, em seguida, desmarque a caixa de seleção para Ativar cálculo iterativo.

• Clique OK para salvar as alterações.
• Agora, a pasta de trabalho ou planilha mostrará automaticamente o aviso de referência circular.
• Se não aparecer instantaneamente, salve a pasta de trabalho, feche-a e reabra-a para ver o aviso.
• Depois de ver o aviso, verifique o Barra de status do Excel para saber qual célula está na referência circular.

A maneira certa de corrigir um ou muitos erros de referência circular no Excel é editando manualmente cada fórmula que o contém. Dessa forma, você pode resolver referências circulares acidentais e indiretas no Excel.

Outra maneira fácil é remover a fórmula da célula atual e colocá-la em outro lugar dentro da mesma planilha.

Como o Excel, o Planilhas Google não permite referências circulares em uma ou mais fórmulas se você não ativou o recurso de cálculo iterativo. Portanto, o Planilhas Google mostrará imediatamente o #REF! erro na célula que contém uma fórmula circular.

Portanto, você pode simplesmente selecionar todas as células que contêm o #REF! aviso para localizar e resolver referências circulares no Planilhas Google.

Então, agora você conhece todos os segredos de como encontrar referências circulares no Excel. Use o método que você gosta e abandone todas as referências circulares para garantir que você não fique confuso com dados não confiáveis ​​de um relatório do Excel ou painel matemático.

A seguir, o melhores modelos de calendário do Excel para qualquer ano e a melhores modelos de orçamento do Excel.