Método medio en movemento en Microsoft Excel

O método medio en movemento é unha ferramenta estatística coa que se poden resolver varios tipos de problemas. En particular, úsase a miúdo na predición. En Excel, tamén podes usar esta ferramenta para resolver varios problemas. Vexamos como se utiliza a media móbil en Excel.

Aplicación media en movemento

O significado deste método é que coa súa axuda, os valores dinámicos absolutos da serie seleccionada cambian aos valores medios aritméticos durante un determinado período alisando os datos. Esta ferramenta úsase para cálculos económicos, previsión, no proceso de negociación na bolsa, etc. Empregando o método promedio móbil de Excel faise mellor usando unha potente ferramenta de procesamento de datos chamada estatística Paquete de análise. Tamén pode usar a función Excel incorporada para o mesmo propósito. MEDIA.

Método 1: Paquete de análise

Paquete de análise é un complemento de Excel que está desactivado de xeito predeterminado. Por iso, primeiro hai que habilitalo.

1. Mover á pestana Arquivo. Fai clic nun elemento. "Opcións".



2. Na xanela de parámetros que se abre, diríxase á sección "Complementos". Na parte inferior da xanela na caixa "Xestión" o parámetro debe ser definido Complementos de Excel. Fai clic no botón Vaia a.



3. Entramos na xanela dos complementos. Marque a caixa xunto Paquete de análise e fai clic no botón "Está ben".

Despois desta acción, o paquete "Análise de datos" activouse e o botón correspondente apareceu na cinta na lapela "Datos".

Agora vexamos como pode usar directamente as características do paquete. Análise de datos para o método da media en movemento. Fagamos unha previsión para o duodécimo mes a partir da información sobre os ingresos da empresa durante 11 períodos anteriores. Para iso, empregaremos unha táboa chea de datos, así como ferramentas Paquete de análise.

1. Vai á lapela "Datos" e fai clic no botón "Análise de datos", que se coloca na cinta da ferramenta no bloque "Análise".



2. Lista dos instrumentos dispoñibles en Paquete de análise. Escolla un nome deles Media en movemento e fai clic no botón "Está ben".



3. Iniciase a xanela de entrada de datos para a previsión media en movemento.

   No campo Intervalo de entrada indique o enderezo do intervalo onde se atopa a cantidade de ingresos mensuais sen a cela na que se deben calcular os datos.

   No campo Intervalo Debe especificar o intervalo para procesar valores mediante o método de suavización. En primeiro lugar, imos establecer o valor de suavización a tres meses e, polo tanto, escriba o número "3".

   No campo "Intervalo de saída" ten que especificar un rango baleiro arbitrario na folla onde se mostrarán os datos despois do procesamento, que debería ser unha cela superior ao intervalo de entrada.

   Tamén marque a caixa xunto ao parámetro. "Erros estándar".

   Se é necesario, tamén pode marcar a caixa xunto "Saída gráfica" para unha demostración visual, aínda que no noso caso isto non é necesario.

   Despois de realizar todos os axustes, faga clic no botón "Está ben".



4. O programa mostra o resultado do procesamento.



5. Agora realizaremos suavización durante dous meses para revelar cal é o resultado máis correcto. Para estes efectos, volva a executar a ferramenta. Media en movemento Paquete de análise.

   No campo Intervalo de entrada deixamos os mesmos valores que no caso anterior.

   No campo Intervalo pon o número "2".

   No campo "Intervalo de saída" especifique o enderezo do novo rango baleiro, que, de novo, debería ser unha cela maior que o intervalo de entrada.

   A configuración restante déixase sen cambios. Despois diso, faga clic no botón "Está ben".



6. Tras isto, o programa calcula e mostra o resultado na pantalla. Para determinar cal dos dous modelos é máis preciso, cómpre comparar os erros estándar. Canto menor sexa este indicador, maior será a probabilidade de precisión do resultado. Como podes ver, para todos os valores, o erro estándar no cálculo do balance de dous meses é inferior ao mesmo indicador durante 3 meses. Así, o valor previsto para decembro pode considerarse o valor calculado polo método de deslizamento para o último período. No noso caso, este valor é de 990,4 mil rublos.

Método 2: empregando a función MEDIA

En Excel hai outro xeito de aplicar o método de media móbil. Para utilizalo, é necesario aplicar unha serie de funcións do programa estándar, cuxo elemento básico é para o noso propósito MEDIA. Como exemplo, empregaremos a mesma táboa de ingresos das empresas que no primeiro caso.

Como a última vez, necesitaremos crear unha serie de tempo suavizada. Pero esta vez, as accións non serán tan automatizadas. Deberías calcular a media por cada dous, e despois tres meses, para poder comparar os resultados.

En primeiro lugar, calculamos os valores medios dos dous períodos anteriores usando a función MEDIA. Só podemos facelo a partir de marzo, xa que para datas posteriores hai un descanso nos valores.

1. Seleccione unha cela nunha columna baleira seguida para marzo. A continuación, fai clic na icona "Función de inserción"que se coloca preto da barra de fórmulas.



2. A xanela está activada Asistentes de funcións. En categoría "Estatística" buscando sentido SRZNACH, selecciónea e fai clic no botón "Está ben".



3. O lanzamento da xanela do argumento do operador MEDIA. A sintaxis é a seguinte:

   = MEDIO (número1; número2; ...)

   Só se precisa un argumento.

   No noso caso, no campo "Número 1" debemos proporcionar unha ligazón ao rango no que se indican os ingresos dos dous períodos anteriores (xaneiro e febreiro). Estableza o cursor no campo e selecciona as celas correspondentes na folla da columna Ingresos. Despois diso, faga clic no botón "Está ben".



