Control de municipios españoles

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

Esta es la cuarta entrega sobre municipios españoles, con un control completo sobre todo lo que pasa en el mapa de Control de municipios españoles, como puedo demostrar si lo descargas y lo pruebas.

En la hoja solamente hay un mapa de España, idéntico al de las entregas anteriores, y un único control que no voy a explicar cómo lo he hecho, pues ya lo expliqué en este artículo:

• Nuevo Control de Usuario personalizable | #ExcelPedroWave

Control de municipios

En la entrega anterior se podían filtrar los municipios pero, al aumentar el zoom y pasar el ratón para seleccionar un municipio, no se veían sus datos ni los filtros pues quedaban fijos arriba a la derecha del mapa. Es mejor que el control sea dinámico y se mueva por el mapa, cosa que he hecho con este Control de municipios españoles.

En esta imagen identifico el detalle de la información y los botones de este control personalizado, con el que controlar a los municipios.

Control Personalizado (con la información, filtros y botones identificados con números dentro de círculos):

(1) Mapa de municipios españoles con un enlace al blog.

(2) Nombre del municipio sobre el que pasa el ratón por encima con enlace a Wikipedia.

(3) Nombre de la provincia con enlace a Wikipedia.

(4) Nombre de la autonomía con enlace a Wikipedia.

(5) Altitud del municipio.

(6) Superficie del municipio.

(7) Longitud geográfica del municipio.

(8) Latitud geográfica del municipio.

(9) Población del municipio.

(10) Filtrar municipios por población mayor que un valor cualquiera.

(11) Número de municipios filtrados. Si son más de 300 no se verán las etiquetas con los nombres de los municipios.

(12) Población mínima y máxima de los municipios filtrados.

(13) Filtrar por autonomías.

(14) Filtrar por provincias.

(15) Filtrar por municipios.

(16) Cambiar a modo vuelo ✈️ o modo fijo 📌

(17) Mostrar u ocultar los contornos de las provincias🔺

(18) Cambiar el tamaño del control.

(19) Mostrar los municipios según su población (P)

(20) Escuchar los nombres de los municipios 🗣️

(21) Mostrar los nombres de los municipios 💬

(22) ZM: Zoom Maximizado al 400%

(23) Zm: Zoom minimizado al 75%

(24) Z0: Zoom ajustado a la ventana.

(25) Z+: Zoom incrementado un 25%

(26) Desplazar el mapa con 4 botones:

• ⬆️ Una fila hacia arriba.
• ⬇️ Una fila hacia abajo.
• ⬅️ Una columna hacia la izquierda.
• ➡️ Una columna hacia la derecha.

(27) Z-: Zoom decrementado un 25%

(28) Mapa con los puntos que representan los municipios.

(...) Se han reservado 3 botones para usos futuros, situados abajo a la izquierda.

Control del modo: fijo o móvil

Con el botón (16) se puede cambiar a modo móvil de vuelo ✈️ (avión) o a modo fijo 📌 (chincheta).

En modo fijo el control se sitúa arriba a la derecha de la hoja 'MAPA', siempre que el botón (16) tenga el símbolo📌

En modo móvil de vuelo el control se mueve por el mapa cuando se pasa con el ratón por encima de un municipio, siempre que el botón (16) tenga el símbolo✈️

La información del control se actualiza con los datos del municipio elegido, siempre que el botón (16) tenga los símbolos ✈️ o 📌 de color rojo. Si el símbolo es de color azul se mostrará información estática del último municipio seleccionado.

ATENCIÓN: Para actualizar los datos de municipios, o para mantener los datos del último municipio seleccionado, se debe presionar el botón derecho del ratón encima del mapa, con lo que el botón (16) cambiará de color rojo a azul alternativamente. Siempre que se pulse el botón (16) cambiará a rojo indicando que se actualizará la información del municipio sobre el que pasa el ratón por encima del mapa.

Será más fácil comprender estos modos de funcionamiento con el videotutorial que he grabado y que puedes ver más abajo.

Cómo filtrar municipios

Para filtrar municipios hay 4 filtros distintos a los que se accede desde el control:

(10) Filtrar municipios por población mayor que un valor cualquiera.

(13) Filtrar por autonomías.

(14) Filtrar por provincias.

(15) Filtrar por municipios.

La explicación de estos filtros se puede leer en la tercera entrega sobre municipios españoles:

• Filtros de municipios españoles | #ExcelPedroWave

Estos filtros no están integrados en el propio control sino que están en la hoja 'CONTROL', de la que se obtiene la imagen del control con la cámara de Excel.

En el control están solamente los enlaces para ir a las celdas de los filtros en la hoja 'CONTROL':

• Filtro de población en la celda E8, con un desplegable de valores de población, aunque se puede escribir cualquier valor. Seleccionando > 0 salen todos los municipios.

• Filtro de autonomías en la celda C10, con un desplegable de autonomías, aunque se puede escribir cualquier secuencia de caracteres con o sin comodines * o ?.

• Filtro de provincias en la celda C11, con un desplegable de provincias, aunque se puede escribir cualquier secuencia de caracteres con o sin comodines * o ?.

