Mapa del clima español

🔝To change the language of this blog post, choose it from the Google box at the top left.

No voy a hablar del clima político en España si no del clima meteorológico, partiendo de un mapa coroplético hecho en un artículo anterior, y que últimamente tiene muchas visitas:

Mapa de Provincias de España en Excel | #ExcelPedroWave

He diseñado este nuevo mapa con varios datos climáticos, para saber el tiempo que ha hecho en España en el transcurso del día de hoy y de días anteriores:

Si buscas la predicción del tiempo para los próximos días, no sigas leyendo y búscala en otros sitios.

Si buscas trucos para tratar datos históricos en Excel, sigue leyendo.

AVISO: Este artículo no va dirigido a la consulta de un mapa climático por usuarios inexpertos de Excel, sino al aprendizaje por parte de usuarios intermedios y avanzados de trucos y tratamientos que ayuden a mostrar cualquier tipo de dato en un mapa coroplético hecho en Excel.

Vídeo del mapa climático

En este vídeo explico cómo usar los dos archivos que puedes descargar más abajo.

Datos del mapa climático

Información elaborada utilizando, entre otras, la obtenida de la Agencia Estatal de Meteorología - AEMET y del Instituto Nacional de Estadística - INE.

El mapa del artículo anterior solamente incluía datos del INE: 

• Población (el 01/01/2022)
• Área (km²)
• Densidad (hab/km²)

Este mapa incluye además datos climáticos de temperatura, velocidad del viento y precipitaciones, elaborados utilizando datos de la AEMET:

• Máximas (°C)
• Mínimas máx. (°C)
• Mínimas (°C)
• Racha (km/h)
• Velocidad máxima (km/h)
• Precipitación (mm)

Los datos climáticos del mapa son los valores máximos por provincia del día elegido, entendiendo que son los valores mínimos de temperatura mínima.

He probado el mapa en la versión de escritorio de Excel para Microsoft 365 y debe funcionar también en Excel 2019 y Excel 2021, según indica esta página:

Crear un gráfico de mapa en Excel (microsoft.com)

En ese artículo ya expliqué cómo crear el mapa coroplético, por lo que no voy a volver a explicarlo, ni a incluir los datos extraídos del INE: población, área y densidad.

Durante el diseño de este mapa he aprendido trucos y tratamientos de datos que quiero compartir con mis lectores y seguidores, para que juntos podamos mejorar nuestros análisis de datos y nuestros conocimientos de Excel y Power Query.

Datos climáticos

La primera versión de este mapa incluye datos climáticos desde el 15 al 30 de junio, para demostrar su aplicación y para poder probarlo con el tiempo que hizo durante la ola de calor de junio.

Por cierto, mientras preparaba este mapa he contribuido, con mi granito de arena, en la redacción del siguiente artículo de Wikipedia:

Ola de calor en Europa de 2022 - Wikipedia, la enciclopedia libre

Como comencé a extraer los datos de la AEMET el 22 de junio, no conseguí extraer datos anteriores al 15 de junio, ya que solamente se pueden consultar los datos de hoy y de los 7 días anteriores desde esta página:

El Tiempo. Hoy y últimos días - Resúmenes diarios anteriores - Tabla - Agencia Estatal de Meteorología - AEMET. Gobierno de España

AVISO: He preferido escribir dos artículos, separando los datos climáticos del mapa propiamente dicho:

• Describiendo el mapa del clima en este primer artículo y
• Describiendo en un segundo artículo cómo extraer, transformar y cargar los datos climáticos, con la ayuda de Power Query.

Por lo que tienes que descargar dos plantillas distintas, una con el mapa que se verá más adelante, y otra con los datos elaborados.

Descarga de los datos climáticos

En primer lugar hay que descargar los datos climáticos de España, desde estos enlaces:

• Microsoft OneDrive: Consulta Tiempo AEMET - PW1.xlsx

• Sites Google Drive: Consulta Tiempo AEMET - PW1.xlsx

Abre la plantilla con una versión de Excel de escritorio a partir de Excel 2013 y presiona el botón: Habilitar edición

En un próximo artículo explicaré cómo conectar con los datos de la AEMET con Power Query, con lo que podrás consultar el clima acaecido en los días transcurridos en julio y en los próximos meses, siempre con datos de los 7 días anteriores o del tiempo transcurrido el día de hoy.

En este archivo hay una tabla denominada "HistorialTiempo", que contiene los datos históricos guardados con este formato:

Los campos de esta tabla son:

• Fecha: Con el día del registro de los datos climáticos.
• Provincia: Con el nombre de la provincia como aparece en el mapa.
• Provincia2: Con el nombre de la provincia como aparece en los datos de la AEMET.
• Valor climático: Valor máximo por provincia en un día de la temperatura máxima, mínima máxima, racha de viento, velocidad máxima y precipitación. Valor mínimo por provincia en un día de la temperatura mínima.
• Estación climática: Estación donde se han registrado los datos climáticos.
• Hora de registro del dato climático.

Descarga el mapa climático

En segundo lugar hay que descargar el mapa del clima en España, desde estos enlaces:

• Microsoft OneDrive: Mapa Clima España PW2.xlsx

• Sites Google Drive: Mapa Clima España PW2.xlsx

Abre la plantilla con una versión de Excel de escritorio a partir de Excel 2019 y presiona el botón: Habilitar edición

Aparece una ADVERTENCIA DE SEGURIDAD: Se han deshabilitado las conexiones de datos externos. Presiona el botón: Habilitar contenido

Esta plantilla no contiene macros, pero todas las hojas están protegidas sin contraseña, por lo que se pueden estudiar y analizar las fórmulas.

El mapa contiene datos de población, área y densidad de población aunque, por defecto, no contiene ningún dato climático. Para rellenar esos datos hay que seguir leyendo...

Cómo actualizar los datos climáticos

Para actualizar los datos climáticos se debe seleccionar la hoja 'Tiempo'.

Esta hoja contiene únicamente una tabla dinámica con cada uno de sus campos en la sección de las filas, aunque se muestran los campos en columnas, al haber elegido el diseño del informe en formato tabular.

El origen de datos de esta tabla dinámica es la tabla "HistorialTiempo", que contiene el archivo descargado en primer lugar: Consulta Tiempo AEMET - PW1.xlsx

ATENCIÓN: Para actualizar esta tabla dinámica deben estar abiertos tanto el archivo con los datos climáticos como el archivo con el mapa climático.

Pasos para actualizar los datos climáticos en el archivo descargado: Mapa Clima España PW1.xlsx

AVISO: Si has descargado los dos archivos en la misma carpeta, únicamente hace falta abrir los dos archivos y actualizar los datos del mapa, seleccionando en la cinta de opciones: Datos > Actualizar todo. Con lo que se actualizará la tabla dinámica de la hoja 'Tiempo'.

Si al Actualizar todo el archivo con los datos climáticos no está abierto, aparecerá un aviso: Abra este libro en Excel e inténtelo de nuevo.

Seguir estos pasos únicamente si los archivos no están en la misma carpeta, o si se quiere estudiar la conexión de datos entre los dos archivos:

1) Seleccionar en la cinta de opciones: Revisar > Proteger libro.

2) Hacer clic en Proteger libro, con lo que queda desmarcado ese botón y el libro se desprotege  sin contraseña.

3) Seleccionar en la cinta de opciones: Datos > Consultas y conexiones

4) Encima de la conexión: WorksheetConnection_Consulta_AEMET, hacer clic con el botón derecho del ratón para mostrar el menú contextual.

5) Seleccionar: Propiedades...

6) Seleccionar la pestaña: Definición

7) Editar el Texto del comando: C:\Users\Pedro Wave\Downloads\Consulta Tiempo AEMET - PW1.xlsx!HistorialTiempo[#Datos]

8) Presionar el botón: Aceptar, cuando hayas cambiado el nombre del usuario o la ruta completa donde está guardado el archivo descargado...

9) Seleccionar en la cinta de opciones: Datos > Actualizar todo

10) En la hoja 'Mapa' seleccionar el día 30 de junio en la escala de tiempos, por ejemplo, estando seleccionadas las temperaturas Máximas (ºC).

11) Opcionalmente, puedes volver a proteger el libro, seleccionando en la cinta de opciones: Revisar > Proteger libro.

12) Ya se puede cerrar el archivo: Consulta Tiempo AEMET - PW1.xlsx

Si has seguido estos pasos, ya se habrá actualizado la tabla dinámica de la hoja 'Tiempos' en el archivo con el mapa:

Y ya puedes usar el mapa del clima, pudiendo seleccionar:

• Tipo de datos, con un cuadro combinado (ComboBox) se pueden seleccionar datos de población, área, densidad o datos climáticos.
• Fecha, con una escala de tiempos se pueden seleccionar los días del 15 al 30 de junio.
• Autonomías, con una segmentación de datos.
• Provincias, con una segmentación de datos.
• TOP5 de datos climáticos, con una barra de desplazamiento (ScrollBar) se pueden listar los datos climáticos ordenados de 5 en 5.

Truco 0: Si se selecciona más de una fecha en la escala de tiempos, la tabla dinámica de la hoja 'Tiempos' necesita crecer hacia abajo, pero se lo impide una tabla que está puesta en el rango A54:A55 a propósito...

Como la tabla dinámica no se puede superponer en esa tabla, se impide seleccionar más de una fecha en la escala de tiempo y aparece el aviso:

Cómo transformar los datos climáticos

En la hoja 'Provincias' se transforman los datos para presentarlos en el mapa.

Para consultar cómo se tratan los datos de población, área y densidad de cada provincia, hay que leer este artículo donde ya está explicado:

Mapa de Provincias de España en Excel | #ExcelPedroWave

Aquí explicaré los trucos con los que se consiguen tratar los datos climáticos.

Trucos para transformar los datos climáticos del mapa:

1) Columna Datos: El primer truco ha sido modificar la fórmula para añadir un valor residual al valor del dato, con lo que se consigue que los datos que son iguales sean distintos por unas millonésimas, con esta fórmula: 

=SI.ERROR(INDICE($E2:$M2;1;COINCIDIR(miMapa;miTipoDatos;0))+FILA()*0,000001;"")

Con un ejemplo se comprenderá mejor. El día 30 de junio las temperaturas máximas de Valencia; Murcia y Córdoba fueron iguales a 34,8 ºC.

Como el TOP5 se calcula con la función K.ESIMO.MAYOR, no se obtendrían las 3 provincias si no la primera de la lista repetida 3 veces, o sea Córdoba.

Truco 1: Sumar al valor de la temperatura: +FILA()*0,000001, con lo que se obtienen 3 temperaturas máximas distintas, pero prácticamente iguales a 34,8 ºC:

• 34,800049 ºC en Valencia.
• 34,800036 ºC en Murcia.
• 34,800017 ºC en Córdoba.

Esa pequeña diferencia, en millonésimas de grado, consigue diferenciar esas 3 temperaturas máximas iguales, mostrando adecuadamente esas 3 provincias en el TOP5:

2) Columna Máximas (°C): Calculada con la fórmula:

=INDICE(Tiempo!$C$2:$C$53;[@[Fila Tiempo]])

3) Columna Mínimas máx. (°C): Calculada con la fórmula:

=INDICE(Tiempo!$F$2:$F$53;[@[Fila Tiempo]])

4) Columna Mínimas (°C): Calculada con la fórmula:

=SI(SUBTOTALES(103;[@Provincias])=0;100;[@Mínimas])

Truco 2: Hacer que las temperaturas mínimas se puedan ordenar de mínima a máxima. Con el número 103 de la función SUBTOTALES se calcula como la función CONTARA, con lo que vale 0 si la fila está oculta y, en ese caso, la fórmula devuelve un 100, temperatura que no se alcanzará en este siglo... ¿O sí?

5) Columna Racha (km/h): Calculada con la fórmula:

=INDICE(Tiempo!$L$2:$L$53;[@[Fila Tiempo]])

6) Columna Velocidad máxima (km/h): Calculada con la fórmula:

=INDICE(Tiempo!$O$2:$O$53;[@[Fila Tiempo]])

7) Columna Precipitación (mm): Calculada con la fórmula:

=INDICE(Tiempo!$R$2:$R$53;[@[Fila Tiempo]])

8) Columna Oculta: Vale un 0 si la fila está oculta y un 1 si la fila está visible, con la fórmula:

=SI(miMapa=Tiempo!$I$1;1;SI(Y(miMapa=Tiempo!$R$1;[@[Precipitación (mm)]]=0);0;SUBTOTALES(103;[@Provincias])))

Truco 3: Hacer que siempre estén visibles las filas con temperatura mínima y que se oculten las filas con precipitación a cero.

9) Columna Mínimas: Es una columna auxiliar con la temperatura mínima:

=INDICE(Tiempo!$I$2:$I$53;[@[Fila Tiempo]])

10) Columna Fila Tiempo: Obtiene la fila de la hoja 'Tiempo' con la fórmula:

=COINCIDIR([@[50 Provincias + 2 CA*]];misProvincias;0)

11) En la fila 54 con los totales se usan diferentes fórmulas, dependiendo del tipo de dato de cada columna de la tabla.

Truco 4: Este truco, basado en las fórmulas de totales, ¡no pienso explicarlo a no ser que alguien pregunte en un comentario!

Tratamiento del mapa climático

Lo más importante no es la visualización de los datos, que es el resultado final, sino el tratamiento de los datos para conseguir ese resultado.

Una vez visto el truco, vamos a ver el trato que se les da a los datos climáticos.

Trato 1: El origen de datos del mapa coroplético está en estas dos columnas de la hoja 'Provincias':

• Columna C con las Provincias españolas y
• Columna D con los Datos a visualizar.

En el anterior artículo en que está basado este mapa, para seleccionar el tipo de datos del mapa se empleaba una celda con lista desplegable de una validación de datos. Estas listas desplegables tienen el problema de que no se puede ajustar el tamaño de la letra, siendo demasiado pequeño y difícilmente accesible para personas con discapacidad.

Trato 2: El tipo de datos visualizados en el mapa se selecciona ahora con un cuadro combinado (ComboBox en inglés), con la letra agrandada para ver mejor el desplegable.

¿Qué desplegable prefieres? ¿El de la derecha con una validación de datos? ¿El de la izquierda con un cuadro combinado? ¡Ninguno de los dos necesita macros para interactuar!

Trato 3: Este cuadro combinado permite elegir la fuente del texto y su tamaño, modificando sus Propiedades en Fuente > Font. Seleccionando Calibri, aparece un botón con 3 puntos suspensivos. Al presionar ese botón (...) aparece una ventana para modificar el tipo de fuente, su estilo, su tamaño, etc... Se ha elegido:

