Cálculos iterativos y 3. Y esta es la redifinitiva

En los dos últimos artículos (aquí y aquí), los he dedicado al uso al uso de las iteraciones en Excel. También hemos visto cómo Excel, para ahorrar tiempo y ganar en velocidad, determina que cosas tiene que calcular y cuales no.

Pues nos queda hablar de un tema fundamental y es cómo y en qué orden calcula Excel. Dado que no puede calcular todas las celdas a la vez, y tiene que volver sobre sus pasos en mas de una ocasión. Excel a la hora de calcular una hoja de cálculo procede de la siguiente manera:

• Linea a línea de arriba a bajo
• En cada fila columna a columna y de izquierda a derecha.

Por supuesto, que cuando la fórmula y/o la celda en que se encuentra realizando el cálculo, si así lo exige, Excel ira a buscar las celdas precedente y vinculadas, complicándose su tarea.

Esto se explica muy bien con un modelo en el que cada fórmula sólo dependa de la fila de abajo y/o de la celda de la izquierda, se calculará mas rápido que un modelo que no respete esta fórmula.

Volviendo a nuestro último modelo (en este modelo teníamos activadas las opciones de calculo manual y el número de iteraciones era de 10):

¿Qué ocurre cuando Excel inicia el cálculo de la iteración #10?

Veamoselo repasando línea a línea el proceso. Cuando se inicia el cálculo de la iteración # 10 y encara el calculo de la línea # 6, toda la parte del modelo colocado por debajo de esa linea y a la derecha, es decir, todas las líneas 7-12, están  todavía en la condición y valores de la iteración # 9. Por lo tanto el contador B7 está en 9, y no en 10. Por ello, el porcentaje de F6 - F12  se calcula dividiendo los valores de la columna E (E6:E11) por 9.

Cuando Excel realice el cálculo de la línea #7, el contador de iteraciones B7 aumentará a 10 y el porcentaje de F6 a F11 serán correctos.

Luego pasa a realizar el calculo de la fila #8, y entonces B8 cambia a 2,  pero las filas #6 y #7 ya han sido calculadas,  ya fue comprobada el condicional "=SI(--$B$7=0;0;E8+SI($B$8=D8;1;0))", porque la prueba E6 para ver si habían salido un 2 ya se ha calculado, y por ello se ignora. Imagina que juegas a los dados y que nunca sale  un 1, un 2 o 3, ya que en estos casos, la prueba del condicional E6, E7 o E8  se calcula con la iteración anterior. ¡Los Soprano!.

El remedio es muy simple, basta con mover el bloque de celdas D5..F12, de forma que todos los cálculos se realicen de arriba a bajo y de izquierda a derecha, tomando siempre y en cada momento los valores correctos. Esta opción al modelo la puedes ver en la hoja "Arreglo" del modelo "Cálculos iterativos y 3.xlsm"

Esto es lo que se habría obtenido si los sorteos anteriores no hubieran estado amañados.

Ojo, lo importante de la Excel es que calcule bien, no que quede bonita ni el diseño, eso viene después. En los cáculos iterativos ten cuidadado donde pones la ojo, ten presente la forma de trabajar de Excel.

Tener activado el calculo automático y no el manual crea el espejismo de que Excel ha realizado algún calculo bien, pero eso no te garantiza el correcto cálculo, puede que lo consigas para alguna fila o columna, pero la totalidad de las celdas, no se logra. El arreglo total es tener los datos de origen arriba y a la izquierda. 

Si te da mucha pereza cambiar la forma de cálculo de Excel de automático a manual y viceversa, para arrancar la simulación y reinicializar el modelos, puedes insertar una macro tal y como te propongo aquí:

Puedes descargarte el modelo "Cálculos iterativos y 3" en flahs foward, totalmente gratuito basta con que estés registrado, en la web. 

Cálculos iterativos en Excel para Mac.

Jugando a los dados con los Soprano.

Había dejado a Excel jugando a los dados con los Soprano, algo del ambiente de esa timba mafiosa le estaba afectando. En cuanto se lanzaban los dados, Excel se ponía nerviosos y empezaba a calcular mal. ¿Cómo? ¿Es qué Excel no sabe sumar o calcular unos porcentajes?, ¿qué le esta pasando?, ¿esta asustada por los Soprano?

Excel no se amilana por nada en el mundo, el problema es de cómo realiza los cálculos. 

La fórmula de la celda B8,  es la que se encarga de tirar los dados:  =SI(NO(B5);0;ENTERO(6*ALEATORIO()+1)).

Excel intenta para no perder tiempo, en un cálculo iterativo, no calcula las células que - según él – no son necesarias calcular, pues no han cambiado de valor.

Cuando se está en el comienzo de la iteración 2, el análisis de Excel se fórmula y  razona de la siguiente manera:

• -       B5 no ha cambiado desde la iteración # 1 ...
• -       El resto de la fórmula es independiente de cualquier célula, y por lo tanto no ha cambiado.
• -       Por consiguiente, no hay ninguna razón para volver a calcular la fórmula. A cascarla!, sigo con otra cosa.

El remedio es simple! Para que la tabla de frecuencias de resultados se calcule en cada iteración,  B8 no debe depender de B5, sino de B7, ya que esta celda si que cambia con cada iteración.

¿Y por qué no aparecían los valores de cada iteración y si todos en grupo?, pues por la misma razón.

Y aquí está la solución! Cambia las fórmulas:

B4

E5

Ahora cambie la fórmula en B8: =SI(B7=0;0;ENTERO(6*ALEATORIO()+1)).

Y en E6:E11: =SI(--$B$7=0;0;E6+SI($B$8=D6;1;0))

Reinicializa el  contador clicando aquí.  Y pulsa [F9] para recalcular el modelo.

Algo hemos avanzado. Ya aparecen los resultados de cada tirada, pero…

La suma de las frecuencias de los resultados no coincide con el total de tiradas, suman 109 cuando se ha tirado 110, y … los porcentajes están mal calculados.

Por lo que se ve la sombra de los Soprano sigue siendo larga. La solución redefinitiva en el siguiente post.

Reconversión MAC

Tal y como comentaba en el anterior post (aquí), intentaré hacer un resumen en cada post, de los pasos que me han costado dar en el cambio de Windows a Mac. Estos son los de esta semana:

1. El botón de programación de alternar, no existe o sigo sin encontrarlo, lo he cambiado, por el de lista, que viene a realizar una funcionalidad parecida.  Cada vez que clicamos, cambiamos el valor de la celda B5, por su valor contrario, de TRUE a FALSE o al revés.
2. Una costumbre que arrastro des de la época de Multiplan o del Lostus 1-2-3, es utilizar la tecla F9 para el recalculo, en principio con Excel-mac puedes utilizar “Cmd + =”, pero no se por qué razón no me funciona, debe ser el teclado?, he optado entre calcularlo en la cinta de opciones en la sección de “fórmulas> calculo”, o dejando fija la tecla [F9] para recalculo, mediante el método abreviado de teclado, que como entra en conflicto con la asignación de una tecla Exposé predeterminada en la versión 10.3 de Mac OS X y en versiones posteriores, hay que cambiarlo. Para usar este método abreviado de teclado de Office, primero debe desconectar el método abreviado de teclado Exposé para esta tecla. En el menú Apple, haga clic en Preferencias del sistema. En Personal, haga clic en Exposé y Spaces.En Método abreviado de teclado y mouse, en el menú emergente del método abreviado de teclado que desea desactivar, seleccione –. Calcular todas las hojas en todos los libros abiertos. Para acceder a todas las pantallas utilizaré el ratón.

Puedes descargarte el modelo en flasforward, aqui.

Excel para Mac. ¡Me voy de travesía!

