Cómo encontrar referencias circulares en Excel

Al trabajar con Microsoft Excel , es posible que ocasionalmente encuentre lo que se conoce como una referencia circular. Esto es cuando una fórmula en una celda hace referencia a sí misma, lo que provoca un ciclo interminable en el proceso de cálculo.

Para encontrar referencias circulares en Excel, use la herramienta "Comprobación de errores" en la pestaña "Fórmulas" para identificar las celdas con una dependencia circular.

Este artículo le muestra cómo encontrar referencias circulares en Excel y corregirlas. También aprenderá a prevenirlos cuando trabaje con hojas de trabajo grandes o complejas.

Tabla de contenido

• ¿Qué son las referencias circulares?
• La ventana de mensaje de error de referencia circular
• Cómo encontrar referencias circulares en Excel
• Cómo encontrar referencias circulares con la barra de estado de Excel
• Cómo encontrar referencias circulares ocultas en Excel
  • Cómo usar la herramienta dependiente de seguimiento
  • Herramienta de rastreo de precedentes
• 2 formas de eliminar referencias circulares en Excel
  • 2. Usa fórmulas alternativas
• 5 consejos para evitar las referencias circulares
  • 1. Tenga cuidado al seleccionar rangos
  • 2. Planifica tus fórmulas cuidadosamente
  • 3. Usa fórmulas alternativas
  • 4. Divide fórmulas complejas en partes más pequeñas
  • 5. Solucione los problemas sobre la marcha
• ¿Cómo habilitar el cálculo iterativo y por qué molestarse?

¿Qué son las referencias circulares?

Las referencias circulares ocurren en Excel cuando el cálculo de una fórmula en una celda depende del valor propio de la celda. Esto crea un bucle y conduce a resultados incorrectos o impredecibles.

Esto es más fácil de entender con un ejemplo simple.

Suponga que tiene dos números en las celdas A1 y B1. Desea usar una fórmula en la celda A3 que suma los dos números. La fórmula en la celda A3 debería verse así:

=A1 + B1

Sin embargo, si comete un error e incluye C1 en los cálculos, ahora tiene una referencia circular:

=A1 + B1 + C1

Este puede ser un problema obvio a evitar cuando se trata de dos celdas. Sin embargo, cuando trabaja con grandes rangos de celdas, es más probable que se produzcan errores.

Afortunadamente, Excel está diseñado para manejar tales situaciones al mostrar un mensaje de advertencia cuando se detecta una referencia circular.

Sin embargo, en algunos casos, puede encontrar una referencia circular sin recibir ninguna alerta. Por lo tanto, es importante saber cómo encontrar y resolver estos problemas manualmente.

La ventana de mensaje de error de referencia circular

De forma predeterminada, Excel supervisa las referencias circulares a medida que ingresa fórmulas en las celdas. Cuando presiona Intro en una celda con un problema, una ventana emergente mostrará el mensaje de error de referencia circular.

Este es el texto explicativo:

Hay una o más referencias circulares donde una fórmula se refiere a su propia celda, ya sea directa o indirectamente. Esto podría hacer que calculen incorrectamente. Intente eliminar o cambiar estas referencias o mover las fórmulas a celdas diferentes.

Cuando vea este cuadro de diálogo, debe hacer clic en "Aceptar" y utilizar los métodos de este artículo para solucionar el problema.

Sin embargo, no es necesario corregir el error de inmediato. Puede continuar trabajando con la hoja de cálculo con la intención de resolver el problema más adelante.

En ese caso, debe saber cómo encontrar referencias circulares en Excel después de haber descartado el cuadro de advertencia.

Cómo encontrar referencias circulares en Excel

Excel tiene una herramienta de verificación de errores incorporada que encuentra varios tipos de errores, incluidas las referencias circulares. Se accede a la herramienta en la pestaña Fórmulas.

Esta imagen muestra la ubicación dentro del grupo Auditoría de fórmulas.

Ahora que sabe cómo encontrar la herramienta, aquí están los pasos detallados para usarla:

1. Vaya a la pestaña Fórmulas.

1. Haga clic en el menú desplegable Comprobación de errores en el grupo Auditoría de fórmulas.

2. Seleccione "Referencias circulares" de la lista desplegable.

3. Haga clic en el enlace para mover el cursor a la primera celda con un problema.

La siguiente imagen muestra que la herramienta ha encontrado un error circular en la celda G1. La celda mostrada es un hipervínculo.

