La referencia circular en Excel

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010Truco compatible con Excel 2007

La referencia circular en Excel se produce cuando en una celda insertamos una fórmula de la que calculamos un valor y tiene como referencia la propia celda, directa o indirectamente. Al no poder leer el valor y modificarlo a la vez, se muestra el error, entrando en un bucle.

Microsoft Excel no puede calcular automáticamente todos los libros abiertos cuando uno de ellos contiene una referencia circular.

Deseamos realizar la suma de las celdas A1, A2 y A3. Para ello simularemos el error en los argumentos de la función SUMA.

 AB
1100 
250 
325 
  1. Ir a: Celda A1 /Escribir: 100. O cualquier valor numérico.

  2. Ir a: Celda A2 / Escribir: 50. O cualquier valor numérico.

  3. Ir a: Celda A3 / Escribir: 25. O cualquier valor numérico.

  4. SIMULAMOS EL ERROR. Ir a: Celda A4 / Escribir:

    =SUMA(A2:A4)

    Indicamos el rango de A2:A4, pero A4 es la celda del resultado.

    Celda A4 con función =SUMA(A1:A4), A4 para resultado y como valor de rango

  5. Pulsar la tecla Enter. Verificar como se muestra el mensaje de advertencia de referencia circular.

    Mensaje de advertencia de la referencia circular en Excel

  6. Pulsar el botón Aceptar. Para confirmar el mensaje.

  7. Verificar como muestra la celda A4 el valor 0 y en la barra de fórmula muestra la función insertada.

  • COMPROBAR REFERENCIAS CIRCULARES EN LA HOJA

Si una hoja Excel incluye una Referencia Circular podemos identificarla por dos métodos

  1. Ir a: Cinta de opciones / Pestaña: Fórmulas / Grupo: Auditoría de fórmula / Comprobación de errores / Referencias circulares. Mostrará cuadro con las referencias existentes.

    Cinta de opciones, pestaña fórmulas - Referncias circulares

  2. Clic sobre el cuadro de la referencia. Nos llevará a la celda que incluye la referencia circular.

  3. Ir a la Barra de estado de la ventana Excel (inferior izquierda). Verificar como muestra la leyenda de Referencias circulares: A4.

    Barra de estado con leyenda de Referencias circulares: A4

  • PERMITIR Y USAR LA REFERENCIA CIRCULAR

Para ciertos cálculos, nos puede interesar que Excel calcule cada celda de dicha referencia utilizando los resultados de la iteración anterior.

Si no se cambia el valor predeterminado de la iteración, Excel detendrá los cálculos tras 100 iteraciones o después de que todos los valores en l referencia circular cambien menos de 0,001 entre iteraciones, independiente de cuál sea la primera.

  1. Según la versión de Excel:

    – EXCEL 2007. Ir a: Botón de Office / Botón: Opciones de Excel. Mostrará ventana de opciones.

    – EXCEL 2010/2013/2016. Ir a cinta de opciones / Pestaña: Archivo / Opciones. Mostrará ventana de opciones.

  2. Clic sobre la categoría: Fórmulas.

    Ventana de opciones Excel con Habilitar cálculo iterativo

  3. Ir a: Grupo Opciones de cálculo / Activar la casilla: Habilitar cálculo iterativo. Cuando se activa esta opción, se calculan las fórmulas iterativas (denominadas también referencias circulares). Si no especifica lo contrario, Excel se detendrá tras realizar 100 iteraciones o cuando el cambio de todos los valores sea inferior a 0,001.

  4. Ir a: Iteraciones máximas / Insertar el valor deseado. Para cambiar los valores de número máximo de iteraciones utilizadas y cantidad máxima de cambio por debajo de los cuales se detiene la iteración.

  5. Pulsar el botón Aceptar. Cierra la ventana de opciones de Excel.

 

Nivel de dificultad: Avanzado Excel Avanzado

 

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *