Cómo manejar las referencias circulares en Excel
En Excel, normalmente escribes fórmulas para obtener resultados y es bastante común hacer referencia a otras celdas en el proceso. Sin embargo, ¿qué ocurre si haces referencia a la propia celda, ya sea directa o indirectamente? ¡Esto significa un bucle infinito de cálculos! Por suerte, Excel tiene soluciones para lidiar con este tipo de situaciones.
Referencia circular significa que su fórmula está tratando de calcular la celda de origen. Normalmente, esto se considera un error. Sin embargo, hay veces en las que este error puede ser realmente útil y es posible que desee crear una referencia circular a propósito. Veamos más de cerca este fenómeno con algunos ejemplos. Puedes descargar el libro de trabajo de ejemplo aquí.
Referencia directa
Si vas a la celda C3, por ejemplo, y luego escribes «=C3», confundirías un poco a Excel porque esta celda está ahora apuntando a sí misma, creando así una referencia circular. Hacer lo mismo dentro de una fórmula también tendrá el mismo efecto (es decir, =IF(C3,…,…) o =C3+10).
Otro ejemplo común es incluir la celda original cuando se selecciona un rango de celdas adyacentes. Por ejemplo, incluir la celda C4 en la fórmula de suma =SUMA(C2:C4) transformará esto en una referencia circular.
Referencia indirecta
Una celda que se dirige a una celda que apunta a la celda de origen creará una referencia indirecta. En otras palabras, la referencia circular puede ser establecida por dos celdas que se apuntan entre sí. Por ejemplo, introduciendo «=D3» en la celda B3, mientras que la celda D3 contiene una referencia de la celda B3 («=B3» en este caso).
¿Por qué es un problema?
Excel advierte al usuario cuando hay una ocurrencia de una referencia circular, porque una referencia circular esencialmente significa bucles interminables. Decirle a Excel que repita el mismo cálculo una y otra vez durante un número infinito de veces no tiene demasiado sentido. Para evitar que se congele el ordenador, Excel simplemente ignora las referencias circulares.
Echemos un vistazo a este mensaje de advertencia. Obtendrá el siguiente mensaje después de escribir una fórmula con referencias circulares,
Note que Excel no da el mismo error para las referencias circulares adicionales de la fórmula. En la barra de estado de Excel, se mostrará la última celda con una referencia circular. Esto significa que puede haber más de una referencia de este tipo en esta ficha. Este es el aspecto,
Excel también mantiene una lista de todas las referencias circulares, a la que se puede acceder desde la opción Comprobación de errores de la pestaña Fórmula,
Cómo deshacerse de ellas
Desgraciadamente, no hay una forma fácil de eliminar o arreglar las referencias circulares no deseadas. Vas a tener que utilizar alguna de las formas mencionadas anteriormente y resolverlas una a una. Para saber qué está causando el problema, puede utilizar la opción de Auditoría de Fórmulas en la pestaña de Fórmulas. Consulta nuestra guía para aprender a hacerlo, https://www.spreadsheetweb.com/identifying-analyzing-spreadsheets-formula-auditing/
¿Pueden ser útiles?
¡Sí! Aunque, sólo en algunas circunstancias. Es posible que quieras calcular intencionadamente un bloque de fórmulas circulares más de unas cuantas veces. Excel permite este enfoque de cálculo «iterativo» utilizando límites.
Para activar y utilizar las referencias circulares primero debes activarlas marcando la opción Habilitar cálculos iterativos en el menú Archivo.
Ir a Archivo > Opciones > Fórmulas > Opciones de cálculo en Excel 2016, Excel 2013 y Excel 2010.
En Excel 2007, hay que ir al botón Office > Opciones de Excel > Fórmulas > Área de iteración
En Excel 2003 y anteriores, hay que ir aMenú > Herramientas > Opciones > Cálculo
Activando los cálculos iterativos aparecerán dos entradas adicionales en el mismo menú,
- Iteraciones máximas determina el número de veces que Excel debe recalcular el libro
- Cambio máximo determina la diferencia máxima entre los valores de las fórmulas iterativas. Tenga en cuenta que un número más pequeño significa resultados precisos.
Los cálculos iterativos se detienen cuando se cumple una de las condiciones definidas anteriormente.
Casos de uso
Calcular el valor futuro de una inversión
Supongamos que tenemos 10.000 dólares queremos invertir este dinero en una cuenta de CD. El interés mensual en esta opción es del 1,25%. Descargue el libro de trabajo de ejemplo para este caso de uso aquí. Para calcular el valor total al final del mes 21, vamos a calcular cada mes y sumar el interés al mes anterior.
En primer lugar, introduzca el dinero inicial, el interés y la función básica de valor total,
=valor * ( 1 + tipo de interés)
A continuación, seleccione la celda con el valor inicial en efectivo y añada la referencia de la función de valor total.
Este movimiento provocará una advertencia de referencia circular si los cálculos iterativos no están activados. Si aún no lo ha hecho, habilite esta opción y establezca Iteraciones máximas en 20 para encontrar el interés en el mes 21.
Pulse OK.
Sello de tiempo automático
Las referencias circulares también se pueden utilizar para añadir el sello de tiempo en las celdas. Descargue el libro de ejemplo para este caso de uso aquí. Supongamos que queremos añadir marcas de tiempo a los pedidos en la tabla de abajo,
Podemos utilizar referencias circulares para añadir una marca de tiempo cuando se introduce la información de un nuevo pedido.
Empiece por añadir una nueva columna en la tabla para colocar las marcas de tiempo. Escribe la fórmula, =IF(A2<>»,IF(I2<>»,I2,NOW()),»»)
Esta fórmula comprobará si se ha introducido el Número de Pedido (celda A2). Si hay un valor introducido y la celda de la marca de tiempo está vacía, entonces la fórmula devolverá la función NOW().
Observe que al pulsar el botón Introducir aparecerá un aviso de referencia circular si los cálculos iterativos no están activados. Esta vez los números de Iteraciones Máximas o Cambio Máximo no significan mucho porque sólo necesitamos una única iteración, así que puedes dejar estos dos inputs en sus valores por defecto.
Ahora, cada vez que introduzcamos una nueva orden y creemos una nueva fila, se imprimirá automáticamente una marca de tiempo en la columna Timestamp.
