Referencias circulares: ¿error o acierto?

Hoy contamos con un gran artículo escrito por Rafael Palacios (ver más info al final de la página) que nos desmitificará uno de los “errores” más comunes en Excel.

¡Adelante, Rafael!

Las referencias circulares suelen considerarse como un error en la introducción de fórmulas del que Excel nos advierte con severidad. La mayoría de vídeos, tutoriales y artículos sobre referencias circulares tratan sobre el modo de evitar que aparezcan y sobre el modo de corregirlas si llegan a aparecer. Es decir, tratan a las referencias circulares como si fuesen un mal. Pero no siempre es así. En este artículo [o vídeo] veremos que las referencias circulares pueden ser un recurso de gran interés cuando se usan de manera intencionada y del modo adecuado.

¿Qué son?

Con carácter general, una referencia circular es un cálculo que depende de sí mismo, es decir, un cálculo en el que la variable independiente (o explicativa) es a la vez la variable dependiente (o explicada). Si circunscribimos nuestro análisis a Excel, una referencia circular es una fórmula que hace referencia a la misma celda en la que está alojada, aunque sea de un modo indirecto. Esto provoca un comportamiento paradójico: para poder determinar el resultado de la celda, Excel necesitaría conocer primero el resultado de esa celda…

Referencias circulares directas e indirectas

Las autorreferencias directas, infrecuentes, pueden proceder de un error de sintaxis, o de una incorrecta delimitación de los rangos que deben constituir un argumento de alguna función, como podría ser un caso así:

La autorreferencia es inmediata: el cálculo de una celda hace referencia a la misma celda en que se aloja, sin que otras celdas intervengan en ningún cálculo. Estos errores de sintaxis, por evidentes, tienen una importancia francamente menor: son fácilmente subsanables y no comportan un problema severo para el algoritmo de cálculo que la hoja deba seguir. El usuario que tropieza con una advertencia de circularidad en un caso como éste puede continuar desarrollando su proyecto sin más rectificación que la puramente formal, porque la estructuración de la información y el procedimiento de cálculo seguido hasta el incidente mantienen intacta su integridad y su consistencia.

Otras veces, la autorreferencia no es inmediata, sino mediata, y proviene de una sucesión de cálculos que, tras una cadena de relaciones de dependencia funcional, provoca un bucle cerrado similar al siguiente:

Cuando la estructura de los libros contiene numerosas hojas y los cálculos toman fuentes de procedencias muy diversas, no es infrecuente que pueda aparecer una referencia circular indirecta, inesperada, y que no siempre es directamente observable ni inmediatamente comprensible.

Este tipo de autorreferencias indirectas merecen una consideración más rigurosa que el tipo anterior, porque revelan con harta frecuencia un defecto procedimental. Si la autorreferencia directa no merecía más calificación que la de mero lapsus de tecleo, la aparición de una referencia circular indirecta revela una carencia en el algoritmo de cálculo o en la estructura de los datos que las funciones y fórmulas toman como materia prima. En otras palabras, las referencias circulares de esta clase pueden llegar a evidenciar que el usuario no tiene del todo claro cuáles son las relaciones entre las variables: no sabe qué variables dependen de otras ni cuáles afectan a las demás, no sabe cuales tienen el carácter de causa (o de variable independiente) y cuáles el de consecuencia (o de variable dependiente), o quizás no pueden determinar de un modo consistente el orden en el que los sucesivos cálculos deben ser ejecutados. Este tipo de errores autorreferenciales no nacen de un desconocimiento o de un despiste en el manejo de la herramienta Excel, sino de un problema conceptual o lógico relacionado con la planificación del proyecto y, por lo tanto, previo e independiente del uso de la hoja de cálculo.

¿Cómo las detecta Excel?

Excel incorpora diversas formas de denuncia de las referencias circulares.
Lo más frecuente es que Excel detecte la aparición de una referencia circular en el mismo instante en el que se ejecuta por primera vez la fórmula que la provoca, mostrando un mensaje de advertencia como este:

Otra de las formas autónomas de advertencia de la existencia de una referencia circular es un discreto aviso en la parte izquierda de la barra de estado:

En la propia área de trabajo aparecerán también las flechas que muestran las relaciones de dependencia que están provocando la autorreferencia:

Y, por si lo anterior no fuese suficiente, también es posible investigar las posibles referencias circulares mediante un recurso específico de la Auditoría de fórmulas:

Merece significarse el hecho de que la comprobación de las referencias circulares esté integrada entre las herramientas de Comprobación de errores, a pesar de lo cual seguimos afirmando que las referencias circulares no siempre son un error.

No puede resultar extraño que el usuario medio, a la vista de tanta insistencia, tanta cautela, y tanta atención de Excel a las referencias circulares, haya interiorizado un rechazo a estas construcciones autorreferenciales como si fuesen un error vergonzante. Pero no siempre lo son.

No siempre son un error

En algunas ocasiones, la aparición de una referencia circular no tiene como causa el error. El usuario puede desear que varias celdas crucen sus referencias mutuamente por alguna necesidad operativa o por la propia naturaleza del problema de cálculo que desea resolver. Cuando esto ocurre, es decir, cuando la autorreferencia está conscientemente planificada, Excel no puede impedir que el proyecto se desarrolle del modo deseado por un capricho de orden formal. Al contrario, el usuario puede configurar la herramienta para que Excel opere con las referencias circulares, aun contra la expresa advertencia de que los resultados obtenidos pudieran ser incorrectos o incluso no pudieran llegar a ser calculados.

Pero si existe una referencia circular indirecta, lo cierto es que habrá dos o más celdas, es decir, dos o más variables, sobre las que se habrá construido una secuencia de cálculo cerrada sobre sí misma, como en el esquema siguiente:

En un caso así, donde la circularidad es evidente, la secuencia cíclica podría empezar a enunciarse en cualquiera de las variables, porque A determina el valor de B, que determina a su vez el valor de C, que determina a su vez el valor de A… Luego A determina su propio valor (autorreferencia), aunque sea pasando por las demás variables. Este mismo enunciado se podría haber iniciado en cualquiera de las variables involucradas en el lazo.

Por otro lado, el enunciado no debería terminar forzosamente tras una vuelta… Si A determinó el valor de B, y B determinó a su vez el valor de C, y C determinó a su vez el valor de A, nada impide proseguir el razonamiento concluyendo que este valor de A así determinado determinará a su vez el valor de B, y éste el de C… sin que al proceso quepa, sin más, concebirle un momento de detención.

Por lo tanto, en las referencias circulares se manifiesta una peculiaridad: originan procesos de cálculo cíclicos y sin fin propio. Excel podría continuar la secuencia de cálculo indefinidamente… ¡hasta el infinito! O quizás no tanto, pero sí lo hará un buen puñado de veces, que el usuario puede determinar. A eso es a lo que se llama cálculo iterativo, que es una opción que el usuario debe habilitar expresamente. Aunque el procedimiento puede diferir en distintas versiones de Excel, la configuración en Microsoft Office 365 ProPlus pasa por activar la casilla Habilitar cálculo iterativo (en la imagen aparece aún como desactivado) en la sección Opciones de cálculo del cuadro de Fórmulas, al que se accede desde el submenú general de Opciones del menú Archivo:

En la configuración del cálculo iterativo el usuario debe establecer cuántas iteraciones desea admitir, es decir, cuántas veces desea que se ejecute el ciclo completo de cálculos. Aunque también puede establecer como criterio de detención una variación máxima en los resultados: si los resultados se estabilizan en el entorno de un valor tan estrecho como se especifique, tampoco será necesario seguir iterando los cálculos.

Las referencias circulares en acción: un ejemplo

Para ilustrar el funcionamiento de las referencias circulares resolveremos un sencillo problema formulado de un modo autorreferencial. Imaginemos que tenemos esta información:

Ataulfo tiene la mitad de la edad de Veneranda. Veneranda le saca quince años a Ataulfo. ¿Qué edades tienen?

Este problema admite diversas técnicas de resolución. La más inmediata, el cálculo a ojo. Si Veneranda le saca quince años a Ataulfo y le dobla la edad, es obvio que Veneranda tiene 30 años y Ataulfo 15. Un sencillo sistema de ecuaciones (A=V/2; V=A+15) se lo confirmará a quien prefiera usar un poco de álgebra, un folio y un bolígrafo. Con Excel se podría recurrir a construir una tabla de doble entrada con un amplio rango de edades para cada uno y un par de funciones condicionales para ver qué combinación satisface las restricciones del problema. Buscar objetivo también podría servir y, por supuesto, Solver. Un problema, por sencillo que sea, puede atacarse por distintos frentes y con diferentes armas…

