Datos climáticos con Power Query

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

En el artículo anterior publiqué un mapa interactivo con datos climáticos fijos, que puedes descargar desde aquí:

Mapa del clima español | #ExcelPedroWave

A continuación explicaré cómo consultar los datos climáticos de un día desde la Agencia Estatal de Meteorología - AEMET, necesarios para visualizarlos en el mapa descargado del artículo anterior, y cómo almacenar un histórico del clima de varios días.

Para seguir la explicación tendrás que descargar el archivo que adjunto al final del artículo y abrirlo por la hoja 'ConsultaFecha':

Para descargar los datos climáticos de un día hay que seleccionar la fecha con el desplegable de la celda E2 y, en la cinta de opciones, seleccionar: Datos > Actualizar todo.

También se puede actualizar todo con la combinación de teclas: Control + Alt + F5

A continuación explicaré cómo extraer, transformar y cargar los datos climáticos con Power Query en Excel, ¡la mejor herramienta ETL del mercado!

Ruleta de preguntas y respuestas para el análisis de datos

Pulsa la flecha para pasar de página: ➡️

A continuación explicaré detalladamente estas 20 preguntas que me he hecho y las 20 respuestas que he dado para obtener los datos climáticos.

Origen de los datos climáticos

Información elaborada utilizando, entre otras, la obtenida de la Agencia Estatal de Meteorología - AEMET.

La AEMET permite consultar infinidad de datos climáticos, tanto del tiempo de los últimos días como de la predicción del tiempo para los próximos días.

En esta ocasión extraeremos datos históricos del tiempo acaecido en los últimos 7 días, aunque también se pueden consultar los datos climatológicos registrados durante el día de hoy, 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

Esta página contiene varias tablas con el clima en España durante el día de ayer:

Se pueden ver 2 botones para exportar dos tipos de archivos:

• Exportar a excel, con esta URL para descargar los datos de ayer:

https://www.aemet.es/es/eltiempo/observacion/ultimosdatos_espana_resumenes-diarios-anteriores.xls?k=&datos=det&w=2&f=&x=d07

• Exportar a csv, con esta URL para descargar los datos de ayer:

https://www.aemet.es/es/eltiempo/observacion/ultimosdatos_espana_resumenes-diarios-anteriores.csv?k=&datos=det&w=2&f=&x=d07

En el resto del artículo plantearé unas cuantas preguntas y las respuestas que he dado para solucionar el problema de extraer, transformar y cargar los datos climáticos de AEMET con Power Query en Excel, partiendo de un archivo con un "formato poco amigable".

Pregunta 1: ¿Qué herramienta ETL usar para datos AEMET?

Respuesta 1: ¡Power Query sin duda!

En Excel 2010 para Windows, apareció por primera vez Power Query como un complemento gratuito descargable desde aquí: Download Microsoft Power Query para Excel from Official Microsoft Download Center

A partir de Excel 2016 Power Query en Excel está integrado en la cinta de opciones: Datos > Obtener datos > Iniciar Editor de Power Query...

Teniendo esta poderosa herramienta ETL en Excel, un analista de datos ¿para qué va a usar otra distinta?

Pregunta 2: ¿De qué archivo se descargan los datos?

Respuesta 2: Del archivo con formato CSV

Los dos tipos de archivos que se pueden descargar desde AEMET son: XLS y CSV.

Para extraer datos de un archivo XLS (formato binario de Excel 97-2003) es necesario tener instalado Access Database Engine OLEDB (ACE): conector de Power Query Excel - Power Query | Microsoft Docs

Para extraer datos desde cualquier versión de Excel es mejor conectar a un archivo OpenXML (como XLSX), lo que obligaría a convertir el archivo XLS en XLSX, antes de importarlo con Power Query.

Como AEMET también suministra el archivo CSV, es mejor usar directamente este formato para extraer los datos climáticos, aunque es un archivo con un "formato poco amigable".

Pregunta 3: ¿Qué datos climáticos se pueden extraer?

Respuesta 3: Temperaturas, viento y precipitaciones

Estos son los datos de los archivos CSV descargados de AEMET y abiertos en Excel:

Son 809 registros de todas las estaciones controladas cada día por AEMET, con 12 campos:

• Estación: donde se han registrado los valores meteorológicos.
• Provincia: con el nombre regional.
• Temperatura máxima (ºC): incluyendo entre paréntesis la hora del registro.
• Temperatura mínima (ºC): incluyendo entre paréntesis la hora del registro.
• Temperatura media (ºC): NO USADO
• Racha (km/h): incluyendo entre paréntesis la hora del registro.
• Velocidad máxima (km/h): incluyendo entre paréntesis la hora del registro.
• Precipitación 00-24h (mm)
• Precipitación 00-06h (mm): NO USADO
• Precipitación 06-12h (mm): NO USADO
• Precipitación 12-18h (mm): NO USADO
• Precipitación 18-24h (mm): NO USADO

Es un archivo con un "formato poco amigable" al incluir entre paréntesis la hora del registro y por poner puntos (.) en lugar de comas (,) para los valores decimales. Por ejemplo, "26.0 (09:30)" para informar la temperatura máxima y su hora entre paréntesis, lo que obliga a cambiar el signo decimal y a separar cada campo en dos campos, uno para el valor y otro para la hora.

Al mapa solo se llevan los datos máximos o mínimos, por lo que los valores marcados como "NO USADO" no se han tenido en cuenta como datos para el mapa climático, aunque la temperatura media se podría incluir en próximas versiones, si este mapa tiene muchos lectores...

Pregunta 4: ¿De qué fechas se extraen datos?

Respuesta 4: De hoy y de los 7 días anteriores

AEMET mantiene solamente los archivos de hoy y de los 7 días anteriores.

Los datos de AEMET se pueden descargar desde este enlace:

https://www.aemet.es/es/eltiempo/observacion/ultimosdatos_espana_resumenes-diarios-anteriores.csv?k=&datos=det&w=2&f=&x=d07

Modificando el argumento final x se obtiene:

1. x=d07 para ayer, o sea, un día antes de hoy.
2. x=d06 para anteayer, o sea, para 2 días antes de hoy.
3. x=d05 para 3 días antes de hoy.
4. x=d04 para 4 días antes de hoy.
5. x=d03 para 5 días antes de hoy.
6. x=d02 para 6 días antes de hoy.
7. x=d01 para 7 días antes de hoy.

También publica los registros de las estaciones meteorológicas durante el día de hoy, que se pueden descargar desde este enlace:

https://www.aemet.es/es/eltiempo/observacion/ultimosdatos_espana_resumenes-diarios-anteriores.csv?k=&datos=det&w=1&f=

Estos datos difieren un poco de los datos de los 7 días anteriores.

Son los mismos datos usados al descargar los 7 días anteriores, excepto la última columna con la hora de "Datos hasta", que indica cuando se registraron los datos por última vez en el día de hoy.

ATENCIÓN: Solamente interesa descargar los datos de hoy si se quieren obtener los datos meteorológicos actuales. Para obtener valores históricos es conveniente que la fecha del registro sea de días anteriores que son actualizados con datos más confiables. Por ejemplo, el día 8 de julio se pueden obtener datos fiables de los días 1 al 4 de julio, pues los datos de los 3 últimos días aún puede modificarlos la AEMET.

Pregunta 5: ¿Cómo se seleccionan las fechas?

Respuesta 5: Mediante un desplegable de 8 días

En la celda E2 con validación de datos se selecciona la fecha con un desplegable en el rango E7:E14, con la lista de fechas de hoy y de los 7 días anteriores.

En la celda J2 se calcula la ruta del archivo CSV a consultar, con el nombre definido: miRuta, y con la fórmula:

="https://www.aemet.es/es/eltiempo/observacion/ultimosdatos_espana_resumenes-diarios-anteriores.csv?k=&datos=det&w=" & $F$6 & "&f=" & SI($G$6=0;"";"&x=d0" & $G$6)

Esta ruta es la que debe leer Power Query para consultar los datos climáticos.

Pregunta 6: ¿Cómo pasar "miRuta" a Power Query?

Respuesta 6: Con una referencia a Excel