• Filtro de municipios en la celda C12, con un desplegable da municipios, aunque se puede escribir cualquier secuencia de caracteres con o sin comodines * o ?.

¿Por qué salto de la hoja 'MAPA' a la hoja 'CONTROL' para definir los filtros?

Pues porque no he querido insertar un cuadro combinado (ActiveX) que es más difícil de controlar cuando cambia el zoom. Si quieres puedes intentar insertarlo tu mismo leyendo este artículo en que lo usé y no me gustó el resultado:

• Buscador de países | #ExcelPedroWave

Vídeo: Control de municipios españoles

En el vídeo explico cómo usar el control de municipios españoles.

Descarga Control de municipios españoles

Este mapa de Control de municipios españoles es compatible con todas las versiones de escritorio, desde Excel 2010 hasta Excel para Microsoft 365.

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

• Microsoft OneDrive: Control de municipios españoles - PW1.xlsm

• Sites Google Drive: Control de municipios españoles - PW1.xlsm

Las macros del archivo descargado están bloqueadas por defecto. Para desbloquear las macros debes modificar las Propiedades del archivo siguiendo estas instrucciones:

Las macros de Internet están bloqueadas de forma predeterminada en Office - Deploy Office | Microsoft Learn

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

Presiona el botón: Habilitar contenido cuando aparezca la ADVERTENCIA DE SEGURIDAD Las macros se han deshabilitado o se deshabilitó parte del contenido activo.

Las hojas están protegidas sin contraseña y no está protegido el proyecto VBA, por lo que puedes estudiar y analizar el código de las macros.

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

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

Más mapas de municipios

En este enlace están todas las entregas dedicadas a los municipios españoles que he escrito hasta ahora:

• municipios | #ExcelPedroWave

La próxima entrega será para crear una lista personalizada de municipios españoles que se usará en el resto de entregas.

Filtros de municipios españoles

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

Esta es la tercera entrega sobre mapas de municipios españoles, en el que he cambiado la forma de filtrarlos.

Su principal característica es que he quitado las segmentaciones de datos, que ocupaban mucho espacio, y las he sustituido por 3 filtros avanzados con validación de datos: Autonomías; Provincias y Municipios.

En los ejemplos de la imagen animada he filtrado por Municipios:

• Que comienzan por za
• Que contienen ona, con el carácter comodín asterisco: *ona
• Que comienzan por las *, con un espacio y un asterisco.

En los desplegables aparece la lista de municipios filtrados y ordenados.

Este modo de filtrado se aplica a los 3 filtros, no sólo a los Municipios sino a Provincias y a Autonomías.

Estos filtros no los he hecho con las nuevas funciones de la última versión de Excel, sino que he usado funciones de toda la vida, para que sigan siendo compatibles con versiones desde Excel 2010 en adelante.

Vídeo: Filtros de municipios españoles

En el vídeo explico cómo filtrar municipios y cómo abrir enlaces a Wikipedia, y así saber más de autonomías, provincias y municipios españoles.

Filtros con validación de datos

Los nuevos filtros permiten filtrar por:

• Autonomías, en la celda U2 con el nombre definido: miAutonomía. Con una validación de datos en una lista desplegable con origen: =miRangoAutonomías

• Provincias, en la celda U4 con el nombre definido: miProvincia. Con una validación de datos en una lista desplegable con origen: =miRangoProvincias

• Municipios, en la celda U6 con el nombre definido: miMunicipio. Con una validación de datos en una lista desplegable con origen: =miRangoMunicipios

Las 3 validaciones de datos no generan ningún mensaje de error cuando se escribe texto que no está en la lista desplegable.

Para ello en la pestaña: Mensaje de error, no debe estar marcado: Mostrar mensaje de error si se introducen datos no válidos

Con lo que, por ejemplo se puede escribir: za; *ona; las *, o cualquier otra combinación de caracteres por la que se filtre por Autonomías, Provincias o Municipios.

En la búsqueda se pueden usar los caracteres comodín:

* asterisco para representar cualquier conjunto de caracteres.

? interrogante para representar un carácter cualquiera en esa posición.

~ tilde generado con la tecla Alt + 126 (código introducido con el teclado numérico) para poder buscar un carácter *, ? o tilde con: ~*. ~? o ~~

En esta página se explica el uso de los comodines:

• Usar caracteres comodín en las búsquedas - Soporte técnico de Microsoft

Origen de las validaciones de datos

En la hoja 'DATOS' se generan lo orígenes de las 3 validaciones de datos.

En el rango T:V se genera el origen de la validación de Autonomías, a partir de la columna H con las autonomías de la tabla dinámica.

Las autonomías no repetidas de la columna T se generan con la fórmula:

=SI.ERROR(INDICE($H$2:$H$8132;COINCIDIR(FILA($H$2:$H$8132)+1-FILA(INDICE($H$2:$H$8132;1));CONTAR.SI($H$2:$H$8132;"<="&INDICE(SI.ERROR($H$2:$H$8132&"";0);FILA($H$2:$H$8132)+1-FILA(INDICE($H$2:$H$8132;1))));0));"")

