Cómo calcular el promedio de ventas mensuales en Excel para los últimos n años

Tener un promedio es fácil en Microsoft Excel. Gracias a las funciones AVERAGEIF() y AVERAGEIFS(), los promedios condicionales también son fáciles, hasta que se desea que la condición sea dinámica. Por ejemplo, si tienes una tabla de cifras de ventas mensuales y alguien te pide la capacidad para obtener el promedio de los últimos n años, es posible que te confundas y te sientas un poco abrumado.

Afortunadamente, es más sencillo de lo que crees adaptarse a esa solicitud de n. En este tutorial, te mostraré cómo combinar funciones y características comúnmente utilizadas para obtener promedios mensuales de los últimos n años. Esta técnica también es útil para crear un panel de control.

Estoy utilizando Microsoft 365 en un sistema Windows 10 de 64 bits, pero puedes utilizar versiones anteriores en formato .xlsx. Excel para la web también es totalmente compatible con esta técnica. Para tu comodidad, puedes descargar el archivo de demostración .xlsx.

Cómo encontrar el promedio rápidamente en Excel

Si solo necesitas el promedio para una tarea única, no necesitas la mayoría de este tutorial. En cambio, hay una forma fácil de obtener lo que necesitas sin utilizar funciones, fórmulas, tablas dinámicas, etc. Para obtener el promedio de ventas de cualquier período, sigue estos pasos:

1. Selecciona las cifras de ventas de los años que deseas promediar. Por ejemplo, si quieres obtener el promedio de ventas de los últimos dos años (2021 y 2020), selecciona C3:C26.
2. En la barra de tareas, encontrarás el promedio de los valores seleccionados. En este caso, es de $1,356,157, como se muestra en Figura A.
3. Haz doble clic en el promedio de la barra de tareas.
4. Selecciona la celda donde deseas ingresar este promedio y presiona Ctrl + V.

Figura A

Como se muestra en Figura B, puedes copiar el promedio desde la barra de tareas en tu hoja. Esta es una característica bastante nueva, por lo que es posible que no hayas oído hablar de ella todavía. Si no necesitas calcular este valor nuevamente, utiliza este método rápido para obtener el promedio de los valores seleccionados.

Cómo usar un video como fondo en PowerPoint

Figura B

Cuando necesitas obtener el promedio de ventas de los últimos n años de manera regular, querrás tener algo más permanente en su lugar.

Cómo generar el año a partir de la fecha en Excel

Si tienes suerte, tus datos contendrán el año como un valor único. Nuestra hoja de ejemplo (Figura A) tiene dos columnas: una para las fechas mensuales y otra para los valores de ventas de cada mes. Esto significa que lo primero que debemos hacer es llenar una columna con el año correspondiente para cada fecha. En una situación del mundo real, es posible que tengas datos para docenas de años, pero vamos a limitarnos a cuatro para simplificar.

Para obtener el año de cada fecha en la columna B, ingresa la siguiente función en D3 y cópiala al resto de las celdas:

=AÑO([@Mes])

Utiliza esta función si no estás utilizando una tabla:

5 consejos para aprovechar al máximo Google Groups y optimizar tu membresía

=AÑO(B3)

Figura C muestra los resultados. Si esta función sencilla devuelve un error, revisa el valor de la fecha porque probablemente no sea una fecha válida. Nuestro siguiente paso es agregar una matriz basada en una lista única de valores de año.

Figura C

Ahora necesitamos una forma de trabajar con el componente n, que limitará los valores de ventas evaluados.

Cómo ajustar n

El siguiente paso es ajustar el componente n, que determinará cuántos años deseas evaluar. Figura D muestra algunas etiquetas y expresiones nuevas. Las expresiones en la columna I son las expresiones utilizadas en la columna G.

Figura D

WebStorm vs. VS Code: Cuál es la mejor opción para el desarrollo web y de aplicaciones

En pocas palabras, G3 es un valor de entrada: es igual al número de últimos años que deseas evaluar. Por ejemplo, si ingresas 2, los dos últimos años son 2021 y 2020; si ingresas 3, los tres últimos años son 2021, 2020 y 2019, y así sucesivamente.

La función MAX() devuelve el año más reciente de la tabla de ventas (B2:D50). También podrías ingresar el año más reciente como un valor literal. Ambas formas funcionan, pero usar MAX() evita la necesidad de conocer el último año. No es necesario ordenar los datos para que todo funcione. Dependiendo de cuántos datos haya y de dónde provengan, podría ser difícil determinar el año más reciente visualmente.

La expresión =G3-(G2-1) devuelve el año más antiguo en la tabla de ventas que se evalúa. G3 siempre es el último año con los datos actuales. Esta expresión resta el último año en G2 para determinar el año más temprano que se incluirá en el promedio.

La última expresión que veremos, =SI.ERROR(PROMEDIO.SI(Ventas[Año],">="&$G$4,Ventas[Ventas]),""), realiza la mayor parte del trabajo pesado. La función PROMEDIO.SI() utiliza el componente de criterio, ">="&$G$4, para determinar los años representados por n. Por ejemplo, si ingresas 2, este componente se evalúa de la siguiente manera:

">="&$G$4

Brave vs. Firefox: Cuál es el mejor navegador web para ti

">="&2020

>=2020

En consecuencia, la función PROMEDIO.SI() solo evalúa los valores de ventas donde el año en la columna D es mayor o igual a 2020. En nuestra tabla, esto significa los años 2020 y 2021. La función SI.ERROR() captura los errores generados cuando el valor de entrada no es 1, 2, 3 o 4, porque la tabla solo contiene cuatro años de registros.

También podrías usar un control de validación de datos para rechazar valores de entrada que no sean 1, 2, 3 o 4. Simplemente selecciona G2, haz clic en la pestaña Datos y luego elige Validación de datos en el grupo Herramientas de datos. En el cuadro de diálogo resultante, selecciona Número entero en el campo Permitir, Entre en el campo Datos y luego ingresa 1 y 4 en los campos Mínimo y Máximo, respectivamente, como se muestra en Figura E.

Figura E

Como puedes ver en Figura F, la media de ventas mensuales de los últimos dos años es de $1,356,156.88. Intenta ingresar 1, 3 y 4 para representar el último año, los últimos tres años y los últimos cuatro años, respectivamente. Luego intenta ingresar 0 o 5. El control de validación de datos rechazará los valores de entrada no válidos.

Cómo agregar líderes en Microsoft Word: tutorial y solución de problemas

Figura F

Ahora borra el valor en G2 y observa qué sucede. ¿Esperabas un error? La función ERROR.Es devuelve una cadena vacía ("") en lugar de un error.

Todo es dinámico siempre y cuando utilices un objeto de tabla para los datos originales. Si no lo haces, las expresiones utilizadas para ajustar n no se actualizarán a medida que agregues y elimines registros.

Maximiza tu Mac con estas 9 increíbles aplicaciones

En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Software, allí encontraras muchos artículos similares a Cómo calcular el promedio de ventas mensuales en Excel para los últimos n años , tenemos lo ultimo en tecnología 2023.