Gráficos de copas en Excel

🔝Select the language of this blog post in the Google box at the top left.

🍻 Os dejo este ejercicio para el verano: ¿Cómo hacer un gráfico de copas en Excel?

AVISO: Ya puedes descargar el gráfico de copas al final de este artículo.

Si no sabes cómo hacerlo, mira el vídeo y aprenderás:

🍷 ¡Con estas copas brindo a vuestra salud! 🍺

🏖️ ¡Felices vacaciones de verano para quien las tenga! 🌊

Descarga del gráfico de copas

Si aún no has hecho el ejercicio, siguiendo las explicaciones del vídeo, ya puedes descargar este gráfico de copas desde estos enlaces:

• Microsoft OneDrive: Gráficos de copas - PW1.xlsx

• Sites Google Drive: Gráficos de copas - PW1.xlsx

Abre la plantilla y presiona el botón: Habilitar edición

Gráfico con máximas climáticas

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

Para saber qué provincias españolas alcanzaron más temperatura durante las olas de calor de este verano no vale con el gráfico del artículo anterior, que puedes descargar desde aquí:

Gráfico del clima en España | #ExcelPedroWave

Hace falta añadir al gráfico los valores máximos de cada provincia durante un período seleccionado de fechas:

Para ello he añadido un nuevo cuadro combinado (ComboBox) con el que seleccionar las provincias con los TOP de valores máximos climáticos:

• NO TOP: Se obtiene el mismo gráfico del anterior artículo, seleccionando las autonomías o provincias en la segmentación de datos.
• TOP 1: Se obtiene la provincia con los valores climáticos máximos en los días seleccionados.
• TOP 2: Se obtienen las 2 provincias con valores máximos.
• TOP 3: Se obtienen las 3 provincias con valores máximos.
• TOP 4: Se obtienen las 4 provincias con valores máximos.
• TOP 5: Se obtienen las 5 provincias con valores máximos.
• TOP 10: Se obtienen las 10 provincias con valores máximos.

Al seleccionar cualquiera de los gráficos, TOP 1 a TOP 10, aparecerán las provincias con los valores máximos de hasta 14 días seleccionados en la escala de tiempo, sin que interactúen las segmentaciones de datos de autonomías y provincias.

En este artículo solamente explicaré los cambios que he hecho al gráfico del artículo anterior para conseguir obtener las provincias con los valores TOP.

Vídeo de los gráficos climáticos

En este vídeo presento estos gráficos con datos del clima en España.

Tablas dinámica con valores Top del clima

En la hoja 'TOP' he incluido 6 tablas dinámicas con las que se obtienen 6 listas de provincias ordenadas por cada uno de los valores máximos climáticos durante el período de días seleccionados, excepto para las temperaturas mínimas para las que se obtienen sus valores mínimos.

Pasos a seguir al insertar cada una de las tablas dinámicas:

1) Como filas de la tabla dinámica se escogen:

• 50 Provincias + 2 CA
• Autonomías
• Fecha

2) Como sumatorio de cada una de las tablas dinámicas se escoge uno de los tipos climáticos:

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

Por ejemplo, para obtener las temperaturas máximas los campos de la primera tabla dinámica son los de la imagen de la derecha.

3) En la cinta de opciones se selecciona: Diseño > Diseño de informe > Mostrar en formato tabular

4) En la configuración de los campos de 50 Provincias + 2 CA y de Autonomías, se marca: Repetir etiquetas de elementos

5) Seleccionar cualquier valor de 50 Provincias + 2 CA

6) En la cinta de opciones seleccionar: Inicio > Ordenar y filtrar > Orden personalizado...

7) Seleccionar Descendente (Z a A) por: Máxima (ºC) o cualquier otro tipo de dato climático, excepto para las temperaturas mínimas que hay que seleccionar Ascendente (A a Z) por: Mínima (ºC)

Estas son las 3 primeras tablas dinámicas obtenidas:

Tabla auxiliar con las provincias

En la hoja 'Provincias' he modificado las fórmulas para referenciarlas a las filas de las 6 tablas dinámicas de la hoja 'TOP'.