Las autonomías ordenadas de la columna U se generan con la fórmula:

=SI.ERROR(INDICE($T$2:$T$8132;K.ESIMO.MENOR(SI(SI.ERROR($T$2:$T$8132&"";"")="";"";COINCIDIR(FILA($T$2:$T$8132);FILA($T$2:$T$8132);0));FILA($T$2:$T$8132)+1-FILA(INDICE($T$2:$T$8132;1))))&"";"")

Las autonomías listadas de la columna V se generan con la fórmula:

=INDICE(DATOS!$U$2:$U$8132;1):INDICE(DATOS!$U$2:$U$8132;MAX(1;CONTAR.SI(DATOS!$U$2:$U$8132;"?*")))

En el rango X:Z se genera el origen de la validación de Provincias, a partir de la columna I con las provincias de la tabla dinámica.

Las provincias no repetidas de la columna X se generan con la fórmula:

=SI.ERROR(INDICE($I$2:$I$8132;COINCIDIR(FILA($I$2:$I$8132)+1-FILA(INDICE($I$2:$I$8132;1));CONTAR.SI($I$2:$I$8132;"<="&INDICE(SI.ERROR($I$2:$I$8132&"";0);FILA($I$2:$I$8132)+1-FILA(INDICE($I$2:$I$8132;1))));0));"")

Las provincias ordenadas de la columna Y se generan con la fórmula:

=SI.ERROR(INDICE($X$2:$X$8132;K.ESIMO.MENOR(SI(SI.ERROR($X$2:$X$8132&"";"")="";"";COINCIDIR(FILA($X$2:$X$8132);FILA($X$2:$X$8132);0));FILA($X$2:$X$8132)+1-FILA(INDICE($X$2:$X$8132;1))))&"";"")

Las provincias listadas de la columna Z se generan con la fórmula:

=INDICE(DATOS!$Y$2:$Y$8132;1):INDICE(DATOS!$Y$2:$Y$8132;MAX(1;CONTAR.SI(DATOS!$Y$2:$Y$8132;"?*")))

En el rango AB:AD se genera el origen de la validación de Municipios, a partir de la columna J con los municipios de la tabla dinámica.

Los municipios no repetidos de la columna AB se generan con la fórmula:

=SI.ERROR(INDICE($J$2:$J$8132;COINCIDIR(FILA($J$2:$J$8132)+1-FILA(INDICE($J$2:$J$8132;1));CONTAR.SI($J$2:$J$8132;"<="&INDICE(SI.ERROR($J$2:$J$8132&"";0);FILA($J$2:$J$8132)+1-FILA(INDICE($J$2:$J$8132;1))));0));"")

Los municipios ordenados de la columna AC se generan con la fórmula:

=SI.ERROR(INDICE($AB$2:$AB$8132;K.ESIMO.MENOR(SI(SI.ERROR($AB$2:$AB$8132&"";"")="";"";COINCIDIR(FILA($AB$2:$AB$8132);FILA($AB$2:$AB$8132);0));FILA($AB$2:$AB$8132)+1-FILA(INDICE($AB$2:$AB$8132;1))))&"";"")

Los municipios listados de la columna AD se generan con la fórmula:

=INDICE(DATOS!$AC$2:$AC$8132;1):INDICE(DATOS!$AC$2:$AC$8132;MAX(1;CONTAR.SI(DATOS!$AC$2:$AC$8132;"?*")))

Las columnas V, Z y AD no son estrictamente necesarias, únicamente están para que quede más claro que se consiguen rango de datos con exactamente el listado de autonomías, provincias y municipios.

Donde si que son necesarias las fórmulas de esas 3 columnas es en estos nombres definidos:

• miRangoAutonomías como origen de la validación de autonomías.
• miRangoProvincias como origen de la validación de provincias.
• miRangoMunicipios como origen de la validación de municipios.

En esta imagen se muestran los orígenes de los 3 filtros de nombres definidos.

PLUS: Enlaces a Wikipedia

Como un plus para hacer este mapa más interesante he añadido 3 enlaces a Wikipedia, para saber más sobre autonomías, provincias y municipios.

Una vez seleccionado un municipio en el mapa con el ratón, en las celdas U14, U15 y U16 se crean hipervínculos a Wikipedia para esos datos con las fórmulas:

Municipio en U14:

=HIPERVINCULO("https://es.wikipedia.org/wiki/" & SI.ERROR(EXTRAE(INDICE(DATOS!$J$2:$J$8132;miPunto)&"";ENCONTRAR("/";INDICE(DATOS!$J$2:$J$8132;miPunto)&"")+1;50);INDICE(DATOS!$J$2:$J$8132;miPunto)&"");INDICE(DATOS!$J$2:$J$8132;miPunto)&"")

Provincia en U15:

=HIPERVINCULO("https://es.wikipedia.org/wiki/provincia_de_" & SI.ERROR(EXTRAE(INDICE(DATOS!$I$2:$I$8132;miPunto)&"";ENCONTRAR("/";INDICE(DATOS!$I$2:$I$8132;miPunto)&"")+1;50);INDICE(DATOS!$I$2:$I$8132;miPunto)&"");INDICE(DATOS!$I$2:$I$8132;miPunto)&"")

