Traducir el blog

Mi bingo iterativo

🔝To translate this blog post to your language, select it in the top left Google box.


Este bingo se me ocurrió mientras estaba diseñando los bingos de los dos artículos anteriores.

Ninguno de los bingos anteriores funciona ya, debido a que Microsoft se ha esmerado en corregir demasiado pronto el excelente BUG que permitió, durante unos meses, que las funciones volátiles dejaran de ser volátiles, tanto en Excel para Microsoft 365 como en Excel para la Web.

Por lo que he tenido que recurrir al viejo truco de usar el cálculo iterativo para diseñar mi bingo iterativo, y poder jugar con las nuevas actualizaciones del canal actual de Excel para Microsoft 365 y en Excel para la Web.


Mi bingo iterativo

Este es el aspecto de mi bingo iterativo en Excel para Microsoft 365.


Este juego de bingo iterativo en Excel contiene 3 hojas:

  • BINGO: Es la hoja donde realmente se juega al bingo.
  • BOLAS: Es la hoja con las 90 bolas de un bingo, ordenadas aleatoriamente.
  • CARTONES: Es la hoja auxiliar donde se calculan los cartones y los premios del bingo.


Juega al bingo iterativo en la nube

Si aún no tienes una suscripción de pago a Microsoft 365, puedes probar gratuitamente mi bingo iterativo en la nube de Microsoft OneDrive.

Para marcar las casillas hay un desplegable para elegir entre VERDADERO (casilla marcada) y FALSO (casilla desmarcada).


Para ajustar el zoom en la nube:

  • En el PC sitúa dentro el cursor y presiona la tecla <Control> mientras giras la ruleta del ratón.
  • En el móvil o celular usa dos dedos en la pantalla, como haces para ampliar o reducir una foto.


Descarga mi bingo iterativo

Descarga este juego de bingo, probado en Excel para Microsoft 365, desde este enlace:

Abre el archivo y presiona el botón: Habilitar edición cuando aparezca el aviso de VISTA PROTEGIDA.

Las hojas están protegidas sin contraseña para que sea fácil analizar las fórmulas.

ATENCIÓN: Se puede modificar este libro de Excel respetando esta licencia:

Creative Commons — Atribución-NoComercial-CompartirIgual-No portada — CC BY-NC-SA 4.0


Instrucciones del bingo iterativo

1) Marcar las casillas del cartón o de los cartones que quieras jugar

2) Marcar la casilla para Extraer las bolas

3) Extraer las bolas presionando:

  • En Excel para la Web con el botón de abajo: Actualizar todas las conexiones de datos
  • En Excel para Microsoft 365 con la tecla F9 repetidamente

4) Cantar línea, dos líneas o ¡Bingo!

5) Comenzar un nuevo juego, desmarcando la casilla: Extraer


Cálculo iterativo del bingo

Para que no existan referencias circulares en las fórmulas de algunas celdas, que se llaman a sí mismas, hace falta tener habilitado el cálculo iterativo.

Aunque por defecto ya está habilitado el cálculo iterativo en el libro con este juego del bingo, no está de más saber cómo cambiar las opciones de cálculo.

Entrar en el menú: Archivo

Abajo hay un botón: Más...

con más opciones.

Seleccionar: Opciones

Y se abre la ventana: Opciones de Excel

Seleccionar: Fórmulas

En Opciones de cálculo:

Cálculo del libro: Automático

Está marcado: Habilitar cálculo iterativo

Iteraciones máximas: 1

Cambio máximo: 1

Con lo que cada recálculo de fórmulas iterativas se hará una vez y se cambiará en una unidad.


Nombres definidos en el bingo

En el menú: Fórmulas > Administrador de nombres, hay 5 nombres definidos:

BolaExtraer: =BINGO!$S$2

BolaNúmero: =BINGO!$Q$2

BolaValor: =BINGO!$P$6

