Una de las advertencias de error más comunes que encuentran los usuarios en Excel es la "Referencia circular". Miles de usuarios tienen el mismo problema, y ocurre cuando una fórmula hace referencia a su propia celda directa o indirectamente, provocando un bucle sin fin de cálculos.
Por ejemplo, tiene dos valores en las celdas B1 y B2. Cuando se ingresa la fórmula = B1 + B2 en B2, se crea una referencia circular; la fórmula en B2 se recalcula repetidamente porque cada vez que calcula, el valor de B2 ha cambiado.
La mayoría de las referencias circulares son errores involuntarios; Excel le advertirá sobre estos. Sin embargo, también existen referencias circulares previstas, que se utilizan para realizar cálculos iterativos. Las referencias circulares no deseadas en su hoja de trabajo pueden hacer que su fórmula se calcule incorrectamente.
Por lo tanto, en este artículo, explicaremos todo lo que necesita saber sobre las referencias circulares y también sobre cómo buscar, corregir, eliminar y usar referencias circulares en Excel.
Cómo buscar y manejar referencias circulares en Excel
Cuando trabajamos con Excel, a veces nos encontramos con errores de referencia circular que ocurren cuando ingresa una fórmula que incluye la celda donde reside su fórmula. Básicamente, sucede cuando su fórmula intenta calcularse a sí misma.
Por ejemplo, tiene una columna de números en la celda A1: A4 y está utilizando la función SUMA (= SUMA (A1: A5)) en la celda A5. La celda A5 se refiere directamente a su propia celda, lo cual es incorrecto. Por lo tanto, obtendrá la siguiente advertencia de referencia circular:
Una vez que reciba el mensaje de advertencia anterior, puede hacer clic en el botón "Ayuda" para saber más sobre el error, o cerrar la ventana del mensaje de error haciendo clic en el botón "Aceptar" o "X" y obtener "0" como resultado.
A veces, los bucles de referencia circulares pueden hacer que su cálculo se bloquee o ralentice el rendimiento de la hoja de trabajo. La referencia circular también puede dar lugar a una serie de otros problemas, que no serán evidentes de inmediato. Por lo tanto, es mejor evitarlos.
Referencias circulares directas e indirectas
Las referencias circulares se pueden clasificar en dos tipos: referencias circulares directas y referencias circulares indirectas.
Referencia directa
Una referencia circular directa es bastante simple. El mensaje de advertencia de referencia circular directa aparece cuando la fórmula se refiere directamente a su propia celda.
En el siguiente ejemplo, la fórmula en la celda A2 se refiere directamente a su propia celda (A2).
Una vez que aparece el mensaje de advertencia, puede hacer clic en "Aceptar", pero solo dará como resultado "0".
Referencia circular indirecta
Una referencia circular indirecta en Excel ocurre cuando un valor en una fórmula hace referencia a su propia celda, pero no directamente. En otras palabras, la referencia circular puede estar formada por dos celdas que se referencian entre sí.
Expliquemos con este sencillo ejemplo.
Ahora el valor comienza en A1, que tiene el valor 20.
A continuación, la celda C3 se refiere a la celda A1.
Entonces, la celda A5 se refiere a la celda C3.
Ahora reemplace el valor 20 en la celda A1 con la fórmula que se muestra a continuación. Todas las demás celdas dependen de la celda A1. Cuando usa una referencia de cualquier otra celda de fórmula anterior en A1, generará una advertencia de referencia circular. Porque, la fórmula en A1 se refiere a la celda A5, que se refiere a C3, y la celda C3 se refiere a A1, de ahí la referencia circular.
Cuando hace clic en "Aceptar", el resultado es un valor de 0 en la celda A1 y Excel crea una línea vinculada que muestra los antecedentes de seguimiento y los dependientes de seguimiento como se muestra a continuación. Podemos utilizar esta función para encontrar y corregir / eliminar fácilmente referencias circulares.
Cómo habilitar / deshabilitar referencias circulares en Excel
De forma predeterminada, los cálculos iterativos están desactivados (deshabilitados) en Excel. Los cálculos iterativos son cálculos repetitivos hasta que cumplen una condición específica. Cuando está deshabilitado, Excel muestra un mensaje de Referencia circular y devuelve un 0 como resultado.
Sin embargo, a veces se necesitan referencias circulares para calcular un bucle. Para usar la referencia circular, debe habilitar los cálculos iterativos en su Excel y le permitirá realizar sus cálculos. Ahora, permítanos mostrarle cómo puede habilitar o deshabilitar cálculos iterativos.
En Excel 2010, Excel 2013, Excel 2016, Excel 2019 y Microsoft 365, vaya a la pestaña "Archivo" en la esquina superior izquierda de Excel, luego haga clic en "Opciones" en el panel izquierdo.
En la ventana Opciones de Excel, vaya a la pestaña "Fórmula" y marque la casilla de verificación "Habilitar cálculo iterativo" en la sección "Opciones de cálculo". Luego haga clic en "Aceptar" para guardar los cambios.
Esto permitirá el cálculo iterativo y, por tanto, la referencia circular.
Para lograr esto en versiones anteriores de Excel, siga estos pasos:
- En Excel 2007, haga clic en el botón de Office> Opciones de Excel> Fórmulas> Área de iteración.
- En Excel 2003 y versiones anteriores, debe ir a Menú> Herramientas> Opciones> pestaña Cálculo.
Iteraciones máximas y parámetros de cambio máximo
Una vez que habilita los cálculos iterativos, puede controlar los cálculos iterativos, especificando dos opciones disponibles en la sección Habilitar cálculo iterativo como se muestra en la captura de pantalla a continuación.
- Iteraciones máximas - Este número especifica cuántas veces se debe recalcular la fórmula antes de darle el resultado final. El valor predeterminado es 100. Si lo cambia a '50', Excel repetirá los cálculos 50 veces antes de darle el resultado final. Recuerde que cuanto mayor sea el número de iteraciones, más recursos y tiempo se necesitará para calcular.
- Cambio máximo - Determina el cambio máximo entre los resultados del cálculo. Este valor determina la precisión del resultado. Cuanto menor sea el número, más preciso será el resultado y más tiempo llevará calcular la hoja de trabajo.
Si la opción de cálculos iterativos está habilitada, no recibirá ninguna advertencia cada vez que haya una referencia circular en su hoja de trabajo. Solo habilite el cálculo interactivo cuando sea absolutamente necesario.
Encuentre una referencia circular en Excel
Suponga que tiene un conjunto de datos grande y recibió la advertencia de referencia circular, aún tendrá que averiguar dónde (en qué celda) se ha producido el error para corregirlo. Para encontrar referencias circulares en Excel, siga estos pasos:
Uso de la herramienta de verificación de errores
Primero, abra la hoja de trabajo donde ocurrió la referencia circular. Vaya a la pestaña "Fórmula", haga clic en la flecha junto a la herramienta "Comprobación de errores". Luego, simplemente coloque el cursor sobre la opción "Referencias circulares", Excel le mostrará la lista de todas las celdas que están involucradas en la referencia circular como se muestra a continuación.
Haga clic en la dirección de celda que desee en la lista y lo llevará a esa dirección de celda para resolver el problema.
Usando la barra de estado
También puede encontrar la referencia circular en la barra de estado. En la barra de estado de Excel, le mostrará la última dirección de celda con una referencia circular, como "Referencias circulares: B6" (vea la captura de pantalla a continuación).
Hay ciertas cosas que debe saber al manejar la referencia circular:
- La barra de estado no mostrará la dirección de la celda de referencia circular cuando la opción Cálculo iterativo esté habilitada, por lo que debe deshabilitarla antes de comenzar a buscar referencias circulares en el libro de trabajo.
- En caso de que no se encuentre una referencia circular en la hoja activa, la barra de estado solo muestra "Referencias circulares" sin dirección de celda.
- Solo recibirá un mensaje de referencia circular una vez y, después de hacer clic en "Aceptar", no volverá a mostrar el mensaje la próxima vez.
- Si su libro de trabajo tiene referencias circulares, le mostrará el mensaje cada vez que lo abra hasta que resuelva la referencia circular o hasta que active el cálculo iterativo.
Eliminar una referencia circular en Excel
Encontrar referencias circulares es fácil, pero arreglarlo no es tan simple. Desafortunadamente, no hay ninguna opción en Excel que le permita eliminar todas las referencias circulares a la vez.
Para corregir referencias circulares, debe buscar cada referencia circular individualmente e intentar modificarla, eliminar la fórmula circular por completo o reemplazarla por otra.
A veces, en fórmulas simples, todo lo que necesita hacer es reajustar los parámetros de la fórmula para que no se refiera a sí misma. Por ejemplo, cambie la fórmula en B6 a = SUM (B1: B5) * A5 (cambiando B6 a B5).
Devolverá el resultado del cálculo como "756".
En los casos en los que una referencia circular de Excel sea difícil de encontrar, puede usar las funciones Trazar precedentes y Trazar dependientes para rastrearla hasta la fuente y resolverla una por una. La flecha muestra qué células se ven afectadas por la célula activa.
Hay dos métodos de rastreo que pueden ayudarlo a eliminar referencias circulares mostrando las relaciones entre fórmulas y celdas.
Para acceder a los métodos de seguimiento, vaya a la pestaña "Fórmulas" y, a continuación, haga clic en "Seguimiento de precedentes" o "Seguimiento de dependientes" en el grupo Auditoría de fórmulas.
Trazar precedentes
Cuando selecciona esta opción, rastrea las celdas que afectan el valor de la celda activa. Dibuja una línea azul que indica qué celdas afectan a la celda actual. La tecla de método abreviado para usar los precedentes de seguimiento es Alt + T U T.
En el siguiente ejemplo, la flecha azul muestra que las celdas que afectan el valor de B6 son B1: B6 y A5. Como puede ver a continuación, la celda B6 también es parte de la fórmula, lo que la convierte en una referencia circular y hace que la fórmula devuelva "0" como resultado.
Esto se puede solucionar fácilmente reemplazando B6 con B5 en el argumento de SUM: = SUM (B1: B5).
Rastrear dependientes
La función de seguimiento de dependientes rastrea las celdas que dependen de la celda seleccionada. Esta función dibuja una línea azul que indica qué celdas se ven afectadas por la celda seleccionada. Es decir, muestra qué celdas contienen fórmulas que hacen referencia a la celda activa. La tecla de método abreviado para usar dependientes es Alt + T U D.
En el siguiente ejemplo, la celda D3 se ve afectada por B4. Depende de B4 por su valor para producir resultados. Por lo tanto, el seguimiento dependiente dibuja una línea azul de B4 a D3, lo que indica que D3 depende de B4.
Uso deliberado de referencias circulares en Excel
No se recomienda el uso deliberado de referencias circulares, pero puede haber casos excepcionales en los que necesite una referencia circular para poder obtener el resultado que desea.
Expliquemos eso usando un ejemplo.
Para empezar, habilite "Cálculo iterativo" en su libro de Excel. Una vez que haya habilitado el cálculo iterativo, puede comenzar a usar referencias circulares a su favor.
Supongamos que está comprando una casa y desea darle una comisión del 2% sobre el costo total de la casa a su agente. El costo total se calculará en la celda B6 y el porcentaje de comisión (tarifa del agente) se calculará en B4. La comisión se calcula a partir del costo total y el costo total incluye la comisión. Dado que las celdas B4 y B6 dependen entre sí, crea una referencia circular.
Ingrese la fórmula para calcular el costo total en la celda B6:
= SUMA (B1: B4)
Dado que el costo total incluye la tarifa del agente, incluimos B4 en la fórmula anterior.
Para calcular la tarifa de agente del 2%, inserte esta fórmula en B4:
= B6 * 2%Ahora, la fórmula en la celda B4 depende del valor de B6 para calcular el 2% de la tarifa total y la fórmula en B6 depende de B4 para calcular el costo total (incluida la tarifa del agente), de ahí la referencia circular.
Si el cálculo iterativo está habilitado, Excel no le dará una advertencia ni un 0 en el resultado. En cambio, el resultado de las celdas B6 y B4 se calculará como se muestra arriba.
La opción de cálculos iterativos suele estar desactivada de forma predeterminada. Si no lo encendió y cuando ingrese la fórmula en B4, se creará una referencia circular. Excel emitirá la advertencia y cuando haga clic en "Aceptar", se mostrará la flecha de seguimiento.
Eso es. Esto es todo lo que necesita saber sobre las referencias circulares en Excel.