Mientras trabaja con fórmulas de Excel, es posible que en algún momento vea el siguiente mensaje de advertencia.
Este mensaje le dice que hay una referencia circular en su hoja de trabajo y esto puede llevar a un cálculo incorrecto de las fórmulas. También le pide que aborde este problema de referencia circular y lo ordene.
En este tutorial, cubriré todo lo que necesita saber sobre la referencia circular, y también cómo encontrar y eliminar referencias circulares en Excel.
¡Entonces empecemos!
¿Qué es la referencia circular en Excel?
En palabras simples, una referencia circular ocurre cuando termina teniendo una fórmula en una celda, que en sí misma usa la celda (en la que se ingresó) para el cálculo.
Permítanme intentar explicar esto con un simple ejemplo.
Suponga que tiene el conjunto de datos en la celda A1: A5 y usa la siguiente fórmula en la celda A6:
= SUMA (A1: A6)
Esto le dará una advertencia de referencia circular.
Esto se debe a que desea sumar los valores en la celda A1: A6 y el resultado debe estar en la celda A6.
Esto crea un bucle ya que Excel sigue agregando el nuevo valor en la celda A6, que sigue cambiando (por lo tanto, un bucle de referencia circular).
¿Cómo encontrar referencias circulares en Excel?
Si bien el aviso de advertencia de referencia circular es lo suficientemente amable para decirle que existe en su hoja de trabajo, no le dice dónde está sucediendo y qué referencias de celda lo están causando.
Entonces, si está tratando de encontrar y manejar referencias circulares en la hoja de trabajo, necesita saber una manera de encontrarlas de alguna manera.
A continuación se muestran los pasos para encontrar una referencia circular en Excel:
- Activar la hoja de trabajo que tiene la referencia circular
- Haga clic en la pestaña Fórmulas
- En el grupo Edición de fórmulas, haga clic en el ícono desplegable Comprobación de errores (pequeña flecha que apunta hacia abajo a la derecha)
- Pase el cursor sobre la opción Referencias circulares. Le mostrará la celda que tiene una referencia circular en la hoja de trabajo
- Haga clic en la dirección de la celda (que se muestra) y lo llevará a esa celda en la hoja de trabajo.
Una vez que haya abordado el problema, puede volver a seguir los mismos pasos anteriores y se mostrarán más referencias de celda que tienen la referencia circular. Si no hay ninguno, no verá ninguna referencia de celda,
Otra forma rápida y sencilla de encontrar la referencia circular es mirando la barra de estado. En la parte izquierda, le mostrará el texto Referencia circular junto con la dirección de la celda.
Hay algunas cosas que debe saber al trabajar con referencias circulares:
- En caso de que el cálculo iterativo esté habilitado (cubierto más adelante en este tutorial), la barra de estado no mostrará la dirección de la celda de referencia circular
- En caso de que la referencia circular no esté en la hoja activa (sino en otras hojas del mismo libro de trabajo), solo mostrará la referencia circular y no la dirección de la celda
- En caso de que reciba un mensaje de advertencia de referencia circular una vez y lo descarte, no volverá a mostrar el mensaje la próxima vez.
- Si abre un libro de trabajo que tiene la referencia circular, le mostrará el mensaje tan pronto como se abra el libro de trabajo.
¿Cómo eliminar una referencia circular en Excel?
Una vez que haya identificado que hay referencias circulares en su hoja, es hora de eliminarlas (a menos que desee que estén allí por algún motivo).
Desafortunadamente, no es tan simple como presionar la tecla de borrar. Dado que estos dependen de fórmulas y cada fórmula es diferente, debe analizar esto caso por caso.
En caso de que sea solo una cuestión de que la referencia de celda que causa el problema por error, simplemente puede corregir ajustando la referencia.
Pero a veces, no es tan simple.
La referencia circular también se puede generar en base a múltiples celdas que se alimentan entre sí en muchos niveles.
Dejame mostrarte un ejemplo.
A continuación, hay una referencia circular en la celda C6, pero no es solo un caso simple de autorreferencia. Es de varios niveles donde las celdas que usa en los cálculos también se refieren entre sí.
- Las fórmulas en la celda A6 es = SUMA (A1: A5) + C6
- La fórmula es la celda C1 es = A6 * 0.1
- La fórmula en la celda C6 es = A6 + C1
En el ejemplo anterior, el resultado en la celda C6 depende de los valores en la celda A6 y C1, que a su vez dependen de la celda C6 (lo que provoca el error de referencia circular)
Y nuevamente, he elegido un ejemplo realmente simple solo para propósitos de demostración. En realidad, estos podrían ser bastante difíciles de descifrar y tal vez estar muy lejos en la misma hoja de trabajo o incluso estar dispersos en varias hojas de trabajo.
En tal caso, hay una forma de identificar las celdas que causan la referencia circular y luego tratarlas.
Es mediante el uso de la opción Trace Precedents.
A continuación se muestran los pasos para usar los precedentes de seguimiento para encontrar celdas que se están alimentando a la celda que tiene la referencia circular:
- Seleccione la celda que tiene la referencia circular
- Haga clic en la pestaña Fórmulas
- Haga clic en Trace Precedents
Los pasos anteriores le mostrarán flechas azules que le dirán qué celdas se están alimentando en la fórmula en la celda seleccionada. De esta manera, puede inspeccionar las fórmulas y las celdas y deshacerse de la referencia circular.
En caso de que esté trabajando con modelos financieros complejos, es posible que estos precedentes también abarquen varios niveles.
Esto funciona bien si tiene todas las fórmulas que se refieren a celdas en la misma hoja de trabajo. Si está en varias hojas de trabajo, este método no es efectivo.
Cómo habilitar / deshabilitar cálculos iterativos en Excel
Cuando tiene una referencia circular en una celda, primero obtiene el mensaje de advertencia como se muestra a continuación, y si cierra este cuadro de diálogo, le dará 0 como resultado en la celda.
Esto se debe a que cuando hay una referencia circular, es un bucle sin fin y Excel no quiere quedar atrapado en él. Entonces devuelve un 0.
Pero en algunos casos, es posible que desee que la referencia circular esté activa y realice un par de iteraciones. En tal caso, en lugar de un bucle infinito, puede decidir cuántas veces se debe ejecutar el bucle.
Se llama cálculo iterativo en Excel.
A continuación se muestran los pasos para habilitar y configurar cálculos iterativos en Excel:
- Haga clic en la pestaña Archivo
- Haga clic en Opciones. Esto abrirá el cuadro de diálogo Opciones de Excel.
- Seleccione Fórmula en el panel izquierdo.
- En la sección Opciones de cálculo, marque la casilla "Habilitar cálculo iterativo". Aquí puede especificar las iteraciones máximas y el valor de cambio máximo
¡Eso es! Los pasos anteriores permitirían el cálculo iterativo en Excel.
Permítanme también explicar rápidamente las dos opciones en el cálculo iterativo:
- Iteraciones máximas: Este es el número máximo de veces que desea que Excel calcule antes de darle el resultado final. Entonces, si especifica esto como 100, Excel ejecutará el ciclo 100 veces antes de darle el resultado final.
- Cambio máximo: Este es el cambio máximo, que si no se logra entre iteraciones, el cálculo se detendría. De forma predeterminada, el valor es 0,001. Cuanto menor sea este valor, más preciso será el resultado.
Recuerde que cuantas más veces se ejecuten las iteraciones, más tiempo y recursos necesitará Excel para hacerlo. En caso de que mantenga altas las iteraciones máximas, es posible que su Excel se ralentice o se bloquee.
Nota: Cuando los cálculos iterativos están habilitados, Excel no le mostrará el mensaje de advertencia de referencia circular y ahora también lo mostrará en la barra de estado.Uso deliberado de referencias circulares
En la mayoría de los casos, la presencia de una referencia circular en su hoja de trabajo sería un error. Y esta es la razón por la que Excel le muestra un mensaje que dice: "Intente eliminar o cambiar estas referencias o mover las fórmulas a diferentes celdas".
Pero puede haber algunos casos específicos en los que necesite una referencia circular para que pueda obtener el resultado deseado.
Ya he escrito uno de esos casos específicos sobre cómo obtener la marca de tiempo en una celda en una celda en Excel.
Por ejemplo, suponga que desea crear una fórmula para que cada entrada se realice en una celda de la Columna A, la marca de tiempo aparezca en la Columna B (como se muestra a continuación):
Si bien puede insertar fácilmente, inserte una marca de tiempo utilizando la siguiente fórmula:
= SI (A2 "", SI (B2 "", B2, AHORA ()), "")
El problema con la fórmula anterior es que actualizaría todas las marcas de tiempo tan pronto como se realice algún cambio en la hoja de trabajo o si la hoja de trabajo se vuelve a abrir (ya que la fórmula NOW es volátil)
Para solucionar este problema, puede utilizar un método de referencia circular. Utilice la misma fórmula, pero habilite el cálculo iterativo.
También hay otros casos en los que se desea tener la capacidad de usar una referencia circular (puede encontrar un ejemplo aquí).
Nota: Si bien puede haber algunos casos en los que pueda usar la referencia circular, creo que es mejor evitar su uso. Las referencias circulares también pueden afectar el rendimiento de su libro de trabajo y hacerlo más lento. En casos excepcionales en los que lo necesita, siempre prefiero usar códigos VBA para hacer el trabajo.¡Espero que este tutorial te haya resultado útil!
Otros tutoriales de Excel que pueden resultarle útiles:
- #¡ÁRBITRO! Error en Excel; ¡Cómo corregir el error de referencia!
- Manejo de errores de Excel VBA
- Utilice IFERROR con VLOOKUP para deshacerse de los errores # N / A
- Cómo hacer referencia a otra hoja o libro de trabajo en Excel (con ejemplos)
- Referencias de celda absoluta, relativa y mixta en Excel