Estos días pasados he recuperado el uso del mac en casa, y después de mucho tiempo, me he dado una vuelta por el office mac, hacia unos años que lo había visto la versión 2004, pero he estado mas centrado en el office de Windows y en el proyecto open office. Y cual ha sido mi sorpresa, al actualizar la versión a Office:mac 2011, me agrado de gran manera el interface, y las pantallas de Excel. En principio es idéntico, pero no igual, era igual pero no era lo mismo. Pintaba muy bien la versión Office: mac 2011.

Así que  me empezó a picar la curiosidad, de si era realmente igual el Excel para mac que el que utilizo normalmente para Windows.  En principio te zambulles a navegar por la hoja por los menús, y las cintas de opciones y te suena todo, todo lo reconoces, pero cuando buscas algo, la primera vez cuesta, y algunas veces te quedas atascado. Por eso, voy a empezar a focalizar los post de Excel de este blog,  en la versión de Excel para mac, de la misma forma que antes lo hacía para el Excel 2011.  La travesía del Windows al Mac, es mejor hacerla en compañía que solo, esta travesía necesita ayuda, y puede que alguien mas se encuentre en estos lances.

Soy consciente de que ya esta la versión de Office 2013, pero por ahora para Mac la versión 2011 es la mas actual, siempre y cuando no uses la nube. 

y tú.... ¿qué opinas? ..... deja un comentario....




Reconversión Mac

En cada post, si procede incluiré una sección  “Reconversión Mac”, donde reflejaré aquellos cambios, diferencias o aspectos entre Windows y Mac que más me hayan   afectado, costado o sorprendido.

Todos esos tropiezos y atascos en que incurres cuando te pasas de un programa a otro, de una versión a otra.

A las etiquetas que utilizo sobre estos temas: "hojas de calculo" y "excel", "open office" añadire la de "office:mac", y "excel para mac".

Y de seguido, el próximo post va de cálculos iterativos y de Los Soprano.

Lo último de Aleix Saló tiene muy buena pinta: "Europesadilla alguien se ha comido a la clase media"

Buscando material para la clase de mañana me he topado con este vídeo, que refleja una una visión del cambio de paradigma en el que nos encontramos, no es una crisis es otro mundo.
Corto, incisivo, pon todos los peros que quieras, pero es muy acertado, 4 minutos que se pasan rápido.

Prueba suerte, ¡ lanza los dados.! Simulación con excel (1).

 ¡ Lanza los dados.!

Siguiendo el hilo de los últimos post sobre el tema de los cálculos iterativos y referencias circulares que nos permite Excel, vamos a dar un paso más sobre este tema, para ello hemos construido un modelo simple que simula 10 lanzamientos de un dado. (Puedes descargarlo enwannanotes gratis).

Estas son las “tripas” del modelo:

B5 ;  Celda está vinculada al botón de “iteraciones”, tomará valores de TRUE/FALSE

B7 :  =SI(--B5=0;0;B7+1)

B8 :  =SI(NO(B5);"";ENTERO(6*ALEATORIO()+1))

E6 (copiado hasta E11):  =SI(--$B$5=0;0;E6+SI($B$8=D6;1;0))

F6 (copiado hasta F11):  =SI($E$12=0;0;E6/$E$12)

En principio como estaremos en el modo de “Recalculo Automático”, nos avisará de que se produce una referencia circular. No nos preocupemos, cambiemos el cálculo a Manual y a 10 iteraciones.

Si pulsamos el botón de “Iteraciones”, cambiaremos el valor de la celda b5, del que tenia a su contrario TRU-> FALSE o FALSE-> TRUE, y se encenderá el semáforo de la celda B7, aparecerá el dado e iniciamos y a continuación pulsamos [F9] para iniciar la simulación de 10 lanzamientos del dado.

KONTUZ!!! Peligro ¡!! El dado esta amañado, estamos jugando con un banquero, o un pijopogre, vas a salir traskilado.

Si fuese totalmente aleatorio todos los números tendrían la misma probabilidad, y no es así.

En el próximo artículo, vamos a descubrir dónde está el problema y cómo solucionarlo  Mientras puedes descargarte este modelo aquí, totalmente gratis. 

En wannanotes