miércoles, 6 de junio de 2012

Excel: Habilitar cálculo iterativo vs Referencia Circular.

Hablaré hoy de un concepto poco conocido de Excel, como es el Habilitar cálculo iterativo y cómo podemos aprovecharnos de él.
Para entender cuál puede ser el beneficio de activar esta opcíon debemos entender primero qué son las Referencias circulares, que seguro en más de una ocasión hemos sufrido. Estas referencias circulares aparcen cuando al trabajar en nuestras Libros u Hojas de Excel incluimos una celda como argumento de una función que necesita la primera para llegar a un resultado; es decir, cuando hay fórmulas que se refieren a otras y estas dependen unas de otras; por ejemplo, si la celda A1 contiene la formula "=B1" y la celda B1 contiene la formula "=A1". Estas referencias circulares nos devuelven un error muy típico que condiciona el resto de resultados de la Hoja de cálculo (incluso del Libro) sobre el que trabajamos.
No se hace necesario para el fin de esta entrada comentar más al respecto.


Nosotros trabajaremos sobre una Hoja donde hemos forzado una Referencia Circular, necesaria para nuestro cálculo final. En nuestro ejercicio hemos resumido en una pequeña cuenta de resultados, Ingresos menos costes fijos, variables y un margen industrial, que irá en función del resultado o beneficio final; es decir, un coste incluido como parte del Beneficio pero que se calcula precisamente a partir del Resultado final. Una referencia circular muy clara.
Podemos verlo en la imagen:

Excel: Habilitar cálculo iterativo vs Referencia Circular.


De alguna forma tendremos que 'romper' ese círculo; y ahí es donde entra la opción de Habilitar cálculo iterativo. Para ello iremos al menú Archivo > Opciones > Fórmulas > Opciones de cálculo > Habilitar cálculo iterativo:

Excel: Habilitar cálculo iterativo vs Referencia Circular.


Sabiendo que las Iteraciones máximas define el número de veces que Excel actualiza los cálculos.
Para definir el número máximo de cambio que se aceptará entre los resultados de los nuevos cálculos, escribimos la cantidad en el cuadro Cambio máximo.
Cuanto mayor sea el número de iteraciones, más tiempo necesitará Excel para calcular una hoja de cálculo; y cuanto menor sea el número, más preciso será el resultado y más tiempo necesitará Excel para volver a calcular una hoja de cálculo.

Una vez marcada la opción de Excel 'Habilitar cálculo iterativo', Excel permitirá el cálculo tantas veces como le hayamos indicado (100 en nuestro ejemplo) hasta alcanzar un resultado con la precisión definida; es decir, de manera similar a como hubiera actuado Buscar objetivo o Solver.
Al volver a la Hoja de cálculo podemos ver el resultado conseguido:

Excel: Habilitar cálculo iterativo vs Referencia Circular.


Donde vemos como en la celda C5 se ha llegado al valor esperado, esto es, el 15% del resultado final (incluido dicho valor en el cálculo!!!).

Concluimos entonces dando una definición de iteración como el cálculo repetido de una hoja de cálculo hasta que se cumple una condición numérica específica. Ojo, por que esta iteración (activada mediante la opción Habilitar cálculo iterativo) puede tener un efecto significativo en el rendimiento de nuestra Hoja de cálculo. Es por tanto importante que a menos que seamos usuarios con cierta experiencia en Excel familiarizados con estos cálculos iterativos, mantengamos las referencias circulares intactas, esto es, si queremos conservar la referencia circular, habilitaremos los cálculos iterativos, pero deberemos determinar la cantidad de veces máxima que se calculará nuestra fórmula.

