Saltar al contenido

Cómo encontrar referencias circulares en Excel

En un artículo reciente expliqué el concepto de referencias circulares y cómo los usuarios a veces incorporan intencionadamente o no esos tipos de fórmulas en las hojas de cálculo. En ese artículo utilicé un ejemplo extremadamente simple de cálculo de una tarifa que implicaba una referencia circular entre dos celdas. Sin embargo, a veces los modelos de previsión complejos requieren referencias circulares que pueden estar dispersas en una hoja de cálculo grande. Además, Excel a veces se pone en un estado en el que no puede resolver la referencia circular. La solución es desactivar temporalmente las fórmulas y luego habilitarlas de nuevo una vez que las fórmulas funcionan. Sin embargo, rastrear todos los zarcillos de una referencia circular siempre fue difícil para mí hasta que descubrí una característica oculta en Excel.

Como se ilustra en la figura 1, vamos a establecer otro ejemplo simple para establecer el escenario para la característica que voy a revelar. En un libro de trabajo en blanco, vamos a replicar el simple ejemplo que usé en mi primer artículo, pero esta vez a través de tres pestañas de la hoja de trabajo:

Cómo encontrar referencias circulares en Excel
  • Hoja1: En la celda A1 introduce las palabras Coste de construcción y en la celda B1 introduce 100.000.000.
  • Hoja2: En la celda A1 introduce las palabras Tasa de desarrollo y en la celda C1 introduce 3%. Deja la celda B1 en blanco por el momento.
  • Hoja3: En la celda A1 introduce las palabras Costo Total del Edificio y en la celda B1 introduce esta fórmula: =SUMA(Hoja1:Hoja2!B1)

    • Esto crea lo que se conoce como una fórmula de perforación, donde Excel perfora a través de dos o más hojas de trabajo y suma la misma celda o celdas en cada hoja.
    • Esto establece el escenario para nuestra referencia circular, así que en la Hoja2 introduce esta fórmula en la celda B1: =Hoja3!B1*C1
    • Al pulsar Intro, dependiendo de su versión de Excel, pueden aparecer en la pantalla cualquier número de avisos e indicaciones.

Figura 1: Este es un ejemplo simple de una referencia circular entre dos celdas.

Para habilitar las referencias circulares, active la opción de Cálculo Iterativo de Excel:

  • Excel 2007 y posteriores: Elija Archivo, Opciones, Fórmulas y luego haga clic en Habilitar cálculos iterativos, como se muestra en la Figura 2.
  • Excel 2003 y anteriores: Elija Archivo, Propiedades, Cálculo y luego haga clic en Iteración.
  • Excel 2011 para Mac: Haz clic en el menú de Excel, elige Preferencias, Cálculos y luego haz clic en Limitar la iteración.

Figura 2: Siga estos pasos para habilitar los cálculos iterativos.

Esto debería hacer que Excel resuelva la fórmula, de tal manera que la celda B1 de la Hoja3 devuelva 103.092.784 dólares. En este punto nuestra fórmula funciona igual que el anterior ejemplo simple. Sin embargo, imaginemos que nuestra referencia circular implica docenas de celdas, quizás dispersas en numerosas hojas de trabajo. Encontrar el punto de partida de una referencia circular en medio de un mar de fórmulas puede llevarte una gran cantidad de tiempo, especialmente en una hoja de cálculo que no hayas escrito. Afortunadamente, en Excel 2007 y posteriores, hay una función que le permitirá rastrear fácilmente la ubicación de cualquier referencia circular:

  • Elija Fórmulas.
  • Haz clic en la flecha situada junto a Comprobación de errores.
  • Elija Referencias Circulares.

Como se ilustra en la figura 3, esta característica está actualmente desactivada porque activamos la opción de Cálculo Iterativo.

Figura 3: El comando Referencias Circulares está desactivado.

Si utilizamos los pasos anteriores para desactivar esta función, y luego volvemos al comando del menú Referencias Circulares, verá que esta vez Excel le da una hoja de ruta a las celdas que están relacionadas entre sí de forma circular, como se ilustra en la Figura 4. Puedes usar esta información para reescribir la fórmula de forma no circular, o para colocar un apóstrofe delante de ciertas celdas para desactivar las fórmulas. Hacerlo puede forzar a Excel a salir de una situación en la que todas las fórmulas relacionadas circularmente devuelven #VALOR!. Una vez que se ha roto el ciclo, se puede habilitar cada fórmula de nuevo para restaurar las referencias circulares si es necesario.

Figura 4: La característica de Referencias Circulares muestra las celdas que están relacionadas entre sí.