#ExcelPedroWave

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

Este es el segundo artículo sobre mapas mundiales explicando cómo hacer:

• Mapa de países del mundo, compatible con Excel para Microsoft 365.

• Mapa moderno de continentes, compatible con versiones a partir de Excel 2019.

• Mapa antiguo de continentes, compatible con versiones a partir de Excel 2010.

Mapa moderno de continentes

En este segundo artículo voy a explicar cómo hacer un mapa con datos de población por continentes, con versiones a partir de Excel 2019 y hasta Excel para Microsoft 365. Por eso lo he denominado Mapa moderno, por ser compatible con las versiones más modernas de Excel.

Los datos de población de países del artículo anterior se podrían agrupar por continentes, aunque la agrupación puede ser problemática, ya que los datos de información geográfica obtenidos de Microsoft Bing, están actualizados en diferentes años para cada país, por lo que no se pueden sumar. Tampoco están actualizados recientemente, pues los datos más recientes son del año 2022.

Se trata de obtener un mapa de continentes con los datos actualizados a día de hoy y a la hora actual, a ser posible. Para ello hay que conectarse a una Web de la que se puedan obtener datos de población actualizados en tiempo real. Para ello usaremos Power Query, nuestra herramienta ETL (de Extracción, Transformación y Carga) por Excelencia.

Este es el aspecto del mapa de continentes con datos de población actualizados en tiempo real a petición del usuario:

Características del mapa de continentes:

• Es un mapa coroplético de países del mundo.

• A cada país se le asigna el valor de la población del continente.

• Filtro por continente con segmentación de datos:
• África
• América del Norte
• América del Sur
• Asia
• Europa
• Oceanía

• Filtro del tipo de datos de población con segmentación de datos:
• Población masculina actual
• Población femenina actual
• Población actual
• Nacimientos hoy
• Nacimientos este año
• Muertes hoy
• Muertes este año
• La migración neta hoy
• La migración neta este año
• Crecimiento poblacional hoy
• Crecimiento poblacional este año

• Extracción, transformación y carga de los datos de población por continente, mediante Power Query para Excel, desde la página Web:

Estadísticas en tiempo real. Reloj de la población para todos los países (countrymeters.info)

• Actualización de los datos de población por continente cuando lo requiera el usuario del mapa:
• Desde la cinta de opciones: Datos > Actualizar todo
• Presionando simultáneamente las teclas: Control + Alt + F5

• Fecha y hora actualizadas con una tabla dinámica en el instante de actualización de los datos de población.

Descarga del mapa de continentes

Este archivo solamente es compatible con versiones desde Excel 2019 hasta Excel para Microsoft 365.

Tiene la hoja protegida sin contraseña, para que los usuarios no destrocen las fórmulas, y no contiene macros.

Descarga la versión 1.0 desde uno de estos enlaces:

• Microsoft OneDrive: Mapa moderno de continentes - PW1.xlsx

• Sites Google Drive: Mapa moderno de continentes - PW1.xlsx

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

Cuando se muestre la ADVERTENCIA DE SEGURIDAD Se han deshabilitado las conexiones de datos externos, presiona el botón: Habilitar contenido

Vídeo: Mapa moderno de continentes

En este vídeo explico cómo hacer un mapa moderno de continentes para versiones desde Excel 2019 hasta Excel para Microsoft 365, con datos obtenidos con Power Query en un mapa coroplético.

Selecciona los subtítulos en tu idioma para entender el vídeo.

Si, como yo, prefieres las explicaciones escritas, sigue leyendo.

Cómo hacer el mapa moderno de continentes

PASO 1: Comienzo

Crear un libro de trabajo nuevo en Excel 2019, Excel 2021 o Excel para Microsoft 365.

PASO 2: Crear consultas en Power Query

Crear 6 consultas en Power Query, una por cada continente.

En la cinta de opciones:

Datos > Obtener datos > De la web

Introducir esta página web en Básico:

https://countrymeters.info/es/Africa

PASO 3: Obtener datos de la web

En la ventana del Navegador seleccionar la tabla:

Reloj de población de África

Y presionar el botón: Transformar datos

PASO 4: Modificar la consulta con el Editor avanzado

En el Editor de Power Query, ir al menú: Inicio > Editor avanzado

Sustituir el código de programación en lenguaje M, que aparece en la imagen de la derecha, por el código copiable de abajo:

let Origen = Web.Page(Web.Contents("https://countrymeters.info/es/Africa")), Data0 = Origen{0}[Data], #"Tipo cambiado" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}}), #"Columnas reordenadas" = Table.ReorderColumns(#"Tipo cambiado",{"Column2", "Column1"}), #"Columnas con nombre cambiado" = Table.RenameColumns(#"Columnas reordenadas",{{"Column2", "Concepto"}, {"Column1", "Valor"}}), #"Valor reemplazado" = Table.ReplaceValue(#"Columnas con nombre cambiado"," ","",Replacer.ReplaceText,{"Valor"}), #"Tipo cambiado1" = Table.TransformColumnTypes(#"Valor reemplazado",{{"Valor", Int64.Type}}) in #"Tipo cambiado1"

Y presionar el botón: Listo

PASO 5: Revisar la consulta

El resultado de la consulta para África será similar a esta imagen:

Con los 7 pasos aplicados en esta primera consulta hemos obtenido los datos del Reloj de población de África.

PASO 6: Duplicar la consulta

Hay que duplicar 5 veces la consulta de África para obtener los datos de población de los otros continentes.

PASO 7: Modificar nombre de la consulta y su origen

Poner el nombre de la segunda consulta duplicada: Reloj de población de América del Norte

En el primer PASO APLICADO con el Origen, modificar el final de la página web de la consulta con:

= Web.Page(Web.Contents("https://countrymeters.info/es/North_America"))

PASO 8: Repetir los pasos 6 y 7

Duplicar 4 veces más la consulta de África, cambiando su nombre y su origen.

Los nombres de las 6 consultas y las páginas web de sus orígenes serán:

• Reloj de población de África:
• https://countrymeters.info/es/Africa
• Reloj de población de América del Norte:
• https://countrymeters.info/es/North_America
• Reloj de población de América del Sur:
• https://countrymeters.info/es/South_America
• Reloj de población de Asia:
• https://countrymeters.info/es/Asia
• Reloj de población de Europa:
• https://countrymeters.info/es/Europe
• Reloj de población de Oceanía:
• https://countrymeters.info/es/Oceania

PASO 9: Cerrar y cargar las consultas

En Power Query solamente falta presionar el botón: Cerrar y cargar

Con lo que las 6 consultas serán copiadas a 6 tablas. Cada tabla en una hoja distinta.

PASO 10: Cambiar nombres de las hojas con las consultas

Modificar el nombre de las hojas, quitando: Reloj de población de , para dejar sólo el nombre del continente en las hojas de consultas.

Con lo que habrá 6 hojas con los nombres de los continentes:

• África
• América del Norte
• América del Sur
• Asia
• Europa
• Oceanía

En esas hojas están todos los datos de población necesarios para montar el mapa de continentes.

PASO 11: Tabla de continentes y países

Crear una hoja 'DATOS' con una tabla de países y sus continentes, denominada: TablaPaíses, a partir de la fila 3, ordenados por continente y país.

Los países de cada continente se obtienen aquí:

Portal:Países - Wikipedia, la enciclopedia libre

PASO 12: Tabla auxiliares de estadísticas y fechas

Crear una tabla denominada: TablaEstadísticas, con los tipos de estadísticas de población en el rango B207:B218

Crear una tabla con fecha y hora en el rango C207:C209, siendo las fórmulas:

• Fecha: =HOY()
• Hora: =AHORA()-HOY()

Insertar una tabla dinámica con origen en la tabla anterior de fecha y hora, en el rango C210:C212

PASO 13: Tabla dinámica de estadística de población

Insertar una tabla dinámica con origen en la tabla de tipos de estadísticas de población, en la celda C1 de la hoja 'Datos'.

Cambiar la celda C1 por: Estadísticas

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

Ordenar de Z a A

Filtrar por: Población actual

Mover la tabla dinámica al rango B1:B2

Hacer clic con el botón derecho del ratón en la celda B1, para mostrar el menú contextual, y seleccionar: Opciones de tabla dinámica

En la pestaña: Diseño y formato, desmarcar: Autoajustar anchos de columnas al actualizar

Si se intenta filtrar por más de un valor, indica que: No se pudo completar la acción de la tabla dinámica... porque ya hay una tabla...

No se ha dejado espacio para que crezca la tabla dinámica a propósito. Con lo que solamente se podrá filtrar por un tipo de estadística de población...

Definir el nombre MiEstadística: =Datos!$B$2

PASO 14: Tabla de continentes con estadísticas de población

Crear una tabla de continentes en una nueva hoja 'Mapa' en el rango A20:B27, denominada:  TablaContinentes, con la siguiente fórmula en la columna B:

=BUSCARV(MiEstadística&"*";Reloj_de_población_de_África;2;FALSO)

Cambiando en cada fila el continente marcado en negrita.

PASO 15: Añadir columna de Población en la tabla de países

En la hoja 'Datos' insertar en la celda C4 la fórmula:

=SI.ERROR(BUSCARV([@Continente];TablaContinentes;2;FALSO);0)

Con lo que todos los países de un mismo continente obtendrán el mismo valor de población, ¡como se quería lograr!

Poner el formato de celdas como número sin decimales y con separador de miles.

PASO 16: Insertar el mapa coroplético

En la hoja 'Datos' seleccionar las columnas B y C con los países y la población de sus continentes.

En la cinta de opciones, seleccionar: Insertar > Gráficos > Mapa coroplético

Cortar el mapa con Control+X y pegarlo con Control+V en la hoja 'Mapa'.

PASO 17: Cambiar el color del mapa

En la cinta de opciones seleccionar: Diseño de gráfico > Cambiar colores > Paleta monocromática 1

PASO 18: Quitar título del mapa y leyenda arriba

En el mapa quitar el título del gráfico, seleccionándolo y presionando la tecla: SUPR

Hacer clic con el botón derecho del ratón en la leyenda para mostrar el menú contextual, seleccionando: Formato de leyenda

En Opciones de leyenda, seleccionar la posición de leyenda: Arriba

PASO 19: Solo regiones con datos

Hacer clic con el botón derecho del ratón en un país del mapa para mostrar el menú contextual, seleccionando: Dar formato a serie de datos

En Opciones de serie, con el desplegable de Área de mapa, seleccionar: Solo regiones con datos

Con lo que desaparece el continente Antártico, normalmente sin población...

PASO 20: Color de la serie de 2 colores

Seleccionar Color de la serie: Secuencial (2 colores)

• Mínimo en color azul claro para los valores más bajos.
• Máximo en color azul oscuro para los valores más altos.

PASO 21: Color de la serie por valor numérico

Este paso es de comprobación, para asegurar que el color es por valor numérico.

Seleccionar el gráfico del mapa.

En la cinta de opciones, seleccionar: Diseño de gráfico > Seleccionar datos

 

En la siguiente pantalla, presionar el botón Modificar:

Comprobar que, en Seleccionar las propiedades de color, está marcado: Color por valor numérico

PASO 22: Situar el mapa y cambiar su tamaño

Dejar espacio encima del mapa para colocar la segmentación de datos de continentes, que será el siguiente paso.

PASO 23: Insertar segmentaciones de datos

Seleccionar en la hoja 'Datos' una celda de la tabla de continentes, países y su población.

Seleccionar en la cinta de opciones: Diseño de tabla > Insertar segmentación de datos

Marcar únicamente: Continente

Cortar con Control+X la segmentación de Continente y pegarla con Control+V en la hoja 'Mapa', encima del mapa, cambiando el número de columnas a 6, y quitando el encabezado.

Seleccionar la celda B1 de la hoja 'Datos' y seleccionar en la cinta de opciones: Analizar la tabla dinámica > Insertar segmentación de datos

Marcar: Estadísticas

Cortar con Control+X la segmentación de Estadísticas y pegarla con Control+V en la hoja 'Mapa', a la izquierda del mapa, quitando el encabezado y redimensionando la segmentación para que no se vea la barra de desplazamiento vertical y se vea el nombre completo de cada tipo de estadística de población.

En la Configuración de las segmentaciones, marcar: Ocultar los elementos que no contienen datos.

Para la segmentación de Estadísticas, seleccionar Ordenación: Descendente.

El resultado de cómo quedan las segmentaciones se muestra en esta imagen:

PASO 24: No mover ni cambiar tamaño del mapa

Es importante que el mapa no se mueva ni cambie su tamaño cuando se filtran filas con las segmentaciones de datos.

Para ello, hacer clic con el botón derecho del ratón sobre el mapa para abrir el menú contextual.

Seleccionar: Formato del área del gráfico

Seleccionar el icono de la derecha en la imagen, para modificar el tamaño y las propiedades.

En Propiedades, marcar: No mover ni cambiar tamaño con celdas.

Ahora ya no desaparecerá el mapa cuando seleccionemos otro continente distinto del africano.

Posdata

En el tercer artículo de esta trilogía sobre mapas, intentaré hacer un Mapa antiguo de continentes. Lo denominaré Mapa antiguo porque será compatible con versiones antiguas de Excel, o sea con todas las versiones a partir de Excel 2010, usadas regularmente en todos los países y continentes del mundo.