• Fuente: Calibri
• Estilo: Negrita
• Tamaño: 15

Trato 4: En Propiedades del cuadro combinado, seleccionar 2-fmStyleDropDownList en Apariencia > Style, con lo que se impide editar el cuadro combinado y únicamente deja seleccionar los 9 tipos de datos.

Trato 5: Seleccionar un 9 en Dato > ListRows, con lo que siempre aparecerán los 9 tipos de datos en el desplegable.

Trato 6: El rango R2:R11 está oculto y relacionado con el cuadro combinado por estas dos propiedades:

• LinkedCell: R2
• ListFillRange: R3:R11

Trato 7: El título del gráfico está en la celda E2, con esta fórmula que no tengo intención de explicar:

=SI(Y(miNúmDatos<>0;O(Y(miFecha>0;SI.ERROR(miTiempo;0)>0);SI.ERROR(COINCIDIR(miMapa;$R$3:$R$5;0);0)>0)); SI(DERECHA(miMapa;4)="(°C)";"Temperaturas ";"") & SUSTITUIR(miMapa;"(";"(" & TEXTO(miTotalDatos;"#.##0" & SI(DERECHA(miMapa;4)="(°C)";",0";"")) & " ");"ATENCIÓN: Fecha sin datos de "&miMapa) & SI(SI.ERROR(miTiempo;0)>0;SI(miFecha>0;" el "&TEXTO(miFecha;"d ""de"" mmmm") & " de "&AÑO(miFecha);"");"")

Tratamiento del TOP5 climático

La lista del TOP5 de provincias con los datos máximos, o mínimos para la temperatura mínima, se ha mejorado insertando una barra de desplazamiento vertical (ScrollBar), que permite ver de 5 en 5 provincias ordenadas según su valor climático.

Como ejemplo el TOP5 de temperaturas mínimas el día 30 de junio, aunque la columna Q está oculta en el mapa:

Trato 8: Barra de desplazamiento TOP5 con estas propiedades:

• LargeChange: 5
• SmallChange: 1
• Max: 45
• Min: 1
• LinkedCell: K33

Trato 9: En K33 está el número del primer TOP5. En K34 la fórmula: =K33+1, arrastrada hasta la celda K37.

Trato 10: Partiendo de los números en el rango K33:K37 se obtienen los datos climáticos ordenados en el rango M33:M37, con la fórmula arrastrada a las celdas del rango:

=SI(O(miTotalDatos="";$K33>MiTotalProvincias);"";SI.ERROR(SI(miMapa=$R$8;K.ESIMO.MENOR(misDatosFiltrados;$K33);K.ESIMO.MAYOR(SI.ERROR(misDatosFiltrados;0);$K33));""))

Para ordenar los datos climáticos se usa la función K.ESIMO.MAYOR, excepto si son las temperaturas mínimas, para las que se usa la función K.ESIMO.MENOR. Se ocultan las celdas sin datos...

Hay que recordar el Truco 1, que hace que los datos climáticos siempre sean distintos al sumar unas millonésimas a cada dato climático, lo que permite ordenarlos sin duplicarlos...

Trato 11: Obtener las provincias en el rango L33:L37 partiendo de los datos climáticos del rango M33:M37, con la fórmula arrastrada a las celdas del rango:

=SI($M33=0;"";SI.ERROR(INDICE(miListaProvincias;COINCIDIR($M33;misDatosFiltrados;0));""))

Trato 12: En el rango invisible Q33:Q37 se calculan las filas de las provincias en la hoja 'Tiempo', con la fórmula:

=COINCIDIR(Mapa!$L33;misProvincias;0)

Trato 13: La hora del rango N33:N37 se calcula con la fórmula que elige el rango dependiendo del tipo de dato:

=SI.ERROR(INDICE(ELEGIR(miTiempo;Tiempo!$E$2:$E$53;Tiempo!$H$2:$H$53;Tiempo!$K$2:$K$53;Tiempo!$N$2:$N$53;Tiempo!$Q$2:$Q$53);$Q33);"")

No hay hora para el dato de precipitación pues es la que cae durante las 24 horas del día. 

Trato 14: La estación meteorológica en el rango O33:O37 se calcula con la fórmula que elige el rango dependiendo del tipo de dato:

=SI.ERROR(INDICE(ELEGIR(miTiempo;Tiempo!$D$2:$D$53;Tiempo!$G$2:$G$53;Tiempo!$J$2:$J$53;Tiempo!$M$2:$M$53;Tiempo!$P$2:$P$53;Tiempo!$S$2:$S$53);$Q33);"")

Trato 15: Las cabeceras del TOP5 se rellenan con estas fórmulas:

• L32: TOP5 Provincias
• M32: =miMapa
• N32: =SI(O(miMapa=Tiempo!$C$1;miMapa=Tiempo!$F$1;miMapa=Tiempo!$I$1;miMapa=Tiempo!$L$1;miMapa=Tiempo!$O$1);"Hora";"")
• O32: =SI(O(miMapa=Tiempo!$C$1;miMapa=Tiempo!$F$1;miMapa=Tiempo!$I$1;miMapa=Tiempo!$L$1;miMapa=Tiempo!$O$1;miMapa=Tiempo!$R$1);"Estación";"")

Con estos 15 tratamientos del TOP5 de datos climáticos se consigue este efecto, viéndose todas las provincias ordenadas según el valor máximo registrado en cada provincia durante el día elegido:

NOTA: Con las fórmulas anteriores se ha corregido el error que mostraba datos duplicados, por ejemplo 3 veces la provincia de Córdoba en el primer TOP5, cosa que ocurre en esta imagen animada, y que me ha costado corregir un poco más de la cuenta, ¡hasta que se me ha ocurrido añadir el Truco 1 visto antes!

Cómo extraer los datos climáticos con Power Query

Como este artículo ya es demasiado extenso, he dejado para un nuevo artículo los pasos aplicados en Power Query para extraer, transformar y cargar los datos climáticos de los ficheros de la AEMET:

Datos climáticos con Power Query | #ExcelPedroWave

Desde este nuevo artículo podrás descargar la actualización del archivo ¡con la consulta del tiempo pasado!

Comparador mensual interactivo

Este es el quinto artículo sobre gráficos escalonados que he escrito en dos semanas y que puedes encontrar aquí:

Resultados de la búsqueda de escalonados | #ExcelPedroWave

Comparador con un gráfico escalonado

Este comparador mensual interactivo está hecho con un gráfico de columnas agrupadas escalonado con sombras, siendo las series los años a comparar.

Atendiendo la consulta de un lector de este blog, he comparado el consumo eléctrico de un hogar cualquiera durante 3 años. En el eje horizontal están los meses y en el eje vertical el consumo mensual de electricidad en kWh. Los escalones del gráfico son las series anuales de consumos mensuales.

Descarga del comparador mensual interactivo

Puedes descargar este comparador mensual interactivo v1.2 desde estos enlaces:

• Microsoft OneDrive: Comparador Mensual Interactivo PW1.xlsm

• Sites Google Drive: Comparador Mensual Interactivo PW1.xlsm

Abre la plantilla con una versión de Excel de escritorio a partir de Excel 2013 y presiona el botón: Habilitar edición

Aparece una ADVERTENCIA DE SEGURIDAD: Las macros se han deshabilitado. Presiona el botón: Habilitar contenido

Esta plantilla contiene macros y está protegida sin contraseña, por lo que se pueden estudiar y analizar las fórmulas y el código VBA.

Si se quieren copiar las hojas de este archivo a otro libro de Excel hay que ir a la cinta de opciones: Revisar > Proteger libro. Haciendo clic en Proteger libro se desprotege sin contraseña, para permitir cambios en la estructura del libro, como mover, eliminar, agregar y copiar las hojas...

ACTUALIZACIÓN versión 1.2 (2022-06-29):

En la hoja 'Comparador' se puede editar el rango de celdas B2:D13 para poner los valores de consumo eléctrico de tu hogar o empresa.

En la hoja 'Comparador2' hay un gráfico escalonado convencional no interactivo, para poder compararlo con el gráfico escalonado con sombras interactivo de la hoja 'Comparador'.

Comparador con un gráfico de líneas

Aunque este artículo presenta un comparador revolucionario con un gráfico escalonado, lo habitual es comparar valores de meses con gráficos clásicos de líneas o de columnas agrupadas.

Este sería el comparador clásico con un gráfico de líneas, en los que los valores mensuales son puntos unidos por líneas quebradas:

Comparador con un gráfico de columnas

Este sería el comparador clásico con un gráfico de columnas agrupadas, siendo el más habitual de usar con una barra vertical por cada valor:

Estos dos últimos comparadores no los vas a poder descargar, pues mi intención era que conocieras el comparador con gráficos escalonados, y son fáciles de hacer partiendo del comparador que te has descargado, con cambiar el tipo de gráfico.

Ahora que conoces 3 tipos de comparadores mensuales interactivos, ¿con qué tipo de gráfico te quedas?

• 😂 Con el de líneas
• 💡 Con el de columnas agrupadas
• 👏 Con el escalonado convencional
• 💗 Con el escalonado con sombras

Vídeo del Comparador mensual interactivo

Cómo hacer un comparador mensual interactivo

Para saber cómo hacer un gráfico escalonado como éste, se pueden consultar estos dos artículos:

Gráficos escalonados - Step charts | #ExcelPedroWave

Comparador interactivo con gráficos escalonados | #ExcelPedroWave

Comparador interactivo con gráficos escalonados

Voy a añadir interactividad al comparador que publiqué aquí:

Comparador PVPC electricidad | #ExcelPedroWave

Así podremos comparar los nuevos precios regulados de la luz en España (PVPC), que se ven reducidos por aplicar un tope al gas al resto de energías con las que se genera la energía eléctrica. El tope al gas será de 40 €/MWh durante los próximos 6 meses. De todos modos, la primera semana de aplicación del tope al gas la factura eléctrica ha seguido siendo muy cara debido a la ola de calor, como se comprueba con el comparador que se puede descargar al final de este artículo.

Para comparar más de dos días es mejor añadir una segmentación de datos con los días a comparar, así se puede elegir qué días comparar y ver los datos de uno o varios días.

La interactividad del gráfico se consigue mediante la intervención de una macro y estas dos segmentaciones de datos:

• Con una segmentación de datos se eligen las fechas sobre la tabla de fechas.
• Con otra segmentación de datos se elige el valor mínimo del eje vertical, que dependerá de los valores PVPC de las fechas elegidas.

Cómo diseñé este comparador interactivo

Los datos están en la tabla "TablaEscalones" con los valores PVPC de las 24 horas de 7 días.

Se ha elegido el 14/06 por ser el último día en que se calculó el PVPC sin el tope al gas para calcular la tarifa eléctrica regulada en España y los 6 días siguientes para comparar el PVPC en la primera semana de aplicación de los precios en la nueva "isla energética" de la península ibérica.

Se calcula el promedio, el máximo y el mínimo de cada día y esta tabla es el origen del gráfico escalonado, con la leyenda de cada serie con el promedio de cada día entre paréntesis.

La tabla "TablaFechas", en el rango A31:B39, contiene las 7 fechas y el valor mínimo PVPC de cada día. Se filtra con la segmentación de datos de fechas.

En la columna A se calculan las fechas con la fórmula:

=FECHANUMERO(TablaEscalones[[#Encabezados];[14/06]])

En la columna B se calculan los mínimos con la fórmula: =B28, cambiando la columna en las siguientes fechas.

En la celda D42, con el nombre definido: miPVPCmín, se calcula con la fórmula:

=REDONDEAR.MENOS(SUBTOTALES(105;TablaFechas[Mínimo])/50;0)*50

Con lo que se consigue el valor mínimo del eje vertical para la tabla de fechas filtrada.

La tabla "TablaMínEjeVertical" está en el rango A42:A50, con los valores mínimos posibles del eje vertical, siendo la fórmula de la celda A43: =miPVPCmín

La celda A44 con la fórmula: =SI(A43=0;0;A43-50) se arrastra hasta la celda A49.

En la celda A50 se calcula el máximo con la fórmula: =SUBTOTALES(104;[Mín. eje])

Esta tabla se filtra con su propia segmentación de datos para el valor mínimo del eje vertical.

Esta es una imagen animada con el efecto de cambiar el valor mínimo del eje vertical:

Macros del comparador interactivo

Para conseguir la interactividad de este comparador con gráficos escalonados hacen falta algunas macros. Como la interactividad se consigue al seleccionar fechas o valores mínimos del eje vertical en las segmentaciones de datos, hay que conseguir que esos cambios sean actualizados en el gráfico, para lo que hace falta responder al evento que se entera del cambio.

Ese evento es Worksheet_Calculate, dentro de la hoja 'PVPC', que llama a la macro: FiltrarSeriesPVPC, que comprueba si el cambio ha sido en la segmentación de las fechas o en la segmentación del valor mínimo del eje vertical.

Private Sub Worksheet_Calculate() ' ' Evento Worksheet_Calculate ' ' Filtra las series PVPC FiltrarSeriesPVPC End Sub

Si ha cambiado la selección de la segmentación de fechas, filtra las series del gráfico en las que la altura de las fechas es cero en la tabla de fechas, por estar filtradas.

Si ha cambiado la segmentación del valor mínimo del eje vertical, lo cambia en el gráfico.

Sub FiltrarSeriesPVPC() ' ' Filtrar las series con 7 días en el gráfico PVPC, ' manteniendo el valor mínimo seleccionado para el eje vertical. ' Actualiza el cambio del valor mínimo del eje vertical. ' Dim iSerie As Integer Dim iMín As Integer Dim bFiltrada As Boolean Dim bFechas As Boolean Dim lFila As Long ' Fila de la tabla de fechas lFila = Range("RangoFechas").Row With Worksheets("PVPC").ChartObjects("Gráfico Escalonado Columnas").Chart ' Bucle para recorrer las 7 series For iSerie = 1 To 7 ' Filtra las series con altura 0 de las fechas bFiltrada = Worksheets("PVPC").Cells(lFila + iSerie, 1).Height = 0 If .FullSeriesCollection(iSerie).IsFiltered <> bFiltrada Then .FullSeriesCollection(iSerie).IsFiltered = bFiltrada bFechas = True End If Next iSerie ' Cambia el valor mínimo del eje vertical If bFechas Then iMín = ObtenerMínEjeVertical If iMín = -1 Then iMín = Range("miEjeVmín").Value2 ElseIf iMín > Range("miPVPCmín").Value2 Then iMín = Range("miPVPCmín").Value2 End If Else iMín = Range("miEjeVmín").Value2 End If .Axes(xlValue).MinimumScale = iMín End With End Sub

Con la función ObtenerMínEjeVertical se obtiene el valor seleccionado en la segmentación de datos con el mínimo valor del eje vertical del gráfico.

Function ObtenerMínEjeVertical() As Double ' ' Obtener el valor mínimo del eje vertical ' Dim ii As Integer ObtenerMínEjeVertical = -1 With ActiveWorkbook.SlicerCaches("SegmentaciónDeDatos_Valor_vertical") For ii = 1 To .SlicerItems.Count If .SlicerItems(ii).Selected Then ObtenerMínEjeVertical = .SlicerItems(ii).Value Exit For End If Next ii End With End Function

Descarga del comparador interactivo

Puedes descargar este comparador interactivo v2.0 desde estos enlaces:

• Microsoft OneDrive: Comparador PVPC PW2.xlsm

• Sites Google Drive: Comparador PVPC PW2.xlsm

Abre la plantilla con una versión de Excel de escritorio a partir de Excel 2013 y presiona el botón: Habilitar edición

Aparece una ADVERTENCIA DE SEGURIDAD: Las macros se han deshabilitado. Presiona el botón: Habilitar contenido

Esta plantilla contiene macros y no está protegida, por lo que se pueden estudiar y analizar las fórmulas y el código VBA.

Y con este artículo finaliza la serie dedicada al PVPC, con el que se confecciona la factura de la energía eléctrica española. Enlace a los artículos PVPC:

Resultados de la búsqueda de PVPC | #ExcelPedroWave

De paso me he atrevido a contribuir en estos dos artículos de Wikipedia:

• Precio voluntario para el pequeño consumidor - Wikipedia, la enciclopedia libre
• Ola de calor en Europa de 2022 - Wikipedia, la enciclopedia libre

Gracias por seguir leyendo este blog.

Gráficos escalonados PVPC electricidad

Lo más importante que quiero enseñar, y que quiero que aprendas, es que podemos usar otros tipos de datos para visualizar nuestros datos en un gráfico. Por ejemplo, para mostrar horas podemos usar días o años en un gráfico de líneas.

En el anterior artículo expliqué cómo hacer gráficos escalonados o gráficos de pasos o step charts:

Gráficos escalonados - Step charts | #ExcelPedroWave

En este artículo explicaré cómo hacer un gráfico escalonado de 24 horas y, como ejemplo, usaré los datos de la tarifa regulada española, denominada PVPC, que puedes obtener desde este otro artículo:

Gráficos PVPC Electricidad España | #ExcelPedroWave

Vamos a ver 4 métodos de representación de un gráfico escalonado, en este orden:

• Primer método revolucionario: Con un gráfico de columnas y sombras.
• Segundo método revolucionario: Con un gráfico de columnas con etiquetas.
• Primer método clásico: Con un gráfico de líneas con series temporales en días.
• Segundo método clásico: Con un gráfico de líneas con series temporales en años.

En esta imagen animada se puede ver el resultado de los 4 métodos expuestos en este artículo para crear un gráfico escalonado de 24 horas.

Observación: En los gráficos de líneas los valores del eje horizontal solamente pueden ser textos o fechas, en días, meses o años, por lo que en un principio no puede haber horas en el eje horizontal.

Truco: Para mostrar hasta 24 horas en el eje horizontal de un gráfico de líneas, no podemos usar 12 meses, pero si que podemos representarlas con 24 días (limitado hasta 31 elementos) o con 24 años (con un límite mucho más elevado de años).

Vídeo de cómo hacer gráficos escalonados

En este videotutorial explico cómo crear un gráfico escalonado mediante los métodos presentados en este artículo.

Cómo crear un gráfico escalonado PVPC

Si prefieres leer una explicación escrita antes que ver el vídeo, ¡sigue leyendo!

Preliminares

Lo fundamental es obtener datos rigurosos y exactos del PVPC de un día.

1) Para ello, hay que descargar la plantilla con el PVPC de electricidad de España desde este enlace:

Gráficos PVPC Electricidad España | #ExcelPedroWave

2) Abrir el archivo: PVPC Electricidad - PW1.xlsm

3) Presionar el botón: Habilitar edición

4) Presionar el botón: Habilitar contenido

5) Se selecciona el día de hoy en la escala de tiempos, con lo que se actualizan las gráficas de PVPC.

6) Los datos de PVPC se copiarán de la columna D en la hoja 'PVPC AUX', como se muestra en la imagen de la derecha.

Esta vez los primeros métodos explicados serán los revolucionarios, a partir de gráficos de columnas.

Primer método revolucionario

Pasos para crear un gráfico escalonado revolucionario, con el PVPC de las 24 horas de un día, mediante un gráfico de columnas y sombras:

1) Crear una tabla con el PVPC de cada una de las 24 horas.

2) Escribir una secuencia del 1 al 24 en el rango de celdas A2:A25, con las 24 horas como números sin decimales.

3) Pegar los valores de PVPC en el rango B2:B25.

4) Con origen en esta tabla, se inserta un gráfico de columnas agrupadas.

5) Se selecciona la serie con las columnas y, con el botón derecho del ratón, se selecciona: Dar formato a la serie de datos.

6) En opciones de serie:

• Superposición de series: 100 %
• Ancho del rango: 0 %

7) En opciones de serie de relleno y línea:

• Relleno: Sin relleno
• Borde: Sin línea

8) En efectos de serie:

• Seleccionar como Sombra: Desplazamiento: arriba
• Color: rojo
• Transparencia: 0 %
• Tamaño: 100 %
• Desenfoque: 3 pt
• Ángulo: 270º
• Distancia: 3 pt

9) Seleccionamos el eje vertical y, con el botón derecho del ratón, dar formato al eje.

10) En Número, con Posiciones decimales: 0

El resultado es un gráfico escalonado como éste:

Segundo método revolucionario

La principal ventaja de este gráfico escalonado hecho con columnas es que se pueden añadir etiquetas de datos encima de los escalones y, si además quitamos el eje vertical, se convierte en este fantástico gráfico escalonado, sin líneas verticales:

Pasos para crear este gráfico escalonado revolucionario, con el PVPC de las 24 horas de un día, mediante un gráfico de columnas con etiquetas:

11) Copiar el gráfico obtenido en el paso 10) del primer método.

12) Se selecciona la serie con las columnas y, con el botón derecho del ratón, se selecciona: Dar formato a la serie de datos.

13) En efectos de serie: Sombra > Desenfoque: 0 pt

14) Agregar etiquetas de datos en la serie de columnas.

15) Seleccionar las etiquetas y, con el botón derecho del ratón, seleccionar: Formato de etiquetas de datos

16) En Número, seleccionar la categoría Número con Posiciones decimales: 0

17) Quitar el eje vertical.

18) Quitar las líneas de división del eje vertical.

Primer método clásico

Con un gráfico de líneas con series temporales en días para representar las horas.

1) Creación de una tabla auxiliar con el PVPC en las columnas L y M.

2) En la celda L2 se escribe: 01/01/2022, para representar la primera hora con el día 1.

3) En la celda L3 se escribe la fórmula: =L2+1

4) Se arrastra esa fórmula hasta la celda L25, con el día 24/01/2022.

5) En la celda L26 se escribe la fórmula: =L2-1, con el día 31/12/2021.

6) En L27 se escribe la fórmula: =L26+1, con el día 01/01/2022.

7) Se arrastra esa fórmula hasta la celda L49, con el día 23/01/2022.

8) En M2 se escribe la fórmula: =B2

9) Se arrastra esa fórmula hasta M25.

10) En M26 se escribe la fórmula: =M2

11) Se arrastra esa fórmula hasta M49, con lo que se tiene dos veces los valores de PVPC.

12) Para que el 31/12/2021 se convierta en un cero, el formato de número personalizado del rango L2:L49 debe ser: [=44561]"0";d

13) Con origen en esta tabla auxiliar, se inserta un gráfico de líneas.

El resultado es un gráfico escalonado convencional.

Segundo método clásico

Con un gráfico de líneas con series temporales en años para representar las horas.

1) Creación de una tabla auxiliar con el PVPC en las columnas W y X.

2) En la celda W2 se escribe: 01/01/2001, para representar la primera hora con el año 2001.

3) En la celda W3 se escribe la fórmula: =FECHA(AÑO(W2)+1;1;1)

4) Se arrastra esa fórmula hasta la celda W25, con el año 2024.

5) En la celda W26 se escribe: 01/01/2000, con el año 2000.

6) En W27 se escribe la fórmula: =FECHA(AÑO(W26)+1;1;1), con el año 2001.

7) Se arrastra esa fórmula hasta la celda W49, con el año 2023.

8) En X2 se escribe la fórmula: =B2

9) Se arrastra esa fórmula hasta X25.

10) En X26 se escribe la fórmula: =B2

11) Se arrastra esa fórmula hasta X49, con lo que se tiene dos veces los valores de PVPC.

12) El formato de número personalizado del rango W2:W49 debe ser: aa

13) Con origen en esta tabla auxiliar, se inserta un gráfico de líneas.

El resultado es un gráfico escalonado convencional con la horas representadas por años, del 00 al 24.

Como dije en el artículo anterior, estos gráficos escalonados a partir de gráficos de líneas tienen el inconveniente de no poder usar las etiquetas, pues salen dobles.

Descarga los gráficos escalonados

Puedes descargar estos gráficos escalonados v1.0 desde estos enlaces:

• Microsoft OneDrive: Gráfico PVPC escalonado PW1.xlsx

• Sites Google Drive: Gráfico PVPC escalonado PW1.xlsx

Abre la plantilla con una versión de Excel de escritorio igual o superior a Excel 2010 y presiona el botón: Habilitar edición

¿Se te ocurre cómo hacer otros tipos de gráficos escalonados? Comenta...

Comparador PVPC electricidad

La ola de calor que está sufriendo la península ibérica nos avisa que estamos en una "isla energética", más próxima a África que a Europa, tanto respecto al abastecimiento energético como al clima.

Cada año que pase se supone que la piel de toro será más una "isla desértica" que una "isla energética" si nos atenemos al cambio climático, provocado fundamentalmente por el consumo de energía sin límites.

Como nuestros políticos y políticas no toman medidas contra la desertización, pues es un fenómeno a años vista que no les preocupa tanto, se les ocurre tomar medidas contra los precios actuales de la energía, pues son datos a meses vista en los que pueden influir electoralmente hablando. Lo que puede provocar el efecto colateral de desincentivar el ahorro energético, ahora que los precios bajarán para la industria y para los pequeños consumidores...

Esto lo hacen aún a sabiendas que la factura real la pagarán nuestros hijos, hijas, nietos y nietas cuando, además de subir desmesuradamente la luz, suba exponencialmente el precio del agua que beban y del aire que respiren cuando esta tierra sea un desierto.

¡Más nos vale consumir menos ahora! ¡Por si las moscas!

Comparador PVPC

Como no soy un analista de big data, no puedo analizar los datos del cambio climático, pero si que puedo comparar el PVPC de la tarifa eléctrica regulada en España con un Comparador PVPC de los precios entre dos días cualesquiera, por ejemplo entre ayer y hoy. La pregunta que me hago es: ¿Cómo es que hoy sube la luz?

¿Por qué comparar el PVPC de ayer y hoy?

Porque hoy 15 de junio entra en vigor el nuevo Precio Voluntario para el Pequeño Consumidor (PVPC) de la electricidad en España, al ser tratada la península ibérica como una "isla energética", con lo que se aplica un precio tope de 40 €/MWh del gas natural con el que se genera la electricidad, siendo que hoy el precio real del gas en el mercado es del doble: 80 €/MWh.

Se supone que la nueva regulación abaratará el coste de la luz en más de un 20% pero, sumando la compensación a las centrales de cogeneración y a los ciclos combinados, el primer día de aplicación del nuevo cálculo solamente lo hará en un 6%.

Para más inri, este miércoles está programada la mayor producción de ciclos combinados en un año (4 de cada 10 MWh consumidos procederán del gas), en gran medida por la ola de calor de esta semana. Por el contrario, la eólica y la solar, mucho más baratas, hoy reducirán su aportación al consumo eléctrico. Con lo que el PVPC promedio de hoy subirá un 5% sobre el de ayer con el tope del gas. Sin el tope del gas hubiera subido más de un 11% respecto a ayer.

Habrá que comparar las diferencias de PVPC durante los 12 meses que estará en vigor esta disminución artificial de precios de la luz y, sobre todo, comparar también los valores actuales con los valores de hace más de un año, pues el 1 de junio de 2021 comenzó a aplicarse la tarifa denominada 2.0 TD, que es el actual valor del PVPC con discriminación horaria, pero desde hoy topado el precio del gas usado para generar electricidad, gracias a la "excepción ibérica".

El precio mayorista de la luz baja menos de lo previsto por el Gobierno en el primer día de aplicación de la ‘excepción ibérica’ | Economía | EL PAÍS (elpais.com)

Excepción ibérica: la luz sube un 5% | LinkedIn

Descarga del Comparador PVPC

Puedes descargar este comparador con gráfico escalonado v1.0 desde estos enlaces:

• Microsoft OneDrive: Comparador PVPC PW1.xlsx

• Sites Google Drive: Comparador PVPC PW1.xlsx

Abre la plantilla con una versión de Excel de escritorio igual o superior a Excel 2010 y presiona el botón: Habilitar edición

Los datos del PVPC de los dos días a comparar los puedes obtener con la plantilla de este artículo:

Gráficos PVPC Electricidad España | #ExcelPedroWave

Ola de calor

Este blog lo inicié como una ola, para intentar llegar a más audiencia hablando de Excel. Ahora sigo hablando de olas pero de olas de calor y de energía. Puedes descargar mi primera ola en Excel desde este enlace:

Cómo hacer cálculos y gráficos animados | #ExcelPedroWave

Hoy hace la misma calor en Zaragoza que en el desierto del Sahara, unos 43º de máxima. Incluso las noches son igual de cálidas que en el desierto, con 25º de mínima. Por esta razón este artículo es el más corto que he escrito desde que me autojubilé hace casi dos años, pues ¡no aguanto la calor!

Como decía el cantautor Labordeta:

• Polvo, niebla, viento y sol
• Esta tierra es Aragón

En el siguiente artículo explicaré cómo he hecho este gráfico escalonado con columnas agrupadas. Mientras tanto puedes aprender a hacer gráficos escalonados más simples siguiendo este enlace:

Gráficos escalonados - Step charts | #ExcelPedroWave

Gráficos escalonados - Step charts

