Como atopar referencias circulares en Excel para evitar datos defectuosos

Viches o erro de referencia circular nunha folla de Excel e te preguntas se a folla de traballo está rota? Confía en min! Non es o primeiro en enfrontarse a este erro de Excel. Centos e miles de usuarios de Excel experimentan erros de referencia circular todos os días. Non te preocupes! Aquí tes como atopar referencias circulares en Excel e resolvelas.

Sendo un usuario ávido de Excel, raramente escribe os mesmos datos en moitas celas. No seu lugar, importa un conxunto de datos da fonte e utiliza a referencia de celas para crear fórmulas. Así, crea unha folla de cálculo dinámica.

Cando alguén cambia os valores das celas das referencias das celas, a folla de traballo actualízase automaticamente. Isto chámase automatización da análise de datos e aos analistas de datos encántalles xogar coas referencias de células.

Non obstante, o problema chega cando tecleas de forma intencionada ou accidental o enderezo da cela onde estás a realizar un cálculo. Ou, cando está a crear moitas fórmulas ao longo da folla de traballo, unha cela que contén unha fórmula aparece noutra fórmula indirectamente.

En tal e moitas outras situacións similares, obtén o erro de referencia circular en Excel. Continúa lendo para saber que é a referencia circular, os seus motivos, por que evitala e, o máis importante, como atopar referencias circulares en Excel para desfacerse definitivamente destas anomalías.

Que é unha referencia circular en Excel?

Excel atopa un erro de referencia circular cando ve que unha ou moitas funcións da folla de traballo fan referencia ao enderezo da cela onde se crearon as súas funcións.

Pode parecer confuso para ti, así que pensa no grave que pode ser para a aplicación Excel. En resumo, nunca quere poñer o enderezo da cela nunha fórmula onde o está a crear. Observa o seguinte exemplo para unha aclaración visual:

Estás tentando multiplicar 10 da cela B1 e poñer o resultado na propia cela B1 .

Agora, no escenario anterior, se só preme Aceptar , Excel pechará o erro e mostrará cero como resultado calculado. Este é só un exemplo sinxelo.

Que é a referencia circular en Excel

Agora, imaxina que hai centos de fórmulas nunha folla de cálculo de contabilidade, folla de traballo de planificación financeira, nómina de empregados, folla de cálculo de pago de provedores, etc.

Confiarás nos datos defectuosos calculados por Excel debido ao erro de referencia circular e iso, á súa vez, afectará a túa empresa ou a toma de decisións profesionais.

Ademais, a referencia circular pode reducir gravemente a velocidade dos cálculos dentro da aplicación Excel. En casos graves, o propio erro tamén pode causar un rendemento extremadamente lento no PC se non se trata inmediatamente.

Non obstante, se estás a traballar en fórmulas de Excel avanzadas nas que o teu modelo de datos require entradas intermedias da referencia circular para continuar cunha función longa, é posible que teñas que usalo. De feito, Excel permíteche utilizar este método de cálculo cando sexa necesario.

Por exemplo, nas unidades de produción de enerxía, como os muíños de vento, cómpre calcular a enerxía de saída reciclada a partir dunha fracción da enerxía de entrada. Alternativamente, nunha planta química, os enxeñeiros usan o vástago de saída como entrada para a mesma ou outra caldeira ou recipiente de proceso. Tales cálculos de enxeñería de alto nivel precisan de feito referencias circulares para cálculos iterativos.

Cando ve o erro de referencia circular en Excel?

Claramente, a ventá emerxente de referencia circular non é unha mensaxe de erro. É unha forma de que Excel te informe de que atopou referencias circulares nunha ou varias fórmulas e que poden influír no seu algoritmo de cálculo e no seu rendemento.

Para evitar os danos causados ​​polas referencias circulares nas fórmulas de Excel, debes saber cando aparecen e cando non. Busca a continuación as instancias:

• En calquera folla de traballo ou libro aberto, crea unha fórmula e introduce a referencia circular por primeira vez.
• Ti ou alguén quitou todas as referencias circulares do ficheiro Excel e gardouno. Agora, de novo, vostede ou outro usuario introducen unha referencia circular no mesmo ficheiro.
• Pechaches todos os libros de traballo abertos. Agora, abriu un libro de traballo diferente e, a continuación, escriba unha fórmula que conteña unha referencia circular.

Cando ve o erro de referencia circular en Excel

• Acabas de abrir un libro de traballo que anteriormente contiña unha ou moitas referencias circulares. Verás o aviso.
• Unha vez que Excel mostrouche a mensaxe de aviso de referencia circular unha vez e premeches Aceptar , xa non mostrará a mesma ventá emerxente na mesma sesión aínda que sigas creando máis fórmulas de referencia circular.

Polo tanto, debe buscar coidadosamente as instancias anteriores para detectar avisos de referencia circular e resolvelos se non está a buscar cálculos iterativos.

Tipos de referencias circulares en Excel

Se queres aprender a atopar referencias circulares en Excel, debes comprender os seus tipos. Porque atopar e eliminar referencias circulares en Excel non é unha tarefa automatizada. Busca a continuación os tipos comúns deste erro:

Un exemplo de uso de referencia circular

• As referencias circulares previstas prodúcense cando as usas de forma intencionada con fins de cálculo iterativo. Por exemplo, cómpre introducir un selo de tempo noutra cela cando alguén introduce un valor nunha cela referenciada usando funcións volátiles e lóxicas como SE, OU, SUMIF, etc.

Referencia circular non desexada en Excel

