Centered moving average excel

Média móvel Este exemplo ensina como calcular a média móvel de uma série temporal no Excel. Um avanço em movimento é usado para suavizar irregularidades (picos e vales) para reconhecer facilmente as tendências. 1. Primeiro, vamos dar uma olhada em nossa série de tempo. 2. No separador Dados, clique em Análise de dados. Observação: não é possível encontrar o botão Análise de dados Clique aqui para carregar o suplemento do Analysis ToolPak. 3. Selecione Média móvel e clique em OK. 4. Clique na caixa Input Range e selecione o intervalo B2: M2. 5. Clique na caixa Intervalo e escreva 6. 6. Clique na caixa Output Range e seleccione a célula B3. 8. Faça um gráfico destes valores. Explicação: porque definimos o intervalo como 6, a média móvel é a média dos 5 pontos de dados anteriores eo ponto de dados atual. Como resultado, os picos e vales são suavizados. O gráfico mostra uma tendência crescente. O Excel não consegue calcular a média móvel para os primeiros 5 pontos de dados porque não existem pontos de dados anteriores suficientes. 9. Repita os passos 2 a 8 para intervalo 2 e intervalo 4. Conclusão: Quanto maior o intervalo, mais os picos e vales são suavizados. Quanto menor o intervalo, mais próximas as médias móveis são para os pontos de dados reais. Ao calcular uma média móvel em execução, colocando a média no período de tempo médio faz sentido No exemplo anterior, calculamos a média dos primeiros 3 períodos de tempo e colocamos Próximo ao período 3. Poderíamos ter colocado a média no meio do intervalo de tempo de três períodos, ou seja, próximo ao período 2. Isso funciona bem com períodos de tempo ímpar, mas não é tão bom para mesmo períodos de tempo. Então, onde colocamos a primeira média móvel quando M 4 Tecnicamente, a Média Móvel cairá em t 2,5, 3,5. Para evitar esse problema, suavizamos as MAs usando M 2. Assim, suavizamos os valores alisados ​​Se nós formos uma média de um número par de termos, precisamos suavizar os valores suavizados A tabela a seguir mostra os resultados usando M 4. Médias de Muda e Médias Móveis Centradas Um par de pontos sobre sazonalidade em uma série de tempo repetir, mesmo que eles parecem óbvias. Um deles é que o termo 8220season8221 não se refere necessariamente às quatro estações do ano que resultam da inclinação do eixo Earth8217s. Na análise preditiva, 8220season8221 muitas vezes significa exatamente isso, porque muitos dos fenômenos que estudamos variam com a progressão da primavera até o inverno: vendas de equipamentos de inverno ou verão, incidência de certas doenças generalizadas, eventos climáticos causados ​​pela localização do Fluxo de jato e mudanças na temperatura da água no oceano Pacífico oriental, e assim por diante. Da mesma forma, eventos que ocorrem regularmente podem atuar como estações meteorológicas, embora tenham apenas uma ligação tênue com os solstícios e equinócios. Mudanças de oito horas em hospitais e fábricas muitas vezes se expressam na incidência de consumos e gastos de energia lá, uma estação é de oito horas de duração e as estações ciclo todos os dias, e não todos os anos. As datas de vencimento dos impostos indicam o início de uma inundação de dólares nos tesouros municipais, estaduais e federais, a estação pode ter um ano de duração (impostos sobre o rendimento das pessoas físicas), seis meses (impostos sobre a propriedade em muitos estados), trimestral ), e assim por diante. É um pouco estranho que tenhamos a palavra 8220season8221 para referir-nos geralmente ao período de tempo regularmente recorrente, mas não há um termo geral para o período de tempo durante o qual ocorre uma volta completa das estações. 8220Cycle8221 é possível, mas em analítica e previsão, esse termo geralmente é usado para significar um período de comprimento indeterminado, como um ciclo econômico. Na ausência de um termo melhor, eu usei o período que inclui 8222 neste capítulo e nos subseqüentes. Isso não é apenas reflexão terminológica. As maneiras pelas quais identificamos as estações e o período de tempo durante o qual as estações se transformam têm implicações reais, embora muitas vezes menores, sobre como medimos seus efeitos. As seções a seguir discutem como alguns analistas variam a maneira como calculam as médias móveis, de acordo com o número de temporadas é ímpar ou mesmo. Usando médias móveis em vez de médias simples Suponha que uma grande cidade está considerando a realocação de sua polícia de trânsito para melhor lidar com a incidência de condução, enquanto deficientes, que a cidade acredita que tem vindo a aumentar. Quatro semanas atrás, nova legislação entrou em vigor, legalizando a posse e uso recreativo de maconha. Desde então, o número diário de detenções de tráfego para DWI parece estar tendendo para cima. Complicando questões é o fato de que o número de prisões parece pico nas sextas-feiras e sábados. Para ajudar a planejar as necessidades de mão-de-obra no futuro, você gostaria de prever qualquer tendência subjacente que esteja sendo estabelecida. Você também gosta de tempo a implantação de seus recursos para ter em conta qualquer sazonalidade relacionada com o fim de semana que acontece. A Figura 5.9 tem os dados relevantes com os quais você tem que trabalhar. Figura 5.9 Com este conjunto de dados, cada dia da semana constitui uma estação. Mesmo observando apenas o gráfico da Figura 5.9. Você pode dizer que a tendência do número de prisões diárias é para cima. You8217ll tem que planejar para expandir o número de agentes de trânsito, e espero que a tendência de níveis logo em breve. Além disso, os dados confirmam a noção de que mais detenções ocorrem rotineiramente às sextas-feiras e aos sábados, então sua alocação de recursos precisa abordar esses picos. Mas você precisa para quantificar a tendência subjacente, para determinar quantos adicionais polícia você tem que trazer. Você também precisa quantificar o tamanho esperado dos picos de fim de semana, para determinar quantos policiais adicionais você precisa assistindo para drivers erráticos naqueles dias. O problema é que, como de ainda você don8217t saber quanto do aumento diário é devido à tendência e quanto é devido ao efeito fim de semana. Você pode começar por detrending a série de tempo. Mais cedo neste capítulo, em 8220Simple Seasonal Averages, 8221 você viu um exemplo de como desviar uma série de tempo para isolar os efeitos sazonais usando o método de médias simples. Nesta seção você verá como fazer isso usando médias móveis, provavelmente, a abordagem de médias móveis é usada com mais freqüência na análise preditiva do que a abordagem de médias simples. Existem várias razões para a maior popularidade das médias móveis, entre elas, que a abordagem das médias móveis não pede que você colapse seus dados no processo de quantificação de uma tendência. Lembre-se que o exemplo anterior tornou necessário colapsar médias trimestrais para médias anuais, calcular uma tendência anual e, em seguida, distribuir um quarto da tendência anual em cada trimestre no ano. Esse passo era necessário para eliminar a tendência dos efeitos sazonais. Em contraste, a abordagem de médias móveis permite que você detrend a série de tempo sem recorrer a esse tipo de maquinação. A Figura 5.10 mostra como a abordagem das médias móveis funciona no exemplo atual. Figura 5.10 A média móvel no segundo gráfico esclarece a tendência subjacente. A Figura 5.10 adiciona uma coluna de média móvel, e uma coluna para períodos sazonais específicos. Para o conjunto de dados na Figura 5.9. Ambas as adições requerem alguma discussão. Os picos em prisões que ocorrem nos fins de semana dá-lhe razão para acreditar que você está trabalhando com as estações que repetem uma vez por semana. Portanto, comece por obter a média para o período abrangente, ou seja, as primeiras sete temporadas, de segunda a domingo. A fórmula para a média na célula D5, a primeira média móvel disponível, é a seguinte: Essa fórmula é copiada e colada para baixo através da célula D29, então você tem 25 médias móveis com base em 25 execuções de sete dias consecutivos. Observe que, para mostrar as primeiras e as últimas observações na série de tempo, eu tenho escondido linhas de 10 a 17. Você pode exibi-los, se você quiser, neste livro capítulo8217s, disponível no site do editor8217s. Faça uma seleção múltipla de linhas visíveis 9 e 18, clique com o botão direito do mouse em um de seus cabeçalhos de linha e escolha Unhide no menu de atalho. Quando você oculta linhas de uma planilha de trabalho, como fez na Figura 5.10. Qualquer dados mapeados nas linhas ocultas também está oculto no gráfico. Os rótulos do eixo x identificam apenas os pontos de dados que aparecem no gráfico. Como cada média móvel na Figura 5.10 engloba sete dias, nenhuma média móvel é emparelhada com as três primeiras ou três últimas observações reais. Copiar e colar a fórmula na célula D5 um dia para a célula D4 é executado fora das observações 8212não há observação registrada na célula C1. Da mesma forma, não há média móvel registrada abaixo da célula D29. Copiar e colar a fórmula em D29 em D30 exigiria uma observação na célula C33, e nenhuma observação está disponível para o dia em que a célula representaria. Seria possível, naturalmente, encurtar o comprimento da média móvel para, digamos, cinco em vez de sete. Fazer assim significaria que as fórmulas de média móvel na Figura 5.10 poderiam começar na célula D4 em vez de D5. No entanto, neste tipo de análise, você quer que a duração da média móvel seja igual ao número de estações: sete dias em uma semana para eventos que se repetem semanalmente implica uma média móvel de comprimento sete e quatro trimestres em um ano para eventos que Recur anualmente implica uma média móvel de comprimento quatro. Em linhas semelhantes, geralmente quantificamos os efeitos sazonais de tal forma que eles totalizam a zero dentro do período abrangente. Como você viu na primeira seção deste capítulo, em médias simples, isso é feito calculando a média de (digamos) os quatro trimestres em um ano, e subtraindo a média para o ano de cada figura trimestral. Assim fazendo garante que o total dos efeitos sazonais é zero. Por sua vez, that8217s útil porque ele coloca os efeitos sazonais em um efeito footing8212a verão de 11 é tão longe da média como um efeito de inverno de 821111. Se você quiser média de cinco estações em vez de sete para obter a sua média móvel, você8217re melhor Off encontrar um fenômeno que se repete a cada cinco temporadas em vez de cada sete. No entanto, quando você toma a média dos efeitos sazonais mais tarde no processo, essas médias são improváveis ​​de somar a zero. É necessário, nesse ponto, recalibrar ou normalizar. As médias para que sua soma seja zero. Quando isso é feito, as médias sazonais médias expressam o efeito em um período de tempo de pertencer a uma estação particular. Uma vez normalizadas, as médias sazonais são denominadas os índices sazonais que este capítulo já mencionou várias vezes. Compreendendo Sazonais Específicos A Figura 5.10 também mostra o que são chamados de sazonalidade específica na coluna E. Eles são os que ficaram depois de subtrair a média móvel da observação real. Para ter uma noção do que os sazonais específicos representam, considere a média móvel na célula D5. É a média das observações em C2: C8. Os desvios de cada observação da média móvel (por exemplo, C2 8211 D5) são garantidos para somar a zero 8212 que é uma característica de uma média. Portanto, cada desvio expressa o efeito de estar associado a esse dia particular naquela semana particular. É um período sazonal específico, então específico, porque o desvio se aplica a essa segunda-feira ou terça-feira e assim por diante, e sazonal, porque neste exemplo tratamos cada dia como se fosse uma estação no período abrangente de uma semana. Como cada temporada específica mede o efeito de estar naquela época em relação à média móvel para esse grupo de (aqui) sete temporadas, você pode, em seguida, classificar os períodos sazonais específicos para uma determinada temporada (por exemplo, todas as sextas-feiras na sua estação). Séries temporais) para estimar que season8217s geral, em vez de efeito específico. Essa média não é confundida por uma tendência subjacente na série temporal, porque cada estação específica expressa um desvio de sua própria média móvel particular. Alinhando as Médias Móveis Há também a questão de alinhar as médias móveis com o conjunto de dados original. Na Figura 5.10. Alinhei cada média móvel com o ponto médio da gama de observações que inclui. Assim, por exemplo, a fórmula na célula D5 faz a média das observações em C2: C8 e alinhei-a com a quarta observação, o ponto médio da faixa média, colocando-a na linha 5. Esta disposição é denominada média móvel centrada . E muitos analistas preferem alinhar cada média móvel com o ponto médio das observações que médias. Tenha em mente que, neste contexto, 8220midpoint8221 refere-se ao meio de um período de tempo: quinta-feira é o ponto médio de segunda-feira a domingo. Ele não se refere à mediana dos valores observados, embora, obviamente, possa funcionar dessa maneira na prática. Outra abordagem é a média móvel à direita. Nesse caso, cada média móvel está alinhada com a observação final de que ela média 8212 e, portanto, trilhas por trás de seus argumentos. Este é frequentemente o arranjo preferido se você quiser usar uma média móvel como uma previsão, como é feito com suavização exponencial, porque sua média móvel final ocorre coincidente com a observação disponível final. Centered Moving Averages with Even Numbers of Seasons Normalmente, adotamos um procedimento especial quando o número de estações é mesmo em vez de estranho. Esse é o típico estado de coisas: tendem a haver números pares de estações no período abrangente para épocas típicas, como meses, trimestres e períodos quadrienais (para as eleições). A dificuldade com um número par de estações é que não há ponto médio. Dois não é o ponto médio de um intervalo começando em 1 e terminando em 4, e nenhum é 3 se pode ser dito ter um, seu ponto médio é 2.5. Seis não é o ponto médio de 1 a 12, e nem é 7 seu ponto médio puramente teórico é 6,5. Para agir como se houvesse um ponto médio, você precisará adicionar uma camada de média sobre as médias móveis. Consulte a Figura 5.11. Figura 5.11 O Excel oferece várias maneiras de calcular uma média móvel centrada. A idéia por trás dessa abordagem para obter uma média móvel centrada em um ponto médio existente, quando há um número par de estações, é puxar esse ponto médio para a frente por meia temporada. Você calcula uma média móvel que seria centrada em, digamos, o terceiro ponto no tempo se cinco estações em vez de quatro constituíam uma volta completa do calendário. Isso é feito tomando duas médias móveis consecutivas e fazendo a média deles. Assim, na Figura 5.11. Existe uma média móvel na célula E6 que calcula a média dos valores em D3: D9. Como existem quatro valores sazonais em D3: D9, a média móvel em E6 é considerada como centrada na estação imaginária 2,5, meio ponto aquém da primeira temporada candidata disponível, 3. (As estações 1 e 2 não estão disponíveis como pontos médios para Falta de dados para a média antes da primeira temporada). Note-se, no entanto, que a média móvel na célula E8 média os valores em D5: D11, o segundo através do quinto na série temporal. Essa média é centrada no (imaginário) ponto 3.5, um período completo à frente da média centrada em 2,5. Ao calcular a média das duas médias móveis, então o pensamento vai, você pode puxar o ponto central da primeira média móvel para a frente por meio ponto, de 2,5 para 3. That8217s o que as médias na coluna F da Figura 5.11 fazer. A célula F7 fornece a média das médias móveis em E6 e E8. E a média em F7 é alinhada com o terceiro ponto de dados na série de tempo original, na célula D7, para enfatizar que a média é centrada nessa temporada. Se você expandir a fórmula na célula F7, bem como as médias móveis nas células E6 e E8, você verá que ele se torna uma média ponderada dos primeiros cinco valores na série de tempo, com o primeiro eo quinto valor dado um peso De 1 e o segundo a quarto valores dado um peso de 2. Isso nos leva a uma maneira mais rápida e simples de calcular uma média móvel centrada com um número par de estações. Ainda na Figura 5.11. Os pesos são armazenados na gama H3: H11. Esta fórmula retorna a primeira média móvel centrada, na célula I7: Essa fórmula retorna 13.75. Que é idêntico ao valor calculado pela fórmula de média dupla na célula F7. Fazendo a referência aos pesos absolutos, por meio dos sinais de dólar em H3: H11. Você pode copiar a fórmula e colá-lo para baixo, na medida do necessário para obter o resto das médias móveis centradas. Detrender a série com médias móveis Quando você tiver subtraído as médias móveis das observações originais para obter os valores sazonais específicos, você removeu a tendência subjacente da série. O que é deixado nos sazonais específicos é normalmente uma série estacionária, horizontal, com dois efeitos que fazem com que os sazonais específicos partam de uma linha absolutamente reta: os efeitos sazonais e erro aleatório nas observações originais. A Figura 5.12 mostra os resultados para este exemplo. Figura 5.12 Os efeitos sazonais específicos para sexta-feira e sábado permanecem claros na série detrended. O gráfico superior na Figura 5.12 mostra as observações diárias originais. Tanto a tendência ascendente geral como os picos sazonais de fim de semana são claros. O gráfico inferior mostra os dados sazonais específicos: o resultado de desviar da série original com um filtro de média móvel, conforme descrito anteriormente em 8220. Compreendendo Sazonais Específicos.8221 Você pode ver que a série detrended é agora praticamente horizontal (uma linha de tendência linear para os sazonais específicos Tem uma ligeira descida), mas os picos sazonais de sexta e sábado ainda estão no lugar. O próximo passo é ultrapassar os sazonais específicos para os índices sazonais. Consulte a Figura 5.13. Figura 5.13 Os efeitos sazonais específicos são primeiro calculados pela média e depois normalizados para atingir os índices sazonais. Na Figura 5.13. Os sais sazonais específicos na coluna E são rearranjados na forma tabular ilustrada na gama H4: N7. O objetivo é simplesmente facilitar o cálculo das médias sazonais. Essas médias são mostradas em H11: N11. No entanto, os números em H11: N11 são médias, não desvios de uma média, e portanto podemos esperar que eles somem a zero. Nós ainda precisamos ajustá-los para que eles expressam desvios de um grande meio. Essa grande média aparece na célula N13, e é a média das médias sazonais. Podemos chegar aos índices sazonais subtraindo a grande média em N13 de cada uma das médias sazonais. O resultado está na gama H17: N17. Esses índices sazonais deixam de ser específicos a uma determinada média móvel, como é o caso dos dados sazonais específicos na coluna E. Como eles são baseados em uma média de cada instância de uma determinada estação, eles expressam o efeito médio de uma determinada estação em todo o período Quatro semanas na série de tempo. Além disso, são medidas de uma estação, um dia em que as prisões de trânsito são visíveis em relação à média de um período de sete dias. Podemos agora usar esses índices sazonais para dessazonalizar a série. Utilizaremos a série dessazonalizada para obter previsões por meio de regressão linear ou método Holt8217s de suavização de séries tendenciosas (discutido no Capítulo 4). Então nós simplesmente somamos os índices sazonais de volta às previsões para reseasonalize eles. Tudo isso aparece na Figura 5.14. Figura 5.14 Depois de ter os índices sazonais, os toques finais aplicados aqui são os mesmos do método de médias simples. As etapas ilustradas na Figura 5.14 são em grande parte as mesmas que as das Figuras 5.6 e 5.7. Discutido nas seções a seguir. Desestacionando as Observações Subtraia os índices sazonais das observações originais para dessazonalizar os dados. Você pode fazer isso como mostrado na Figura 5.14. Em que as observações originais e os índices sazonais são organizados como duas listas começando na mesma linha, colunas C e F. Este arranjo torna um pouco mais fácil de estruturar os cálculos. Você também pode fazer a subtração como mostrado na Figura 5.6. Em que as observações trimestrais originais (C12: F16), os índices trimestrais (C8: F8) e os resultados dessazonalizados (C20: F24) são mostrados em formato tabular. Esse arranjo torna um pouco mais fácil de se concentrar nos índices sazonais e os trimestrais dessazonalizados. Previsão das Observações desestacionalizadas Na Figura 5.14. As observações dessazonalizadas estão na coluna H e na Figura 5.7 estão na coluna C. Independentemente de você desejar usar uma abordagem de regressão ou uma abordagem de suavização da previsão, é melhor organizar as observações dessazonalizadas em uma lista de uma única coluna. Na Figura 5.14. As previsões estão na coluna J. A seguinte fórmula de matriz é inserida no intervalo J2: J32. Anteriormente neste capítulo, eu apontei que se você omitir o argumento x-values ​​dos argumentos da função TREND () function8217s, o Excel fornece os valores padrão 1. 2. N. Onde n é o número de valores y. Na fórmula dada anteriormente, H2: H32 contém 31 valores y. Como o argumento normalmente contendo os valores x está faltando, o Excel fornece os valores padrão 1. 2. 31. Esses são os valores que nós gostaríamos de usar de qualquer maneira, na coluna B, então a fórmula como dado é equivalente a TENDÊNCIA (H2: H32, B2: B32). E essa é a estrutura usada em D5: D24 da Figura 5.7: Fazendo a Previsão de Um Passo Ahead Até agora, você arranjou previsões das séries temporais dessazonalizadas de t 1 a t 31 na Figura 5.14. E de t 1 a t 20 na Figura 5.7. Estas previsões constituem informações úteis para vários fins, incluindo a avaliação da exactidão das previsões através de uma análise RMSE. Mas seu objetivo principal é prever, pelo menos, o próximo, ainda não observado período de tempo. Para conseguir isso, você pode primeiro prever a partir da função TREND () ou PROJ. LIN () se você estiver usando a regressão, ou da fórmula de suavização exponencial se você usar o método Holt8217s. Em seguida, você pode adicionar o índice sazonal associado à previsão de regressão ou suavização, para obter uma previsão que inclua a tendência eo efeito sazonal. Na Figura 5.14. Você obtém a previsão de regressão na célula J33 com esta fórmula: Nesta fórmula, os valores de y em H2: H32 são os mesmos que nas outras fórmulas de TREND () na coluna J. Assim são os valores de x (padrão) de 1 Até 32. Agora, porém, você fornece um novo valor x como o terceiro argumento de function8217s, que você diz para TREND () procurar na célula B33. It8217s 32. O próximo valor de t. E Excel retorna o valor 156.3 na célula J33. A função TREND () na célula J33 está dizendo ao Excel, na verdade, 8220Cálculo da equação de regressão para os valores em H2: H32 regrediu nos valores t de 1 a 31. Aplicar essa equação de regressão para o novo valor de x de 32 e retornar o resultado.8221 Você encontrará a mesma abordagem na célula D25 da Figura 5.7. Onde a fórmula para obter a previsão um passo adiante é esta: Adicionando os índices sazonais Voltar Na etapa final é reseasonalize as previsões, adicionando os índices sazonais para as previsões de tendência, reverter o que você fez quatro passos para trás quando você subtraiu o Índices das observações originais. Isso é feito na coluna F na Figura 5.7 e na coluna K na Figura 5.14. Não se esqueça de adicionar o índice sazonal apropriado para a previsão de um passo em frente, com os resultados mostrados na célula F25 na Figura 5.7 e na célula K33 na Figura 5.14. Você pode encontrar gráficos de três representações dos dados de detenção de tráfego na Figura 5.15. A série dessazonalizada, a previsão linear a partir dos dados dessazonalizados, e as previsões reseasonalized. Observe que as previsões incorporam tanto a tendência geral dos dados originais quanto os picos de sexta-feira / sábado. Figura 5.15 Gráfico das previsões.