Mientras trabaja con fórmulas de Excel, puede ver a veces el siguiente aviso de advertencia.
Este aviso le indica que hay una referencia circular en su hoja de trabajo y esto puede conducir o un cálculo incorrecto por 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, así como la forma de encontrar y eliminar las referencias circulares en Excel.
Así que vamos a empezar!
Este tutorial cubre:
¿Qué es la referencia circular en Excel?
En palabras sencillas, una referencia circular ocurre cuando se termina teniendo una fórmula en una celda – que en sí misma utiliza la celda (en la que se ha introducido) para el cálculo.
Déjame intentar explicar esto con un ejemplo sencillo.
Supongamos que tienes el conjunto de datos en la celda A1:A5 y utilizas la siguiente fórmula en la celda A6:
=SUM(A1:A6)
Esto te dará un aviso de referencia circular.
Esto se debe a que quieres sumar los valores de la celda A1:A6, y el resultado debería estar en la celda A6.
Esto crea un bucle ya que Excel simplemente sigue añadiendo 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?
Aunque el aviso de referencia circular es lo suficientemente amable como para decirte que existe en tu hoja de trabajo, no te dice dónde está ocurriendo y qué referencias de celda lo están causando.
Así que si estás tratando de encontrar y manejar las referencias circulares en la hoja de trabajo, necesitas 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
- Hacer clic en la pestaña Fórmulas
- En el grupo Edición de fórmulas, hacer clic en el icono desplegable de Comprobación de errores (flechita hacia abajo a la derecha)
- Pasar el cursor por encima de la opción Referencias circulares. Te mostrará la celda que tiene una referencia circular en la hoja de trabajo
- Pulsa sobre la dirección de la celda (que se muestra) y te llevará a esa celda de la hoja de trabajo.
- 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), sólo mostrará Referencia circular y no la dirección de la celda
- En caso de que obtenga un aviso de referencia circular una vez y lo desestime, no volverá a mostrar el aviso la próxima vez.
- Si abre un libro que tiene la referencia circular, le mostrará el aviso en cuanto se abra el libro.
Una vez que hayas solucionado el problema, puedes volver a seguir los mismos pasos anteriores y te mostrará más referencias de celdas que tengan la referencia circular. Si no hay ninguna, no verás 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 de la misma, te mostrará el texto Referencia circular junto con la dirección de la celda.
Hay algunas cosas que debes saber cuando trabajas con referencias circulares:
¿Cómo eliminar una referencia circular en Excel?
Una vez que has identificado que hay referencias circulares en tu hoja, es el momento de eliminarlas (a no ser que quieras que estén ahí por algún motivo).
Desgraciadamente, no es tan sencillo como darle a la tecla de borrar. Dado que dependen de las fórmulas y cada fórmula es diferente, hay que analizarlo caso por caso.
En caso de que sólo se trate de que la referencia de la celda esté causando el problema por error, se puede corregir simplemente ajustando la referencia.
Pero a veces, no es tan simple.
La referencia circular también puede ser causada en base a múltiples celdas que se alimentan entre sí en muchos niveles.
Déjeme mostrarle un ejemplo.
Abajo hay una referencia circular en la celda C6, pero no es un simple caso de autorreferencia. Es multinivel donde las celdas que utiliza en los cálculos también se referencian entre sí.
- La fórmula en la celda A6 es =SUMA(A1:A5)+C6
- La fórmula es la celda C1 es =A6*0.1
- La fórmula de la celda C6 es =A6+C1
En el ejemplo anterior, el resultado de la celda C6 depende de los valores de la celda A6 y C1, que a su vez dependen de la celda C6 (provocando así el error de referencia circular)
Y de nuevo, he elegido un ejemplo realmente sencillo sólo a modo de demostración. En realidad, estos podrían ser bastante difíciles de averiguar y tal vez lejos en la misma hoja de trabajo o incluso dispersos a través de múltiples hojas de trabajo.
En tal caso, hay una manera de identificar las celdas que están causando la referencia circular y luego tratar estos.
Es mediante el uso de la opción Trazar Precedentes.
A continuación se muestran los pasos para utilizar los precedentes de rastreo para encontrar las celdas que 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 Rastrear precedentes
Los pasos anteriores le mostrarían flechas azules que le indicarán qué celdas están alimentando la fórmula de 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, puede ser posible que estos precedentes también vayan a múltiples niveles de profundidad.
Esto funciona bien si tiene todas las fórmulas que se refieren a las celdas en la misma hoja de trabajo. Si está en múltiples hojas de trabajo, este método no es efectivo.
Cómo activar/desactivar los cálculos iterativos en Excel
Cuando tienes una referencia circular en una celda, primero te aparece el aviso de advertencia como el que se muestra a continuación, y si cierras este cuadro de diálogo, te 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. Así que devuelve un 0.
Pero en algunos casos, puede que realmente quieras que la referencia circular esté activa y haga un par de iteraciones. En tal caso, en lugar de un bucle infinito, y usted puede decidir cuántas veces debe ejecutarse el bucle.
Esto se llama cálculo iterativo en Excel.
A continuación se muestran los pasos para activar y configurar los 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 todo! Los pasos anteriores permitirían habilitar el cálculo iterativo en Excel.
Déjame también explicar rápidamente las dos opciones del cálculo iterativo:
- Iteraciones máximas: Es el número máximo de veces que quieres que Excel calcule antes de darte el resultado final. Así, si especifica esto como 100, Excel ejecutará el bucle 100 veces antes de darle el resultado final.
- Cambio máximo: Este es el cambio máximo, que si no se alcanza entre iteraciones, el cálculo se detendría. Por defecto, el valor es 0,001. Cuanto más bajo sea este valor, más preciso sería 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 mantengas el máximo de iteraciones alto, puede llevar a que tu Excel se ralentice o se cuelgue.
Usando deliberadamente referencias circulares
En la mayoría de los casos, la presencia de una referencia circular en tu hoja de trabajo sería un error. Y esta es la razón por la que Excel le muestra un aviso 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 necesita una referencia circular para que pueda obtener el resultado deseado.
Uno de estos casos específicos ya he escrito sobre él obteniendo la marca de tiempo en una celda en Excel.
Por ejemplo, supongamos que queremos crear una fórmula para que cada vez que se realice una entrada en una celda de la Columna A, la marca de tiempo aparezca en la Columna B (como se muestra a continuación):
Mientras que podemos insertar fácilmente una marca de tiempo utilizando la siguiente fórmula:
=IF(A2<>"",IF(B2<>"",B2,NOW()),"")
El problema con la fórmula anterior es que actualizaría todas las marcas de tiempo tan pronto como se realice cualquier cambio en la hoja de trabajo o si se vuelve a abrir la hoja de trabajo (ya que la fórmula NOW es volátil)
Para evitar este problema, puede utilizar un método de referencia circular. Utilice la misma fórmula, pero habilite el cálculo iterativo.
Hay algunos otros casos también en los que se desea tener la capacidad de utilizar la referencia circular (puede encontrar un ejemplo aquí).
Espero que hayas encontrado este tutorial útil!
Otros tutoriales de Excel que pueden ser útiles: