#ExcelPedroWave

Felicidades a las generaciones: 1G, 2G, 3G, 4G, 5G

Hace unos días cumplió 92 años mi padre 😘 y, para celebrarlo como se merece, que mejor que su hijo se dedique a crear pirámides de población española en Excel, para que sepa cuántas personas hay en España de su generación, y de las 3Generaciones de sus hijos, nietos y bisnieto, que el chiquillo cumplirá su primer año de vida esta misma semana. 🥰

Como comprenderás, este artículo no trata de las generaciones de tecnologías de telefonía móvil, aunque me las conozca todas desde la primera generación 1G de los años 80, que para eso trabajé en el departamento de I+D de una fábrica de teléfonos durante 23 años.

Voy a hablar de las generaciones humanas, que me gusta clasificar según el número de relaciones dentro de la pirámide poblacional:

• 1G si no tienen descendientes, personas solas con o sin pareja, singles, como mis hijos varones.
• 2G si solo tienen hijos, como mi hija.
• 3G si tienen nietos, como yo.
• 4G si tienen bisnietos, como mi padre.
• 5G si tienen tataranietos, como mi tía paterna.
• 6G aún por inventar, aunque puede que mis nietos sean parte de esa generación, si sigue aumentando la esperanza de vida y cambia la tendencia de procrear, cosa difícil siendo que los españoles tenemos una de las tasas de fertilidad por mujer más bajas del mundo...

Al final de este artículo se puede descargar una comparativa entre dos pirámides de población, de igual o distinto año y de 3 grupos de población: Españoles; Extranjeros y Total.

Es de elaboración propia con datos extraídos del sitio web del INE: ine.es, con las cifras oficiales de las principales series de población a 1 de enero de cada año, desde 1998 hasta 2021. Aún no se han publicado los datos oficiales del 1 de enero de 2022.

Esta es la imagen de la comparativa con un par de pirámides interactivas, mediante dos segmentaciones de datos de grupos de población y dos escalas de tiempo de años:

He preferido insertar escalas de tiempo de tablas dinámicas para filtrar datos porque se admiten en Excel para la Web, y así puedo incrustar la plantilla en este blog, aunque limita las versiones de Excel en las que son admitidas a versiones a partir de Excel 2013.

Desde Excel 2007 se pueden insertar segmentaciones de datos en tablas dinámicas, por lo que son perfectamente válidas para filtrar por grupos de población.

Datos del padrón

Con los datos del padrón municipal, que es el registro administrativo de los vecinos de un municipio, el INE elabora la Estadística del Padrón continuo, que ofrece los datos de la población residente en España a 1 de enero de cada año, según lugar de residencia, sexo, edad, nacionalidad y lugar de nacimiento. Los datos de lugar de residencia se facilitan para distintos niveles de desagregación territorial: nacional, comunidades autónomas, provincias, municipios y secciones censales.

La metodología del padrón español, empleada para obtener los datos de población, está explicada en esta página:

INEbase / Demografía y población / Estadística del Padrón continuo / Metodología

La infografía sobre los municipios de España se recoge en esta página:

infografia_padron_2021.cdr (ine.es)

Qué son las pirámides de población

En esta página se puede consultar la Pirámide de la población empadronada en España, publicada por el INE:

Pirámide de la población empadronada en España (ine.es)

Lo que más me gusta de esta pirámide es que los grupos de Edad aparecen en el centro, separando los datos con barras horizontales de Hombres y Mujeres. Cuando se pasa el ratón sobre una barra, muestra la información de la misma, encima del dato Total. El eje horizontal muestra el porcentaje de población, aunque la mayoría de las pirámides indican el número de hombres y mujeres.

Esta pirámide contiene varios filtros por:

• Total Nacional.
• Comunidades y Ciudades Autónomas.
• Provincias.
• Municipios.
• Fecha de referencia desde 1 de enero de 2003 hasta 1 de enero de 2021.

ATENCIÓN: El rango de años va de 1998 a 2021 en las 3 pirámides que publico en este artículo, dos incrustadas en el artículo y la comparativa de pirámides que se puede descargar al final del artículo.

Lo que echo de menos es un filtro por: Españoles; Extranjeros y Total, cosa que he añadido en mis pirámides, pues existen esos datos en la estadística del padrón y su comparación es muy significativa... 

Una pirámide de población es un gráfico con las siguientes características:

• Es un gráfico de barras horizontales cuya longitud es proporcional a la cantidad de personas que representa la edad y sexo de la población.
• Gráficamente se trata de un doble histograma de frecuencias.
• Convencionalmente se indican los grupos de edad de la población masculina a la izquierda y los que representan la población femenina a la derecha.
• En el eje vertical se identifican los grupos de edad, por lo general en intervalos quinquenales, de cinco en cinco años.
• Las barras de menor edad se colocan en la parte inferior del gráfico, aumentando progresivamente hacia la cúspide las edades de cada intervalo.
• En las pirámides quinquenales, cada 5 años de vida cambiamos de grupo de edad... Ahora caigo en la cuenta que me quedan 3 meses para formar parte del grupo de 65 a 69 años...
• Se han llamado habitualmente pirámides de población cuando su base es amplia, debido al gran número de nacimientos y se estrecha paulatinamente por la mortalidad creciente y acumulativa a medida que aumenta la edad de la población.
• En el caso de España ya no es una pirámide sino un embudo, pues nos hacemos más viejos sin que haya relevo generacional. Hay que estudiar la Geografía de la población - Wikipedia, la enciclopedia libre, con los efectos derivados de la dinámica demográfica española, y contemplando también las migraciones de extranjeros (desde hace un mes muchos ucranianos), que suelen rejuvenecer a un "país marchito"... ¡Y no solo a la España vaciada!

Ejemplo en francés de la pirámide de una población 🚼 en rápido crecimiento, con la forma gráfica similar a una pirámide.

¡Lo contrario que la pirámide española, que se parece más a la forma de un tonel!

A la izquierda del gráfico está el eje vertical con los grupos de edad, aunque yo prefiero situarlos en el centro, separando las barras horizontales entre los hombres y las mujeres, coloreadas de distinto color y con los símbolos masculino y femenino respectivamente, aunque prefiero usar los iconos de hombre 🚹 y de mujer 🚺, como los de los aseos 🚻.

Abajo se muestra el eje horizontal con los millares de hombres y mujeres, aunque prefiero ocultar el eje horizontal y escribir en cada barra el valor, para que pueda verse su número sin necesidad de pasar el cursor por encima de cada una de las barras.

Vídeo del INE explicando qué es una pirámide de población

Un par de pirámides de población en la nube

He preparado un par de pirámides de ejemplo, y las he incrustado aquí por si no tienes instalado Excel, o por si las quieres probar antes de descargarlas de la nube de Microsoft OneDrive.

Se puede interactuar con esta primera pirámide v1.0, filtrando con los desplegables de Población y Año.

Se puede interactuar con esta segunda pirámide v2.0 si se abre en Excel para la Web con el botón de abajo a la derecha que pone: Ver libro a tamaño completo

En la hoja 'Pirámide' están los datos del gráfico y la pirámide poblacional española.

En la hoja 'tabla-0' están los datos descargados del INE.

Se puede cambiar el Año y el Grupo: Españoles; Extranjeros y Total.

Se pueden descargar estas dos pirámides de población con uno de los símbolos de abajo a la derecha, el que pone: Descargar

Cómo hacer una pirámide de población

Lo primero que hay que hacer es descargar el archivo con los datos del INE desde esta página:

https://www.ine.es/jaxi/files/_px/es/xlsx/t20/e245/p08/l0/01002.xlsx

Y seguir estos pasos:

1) Extraer datos del INE

Se descarga el archivo 01002.xlsx, con los datos oficiales a 1 de enero de cada año de las Principales series de población desde 1998 hasta 2021, con el Total nacional español de la Población por edad (grupos quinquenales), Total/Españoles/Extranjeros, Sexo y Año. Estos datos están en una hoja denominada 'tabla-0'. 

Se cambia el nombre del archivo 01002 por: Pirámide Poblacional Española, y se abre con una versión a partir de Excel 2013, presionando el botón: Habilitar edición

2) Crear la hoja de la pirámide

Se crea una hoja nueva con el nombre: Pirámide

Se crean 4 nuevos nombres definidos desde el menú: Fórmulas > Administrador de nombres

• MiEdad: ='tabla-0'!$A$1:$A$96
• MiNúmAmbos: ='tabla-0'!$B$1:$Y$96
• MiNúmHombres: ='tabla-0'!$Z$1:$AW$96
• MiNúmMujeres: ='tabla-0'!$AX$1:$BU$96

En esos rangos están los datos de edad de los 3 grupos de población en la tabla descargada del INE.