Autonomía en U16:

=HIPERVINCULO("https://es.wikipedia.org/wiki/" & IZQUIERDA(INDICE(DATOS!$H$2:$H$8132;miPunto)&"";SI.ERROR(ENCONTRAR("/";INDICE(DATOS!$H$2:$H$8132;miPunto)&"")-1;LARGO(INDICE(DATOS!$H$2:$H$8132;miPunto)&"")));INDICE(DATOS!$H$2:$H$8132;miPunto)&"") 

Descarga Filtros de municipios españoles

Este mapa con filtros de municipios españoles es compatible con todas las versiones de escritorio, desde Excel 2010 hasta Excel para Microsoft 365.

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

• Microsoft OneDrive: Filtros de municipios españoles - PW1.xlsm

• Sites Google Drive: Filtros de municipios españoles - PW1.xlsm

Las macros del archivo descargado están bloqueadas por defecto. Para desbloquear las macros debes modificar las Propiedades del archivo siguiendo estas instrucciones:

Las macros de Internet están bloqueadas de forma predeterminada en Office - Deploy Office | Microsoft Learn

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

Presiona el botón: Habilitar contenido cuando aparezca la ADVERTENCIA DE SEGURIDAD Las macros se han deshabilitado o se deshabilitó parte del contenido activo.

Las hojas están protegidas sin contraseña y no está protegido el proyecto VBA, por lo que puedes estudiar y analizar el código de las macros.

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

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

Más mapas de municipios

En este enlace están todas las entregas dedicadas a los municipios españoles que he escrito hasta ahora:

• municipios | #ExcelPedroWave

En la próxima entrega pensaré en cómo controlar los municipios españoles al cambiar el zoom para no perder el foco.

Clases de municipios españoles

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

Esta es la segunda entrega sobre municipios españoles, con la particularidad de que este mapa está hecho con un módulo de clase, de ahí el título de esta entrega: Clases de municipios españoles, que puede servir para dar clases educativas sobre los pueblos y ciudades de España.

Pasando el ratón por encima de los puntos del mapa se obtiene el municipio con sus datos, como su población y su altitud, ayudado por un módulo de clase en lenguaje VBA, pues el gráfico de dispersión no admite eventos por defecto.

Una imagen vale más que mil palabras.

Clases de municipios

Si dudabas que hay diferentes clases de municipios, los habitantes de su población son un filtro poderoso para saber la importancia de los 8.131 municipios españoles.

No es lo mismo Zaragoza con más de 600.000 habitantes que mi pueblo con unos 600 habitantes. La mitad de los pueblos españoles tienen menos de 500 habitantes y 1.356 municipios tienen menos de 100 habitantes.

No es lo mismo vivir a 200 metros de altitud que a 1 metro en la costa, pues el nivel de los mares puede subir más de lo previsto si el cambio climático se acelera. En España hay 33 municipios a menos de 5 metros de altitud.

No es lo mismo estar a más de 1.500 metros de altitud, como lo están 6 pueblos de Ávila, uno de Gerona (el único del Pirineo) y 5 de Teruel. El municipio más elevado de España es Valdelinares en Teruel a 1.695 metros de altitud.

No es lo mismo sufrir los rigores del clima según la longitud y latitud del municipio. Y si no que se lo digan a los canarios que gozan de un clima totalmente diferente a los peninsulares.

Todas estas clases de municipios se pueden analizar con este mapa...

Los datos de los 8.131 municipios se han copiado del:

• Centro de Descargas del CNIG (IGN)

Seleccionando: Nomenclátor Geográfico de Municipios y Entidades de Población 

Y descargando el archivo comprimido: BD_Municipios-Entidades.zip

Extrayendo dos archivos:

• PROVINCIAS.csv, con la relación entre provincias y comunidades autonómicas.
• MUNICIPIOS.csv, con datos de todos los municipios españoles.

Datos copiados a dos tablas en sendas hojas de 'PROVINCIAS' y 'MUNICIPIOS'.

Descarga Clases de municipios españoles

Este mapa con Clases de municipios españoles es compatible con todas las versiones de escritorio, desde Excel 2010 hasta Excel para Microsoft 365.

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

• Microsoft OneDrive: Clases de municipios españoles - PW1.xlsm

• Sites Google Drive: Clases de municipios españoles - PW1.xlsm

Las macros del archivo descargado están bloqueadas por defecto. Para desbloquear las macros debes modificar las Propiedades del archivo siguiendo estas instrucciones:

Las macros de Internet están bloqueadas de forma predeterminada en Office - Deploy Office | Microsoft Learn

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

Presiona el botón: Habilitar contenido cuando aparezca la ADVERTENCIA DE SEGURIDAD Las macros se han deshabilitado o se deshabilitó parte del contenido activo.

Las hojas están protegidas sin contraseña y no está protegido el proyecto VBA, por lo que puedes estudiar y analizar el código de las macros.

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

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

