Excel Intermedio

Un blog para compartir los tips de Excel Intermedio que todos necesitamos!

Referencias circulares en Excel, 14 150x150

Referencias circulares en Excel


Referencias circulares en Excel, 14 300x183

Una referencia circular ocurre en Excel cuando:

Al momento de ingresar una formula, esta contiene una referencia de celda a la celda que contiene la fórmula misma. Un ejemplo de este tipo de referencia circular se muestra en la imagen de arriba donde la fórmula en la celda C1 contiene una referencia a esa celda en la fórmula: = A1+A2+A3+C1

Una fórmula hace referencia a otra, que finalmente hace referencia a la celda que contiene la fórmula original.

Se ha elaborado un ejemplo de este tipo de referencia indirecta, tal como se conoce se muestra en el segundo ejemplo en la imagen donde las flechas verdes unen las celdas A7, B7 y B9, estas indican que las fórmulas de las celdas se referencian entre sí.

Advertencia de referencia circular

Según la imagen de arriba, si se produce una referencia circular en una hoja de cálculo de Excel, el programa debe mostrar un cuadro de diálogo de Alerta en donde indica el problema.

La alerta, el mensaje en el cuadro de diálogo está redactado específicamente porque no todas las referencias circulares en las fórmulas son involuntarias, como se describe a continuación.

"Cuidado, encontramos una o más referencias circulares en su libro de trabajo que pueden hacer que su fórmula se calcule incorrectamente"

Opciones de usuario

Las opciones de usuario al momento que aparece este cuadro de diálogo, es hacer clic en Aceptar o Ayuda, ninguno de ellos solucionará el problema de referencia circular.

Referencias circulares involuntarias

En cuanto a una referencia circular involuntaria, la información del archivo de ayuda le mostrará cómo encontrar y eliminar referencias circulares.

El archivo de ayuda lo dirigirá a utilizar la herramienta de comprobación de errores de Excel ubicada en FórmulasAuditoria de fórmulas en la cinta.

Existen varias referencias de células no intencionales que se pueden corregir sin necesidad de verificar errores, simplemente corrigiendo las referencias de celda utilizadas en la fórmula. En lugar de escribir referencias de celda en una fórmula, se puede usar señalar haciendo clic en referencias de celda con el mouse para ingresar referencias en una fórmula.

Referencias circulares intencionales

La referencia circular de Excel no necesariamente ofrece una solución para un problema de referencia circular ya que no todas las referencias circulares son errores.

Si bien es cierto que las referencias circulares intencionales son menos comunes que las involuntarias, estas pueden ser usadas para que Excel itere o ejecute una fórmula varias veces antes de producir un resultado.

Al momento de leer el mensaje puede parecer extenso y algo confuso en el cuadro de diálogo, descubrirá que:

hacer clic en Ayuda está destinado a ser utilizado cuando se produce una referencia circular no intencional; esto lo lleva a la información del archivo de ayuda de Excel sobre referencias circulares.

Si desea darle clic en OK, esto servirá para indicarle a Excel que la referencia circular se hizo intencionalmente y que debería dejarla en su lugar.

Habilitar cálculos iterativos

Excel tiene una opción para habilitar estos cálculos iterativos si planea usarlos.

Para habilitar cálculos iterativos:

Hacer clic en la pestaña Archivo (o en el botón de Office en Excel 2007)

Hacer clic en Opciones para abrir el cuadro de diálogo Opciones de Excel

Y luego hacer clic en el panel izquierdo del cuadro de diálogo, clic en Fórmulas

En el panel derecho del cuadro de diálogo, seleccionar la casilla de verificación Habilitar el cálculo iterativo

Debajo de las opciones de casillas de verificación están disponibles para:

Establecer el número máximo de iteraciones: La cantidad de veces que Excel debe volver a calcular la fórmula

Establecer la cantidad máxima de cambio aceptable entre los resultados del cálculo: cuanto menor es el número, más preciso es el resultado

Mostrando ceros en las celdas afectadas

Para las celdas que contienen referencias circulares, Excel muestra un cero como aparece en la celda C1 en el ejemplo o el último valor calculado en la celda.

En diversos casos, las fórmulas se pueden ejecutar con éxito antes de intentar calcular el valor de la referencia de celda donde están ubicadas. Cuando esto pasa, la celda que contiene la fórmula muestra el valor del último cálculo exitoso.

Más sobre la advertencia de referencia circular

Luego de la primera instancia de una fórmula que contiene una referencia circular en un libro de trabajo, Excel no mostrará necesariamente el mensaje de advertencia nuevamente. Depende de las circunstancias de cómo y dónde se crean las referencias circulares adicionales.

Los ejemplos de cuándo se mostrará el cuadro de alerta que contiene el mensaje de advertencia para referencias circulares posteriores incluyen:

La primera instancia de una referencia circular en cualquier libro de trabajo abierto.

Después de eliminar todas las referencias circulares en todos los libros abiertos, se crea una nueva referencia circular.

Después de cerrar todos los libros de trabajo, se crea una fórmula que contiene una referencia circular en un nuevo libro de trabajo.

Se abre un libro de trabajo que contiene una referencia circular.