martes, 11 de octubre de 2011

Referencias circulares en Excel

Si en una celda ponemos una fórmula que haga referencia a la propia celda, Excel mostrará un mensaje indicando que se ha producido una referencia circular. Por ejemplo, si en B1 ponemos =2*B1, nos mostrará el mensaje:

Esto nos indica que hemos podido cometer un error. Quizás quisimos poner =2*A1

Sin embargo, las referencias circulares no son necesariamente errores; podemos usarlas para resolver problemas. Veamos un ejemplo.

Queremos calcular los beneficios obtenidos en una operación en la que hemos ingresado 1.000 € y hemos gastado 700. El 10% de los beneficios netos lo vamos a destinar a obras benéficas. Pongamos estos datos en una hoja de cálculo.

En C4:
=10%*C5

En C5:
=C2-C3-C4

Excel nos responde con la advertencia de referencia circular y señala con flechas azules las celdas implicadas.

Las fórmulas son correctas, pero el cálculo es imposible. Para obtener el valor en C4 es necesario tener previamente el valor de C5; pero para obtener el valor de C5 es preciso conocer el de C4. Como se observa, hay una referencia circular.

Comencemos eliminando las flechas. Para ello, en el grupo Auditoría de fórmulas de la pestaña Fórmulas, seleccionamos Quitar flechas.

Borramos las fórmulas de las celdas C4 y C5.

El siguiente paso consiste en habilitar el cálculo iterativo. Abrimos el menú Archivo y elegimos Opciones. En el apartado Fórmulas, marcamos Habilitar cálculo iterativo, ponemos 1 en Iteraciones máximas y dejamos 0,001 en Cambio máximo. Terminamos pulsando Aceptar.

Volvemos a poner las fórmulas:

En C4:
=10%*C5

En C5:
=C2-C3-C4

Al poner la fórmula en C4 todavía no hay nada en C5, por lo que el 10% de nada es nada.
En C5 se resta a C2 (1.000 €) el valor de C3 (700 €) y de C4 (nada). Por tanto, se obtienen 300,00 €.

Pulsamos la tecla F9 para recalcular la hoja. Se obtienen los valores siguientes:

En C4 se obtienen 30,00 € (el 10% de los 300,00 € de C5).
En C5 se restan 1.000 € - 700 € - 30,00 € = 270,00 €

Volvemos a pulsar repetidamente la tecla F9 hasta que observemos que los valores de la hoja no cambian. Es el resultado final. Han sido necesarias menos de 10 pulsaciones.

Pero no vamos a pulsar reiteradamente F9; dejaremos que Excel haga el trabajo. Para ello, repetimos el ejercicio poniendo un número suficiente de iteraciones en el cuadro de diálogo Opciones de Excel. El número máximo de iteraciones es 32.767; a nosotros nos bastan 100.

El cálculo se detendrá cuando el siguiente recálculo provoque una diferencia menor de 0,001.

Ahora, surge la pregunta, ¿podría resolverse el ejercicio sin usar referencias circulares?
Se puede, y vamos a hacerlo.

Tenemos que encontrar la forma de relacionar las aportaciones a Obras Benéficas con los Ingresos y los Gastos, pero no con los Beneficios netos. De este modo, evitaremos las referencias circulares. Un poco de matemática elemental nos ayudará a conseguirlo.
Bastará borrar las fórmulas de las celdas C4 y C5, deshabilitar las referencias circulares y escribir las nuevas fórmulas:

En C4:
=(C2-C3)/11

En C5:
=C2-C3-C4

En la próxima entrada mostraré otro ejemplo del uso de las referencias circulares.

No hay comentarios:

Publicar un comentario