Se abre el Editor de Power Query desde la cinta de opciones con: Datos > Obtener datos > Iniciar Editor de Power Query...

Se selecciona: Nuevo origen > Otros orígenes > Consulta en blanco

y se cambia el nombre de la consulta: ConsultaAEMET

En la ventana de fórmulas se escribe: 

= Excel.CurrentWorkbook(){[Name="miRuta"]}[Content]{0}[Column1]

Con lo que se obtiene la URL del archivo CSV a extraer desde AEMET: 

Pregunta 7: ¿Cómo extraer los datos de la ruta?

Respuesta 7: Como documento CSV codificado en español

Insertar un paso después y editar la fórmula:

= Csv.Document(Web.Contents(Origen),[Delimiter=",", Columns=8, Encoding=1252])

Que extrae los datos climáticos del archivo CSV:

Se extraen únicamente 8 columnas del archivo CSV separado por comas, y con el argumento Encoding=1252 para traducir el texto al idioma español, indicando como Origen de archivo: 1252: Europeo occidental (Windows), lo que se puede ver haciendo clic en la rueda de configuración de este segundo paso:

Esto pasaría sin Encoding=1252, que no entendería algunas letras en español, como la letra ñ:

Encoding: es la codificación de texto del archivo. Su valor predeterminado es: 65001 (UTF-8), que no soporta el idioma español, por lo que hay que cambiarlo a 1252: Europeo occidental (Windows). Enlace aquí: Csv.Document - PowerQuery M | Microsoft Docs

Pregunta 8: ¿Decimales con puntos en lugar de comas?

Respuesta 8: AEMET obliga a convertir los decimales

Los datos decimales de AEMET están separados por puntos en lugar de comas. ¿Por qué será?

En el paso 3 es necesario convertir los puntos "." en comas "," con esta fórmula:

= Table.ReplaceValue(Personalizado1,".",",",Replacer.ReplaceText,{"Column3", "Column4", "Column5", "Column6", "Column7", "Column8"})

Pregunta 9: ¿Crear tabla o solo conexión a AEMET?

Respuesta 9: Crear solo conexión

Es mejor: Crear únicamente la conexión para la ConsultaAEMET, pues esta conexión se usará en dos consultas:

Pregunta 10: ¿Cómo separar los datos de la fecha?

Respuesta 10: Con dos referencias a la ConsultaAEMET

Desde Inicio > Administrar > Referencia, se crea una consulta nueva que hace referencia a ConsultaAEMET, creando dos consultas diferentes:

• ConsultaFecha: con la fecha de los datos climáticos.
• ConsultaTiempo: con los datos del tiempo meteorológico.

También se pueden crear, haciendo clic con el botón derecho del ratón sobre la ConsultaAEMET, y seleccionando en el menú contextual: Referencia

Pregunta 11: ¿Cómo obtener la fecha de la consulta?

Respuesta 11: Conservando las filas superiores de la consulta

Con 4 pasos se obtiene la fecha en ConsultaFecha:

• Paso 1: Referencia a ConsultaAEMET.
• Paso 2: Conservar las 3 filas superiores.
• Paso 3: Quitar todas las columnas excepto las dos primeras.
• Paso 4: Cambiar el nombre de las columnas: Tiempo; Fecha y hora.

Pasos en el editor avanzado del lenguaje M de Power Query:

let Origen = ConsultaAEMET, #"Conservar filas superiores" = Table.FirstN(Origen,3), #"Otras columnas quitadas" = Table.SelectColumns(#"Conservar filas superiores",{"Column1", "Column2"}), #"Columnas con nombre cambiado" = Table.RenameColumns(#"Otras columnas quitadas",{{"Column1", "Tiempo"}, {"Column2", "Fecha y hora"}}) in #"Columnas con nombre cambiado"

Con lo que se obtiene la fecha y hora oficial de actualización y la fecha del tiempo meteorológico.

Pregunta 12: ¿Cómo obtener los datos climáticos?

Respuesta 12: Quitando las filas superiores de la consulta

Con unos cuantos pasos se obtiene la fecha en ConsultaTiempo:

• Paso 1: Referencia a ConsultaAEMET.
• Paso 2: Quitando las 3 filas superiores.
• Paso 3: Quitando las filas en blanco.
• Paso 4: Promoviendo los encabezados de la primera fila.
• Paso X: Los siguientes pasos dividen las columnas con valor y hora entre paréntesis en dos columnas separadas.

No voy a explicar todos los pasos, pues son muchos, aunque se pueden ver en el editor avanzado del lenguaje M de Power Query:

let Origen = ConsultaAEMET, #"Filas superiores quitadas" = Table.Skip(Origen,3), #"Filas en blanco eliminadas" = Table.SelectRows(#"Filas superiores quitadas", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))), #"Encabezados promovidos" = Table.PromoteHeaders(#"Filas en blanco eliminadas", [PromoteAllScalars=true]), #"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"Precipitación 00-24h (mm)", type number}}), #"Dividir columna por delimitador" = Table.SplitColumn(#"Tipo cambiado", "Temperatura máxima (ºC)", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Temperatura máxima (ºC).1", "Temperatura máxima (ºC).2"}), #"Tipo cambiado1" = Table.TransformColumnTypes(#"Dividir columna por delimitador",{{"Estación", type text}, {"Provincia", type text}, {"Temperatura máxima (ºC).1", type number}, {"Temperatura máxima (ºC).2", type text}}), #"Columnas con nombre cambiado" = Table.RenameColumns(#"Tipo cambiado1",{{"Temperatura máxima (ºC).1", "Temperatura máxima (ºC)"}, {"Temperatura máxima (ºC).2", "Hora máxima"}}), #"Valor reemplazado" = Table.ReplaceValue(#"Columnas con nombre cambiado","(","",Replacer.ReplaceText,{"Hora máxima"}), #"Valor reemplazado1" = Table.ReplaceValue(#"Valor reemplazado",")","",Replacer.ReplaceText,{"Hora máxima"}), #"Tipo cambiado2" = Table.TransformColumnTypes(#"Valor reemplazado1",{{"Hora máxima", type time}}), #"Dividir columna por delimitador1" = Table.SplitColumn(#"Tipo cambiado2", "Temperatura mínima (ºC)", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Temperatura mínima (ºC).1", "Temperatura mínima (ºC).2"}), #"Tipo cambiado3" = Table.TransformColumnTypes(#"Dividir columna por delimitador1",{{"Temperatura mínima (ºC).1", type number}, {"Temperatura mínima (ºC).2", type text}}), #"Columnas con nombre cambiado1" = Table.RenameColumns(#"Tipo cambiado3",{{"Temperatura mínima (ºC).1", "Temperatura mínima (ºC)"}}), #"Valor reemplazado2" = Table.ReplaceValue(#"Columnas con nombre cambiado1","(","",Replacer.ReplaceText,{"Temperatura mínima (ºC).2"}), #"Valor reemplazado3" = Table.ReplaceValue(#"Valor reemplazado2",")","",Replacer.ReplaceText,{"Temperatura mínima (ºC).2"}), #"Tipo cambiado4" = Table.TransformColumnTypes(#"Valor reemplazado3",{{"Temperatura mínima (ºC).2", type time}}), #"Columnas con nombre cambiado2" = Table.RenameColumns(#"Tipo cambiado4",{{"Temperatura mínima (ºC).2", "Hora mínima"}}), #"Dividir columna por delimitador2" = Table.SplitColumn(#"Columnas con nombre cambiado2", "Racha (km/h)", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Racha (km/h).1", "Racha (km/h).2"}), #"Tipo cambiado5" = Table.TransformColumnTypes(#"Dividir columna por delimitador2",{{"Racha (km/h).1", Int64.Type}, {"Racha (km/h).2", type text}}), #"Columnas con nombre cambiado3" = Table.RenameColumns(#"Tipo cambiado5",{{"Racha (km/h).1", "Racha (km/h)"}}), #"Valor reemplazado4" = Table.ReplaceValue(#"Columnas con nombre cambiado3","(","",Replacer.ReplaceText,{"Racha (km/h).2"}), #"Valor reemplazado5" = Table.ReplaceValue(#"Valor reemplazado4",")","",Replacer.ReplaceText,{"Racha (km/h).2"}), #"Tipo cambiado6" = Table.TransformColumnTypes(#"Valor reemplazado5",{{"Racha (km/h).2", type time}}), #"Columnas con nombre cambiado4" = Table.RenameColumns(#"Tipo cambiado6",{{"Racha (km/h).2", "Hora racha"}}), #"Dividir columna por delimitador3" = Table.SplitColumn(#"Columnas con nombre cambiado4", "Velocidad máxima (km/h)", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Velocidad máxima (km/h).1", "Velocidad máxima (km/h).2"}), #"Tipo cambiado7" = Table.TransformColumnTypes(#"Dividir columna por delimitador3",{{"Velocidad máxima (km/h).1", Int64.Type}, {"Velocidad máxima (km/h).2", type text}}), #"Columnas con nombre cambiado5" = Table.RenameColumns(#"Tipo cambiado7",{{"Velocidad máxima (km/h).1", "Velocidad máxima (km/h)"}}), #"Valor reemplazado6" = Table.ReplaceValue(#"Columnas con nombre cambiado5","(","",Replacer.ReplaceText,{"Velocidad máxima (km/h).2"}), #"Valor reemplazado7" = Table.ReplaceValue(#"Valor reemplazado6",")","",Replacer.ReplaceText,{"Velocidad máxima (km/h).2"}), #"Columnas con nombre cambiado6" = Table.RenameColumns(#"Valor reemplazado7",{{"Velocidad máxima (km/h).2", "Hora Velocidad máxima"}}), #"Tipo cambiado8" = Table.TransformColumnTypes(#"Columnas con nombre cambiado6",{{"Hora Velocidad máxima", type time}}) in #"Tipo cambiado8"