misBolas: =SI(BolaNúmero=0;"";BOLAS!$A$2:INDICE(BOLAS!$A$2#;BolaNúmero))

misCartones: =CARTONES!$AB$6:$AJ$23



Hoja BOLAS

La hoja 'BOLAS' solamente contiene una fórmula matricial iterativa en la celda A2:

=SI(BolaExtraer;$A$2#;ORDENARPOR(SECUENCIA(90);MATRIZALEAT(90)))

Si el valor de la celda BolaExtraer es:

  • FALSO, obtiene una nueva matriz dinámica desbordada de 90 números de bola aleatorios distintos, del 1 al 90. Son los números que se van extrayendo durante el juego del bingo.


Hoja CARTONES

La hoja 'CARTONES' genera los números de bolas de 6 cartones, que es un límite que me he impuesto por necesidades didácticas, y que se puede ampliar si se tiene un nivel intermedio de Excel.

En el bingo de 90 bolas, cada uno de los cartones tiene 3 líneas de 5 bolas cada una, con 4 espacios sin números en cada fila.

AVISO: En cualquiera de los bingos que he diseñado las bolas no se repiten en ninguno de los 6 cartones generados en cada juego.


Los números de bola mínimos de cada columna de cada cartón, están escritos en el rango B5:J5.

Los números de bola aleatorios de cada columna de cada cartón, están en el rango B6:J6 con unas fórmulas iterativas matriciales dinámicas, que se desbordan en las siguientes filas. Por ejemplo, para generar números aleatorios únicos del 1 al 9, la fórmula de la celda B6 es:

=SI(BolaExtraer;B$6#;ORDENARPOR(SECUENCIA(9;1;B$5);MATRIZALEAT(9)))

Para cambiar aleatoriamente el orden de los cartones en cada juego, se hace con la fórmula en la celda M6:

=SI(BolaExtraer;$M$6#;ORDENARPOR(SECUENCIA(6);MATRIZALEAT(6)))

Las filas de cada cartón se calculan en el rango N6:N11 con la fórmula en N6 arrastrada hacia abajo:

=3*(M6-1)

Los patrones de los 6 cartones, con un 1 si hay número de bola, y un 0 si no hay número de bola, están escritos en el rango Q6:Y23

Los 6 cartones con sus números de bola se generan en el rango AB6:AJ23. Por ejemplo, la primera columna de cada cartón se genera con la fórmula matricial desbordada de la celda AB6:

=SI(Q6:Q23=0;"";INDICE(B$6:B$16;Q6:Q23))

Para saber cuando se extrae la bola con la que cantar los premios, en el rango AS5:ED5 hay una secuencia del 1 al 90, con las 90 extracciones posibles de todas las bolas.

Para saber cuantos números han salido en cada fila de cada cartón, se copia en el rango AS6:ED23 la siguiente fórmula, con la que se obtiene un valor entre 0 y 5 aciertos:

=SUMA(SI(SI.ERROR(COINCIDIR($AB6:$AJ6;BOLAS!$A$2:INDICE(BOLAS!$A$2#;AS$5);0);0)>0;1;0))

Para saber el número de bola que haría cantar ¡LÍNEA!, en cada fila de cada cartón, se hace arrastrando hacia abajo la fórmula de AN6, en el rango AN6:AN23:

=COINCIDIR(5;$AS6:$ED6;0)

Para saber el número de bola que haría cantar ¡DOS LÍNEAS! en cada cartón, se copia en cada 3 filas del rango AO6:AO23, la siguiente fórmula de AO6:

=SUMA($AN6:$AN8)-MAX($AN6:$AN8)-MIN($AN6:$AN8)

Para saber el número de bola que haría cantar ¡BINGO! en cada cartón, se copia en cada 3 filas del rango AP6:AP23, la siguiente fórmula de AP6:

=MAX($AN6:$AN8)

Los números de extracción con alguno de los 3 premios se calculan en el rango AN5:AP5, con la fórmula de AN5 copiada hacia la derecha:

=MIN(AN6:AN23)

Los números de bola con alguno de los 3 premios se calculan en el rango AN4:AP4, con la fórmula de AN4 copiada hacia la derecha:

=INDICE(BOLAS!$A$2#;AN5)

Los números de fila de la matriz de cartones con alguno de los 3 premios se calculan en el rango AN3:AP3, con la fórmula de AN3 copiada hacia la derecha:

=MIN(SI(misCartones=AN4;FILA(misCartones)))

Los números de cartón que han sido premiados se calculan en el rango AN2:AP2, con la fórmula de AN2 copiada hacia la derecha:

=COINCIDIRX(AN3-6;$N$6:$N$11;-1;1)

La información del rango AN2:AP5 se mostrará en la hoja 'BINGO' según se vayan extrayendo las bolas...


Hoja BINGO

A este bingo iterativo se juega en la hoja 'BINGO'.

Con cálculo iterativo se obtiene la siguiente bola extraída en la celda Q2, con la fórmula:

=SI(BolaExtraer;MIN(CARTONES!$AP$5;BolaNúmero+1);0)

Siempre que sea VERDADERO la celda S2 con el nombre definido: BolaExtraer

Si ese nombre es FALSO, la celda S2 se pone a 0 y genera un nuevo juego de bolas y de cartones.

En el rango B3:J12 están las 90 bolas que, con un formato condicional, cambian de color si es la bola recién extraída o ya ha sido extraída. La fórmula matricial en B3 es:

=TRANSPONER(SECUENCIA(9;10))

Se indican las 3 bolas previas extraídas en el rango L5:L7, con la fórmula matricial en L5:

=SI(BolaNúmero<2;"";INDICE(misBolas;SI(BolaNúmero>3;BolaNúmero-3;SI(BolaNúmero>2;BolaNúmero-2;1))):INDICE(misBolas;SI(BolaNúmero>1;BolaNúmero-1;1)))

El último número de bola extraído se muestra en la celda combinada P6:R8, con la fórmula:

=SI(BolaNúmero=0;0;INDICE(BOLAS!$A$2#;BolaNúmero))

Los 3 premios aparecen en las siguientes celdas con sus fórmulas:

L13: =SI(BolaNúmero>=Q13;"¡LÍNEA!";"")

L14: =SI(BolaNúmero>=Q14;"¡DOS LÍNEAS!";"")

L15: =SI(BolaNúmero>=Q15;"¡BINGO!";"")

En la fila 16 hay 3 secuencias auxiliares del 1 al 9 para pintar los 6 cartones.

En la columna A hay 2 secuencias auxiliares del 1 al 3 para pintar los 6 cartones.

En 6 celdas concretas se indica qué cartón se pintará de los 6 cartones posibles:

A17: =CARTONES!$N$6

L17: =CARTONES!$N$7

W17: =CARTONES!$N$8

A22: =CARTONES!$N$9

L22: =CARTONES!$N$10

W22: =CARTONES!$N$11

Los números de bolas del cartón 1 se obtienen copiando en el rango B18:J20 la fórmula de la celda B18:

=INDICE(misCartones;$A$17+$A18;B$16)

Los números de bolas del cartón 2 se obtienen copiando en el rango M18:U20 la fórmula de la celda M18:

=INDICE(misCartones;$L$17+$A18;M$16)

Los números de bolas del cartón 3 se obtienen copiando en el rango X18:AF20 la fórmula de la celda X18:

=INDICE(misCartones;$W$17+$A18;X$16)

Los números de bolas del cartón 4 se obtienen copiando en el rango B23:J25 la fórmula de la celda B23:

=INDICE(misCartones;$A$22+$A23;B$16)

Los números de bolas del cartón 5 se obtienen copiando en el rango M23:U25 la fórmula de la celda M23:

=INDICE(misCartones;$L$22+$A23;M$16)

Los números de bolas del cartón 6 se obtienen copiando en el rango X23:AF25 la fórmula de la celda X23:

=INDICE(misCartones;$W$22+$A23;X$16)

¡Y eso es todo amigos!

¡Felices vacaciones de verano!

¡Hasta septiembre!

Mi bingo en la nube

🔝To translate this blog post to your language, select it in the top left Google box.


ATENCIÓN: En Excel para la Web ¡ya está corregido el BUG!, que permitía jugar nuevas partidas de bingo con funciones volátiles que dejaban de serlo gracias al BUG, por lo que este artículo queda obsoleto en su mayor parte...


AVISO: Pero al final de este artículo encontrarás un bingo programado con cálculo iterativo, que funciona en la nube ¡con el BUG corregido!


Para quien no tenga una suscripción de pago a Excel para Microsoft 365, aún puede probar mi juego de bingo volátil en la nube de Microsoft OneDrive.



¡Pruébalo un poco más abajo!

¡AHORA!

¡Antes de que deje de funcionar el BUG de Excel, en el que se basa este juego!

Lee sobre el BUG aquí:


Prueba el bingo en la nube ¡mientras puedas!

Juega en la nube aquí mismo, aunque de momento no tengo resuelto cómo comenzar un nuevo juego de bingo.

¡Siempre se juegan las mismas bolas y cartones en este bingo!

Para marcar las casillas hay un desplegable para elegir entre VERDADERO (casilla marcada) y FALSO (casilla desmarcada).

TRUCO: Mantén presionada la tecla F9 y extraerás las bolas a toda velocidad.


Para ajustar el zoom en la nube:

  • En el PC sitúa dentro el cursor y presiona la tecla <Control> mientras giras la ruleta del ratón.
  • En el móvil o celular usa dos dedos en la pantalla, como haces para ampliar o reducir una foto.


Prueba el bingo en el móvil o celular

En el móvil o celular no hay tecla F9 para extraer las bolas.

El siguiente procedimiento para extraer las bolas sirve tanto para el móvil o celular como para cualquier navegador de un PC:

1) Selecciona una celda cualquiera de la hoja 'BINGO'.

2) Presiona el botón del menú de abajo, indicado con una flecha en color rojo en la imagen de la derecha. Cuando pasas el ratón por encima de ese botón muestra el texto: "Actualizar todas las conexiones de datos"

3) Extraerás la siguiente bola cada vez que pulses ese botón del menú, hasta que el primer cartón sea premiado con el BINGO, por contener todas las bolas extraídas hasta el momento.


Cuando salga este mensaje de aviso, ciérralo haciendo clic en la cruz de arriba a la derecha.


Juega al bingo iterativo en la nube

Juega en la nube a mi bingo iterativo aquí mismo.

Para marcar las casillas hay un desplegable para elegir entre VERDADERO (casilla marcada) y FALSO (casilla desmarcada).


Para ajustar el zoom en la nube:

  • En el PC sitúa dentro el cursor y presiona la tecla <Control> mientras giras la ruleta del ratón.
  • En el móvil o celular usa dos dedos en la pantalla, como haces para ampliar o reducir una foto.


En el siguiente artículo explico cómo he hecho este bingo iterativo:

Gracias por seguirme...

Mi bingo volátil en Excel

🔝To translate this blog post to your language, select it in the top left Google box.


ATENCIÓN: En la última actualización de Excel para Microsoft 365 ¡ya está corregido el BUG que permitía jugar al bingo usando funciones volátiles!, por lo que este artículo queda obsoleto...


En verano me apetece estar con los amigos en la piscina o en la playa, por lo que bajo el ritmo de mis retos en Excel, aunque nunca me viene mal programar un nuevo juego, ¡sobre todo si se basa en un BUG del propio Excel!



La idea se me ocurrió mientras estaba escribiendo este artículo:

Y maduré la idea mientras escribía la continuación de dicho artículo:


Es un juego volátil

Como este juego del bingo está basado en un BUG de Excel para Microsoft 365, solamente funcionará mientras Microsoft no lo corrija, por lo que es un juego muy volátil. Hoy puede funcionar ¡y mañana no!

Ya me han avisado mis amigos del foro que en la próxima actualización dejará de funcionar... Por lo que hay prisa para publicarlo ¡y poder jugar a este bingo tan volátil!

En el enlace de arriba tienes la discusión en el foro sobre este BUG.


Mi bingo volátil

El juego es una variante del bingo con 90 bolas. Lee sus reglas aquí:

Este es el aspecto del Bingo antes de comenzar a jugar.



INSTRUCCIONES:

1) Marcar las casillas de los cartones a jugar.

2) Seleccionar el control de número en V6

3) Presionar la tecla de cursor hacia arriba.

4) Cantar línea, dos líneas o bingo de tu cartón.

5) Repetir la partida con las teclas: Control + Fin

6) Comenzar una nueva partida presionando las teclas: Control + Fin, la tecla Esc y las teclas: Control + Alt + F9


Descarga este bingo volátil ¡AHORA!

Descarga este juego de bingo, probado en Excel para Microsoft 365 (versión 2506 compilación 16.0.18925.20158), desde este enlace:

Abre el archivo y presiona el botón: Habilitar edición cuando aparezca el aviso de VISTA PROTEGIDA.

Las hojas no están protegidas para que sea fácil analizar las fórmulas.

ATENCIÓN: Se puede modificar este libro de Excel respetando esta licencia:

Creative Commons — Atribución-NoComercial-CompartirIgual-No portada — CC BY-NC-SA 4.0


Mi vídeo volátil del bingo

En este vídeo explico cómo jugar volátilmente al bingo.


Juega a este bingo ¡mientras puedas!

¡¡¡ Disfruta del juego de este verano en Excel !!!

¡¡¡ Descárgalo ahora, pues en unos días dejará de funcionar con la próxima actualización de Excel para Microsoft 365 !!!

Cómo conservarnos como ahora - Parte 2

🔝To translate this blog post to your language, select it in the top left Google box.


Esta es la continuación del anterior artículo que trataba de

Es mejor que lo leas AHORA antes de comenzar a leer éste.

En el artículo anterior comenté que la idea de conservar el AHORA me vino por un vídeo de Albert Cid, pero yo quería saber de dónde procedía la idea original y se lo pregunté directamente.

Este es el enlace al artículo en inglés que Albert, muy amablemente, me comunicó:

El que escribió esto fue Owen Price, un Excel MVP norteamericano, aunque todo el crédito de la idea de esta publicación corresponde a Lorimer Miller.

Lo primero que nos cuenta es para avisarnos que:

Un hombre de dentro me ha informado con fiabilidad que la capacidad descrita a continuación, por muy tentadora que parezca, ¡es en realidad el efecto secundario de un error! Y como muchos errores, pronto será aplastado. Aplanado. Desaparecido. ¡Así que estén advertidos!

Y yo digo que:

Mientras exista este efecto colateral indeseado, ¿por qué no usarlo a nuestro favor?

¡Y puede ser que este bug dure mucho tiempo, pues Microsoft suele tardar en resolver sus errores!

En esta imagen animada muestro la diferencia palpable entre usar una función volátil y usar el truco que permite conservar ahora las funciones volátiles:

La función volátil de la columna B cambia con solo pulsar la tecla F9, lo que no ocurre con el truco para conservar los valores de la función volátil de la columna C.


Nuevas fórmulas para conservarnos como ahora

El truco consiste en aprovechar el bug, que hace que las funciones volátiles se comporten como no volátiles, si se llaman en fórmulas como éstas:

=(@AHORA)()

=SI($B2;(@AHORA)();"")

Estas dos fórmulas ya las vimos en el artículo anterior, pero ahora vamos a ver nuevas fórmulas en las que actúa este truco, sacadas de un comentario del artículo de Owen Price.

NUEVO TRUCO: Las fórmulas no tienen por qué incorporar el operador @. Parece ser que cualquier cosa que permita tomar una forma eta-reducida, y convertirla en la abstracción de la función LAMBDA, realiza el truco de la desvolatilización.

Aquí hay ejemplos::

=LAMBDA(AHORA)()()

=SI(1=1;AHORA)()

=ELEGIR(1;AHORA)()

=LAMBDA(f; f)(AHORA)()

=LET(f; AHORA; f())

=AHORA_Fijo()

La fórmula final es la llamada a un nombre definido en el Administrador de nombres:

AHORA_Fijo: =AHORA

En este nombre definido la función AHORA() no están escritos los paréntesis () pues esta función no tiene argumentos.


En un par de fórmulas aparece la función LAMBDA, lo que induce a pensar que algo tiene que ver con este truco.

Observa que en el resto de fórmula aparece la función AHORA() sin los paréntesis (). que es una forma de simplificar la sintaxis de LAMBDA cuando simplemente pasamos el argumento a una función sin más operaciones. Se puede leer sobre esta nueva sintaxis en el siguiente enlace: 

Me quedo con la posibilidad de crear nombres definidos con la nueva sintaxis de LAMBDA: "eta-reducida" o "eta lambda".

En esta imagen muestro la hoja 'AHORA', con las fórmulas que generan el truco comentado, y que puedes descargar como ejemplo al final de este artículo.


Cómo conservar ahora números aleatorios

Las funciones con números aleatorios son volátiles en Excel. ¡Hasta AHORA!

En la hoja 'ALEATORIO' he escrito otra fórmula con una función volátil que conserva el valor de forma no volátil.

En la celda B2 he escrito la fórmula volátil:

=ORDENARPOR(SECUENCIA($A$1);MATRIZALEAT($A$1))

En la celda C2 he escrito la fórmula no volátil:

=ORDENARPOR(SECUENCIA($A$1);MATRIZALEATORIA_Fija($A$1))

Que se basa en el nombre definido: MATRIZALEATORIA_Fija =MATRIZALEAT

Es una forma de llamar a la función LAMBDA con la nueva sintaxis "eta-reducida".

Estas fórmulas devuelven valores únicos de manera aleatoria, como explica la IA:

En la siguiente imagen he capturado un momento con estas fórmulas, que calculan un número determinado de números distintos de manera aleatoria, con la función MATRIZALEAT:

La tecla F9 modifica solo la fórmula de la columna B, sin cambiar los valores de la columna C.

Al editar la celda A1 se recalculan las dos fórmulas, obteniendo dos listas de números aleatorios distintos.

Convertir la función volátil MATRIZALEAT en una función no volátil permite desarrollar juegos de azar usando únicamente fórmulas, sin necesidad de cálculo iterativo de las fórmulas y sin tener que programar macros en lenguaje VBA.


Cómo dejar de conservarnos como ahora

El truco para convertir una función volátil en no volátil funciona incluso si se presionan las teclas:

  • F9: que recalcula las fórmulas que Excel considera “pendientes” en todo el libro activo ahora, si está desactivado el cálculo automático.
  • Mayús + F9: que recalcula solamente la hoja activa ahora, si está desactivado el cálculo automático.

El truco deja de funcionar, perdiendo los valores anteriores y actualizando todos los valores ahora como funciones volátiles, en cualquiera de estos casos:

  • Control + Alt + F9: que fuerza un recálculo completo de todas las fórmulas en todo el libro.
  • Control + Alt + Mayús + F9: que reconstruye el árbol de dependencias de todas las fórmulas y, a continuación, calcula cada celda de todos los libros abiertos, incluidas aquellas que no estaban marcadas como pendientes de recálculo.
  • Al modificar la tabla con el truco, si se insertan o borran filas o columnas intermedias.
  • Al insertar o borrar una fila o columna de la hoja con las fórmulas del truco.

El último caso obliga a aislar cuidadosamente las fórmulas en hojas aparte, para que no se desvolatilice este truco.


Vídeo no volátil

En el vídeo explico las fórmulas con las que conseguir que pierdan la volatilidad algunas funciones volátiles.


Descarga los ejemplos ¡AHORA!

Descarga ahora los ejemplos de cómo conservar las funciones AHORA y MATRIZALEAT, probados en Excel para Microsoft 365, desde este enlace:

Abre el archivo y presiona el botón: Habilitar edición cuando aparezca el aviso de VISTA PROTEGIDA.

Las hojas no están protegidas para que sea fácil analizar las fórmulas.

ATENCIÓN: Se puede modificar este libro de Excel respetando esta licencia:

Creative Commons — Atribución-NoComercial-CompartirIgual-No portada — CC BY-NC-SA 4.0


Cómo jugar con fórmulas volátiles

Los próximos días programaré un juego de azar.

¡Usando solamente fórmulas volátiles y no volátiles!

Cómo conservarnos como ahora - Parte 1

🔝To translate this blog post to your language, select it in the top left Google box.


La vida es un viaje de aprendizaje continuo.

¡El mejor momento de actuar es ahora!

Caminante, no hay camino,

se hace camino al andar.

Antonio Machado (1912)


Cómo conservarnos como ahora

Para conservar el efímero ahora solemos hacer fotos o vídeos con la cámara, intentando capturar el presente, pero es una labor imposible, ya que son fotos o vídeos del pasado al cabo de unos segundos después de tomarlas. Lo mismo pasa con nuestro propio cuerpo, que no se conserva nunca como ahora, ni siquiera a nivel celular, pues las células se regeneran continuamente...

Pregunta lanzada a Microsoft Copilot:

Esa es una pregunta profunda, Pedro. Si te refieres a conservarnos tal como somos ahora —ya sea física, emocional o espiritualmente—, hay algo poético en querer atrapar un momento en el tiempo, ¿no?

A nivel personal, podríamos hablar de hábitos saludables: cuidar el cuerpo con ejercicio y buena alimentación, cultivar la mente con aprendizaje constante, y mantener relaciones significativas. Pero también está la parte intangible: la actitud, la curiosidad, la manera de ver la vida. Eso es lo que realmente nos mantiene “como ahora”, aunque el tiempo siga pasando.

¿Lo estás pensando desde un deseo de inmortalizar algo bonito que estás viviendo? Porque también podría ser eso: aprender a conservar el recuerdo, el espíritu del momento, sin necesidad de que todo se quede congelado. A veces, lo mejor que podemos hacer es atesorarlo y permitir que nos transforme suavemente.

¿Quieres que lo exploremos desde el punto de vista emocional, filosófico o más práctico?

¡¡¡ Más práctico !!!


El ahora es volátil

Cada vez que hacemos referencia al presente, se transforma en el ahora, perdiendo el valor del pasado.

Hasta ahora no se podía guardar la función AHORA() en Excel, pues es una función volátil.

Pero va a haber un antes y un después, pues desde ahora se pueden guardar marcas de tiempo no volátiles con la función AHORA(), sin tener que pegar el tiempo devuelto por la función, sin necesidad de usar macros VBA e incluso sin usar el viejo truco de conservar el ahora con la opción de cálculo iterativo.

Explicación de las funciones volátiles como AHORA():


Cómo congelar el ahora

Estos son ejemplos de capturas del tiempo devuelto por la función AHORA() dentro de una fórmula no volátil:

He creado 3 tablas, dos de ellas con marcas de tiempo:

  • En la hoja 'AHORA' hay una tabla de actividades, con un desplegable de actividades. Al seleccionar una actividad queda guardada la marca de tiempo, con lo que puedo conseguir los timestamps de todas las actividades que registre.
  • En la hoja 'MARCAS DE TIEMPO' hay una tabla con mi lista de deseos y una columna de casillas. Al marcar una casilla queda guardada la marca de tiempo, con lo que puedo saber cuándo he conseguido satisfacer cada uno de mis deseos.
  • La hoja 'Actividades' contiene una tabla personalizable de actividades.


Cómo congelar el ahora hasta ahora

Hasta ahora hay varias maneras de congelar la función AHORA(), que puedes leer aquí:

En este vídeo se explica cómo crear marcas de tiempo usando el cálculo iterativo:

Era la forma habitual de crear marcas de tiempo, sin usar macros VBA, que yo usaba antes de conocer el siguiente truco hecho sólo con fórmulas.


Fórmula para conservar el ahora

La siguiente fórmula es volátil, por lo que Excel la recalcula cuando recalcula la hoja o si se pulsa la tecla F9, cambiando su valor al momento del recálculo:

=AHORA()

TRUCO: La siguiente fórmula hace que la función AHORA() se recalcule una sola vez, aunque no lo ponga en la documentación de la función, dejando de ser volátil y devolviendo un valor estático fijo, que no cambia con el tiempo, por lo que sirve como marca de tiempo (timestamp en inglés):

=(@AHORA)()

Esta es la función usada en la celda B2 de la tabla de la hoja 'AHORA' del archivo de ejemplo que puedes descargar más adelante.


La siguiente función está escrita en la celda C2 de la tabla de la hoja 'MARCAS DE TIEMPO':

=SI($B2;(@AHORA)();"")

Cuando se marca una casilla de la columna B, se calcula la marca de tiempo de la columna C.

TRUCO: Lo que no se puede hacer es usar esta fórmula:

=SI([@Marca];(@AHORA)();"")

Pues añadiendo la referencia estructurada a la columna de la tabla se recalcula siempre el ahora...


Trucos para no recalcular ahora

Como no he encontrado ninguna documentación en el Soporte de Microsoft que explique cómo es que las fórmulas anteriores no recalculan la función AHORA(), le he preguntado a Microsoft Copilot:

Contestación de Microsoft Copilot:

La función AHORA() es volátil por definición; es decir, Excel la recalcula en cada ciclo de recálculo de la hoja para actualizar la fecha y hora actuales. Sin embargo, al escribir la fórmula de esta forma:

=(@AHORA)()

estamos usando dos “trucos” de la nueva sintaxis de Excel:

1. El operador de intersección implícita @: Esto indica a Excel que, aunque la función podría devolver un array, se debe tratar únicamente como un valor escalar. Si bien esto, por sí solo, no cambia la volatilidad, ayuda a que Excel interprete la fórmula dentro de un contexto de función que espera un único valor.

2. La invocación mediante paréntesis adicionales (()) al final: Al agregar estos paréntesis, Excel no trata simplemente a AHORA() como una llamada directa a una función volátil, sino que la evalúa como si fuera una función que está “envuelta” en una especie de función lambda implícita. Este cambio en la forma de invocación provoca que Excel capture el resultado en el momento de la llamada y no marque la celda como dependiente de una función volátil. En esencia, se “rompe” la conexión directa que existiría con el recálculo constante, y el motor de Excel entiende que el resultado ya no requiere actualizarse automáticamente en cada cambio de la hoja.

Esta optimización se alinea con algunas de las nuevas capacidades y mecanismos de evaluación introducidos en Excel recientemente (por ejemplo, con las funciones lambda y la gestión de arrays dinámicos). Aunque en teoría la función original es volátil, esta “envoltura” sintáctica hace que su resultado se trate de forma estática—al menos hasta que se fuerce un recálculo o cambie una dependencia. Es una estrategia interesante para reducir recálculos innecesarios en hojas muy complejas.

¿Te interesa profundizar en cómo Excel está incorporando estas nuevas estructuras tipo lambda o quizás en otras funciones optimizadas para mejorar el rendimiento?

La clave aquí es que AHORA no es la función nativa de Excel, sino un Nombre definido que internamente guarda un LAMBDA, como explica la IA de Microsoft en este enlace:

Por tanto, =(@AHORA)() significa literalmente:

  • Toma el objeto función AHORA con intersección implícita
  • Ejecuta esa función sin argumentos
  • Devuelve la hora actual


Descarga AHORA

Descarga un par de ejemplos de cómo conservar el AHORA, probados en Excel para Microsoft 365, desde este enlace:

Abre el archivo y presiona el botón: Habilitar edición cuando aparezca el aviso de VISTA PROTEGIDA.

Las hojas no están protegidas para que sea fácil analizar las fórmulas.

ATENCIÓN: Se puede modificar este libro de Excel respetando esta licencia:

Creative Commons — Atribución-NoComercial-CompartirIgual-No portada — CC BY-NC-SA 4.0


Idea de ahora

Este artículo se me ocurrió cuando ví este vídeo corto de Albert Cid, MVP en la categoría de M365 Apps & Services, que lo grabó cuando un amigo suyo le recomendó un artículo con una nueva idea para que la función AHORA() sea estática.


De momento me resisto a dejar de ser como ahora, dedicado a mis aficiones en Excel, pero tengo mucha presión para que cambie, y para que ayude en casa. Sobre todo desde que me autojubilé.

Si me animas a seguir como hasta ahora, me ayudarás a resistir a las presiones familiares, que no comprenden mi vocación y no entienden mi estilo de vida.

Y si me lo pides, hasta grabaré a partir de ahora un vídeo explicando cómo he hecho el archivo para conservar el AHORA.


Mira ahora este vídeo


Te agradezco que seas uno de los quinientos mil visitanes de mi blog, y me hará ilusión saber que aquí has aprendido algo nuevo de Excel.

Si compartes AHORA este blog con tus contactos, igual no hace falta esperar otros 15 años para llegar al millón de visitantes.

Gráficos retro con colores difusos

🔝To translate this blog post to your language, select it in the top left Google box.


¿A quien no le gustan los juegos retro, los juegos clásicos de toda la vida?

¡A mí sí!

Este artículo está escrito para versiones de Excel retro, de Excel 2010 en adelante.

¡Porque no todo el mundo tiene la última versión de Excel instalada en su escritorio!

Aquí aprenderás cómo convertir unos gráficos, creados con las versiones más recientes de Excel, para que sean compatibles con versiones de Excel clásicas, antiguas o retro.


Incompatibilidad entre Excel recientes vs retro

Se presupone la compatibilidad de versiones Excel antiguas cuando se ejecutan en versiones Excel más recientes pero, un Excel creado con versiones recientes de Excel no tiene por qué funcionar en versiones antiguas de Excel, debido a que hay funciones que no son retro compatibles...

En los últimos 15 años de la historia de Microsoft Excel para Windows se han liberado 6 versiones de Excel de escritorio, desde Excel 2010 hasta Excel 2024, además de Excel para Microsoft 365 que es un servicio de suscripción, y de Excel para la Web que es una versión gratuita que corre en navegadores Web.

En todas esa versiones de Excel funcionan los gráficos de barras, los gráficos de columnas y los gráficos de dispersión por lo que, en un principio, se pueden visualizar los gráficos de aranceles con colores difusos, que publiqué en el artículo anterior de este blog:

Léelo para comprender cómo se hicieron estos gráficos, y quienes tuvieron las brillantes ideas originales para hacer estos gráficos difusos, tan impactantes visualmente, con una única serie definida para el gráfico de barras o de columnas.

En los últimos 15 años me ha tocado lidiar con problemas de incompatibilidad entre versiones de Excel, pues he tenido que compatibilizar versiones antiguas, por ejemplo Excel 2003, con versiones modernas como Excel 2016, para que una plantilla determinada corriera en cualquiera de esas versiones de Excel.

En este caso el ejercicio de compatibilidad consiste en que:

Unos gráficos creados en versiones modernas de Excel, sean visualizados en versiones antiguas de Excel.

¡Y lo he conseguido!

Como evidencias observa esta imagen animada de un par de pantallazos visualizados en Excel 2010 de los gráficos de aranceles con colores difusos:

Una vez que conseguí crear gráficos compatibles en versiones antiguas de Excel, me quedaba un problema por resolver: Cómo mostrar las etiquetas con los nombres de los países.

En una de las imágenes de arriba. creada con Excel 2010, no se ven las etiquetas. en la otra sí se ven.

Si sigues leyendo te enterarás cómo he resuelto el problema de mostrar las etiquetas mediante dos procedimientos totalmente distintos...


Funciones de Excel más recientes vs retro

Para estos gráficos, el problema de incompatibilidad entre versiones de Excel es debido al origen de datos de los gráficos, obtenido mediante fórmulas construidas con las funciones de las versiones más recientes de Excel, que generan error en versiones de Excel antiguas, en las que esas funciones no existen.

En las versiones más recientes de Excel, la fórmula de la celda AUX!A2 son los valores del eje horizontal del gráfico de columnas y los valores del eje vertical del gráfico de barras, con el nombre definido: filasEje

Esta fórmula se basa en las siguientes funciones, que son compatibles con las versiones de Excel descritas a continuación:

LET: Se aplica a Excel para Microsoft 365; Excel para la Web; Excel 2024; Excel 2021.

SECUENCIA: Se aplica a Excel para Microsoft 365; Excel para la Web; Excel 2024; Excel 2021.

EXCLUIR: Se aplica a Excel para Microsoft 365; Excel para la Web.

REDUCE: Se aplica a Excel para Microsoft 365; Excel para la Web.

APILARV: Se aplica a Excel para Microsoft 365; Excel para la Web.

LAMBDA: Se aplica a Excel para Microsoft 365; Excel para la Web; Excel 2024.

Por lo que una fórmula basada en todas estas funciones únicamente es compatible con dos versiones: Excel para Microsoft 365 y Excel para la Web, que son las que son compatibles con todas las funciones más recientes de Excel.

En cualquier otra versión anterior de Excel dicha fórmula generará errores de valor: #¡VALOR!, o de que la función no existe: #¿NOMBRE?, pues son retro incompatibles en versiones antiguas.

Incluso los gráficos difusos son incompatibles con la última versión de escritorio, que es Excel 2024, lo que nos obliga a pagar por una suscripción mensual o anual a Microsoft 365, para poder crearlos y visualizarlos. Aunque también podemos verlos en Excel para la Web, como hice en el artículo anterior, insertándolos en la nube de Microsoft OneDrive.


Reto: Usar funciones retro compatibles

Como me gustó muchísimo el gráfico propuesto por Roberto Mensa en este artículo en inglés:

Desde que lo publicó me propuse el reto de hacerlo compatible con versiones retro antiguas de Excel, a partir de Excel 2010, lo que me ha obligado a modificar las fórmulas que generan el origen de datos de los gráficos, eliminando cualquier función incompatible con las versiones no tan recientes de Excel.

Una vez analizada la fórmula que genera el origen de datos del gráfico, sustituí las funciones LET, LAMBDA, etc., que no existen en Excel 2010, modificando la fórmula en AUX!A2 así:

Esta es una fórmula matricial, en la que no hay que escribir los corchetes {}, que aparecen automáticamente pulsando a la vez la combinación de teclas: Control + Mayús + Intro. En inglés se conocen como fórmulas matriciales CSE, al introducirlas con las teclas: Ctrl + Shift + Enter.

Para saber más sobre las diferencias entre las nuevas fórmulas de matriz dinámica vs las fórmulas de matriz heredadas, lee este artículo:

En la hoja 'AUX' están escritas las fórmulas retro matriciales necesarias para crear el origen de datos de los gráficos en versiones retro de Excel.


Nombres definidos retro compatibles

En este pantallazo del Administrador de nombre están todos los nombres definidos para hacer que los gráficos sean retro compatibles:


Con la fórmula matricial comentada anteriormente se genera el nombre definido usado como rango de rótulos del eje en los gráficos.

filasEje: Devuelve un rango de valores mayor que cero en el rango AUX!$A:$A, comenzando por la celda A2.

=AUX!$A$2:INDICE(AUX!$A:$A;CONTAR.SI(AUX!$A:$A;">0")+1)

Dicha fórmula matricial de la columna A, usa 3 nombres definidos:

secFilas: Devuelve una secuencia de números enteros, del 1 al número de filas de la tabla de aranceles.

=FILA(AUX!$A$1:INDICE(AUX!$A:$A;nFilasSubtotales+SI(nFilasSubtotales=nFilasTotales;1;0)))

filasRepeticiones: Devuelve una secuencia de números enteros, del 1 a la suma de la secuencia de repeticiones.

=FILA(AUX!$A$1:INDICE(AUX!$A:$A;SUMA(secRepeticiones)))

secRepeticiones: Devuelve un rango de valores mayor que cero en el rango AUX!$K$2:$K$31

=AUX!$K$2:INDICE(AUX!$K$2:$K$31;CONTAR.SI(AUX!$K$2:$K$31;">0"))

En el rango AUX!$K$2:$K$31 hay una fórmula matricial con el número de repeticiones según una escala de los valores del filtro de aranceles:

{=COINCIDIR(filasFiltro2;secEscala;1)}

filasFiltro2: Nombre definido para obtener los valores filtrados de la tabla de aranceles en el rango AUX!$G$2:$G$31

=AUX!$G$2:INDICE(AUX!$G$2:$G$31;nFilasSubtotales+SI(SI.ERROR(COINCIDIR(-1%;AUX!$G$2:$G$31;0);0)>0;0;1))

filasFiltro1: Se escribe como fórmula matricial en el rango AUX!$G$2:$G$31 con los aranceles visibles filtrados de la lista de aranceles de la tabla.

=SI.ERROR(INDICE(listaAranceles; K.ESIMO.MENOR(SI(listaVisible=""; FILA(listaAranceles)-6); FILA(listaAranceles)-6));-1%)

secEscala: La escala de colores de corte según el valor de los aranceles y el valor de difusión de los colores.

=(FILA(AUX!$A$1:INDICE(AUX!$A:$A;nColores))-1)/nColores*nMax

siendo nMax: =MAX(filasFiltro2) 

nColores: El valor de difusión de los colores de 5 a 25.

='Aranceles difusos'!$H$3

En el rango AUX!$B$2:$B$501 hay una fórmula matricial, con la que se obtienen los valores de aranceles de cada fila del eje. Este método es mucho más simple y efectivo que en la versión original con fórmulas llamando a funciones recientes, usando únicamente la conocida función INDICE:

{=INDICE(filasFiltro2;filasEje)}

filasValores: Son los valores de la serie, tanto en el gráfico de barras como en el de columnas, con los aranceles repetidos según la escala de colores difusos:

=AUX!$B$2:INDICE(AUX!$B:$B;CONTAR.SI(AUX!$A:$A;">0")+1)

Para las etiquetas con los nombres de los países, que se visualizan en una serie del tipo gráfico de dispersión, se usan dos rangos con fórmulas matriciales:

Fórmula matricial en el rango AUX!$D$2:$D$31

{=FILA($A$1:INDICE($A:$A;nFilasSubtotales))}

EtiquetaX: Valores de la serie en el gráfico de dispersión.

=AUX!$D$2:INDICE(AUX!$D$2:$D$31;nFilasSubtotales)

Fórmula matricial en el rango AUX!$D$2:$D$31

{=EtiquetaX*0}

EtiquetaY: Valores de la serie en el gráfico de dispersión.

=AUX!$E$2:INDICE(AUX!$E$2:$E$31;nFilasSubtotales)


¡¡¡ RETO CONSEGUIDO !!!

¡Todas las funciones usadas en la fórmula de arriba son compatibles en Excel 2010 y versiones posteriores!

¡Y se obtienen los mismos resultados que con las fórmulas escritas en la versión más reciente de Excel, creadas por Roberto Mensa!

¡Se pueden comprobar los resultados en el rango de columnas AUX!M:P, siempre que se abra el archivo en Excel para Microsoft 365!


Retroalimentación de Copilot

Como no era nada sencilla la tarea de convertir las fórmulas de matriz dinámica en fórmulas matriciales heredadas, le pedí ayuda a la IA de Microsoft Copilot, pues pienso que no hay que ser retrógrado y quedarse obsoleto, sino usar los asistentes personales expertos en Excel, como es la IA que, si se le pregunta, responderá retroalimentando y mejorando nuestras capacidades de formulación y programación. Eso sí, analizando la respuesta de la IA con sentido crítico.

Estas son las consultas que le hice a Copilot en modo pensamiento profundo (Think Deeper):

Las fórmulas generadas por Copilot usan funciones volátiles, como INDIRECTO, que prefiero no usar porque se recalculan siempre. En todas las fórmulas he usado funciones no volátiles...

Por ejemplo, para el nombre definido: filasRepeticiones, la fórmula volátil sería:

=FILA(INDIRECTO("1:" & SUMA(secRepeticiones)))

Por lo que la he convertido a una fórmula no volátil con la función INDICE: 

=FILA(AUX!$A$1:INDICE(AUX!$A:$A;SUMA(secRepeticiones)))

RECOMENDACIÓN: Es fundamental mantener una actitud crítica frente a las respuestas de la IA, lo que implica tener un conocimiento profundo de las funciones de Excel, así como probar y validar las fórmulas que sugiera la IA antes de darlas por buenas.


Etiquetas retro incompatibles

ATENCIÓN: Las etiquetas con los nombres de los países, generadas con los gráficos de dispersión no son visibles en Excel 2010.

En versiones posteriores de Excel se permite seleccionar un rango de datos cualquiera.


A partir de versiones de Excel 2013, se obtienen las etiquetas según el Valor de las celdas seleccionadas con el botón: Seleccionar rango... 

Por ejemplo, se seleccionan los nombres de los países en el rango de la tabla de aranceles:

='Aranceles difusos'!$B$7:$B$25

Pero en Excel 2010 genera error cuando trata de visualizar estas etiquetas:

Siempre muestra como etiqueta el error: [CELLRANGE], por lo que hay que buscar soluciones alternativas a este problema, aunque solamente ocurra este problema en Excel 2010.

A continuación propongo dos workarounds o soluciones alternativas.


Solución con etiquetas en un gráfico superpuesto

Esta solución para Excel 2010 pasa por:

  • Ocultar las etiquetas con errores en los gráficos difusos de barras y columnas.
  • Crear gráficos nítidos de barras y de columnas, ocultando todo excepto las etiquetas.
  • Agrupar los gráficos difusos y nítidos superpuestos para ver las etiquetas nítidas y las barras y columnas difusas.

Este tipo de soluciones son fáciles de implementar si no se puede crear el gráfico combinado de nuestros sueños, por lo que no es la solución definitiva para los gráficos de aranceles difusos en Excel 2010.


Solución con etiquetas en un gráfico combinado

Esta es la mejor solución para Excel 2010, pues se puede hacer con un gráfico combinado:

  • Eliminar la serie del tipo gráfico de dispersión que mostraba las etiquetas.
  • Crear una nueva serie con un gráfico nítido de barras o de columnas agrupadas.
  • Combinar los dos gráficos, colocando en el eje secundario la nueva serie.
  • En el gráfico nítido del eje secundario ocultar todo excepto las etiquetas.
  • Esta solución no precisa datos para las etiquetas, por lo que hay menos nombres definidos implicados.

Este el resultado en Excel 2010 con cualquiera de las dos retro soluciones alternativas propuestas:


Descargas retro difusas

Descarga los gráficos de aranceles con colores difusos, compatibles desde Excel 2010, desde estos enlaces:

Abre el archivo y presiona el botón: Habilitar edición cuando aparezca el aviso de VISTA PROTEGIDA.

Las hojas están protegidas sin contraseña para que sea fácil analizar las fórmulas y los gráficos.

ATENCIÓN: Se puede modificar este libro de Excel respetando esta licencia:

Creative Commons — Atribución-NoComercial-CompartirIgual-No portada — CC BY-NC-SA 4.0


Vídeo retro difuso

En este nuevo vídeo explico cómo hice los gráficos retro con colores difusos.


Sígueme si te ha parecido interesante el contenido de este artículo, y me animarás a seguir publicando más retos diseñados en Excel.

Mi lista de blogs