Vídeo: Clases de municipios españoles

En el vídeo explico cómo usar este mapa de clases de municipios españoles.

Mejoras de este mapa

En la primera entrega expliqué cómo hice el primer mapa de municipios de España, por lo que no repetiré esa información aquí, ya que se puede leer en este enlace:

• Mapa de municipios españoles | #ExcelPedroWave

Las mejoras de esta segunda entrega sobre la primera son:

1) Mucha más funcionalidad gracias a las macros VBA.

2) Filtra automáticamente por Población mayor que un número de habitantes.

3) Selección con el ratón de un municipio.

4) Detecta los municipios sobre los que pasa el cursor del ratón.

5) Obtiene automáticamente los datos de los municipios.

6) Tamaño de los municipios según su población.

7) Etiquetas de los municipios en el mapa.

A continuación doy más detalles de cómo se han implementado estos cambios.

Cambios en la hoja 'DATOS'

La tabla de municipios, denominada: TablaEspaña, de la hoja 'DATOS' se rellena con fórmulas que obtienen los datos de las hojas 'MUNICIPIOS' y 'PROVINCIAS'.

Sus campos son: Comunidades autónomas; Provincias; Municipios; Longitud; Latitud y Población.

Estos datos son el origen de la tabla dinámica de municipios, con la que se obtienen los puntos del gráfico de dispersión desde las columnas O y P.

En la celda R2 se guarda el punto seleccionado del mapa o por el que acaba de pasar el ratón. Con ese valor se obtienen los datos del municipio.

En el rango R6:R13 están varios valores del filtro de población por defecto.

En la celda R17 se guarda el valor de la casilla de etiquetas.

En la celda R21 se guarda el valor de la casilla de municipios según su población.

En la celda R25 se guarda el valor de la casilla de contornos de provincias.

Cuando hay cambios en el rango de la columna M, con la población de la tabla dinámica, se dispara el evento Worksheet_Change, que llama a la macro: CambiarMunicipios.

Eventos del gráfico de dispersión

Si nunca has usado módulos de clase, esta es la mejor ocasión para comenzar con estas Clases de municipios españoles.

Por defecto no se pueden usar eventos en los gráficos incrustados, por lo que hace falta insertar un módulo de clase para escuchar los eventos del gráfico de dispersión. Más información en esta página:

• Uso de eventos con gráficos incrustados | Microsoft Learn

Por lo que he creado el módulo de clase: CChartEvent

Definiendo el objecto EventChart para escuchar eventos:

Public WithEvents EventChart As Chart

En ese módulo de clase cancelo los eventos de doble clic y de botón derecho del ratón y atiendo los eventos de selección de un punto del gráfico, gracias al código de Jon Peltier.

El principal evento es el del movimiento del ratón: EventChart_MouseMove

que permite identificar el punto sobre el que pasa el ratón, llamando a la macro: IdentificarPunto x, y

Que ejecuta: EventChart.GetChartElement x, y, ElementID, Arg1, Arg2

En el último argumento (Arg2) se obtiene el número del punto del gráfico sobre el que ha pasado el ratón, con lo que se puede obtener información del municipio asociado a ese punto...

El método GetChartElement devuelve información sobre el elemento de gráfico en las coordenadas x e y especificadas. Este método tiene la particularidad de que sólo se especifican los valores de los dos primeros argumentos. Microsoft Excel rellena el resto de los argumentos y el código debe examinar sus valores al finalizar el método.

Sin embargo, antes de ejecutar los procedimientos, se debe conectar el objeto declarado en el módulo de clase con el gráfico incrustado.

Para ello, en el módulo MóduloMunicipios se declara:

Dim clsChartEvent As New CChartEvent

Y se indica a qué gráfico se le habilitan los eventos:

Set clsChartEvent.EventChart

= Sheets("MAPA").ChartObjects("Gráfico Municipios").Chart

¡Ya se puede pasar el ratón por encima para interactuar con el mapa!

¡Esta es la mejora más importante de este mapa! ¿Qué te parece?

Tamaño de los municipios según su población

Cuando se marca la casilla: Municipios según su población, el tamaño de los puntos varía según la población del municipio, con la macro: CambiarMunicipios

Se cambia el tamaño de cada marcador dependiendo de la población.

Si se desmarca la casilla, todos los marcadores son del mismo tamaño, con los puntos iguales de todos los municipios.

Etiquetas de los municipios

Cuando se marca la casilla: Etiqueta hasta 200 municipios, al lado de cada punto se muestra la etiqueta con el nombre del municipio, con la macro: CambiarMunicipios

Si hay más de 200 municipios filtrados, no se muestra ninguna etiqueta. Tampoco si se demarca la casilla.

Filtro automático por población

En la celda Q6 se introduce un valor de población, con lo que se filtran automáticamente los municipios que son mayores de ese valor.

Para llegar a esa celda cuando está activo el gráfico, se debe teclear <Esc> (la tecla de arriba a la izquierda en el teclado) y luego la tecla <Tab> (la tecla encima de Bloq Mayús)