3) Crear la tabla auxiliar de años

En la celda S1 se escribe: Años

En la celda S2 se escribe: 01/01/1998

En la celda S3 se escribe la fórmula: =FIN.MES(S2;11)+1

Y se arrastra hacia abajo la celda S3 hasta la celda S25, con lo que la última fecha será: 01/01/2021

Se pone como nombre definido de la celda S25: MiÚltimoAño

Seleccionando una de esas celdas se crea una tabla con la combinación de teclas: Control + T, marcando: La tabla tiene encabezados

En Diseño de tabla, se desmarca: Fila de totales

Se cambia el nombre de la tabla por: TablaAños

Se cambia el formato de las fechas de las celdas S2 a S25 por: aaaa, para mostrar sólo los años.

4) Crear la tabla dinámica con el año de la pirámide

Seleccionar una celda de la tabla anterior TablaAños y seleccionar en Diseño de tabla: Resumir con tabla dinámica

Colocar la tabla dinámica en la celda O1 de la hoja y presionar el botón: Aceptar

Seleccionar el campo: Años, y cambiar la etiqueta por: Año

En Analizar tabla dinámica, presionar Desagrupar

Filtrar por el año: 2021

Quitar los totales en Diseño > Totales generales presionando: Desactivado para filas y columnas

Cortar el rango de la tabla dinámica O1:O2 y pegarlo en R1:R2, quedando así: 

En la celda R2 se crea el nombre definido: MiAño

5) Crear la tabla de grupos de población

Escribir lo siguiente en las celdas:

R5: Grupos; R6: Total; R7: Españoles; R8: Extranjeros

Crear una tabla en el rango R5:R8, seleccionando una de esas celdas y presionando la combinación de teclas: Control + T, marcando: La tabla tiene encabezados

En Diseño de tabla, se desmarca: Fila de totales

Se cambia el nombre de la tabla por: TablaGrupos

6) Crear la tabla dinámica con el grupo de población

Seleccionar una celda de la tabla anterior TablaGrupos y seleccionar en Diseño de tabla: Resumir con tabla dinámica

Colocar la tabla dinámica en la celda N3 de la hoja y presionar el botón: Aceptar

Seleccionar el campo: Grupos, y cambiar la etiqueta por: Grupo

En Analizar tabla dinámica, presionar Desagrupar

Filtrar por el grupo: Total

Quitar los totales en Diseño > Totales generales presionando: Desactivado para filas y columnas

Cortar el rango de la tabla dinámica N3:N4 y pegarlo en R3:R4, quedando así:

En la celda R4 se crea el nombre definido: MiGrupo

En la celda R9 se escribe la fórmula: =COINCIDIR(MiGrupo;$R$6:$R$8;0)

En la celda R9 se crea el nombre definido: MiNúmGrupo

7) Crear la tabla de datos para el gráfico

En la celda I1 se escribe: Fila, en la celda I2: 13, y en la celda I3 la fórmula: =I2+4

Y se arrastra hacia abajo la celda I3 hasta la celda I22, con lo que el valor de esta última celda será: 93.

En la celda I23 se escribe: Total

Seleccionando una de esas celdas se crea una tabla con la combinación de teclas: Control + T, marcando: La tabla tiene encabezados

Se cambia el nombre de la tabla por: TablaEspaña

En Diseño de tabla se marca: Fila de totales

Crear estas columnas de la tabla a la derecha de la columna Fila:

Edades, Izquierda, Hombres, Centro, Mujeres, Derecha, Total 

IMPORTANTE: Escribir en este orden las fórmulas de cada una de las celdas de la tabla: 