4. Como podes ver, apareceu na cela o resultado do cálculo do valor medio dos dous períodos anteriores. Para realizar cálculos similares para o resto de meses do período, é necesario copiar esta fórmula noutras celas. Para iso, convertémonos no cursor na esquina inferior dereita da cela que contén a función. O cursor convértese nun marcador de recheo, que parece unha cruz. Manteña o botón esquerdo do rato e arrástreo ata o final da columna.



5. Obtemos o cálculo dos resultados do valor medio dos dous meses anteriores antes de que remate o ano.



6. Agora seleccione a cela na seguinte columna baleira da liña para abril. Chamar á xanela do argumento da función MEDIA do mesmo xeito que antes descrito. No campo "Número 1" introduza as coordenadas das celas da columna Ingresos Xaneiro a marzo. Despois faga clic no botón "Está ben".



7. Usando o marcador de recheo, copia a fórmula nas celas da táboa que se atopan a continuación.



8. Entón, calculamos os valores. Agora, como na época anterior, precisaremos que tipo de análise é mellor: con suavización aos 2 ou 3 meses. Para iso, calcula a desviación estándar e algúns outros indicadores. En primeiro lugar, calculamos a desviación absoluta usando a función Excel estándar ABS, que en vez de números positivos ou negativos devolve o seu módulo. Este valor será igual á diferenza entre o indicador de ingresos reais do mes seleccionado e o previsto. Estableza o cursor na seguinte columna baleira da liña para maio. Chamamos Asistente de características.



9. En categoría "Matemático" selecciona o nome da función "ABS". Fai clic no botón "Está ben".



10. Iníciase a xanela do argumento da función ABS. Nun único campo "Número" indica a diferenza entre o contido das celas das columnas Ingresos e 2 meses para maio. Despois faga clic no botón "Está ben".



11. Usando o marcador de recheo, copia esta fórmula en todas as filas da táboa ata novembro incluído.



12. Calculamos o valor medio da desviación absoluta para todo o período empregando a función que xa coñecemos MEDIA.



13. Realizamos un procedemento similar para calcular a desviación absoluta para o en movemento en 3 meses. En primeiro lugar, aplique a función ABS. Só esta vez consideramos a diferenza entre o contido das celas coa renda real e a planificada, calculada mediante o método medio móbil durante 3 meses.



14. A continuación, calculamos o valor medio de todos os datos de desviación absoluta usando a función MEDIA.



15. O seguinte paso é calcular a desviación relativa. É igual á proporción da desviación absoluta ao indicador real. Para evitar valores negativos, volveremos a usar as posibilidades que o operador ofrece ABS. Esta vez, utilizando esta función, dividimos o valor da desviación absoluta ao usar o método medio móbil durante 2 meses polo ingreso real do mes seleccionado.



16. Pero a desviación relativa móstrase normalmente en forma porcentual. Polo tanto, seleccione o rango adecuado na folla, vaia á lapela "Casa"onde na caixa de ferramentas "Número" no campo de formato especial fixamos o formato porcentual. Despois, o resultado do cálculo da desviación relativa móstrase en por cento.



17. Realizamos unha operación similar para calcular a desviación relativa cos datos usando suavización durante 3 meses. Só neste caso, para o cálculo como dividendo, empregamos outra columna da táboa, que temos o nome "Desactivado en absoluto (3m)". Despois traducimos os valores numéricos a unha forma porcentual.



18. Despois diso, calculamos os valores medios para ambas columnas con desviación relativa, como antes de usar a función MEDIA. Dado que tomamos valores porcentuais como argumentos para a función, non é necesario que fagamos conversión adicional. O operador de saída dá o resultado xa en formato porcentual.



19. Agora chegamos ao cálculo da desviación estándar. Este indicador permitiranos comparar directamente a calidade do cálculo ao usar suavización durante dous e tres meses. No noso caso, a desviación estándar será igual á raíz cadrada da suma dos cadrados das diferenzas nos ingresos reais e a media móbil dividida polo número de meses. Para facer cálculos no programa, temos que empregar unha serie de funcións, en particular ROOT, RESUMO e Conta. Por exemplo, para calcular a desviación cadrada media ao usar a liña de alisado durante dous meses en maio, no noso caso, empregarase a seguinte fórmula:

    = ROOT (RESUMO (B6: B12; C6: C12) / COUNT (B6: B12))

    Copialo a outras celas da columna co cálculo da desviación estándar usando o marcador de recheo.



20. Realízase unha operación similar para calcular a desviación estándar para a media móbil durante 3 meses.



21. Despois diso, calculamos o valor medio de todo o período para ambos estes indicadores, aplicando a función MEDIA.



22. Comparando os cálculos empregando o método medio en movemento con suavización a 2 e 3 meses para indicadores como desviación absoluta, desviación relativa e desviación estándar, podemos dicir con confianza que o alisado durante dous meses dá resultados máis fiables que a aplicación de suavización durante tres meses. Isto evidénciao o feito de que os indicadores anteriores para a media móbil de dous meses son inferiores aos do mes de tres.



23. Así, o indicador previsto dos ingresos da compañía para decembro será de 990,4 mil rublos. Como podes ver, este valor coincide completamente co obtido calculando mediante ferramentas Paquete de análise.

Lección: Asistente de funcións de Excel

Calculamos a previsión empregando o método da media móbil de dúas formas. Como podes ver, este procedemento é moito máis sinxelo de empregar ferramentas. Paquete de análise. Non obstante, algúns usuarios non sempre confían no cálculo automático e prefiren empregar a función para os cálculos. MEDIA e operadores relacionados para verificar a opción máis fiable. Aínda que, se todo se fai correctamente, a saída do cálculo debería resultar completamente igual.