Esta consulta se carga en la tabla con la ConsultaTiempo, con los datos climáticos necesarios para el mapa y con el tipo de dato adecuado.

En esta tabla están los datos climáticos de un día:

• Estación: cada una de las estaciones meteorológicas.
• Provincia: cada una de las provincias.
• Temperatura máxima (ºC)
• Hora máxima: con la hora de la temperatura máxima.
• Temperatura mínima (ºC)
• Hora mínima: con la hora de la temperatura mínima.
• Temperatura media (ºC)
• Racha (km/h)
• Hora racha: con la hora de la racha de viento.
• Velocidad máxima (km/h)
• Hora Velocidad máxima: con la hora de la velocidad máxima del viento.
• Precipitación 00-24h (mm)

Pregunta 13: ¿Cómo resumir los datos por provincia?

Respuesta 13: Con una tabla dinámica

La tabla anterior, obtenida con Power Query, se resume con una tabla dinámica en una nueva hoja de Excel denominada: TD_Tiempos, para obtener los valores máximos y la temperatura mínima de cada provincia en un día.

En el área de Filas se coloca la Provincia y los demás campos se resumen en el área de Valores:

• Temperatura máxima (ºC): como máximo.
• Temperatura mínima (ºC): como máximo.
• Temperatura mínima (ºC): como mínimo.
• Racha (km/h): como máximo.
• Velocidad máxima (km/h): como máximo.
• Precipitación 00-24h (mm): como máximo.

El resultado es la siguiente tabla con máximos y mínimos de datos climáticos por provincia:

Pregunta 14: ¿Cómo transformo los datos climáticos?

Respuesta 14: Mediante una tabla auxiliar

En la hoja 'Tiempo' se ha diseñado una tabla auxiliar, denominada TablaTiempo, con los datos climáticos transformados, para que cada dato climático de una provincia en el día seleccionado incluya su valor máximo o mínimo, su hora y su estación meteorológica, excepto para las precipitaciones que son durante las 24 horas de un día, por lo que no llevan hora.

Los cálculos se hacen con los datos de la tabla ConsultaTiempo y con los campos de la tabla dinámica resumida anteriormente en la hoja 'TD_Tiempos', gracias a la función: IMPORTARDATOSDINAMICOS.

Esta tabla tiene 52 filas, una por cada provincia, y 26 columnas:

• Fecha: Con la fórmula en A2:

=SI.ERROR(FECHANUMERO('ConsultaFecha'!$B$4);FECHANUMERO(EXTRAE('ConsultaFecha'!$B$3;1;ENCONTRAR(" a las";'ConsultaFecha'!$B$3))))

y en A3 y siguientes: =$A$2

• Provincia: Lista de provincias con el nombre que aparece en el mapa.

• Provincia2: Lista de provincias con el nombre en AEMET.

• Máxima (ºC):

=IMPORTARDATOSDINAMICOS("Máx. de Temperatura máxima (ºC)";TD_Tiempos!$A$1;"Provincia";[@Provincia2])

• Fila Máxima:

=MAX(([@Provincia2]=ConsultaTiempo[Provincia])*([@[Máxima (ºC)]]=ConsultaTiempo[Temperatura máxima (ºC)])*FILA(ConsultaTiempo[Provincia]))-1

• Estación Máxima:

=INDICE(ConsultaTiempo[Estación];[@[Fila Máxima]])

• Hora Máxima:

=INDICE(ConsultaTiempo[Hora máxima];[@[Fila Máxima]])

• Mínima máx. (ºC):

=IMPORTARDATOSDINAMICOS("Máx. de Temperatura mínima (ºC)";TD_Tiempos!$A$1;"Provincia";[@Provincia2])

• Fila Mínima máx.:

=MAX(([@Provincia2]=ConsultaTiempo[Provincia])*([@[Mínima máx. (ºC)]]=ConsultaTiempo[Temperatura mínima (ºC)])*FILA(ConsultaTiempo[Provincia]))-1

• Estación Mínima máx.:

=INDICE(ConsultaTiempo[Estación];[@[Fila Mínima máx.]])

• Hora Mínima máx.:

=INDICE(ConsultaTiempo[Hora mínima];[@[Fila Mínima máx.]])

• Mínima (ºC):

=IMPORTARDATOSDINAMICOS("Mín. de Temperatura mínima (ºC)";TD_Tiempos!$A$1;"Provincia";[@Provincia2])

• Fila Mínima:

=MAX(([@Provincia2]=ConsultaTiempo[Provincia])*([@[Mínima (ºC)]]=ConsultaTiempo[Temperatura mínima (ºC)])*FILA(ConsultaTiempo[Provincia]))-1

• Estación Mínima:

=INDICE(ConsultaTiempo[Estación];[@[Fila Mínima]])

• Hora Mínima:

=INDICE(ConsultaTiempo[Hora mínima];[@[Fila Mínima]])

• Racha (km/h):

=IMPORTARDATOSDINAMICOS("Máx. de Racha (km/h)";TD_Tiempos!$A$1;"Provincia";[@Provincia2])

• Fila Racha:

=MAX(([@Provincia2]=ConsultaTiempo[Provincia])*([@[Racha (km/h)]]=ConsultaTiempo[Racha (km/h)])*FILA(ConsultaTiempo[Provincia]))-1

• Estación Racha:

=INDICE(ConsultaTiempo[Estación];[@[Fila Racha]])

• Hora Racha:

=INDICE(ConsultaTiempo[Hora racha];[@[Fila Racha]])

• Velocidad máxima (km/h):

=IMPORTARDATOSDINAMICOS("Máx. de Velocidad máxima (km/h)";TD_Tiempos!$A$1;"Provincia";[@Provincia2])

• Fila Velocidad:

=MAX(([@Provincia2]=ConsultaTiempo[Provincia])*([@[Velocidad máxima (km/h)]]=ConsultaTiempo[Velocidad máxima (km/h)])*FILA(ConsultaTiempo[Provincia]))-1

• Estación Velocidad:

=INDICE(ConsultaTiempo[Estación];[@[Fila Velocidad]])

• Hora Velocidad:

=INDICE(ConsultaTiempo[Hora Velocidad máxima];[@[Fila Velocidad]])

• Precipitación (mm):

=IMPORTARDATOSDINAMICOS("Máx. de Precipitación 00-24h (mm)";TD_Tiempos!$A$1;"Provincia";[@Provincia2])

• Fila Precipitación:

=SI([@[Precipitación (mm)]]=0;0;MAX(([@Provincia2]=ConsultaTiempo[Provincia])*([@[Precipitación (mm)]]=ConsultaTiempo[Precipitación 00-24h (mm)])*FILA(ConsultaTiempo[Provincia]))-1)

• Estación Precipitación:

=SI([@[Fila Precipitación]]=0;"";INDICE(ConsultaTiempo[Estación];[@[Fila Precipitación]]))

Este es el resultado de la tabla TablaTiempo en la hoja 'Tiempo':

Pregunta 15: ¿Cómo extraer solo los datos climáticos?

Respuesta 15: Con una consulta en Power Query

La tabla anterior se abre en Power Query seleccionando en la cinta de opciones: Datos > Obtener datos > De otras fuentes > De una tabla o rango

Con lo que se muestra en el Editor de Power Query:

Estos son los pasos aplicados:

• Paso 1: Se extrae la tabla con: = Excel.CurrentWorkbook(){[Name="TablaTiempo"]}[Content]

• Paso 2: Se transforman los tipos de las columnas con:

= Table.TransformColumnTypes(Origen,{{"Fecha", type date}, {"Provincia", type text}, {"Provincia2", type text}, {"Máxima (ºC)", type number}, {"Fila Máxima", Int64.Type}, {"Estación Máxima", type text}, {"Hora Máxima", type time}, {"Mínima máx. (ºC)", type number}, {"Fila Mínima máx.", Int64.Type}, {"Estación Mínima máx.", type text}, {"Hora Mínima máx.", type time}, {"Mínima (ºC)", type number}, {"Fila Mínima", Int64.Type}, {"Hora Mínima", type time}, {"Racha (km/h)", Int64.Type}, {"Fila Racha", Int64.Type}, {"Velocidad máxima (km/h)", Int64.Type}, {"Fila Velocidad", Int64.Type}, {"Fila Precipitación", Int64.Type}, {"Hora Racha", type time}, {"Hora Velocidad", type time}, {"Precipitación (mm)", type number}, {"Estación Velocidad", type text}, {"Estación Precipitación", type text}, {"Estación Racha", type text}, {"Estación Mínima", type text}})

• Paso 3: Se quitan las columnas con los números de fila:

= Table.RemoveColumns(#"Tipo cambiado",{"Fila Máxima", "Fila Mínima máx.", "Fila Mínima", "Fila Racha", "Fila Velocidad", "Fila Precipitación"})

Esta consulta TablaTiempo se guarda como solo conexión. Más adelante se anexará a una nueva tabla con el historial del tiempo meteorológico de varios días...

Pregunta 16: ¿Cómo pasar "miFecha" a Power Query?

Respuesta 16: Con una referencia a Excel

Se abre el Editor de Power Query desde la cinta de opciones con: Datos > Obtener datos > Iniciar Editor de Power Query...

Se selecciona: Nuevo origen > Otros orígenes > Consulta en blanco

y se cambia el nombre de la consulta: ExtraeFecha

En la ventana de fórmulas se escribe:

= Excel.CurrentWorkbook(){[Name="miFecha"]}[Content]{0}[Column1]

Se crea un segundo paso con la fórmula:

= DateTime.Date(Origen)

O seleccionando en la pestaña: Transformar > Fecha > Solo fecha, con lo que la fórmula será:

= Date.From(Origen)

Con lo que se obtiene la fecha elegida para la consulta del tiempo en la celda E2 (miFecha) de la hoja 'ConsultaFecha':

Pregunta 17: ¿Cómo crear un historial del clima?

Respuesta 17: Con una tabla de historial del tiempo

Como la consulta del tiempo es de un día, hace falta crear una tabla con el historial del tiempo meteorológico de varios días.

La nueva tabla HistorialTiempo se crea en la hoja 'HistorialTiempo', con las mismas columnas que la consulta solo conexión que hemos creado antes en Power Query: TablaTiempo

Esta tabla contendrá los datos climáticos de cada provincia para cada día en que se hayan consultado datos.

Pregunta 18: ¿Cómo anexar el tiempo de varias fechas?

Respuesta 18: Anexando filas con Power Query

Solo queda concatenar las filas de la tabla anterior HistorialTiempo con la consulta solo conexión TablaTiempo en la tabla con el historial del tiempo HistorialTiempo, anexando cada consulta que se haga al historial según la fecha del tiempo.

La tabla anterior HistorialTiempo se abre en Power Query seleccionando en la cinta de opciones: Datos > Obtener datos > De otras fuentes > De una tabla o rango

Con lo que se muestra en el Editor de Power Query:

Estos son los pasos aplicados:

• Paso 1: Se extrae la tabla con:

= Excel.CurrentWorkbook(){[Name="HistorialTiempo"]}[Content]

• Paso 2: Se transforman los tipos de las columnas con:

= Table.TransformColumnTypes(Origen,{{"Fecha", type date}, {"Provincia", type text}, {"Provincia2", type text}, {"Máxima (ºC)", type number}, {"Estación Máxima", type text}, {"Hora Máxima", type time}, {"Mínima máx. (ºC)", type number}, {"Estación Mínima máx.", type text}, {"Hora Mínima", type time}, {"Hora Mínima máx.", type time}, {"Mínima (ºC)", type number}, {"Estación Mínima", type text}, {"Racha (km/h)", Int64.Type}, {"Estación Racha", type text}, {"Hora Racha", type time}, {"Velocidad máxima (km/h)", Int64.Type}, {"Estación Velocidad", type text}, {"Hora Velocidad", type time}, {"Precipitación (mm)", type number}, {"Estación Precipitación", type text}})

• Paso 3: Se filtran las filas para quitar las de la fecha de la consulta ExtraeFecha con:

= Table.SelectRows(#"Tipo cambiado", each [Fecha] <> ExtraeFecha)

• Paso 4: Se anexa la TablaTiempo a esta consulta con:

= Table.Combine({#"Filas filtradas", TablaTiempo})

Este último paso se inserta desde el menú: Inicio > Combinar > Anexar consultas

Si en el historial existían filas con la fecha de la consulta, primero se filtran y luego se añaden las filas para esa fecha con los datos climáticos actualizados. Esto es así porque AEMET va actualizando los datos de los días anteriores progresivamente cuando tiene datos rigurosos de todas las estaciones meteorológicas, lo que puede ser al cabo de unos días.

Pregunta 19: ¿Cómo cargar el historial del tiempo en el mapa?

Respuesta 19: Con una tabla dinámica

Los datos históricos del clima español se conectan a la plantilla con el mapa del tiempo mediante una tabla dinámica.

Ya está explicado en la sección: Cómo actualizar los datos climáticos de este artículo:

Mapa del clima español | #ExcelPedroWave

por lo que no lo explicaré aquí. El resultado es la hoja 'Tiempo' con la tabla dinámica con el historial del tiempo:

Pregunta 20: ¿Cómo visualizar el tiempo en el mapa?

Respuesta 20: Con una mapa coroplético

La explicación vuelve a estar en este artículo desde el que se puede descargar el mapa:

Mapa del clima español | #ExcelPedroWave

y se puede ver en acción el mapa interactivo del clima español: 

Descarga de la consulta del tiempo AEMET

Puedes descargar la consulta del tiempo AEMET v1, con datos climáticos desde el 15 de junio de 2022 hasta ayer, desde este enlace:

• 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

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

Para usarla seleccionar un día en la celda E2 de la hoja 'ConsultaFecha' y en la cinta de opciones seleccionar: Datos > Actualizar todo, o la combinación de teclas: Control + Alt + F5

Fin de este extenso análisis de datos climáticos. Si ves que ha quedado algo pendiente de explicar, puedes hacer un comentario.

Tráiler del clima español con Power Query

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

Este es un avance de un extenso artículo que publicaré mañana, en el que explico cómo extraer, transformar y cargar con Power Query los datos climáticos de España.

Información elaborada utilizando, entre otras, la obtenida de la Agencia Estatal de Meteorología - AEMET.

Ruleta de preguntas y respuestas para el análisis de datos

Pulsa la flecha para pasar de página: ➡️

Estas 20 preguntas y sus 20 respuestas las explico detalladamente en este extenso artículo, que no te puedes perder:

Datos climáticos con Power Query | #ExcelPedroWave

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!