Para ello he creado un nuevo nombre definido: miNúmTOP, con la fórmula:

=COINCIDIR(miTOP;Gráfico!$S$3:$S$9;0)

Por ejemplo, para las temperaturas máximas en la celda N4 la fórmula es:

=SI(miNúmTOP=1;SI(Tiempo!$A2="";"";Tiempo!D2);SI(TOP!$A2="";"";TOP!D2))

Las provincias y autonomías se calculan arrastrando al rango A4:B143 la fórmula de la celda A4:

=SI(miNúmTOP=1;SI(Tiempo!A2>0;Tiempo!A2 & "";"");

ELEGIR(miNúmTiempo;

SI(TOP!A2>0;TOP!A2 & "";"");

SI(TOP!F2>0;TOP!F2 & "";"");

SI(TOP!K2>0;TOP!K2 & "";"");

SI(TOP!P2>0;TOP!P2 & "";"");

SI(TOP!U2>0;TOP!U2 & "";"");

SI(TOP!Z2>0;TOP!Z2 & "";"")))

La fecha se calcula arrastrando al rango C4:C143 la fórmula de la celda C4:

=--SI(miNúmTOP=1;SI(Tiempo!C2>0;Tiempo!C2 & "";NOD());

ELEGIR(miNúmTiempo;

SI(TOP!C2>0;TOP!C2 & "";NOD());

SI(TOP!H2>0;TOP!H2 & "";NOD());

SI(TOP!M2>0;TOP!M2 & "";NOD());

SI(TOP!R2>0;TOP!R2 & "";NOD());

SI(TOP!W2>0;TOP!W2 & "";NOD());

SI(TOP!AB2>0;TOP!AB2 & "";NOD())))

Hoja 'Gráfico'

En la hoja 'Gráfico' he añadido un cuadro combinado (ComboBox) para elegir el tipo de TOP.

En el rango oculto S3:S9 están los posibles valores: NO TOP; TOP 1 a TOP 5 y TOP 10, como se explicó al principio de este artículo.

En la celda S2, con el nombre definido miTOP, está el valor elegido. Las columnas Q y siguientes se ocultan.

Por ejemplo, este es el gráfico del TOP5 de temperaturas mínimas entre los días 12 y 25 de julio:

Zamora tuvo la temperatura mínima durante esos 14 días y se muestran sus etiquetas por ser la primera provincia de la lista. No se han añadido etiquetas a las otras 9 series del gráfico porque serían imposibles de leer al superponerse unas con otras.

Descarga del gráfico con valores TOP climáticos

Descarga el gráfico TOP climático de España desde estos enlaces:

• Microsoft OneDrive: Gráfico Clima España PW2.xlsx

• Sites Google Drive: Gráfico Clima España PW2.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

IMPORTANTE: Este archivo contiene datos climáticos de 6 meses, desde el 15 de junio al 16 de diciembre de 2022. Para actualizar los datos climáticos del gráfico a partir de esa fecha hay que descargar el archivo con los datos actualizados desde este artículo:

Datos climáticos con Power Query | #ExcelPedroWave

Para actualizar los datos hay que tener los 2 archivos abiertos en la misma carpeta y, en la cinta de opciones del archivo con el gráfico, seleccionar: Datos > Actualizar todo, o la combinación de teclas: Control + Alt + F5

Cuéntame si necesitas ayuda para adaptar este gráfico y para analizar los datos climáticos extremos registrados en España durante este verano, que han provocado varias olas de calor récord en Europa.

Gráfico del clima en España

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

Este mes de julio lo estoy dedicando a generar datos, mapas y gráficos de las olas de calor de junio y julio en España, aunque primordialmente quiero que sirva para enseñar técnicas y trucos en Excel.

Estos son los artículos sobre datos climáticos:

1. Cómo obtener datos climáticos: Datos climáticos con Power Query | #ExcelPedroWave
2. Cómo crear un mapa coroplético: Mapa del clima español | #ExcelPedroWave
3. Cómo crear un primer gráfico (este artículo): Gráfico del clima en España | #ExcelPedroWave
4. Cómo crear un segundo gráfico con el TOP TEN de datos climáticos: Gráfico con máximas climáticas | #ExcelPedroWave