Al hacer clic en el enlace, el cursor se mueve a la celda. Esto es muy útil con un gran volumen de datos.

Una vez que haya resuelto la primera referencia circular, puede volver a la herramienta Comprobación de errores para encontrar la siguiente en la hoja.

Siga los pasos de las secciones posteriores de este artículo para solucionar el problema y use la herramienta para pasar al siguiente problema.

Si no hay más problemas circulares, el elemento de menú "Referencias circulares" en el menú desplegable aparecerá atenuado. No tienes más trabajo que hacer.

Cómo encontrar referencias circulares con la barra de estado de Excel

Otra forma de detectar una referencia circular es mirar la barra de estado en la parte inferior de la hoja de cálculo de Excel.

Cuando hay uno o más problemas en la hoja de trabajo, la barra de estado muestra la ubicación de una referencia de celda de problema.

Por ejemplo, si hay una referencia circular en la celda C1 y no hay otros problemas con la hoja de cálculo, la barra de estado mostrará: "Referencias circulares: C1".

Esta imagen le muestra dónde ver la pantalla.

Cómo encontrar referencias circulares ocultas en Excel

Las referencias circulares ocultas en Excel son referencias circulares que las funciones de error integradas no detectan inmediatamente. En otras palabras, Excel no muestra la advertencia estándar.

Ocurren cuando una celda hace referencia a otra celda indirectamente, a través de una serie de celdas intermedias, y finalmente se refiere de nuevo a la celda original.

Excel tiene dos herramientas que lo ayudan a rastrear relaciones e investigar estos problemas:

1. Herramienta Rastrear dependientes

2. Herramienta de rastreo de precedentes

Cómo usar la herramienta dependiente de seguimiento

Los usuarios experimentados de Excel aprovechan la herramienta Rastrear dependientes para investigar estos problemas y corregir referencias circulares. Al elegir una o más celdas y usar esta herramienta, Excel le mostrará las celdas dependientes.

La herramienta está disponible en la sección Auditoría de fórmulas en la pestaña Fórmulas.

Para utilizar la herramienta, siga estos pasos:

1. Haga clic en la celda o celdas para las que desea rastrear a los dependientes.

2. Vaya a la pestaña 'Fórmulas' en la cinta de opciones de Excel.

3. Haga clic en el botón 'Rastrear dependientes' en el grupo 'Auditoría de fórmulas'.

4. Revise la flecha o flechas de seguimiento que muestran las dependencias.

Excel muestra flechas desde el valor de la celda activa hasta cualquier celda que dependa de él. La flecha azul en la imagen a continuación muestra que la celda D6 depende de la celda B2.

Herramienta de rastreo de precedentes

Los precedentes son lo opuesto a los dependientes. La herramienta Rastrear precedentes le permite elegir una celda con una fórmula y ver todas las celdas de las que depende, es decir, los precedentes.

La herramienta está disponible en la sección Auditoría de fórmulas en la pestaña Fórmulas.

Para utilizar la herramienta, siga estos pasos:

1. Haga clic en la celda que contiene la fórmula que desea investigar.

2. Vaya a la pestaña Fórmula en la cinta de opciones de Excel.

3. Haga clic en el botón 'Rastrear precedentes' en el grupo 'Auditoría de fórmulas'.

4. Revise las flechas de seguimiento que muestra Excel.

Las flechas azules en la imagen a continuación le muestran que la celda D6 depende del rango de celdas A2: B5.

El uso de ambas herramientas lo ayuda a encontrar referencias circulares de Excel que no son evidentes de inmediato. El siguiente paso es solucionar el problema en la celda seleccionada. A continuación, puede continuar buscando el mismo problema en otras celdas.

2 formas de eliminar referencias circulares en Excel

Hay dos métodos principales para resolver las referencias circulares:

• rompiendo la referencia circular

• utilizando fórmulas alternativas

Expliquemos ambas formas.

1. Romper la referencia circular

Romper la referencia circular en Excel es una forma eficiente de resolver el problema. Esto requiere que identifique las celdas involucradas en la referencia circular y modifique sus fórmulas en consecuencia.

Para hacer esto, siga los pasos descritos anteriormente para encontrar la dirección del celular.

A continuación, debe examinar la fórmula en la barra de fórmulas. Busque una referencia a la propia celda.

En nuestro primer ejemplo de una referencia circular, la fórmula de suma en la celda C1 incluía una referencia a la celda misma:

=A1 + B1 + C1

Esto se puede solucionar eliminando la adición final. Una vez que se resuelve este problema, pasa a buscar otras referencias circulares y abordarlas.

