Calcular la edad a partir de fechas
En esta publicación, respondo una pregunta de Emmanuel, quien preguntó cómo calcular la edad cuando se le da una lista de fechas de nacimiento. Queremos que la solución sea dinámica, para que sea fácil actualizar el cálculo de edad en el futuro. Como se trata de Excel, hay muchas formas de lograrlo. En esta publicación, demostraré cómo utilizar una consulta Obtener y transformar.
Objetivo
Aquí está la idea básica. Tenemos una tabla de fechas, como se muestra a continuación.
Queremos calcular la edad de cada persona en años. Pero, de ahora en adelante, queremos que sea muy fácil actualizar el cálculo de edad según la fecha actual. Creé un video corto y una narrativa escrita completa con todos los detalles como referencia.
Video
Detalles
Lo lograremos con una consulta Obtener y transformar. Revisaremos cada uno de los siguientes pasos:
- Obtener la tabla en Power Query
- Agrega una columna de edad y transfórmala a años.
- Tabla dinámica para crear las franjas de edad
Hagámoslo.
Nota: Los pasos a continuación se presentan con Excel para Windows 2016. Si está utilizando una versión diferente de Excel, tenga en cuenta que es posible que las funciones presentadas no estén disponibles o que deba descargar e instalar el complemento Power Query.
Obtener la tabla en Power Query
El primer paso es introducir los datos en Power Query. Hacemos esto haciendo clic en cualquier celda dentro de la tabla y seleccionando Datos Desde tabla/rango en el grupo de cinta Obtener y transformar. El Editor de consultas se abre como se muestra a continuación.
¡Y este paso está completo!
Agrega una columna de edad y transfórmala a años.
A continuación, necesitamos una columna de edad. Sorprendentemente, esta parte puede resultar complicada si desea una precisión total para todos los casos, incluidas todas las edades, años bisiestos y cumpleaños de días bisiestos. Entonces, lo que haré es demostrar una forma que funciona en la mayoría de los casos porque es realmente fácil y proporciona una buena aproximación. Pero, si necesita una precisión total y desea manejar los cumpleaños de días bisiestos, etc., descargue el archivo de muestra al final de esta publicación donde le proporcioné otra hoja con los detalles para eso.
El primer paso en nuestra versión simplificada es seleccionar la columna Fecha de nacimiento, como se muestra a continuación.
A continuación, hacemos clic en el comando Agregar columna Fecha Edad . Ahora tenemos una nueva columna Edad que muestra la duración entre la fecha/hora actual y la columna Cumpleaños, como se muestra a continuación.
Nota: al hacer clic en Inicio Actualizar vista previa se actualizan los valores de Edad. Es bueno saberlo porque significa que, en el futuro, todo lo que necesitamos hacer es Actualizar y se calculan los valores de edad actuales.
Ahora podemos elegir cómo queremos que se muestre la columna Edad. En nuestro caso, queremos años, así que hacemos clic en el comando Transformar Duración Años totales . La fórmula resultante en realidad divide el número de días transcurridos por 365. Si queremos factorizar los años bisiestos, podemos editar la fórmula predeterminada cambiando el denominador de 365 a 365,25.
El resultado es un valor decimal. Nos gustaría redondear el número al entero más cercano, por lo que hacemos clic en el comando Transformar Redondear Redondear hacia abajo . La columna Edad resultante se muestra a continuación.
Una vez completadas nuestras transformaciones, simplemente seleccionamos Inicio Cerrar y cargar en… en una nueva hoja de trabajo. La tabla de resultados se carga en Excel como se muestra a continuación.
Cuando queremos actualizar los cálculos de Edad, simplemente hacemos clic derecho en la tabla de resultados y seleccionamos Actualizar.
Pero espera, hubo una pregunta más de Emmanuel, que fue cómo agruparlos en franjas de edad. Para eso podemos usar una tabla dinámica.
Tabla dinámica para crear las franjas de edad
Para agrupar a estos clientes en franjas de edad, podemos utilizar una tabla dinámica tradicional. Primero, seleccionamos cualquier celda dentro de la tabla de resultados y seleccionamos Insertar Tabla dinámica .
A continuación, insertamos los campos Edad, Nombre y Último en el área de diseño Filas . Esto no es exactamente lo que queremos, pero nos estamos acercando. El informe actualizado se muestra a continuación como referencia.
Ahora, a hacer los grupos. Seleccionamos cualquier celda de valor de edad (como 2, 7, 12 o 18). Luego, seleccionamos el comando Tabla dinámica Selección de grupo . El cuadro de diálogo Agrupación resultante se muestra a continuación.
Podemos utilizar los valores predeterminados o definir nuestros propios valores iniciales, finales y de grupo por valores. Al hacer clic en Aceptar se crean los grupos correspondientes, como se muestra a continuación.
Si lo deseamos, podemos aplicar algunas actualizaciones cosméticas para limpiar el informe. Por ejemplo, podríamos ir al cuadro de diálogo Configuración del campo Apellido y seleccionar Diseño e impresión Mostrar etiquetas de elementos en forma tabular . También podríamos hacer clic en Herramientas de tabla PIvotTable Subtotal No mostrar subtotales . Podríamos desmarcar la casilla Herramientas de tabla dinámica Encabezados de fila . Podríamos seleccionar Herramientas de tabla dinámica Totales generales Desactivado para filas y columnas .También podríamos seleccionar todos los grupos de edad para formatos personalizados. Por ejemplo, pasamos el mouse sobre el borde de la celda superior del primer grupo de edad de 2 a 11 años y hacemos clic cuando el puntero se convierte en una flecha hacia abajo. Con todos los grupos de edad seleccionados, podemos aplicar cualquier formato, por ejemplo, podríamos aplicar nuestro Estilo de celda favorito. Finalmente, podemos desactivar la visualización de los botones +/- haciendo clic en el comando Herramientas de tabla dinámica Botones +/- . Y podríamos escribir Último en la celda Etiquetas de fila. El informe actualizado se muestra a continuación.
Más precisión
El método anterior funciona para la mayoría de las combinaciones de cumpleaños/hoy, pero no para todas. Por ejemplo, cumpleaños en días bisiestos. Por lo tanto, incluí una consulta adicional en el archivo de muestra a continuación, junto con las fórmulas de Excel correspondientes, en caso de que necesite ese nivel de precisión.
En esencia, las fórmulas tienen en cuenta los cumpleaños de los días bisiestos (29 de febrero), y si el año actual no es un año bisiesto, trata el cumpleaños como el 28/2 para que la edad avance el 28/2. Pero, cuando el año actual es bisiesto, la edad no avanzará hasta que la fecha actual también sea 29/02.
Si tiene otras formas divertidas de calcular los valores de Edad, tiene alguna sugerencia para mejorar mis fórmulas o encuentra alguna combinación de cumpleaños/hoy donde mis fórmulas se rompen (proporciona resultados inexactos), hágamelo saber para poder actualizarlas en consecuencia. ¡Gracias!
- Archivo de muestra: AgeFromDate.xlsx