Suponga que tiene una hoja de trabajo enorme que contiene todas las lecturas de lluvia para un lugar determinado durante los últimos cien años aproximadamente. En las celdas A2: A42733 tiene las fechas, del 1 de enero de 1903 al 31 de diciembre de 2019.

En las celdas B2: B42733 tienes las medidas para cada fecha. Además, algunas de las mediciones pueden ser cero (si no hay lluvia durante el día) o en blanco (si no se tomó ninguna lectura ese día en particular). Con toda esta información, desea calcular el promedio histórico de precipitaciones para un día determinado del año.

Una solución implica el uso de fórmulas de matriz, como se detalla aquí:

  1. Seleccione todas las celdas de la columna A que contienen fechas y asigne a este rango el nombre Fechas.

  2. Seleccione todas las celdas correspondientes en la columna B; estos contienen los datos de precipitación para las fechas en la columna A. Asigne a este rango el nombre de Precipitación.

  3. En la columna D, comenzando en la celda D2, coloque todos los días del año. Debería terminar con D2 a D366 lleno de fechas.

  4. En la celda E2, ingrese la siguiente fórmula de matriz (termine la fórmula presionando Shift + Ctrl + Enter). El resultado de la fórmula es la suma de todas las celdas en el rango de Lluvia, para la fecha especificada en la celda D2.

  5. En la celda F2, ingrese la siguiente fórmula de matriz (termine la fórmula presionando Shift + Ctrl + Enter). El resultado de la fórmula es el número de celdas en el rango de Lluvia, para la fecha en la celda D2, que tienen un valor en ellas.

  6. En la celda G2, ingrese la siguiente fórmula regular. Este es su promedio para la fecha en la celda D2.

  7. Seleccione el rango E2: G2 y cópielo para todas las fechas que se muestran en la columna D.

Este enfoque funciona, pero lleva bastante tiempo calcularlo. Esto se debe a que ingresó efectivamente 822 fórmulas de matriz, cada una de las cuales verificó más de 42,000 celdas. Esto es mucho trabajo y, en consecuencia, puede parecer que su máquina se ha «colgado» después de completar el paso 7. No se ha bloqueado; solo tomará un tiempo completar los cálculos.

Para reducir el número de cálculos que se deben realizar, puede utilizar una variación de los pasos anteriores. Siga los pasos 1 a 3, como se indica, y luego coloque la siguiente fórmula de matriz en la celda E2:

=AVERAGE(IF((Dates<>0)(Rainfall<>0)(MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2)),Rainfall))

Luego puede copiar la fórmula para todas las fechas que se muestran en la columna D.

El resultado de esta fórmula es la precipitación media real, la misma que se había mostrado en la columna G en el enfoque anterior.

Puede reducir aún más la sobrecarga de cálculo simplemente eliminando toda la tabla que calcula los promedios para todos los días del año. Con sus fechas y precipitaciones en las columnas A y B, siga estos pasos:

  1. Seleccione todas las celdas de la columna A que contienen fechas y asigne a este rango el nombre Fechas.

  2. Seleccione todas las celdas correspondientes en la columna B; estos contienen los datos de precipitación para las fechas en la columna A. Asigne a este rango el nombre de Precipitación.

  3. En la celda D2, coloque la fecha para la que desea verificar la precipitación promedio. (El año no es importante; solo se utilizan el mes y el día en el cálculo).

  4. Ingrese la siguiente fórmula en la celda E2:

=AVERAGE(IF((Dates<>0)(Rainfall<>0)(MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2)),Rainfall))

Eso es. Ahora, puede cambiar la fecha en la celda D2 como desee, y la celda E2 siempre indicará la precipitación promedio para esa fecha. La fórmula en la celda E2 es la misma que la fórmula utilizada en el último enfoque; la diferencia es que no lo está calculando para todos los días del año y, por lo tanto, el cálculo se hace mucho más rápido.

Otro enfoque implica el uso de las capacidades de filtrado de Excel.

Sin embargo, antes de poder usarlos correctamente, debe crear una columna que muestre solo el mes y el día de cada fecha en sus datos. Use esta fórmula en la celda C2:

=MONTH(A2) & "-" & DAY(A2)

Ahora, active Autofiltrado (muestre la pestaña Datos en la cinta | Grupo Ordenar y filtrar | Filtro) y use la lista desplegable en la parte superior de la nueva columna para seleccionar la fecha para la que desea un promedio. Luego, use la siguiente fórmula, colocada en cualquier celda deseada, para mostrar la precipitación promedio para la fecha seleccionada:

=SUBTOTAL(1,B:B)

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

Este consejo (10319) 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-Calculating_Average_by_Date [Calculando promedios por fecha].