O método de media móbil é unha ferramenta estatística coa que podes resolver varios tipos de problemas. En particular, a miúdo úsase na previsión. En Excel, esta ferramenta tamén pode usarse para resolver unha variedade de tarefas. Vexamos como se usa o promedio móbil en Excel.
Aplicación da media móbil
O significado deste método é que coa súa axuda hai un cambio dos valores dinámicos absolutos da serie seleccionada ás medias aritméticas dun determinado período, suavizando os datos. Esta ferramenta úsase para cálculos económicos, previsións, no proceso de negociación na bolsa de valores, etc. É mellor usar o método Moving Average en Excel coa axuda da ferramenta máis poderosa para o procesamento de datos estatísticos, que se chama Paquete de análise. Ademais, para estes efectos, pode empregar a función de Excel integrada. MEDIA.
Método 1: Paquete de análise
Paquete de análise é un complemento de Excel que está desactivado por defecto. Polo tanto, primeiro é necesario para habilitalo.
- Mover á pestana "Ficheiro". Fai clic no elemento. "Opcións".
- Na ventá de parámetros que comeza, vaia á sección Complementos. No fondo da xanela no campo "Xestión" Debe axustarse o parámetro Complementos de Excel. Fai clic no botón "Vaia".
- Entramos na ventá de complementos. Estableza unha marca preto do elemento "Paquete de análise" e prema no botón "OK".
Despois deste paquete de acción "Análise de datos" activado, e o botón correspondente apareceu na cinta da pestana "Datos".
Agora vexamos como pode usar directamente as capacidades do paquete. Análise de datos para traballar no método de media móbil. A base de información sobre os ingresos da empresa nos últimos 11 períodos, imos facer unha previsión para o duodécimo mes. Para iso, utilizamos a táboa chea de datos e ferramentas. Paquete de análise.
- Vaia á pestana "Datos" e prema no botón "Análise de datos"que se coloca na cinta de ferramentas do bloque "Análise".
- Unha lista de ferramentas dispoñibles en Paquete de análise. Eliximos o nome deles "Media móbil" e prema no botón "OK".
- Iníciase a ventá de entrada de datos para a predición media en movemento.
No campo "Intervalo de entrada" especifique o enderezo do intervalo, onde o importe mensual dos ingresos está situado sen unha cela, cuxos datos deben calcularse.
No campo "Intervalo" especificar o intervalo de valores de procesamento usando o método de suavizado. Para comezar, imos definir o valor de suavizado en tres meses e, polo tanto, introduza a figura "3".
No campo "Espazo de saída" cómpre especificar un intervalo baleiro arbitrario na folla, onde se mostrarán os datos despois do procesamento, que debería ser unha cela maior que o intervalo de entrada.
Marca tamén a caixa situada ao lado "Erros estándar".
Se é necesario, tamén pode marcar a caixa situada ao lado "Tramar" para demostración visual, aínda que no noso caso non é necesario.
Despois de facer todas as configuracións, faga clic no botón. "OK".
- O programa mostra o resultado do procesamento.
- Agora executaremos o suavizado para o período de dous meses para revelar cal é o resultado máis correcto. Para iso, executamos a ferramenta de novo. "Media móbil" Paquete de análise.
No campo "Intervalo de entrada" Deixa os mesmos valores que no caso anterior.
No campo "Intervalo" poñer o número "2".
No campo "Espazo de saída" especificamos o enderezo do novo intervalo baleiro, que de novo debe ser unha cela maior que o intervalo de entrada.
Os axustes restantes non se modifican. Despois, faga clic no botón "OK".
- Despois disto, o programa calcula e mostra o resultado na pantalla. Para determinar cal dos dous modelos é máis preciso, necesitamos comparar erros estándar. Canto menor este indicador, maior será a probabilidade de exactitude do resultado. Como podes ver, para todos os valores do erro estándar no cálculo do deslizamento de dous meses é menor que o mesmo durante 3 meses. Así, o valor previsto para decembro pódese considerar 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: use a función MEDIA
En Excel hai outro xeito de usar o método de media móbil. Para usalo, ten que aplicar varias funcións estándar do programa, cuxa base para o noso propósito é MEDIA. Por exemplo, usaremos a mesma táboa de ingresos empresariais como no primeiro caso.
Como a última vez, teremos que crear unha serie temporal suave. Pero esta vez as accións non serán tan automatizadas. Calcula o valor medio para cada dous e despois tres meses para poder comparar os resultados.
En primeiro lugar, calculamos os valores medios para os dous períodos anteriores empregando a función MEDIA. Podemos facelo a partir de marzo, xa que para datas posteriores hai un valor de ruptura.
- Seleccione a cela na columna baleira da fila de marzo. A continuación, fai clic na icona "Inserir función"que se atopa preto da barra de fórmulas.
- Xanela activada Mestría de funcións. Na categoría "Estatística" buscando valor "SRZNACH"selecciona e fai clic no botón "OK".
- Inicia a xanela de argumento do operador. MEDIA. A súa sintaxe é a seguinte:
= MEDIA (número1; número2; ...)Só se require un argumento.
No noso caso, no campo "Número1" debemos proporcionar unha ligazón ao rango onde se indican os ingresos dos dous períodos anteriores (xaneiro e febreiro). Sitúe o cursor no campo e seleccione as celas correspondentes na folla na columna "Ingresos". Despois, faga clic no botón "OK".
- Como podes ver, o resultado do cálculo da media para os dous períodos anteriores mostrouse na cela. Para realizar cálculos similares para todos os meses restantes do período, necesitamos copiar esta fórmula noutras celas. Para facelo, convertemos no cursor na esquina inferior dereita da cela que contén a función. O cursor convértese nun marcador de recheo, que se parece a unha cruz. Manteña premido o botón esquerdo do rato e arrástrea ata o final da columna.
- Recibimos o cálculo dos resultados medios dos dous meses anteriores ao final do ano.
- Agora seleccione a cela na seguinte columna baleira da fila de abril. Chama á fiestra do argumento da función MEDIA do mesmo xeito que o descrito anteriormente. No campo "Número1" introduza as coordenadas das celas na columna "Ingresos" de xaneiro a marzo. A continuación, faga clic no botón "OK".
- Utilizando o marcador de recheo, copie a fórmula ás celas da táboa de abaixo.
- Así, calculamos os valores. Agora, como no anterior, teremos que determinar que tipo de análise é mellor: con antialiasing en 2 ou 3 meses. Para iso, calcula a desviación estándar e algúns outros indicadores. Primeiro, calculamos a desviación absoluta usando a función estándar de Excel. ABS, que no canto de números positivos ou negativos devolve o seu módulo. Este valor será igual á diferenza entre os ingresos reais para o mes seleccionado e a previsión. Sitúe o cursor na seguinte columna baleira nunha fileira para maio. Chamar Asistente de funcións.
- Na categoría "Matemática" seleccione o nome da función "Abs". Pulsamos o botón "OK".
- Iníciase a fiestra do argumento da función. ABS. Nun único campo "Número" especifique a diferenza entre o contido das celas nas columnas "Ingresos" e "2 meses" para maio. A continuación, faga clic no botón "OK".
- Empregando o marcador de recheo, copiamos esta fórmula en todas as filas da táboa ata novembro incluída.
- Calcula o valor medio da desviación absoluta durante todo o período usando a función xa coñecida MEDIA.
- Realizamos o mesmo procedemento para calcular a desviación absoluta dun deslizante durante 3 meses. Primeiro utilizamos a función ABS. Só esta vez, consideramos a diferenza entre o contido das células co rendemento real e o planificado, calculado mediante o método de media móbil durante 3 meses.
- A continuación, calculamos a media de todos os datos de desviación absoluta usando a función MEDIA.
- O seguinte paso é calcular a desviación relativa. É igual á relación da desviación absoluta co indicador real. Para evitar valores negativos, volvemos utilizar as posibilidades que ofrece o operador ABS. Esta vez usando esta función, dividimos o valor da desviación absoluta cando se usa o método de media móbil durante 2 meses polo ingreso real do mes seleccionado.
- Pero a desviación relativa normalmente móstrase como porcentaxe. Polo tanto, selecciona o intervalo axeitado na folla, vai á pestana "Fogar"onde en ferramentas de bloqueo "Número" No campo de formato especial, estableza o formato de porcentaxe. Despois diso, o resultado do cálculo da desviación relativa móstrase en porcentaxe.
- Realizamos unha operación similar para calcular a desviación relativa cos datos mediante o suavizado durante 3 meses. Só neste caso, para calcular como dividendo, utilizamos outra columna da táboa, que temos o nome "Abs. Off (3m)". Despois traducimos os valores numéricos á porcentaxe.
- Despois diso, calculamos os valores medios das dúas columnas con desviación relativa, como antes de usar para este propósito a función MEDIA. Xa que tomamos valores porcentuais para a función como argumentos da función, non necesitamos conversión adicional. O operador na saída dá o resultado xa en formato de porcentaxe.
- Agora chegamos ao cálculo da desviación estándar. Este indicador permitiranos comparar directamente a calidade do cálculo ao usar o anti-aliasing durante dous e tres meses. No noso caso, o desvío estándar será igual á raíz cadrada da suma dos cadrados das diferenzas de ingresos reais e da media móbil dividida polo número de meses. Para facer o cálculo no programa, temos que utilizar varias funcións, en particular ROOT, SUMMKRAVN e CUENTA. Por exemplo, para calcular a desviación estándar cando se usa a liña de suavización durante dous meses en maio, no noso caso aplicarase a seguinte fórmula:
= ROOT (SUMKVRAZN (B6: B12; C6: C12) / ACCOUNT (B6: B12))Copiámolo a outras células da columna co cálculo da desviación estándar por medio dun marcador de recheo.
- Realizamos unha operación similar para calcular o desvío estándar para a media móbil durante 3 meses.
- Despois diso, calculamos o valor medio para todo o período para estes dous indicadores, aplicando a función MEDIA.
- Unha vez realizada unha comparación de cálculos utilizando o método de media móbil con suavización aos 2 e 3 meses usando indicadores como desviación absoluta, desviación relativa e desvío estándar, podemos dicir con seguridade que dous meses de alisado dá resultados máis fiables que tres meses de alisado. Isto é indicado polo feito de que os indicadores anteriores para a media móbil de dous meses son inferiores aos tres meses.
- Así, a renda proxectada da empresa en decembro será de 990,4 mil rublos. Como podes ver, este valor é o mesmo que o recibido, facendo o cálculo empregando as ferramentas Paquete de análise.
Lección: Asistente de funcións de Excel
Calculamos a previsión usando o método de media móbil de dous xeitos. Como podes ver, este procedemento é moito máis fácil de usar con ferramentas. Paquete de análise. Non obstante, algúns usuarios non sempre confían no cálculo automático e prefiren usar 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, na saída o resultado dos cálculos debe ser completamente igual.
