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%*C5En 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%*C5En 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)/11En C5:
=C2-C3-C4
En la próxima entrada mostraré otro ejemplo del uso de las referencias circulares.
y si necesito hacer lo siguiente
ResponderEliminara1=si(a1=".",1,0)
como lo ago ayuda
me sirvio, saludos !!
ResponderEliminarMuy buen ejemplo
ResponderEliminarHola Javier:
ResponderEliminarSiempre tuve un problema con Excel, cuando calculaba horas de trabajo para un trabajador que laboraba de un día para otro; en razón que estaríamos calculando horas y fechas diferentes. Así por ejemplo: si un trabajador ingresa a laborar a las 7:00 a.m. y sale al día siguiente a las 6:00 a.m. Aquí el problema se centra en:
1. El formato de la celda debe ser hh:mm:ss pero de estilo 30:00:00
2. Si ese es el formato, al ingresar en una celda el horario de entrada y el horario de salida, de restar los dos el cálculo no refleja lo que realmente sucede. (sale -01 hora).
3. Cómo solucionar este error para que no salga este cálculo incorrecto. Hace muchos años atrás envié un email a Microsoft U.S., y no me respondieron. Lo extraño fue que me mandaron un aviso que ya había salido Office 2007 (yo usaba Office 2003). Pero aún no tengo una solución a este problema.
Estaré muy agradecido por tu respuesta.
Gracias
quiero clcular el 10% de un total, pero este 10% debe sumarse a ese total, cómo lo hago?
ResponderEliminarel sub total es de 6.165.000 y en el total final debe sumarse el 10% de este subtotal
confuso?
ResponderEliminar