En este artículo explicaré, paso a paso, cómo crear un gráfico con datos climáticos como el de la imagen:

En este gráfico interactivo se pueden ver los valores climáticos de hasta 10 provincias durante un máximo de 14 días.

Se puede elegir:

• El tipo de dato climático: Máximas; Mínimas máximas; Mínimas; Rachas; Velocidad del viento; Precipitación.
• Una o varias autonomías con una segmentación de datos.
• Una o varias provincias con una segmentación de datos.
• De 1 a 14 días con una escala de tiempo.

Vídeo de los gráficos climáticos

En este vídeo presento los gráficos con datos del clima en España.

Origen de los datos climáticos

En el anterior artículo expliqué cómo extraer, transformar y cargar con Power Query en Excel los datos del clima español:

Datos climáticos con Power Query | #ExcelPedroWave

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

Crear tabla de Autonomías

1) Crear un libro nuevo en blanco para el gráfico.

2) Crear la hoja de 'Autonomías'.

3) Crear la tabla de Autonomías con sus provincias en 52 filas, 50 filas para las provincias de cada autonomía y 2 filas para las ciudades autónomas de Ceuta y Melilla.

Los datos se han obtenido de esta página:

Provincia (España) - Wikipedia, la enciclopedia libre

Crear tabla dinámica con valores climáticos

Pasos para crear una tabla dinámica con los datos del clima:

1) Abrir el archivo con el origen de los datos climáticos: Consulta Tiempo AEMET - PW1.xlsx

2) Crear la hoja 'Tiempo' en el libro nuevo.

3) Insertar una tabla dinámica desde la cinta de opciones: Insertar > Tabla dinámica > De una tabla o rango

4) Tabla o rango: 'Consulta Tiempo AEMET - PW1.xlsx'!HistorialTiempo

5) Hoja de cálculo existente. Ubicación: Tiempo!$A$1

6) Importante: Para poder analizar varias tablas con Power Pivot en Excel, marque: Agregar estos datos al Modelo de datos

7) Seleccionar en la cinta de opciones: Analizar tabla dinámica > Cálculos > Relaciones

8) En la ventana Administrar relaciones, presione el botón: Nuevo

9) En la ventana Crear relación, seleccionar:

• Tabla de modelo de datos: HistorialTiempo. Columna: Provincia
• Tabla de hoja de cálculo: TablaAutonomías. Columna: 50 Provincias + 2 CA

10) Presionar el botón: Aceptar, con lo que se crea la relación entre las dos tablas en el modelo de datos en Power Pivot.

11) Presionar el botón: Cerrar

12) En Campos de la tabla dinámica, en la sección Todas, seleccionar los dos campos de la TablaAutonomías en este orden:

1. 50 Provincias + 2 CA
2. Autonomías

13) En la cinta de opciones seleccionar: Diseño > Diseño del informe > Mostrar en formato tabular

14) En la cinta de opciones desmarcar botones en: Analizar tabla dinámica > Mostrar > Botones

15) Añadir el campo Fecha de la tabla HistorialTiempo en el área de Filas.

16) Seleccionar la celda C2 y seleccionar en la cinta de opciones: Analizar tabla dinámica > Grupo > Desagrupar

17) Seleccionar la celda A2 y seleccionar en la cinta de opciones: Analizar tabla dinámica > Campo activo > Configuración de campo

18) En la ventana de Configuración de campo, seleccionar la pestaña Diseño e impresión y marcar: Repetir etiquetas de elementos y presionar el botón: Aceptar

19) Repetir los pasos anteriores con la celda B2 de Autonomías, con lo que resulta lo siguiente:

20) Seleccionar los campos con valores climáticos de la tabla HistorialTiempo, que irán al área de sumario de valores:

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

21) Seleccionar cada vez una celda en la fila 2 del rango de columnas D:I, seleccionando en la cinta de opciones: Analizar tabla dinámica > Campo activo > Configuración de campo

