Cálculo del promedio móvil en Excel En este breve tutorial, aprenderá a calcular rápidamente un promedio móvil simple en Excel, qué funciones utilizar para obtener el promedio móvil de los últimos N días, semanas, meses o años y cómo agregar un movimiento Línea de tendencia promedio 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 determinado. 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 media antigua 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 agreguen 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. Te agradezco por leer y espero verte la semana que viene También te puede interesar: Su ejemplo 3 anterior (Obtener la media móvil de los últimos valores de N en una fila) funcionó perfectamente para mí si toda la fila contiene números. Im que hace esto para mi liga del golf donde utilizamos un promedio rodante de 4 semanas. A veces los golfistas están ausentes por lo que en lugar de una puntuación, voy a poner ABS (texto) en la celda. Todavía quiero que la fórmula busque los últimos 4 puntajes y no cuente el ABS ni en el numerador ni en el denominador. ¿Cómo puedo modificar la fórmula para lograr esto Im tratando de crear una fórmula para obtener el promedio móvil para el período 3, apreciar si puede ayudar a pls. Fecha Producto Precio 10/1/2016 A 1,00 10/1/2016 B 5,00 10/1/2016 C 10,00 10/2/2016 A 1,50 10/2/2016 B 6,00 10/2/2016 C 11,00 10/3/2016 A 2,00 10/3/2016 B 15,00 10/3/2016 C 20,00 10/4/2016 A 4,00 10/4/2016 B 20,00 10/4/2016 C 40,00 10/5/2016 A 0,50 10/5/2016 B 3,00 10/5/2016 C 5,00 10/6/2016 A 1,00 10/6/2016 B 5,00 10/6/2016 C 10,00 10/7/2016 A 0,50 10/7/2016 B 4,00 10/7/2016 C 20,00 Promedio de Promedio Necesito una fórmula para calcular el promedio de 30 trabajadores por semana durante un período de 52 semanas. El promedio debe ser 12 semanales por ejemplo, semana 1 a 12 entonces semana 2 a 13 y así sucesivamente. La media móvil también no incluye las semanas que son cero horas trabajadas y sólo incluyen 12 semanas que tienen un valor superior a cero. También me gustaría que la hoja de cálculo para ser horizontal por ejemplo A2 a AZ2 etc Por favor, alguien puede ayudar a una persona tenía esta pregunta disculpas por la respuesta tardía. A continuación encontrará un ejemplo de lo que estoy tratando de lograr. Fila A1 Titulada Nombre y semana, B1 a BA1 semanas 1 -52, BB1 a BB36 12 horas semanales promedio de horas trabajadas y A2 a A36 nombre del empleado. El promedio de las últimas 12 semanas no debe incluir las semanas en las que no se realizó ningún trabajo, sólo 12 semanas donde los totales son superiores a 0. He intentado esta fórmula, pero no promedio las horas correctamente y siempre es ligeramente fuera: Ser la primera persona a marcar This useful Historia del abuso Introduzca esta fórmula en la celda BC2 y copie hacia abajo También puede referirse a mi solución a un problema similar en este enlace. Espero que esto ayude. 2 personas encontraron esta útil Historia del abuso Aquí hay un par de artículos sobre el tema. Cálculo del promedio móvil en Excel En este breve tutorial, aprenderá a calcular rápidamente un promedio móvil simple en Excel, qué funciones utilizar para obtener el promedio móvil de los últimos N días, semanas, meses o años y cómo agregar un movimiento Línea de tendencia promedio a un gráfico de Excel. Seguir leyendo por Svetlana Cheusheva 2015 09 25 Determinar un promedio móvil simple Un promedio móvil puede ser una excelente manera de analizar una serie de puntos de datos que ha recogido con el tiempo. Configuración de una fórmula para mostrar el promedio móvil que necesita puede ser un poco confuso, sin embargo. Este consejo explica cómo puedes obtener el promedio. 34Pay34 4 este servicio gratuito haciendo clic en el enlace azul 34Answer34 en cualquier respuesta que solucione su problema 1 persona ha encontrado útil Ingrese debajo de la fórmula como una fórmula de matriz (CTRLSHIFTENTER) en la celda B8 amp copia a la derecha: Calcula el promedio móvil, comenzando Desde B2: BA2, durante 12 semanas que son gt cero y también excluye espacios en blanco. La célula C8 calcula el promedio durante 12 semanas en el intervalo C2: BA2 que son gt cero. Y así. Nota: Esta fórmula calculará durante 12 semanas o si termina el final hay menos de 12 semanas que son gt 0 entonces para esos número reducido de semanas. Si desea mostrar espacios en blanco para los casos en que el número de semanas que quedan son menos de 12 que son gt 0, a continuación, puede utilizar la siguiente fórmula de matriz: Puede descargar el archivo de Excel desde abajo enlace en el que se ha ilustrado: 1 persona encontró este Útil Abuso de la historia He intentado esta primera cosa esta mañana y es exactamente el resultado que buscaba. Muchas gracias por tu tiempo. Sé la primera persona en marcar esta útil Historia del abuso Gracias por tu respuesta. Idealmente quería que el resultado estuviera en 1 columna después de BA2. Sin embargo, ha creado la solución a otro problema en el que he estado trabajando. Otra vez, gracias por tu tiempo. Sé la primera persona en señalar esto útil Historia del abuso He creado un promedio móvil de 12 semanas a la vez, pasando de la columna B a la BA. En la celda B8 he considerado los primeros 12 valores que son distintos de cero y no blancos partiendo de la celda B2 a la derecha. En la celda C8, los primeros valores que son distintos de cero y no blancos partiendo de la celda C2 a la derecha. En la fila 8 he dado estos promedios de rodadura para el primer empleado - Emp1. Esta era mi comprensión porque usted había mencionado - entrenamiento el promedio móvil de 30 trabajadores horas semanales durante un período de 52 semanas. El promedio debe ser de 12 semanales por ejemplo, semana 1 a 12 y luego semana 2 a 13 y así sucesivamente .. Si usted copia mis fórmulas abajo, la fila 9 dará los promedios de rodadura para el segundo empleado - Emp2, y así sucesivamente. Parece que sólo desea que el promedio de las últimas 12 semanas que no son espacios en blanco (pero podría ser ceros) - aunque esto es también una parte de mi solución. Quería aclarar que ya parecen haber pasado por esto. Se puede utilizar el módulo Filtro de media móvil para calcular una serie de promedios unilaterales o de dos caras en un conjunto de datos, utilizando una longitud de ventana que Que especifique. Después de haber definido un filtro que satisfaga sus necesidades, puede aplicarlo a las columnas seleccionadas de un conjunto de datos conectándolo al módulo Aplicar filtro. El módulo hace todos los cálculos y reemplaza los valores dentro de las columnas numéricas con las medias móviles correspondientes. Puede usar el promedio móvil resultante para el trazado y la visualización, como una nueva línea de base suave para el modelado, para calcular las desviaciones con respecto a los cálculos para periodos similares, y así sucesivamente. Este tipo de promedio le ayuda a revelar y pronosticar patrones temporales útiles en datos retrospectivos y en tiempo real. El tipo más simple de media móvil comienza en alguna muestra de la serie y utiliza el promedio de esa posición más las n posiciones anteriores en lugar del valor real. (Puede definir n como quiera.) Cuanto más largo sea el período n en el que se calcula el promedio, menor será la varianza entre los valores. Además, a medida que aumenta el número de valores utilizados, menos efecto tiene un solo valor en el promedio resultante. Un promedio móvil puede ser unilateral o bilateral. En un promedio unilateral, sólo se utilizan los valores que preceden al valor del índice. En un promedio de dos caras, se utilizan valores pasados y futuros. Para los escenarios en los que está leyendo los datos de transmisión, las medias móviles acumuladas y ponderadas son particularmente útiles. Un promedio móvil acumulativo toma en cuenta los puntos anteriores al período actual. Puede ponderar todos los puntos de datos de manera igual al calcular el promedio, o puede garantizar que los valores más cercanos al punto de datos actual se ponderan más fuertemente. En una media móvil ponderada. Todos los pesos deben sumar a 1. En una media móvil exponencial. Los promedios consisten en una cabeza y una cola. Que pueden ponderarse. Una cola ligeramente ponderada significa que la cola sigue la cabeza muy de cerca, por lo que el promedio se comporta como un promedio móvil en un corto período de ponderación. Cuando los pesos de la cola son más pesados, el promedio se comporta más como un promedio móvil simple más largo. Agregue el módulo de filtro de media móvil a su experimento. Para Longitud. Escriba un valor de número entero positivo que define el tamaño total de la ventana a través de la cual se aplica el filtro. Esto también se llama la máscara de filtro. Para una media móvil, la longitud del filtro determina cuántos valores se promedian en la ventana deslizante. Los filtros más largos también se llaman filtros de orden superior y proporcionan una ventana de cálculo más grande y una aproximación más cercana a la línea de tendencia. Los filtros de orden más corto o inferior utilizan una ventana de cálculo más pequeña y se asemejan más a los datos originales. Para Tipo. Elija el tipo de media móvil a aplicar. Azure Machine Learning Studio soporta los siguientes tipos de cálculos de promedio móvil: Se calcula un promedio móvil simple (SMA) como una media de balanceo no ponderada. Los promedios móviles triangulares (TMA) se promedian dos veces para una línea de tendencia más suave. La palabra triangular se deriva de la forma de los pesos que se aplican a los datos, lo que hace hincapié en los valores centrales. Un promedio móvil exponencial (EMA) da más peso a los datos más recientes. La ponderación disminuye exponencialmente. Un promedio móvil exponencial modificado calcula un promedio móvil en ejecución, donde calcular el promedio móvil en cualquier punto considera la media móvil previamente calculada en todos los puntos anteriores. Este método produce una línea de tendencia más suave. Dado un solo punto y una media móvil actual, la media móvil acumulativa (CMA) calcula la media móvil en el punto actual. Agregue el conjunto de datos que tiene los valores que desea calcular un promedio móvil y agregue el módulo Aplicar filtro. Conecte el filtro de media móvil a la entrada de la izquierda de Aplicar filtro. Y conecte el dataset a la entrada derecha. En el módulo Aplicar filtro, utilice el selector de columnas para especificar a qué columnas debe aplicarse el filtro. De forma predeterminada, el filtro que cree se aplicará a todas las columnas numéricas, así que asegúrese de excluir todas las columnas que no tienen los datos adecuados. Ejecute el experimento. En ese punto, para cada conjunto de valores definido por el parámetro de longitud del filtro, el valor actual (o índice) se sustituye por el valor de la media móvil.
No comments:
Post a Comment