Media móvil Este ejemplo le enseña cómo calcular el promedio móvil de una serie de tiempo en Excel. Una gran ventaja se utiliza para suavizar las irregularidades (picos y valles) para reconocer fácilmente las tendencias. 1. En primer lugar, echemos un vistazo a nuestra serie de tiempo. 2. En la ficha Datos, haga clic en Análisis de datos. Nota: no puede encontrar el botón Análisis de datos Haga clic aquí para cargar el complemento Herramientas de análisis. 3. Seleccione Media móvil y haga clic en Aceptar. 4. Haga clic en el cuadro Rango de entrada y seleccione el rango B2: M2. 5. Haga clic en el cuadro Interval y escriba 6. 6. Haga clic en el cuadro Rango de salida y seleccione la celda B3. 8. Trazar un gráfico de estos valores. Explicación: dado que establecemos el intervalo en 6, el promedio móvil es el promedio de los 5 puntos de datos anteriores y el punto de datos actual. Como resultado, los picos y valles se suavizan. El gráfico muestra una tendencia creciente. Excel no puede calcular el promedio móvil para los primeros 5 puntos de datos porque no hay suficientes puntos de datos anteriores. 9. Repita los pasos 2 a 8 para el intervalo 2 y el intervalo 4. Conclusión: Cuanto mayor sea el intervalo, más se suavizarán los picos y los valles. Cuanto más pequeño es el intervalo, más cerca están las medias móviles de los puntos de datos reales. ¿Te gusta este sitio web gratis? Comparte esta página en GoogleComo calcular los promedios móviles en Excel Excel Data Analysis For Dummies, 2nd Edition El comando Data Analysis proporciona una herramienta para calcular promedios móviles y exponencialmente suavizados en Excel. Supongamos, por razones ilustrativas, que usted ha recopilado información diaria sobre la temperatura. Desea calcular el promedio móvil de tres días 8212 el promedio de los últimos tres días 8212 como parte de algún pronóstico meteorológico simple. Para calcular las medias móviles para este conjunto de datos, siga estos pasos. Para calcular una media móvil, primero haga clic en el botón de comando Data Analysis (Análisis de datos) tab8217s. Cuando Excel muestra el cuadro de diálogo Análisis de datos, seleccione el elemento Promedio móvil de la lista y, a continuación, haga clic en Aceptar. Excel muestra el cuadro de diálogo Promedio móvil. Identifique los datos que desea utilizar para calcular el promedio móvil. Haga clic en el cuadro de texto Intervalo de entrada del cuadro de diálogo Promedio móvil. A continuación, identifique el intervalo de entrada, ya sea escribiendo una dirección de rango de hoja de cálculo o utilizando el mouse para seleccionar el rango de hoja de cálculo. Su referencia de rango debe usar direcciones de celdas absolutas. Una dirección de celda absoluta precede la letra de la columna y el número de fila con signos, como en A1: A10. Si la primera celda de su rango de entrada incluye una etiqueta de texto para identificar o describir sus datos, active la casilla de verificación Etiquetas en primera fila. En el cuadro de texto Intervalo, indique a Excel cuántos valores deben incluirse en el cálculo del promedio móvil. Puede calcular un promedio móvil usando cualquier número de valores. De forma predeterminada, Excel utiliza los tres valores más recientes para calcular el promedio móvil. Para especificar que se utilice otro número de valores para calcular el promedio móvil, ingrese ese valor en el cuadro de texto Intervalo. Dígale a Excel dónde colocar los datos del promedio móvil. Utilice el cuadro de texto Rango de salida para identificar el intervalo de hoja de cálculo en el que desea colocar los datos del promedio móvil. En el ejemplo de la hoja de cálculo, los datos del promedio móvil se han colocado en el rango B2 de la hoja de cálculo: B10. (Opcional) Especifique si desea un gráfico. Si desea un gráfico que trace la información del promedio móvil, seleccione la casilla de verificación Salida del gráfico. (Opcional) Indique si desea calcular la información de error estándar. Si desea calcular errores estándar para los datos, seleccione la casilla de verificación Estándar Errores. Excel coloca valores de error estándar junto a los valores de media móvil. (La información de error estándar pasa a C2: C10.) Una vez que haya terminado de especificar qué información del promedio móvil desea calcular y dónde desea colocarla, haga clic en Aceptar. Excel calcula la información del promedio móvil. Nota: Si Excel doesn8217t tiene suficiente información para calcular un promedio móvil para un error estándar, coloca el mensaje de error en la celda. Puedes ver varias celdas que muestran este mensaje de error como un valor. Guarda tres horas de trabajo al día con estos 5 hacks sencillos de Excel Trabaja más inteligente, no más difícil. Inscríbete en nuestro mini-curso de 5 días para recibir lecciones sobre cómo automatizar tu trabajo con Excel. Por qué las funciones TRUE y FALSE son tan importantes. ¿Por qué repensar la forma de hacer VLOOKUPs. Además, bien revelar por qué no debe utilizar PivotTables y qué utilizar en su lugar. Encontrar un promedio móvil en Excel El cálculo de un promedio móvil (también conocido como promedio móvil) es fácil en Excel utilizando la fórmula MEDIA combinada con referencias de celdas absolutas y relativas. Un promedio dinámico ayuda a suavizar las tendencias con el tiempo, sobre todo cuando sus datos muestran ciclicidad por semana, mes o año. Echemos un vistazo a lo que son los promedios de balanceo, y cómo se pueden calcular en Excel. Si no está familiarizado con la diferencia entre las referencias de celdas relativas y absolutas, eche un vistazo a nuestro tutorial de referencias de celdas antes de continuar ¿Qué es un promedio de balanceo? En muchos escenarios empresariales, los datos como ventas de productos pueden fluctuar significativamente debido a la ciclicidad o la estacionalidad. Esta fluctuación puede hacer que sea muy difícil decir, al mirar datos en bruto si se trata de una dirección positiva o negativa. Tomemos, por ejemplo, los siguientes escenarios: Un restaurante quiere averiguar si su número de clientes diarios está aumentando o disminuyendo. Sin embargo, el restaurante siempre recibe más clientes en los fines de semana mdash por lo que comparar el número de clientes atendidos en un martes a la cantidad de clientes atendidos en el sábado anterior es engañoso. A pesar de que el número de clientes atendidos puede bajar, esto puede ser debido al hecho de que es un martes mdash no debido al hecho de que el restaurante es realmente hacer menos negocios. Un gerente de ventas quiere averiguar si su tienda está vendiendo más juguetes cada mes. Ella compara las ventas de diciembre a las ventas de febrero y ve una disminución significativa. Sin embargo, ella piensa que estos datos pueden no ser representativos de las tendencias de ventas reales, porque el minorista siempre vende un montón de juguetes alrededor de las fiestas. Ambos de los problemas anteriores se pueden resolver tomando medias de rodadura el promedio de varios puntos de datos en lugar de una comparación directa de un solo punto. Tenga en cuenta lo siguiente: El restaurante puede tomar el número promedio de clientes servidos por cada día durante los últimos siete días sobre una base continua. Así que el sábado, el restaurante promedio de datos del domingo anterior a sábado el martes, el restaurante promedio de los datos del miércoles anterior a través de martes. Esta media móvil asegura que una semana completa de datos siempre se contabilizan, lo que permite comparaciones precisas a lo largo de semanas. El gerente de ventas puede calcular las ventas mensuales promedio del año pasado sobre una base de balanceo. Así que en diciembre, ella los datos promedio de enero anterior a diciembre en febrero, que los datos promedio de marzo anterior a febrero. Esto le proporciona una imagen más exacta y balanceada de las ventas anuales a medida que avanza el tiempo, lo que controla la estacionalidad en el ciclo de ventas. En resumen, los promedios de rodadura se utilizan para suavizar los datos durante un período significativo de tiempo y dar cuenta de la estacionalidad y la tendencia cíclica. Cálculo manual de un promedio móvil en Excel es sencillo: creamos una nueva columna, usamos la función MEDIA para calcular un promedio durante un período de tiempo especificado y aprovechamos la funcionalidad de referencia relativa de celdas de Excels para asegurar que Nuestro promedio de movimientos a medida que avanza nuestro conjunto de datos. Veamos un ejemplo usando números de ventas diarias. Echa un vistazo a la siguiente hoja, que muestra las ventas diarias de SnackWorld de cookies: Como puede ver, las ventas de galletas varían ampliamente por día, llegando a mediados de semana cuando muchos clientes están pidiendo productos. Vamos a utilizar un promedio móvil para suavizar esta tendencia y averiguar lo que está sucediendo a las ventas a largo plazo: Hay un par de cosas a tener en cuenta en la fórmula anterior: Primero, comenzamos a introducir nuestra fórmula en la celda E9. Porque el 15 de septiembre es el primer día en el que tenemos un total de siete días de datos a la media. No sería posible tomar un promedio móvil de 7 días a partir del 14 de septiembre, porque sólo hemos recopilado seis días de datos en ese momento. Cada vez que calculamos un promedio móvil utilizando este método, también necesitamos ejercer un juicio manual para averiguar en qué parte de nuestros datos comenzar. En segundo lugar, utilizamos una referencia de celda relativa para calcular el PROMEDIO de los últimos siete días de datos a partir del 15 de septiembre promediando las células D3: D9. La salida de esta fórmula es 5012.37. Tenga en cuenta que al copiar y pegar esta fórmula en otras celdas, el rango que se promedió cambia porque estamos usando referencias de celdas relativas. Por ejemplo, la fórmula copiada y pegada a la celda E13 tomará el promedio del rango D7: D13. Porque eso representa los últimos 7 días de datos: Con nuestro cálculo de promedio móvil, podemos decir claramente que las ventas semanales están tendiendo consistentemente hacia arriba. Este método de cálculo ha simplificado drásticamente un conjunto de datos que de otro modo era muy difícil de interpretar. Promedio de promedios usando el Analysis ToolPak Calculamos todos nuestros promedios de rodadura manualmente para que podamos ejercer un control total sobre los resultados. Pero si te gustaría que las cosas fueran más automáticas, puedes usar una herramienta llamada Analysis ToolPak para acelerar las cosas. Heres cómo funciona: En primer lugar, abra el cuadro de herramientas de análisis, vaya a Análisis de datos de datos: No vea la opción Análisis de datos en el menú Probablemente no ha activado el ToolPak de análisis en su versión de Excel. Para activarlo, vaya a Opciones de archivo Add-Ins Seleccione Analysis ToolPak Haga clic en Ir. Check Analysis ToolPak Haga clic en Aceptar A continuación, seleccione la opción Media móvil en el menú ToolPak: En el diálogo que sigue, ingrese las celdas de entrada y salida para su promedio, así como su intervalo. En nuestro ejemplo, nuestras células de entrada son D3: D23. Ya que esas células representan nuestro número de ventas, y nuestras células de salida son E3: E23. Ya que es donde queremos que nuestro promedio de 7 días de rodadura para ir. En el recuadro de entrada Intervalo, ponemos el número 7. porque queremos tomar el promedio móvil en 7 días. Hay un número de opciones adicionales en este diálogo que puede jugar con, incluyendo una característica que generará automáticamente un gráfico de nuestra salida. Excel rellenará automáticamente las fórmulas para calcular el promedio de rodadura según nuestras especificaciones. Tenga en cuenta que el programa devuelve errores N / A en las celdas dentro de las cuales es imposible calcular un promedio móvil con nuestro intervalo de 7 días dado. Ahora ya sabe todo lo que necesita para calcular promedios de rodadura en Excel. Utilice esta herramienta práctica para suavizar sus datos y controlar la estacionalidad y la coyuntura al realizar análisis. Ahorre tres horas de trabajo al día con estos 5 simples hacks de Excel. Trabaje de manera más inteligente, no más difícil. Inscríbete en nuestro mini-curso de 5 días para recibir lecciones sobre cómo automatizar tu trabajo con Excel. Por qué las funciones TRUE y FALSE son tan importantes. ¿Por qué repensar la forma de hacer VLOOKUPs. Además, bien revelar por qué no debe utilizar las tablas dinámicas y qué utilizar en su lugar. Calcular el promedio móvil en Excel En este breve tutorial, usted aprenderá cómo calcular rápidamente un promedio móvil simple en Excel, qué funciones utilizar para obtener el promedio móvil para el último N días, semanas, meses o años y cómo agregar una línea de tendencia de media móvil a un gráfico de Excel. En un par de artículos recientes, hemos examinado de cerca el cálculo del promedio en Excel. Si has estado siguiendo nuestro blog, ya sabes cómo calcular un promedio normal y qué funciones utilizar para encontrar el promedio ponderado. En el tutorial de hoy, vamos a discutir dos técnicas básicas para calcular el promedio móvil en Excel. En general, el promedio móvil (también denominado media móvil, promedio móvil o media móvil) puede definirse como una serie de promedios para diferentes subconjuntos del mismo conjunto de datos. Se utiliza con frecuencia en estadísticas, previsiones económicas y meteorológicas ajustadas estacionalmente para comprender las tendencias subyacentes. En el comercio de valores, el promedio móvil es un indicador que muestra el valor promedio de un valor en un período de tiempo dado. En los negocios, es una práctica común para calcular un promedio móvil de las ventas de los últimos 3 meses para determinar la tendencia reciente. Por ejemplo, el promedio móvil de las temperaturas de tres meses se puede calcular tomando el promedio de las temperaturas de enero a marzo, luego el promedio de las temperaturas de febrero a abril, luego de marzo a mayo, y así sucesivamente. Existen diferentes tipos de media móvil, tales como simple (también conocido como aritmética), exponencial, variable, triangular y ponderada. En este tutorial, estaremos estudiando el promedio móvil más comúnmente usado. Calculando el promedio móvil simple en Excel En general, hay dos maneras de obtener una media móvil simple en Excel: mediante fórmulas y opciones de línea de tendencia. Los siguientes ejemplos demuestran ambas técnicas. Ejemplo 1. Calcular el promedio móvil durante un cierto período de tiempo Se puede calcular un promedio móvil simple en ningún momento con la función MEDIA. Supongamos que tiene una lista de temperaturas medias mensuales en la columna B y desea encontrar una media móvil de 3 meses (como se muestra en la imagen anterior). Escriba una fórmula normal de promedio para los primeros 3 valores e introdúzcala en la fila correspondiente al 3er valor de la parte superior (celda C4 en este ejemplo) y luego copie la fórmula a otras celdas de la columna: Columna con una referencia absoluta (como B2) si desea, pero asegúrese de utilizar referencias de fila relativa (sin el signo) para que la fórmula se ajusta correctamente para otras celdas. Recordando que un promedio se calcula sumando valores y luego dividiendo la suma por el número de valores a promediar, puede verificar el resultado usando la fórmula SUM: Ejemplo 2. Obtenga el promedio móvil de los últimos N días / semanas / Meses / años en una columna Suponiendo que tiene una lista de datos, por ejemplo Cifras de ventas o cotizaciones de acciones, y desea conocer el promedio de los últimos 3 meses en cualquier momento. Para ello, necesita una fórmula que recalcule el promedio tan pronto como introduzca un valor para el próximo mes. ¿Qué función de Excel es capaz de hacer esto? La buena PROMEDIO antiguo en combinación con OFFSET y COUNT. NOMBRE PROMEDIO (OFFSET (primera celda, COUNT (rango completo) - N, 0, N, 1)) Donde N es el número de los últimos días / semanas / meses / años para incluir en el promedio. No está seguro de cómo usar esta fórmula de promedio móvil en sus hojas de cálculo de Excel El ejemplo siguiente hará las cosas más claras. Suponiendo que los valores a la media están en la columna B comenzando en la fila 2, la fórmula sería la siguiente: Y ahora, vamos a tratar de entender lo que esta fórmula de promedio móvil Excel está haciendo realmente. La función COUNT COUNT (B2: B100) cuenta cuántos valores ya están ingresados en la columna B. Comenzamos a contar en B2 porque la fila 1 es el encabezado de columna. La función OFFSET toma la celda B2 (el primer argumento) como punto de partida y compensa el recuento (el valor devuelto por la función COUNT) moviendo 3 filas hacia arriba (-3 en el 2do argumento). Como resultado, devuelve la suma de valores en un rango que consta de 3 filas (3 en el 4 º argumento) y 1 columna (1 en el último argumento), que es el último 3 meses que queremos. Finalmente, la suma devuelta se pasa a la función MEDIA para calcular el promedio móvil. Propina. Si está trabajando con hojas de trabajo continuamente actualizables en las que es probable que se añadan nuevas filas en el futuro, asegúrese de proporcionar un número suficiente de filas a la función COUNT para acomodar nuevas entradas potenciales. No es un problema si se incluyen más filas de lo que realmente se necesita, siempre y cuando tenga la primera celda derecha, la función COUNT descartará todas las filas vacías de todos modos. Como probablemente habrás notado, la tabla de este ejemplo contiene datos durante sólo 12 meses, y, sin embargo, el rango B2: B100 se suministra a COUNT, sólo para estar en el lado de guardar :) Ejemplo 3. Obtener el promedio móvil de los últimos valores N Una fila Si desea calcular una media móvil para los últimos N días, meses, años, etc. en la misma fila, puede ajustar la fórmula de desplazamiento de esta manera: Suponiendo que B2 es el primer número en la fila y desea Para incluir los últimos 3 números en el promedio, la fórmula toma la siguiente forma: Creación de un gráfico de promedio móvil de Excel Si ya ha creado un gráfico para sus datos, agregar una línea de tendencia de media móvil para ese gráfico es cuestión de segundos. Para ello, vamos a utilizar la función de Excel Trendline y los pasos detallados a continuación. Para este ejemplo, he creado un gráfico de columnas en 2D (grupo Insertar pestaña gt) para nuestros datos de ventas: Y ahora, queremos visualizar el promedio móvil durante 3 meses. En Excel 2010 y Excel 2007, vaya a Layout gt Trendline gt Más opciones de línea de tendencia. Propina. Si no necesita especificar los detalles, como el intervalo o los nombres del promedio móvil, puede hacer clic en Design gt Add Chart Elemento gt Trendline gt Promedio móvil para el resultado inmediato. El panel Formato de líneas de tendencia se abrirá en el lado derecho de la hoja de cálculo en Excel 2013 y el cuadro de diálogo correspondiente aparecerá en Excel 2010 y 2007. Para refinar su conversación, puede cambiar a la línea El panel Formato de línea de tendencia y el juego con diferentes opciones como el tipo de línea, color, ancho, etc. Para un análisis de datos potente, puede agregar algunas líneas de tendencia de media móvil con diferentes intervalos de tiempo para ver cómo evoluciona la tendencia. La siguiente captura de pantalla muestra las líneas de tendencia de 2 meses (verde) y 3 meses (rojo de ladrillo): Bueno, eso es todo sobre el cálculo del promedio móvil en Excel. La hoja de cálculo de ejemplo con las fórmulas de promedio móvil y la línea de tendencia está disponible para descargar - hoja de cálculo de Moving Average. Le doy las gracias por leer y esperamos verla la semana que viene También puede ser que esté interesado en: Rolling Average Table A continuación veremos un programa en Excel VBA que crea una tabla media móvil. Coloque un botón de comando en su hoja de cálculo y agregue la línea de código siguiente: Range (quotB3quot).Value WorksheetFunction. RandBetween (0, 100) Esta línea de código introduce un número aleatorio entre 0 y 100 en la celda B3. Queremos que Excel VBA tome el nuevo valor de stock y lo coloque en la primera posición de la tabla media móvil. Todos los demás valores deben moverse hacia abajo en un lugar y el último valor debe suprimirse. Crear un evento de cambio de hoja de cálculo. El código agregado al evento de cambio de hoja de cálculo será ejecutado por Excel VBA cuando cambie una celda en una hoja de cálculo. 2. Haga doble clic en Sheet1 (Sheet1) en el Explorador de proyectos. 3. Seleccione Hoja de trabajo en la lista desplegable de la izquierda. Seleccione Cambiar en la lista desplegable derecha. Agregue las siguientes líneas de código al evento de cambio de hoja de cálculo: 4. Declare una variable llamada newvalue del tipo Integer y dos rangos (firstfourvalues y lastfourvalues). Dim newvalue As Integer. Firstfourvalues As Range, lastfourvalues As Range 5. El evento de cambio de hoja de trabajo escucha todos los cambios en Sheet1. Sólo queremos que Excel VBA haga algo si algo cambia en la celda B3. Para ello, añada la siguiente línea de código: If Target. Address quotB3quot Then 6. Inicializamos newvalue con el valor de la celda B3, firstfourvalues con Range (quotD3: D6quot) y lastfourvalues con Range (quotD4: D7quot). Newvalue Rango (quotB3quot).Value Set firstfourvalues Rango (quotD3: D6quot) Set lastfourvalues Rango (quotD4: D7quot) 7. Ahora viene el simple truco. Queremos actualizar la tabla de promedio móvil. Puede conseguirlo reemplazando los cuatro últimos valores con los primeros cuatro valores de la tabla y colocando el nuevo valor de stock en la primera posición. Lastfourvalues. Value firstfourvalues. Value Range (quotD3quot).Value newvalue 8. No olvide cerrar la sentencia if. 9. Finalmente, ingrese la fórmula PROMEDIO (D3: D7) en la celda D8. 10. Pruebe el programa haciendo clic en el botón de comando.
Comments
Post a Comment