22) Resumir campo de valor por Máx. Para las temperaturas mínimas resumir por Mín.

Con lo que hemos diseñado una tabla dinámica con los valores climáticos necesarios para crear el gráfico del clima.

Crear filtros de fechas, provincias y autonomías

En la tabla dinámica insertada en la hoja 'Tiempo' hay que crear 3 filtros:

• Filtro con una escala de tiempos para filtrar las fechas.
• Filtro con una segmentación de datos para filtrar las provincias.
• Filtro con una segmentación de datos para filtrar las autonomías.

1) Teniendo seleccionada una celda de la tabla dinámica, en la cinta de opciones seleccionar: Analizar tabla dinámica > Insertar escala de tiempo

2) En la tabla HistorialTiempo, seleccionar: Fecha, y presionar el botón: Aceptar, con lo que se inserta la escala de tiempo por defecto para la Fecha:

3) En la cinta de opciones seleccionar: Escala de tiempo

4) Cambiar el nivel de tiempo, de MESES a DÍAS.

5) En la sección Mostrar desmarcar las 4 casillas: Encabezado; Barra de desplazamiento; Etiqueta de selección y Nivel de tiempo.

6) Ajustar para que se vean 14 días y seleccionarlos.

7) Cambiar el estilo de la escala de tiempo a gusto del usuario.

8) Teniendo seleccionada una celda de la tabla dinámica, en la cinta de opciones seleccionar: Analizar tabla dinámica > Insertar Segmentación de datos

9) En la tabla de autonomías, seleccionar los dos campos, de Provincias y de Autonomías, y presionar el botón: Aceptar

10) Ajustar las segmentaciones de datos.

11) Cambiar el estilo de las segmentaciones al gusto del usuario.

12) Mover los 3 filtros a una nueva hoja denominada 'Gráfico'.

Crear 4 tablas auxiliares

En la hoja 'Tiempo' hay que crear 4 tablas auxiliares:

1. Una tabla dinámica con la lista de provincias filtradas.
2. Una tabla para limitar el tamaño de la tabla dinámica de provincias.
3. Una tabla dinámica con la lista de fechas filtradas. 
4. Una tabla para limitar el tamaño de la tabla dinámica de fechas.

1. Crear tabla dinámica con la lista de provincias filtradas:

1) Seleccionar la celda Tiempo!K1

2) En la cinta de opciones, seleccionar: Insertar > Tablas > Tabla dinámica > Desde modelo de datos

3) Seleccionar en TablaAutonomías: 50 Provincias + 2 CA

4) Cambiar nombre de la tabla dinámica: TablaDinámicaProvincias

5) En la cinta de opciones seleccionar: Diseño > Diseño del informe > Mostrar en formato tabular

6) En la cinta de opciones seleccionar: Analizar tabla dinámica > Filtrar > Conexiones de filtro

8) Marcar el filtro de Provincias y el de Autonomías, y presionar el botón: Aceptar

9) En la cinta de opciones seleccionar: Diseño > Totales generales > Desactivado para filas y columnas 

10) Seleccionar una única autonomía en la segmentación de Autonomías de la hoja 'Gráfico'.

2. Crear tabla para limitar el tamaño de la tabla dinámica de provincias:

1) Crear una tabla en el rango K12:K13 con el nombre TablaFinProvincias.

2) La celda K13 con la fórmula:  =CONTARA($K$2:$K$11)

3. Crear una tabla dinámica con la lista de fechas filtradas:

1) Copiar la tabla dinámica de K1 en L1

2) Agregar al área de Filas el campo Fecha.

3) Quitar el campo 50 Provincias + 2 CA.

4) Cambiar el nombre de la tabla dinámica: TablaDinámicaFechas

5) En la cinta de opciones seleccionar: Analizar tabla dinámica > Filtrar > Conexiones de filtro

6) Marcar el filtro de Fecha y presionar el botón: Aceptar 

4. Crear tabla para limitar el tamaño de la tabla dinámica de fechas:

1) Crear una tabla en el rango L16:L17 con el nombre TablaFinFechas.