En la celda Q6 hay un desplegable con algunos valores de Población, pero se puede introducir cualquier número mayor o igual que cero.

Este filtro se hace en la hoja 'MAPA', con el evento Worksheet_Change que refresca automáticamente la tabla dinámica de la hoja 'DATOS' y refresca todo el libro.

Marcar los contornos de las provincias

En los comentarios del vídeo me han sugerido marcar más el contorno de las provincias, que está muy difuminado. Por lo que he añadido una casilla para marcar los contornos de las provincias, con la macro: CambiarContornoMapa.

¿Queda más claro?

Más mapas de municipios

En este enlace están todas las entregas dedicadas a los municipios españoles que he escrito hasta ahora:

• municipios | #ExcelPedroWave

En la próxima entrega exploraré filtros alternativos a las segmentaciones de datos.

Mapa de municipios españoles

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

Para comenzar el año voy a dedicar las próximas publicaciones a crear mapas de municipios españoles ¡pero sin usar los mapas coropléticos!, que solamente están en versiones recientes de Excel y que no funcionan según lo esperado, como expliqué en este artículo:

• Mapa de Municipios de Zaragoza en Excel | #ExcelPedroWave

Intentaré que los mapas sean compatibles con todas las versiones de escritorio, desde Excel 2010 hasta Excel para Microsoft 365, para lo que los crearé con un gráfico de dispersión de puntos, en el que cada punto representará uno de los 8.131 municipios de España, tanto de la península ibérica como de Canarias, Ceuta y Melilla.

Esta es la primera entrega, con un mapa me permite geolocalizar cualquier municipio español con unos pocos clics. Por ejemplo, para encontrar el municipio de Zaragoza, filtro por Aragón, luego filtro por provincia de Zaragoza y por último filtro por el municipio de Zaragoza, como se ve en esta imagen animada.

En la imagen animada se ven 3 segmentaciones de datos para filtrar por:

• 17 comunidades autónomas y 2 ciudades autónomas.
• 50 provincias y 2 ciudades autónomas.
• 8.131 municipios marcados con puntos.

Filtro manual de población

Es más fácil localizar los municipios si se filtran por su población.

Como esta es la primera entrega sobre municipios españoles, el mapa es un archivo sin macros VBA con el que explicaré las técnicas fundamentales para diseñar este tipo de mapas, y es el motivo por el que el filtro de población se debe hacer manualmente.

Para facilitar el filtrado manual por la población de cada municipio, en el rango V2:W13 he añadido información de los municipios filtrados.

En la siguiente imagen se han filtrado los municipios de más de 100.000 habitantes, con lo que en este mapa aparecen los puntos que representan los 64 municipios con una población entre 100.535 y 3.280.782 habitantes.

Para modificar el filtro de población hay que seguir el enlace de la celda V7: Ir al filtro de población

En la celda M1 de la hoja 'DATOS' está el filtro de población de la tabla dinámica de municipios.

Se debe seleccionar el Filtro de etiqueta por cualquier criterio. En el ejemplo si Mayor que...

En este caso la población filtrada es mayor que 100.000 habitantes, con lo que solamente aparecerán los municipios que superen esa población.

Para actualizar las segmentaciones de datos es preciso actualizar la tabla dinámica manualmente, por lo que en la cinta de opciones hay que seleccionar: Datos > Actualizar todo

También se puede actualizar todo con el teclado presionando: Control + Alt + F5

Datos de municipios

En la hoja 'DATOS' están los 8.131 municipios españoles, descargados desde la página de descargas del Centro Nacional de Información Geográfica (CNIG) del Instituto Geográfico Nacional (IGN):

• Centro de Descargas del CNIG (IGN)

En este documento se explican los términos de la licencia de uso de los datos descargados:

• Condiciones_licenciaUso_IGN.pdf | Ministerio de Fomento (ign.es)

ATENCIÓN: Los datos de municipios se publican y se pueden modificar respetando esta licencia:

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

Se selecciona: Nomenclátor Geográfico de Municipios y Entidades de Población 

Y se descarga el archivo comprimido: BD_Municipios-Entidades.zip

Los datos de municipios se han extraído del archivo: MUNICIPIOS.csv

Los datos de comunidades autónomas se han extraído de: PROVINCIAS.csv

En la tabla TablaEspaña de la hoja 'DATOS' se han cargado los nombres de las comunidades autónomas, las provincias, los municipios, su longitud, latitud y población, incluidas las ciudades autónomas de Ceuta y Melilla.

Trucos con las segmentaciones de datos

Como quiero que el mapa sea compatible con Excel 2010 y versiones posteriores, en Excel 2010 la segmentación de datos únicamente está disponible para tablas dinámicas. Sin embargo, para las tablas normales (rangos de celdas convertidos en tablas), esta funcionalidad no está disponible en esa versión. La segmentación de datos para tablas normales se introdujo en versiones posteriores, a partir de Excel 2013.

Truco 1: Por lo tanto, para que el mapa sea compatible con Excel 2010, hay que insertar las segmentaciones de datos desde una tabla dinámica.

En la hoja 'DATOS' he creado la tabla dinámica de municipios: TablaDinámicaMunicipios