17 comentarios:

  1. Si realizo 3 pagos por las cantidades de $6666.66 $2405.71, $9042.48 con un total pagado de $18,114.85, ¿ como podria distribuir este importe a mis adeudos si estos son fijos durante varios meses ? por ejemplo de los adeudos siguientes: $33,333.38 $963.67 $5,192.14 $6,942.14 $51,490.72 $20,737.93
    $12,353.99. Cada una de estos tiene una cantidad mensual fija a pagar por lo que a cada una le corresponde los siguientes importes:$6,666.66 $160.61 $741.73 $991.72 $5,149.07 $1,885.26 $1,123.09

    En Excel ¿hay una forma de distribuir lo pagado y saber los nuevos saldos, respecto del adeudo original de cada uno de ellos?

    ResponderEliminar
    Respuestas
    1. Hola José Armando,
      entiendo que en una celda A1 tienes, por ejemplo el valor inicial de 33.333,33$, y que conoces unas condiciones de devolución para esta cantidad de 5 meses, a razón de 6.666,66$ por mes.
      Y necesitas, en la misma celda, conocer el valor restante después de haber devuelto esa catidad cada mes.
      Una solución, sin emplear macros, sería disponer de la cantidad devuelta cada mes (6666,66) en una celda, por ejempl B1, copiarla cada mes y Pegarla encima de A1, con Pegado especial > Operación > Resta. Así conseguirías el neto resultante en A1.

      Espero haberte entendido bien.
      Slds

      Eliminar
  2. Buenos días

    Por favor tengo un problema con un archivo en excel que es una plantilla para introducir una variedad de datos texto y numéricos y al abrir el archivo me dice que se encuentran unas referencias circulares y que si quiero o no actualizar, entonces al actualizar o no este me calcula en las celdas donde están datos de fecha y lo deja en un numero total "calculando el valor" pero yo no quiero y tampoco me deja cambiarlo ni siquiera activando o desactivando el calculo iterativo. me pueden ayudar, me voy a enloquecer.

    ResponderEliminar
    Respuestas
    1. Hola Lissa,
      el problema de las referencias circulares es complicado de salvar, salvo eliminando dichas referencias:
      http://excelforo.blogspot.com.es/2012/12/localizar-referencias-circulares-en.html
      Inténtalo y me dices.
      Slds cordiales

      Eliminar
  3. HOLA!! TENGO UNA DUDA SOBRE LAS REFERENCIAS CIRCULARES, LO UNICO QUE QUIERO ES COGER EL NÚMERO RESULTANTE DE LA CELDA A1(B1-B2) PARA GENERAL OTRA FORMULA QUE SERIA( B21+C2) COMO LO HAGO??? LOS DATOS QUE ME DAN AL CALCULARLAS NO ME COINCIDEN CON LO QUE DEBERIA SER....

    ResponderEliminar
    Respuestas
    1. Hola,
      no veo por ningún lado la referencia circular, si en la celda A1 incluyes la fórmula =B1-B2
      y en otra celda (no indicas cuál) insertas otra fórmula
      =B21+C2
      no hay relación entre ellas, y por tanto no puede haber ref circular.

      Revisa el resto de la hoja por si existiera otra Ref Circular diferente.. ya que cuando existe alguna Ref Cir es posible los cálculos de toda la hoja fallen...
      Slds
      P.D.: Por favor, eviata escribir en mayúsculas... es como si me gritaras ;-)

      Eliminar
  4. excel es genial, es lo mejor que me ha pasado en la vida.

    ResponderEliminar
    Respuestas
    1. ;-)
      pues ciertamente tiene bastantes recursos disponibles
      Saludos

      Eliminar
  5. Hola, buenos dias.
    Sobre las referencias circulares. .
    Marco en opciones / formulas y en numero dejo tal cual viene por defecto.
    Me ocurre que cuando abro excel en otro momento se desmarca la casilla.
    Que puedo hacer para que quede marcada la casilla para siempre??

    Un saludo y muchas gracias.
    Juan Ojeda

    ResponderEliminar
    Respuestas
    1. Hola Juan,
      no hay que hacer nada especial, salvo aceptar.
      Los cambios realizados en las Opciones de Excel se producen sobre la aplicación, esto es, sobre cualquier libro de Excel que abras...
      Saludos

      Eliminar
    2. Todo funciona bien, pero cuando cierro y abro la hoja unas cuantas veces se desconfiguran las opciones y tengo que volver a imponer el cálculo iterativo. ¿Existe alguna solución?

      Eliminar
    3. Hola que tal estás?
      un placer saludarte igualmente.
      Los cambios realizados en las Opciones de Excel se producen sobre la aplicación, esto es, sobre cualquier libro de Excel que abras...
      es decir, una vez cambiado esta opción se debe mantener en el tiempo hasta nuevo cambio.
      Saludos

      Eliminar
  6. Hola!
    A ver si todavía sigues respondiendo dudas. Tengo que resolver una ecuación en modo iterativo. Lo que pasa es que me un error de división entre cero. En una celda tengo la fórmula y que es dividida por la siguiente. Esa siguiente celda la referencio a la anterior. Por ejemplo, tenemos la celda B1 con la fórmula y ésta está dividida por C1, y la C1 es igual a la B1. Es decir Celda B1 = fórmula/C1 Celda C1=B1.
    He visto en algunas excel que solucionan este problema, pero yo por más que intento no puedo.
    Espero puedas ayudarme. Gracias!!!

    ResponderEliminar
    Respuestas
    1. Hola,
      con sinceridad no veo el sentido de la fórmula y del planteamiento, y creo que ese es el problema..
      En todo caso, prueba primero poniendo la fórmula en B1, y en C1 el vínculo:
      B1:=fórmula
      C1:=B1
      así no tendrás ningún fallo.
      A continuación edita B1 y añade el cociente
      B1:=fórmula/C1
      Así evitarás el error de DIV0... otra cosa es que obtengas algo con sentido
      Saludos

      Eliminar
    2. La fórmula tiene muchísimo sentido, porque me permite calcular la longitud de onda y es ese el valor que me da el problema. La fórmula es esta.
      L = g*T^2/(2pi)*tanh(2pi*h/L)
      El resto de los valores los tengo, a excepción de L

      Intentaré tu solución y te cuento.

      Gracias por tu respuesta!!!!

      Eliminar
  7. habilitar la iteracion no me funciona !!!

    ResponderEliminar

Nota: solo los miembros de este blog pueden publicar comentarios.