Los gráficos escalonados o gráficos de pasos o step charts en inglés, no están implementados por defecto en Excel. Hace falta un trabajo adicional para crear un gráfico escalonado.

Voy a explicar dos métodos para construir un gráfico escalonado:

• 📈 Con una tabla auxiliar y con un gráfico de líneas.
• 📊 Sin ninguna tabla auxiliar y con un gráfico de columnas agrupadas.

El primero es el método convencional. El segundo es un truco hecho con sombras.

Como ejemplo usaré datos de stock durante un año.

En esta imagen animada se puede ver el resultado de los dos métodos para crear un gráfico escalonado, uno con líneas del mismo grosor tanto en vertical como en horizontal, y otro con líneas más marcadas en horizontal y más difusas en vertical.

Vídeo con el stock en un gráfico escalonado

Este vídeo es un tutorial de cómo crear un gráfico escalonado mediante los dos métodos presentados en este artículo.

Cómo crear un gráfico escalonado

Si prefieres leer una explicación escrita antes que ver el vídeo, ¡sigue leyendo!

Primer método

Pasos para crear un gráfico escalonado convencional con el stock de cada mes del año 2021:

1) Crear una tabla con el stock de cada uno de los 12 meses.

2) En la celda A2 se escribe: 01/01/2021 y en la celda A3 se escribe: 01/02/2021

3) Se arrastran esas dos celdas A2:A3 hasta la celda A13, con lo que se obtiene el primer día de cada mes del año 2021.

4) Se selecciona el rango A2:A13 y se cambia el formato de celda de número personalizado del tipo: mmm, para visualizar las 3 primera letras de cada mes.

5) El stock del año 2021 se puede inventar con cualquier valor en el rango B2:B13.

6) Otra alternativa es que los días de la columna A no sean el primer día del mes sino el último día del mes. En este caso, en la celda A2 se escribe: 31/01/2021. En la celda A3 se escribe la fórmula: =FIN.MES(A2;1) y se arrastra hasta la celda A13. El resultado en los gráficos será el mismo con una fecha u otra.

7) Creación de la tabla auxiliar con el stock en las columnas J y K.

8) En la celda J2 se escribe la fórmula: =A3, con lo que se comienza por el mes de febrero.

9) Se arrastra esa fórmula hasta la celda J12.

10) En la celda J13 se escribe la fórmula: =FECHA.MES(J12;1)

11) En J14 se escribe la fórmula: =A2

12) Se arrastra esa fórmula hasta la celda J25.

13) En K2 se escribe la fórmula: =B2

14) Se arrastra esa fórmula hasta K13.

15) En K14 se escribe la fórmula: =B2

16) Se arrastra esa fórmula hasta K25, con lo que se tiene dos veces el stock, con los primeros 12 meses desplazados un mes hacia delante y los segundos 12 meses en el mismo orden que la tabla original.

17) Con origen en esta tabla auxiliar, se inserta un gráfico de líneas.

El resultado es un gráfico escalonado convencional.

Segundo método

Pasos para crear un gráfico escalonado revolucionario con el stock de cada mes del año 2021:

1) a 6) Son los mismos pasos de antes para crear una tabla con el stock de cada uno de los 12 meses.

7) Con origen en esta tabla, se inserta un gráfico de columnas agrupadas.

8) Se selecciona la serie con las columnas y, con el botón derecho del ratón, se selecciona: Dar formato a la serie de datos.

9) En opciones de serie:

• Superposición de series: 100 %
• Ancho del rango: 0 %

10) En opciones de serie de relleno y línea:

• Relleno: Sin relleno
• Borde: Sin línea

11) En efectos de serie:

• Seleccionar como Sombra: Desplazamiento: arriba
• Color: azul
• Transparencia: 0 %
• Tamaño: 100 %
• Desenfoque: 3 pt
• Ángulo: 270º
• Distancia: 3 pt

El resultado es un gráfico escalonado como éste:

La principal ventaja de este gráfico escalonado hecho con columnas es que se pueden añadir etiquetas de datos encima de los escalones y, si además quitamos el eje vertical, se convierte en este fantástico gráfico escalonado:

Descarga los gráficos escalonados

Puedes descargar estos gráficos escalonados v1.0 desde estos enlaces:

• Microsoft OneDrive: Gráfico STOCK escalonado PW1.xlsx

• Sites Google Drive: Gráfico STOCK escalonado PW1.xlsx

Abre la plantilla con una versión de Excel de escritorio igual o superior a Excel 2010 y presiona el botón: Habilitar edición

Como he dicho en el vídeo, este es el gráfico escalonado que más me gusta y además no necesita ninguna tabla auxiliar.

Y a tí, ¿qué te parece?

Montaña Rusa Excel Roller Coaster

¿Una montaña rusa en Excel?

Hace años me hice amigo de George Lungu, cuando me enteré por casualidad que era el autor de una hoja con una "roller coaster" en inglés, o sea una "montaña rusa" en español (¿ahora se debería decir "montaña ucraniana"?) hecha enteramente en Excel y con unas pocas macros.

¡IMPRESIONANTE! ¡ASOMBROSA! ¡EXCELENTE!

Cuando George Lungu comenzó a publicar su blog hace 11 años, escribí el siguiente artículo maravillado por su gran talento:

Excel Unusual Engineering | #ExcelPedroWave 

Este es el aspecto de la "montaña rusa": 

¡Una montaña rusa en Excel!

Hace unos días carlos barboza publicó un vídeo con la "montaña rusa" en este enlace:

A 3D Animated Roller Coaster Model on spreadsheet | carlos barboza | LinkedIn

El autor del vídeo original George Lungu comentó lo siguiente:

En el nuevo Excel es bastante lento (alrededor de 4 veces más lento que en el antiguo Excel 2003). Tendré que rehacerlo sin el gráfico, redefiniendo los vértices y los segmentos entre ellos como segmentos libres. (no en un gráfico) durante cada fotograma. Eso debe acelerarlo considerablemente. Ustedes me dieron algo que hacer :).

Le comenté lo siguiente:

George Lungu, no necesitas rehacer nada. En Excel para Microsoft 365 funciona bien y tarda menos de un minuto en completar la vuelta. Lo que si me he atrevido es a rehacer las macros, que publicaré con tu permiso en mi tablón de LinkedIn.

Y es lo que hice en esta publicación: Roller Coaster | ExcelPedroWave | LinkedIn 

Ahora voy a publicar un revisión de las macros originales de George Lungu, para intentar montarme en la "montaña rusa" en cualquier versión a partir de Excel 2010.

Vídeo de la montaña rusa en Excel

En este vídeo la he probado en Excel para Microsoft 365, completando la vuelta en unos 24 segundos con un retardo de 20 milisegundos.

¿Cómo mejorar la montaña rusa en Excel?

He intentado mejorar la "montaña rusa" con una mínima reingeniería en VBA para:

1. Guardar el archivo como .xlsm, compatible a partir de Excel 2007 y que ocupa menos tamaño, por ser guardado en formato XML comprimido en ZIP.
2. Proteger las hojas 'Roller Coaster' y 'Calculations' sin contraseña, para lo que únicamente ha hecho falta desbloquear algunas celdas.
3. Poner en la hoja 'Calculations' un 0 en AG45, un 1 en AG46 y un 2 en AG76 para que el reseteo sea a velocidad 0 y la vuelta comience a velocidad 1.
4. Guardar en la hoja 'Calculations' solamente las macros para los controles de número (Forms.SpinButton) alineados.
5. Crear un módulo 'modCoaster' con las macros de George modificadas ¡y comentadas!
6. Editar la macro 'Go_' para arrancar y pausar la vuelta, haciendo clic en el sol o estrella: Go 🌞
7. Modificar la macro para que el índice, Index de la celda C21, se calcule sin decimales...
8. Cambiar el nombre de la variable n por: Private bLoop As Boolean
9. Quitar las macros sobrantes hechas con el grabador de macros.
10. Añadir: Option Explicit en honor a Jordan Morris Goldmeier.

