Derivación de valores medios mensuales (Microsoft Excel)

John tiene una hoja de trabajo enorme que incluye datos diarios que cubren unos sesenta años. Quiere llegar a una fórmula que calcule los valores medianos de cada mes en el período de tiempo cubierto por los datos.

Antes de proponer una solución, es mejor plantear algunos supuestos. Para los propósitos de este consejo, supongamos que los datos diarios están en las columnas A y B. En la columna A están las fechas asociadas con los datos y en la columna B están los valores de datos reales para cada una de esas fechas. Además, las celdas A1 y B1 contienen títulos para cada columna. Esto significa que los datos reales están aproximadamente en el rango de A2: B22000.

Para que sus fórmulas sean más fáciles de usar, debe definir nombres para los datos en las columnas A y B. Seleccione el rango de fechas (por ejemplo, A2: A22000) y asígnele un nombre como AllDates. Seleccione el rango de datos correspondientes (por ejemplo, B2: B22000) y utilice el mismo proceso para crear un nombre como AllData.

Puede utilizar fórmulas de matriz para calcular los valores medianos reales. Esto implica la creación de otra tabla de datos para contener las medianas. Coloque títulos como «Mes» en la celda E1 y «Mediana» en la celda F1. En la celda E2, coloque el primer día del primer mes de su conjunto de datos, como 1/1/1940. En la celda E3, ponga una fecha que sea un mes después, como el 2/1/1940. Seleccione estas dos celdas (E2: E3) y arrastre el controlador de relleno hacia abajo para el número de meses que desee en la tabla de datos.

Si no hay espacios en blanco en sus sesenta años de datos, ingrese la siguiente fórmula en la celda F2:

Finalice la fórmula presionando Ctrl + Shift + Enter, lo que le dice a Excel que esta es una fórmula de matriz. Luego puede copiar la fórmula en F2 en cada una de las celdas en la columna F que tiene un mes correspondiente en la columna E. La fórmula analiza las fechas en la columna B, y si el año y el mes son iguales a cualquier fecha que ingrese en la celda E2 , entonces la mediana se calcula a partir de todos los puntos de datos correspondientes.

Si hay espacios en blanco en sus sesenta años de datos (una fecha en la columna A sin valor correspondiente en la columna B), entonces la fórmula trata el espacio en blanco como si fuera un valor cero. Si tiene espacios en blanco, esto puede resultar en medianas sesgadas. Para evitar esto, puede usar una fórmula de matriz diferente que verifique e ignore cualquier valor en blanco:

Hay una advertencia para usar fórmulas de matriz de esta manera. Si tiene sesenta años de datos, con aproximadamente 22.000 valores individuales, entonces todavía son muchos meses: unos 720 de ellos. Eso significa que está creando 720 fórmulas de matriz, cada una de las cuales analiza 22,000 valores de datos para llegar a una respuesta. Eso es mucho cálculo, por lo que notará una desaceleración en la capacidad de respuesta de Excel cada vez que recalcula la hoja de trabajo.

Si la lentitud se convierte en un problema, entonces podría considerar reorganizar sus datos originales para que cada fila de la hoja de trabajo represente un mes individual. La columna A podría contener el mes de la fila (1/1/1940, 2/1/1940, 3/1/1940, etc.) y las columnas B: AF serían los días 1 a 31 de cada mes. Las celdas que se cruzan en la tabla podrían contener el punto de datos para cada día del mes, y podría usar la función MEDIANA en la columna AG para calcular la mediana de cada mes. Esto todavía da como resultado 720 fórmulas, pero estas son fórmulas regulares que cada una solo necesita procesar el valor de un mes de datos en lugar de las fórmulas de matriz que necesitan para cada proceso sesenta años de datos. El resultado son cálculos mucho más rápidos.

Por supuesto, para la mayoría de las personas, la idea de reorganizar una cantidad tan grande de datos es suficiente para mantenerte despierto por la noche. En cambio, puede adoptar un enfoque completamente diferente para analizar los datos. Este enfoque es posible porque una mediana es una función estadística muy fácil de calcular manualmente. Simplemente ordena tu conjunto de datos y, si el número de elementos del conjunto de datos es impar, selecciona el elemento del medio. Si el número de elementos es par, toma el promedio de los dos elementos del medio.

Para prepararse para analizar los datos, hay un par de cosas que hacer.

Primero, será útil tener alguna forma de identificar de forma única el mes de cada punto de datos. En la celda C2 coloque la siguiente fórmula:

Esto da como resultado un valor como 194001, 194002, 194003, etc. que se almacena en la columna C. Este es el valor único del mes. Ahora, debe ordenar los datos por columna C y luego por columna B. Ambas clasificaciones deben estar en orden ascendente, de modo que termine con sus datos primero ordenados por año / mes y luego por valor dentro del año / mes.

A continuación, debe agregar subtotales a sus datos. Elija Subtotales en el menú Datos, que muestra el cuadro de diálogo Subtotal. Desea agregar un subtotal en cada cambio en la columna C. La función a usar es Contar, y desea agregar el subtotal a la columna B. Cuando hace clic en Aceptar, termina con 720 subtotales, uno para cada mes en los datos. rango, cada uno mostrando un recuento de cuántos elementos de datos había en ese mes.

Para obtener las medianas, agregue una fórmula a la celda D2:

La fórmula examina lo que hay en la celda B2, y si contiene la palabra «Recuento», entonces sabe que se trata de una fila de subtotal. En ese caso, verifica si el recuento en la celda C2 es par o impar. Si es impar, entonces la función INDIRECTO se usa para tomar el valor mediano del mes. Si es par, los dos valores medios del mes se suman y se dividen por la mitad.

El resultado es que ahora tiene valores medianos para cada mes, en la misma fila que sus subtotales. Por lo tanto, puede contraer el esquema de los datos (haga clic en los signos más en el área del esquema a la izquierda de sus datos)

para que solo muestre esas filas de subtotales.

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

Este consejo (12727) 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-Deriving_Monthly_Median_Values ​​[Derivación de valores medianos mensuales].