Fórmula genérica
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Resumen
Para promediar por mes, puede usar una fórmula basada en la función AVERAGEIFS, con la ayuda de la función EOMONTH. En el ejemplo que se muestra, la fórmula en F4 es:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Esta fórmula utiliza los rangos con nombre "cantidades" (D5: D104) y "fechas" (C5: C104).
Explicación
La función AVERAGEIFS puede promediar rangos según múltiples criterios. En este caso, configuramos AVERAGEIFS para promediar montos por mes usando dos criterios: (1) coincide con fechas mayores o iguales al primer día del mes, (2) coincide con fechas menores o iguales que el último día del mes. Si codificamos las fechas de enero de 2016 en la fórmula utilizando la función FECHA, se vería así.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Pero no queremos codificar fechas, queremos que Excel las genere por nosotros. Normalmente, esto es una molestia, porque si agrega nombres de meses como texto (es decir, "enero", "febrero", "marzo", etc.) en la columna F, tendrá que hacer un esfuerzo adicional para crear fechas que pueda usar como criterio. .
Sin embargo, en este caso, usamos un truco simple para facilitar las cosas: en la columna F, en lugar de escribir los nombres de los meses, agregamos fechas reales para el primero de cada mes (1/1/2016, 2/1/2016, 3 / 1/2016, etc.) y utilice un formato de fecha personalizado ("mmm") para mostrar los nombres de los meses.
Esto facilita la creación de los criterios que necesitamos para AVERAGEIFS. Para hacer coincidir fechas mayores o iguales al primero del mes, usamos:
">="&E4
Y para hacer coincidir fechas menores o iguales al último día del mes, usamos:
"<="&EOMONTH(E4,0)
EOMONTH devuelve automáticamente el último día del mismo mes porque proporcionamos cero para el argumento de meses.
Nota: la concatenación con un ampersand (&) es necesaria cuando se crean criterios basados en una referencia de celda.
Solución de tabla dinámica
Una tabla dinámica es una solución excelente cuando necesita resumir datos por año, mes, trimestre, etc., porque las tablas dinámicas proporcionan controles para la agrupación automática por fecha. Para ver una comparación en paralelo de fórmulas y tablas dinámicas, vea este video: Por qué las tablas dinámicas.