Lo que nunca he pretendido ha sido modificar la lógica de las fórmulas de George Lungu, que consiguen mover la "montaña rusa" en un gráfico de dispersión con líneas suavizadas, por lo que casi todos los cambios los he hecho en las macros.

Lo que he tratado de resolver es la gran diferencia de tiempos que tarda una vuelta en mis dos portátiles con la versión original, que se puede descargar desde aquí:

Animated Roller Coaster in Excel – Excel Unusual

• Tarda de 57 a 62 segundos en mi viejo portátil, con Excel 2010 en Windows 7 y con una tarjeta gráfica ATI Radeon.
• Tarda de 11 a 13 segundos en mi nuevo portátil, con Excel para Microsoft 365 en Windows 11 y con una tarjeta gráfica NVIDIA.

Lo que quiero es que la vuelta dure aproximadamente lo mismo en mis dos portátiles, para lo que he añadido un retardo, obteniendo estos tiempos:

• Con un retardo de 20 milisegundos consigo unos 70 segundos por vuelta en mi viejo portátil.
• Con un retardo de 100 milisegundos consigo unos 70 segundos por vuelta en mi nuevo portátil.

Tiempos inferiores no me satisfacen, por ser una velocidad demasiado elevada para disfrutar de la vuelta...

¡Cambios en las macros de la montaña rusa en Excel!

Estas son la macros, modificadas con el permiso de George Lungu, en el módulo modCoaster:

' ------------------------------------------------------ ' VBA Project: Coaster7_short-may2022_PW1 ' Module: modCoaster ' Author: George Lungu ' Copyright: © 2009-2022 by George Lungu ' Web: https://excelunusual.com/coaster/ ' Last edit: 2022-06-03 by Pedro Wave ' Purpose: Roller Coaster macros ' ------------------------------------------------------ Option Explicit #If VBA7 Then Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems #Else Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds as Long) 'For 32 Bit Systems #End If Private bLoop As Boolean Sub Go_() ' ' Go Roller Coaster ' Loop play/pause/stop function ' Delay to control the ride's speed ' Reporting the ride's time in seconds ' Dim ii As Integer Dim sgTimer As Single Dim sgDelay As Single ' Initialize variables SelectCell sgDelay = [myDelay] / 1000 sgTimer = Timer If [myIndex] < 1 Then [myIndex] = 1 [mySeconds] = "" Else sgTimer = sgTimer - [mySeconds] End If ' Roller Coaster loop? bLoop = Not bLoop ' Loop play? If bLoop Then ' Roller Coaster ride For ii = [myIndex] To 2100 ' Index depends of the ride speed ' Notice the conversion to integers [myIndex] = [myIndex] + Int([mySpeed]) ' Loop delay Delay sgDelay ' Loop pause? If bLoop = False Then GoTo EXIT1 ' Loop stop? If [myIndex] > 1980 Then Reset_Change GoTo EXIT1 End If Next ii End If EXIT1: ' Ride time [mySeconds] = Timer - sgTimer End Sub Sub Delay(sgDelay As Single) ' ' Delay with the Timer function ' Dim myTimer As Single If sgDelay = 0 Then ' Yields execution to process other events DoEvents Else ' Delay greater than 0 If [mySleep] = "Sleep" Then ' Sleep function in miliseconds Sleep sgDelay * 1000 ' Yields execution to process other events DoEvents Else ' Add delay to the Timer function myTimer = Timer + sgDelay ' Loop delay Do ' Yields execution to process other events DoEvents ' Loop until the delay is exceeded Loop While Timer < myTimer End If End If End Sub Sub Reset_Change() ' ' Reset Roller Coaster ' bLoop = False [myIndex] = 0 [mySeconds] = "" SelectCell End Sub Sub SelectCell() ' ' Select default cell and protect sheet ' If ActiveSheet.Name = "Calculations" Then ' Try to edit the Index cell Range("myIndex").Select Else ' Hidden cell Range("E2").Select End If ' Protect sheet ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub

He incluido la función Timer y la función Sleep, sabiendo que esta última es una mala elección, pero la he incluido para comparar el efecto de las dos funciones.

Un desplegable en la celda A24 de la hoja 'Calculations' permite elegir entre: Delay (con la función Timer) y Sleep (con la función Sleep).

En la celda B24 se introduce el retardo en milisegundos, de uno en uno o de diez en diez, con el control de número ActiveX de la derecha.

El número total de segundos que se tarda en dar una vuelta se informa en la celda B26.

La posición de la "montaña rusa" se puede modificar con el índice, Index de la celda C21.

También he añadido una barra de desplazamiento para modificar fácilmente el índice, pudiendo cambiar de uno en uno o de diez en diez.

Incluso se puede mover en sentido inverso, gracias a esa barra de desplazamiento.

Otra característica nueva es la posibilidad de pausar la "montaña rusa" con el mismo botón con el que se arranca, ese icono con un sol. 🌞

Descarga la montaña rusa

Puedes descargar esta montaña rusa v1.0 desde estos enlaces:

• Microsoft OneDrive: Coaster7_short-may2022_delay-PW1.xlsm

• Sites Google Drive: Coaster7_short-may2022_delay-PW1.xlsm

Abre la plantilla con una versión de Excel de escritorio igual o superior a Excel 2007 y presiona el botón: Habilitar edición

Aparece una ADVERTENCIA DE SEGURIDAD: Las macros se han deshabilitado. Presiona el botón: Habilitar contenido

Esta plantilla contiene macros y no está protegida, por lo que se pueden estudiar y analizar las fórmulas y el código VBA, gracias a que su autor original la compartió así desde que la publicó hace más de 11 años.

¿Una nueva montaña rusa?

George Lungu me ha mandado este fin de semana un par de versiones nuevas de la "montaña rusa" para probarlas.

¡Y me ha vuelto a dejar impresionado!

¡Este hombre no tiene límites en Excel!

George acaba de subir un vídeo con los cambios en la montaña rusa que ha hecho últimamente.

Traducción de la descripción del vídeo:

Por demanda popular, vuelvo a Excel después de un largo paréntesis. Y esta vez lo haré a lo grande.

Agregué algunas características al modelo de montaña rusa de la siguiente manera:

1) Registro de rendimiento

2) Todo tipo de colores y selección de esquemas de color.

3) control de velocidad

4) Ajuste de la longitud del posavasos

5) Ajuste del tamaño del plano de tierra (malla)

Todo este modelo se reconstruirá desde cero con mucha mejor velocidad (en el nuevo Excel) y una variedad de pistas. También mentiría para hacerlo ajustable, de modo que los usuarios puedan crear sus propias pistas personalizadas.

¡¡¡Gracias por su apoyo!!!

Se puede descargar desde aquí:

A 3D Animated Excel Roller Coaster – video preview – Excel Unusual

¡¡¡ Disfruta del viaje !!!