Cómo buscar y eliminar referencias circulares en Excel

Aranzulla de Los Pobres

Las referencias circulares pueden ser tediosas de rastrear y eliminar por su cuenta. Aprenda a utilizar las herramientas de Excel para deshacerse de ellas.

¿Qué son las referencias circulares en Excel?

Una referencia circular es un término utilizado para una fórmula que visita una celda más de una vez en su línea de cálculos. Debido a esto, los cálculos tomarán mucho tiempo para procesar números, y es muy probable que los cálculos arrojen una respuesta incorrecta la mayor parte del tiempo.

Sin embargo, las referencias circulares no siempre son perjudiciales. En algunos casos, son bastante útiles. A pesar de eso, las referencias circulares plantean el riesgo de salirse de control y contribuir a problemas que no son evidentes de inmediato.

Si tiene una referencia circular en su hoja de cálculo pero nunca tuvo la intención de tener una, entonces debería deshacerse de ella.

Comprensión de las referencias circulares

Para eliminar la fórmula, primero debe comprenderla. Hay dos formas en que una referencia puede ser circular: directa e indirectamente.

Referencias circulares directas

Una referencia circular directa ocurre cuando una celda se refiere directamente a sí misma. Hagamos una referencia circular directa:

  1. En las celdas A1 , A2 y A3 , ingrese los números 100, 200 y 300.
  2. Seleccione la celda A4 y, en la barra de fórmulas, ingrese la fórmula siguiente:
     =SUM(A1+A2+A3+A4)

    Esta es una función SUMA simple que sumará las celdas A1, A2, A3 y A4.

  3. Una vez que haya escrito la fórmula, presione Entrar . Aparecerá un mensaje de error.
  4. En el mensaje de error que aparece, Excel le advierte que existe una referencia circular en esta hoja de cálculo. Haga clic en Aceptar .

Esta fórmula crea una referencia circular en la que A4 busca sumar un conjunto de valores con el valor de A4. Sin embargo, ¿cuál es el valor de A4? Nunca lo sabrá, ni tampoco Excel.

Como resultado de esta referencia circular, Excel devolverá 0 para la fórmula. Es posible que no sepa la respuesta a esta fórmula, pero no es 0.

Relacionado: Funciones de búsqueda de Excel para buscar hojas de cálculo de manera eficiente

Referencias circulares indirectas

Una referencia circular indirecta es cuando una celda se refiere a sí misma a través de otras celdas.

  1. En la celda A1 , ingrese la fórmula a continuación:
     =D1

    Esto establecerá el valor de A1 en el de D1. Lo que sea que pueda ser.

  2. Ingrese los números 100 y 200 en las celdas B1 y C1 , respectivamente.
  3. Seleccione la celda D1 y, en la barra de fórmulas, ingrese la fórmula siguiente:
     =SUM(A1+B1 + C1)

    Esta es otra función SUMA que sumará los valores en las celdas A1, B1 y C1 y devolverá el resultado en D1.

  4. Presione Entrar . Aparecerá un mensaje de error.
  5. En el mensaje de error, haga clic en Aceptar .

Como antes, Excel devuelve 0. Sin embargo, a diferencia del ejemplo anterior, la celda D4 no hace referencia directa a sí misma. En cambio, suma A1 con un conjunto de valores. ¿Qué es A1? Es D4. Entonces, ¿qué es D4? Es A1 con una matriz de valores.

Se ha formado una referencia circular.

Relacionado Cómo copiar fórmulas en Microsoft Excel

Buscar y eliminar referencias circulares en Excel

En escenarios más complejos, donde numerosas celdas se refieren entre sí, encontrar referencias circulares puede no ser tan sencillo como mirar la fórmula sola. Excel tiene herramientas para ayudarlo a encontrar referencias circulares.

Antes de empezar a encontrar referencias circulares, agreguemos algunas a su hoja de cálculo:

  1. En las celdas A1 , A2 y A3 , ingrese 1, 2 y 3.
  2. Seleccione la celda A4 .
  3. En la barra de fórmulas, ingrese la fórmula a continuación y presione Entrar :
     =SUM(A1+A2+A3+A4)

    Como en el primer ejemplo, esta también es una referencia circular directa donde A4 se refiere a sí mismo.

  4. En el mensaje de error, haga clic en Aceptar .

Excel devuelve 0 porque la fórmula en A4 es una referencia circular directa. Creemos también uno indirecto:

  1. En la celda C1 , escriba 20 .
  2. Ingrese la fórmula a continuación para la celda E4 :
     =G1+3
  3. Ingrese la fórmula a continuación para la celda G1 :
     =C1+2
  4. Por último, seleccione la celda C1 en la barra de fórmulas y reemplace 20 con la siguiente línea:
     =E4+1

Como se esperaba, Excel devuelve 0 porque estas celdas se refieren entre sí en un círculo. Sin embargo, dado que no se refieren directamente a sí mismos, son referencias circulares indirectas.

Relacionado: Cómo contar valores únicos en Excel

Exploremos cómo puede usar las herramientas de Excel para encontrar referencias circulares en su hoja de cálculo. Tenga en cuenta que su hoja de cálculo contiene cuatro referencias circulares, a saber, A4 (directo), C1, E4 y G1 (indirecto).

  1. Desde el menú de la cinta, vaya a la pestaña Fórmula .
  2. En la sección Auditoría de fórmulas , haga clic en el botón de flecha junto a Comprobación de errores .
  3. En el menú, pase el mouse sobre Referencias circulares . Esto abrirá una lista de referencias circulares.
  4. Haga clic en la celda de la lista. Esto lo llevará a la celda con referencia circular.
  5. Notará que Excel muestra solo una de las cuatro referencias. Esto se debe a que Excel maneja estas cadenas una a la vez.
  6. Una vez que arregle la primera referencia circular, puede pasar a la siguiente.
  7. En el siguiente paso, corrija la referencia circular .
  8. Vuelva a Comprobación de errores y haga clic en Referencias circulares .
  9. De la lista, seleccione la siguiente referencia circular y proceda a arreglarla.

Rastrear relaciones celulares

Hacer un seguimiento de las relaciones entre las celdas puede convertirse en un desafío en las hojas de cálculo donde demasiadas celdas se refieren entre sí. Ahí es donde la función Auditoría de fórmulas de Excel puede resultar útil. Puede ayudarlo a obtener una mejor visualización de sus fórmulas.

  1. Seleccione una celda que contenga una fórmula.
  2. Desde la cinta, vaya a la pestaña Fórmula .
  3. En la sección Auditoría de fórmulas , haga clic en Rastrear precedentes .
  4. Todas las celdas que afectan los valores de la celda seleccionada se conectarán entre sí con flechas azules.
  5. A continuación, en la misma sección, haga clic en Rastrear dependientes . (Este método dibujará flechas azules desde la celda seleccionada a cada una de las celdas a las que afecta).
  6. Finalmente, para ver las fórmulas en lugar de los valores de todas las celdas, haga clic en Mostrar fórmulas .

Romper el círculo

Excel usa referencias circulares cuando intenta calcular el resultado de una celda que ha sido visitada más de una vez durante la ronda de cálculo. Entonces, no te ayudará todo el tiempo.

Sin embargo, muchos modelos financieros contienen referencias circulares. Pero deben evitarse siempre que sea posible. Ahora que sabe cómo cuidar las referencias circulares, podría ser el momento adecuado para aprender funciones más avanzadas de Excel.