Veamos cómo podría hacerse también usando referencias circulares:

Empezaremos con el cálculo iterativo desactivado, para comprobar que Excel reconoce la referencia circular. Las fórmulas que se introducen en la hoja de cálculo son las indicadas:

Como se aprecia, en C3 hacemos depender la edad de Ataulfo de la de Veneranda (C7), y la de Veneranda, según la fórmula de C7, depende a su vez de la de Ataulfo (C3), que depende a su ve de la de Veneranda, que depende a su vez de la de Ataulfo… La circularidad es más que evidente, pero ¿dónde está el error? Ninguna de las ecuaciones (o fórmulas) es incorrecta, y ambas son independientes y, por lo tanto, necesarias, así que nuestro problema está bien planteado. No hay error. Y, sin embargo, Excel no miente: nuestras fórmulas incurren en circularidad, así que Excel necesita algo más de información para no atascarse con una sucesión indefinida de cálculos sin fin en la que la edad de uno condiciona la edad del otro y la edad del otro condiciona la edad del uno.

Para resolver estas fórmulas y, con ello, nuestro problema, activamos el cálculo iterativo, según el procedimiento explicado antes:

Y Excel nos ofrecerá la siguiente solución:

Esta solución, aunque pudiera servir como aproximación satisfactoria dada la naturaleza del problema, no es exacta, y merece tenerse en cuenta el motivo por el que esto ocurre. El cálculo iterativo es, como cabría esperar, una iteración de cálculos: una cadena de cálculos que pasan de una celda a otra. El proceso, en resumen, es el siguiente. Excel parte de los valores que hay inicialmente en las celdas C3 y C7: dos ceros. Si empieza calculando la edad de Ataulfo, como es la mitad de la de Veneranda (0) será A=0/2=0. Y ahora pasa a la edad de Veneranda, que será 15 años más que la de Ataulfo (0), así que será V=0+15=15. En la vuelta siguiente, la edad de Ataulfo será la mitad de la de Veneranda (15): A=15/2=7,5. La edad de Veneranda será ahora V=7,5+15=22,5. La edad de Ataulfo será, en otra iteración, A=22,5/2=11,25, y la de Veneranda será V=11,25+15=26,25… El lector podrá calcular algunas iteraciones más para comprobar cómo los valores van aproximándose cada vez más a la solución del problema. El proceso no seguirá indefinidamente porque la configuración por defecto estableció un máximo de 100 iteraciones y una variación máxima de 0,001. Dado que la solución de este problema es convergente (es decir, la solución tiende a estabilizarse en torno a un valor, no a alejarse de él), cuando Excel detecta que entre dos iteraciones la variación ya es más pequeña que ese 0,001 detiene el proceso, aunque no haya llegado a las 100 vueltas. En caso contrario, se detendrá en cualquier caso tras 100 ciclos. Estos valores se pueden modificar para ganar precisión o para acelerar los cálculos, pero explican por qué la solución ofrecida no es exactamente 15 y 30 sino precisamente dos números que se han aproximado tanto que Excel ya no aprecia cambios relevantes en cada iteración.

Conclusión

Las referencias circularesson tratadas en Excel como errores, con rigurosas advertencias que incluyen alertas visuales, sonoras y hasta un cuadro de diálogo que impide continuar sin dar una respuesta. No obstante, se ha ofrecido una sugerencia de uso intencionado como herramienta para la resolución de problemas en los que la naturaleza de las relaciones entre variables las hacen imprescindibles, necesarias o convenientes. El cálculo iterativo es la técnica de la que Excel se sirve para solventar el problema esencial de las formulaciones autorreferenciales: dónde empezar y cuándo parar…
Aunque el ejemplo propuesto aquí, por su simplicidad, haya servido meramente para ilustrar cómo funcionan, la utilización de referencias circulares en la resolución de problemas empresariales puede adquirir cotas de complejidad notablemente superiores. El lector interesado puede encontrar aquí un ejemplo algo más sofisticado sobre la utilización de referencias circulares para afrontar cálculos de tiempos en la gestión de proyectos.

En cualquier caso, agradecemos sus opiniones y quedamos a su disposición para comentar las cuestiones que deseen.