Cómo Calcularse La Ración Para Modificar Por Por Excel

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 de 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. Puede ver varias celdas que muestran este mensaje de error como un valor. 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 la última 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 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 su descarga: hoja de cálculo de Moving Average. Le agradezco por leer y espero verle la próxima semana También puede ser que esté interesado en: Calcule el promedio móvil en Excel Una de las maneras de predecir el futuro es observando dónde se inclina el pasado. El análisis de tendencias ayuda a pronosticar el futuro basado en datos pasados. Sin embargo, estos datos tienen los efectos de diferentes tipos de variaciones. Con tales variaciones es difícil de realizar la extrapolación y tenemos que deshacerse de ellos para obtener la tendencia central. Los analistas financieros usan ampliamente la técnica del promedio móvil para determinar las tendencias ocultas del conjunto de datos. Y hoy estamos aprendiendo cómo hacerlo usando Excel empezando con muy básico y poco a poco discutiendo usos avanzados. Un aspecto importante es el intervalo utilizado para el promedio. Es simplemente el número de períodos que estamos considerando para promediar cada vez, digamos 3, y luego mover un período adelante y tomar grupo de 3 períodos de nuevo para calcular el promedio. Así que si tengo que mostrar cómo su hecho tiene un pico en la imagen. Los tres primeros números se hacen para usted. El resto se hace sobre la misma base. Queremos lograr esto con Excel y conocer algunos requisitos de la vida real. Pocas cosas antes de seguir adelante: Descargue este libro para que también pueda trabajar y aplicar el proceso que se discute a continuación. En el principio supongo que el intervalo de media móvil es 3. Vamos a tener esta variable más adelante en este tutorial. Caso 1 8211 Uso de la función SUM y ratio Muy básico y requiere trabajo manual sin inteligencia en parte de Excel. Como estoy asumiendo tres periodos para promediar cada vez así que comenzaremos con la tercera figura en los datos saltando primero dos e incorporamos la fórmula siguiente: Arrastre la manija del terraplén abajo para llenar el rango entero. Ignore la marca de error. Caso 2 8211 Uso de la función MEDIA Por qué molestarse en sumar primero y luego dividir si ya tenemos una función de AVALIZACIÓN incorporada. Así que a partir de la celda D6 como antes de usar la siguiente fórmula le dará el mismo resultado: Fácil derecho Lets tomar las cosas un poco de muesca Bono: Auto Moving Media en la actualización de datos Antes de seguir adelante permite configurar nuestros datos de una manera que pueda acomodar Datos adicionales calculando el promedio móvil cada vez que se ingresa nueva transacción. Para esta mejor manera posible es utilizar las tablas de Excel. Simplemente convierta el rango en tablas seleccionando y pulsando CTRLT. En mi caso convertí los datos en tablas primero y luego intenté perforar en la fórmula como se discutió en el Caso 2 anterior. Pero sucedió algo gracioso, la fórmula se aplicó a toda la columna por su cuenta: ahora es hasta nosotros si queremos aceptar la forma en que se calculan los dos primeros números. A veces lo aceptamos así. Pero a veces queremos que esté vacío y que los promedios comiencen en el intervalo correcto. Vendré este segundo requisito más adelante en este tutorial. Es una solución sencilla. Olvidé cambiar el nombre de la tabla. Darle cualquier nombre. Le di MAv. Bonus 8211 Promedio móvil del último trimestre Últimos 3 meses Ahora que nuestra tabla puede actualizar y realizar cálculos automáticamente si se introducen nuevos datos. Otro beneficio es que nos ahorrará mucho alboroto y que hacer cálculos relacionados, así, muy fácilmente. Por ejemplo, si queremos tener un promedio móvil del último período 3, entonces la siguiente fórmula ayudará: Vamos a hablar de esto un poco. La fórmula anterior tiene tres funciones combinadas. MEDIA, OFFSET y COUNT. Empecemos con lo más interno. Sugerencia Pro aquí Siempre comience con la función más interna ya que la mayoría de las veces se calculará primero. COUNT (MAvSales) 8211 Esta parte está contando cuántas filas tiene nuestra tabla. He dado la columna de ventas de MAv tabla para ser comprobado. Esto cambiará a medida que crezca el dato. Pero por qué 8220-38221. Voy a llegar a eso en un poco. Pocas palabras sobre OFFSET, esta función nos ayuda a compensar o saltar el número especificado de filas y columnas antes de buscar una celda o rango de celdas de cierta altura y ancho OFFSET (C4, COUNT (MAvSales) -3,0,3,1) 8211 Lets Entiéndelo por cada argumento: Row: COUNT (MAvSales) -3 8211 Esta parte está calculando cuántas filas saltar de C4. En mi ejemplo de ahora hay 14 filas en total y 14-3 da 11. Así que desde C4 llegará a C15. Columna: 0 8211 Zero se menciona aquí, por lo que no se omitirá ninguna columna. Así que Excel se quedará en la celda C15. Altura: 3 8211 Indica cuántas filas se deben buscar. 3 se menciona para que Excel marque las filas 15, 16 y 17. Ancho: 1 8211 Indica cuántas columnas se deben buscar. Aquí se menciona 1, por lo que su única columna C. Por lo tanto Excel buscará C15, C16 y C17. PROMEDIO 8211 Ahora que tenemos C15, C16 y C17 seleccionados como resultado de OFFSET y COUNT, la función MEDIA calculará el promedio de estos tres. Caso 3 8211 Hacer que el intervalo promedio sea variable En este momento hemos calculado los promedios seleccionando un rango particular que es igual al número de periodos que queremos que medie. Eso ha fijado efectivamente el número de intervalos. Qué pasa si queremos cambiarlo en el camino Esto es muy posible. Hagamos esto, mencioné la figura de intervalo en la celda G8 y nombré esa celda 8220Int8221 escribiendo en el cuadro de nombre con la casilla activa. Posteriormente utilizó la siguiente fórmula: Ahora si cambia el número de intervalo, Excel calculará automáticamente las medias móviles basadas en consecuencia. 8211 Gracias a Brian Aunque las historias de las secciones de comentarios deben permanecer en la sección de comentarios, pero la versión de Brian8217s fue una alegría y me gustó mucho la forma en que simplificó el uso y aún así se hizo el trabajo. Así que para aplaudir y agradecer no encontré mejor manera de actualizar el artículo con su sugerencia agregada para la vida con su nombre En lugar de la fórmula propuesta anterior que en realidad compensa las filas hacia arriba desde la celda dada y luego buscar el rango hacia abajo podemos simplemente buscar el Altura hacia arriba utilizando un argumento negativo. Siguiendo la sugerencia de Brian8217s la fórmula puede ser: Caso 4 8211 Ajuste de la media para acomodar a Zero Deja que nuestros datos pueden tener ceros. Y nuestro cliente sugiere que si sucede a continuación, el valor inmediatamente anterior debe ser considerado para los cálculos de promedio saltar cero. Para esto, nuestra fórmula tiene que someterse a algunas actualizaciones graves de la siguiente manera: La fórmula anterior es una fórmula de matriz y tiene que ser ingresado por golpear CTRLSHIFTENTER en lugar de simplemente ENTRAR golpe. Así es como funciona la hoja de cálculo después de implementar la fórmula anterior: Bonificación 8211 Ocultar números durante periodos antes de completar el intervalo Una mejor práctica consiste en tomar sólo aquellas medias móviles cuando el intervalo realmente termina. Por ejemplo, si mi intervalo es 3, entonces no puedo tener un promedio móvil antes del 1 de septiembre de 2012 de acuerdo a mis datos. Hasta ahora no ha estado causando ningún problema, pero si los datos se hace hincapié en que puede a través de diferentes tipos de errores, por lo que es mejor deshacerse de los datos no relacionados. Por lo tanto, ajustar nuestra fórmula un poco más utilizando otra función IF puede obtener resultados deseados: Una vez más, es importante recordar que la fórmula anterior es una fórmula de matriz y debe introducirse mediante la combinación CTRLSHIFTENTER. Y el siguiente es el resultado una vez actualizado: Caso 5 8211 Utilizar builtin Data Analysis Toolpak Sí, guardé el secreto hasta ahora. Me gusta disfrutar de bits fáciles como recompensa al final de hardwork Para acceder a las herramientas de análisis de datos debe tener 8220Analysis Tookpak8221 habilitado desde la ficha de Developer8217s. Una vez activado, el botón 8220Data Analysis8221 estará disponible en la ficha Datos de la cinta de Excel. Esta herramienta realmente simplifica el proceso, ya que sólo tiene que seguir las instrucciones en el cuadro de diálogo y that8217t Así que aquí tienes cinco casos para aprender. Definitivamente, puede haber cientos de otros requisitos de la vida real, pero espero que le ayudará a tener un comienzo de salto y mejorar en Excel Checkout los siguientes tutoriales, así para ser mejor en Análisis de datos: 2 COMENTARIOS Brian Canes Consulte bit. ly/MovAvg C1 Es un campo de entrada. La fórmula básica en C es AVERAGE (OFFSET (B7. -3)) Creo que no es bien sabido que puede tener una altura y un ancho negativos en la función OFFSET. Esto es probablemente debido a las notas de la Ayuda en las que especifican (inexactamente) que estos deben ser números positivos. Usted puede futz con esto para que el primer promedio no es C1-1 o Ref Salud Brian Canes Hey Brian, Gracias por pasar y agregando That8217s una idea genial me gusta este. Nunca supe de esa inexactitud positiva, sin embargo, sin duda simplificado la fórmula. En lugar de compensar la fila hacia arriba y luego buscar la altura hacia abajo8230, por qué no simplemente obtener la altura desde allí hacia arriba. Gracias de nuevo y bienvenido a PakAccountants LEAVE A REPLY Cancelar respuestaCómo calcular una media móvil en Excel Una media móvil es una estadística utilizada para analizar partes de un conjunto de datos grande durante un período de tiempo. Se utiliza comúnmente con los precios de las acciones, los rendimientos de las acciones y los datos económicos como el producto interno bruto o los índices de precios al consumidor. Utilizando Microsoft Excel, puede organizar y calcular promedios móviles en cuestión de minutos, lo que le permite centrarse más tiempo en el análisis real en lugar de construir las series de datos. Abra una nueva hoja de cálculo en Microsoft Excel. Introduzca las fechas y sus puntos de datos correspondientes en dos columnas. Por ejemplo, para analizar las cifras de ingresos mensuales, ingrese cada mes en la columna A y la cifra de ingresos correspondiente junto a ella en la columna B. Un año de datos, entonces, llenaría las celdas A1 a A12 y B1 a B12. Determine el intervalo de tiempo del promedio móvil que desea calcular, como una media móvil de tres meses o seis meses. Vaya al último valor del primer intervalo y haga clic en la celda vacía correspondiente a la derecha. Utilizando el ejemplo del paso 1, si desea calcular una media móvil de tres meses, haga clic en la celda C3 porque B3 contiene el último valor de los tres primeros meses del año. Utilice la función MEDIA y escriba una fórmula en la celda vacía que seleccionó, especificando el rango de datos para el primer intervalo. En este ejemplo, escriba quotAVERAGE (B1: B3) quot. Coloque el ratón en la esquina inferior derecha de la celda con la fórmula hasta que vea un quot. quot Haga clic izquierdo y arrastre la fórmula hasta la celda vacía al lado del último punto de datos en la columna adyacente. En el ejemplo anterior, arrastraría la fórmula desde la celda C3 hasta la celda C12 para calcular la media móvil de tres meses para el resto del año.