El diseño del informe se muestra en formato tabular, para así poder insertar 3 segmentaciones de datos: Comunidades autónomas; Provincias y Municipios, que se han llevado a la hoja 'MAPA'.

Además contiene los campos de Longitud y Latitud convertidos para mostrar Canarias en el mapa.

El campo Población permite filtrar desde la celda M1, como he comentado anteriormente.

En las columnas O y P se copian los campos de Longitud y Latitud de la tabla dinámica, siendo estos datos el origen de datos del gráfico de dispersión en la hoja 'Mapa'.

Los campos Longitud y Latitud son iguales, excepto para los municipios de Canarias, que se han desplazado en el mapa para situarlos abajo a la derecha.

Para ello se han definido dos nombres definidos: LongCanarias y LatCanarias con valores fijos que se suman a las coordenadas geográficas.

IMPORTANTE: Para que los usuarios finales de este mapa no sean capaces de modificar las segmentaciones de datos es preciso usar un par de trucos más.

Al insertar una segmentación de datos, se crea con estas propiedades:

Como está marcado por defecto el campo: Bloqueado, la segmentación no funciona si se protege la hoja sin marcar: Modificar objetos

Pero no deseo por nada del mundo que el usuario pueda modificar objetos y el mapa mismo, por lo que no se debe marcar Modificar objetos en ningún caso.

Truco 2: Desmarcar: Bloqueado en cada una de las 3 segmentaciones de datos.

Al estar desbloqueadas las segmentaciones aunque la hoja esté protegida, es necesario aplicar otro truco más.

Truco 3: en la sección de Posición y diseño marcar: Deshabilitar cambiar tamaño y mover

Ahora es imposible cambiar el tamaño y mover segmentaciones de datos, y siguen siendo totalmente funcionales como filtro de datos.

Problema: Al equipo de desarrollo de Microsoft Excel se le olvidó proteger a las segmentaciones de datos del borrado accidental cuando se marca: Deshabilitar cambiar tamaño y mover. Este problema también se tiene si se permiten Modificar objetos... No hay solución alternativa conocida a este problema... La única posibilidad es Deshacer el borrado (Control+Z) a tiempo, o abrir de nuevo un archivo que contenga la segmentación...

Como se ve en la imagen de arriba, la segmentación de municipios comienza con el municipio de Jun ¡que si que existe en la provincia de Granada!

¿Cómo lograr que Jun no aparezca ordenado el primero en la segmentación de municipios?

Truco 4: Pues desmarcando: Usar listas personalizadas al ordenar, como se ve en la Configuración de la imagen de la derecha.

Esto ocurre porque para ordenar los meses del año existen listas personalizadas para el nombre completo del mes y para sus abreviaturas de 3 letras, como Jun para Junio.

También he marcado: Ocultar los elementos que no contienen datos, con lo que las segmentaciones de Municipios y Provincias son más claras y ayudan a filtrar mejor.

Está desmarcado en la segmentación de Comunidades autónomas y así siempre están visibles todas, donde solamente se marca el filtro: Indicar visualmente elementos sin datos, con lo que las comunidades autónomas sin datos aparecen en otro color pero mantienen su posición en la segmentación.

Cómo he dibujado el mapa

Como me he propuesto que estos mapas de municipios sean compatibles con versiones desde Excel 2010 y superiores, no puedo usar mapas coropléticos (que aparecieron en Excel 2019) ni datos de información geográfica (que sólo están en Excel para Microsoft 365 y en Excel para la Web).

Pero necesito un mapa de España y de Canarias por separado.

¿De dónde lo obtengo?

¡De mi suscripción a Microsoft 365!

Y de este artículo de mi blog, ¡que está teniendo mucho éxito!:

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

Borrando los datos del rango Provincias!D2:D53 de obtiene un mapa de color neutro con las fronteras entre provincias.

Se eliminan las etiquetas con los nombres de las provincias. En el desplegable Etiquetas de mapa, cambiar Mostrar todo por: Ninguno

Se quita la Leyenda y se ajusta el color de la serie. ¡Y ya se puede copiar este mapa como una imagen!

Eso sí, pasando por un editor de imágenes como MS Paint.

Después de pegarlo en la hoja 'MAPA', desde la cinta de opciones en Formato de imagen he seleccionado como estilo de imagen: Rectángulo sombreado

El mismo procedimiento he seguido para conseguir la imagen de Canarias.

Sobre estas dos imágenes se colocarán los puntos geolocalizados de los municipios.

Geolocalización de los municipios

Las latitudes y longitudes son el rango de origen de datos de un gráfico de dispersión:

=DATOS!$O$1:$P$8132

En la imagen de abajo se ve el gráfico de dispersión de municipios si no se filtra ningún municipio.

En el Panel de Selección el "Gráfico Municipios" debe estar por encima de las imágenes de España y Canarias, para pasar el ratón por encima de los puntos que representan los municipios.

En Opciones del eje se han modificado los límites de las coordenadas geográficas:

• Latitud en el eje vertical, con un mínimo de 35 y un máximo de 44.
• Longitud en el eje horizontal, con un mínimo de -10 y un máximo de 10,5

En Formato de la serie de datos, selecciono como Opciones de marcador: Integrado del tipo O y tamaño 5. Con este marcador se pintan los puntos que representan los municipios en el mapa.

El resto de opciones de serie se pueden modificar a gusto del usuario final.

Únicamente hace falta hacer coincidir las imágenes de España y Canarias con el gráfico de dispersión para que se solapen, lo que se hace manualmente ¡a ojo!

Y después de este rollo explicando cómo he hecho este mapa, toca descargarlo para probar si lo he conseguido.

Descarga del mapa de municipios

Este archivo es compatible desde Excel 2010 hasta Excel para Microsoft 365, con la hoja protegida sin contraseña, para que los usuarios no destrocen el mapa, y no contiene macros.

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

• Microsoft OneDrive: Mapa de municipios españoles - PW1.xlsx

• Sites Google Drive: Mapa de municipios españoles - PW1.xlsx

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

Vídeo: Mapa de municipios españoles

En el vídeo explico cómo usar el mapa de municipios españoles.

Más mapas de municipios

En este enlace están todas las entregas dedicadas a los municipios españoles que he escrito hasta ahora:

• municipios | #ExcelPedroWave

En la próxima entrega exploraré macros en módulos de clase para detectar eventos en el gráfico de dispersión de puntos, lo que hará este mapa más interactivo para encontrar los municipios españoles.

Agenda Calendario Lunar 2024

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

¡¡¡ Feliz y Próspero Año Nuevo 2024 !!!

Para comenzar este Año Nuevo lo mejor posible publico la Agenda Calendario Lunar actualizada para este nuevo año, que funciona únicamente con fórmulas, ¡sin macros VBA!

Se puede usar tanto en español como en inglés, y comenzar la semana en lunes o en domingo.

He intentado que se pueda abrir en cualquier versión de Excel, también en Excel para la Web y en las aplicaciones de Excel para Android y de Excel para Mac. ¡Ya me dirás si lo he conseguido!

De todos los calendarios que he hecho en Excel, éste es del que estoy más satisfecho para llevar al día mis asuntos. Espero que a ti te pase lo mismo que a mí.

Sirva esta Agenda Calendario Lunar para felicitar el Año Nuevo a mis seguidores.

Descarga la Agenda Calendario Lunar

El archivo tiene todas las hojas protegidas con contraseña, para que los usuarios no destrocen las fórmulas.

Descarga desde uno de estos enlaces:

• Microsoft OneDrive: Agenda Calendario Lunar 2024 PW1.xlsx

• Sites Google Drive: Agenda Calendario Lunar 2024 PW1.xlsx

Esta plantilla no contiene macros, pues todos los cálculos se realizan ¡únicamente con fórmulas!

Si te interesa analizar las fórmulas, encontrarás la contraseña, para desproteger las hojas, en el primer artículo que publiqué sobre calendarios lunares:

• Nueva Agenda Calendario de Fases Lunares

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

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

Videotutorial de la Agenda Calendario Lunar

En este videotutorial explico como usar la Agenda Calendario Lunar:

Hojas de la Agenda Calendario Lunar

Esta agenda calendario lunar contiene las siguiente hojas:

• Calendario: mensual y lunar, con hipervínculos a los eventos del año.
• Agenda: con los próximos eventos: tareas, festivos y aniversarios.
• Diario: con los eventos de las 24 horas de un día, y con hipervínculos.
• Buscar: buscador de cualquier evento del año.
• Festivos: lista editable de días festivos para cualquier año.
• Aniversarios: lista editable de aniversarios para cualquier año.
• Ayuda: enlaces a las páginas que me han servido de inspiración.
• 2024: Planificador de eventos editables para 366 días x 24 horas.

Menú de la Agenda Calendario Lunar

Con los iconos del menú es más fácil cambiar de hoja, y saber qué hoja se ha seleccionado, pues cambia a rojo el color del icono.

Desde cualquier hoja se puede ir al evento del día de hoy, haciendo clic en el enlace con el hipervínculo.

Además he añadido la hora actual en todas las hojas de la agenda.

Tutoriales de la Agenda Calendario Lunar

En los siguientes artículos explico las principales características de estas Agendas Calendarios Lunares:

• Nueva Agenda Calendario de Fases Lunares
• Agenda Calendario Lunar con fórmulas
• Cómo funciona la Agenda Calendario Lunar (1/2)
• Cómo funciona la Agenda Calendario Lunar (2/2)

Prueba la Agenda Calendario Lunar en la nube

Si no quieres descargarla puedes probarla en mi nube de Microsoft OneDrive:

AVISO: En mi nube no se guardan los cambios que hagas.

Cómo añadir años a la Agenda Calendario Lunar

En este vídeo explico cómo he actualizado la agenda, con un ejemplo de cómo convertirla en un calendario del año 2025, con lo que será fácil crear agendas de otros años.

¡Apunta en esta agenda tus propósitos para el Año Nuevo!

¡Y así nunca los olvidarás!