• J2: =INDICE(MiEdad;[@Fila])
• L2: =INDICE(MiNúmHombres;[@Fila]+MiNúmGrupo;SI(MiAño=0;1;2022-AÑO(MiAño)))
• L23: =SUBTOTALES(109;[Hombres])
• K23: =SUBTOTALES(104;[Hombres]) con el nombre definido: MiMáxHombres
• K2: =MiMáxHombres-[@Hombres]
• N2: =INDICE(MiNúmMujeres;[@Fila]+MiNúmGrupo;SI(MiAño=0;1;2022-AÑO(MiAño)))
• N23: =SUBTOTALES(109;[Mujeres])
• O23: =SUBTOTALES(104;[Mujeres]) con el nombre definido: MiMáxMujeres
• O2: =MiMáxMujeres-[@Mujeres]
• P2: =SUMA($L2;$N2)
• P23: =SUBTOTALES(109;[Total])
• M23: =SUBTOTALES(104;[Total])/6
• M2: =TablaEspaña[[#Totales];[Centro]]

TRUCO: En la hoja 'Datos' divido por 6 el Total del Centro, en la celda M23, para ajustar el ancho de la zona central que separa las pirámides de población entre hombres y mujeres. Así se pueden ver bien los rangos de años, sobre todo el texto más largo: 100 años y más. Es un ajuste visual determinado probando. Prueba a dividir por otros valores distintos de 6 y comprobarás que cambia la separación entre hombres y mujeres.

Cambiar el formato del rango de celdas K2:P2, como números con 0 posiciones decimales y usar separador de miles: #.##0

La tabla resultante será así:

8) Crear el gráfico de la pirámide de población

En esa tabla se selecciona el rango J1:O22, y se inserta un gráfico de barras apiladas, presionando el botón: Aceptar

Se eliminan los siguientes elementos del gráfico:

• El eje vertical: con la categoría de grupos de población.
• El eje horizontal: con los valores de población.
• La leyenda: situada abajo con los nombres de las series.
• Las líneas verticales de división: de los valores del eje horizontal.

Con lo que resulta el gráfico de arriba a la derecha, sin pulir.

En este gráfico se cambia el color de relleno de las 5 series:

• Color blanco de las series: "Izquierda"; "Centro" y "Derecha".
• Color azul claro: "Hombres".
• Color verde: "Mujeres".

Con lo que resulta el gráfico de la derecha, en el que ya se puede apreciar la pirámide de población, con las barras de hombres a la izquierda y las barras de mujeres a la derecha.

Se hace clic con el botón derecho del ratón en cualquier barra horizontal, seleccionando: Dar formato a serie de datos...

En Opciones de serie, se cambia el Ancho del rango del valor 150 % al valor 10 %, con lo que las barras son más gruesas.

Se hace clic con el botón derecho del ratón en una barra horizontal de la serie "Hombres", seleccionando: Agregar etiquetas de datos

Se hace clic en una etiqueta de la serie "Hombres" y, en Opciones de etiqueta, se marca la Posición de etiqueta como: Base interior

Se hace clic con el botón derecho del ratón en una barra horizontal de la serie "Mujeres", seleccionando: Agregar etiquetas de datos

Se hace clic en una etiqueta de la serie "Mujeres" y, en Opciones de etiqueta, se marca la Posición de etiqueta como: Extremo interno

Se hace clic con el botón derecho del ratón en una barra horizontal de la serie "Centro", seleccionando: Agregar etiquetas de datos

Se hace clic en una etiqueta de la serie "Centro" y, en Opciones de etiqueta, se desmarca Valor y se marca Nombre de la categoría

Todas las etiquetas se ponen en Negrita, y se cambia el tamaño de la fuente de las etiquetas de la serie "Centro" a un valor de 8.

La pirámide sin el título será la de la imagen de arriba a la derecha.

Se mueve el gráfico a la celda A2.

En la celda B1 se escribe la fórmula: ="Pirámide de la población en España

" & TEXTO($P$23;"#.##0") & " " & MINUSC(MiGrupo) & " el " & TEXTO(SI(MiAño=0;MiÚltimoAño;MiAño);"dd ""de"" mmmm ""de"" aaaa")

Se hace clic en el Título del gráfico y se selecciona la celda B1, con lo que se crea la fórmula: =Pirámide!$B$1, consiguiendo que el título sea dinámico y dependiente del valor de la celda B1, obtenido con la fórmula de más arriba.

El título del gráfico se pone en Negrita y su tamaño del texto con valor 11.

Se oculta el valor de la celda B1, cambiando su color de fuente a blanco, con lo que el gráfico con el título tendrá el aspecto de la imagen de arriba a la derecha.

Para diferenciar entre las barras de hombres y mujeres, se añaden iconos azul claro para los hombres y verde para las mujeres. Los iconos son de estos tipos: 🚹 y 🚺, respectivamente. Pero esto lo explicaré en un próximo vídeo.

Las etiquetas de los iconos apuntan a las celdas: L23 para los hombres y N23 para las mujeres, con lo que se obtienen los valores totales de población masculina y femenina para un año.

9) Crear filtro de Grupo con Segmentación de datos

Se selecciona la celda R4 de la tabla dinámica con el Grupo de población y, en Analizar tabla dinámica, se hace clic en: Insertar segmentación de datos