2) La celda L17 con la fórmula:  =CONTARA($L$2:$L$15) 

El resultado son 4 tablas en la hoja 'Tiempo' con las que se consigue limitar en las segmentaciones de datos el número máximo de provincias seleccionadas a 10 y el número máximo de días a 14.

Si se intenta seleccionar más de 10 provincias con las segmentaciones de Autonomía o Provincia, o más de 14 días en la escala de tiempo, las tablas auxiliares lo impiden mostrando este aviso:

Datos climáticos por provincia

La tabla dinámica con los datos climáticos hay que transformarla antes de crear el gráfico climático, para lo que hay que seguir estos pasos:

1) Crear una nueva hoja denominada: Provincias

2) En la celda D1 escribir la fórmula: =TRANSPONER(Tiempo!$K$2:$K$11) & ""

3) En el rango A3:S3 escribir este encabezado y convertirlo en una tabla:

4) Cambiar el nombre de la tabla: TablaProvincias

5) En Diseño de tabla, marcar: Fila de totales

6) Cambiar el tamaño de la tabla: $A$3:$S$144, con lo que admite un máximo de 10 provincias x 14 días.

7) Seleccionar el rango: Provincias!N3:S3, creando el nombre definido: miTipoDatos

8) En la celda R3 de la hoja 'Gráfico' escribir la fórmula: =TRANSPONER(miTipoDatos)

9) En la celda R2 de la hoja 'Gráfico' crear una validación de datos con lista y origen: =$R$3:$R$8

10) En la celda Gráfico!R2 crear el nombre definido: miTiempo

11) Crear un nuevo nombre definido: miNúmTiempo con la fórmula:

=COINCIDIR(miTiempo;Gráfico!$R$3:$R$8;0)

12) Escribir fórmulas en las celdas:

• B1: =miTiempo
• A4: =SI(Tiempo!A2>0;Tiempo!A2 & "";"")
• B4: =SI(Tiempo!B2>0;Tiempo!B2 & "";"")
• C4: =SI(Tiempo!C2>0;Tiempo!C2;NOD()) y cambiar el formato de celda: dd mmm
• D4: =SI(Provincias!$A4=D$1;SI.ERROR(INDICE($N4:$S4;1;miNúmTiempo);NOD());NOD()) y arrastrar la fórmula en el rango D4:M143
• N4: =SI(Tiempo!$A2="";"";Tiempo!D2) y arrastrar la fórmula en el rango N4:S143
• C144: =MAX.SI.CONJUNTO([Fecha];[Fecha];">0") y convertirlo en fecha con el formato de celda: dd mmm
• D144: =SI(miNúmTiempo=3;MIN.SI.CONJUNTO([Datos1];[Datos1];">0");MAX.SI.CONJUNTO([Datos1];[Datos1];">0")) y arrastrar la fórmula al rango D144:M144
• N144: =SUBTOTALES(104;[Máximas (°C)]) y arrastrar la fórmula al rango N144:S144
• P144: =SUBTOTALES(105;[Mínimas (°C)])
• D2: =SI(D1="";"";D1 & " (" & TablaProvincias[[#Totales];[Datos1]] & ")") y arrastrar la fórmula al rango D2:M2

La hoja 'Provincias' tendrá una apariencia como ésta:

Cómo crear el gráfico climático

Para saber cómo insertar un cuadro combinado (ComboBox), con la lista desplegable de los tipos de datos climáticos, lee la sección: Tratamiento del mapa climático, en este artículo:

Mapa del clima español | #ExcelPedroWave

En la hoja 'Gráfico' se tendrán colocados los siguientes elementos interactivos:

• Un cuadro combinado para seleccionar el tipo de dato climático, que es una réplica exacta del desplegable en R2 que se ocultará...
• Una escala de tiempos para seleccionar hasta 14 días.
• Una segmentación de datos para seleccionar autonomías.
• Una segmentación de datos para seleccionar hasta 10 provincias.

Pasos para crear el gráfico climático:

1) En la hoja 'Provincias' seleccionar el rango combinado de los nombres de las series (C2:M2) y de los valores de las series (C4:M143). Para ello seguir estos pasos:

1. Seleccionar la hoja 'Provincias'.
2. Seleccionar la celda C2.
3. Mantener pulsada la tecla de Mayúsculas.
4. Presionar una vez la tecla Fin y una vez la tecla de cursor hacia la derecha.
5. Soltar la tecla de Mayúsculas.
6. Manteniendo pulsada la tecla Control, seleccionar con el ratón la celda C4.
7. Soltar la tecla Control.
8. Mantener pulsada la tecla de Mayúsculas.
9. Presionar una vez la tecla Fin y una vez la tecla de cursor hacia abajo.
10. Presionar una vez la tecla de cursor hacia arriba para no seleccionar la fila de totales. 
11. Soltar la tecla de Mayúsculas.
12. Mantener pulsada la tecla de Mayúsculas.
13. Presionar 10 veces la tecla de cursor hacia la derecha.

2) En la cinta de opciones seleccionar: Insertar > Gráfico recomendado

3) En la cinta de opciones seleccionar: Diseño de gráfico > Seleccionar datos, y comprobar que el Rango de datos del gráfico es: =Provincias!$C$2:$M$2;Provincias!$C$4:$M$143

