Promedio de valores para un mes y año determinados (Microsoft Excel)

George tiene una hoja de trabajo que incluye fechas (en la columna A) y valores asociados con esas fechas (en la columna B). La hoja de trabajo incluye valores de los últimos años. Le gustaría calcular el promedio de todos los valores de un mes determinado en un año determinado. Por ejemplo, a George le gustaría calcular el promedio de todos los valores para febrero de 2020.

Hay varias formas diferentes de abordar este problema. Una forma es crear una tabla dinámica basada en sus datos. (Las tablas dinámicas son excelentes para agregar y analizar grandes cantidades de datos). Puede establecer fácilmente el campo de valor en Promedio (en lugar de la Suma predeterminada) y agrupar la columna Fechas por lo que desee.

Si prefiere no usar una tabla dinámica, hay varias fórmulas que puede agregar a su hoja de trabajo. Por ejemplo, la siguiente fórmula usa la función SUMPRODUCT para calcular el promedio:

La fórmula asume que sus fechas y valores comienzan en la fila 2 (para permitir encabezados) y no pasan de la fila 1000. Si no hay fechas en los datos que estén en el mes de febrero de 2020, la fórmula devuelve un #DIV / 0! error.

Otro enfoque es utilizar una fórmula de matriz, como la siguiente:

Este enfoque es más corto que la fórmula SUMPRODUCT, pero debe recordar mantener presionadas las teclas Ctrl + Shift + Enter mientras ingresa la fórmula.

También obtendrá la división por error cero si no hay datos para el mes y año deseados.

Otro enfoque más es utilizar una de las funciones de base de datos de Excel, DAVERAGE. Todo lo que necesita hacer es configurar una tabla de criterios que defina lo que está buscando. Suponga, por ejemplo, que los títulos de las columnas son algo original, como Fecha (celda A1) y Valor (celda B1).

Puede configurar una tabla de criterios en otro lugar, como D1: E2. La tabla podría verse así:

La tabla de criterios dice que desea que DAVERAGE use cualquier cosa en la que la columna Fecha contenga una fecha mayor que 1/31/20 y una fecha menor que 3/1/20. Aquí está la fórmula:

El primer parámetro define su base de datos, el segundo parámetro indica que desea promediar la información en la columna Valor (columna B) y el tercer parámetro le dice a DAVERAGE dónde se encuentra su tabla de criterios.

Una forma bastante sencilla es aplicar un filtrado de fechas y utilizar la función SUBTOTAL. Ingrese la siguiente fórmula en una celda:

Seleccione una celda en su rango de datos y filtre sus datos (muestre la pestaña Datos de la cinta y haga clic en la herramienta Filtro). Haga clic en la flecha de filtrado en la parte superior de la columna A y luego seleccione Filtros de fecha | Filtro personalizado de la lista desplegable. Excel muestra el cuadro de diálogo Autofiltro personalizado.

(Ver figura 1)

Figura 1. Cuadro de diálogo Autofiltro personalizado.

Utilice los controles del cuadro de diálogo para especificar que desea registros mayores que 1/31/20 y menores que 3/1/20. Cuando hace clic en Aceptar, solo se muestran los registros dentro de febrero de 2020, y la fórmula de subtotal muestra el promedio de esos registros visibles.

Quizás el enfoque más simple, sin embargo, es usar la función AVERAGEIFS. Te permite calcular un promedio de algunos valores dependiendo de si un valor asociado (la fecha, en este caso) cumple con los criterios que especificas. Así es como se ve la fórmula:

Tenga en cuenta que esta no es una fórmula de matriz y no necesita definir una tabla de criterios para sus criterios; están integrados directamente en la fórmula.

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (10671) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365. Puede encontrar una versión de este consejo para la interfaz de menú anterior de Excel aquí:

link: / excel-Averaging_Values_for_a_Given_Month_and_Year [Promedio de valores para un mes y año determinados].