Se marca: Grupos, y se presiona el botón: Aceptar

En la pestaña Segmentación, se incrementan las Columnas a 3, y se sitúa encima del gráfico.

Se hace clic con el botón derecho del ratón en la Segmentación y se elige: Configuración de Segmentación de datos, desmarcando: Mostrar encabezado, y presionando el botón: Aceptar

Se hace clic con el botón derecho del ratón en la Segmentación y se elige: Enviar al fondo

Falta modificar el Estilo de la segmentación de datos para quitar el borde, pero eso lo explicaré en un próximo vídeo...

10) Crear filtro de Año con Escala de tiempo

Se selecciona la celda R2 de la tabla dinámica con el Año y, en Analizar tabla dinámica, se hace clic en: Insertar escala de tiempo

Se marca: Años, y se presiona el botón: Aceptar

La escala de tiempo se sitúa debajo del gráfico y se selecciona el período en AÑOS.

Se hace clic con el botón derecho del ratón en la Escala de tiempo y se elige: Enviar al fondo

En la pestaña Escala de tiempo, en la sección Mostrar se desmarca: Encabezado; Etiqueta de selección y Nivel de tiempo, dejando marcada únicamente la Barra de desplazamiento.

Falta modificar el Estilo de la escala de tiempo para quitar el borde, pero eso lo explicaré en un próximo vídeo...

11) Pirámide de población interactiva por Grupo y Año

Con la segmentación de datos de los grupos de población y con la escala de tiempos para seleccionar el año, se crea una pirámide de población interactiva como la de esta imagen:

Se puede descargar esta pirámide desde la sección de más arriba en este artículo, titulada: Pirámide de población en la nube, pues está incrustada la plantilla en este blog desde la nube de OneDrive.

Vídeo de cómo crear una pirámide de población

En este vídeo se explican los pasos descritos anteriormente para los que no les guste leer cómo crear una pirámide de población. Y también explicaré por qué he colocado las dos tablas dinámicas una debajo de otra. ¡Verás que truco se me ha ocurrido!

Descarga de la comparativa de dos pirámides de población

Descarga la comparativa con 2 pirámides de población v3.0 desde cualquiera de estos enlaces:

• Microsoft OneDrive: Pirámide Poblacional Española - PW3.xlsx

• Sites Google Drive: Pirámide Poblacional Española - PW3.xlsx

La plantilla está protegida sin contraseña, así se protege de los usuarios y a la vez se pueden estudiar y analizar las pirámides de población.

Abre la plantilla con alguna versión reciente de Excel y habilita la edición.

Datos curiosos al comparar pirámides de población

Siempre se ha dicho que las comparaciones son odiosas, pero es la mejor manera de aprender de los datos estadísticos tan fríos...

Comparando dos pirámides de población españolas averiguaremos por ejemplo estas 10 cosas:

1. Que hay más hombres de menos de 50 años que mujeres. ¿Será que los hombres se cuidan menos?
2. Que las mujeres son mayoría con más de 50 años. ¿Será por los genes?
3. Que hay 3 veces más mujeres de más de 90 años que hombres. ¡Los hombres no llegamos a viejos!
4. Que todos los años hay 50.000 niños más que niñas de menos de 5 años. ¿Será por selección natural o artificial?
5. Que los extranjeros son una población creciente y joven. ¡Es natural! ¡Y más con la llegada de ucranianas jóvenes con niños!
6. Que hace 20 años la pirámide española aún tenía forma de pirámide, pero desde hace 10 años tiene forma de tonel. ¡España es un país de viejos!
7. Que en 10 años el grupo de edad más numeroso ha pasado de 35-39 años a 45-49 años. ¡Es la cuenta la vieja!
8. Que los españoles son una población decreciente y envejecida. ¡Vaya noticia!
9. En 2014 había 2 millones de niños menores de 5 años. El año pasado había 400 mil niños menos. A este ritmo, ¡en 30 años no nacerán niños en España!
10. Que aún descontando los fallecidos por la pandemia, la población de más de 100 años siguió creciendo en 2020. Y eso que aún falta por saber los datos de 2021, ¡qué raro!

¿Qué otros datos sacas del análisis de las pirámides de población?

¡Compártelos con nosotros!

Para analizar mejor los datos de población he creado una pirámide alternativa que puedes ver, probar y descargar desde aquí:

Pirámide alternativa de población | #ExcelPedroWave