4) En la cinta de opciones seleccionar: Diseño de gráfico > Cambiar tipo de gráfico, y seleccionar: Línea con marcadores

5) Situar el formato de la leyenda hacia la derecha.

6) En la cinta de opciones seleccionar: Diseño de gráfico > Cambiar colores, seleccionar: Paleta monocromática 2

7) Cortar el gráfico con la combinación de teclas: Control + X

8) Pegar el gráfico en la hoja 'Gráfico' con la combinación de teclas: Control + V

9) Quitar el borde exterior del gráfico.

10) Poner en negrita el título del gráfico, la leyenda y los ejes vertical y horizontal.

11) En la celda E2 escribir la fórmula: =SI(DERECHA(miTiempo;4)="(°C)";"Temperaturas ";"") & miTiempo

12) Seleccionar como título del gráfico la celda E2, que debe estar oculta detrás del gráfico.

13) En opciones de eje poner el límite mínimo a 30. Para aprender a hacerlo ver el vídeo de mas arriba.

14) Seleccionar la serie que aparece primera en la leyenda, en este caso: Alicante.

15) En la cinta de opciones seleccionar: Diseño de gráfico > Agregar elemento de gráfico > Etiqueta de datos > Encima

16) Poner en negrita las etiquetas de datos de Alicante.

17) En opciones de eje del límite mínimo presionar el botón: Restablecer. Para aprender a hacerlo ver el vídeo de mas arriba.

Este es el gráfico climático obtenido siguiendo los pasos anteriores:

NOTA: Entre paréntesis está la temperatura máxima alcanzada por cada provincia en los días seleccionados.

Si has leído hasta aquí habrás aprendido a hacerlo por ti mismo. Si no quieres aprender a hacerlo puedes descargar y probar el gráfico a continuación.

Descarga del gráfico climático

Descarga el gráfico climático de España desde estos enlaces:

• Microsoft OneDrive: Gráfico Clima España PW1.xlsx

• Sites Google Drive: Gráfico Clima España 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

IMPORTANTE: Este archivo contiene datos climáticos desde el 15 de junio hasta el 22 de julio. Para actualizar los datos climáticos del gráfico a partir de esa fecha hay que descargar el archivo con los datos actualizados desde este artículo:

Datos climáticos con Power Query | #ExcelPedroWave

Para actualizar los datos hay que tener los 2 archivos abiertos en la misma carpeta y, en la cinta de opciones del archivo con el gráfico, seleccionar: Datos > Actualizar todo, o la combinación de teclas: Control + Alt + F5

ATENCIÓN: En el siguiente artículo ampliaré la funcionalidad de este gráfico, añadiendo el TOP5 y el TOP10 de provincias con más valor climático, para tener el registro de temperaturas que han sufrido los españoles durante las olas de calor de junio y julio.

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 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

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.