• Referencias circulares non desexadas cando intentas traballar nunha fórmula de Excel máis rápido e fai referencia por erro á cela que contén a fórmula. Por exemplo, cómpre aplicar a función SUMA da cela A1 a A9 e obter o resultado na cela A10 . Pero por erro, escribe a fórmula como esta na cela A10:

=SUMA(A2:A10)

• As referencias circulares ocultas son aquelas que non mostran ningunha mensaxe de aviso cando crea unha fórmula ou abre a folla de traballo por primeira vez. Ocorre porque alguén permitiu o cálculo iterativo no ficheiro Excel.

Un exemplo de referencia circular indirecta

• As referencias circulares indirectas tamén son populares e ocorren cando crea unha fórmula nova en calquera cela, por exemplo, a cela A1 , e esta nova fórmula usaría outra función noutra cela, como a cela B1 , que xa está usando a cela A1 como referencia. .

Agora que entendeu os conceptos básicos das referencias circulares en Excel, descubramos como atopar referencias circulares en Excel a continuación:

Como atopar referencias circulares en Excel: desde a barra de estado

Sempre que a función de cálculo iterativo estea desactivada, podes ver as seguintes notificacións sobre referencias circulares na barra de estado de Excel:

• Referencias circulares: C3  (ou calquera outra referencia de cela) cando estás nunha folla de traballo que contén fórmulas circulares no libro de traballo.

Mostrando referencias circulares na barra de estado de Excel

• Só verás referencias circulares se estás nunha folla de traballo que non contén ningunha, pero o libro de traballo ten algunhas destas fórmulas circulares.

Como atopar referencias circulares en Excel: usando a comprobación de erros

É a mellor forma de comprobar as referencias circulares nun libro ou folla de traballo. Aquí tes os pasos que debes saber:

• Abra unha folla de traballo que sospeita que contén fórmulas circulares.
• Fai clic no menú Fórmulas na cinta de Excel.

Use a comprobación de erros para Como atopar referencias circulares en Excel

• No grupo de comandos de Auditoría de fórmulas , faga clic na lista despregable Comprobación de erros  .
• Pase o cursor sobre a opción Referencias circulares para ver todas as instancias dispoñibles en todas as follas de traballo.
• Seleccione cada instancia e Excel identificará esas celas automaticamente.

Como atopar referencias circulares en Excel: desde Formula Tracing

Se non consideras que o método de comprobación de erros é o suficientemente eficiente como para detectar un gran número de fórmulas con referencias circulares, podes probar estes pasos para usar o método de rastrexo de fórmulas:

• Seleccione calquera cela referenciada ou a cela que conteña fórmulas.
• Vaia á pestana Fórmulas no menú da cinta de Excel e, a continuación, faga clic en Rastrexar precedentes .
• Excel resaltará con frechas as celas que afectan ao resultado da fórmula na cela actual.

Use o rastrexo de fórmulas para como atopar referencias circulares en Excel

• Tamén pode facer clic en Trazar dependentes para descubrir celas afectadas polo valor da referencia de cela seleccionada.
• Se Excel non mostra ningún erro despois de facer clic nos comandos anteriores, non hai referencias circulares na túa folla de traballo.
• Repita os pasos en todas as follas de traballo para comprobar o libro completo.

Este método é especialmente importante cando as funcións de cálculo iterativo están activas na folla de cálculo de Excel.

Como atopar referencias circulares en Excel: Usando as opcións de Excel

• Fai clic na pestana Ficheiro na fita de Excel e despois abre Opcións de Excel na barra lateral que aparece.
• No cadro de diálogo Opcións de Excel , seleccione o menú Fórmulas na barra lateral e, a continuación, desmarque a caixa de verificación Activar cálculo iterativo .

Como desactivar o cálculo iterativo en Excel

• Fai clic en Aceptar para gardar os cambios.
• Agora, o libro de traballo ou a folla de traballo amosarache automaticamente o aviso de referencia circular.
• Se non se mostra ao instante, garda o libro de traballo, péchao e ábreo de novo para ver o aviso.
• Unha vez que vexa o aviso, verifique a barra de estado de Excel para saber que cela está na referencia circular.

Como resolver referencias circulares en Excel

A forma segura de corrixir un ou moitos erros de referencia circular en Excel é editando manualmente cada fórmula que o contén. Deste xeito, pode resolver tanto as referencias circulares accidentais como indirectas en Excel.

Como resolver referencias circulares en Excel

Outra forma sinxela é eliminar a fórmula da cela actual e colocala noutro lugar dentro da mesma folla de traballo.

Como atopar referencias circulares en Google Sheets (método de bonificación)

Do mesmo xeito que Excel, Follas de cálculo de Google non permite referencias circulares nunha ou máis fórmulas se non activaches a función de cálculo iterativo. Polo tanto, Google Sheets mostrará inmediatamente o #REF! erro na cela que contén unha fórmula circular.

Follas de cálculo de Google Como atopar referencias circulares

Polo tanto, pode simplemente seleccionar todas as celas que conteñan o #REF! aviso para buscar e resolver referencias circulares en Follas de cálculo de Google.

Referencias circulares de Excel: palabras finais

Entón, agora coñeces todos os segredos de como atopar referencias circulares en Excel. Usa o método que che guste e elimina todas as referencias circulares para asegurarte de que non te confundas con datos pouco fiables dun informe de Excel ou dun panel matemático.

A continuación, os  mellores modelos de calendario de Excel para calquera ano  e os  mellores modelos de orzamento de Excel .