2. Usa fórmulas alternativas

Algunas de las causas más comunes de referencias circulares son al sumar o promediar celdas. El error es incluir la celda de resultados en la fórmula.

En nuestro ejemplo anterior, la fórmula usaba operadores de suma para calcular la suma de un rango de celdas. El método alternativo es utilizar la función SUM Excel.

Para corregir el ejemplo, reemplaza la fórmula con:

=SUMA(A1:B1)

De manera similar, podría promediar un rango con una fórmula como =(A1+B1)/2. Sin embargo, es menos probable que la función PROMEDIO dé lugar a un error.

=PROMEDIO(A1:B1)

5 consejos para evitar las referencias circulares

Para evitar referencias circulares en Microsoft Excel, es esencial comprender claramente cómo interactúan las fórmulas entre sí en la hoja de Excel.

Al identificar las relaciones entre las celdas y ser consciente de cómo funcionan juntas estas conexiones, puede evitar la creación de fórmulas circulares.

Estos son nuestros cinco mejores consejos para ayudarlo a evitar las referencias circulares.

1. Tenga cuidado al seleccionar rangos

En mi experiencia, una de las causas más comunes de referencias circulares es cuando los usuarios seleccionan rangos rápidamente para incluirlos en sus fórmulas.

Al deslizar el dedo vertical y/u horizontalmente para agrupar celdas, es fácil seleccionar una celda más que esté adyacente al rango correcto. Si esa celda es también la celda de resultados, sin darse cuenta ha creado una referencia circular.

Este error se suele cometer cuando se trabaja con grandes conjuntos de datos, como al ejecutar pruebas de chi-cuadrado. La solución es tener cuidado con la selección de rango.

2. Planifica tus fórmulas cuidadosamente

Antes de ingresar fórmulas, piense en la lógica detrás de sus cálculos y cómo deben fluir a través de las celdas.

Puede trazar los cálculos por adelantado utilizando herramientas de diagramación o una pizarra.

Esto puede ayudarlo a identificar posibles bucles que podrían conducir a referencias circulares.

3. Usa fórmulas alternativas

Ciertas funciones, como INDIRECT o OFFSET , pueden crear referencias circulares sin darse cuenta cuando se usan incorrectamente.

Considere el uso de funciones alternativas que proporcionen una funcionalidad similar sin el riesgo de referencias circulares.

4. Divide fórmulas complejas en partes más pequeñas

En lugar de crear una sola fórmula compleja con múltiples funciones anidadas, considere dividir sus cálculos en partes más pequeñas y manejables.

Esto no solo hace que su libro de trabajo sea más fácil de entender y mantener, sino que también reduce las posibilidades de introducir una referencia circular.

5. Solucione los problemas sobre la marcha

Cuando se trabaja con libros de trabajo grandes o complicados, también es esencial controlar las referencias circulares. En lugar de ignorar los problemas hasta que se haya ensamblado un gran conjunto de datos y fórmulas, es mejor corregir cada error a medida que surja.

Puede optar por ignorar un mensaje de advertencia de referencia circular durante un breve período de tiempo. Sin embargo, debe usar la función de verificación de errores para ponerse al día con las referencias circulares existentes en poco tiempo.

Esto los hace más fáciles de arreglar y prevenir sus efectos en su libro de trabajo.

¿Cómo habilitar el cálculo iterativo y por qué molestarse?

Por lo general, desea evitar las referencias circulares en su hoja de trabajo. Sin embargo, hay algunos escenarios en los que son necesarios.

Por ejemplo, algunos cálculos científicos o de ingeniería necesitan utilizar el resultado de un cálculo anterior. Esto se conoce como una solución iterativa.

Cada ciclo refina los resultados de un cálculo hasta que se alcanza un umbral u objetivo específico.

En estos casos, es esencial habilitar los cálculos iterativos en la configuración de Excel para permitir las referencias circulares previstas. Esto permite que el software repita los cálculos un número específico de veces, convergiendo en una solución.

Siga estos pasos para hacer que una referencia circular funcione en su archivo de Excel:

1. Haga clic en la pestaña Archivo.

2. Seleccione opciones.

3. Elija la categoría Fórmulas.

4. En la sección "Opciones de cálculo", marque la casilla "Habilitar cálculo iterativo".

5. Cambie el número en el cuadro de iteraciones máximas o déjelo como predeterminado de 100.

6. Utilice el cuadro de cambio máximo para reducir las iteraciones según sus requisitos.