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 mayo de 2011.
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:
=SUMPRODUCT((MONTH(A2:A1000)=5)(YEAR(A2:A1000)=2011)(B2:B1000)) / (SUMPRODUCT((MONTH(A2:A1000)=5)(YEAR(A2:A1000)=2011)1))
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 mayo de 2011, entonces la fórmula devuelve un #DIV / 0!
error.
Otro enfoque es utilizar una fórmula de matriz, como la siguiente:
=AVERAGE(IF((MONTH(A2:A1000)=5)*(YEAR(A2:A1000)=2011),B2:B1000))
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í:
Date Date >4/30/11 <6/1/11
La tabla de criterios dice que desea que DAVERAGE use cualquier elemento en el que la columna Fecha contenga una fecha mayor que 30/4/11 y una fecha menor que 1/6/11. Aquí está la fórmula:
=DAVERAGE(A1:B1000,"Value",D1:E2)
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:
=SUBTOTAL(101,B2:B1000)
Seleccione una celda en su rango de datos y active el Autofiltro (elija Datos | Filtro | Autofiltro). Haga clic en la flecha de filtrado en la parte superior de la columna A y luego elija Filtro personalizado en la lista desplegable. Excel muestra el cuadro de diálogo Autofiltro personalizado. (Ver figura 1)
Figura 1. Cuadro de diálogo Autofiltro personalizado.
Use los controles en el cuadro de diálogo para especificar que desea registros mayores que 4/30/11 y menores que 6/1/11. Cuando hace clic en Aceptar, solo se muestran los registros de mayo de 2011 y la fórmula de subtotal muestra el promedio de esos registros visibles.
ExcelTips es su fuente de formación rentable en Microsoft Excel.
Este consejo (10670) se aplica a Microsoft Excel 97, 2000, 2002 y 2003.
Puede encontrar una versión de este consejo para la interfaz de cinta de Excel (Excel 2007 y posterior) aquí:
link: / excelribbon-Averaging_Values_for_a_Given_Month_and_Year [Valores promediados para un mes y año determinados]
.