Como muitos de meus tutoriais do Excel, este também foi inspirado por uma das consultas que recebi de um amigo. Ela queria calcular a média móvel no Excel e pedi a ela que pesquisasse online (ou assistisse a um vídeo no YouTube sobre isso).
Mas então, decidi escrever um para mim (o fato de que eu era um tanto nerd em estatísticas na faculdade também desempenhou um papel menor).
Agora, antes de dizer como calcular a média móvel no Excel, deixe-me dar uma visão geral do que significa a média móvel e quais tipos de médias móveis existem.
Caso queira pular para a parte onde mostro como calcular a média móvel no Excel, clique aqui.
Nota: Não sou um especialista em estatística e minha intenção neste tutorial não é cobrir tudo sobre médias móveis. Meu objetivo apenas é mostrar a você como calcular médias móveis no Excel (com uma breve introdução do que significam as médias móveis).O que é uma média móvel?
Tenho certeza que você sabe o que é um valor médio.
Se eu tiver três dias de dados diários de temperatura, você pode me dizer facilmente a média dos últimos três dias (dica: você pode usar a função AVERAGE no Excel para fazer isso).
Uma média móvel (também chamada de média móvel ou média móvel) ocorre quando você mantém o mesmo período de tempo da média, mas continua se movendo à medida que novos dados são adicionados.
Por exemplo, no dia 3, se eu perguntar a temperatura média móvel de 3 dias, você me dará o valor da temperatura média dos dias 1, 2 e 3. E se no dia 4 eu perguntar a temperatura média móvel de 3 dias , você me dará a média dos dias 2, 3 e 4.
À medida que novos dados são adicionados, você mantém o mesmo período de tempo (3 dias), mas usa os dados mais recentes para calcular a média móvel.
A média móvel é muito usada para análise técnica e muitos bancos e analistas do mercado de ações a usam diariamente (abaixo está um exemplo que peguei no site Market Realist).
Tipos de médias móveis
Existem três tipos de médias móveis:
- Média móvel simples (SMA)
- Média móvel ponderada (WMA)
- Média móvel exponencial (EMA)
Média Móvel Simples (SMA)
Esta é a média simples dos pontos de dados na duração fornecida.
Em nosso exemplo de temperatura diária, quando você simplesmente tira uma média dos últimos 10 dias, dá a média móvel simples de 10 dias.
Isso pode ser obtido calculando a média dos pontos de dados na duração fornecida. No Excel, você pode fazer isso facilmente usando a função AVERAGE (abordada posteriormente neste tutorial).
Média móvel ponderada (WMA)
Digamos que o tempo está ficando mais frio a cada dia que passa e você está usando uma média móvel de 10 dias para obter a tendência da temperatura.
É mais provável que a temperatura do dia 10 seja um melhor indicador da tendência em comparação com o dia 1 (uma vez que a temperatura está caindo a cada dia que passa).
Portanto, estaremos melhor se confiarmos mais no valor do Dia 10.
Para que isso se reflita em nossa média móvel, você pode dar mais peso aos dados mais recentes e menos aos dados anteriores. Dessa forma, você ainda obtém a tendência, mas com mais influência dos dados mais recentes.
Isso é chamado de média móvel ponderada.
Média móvel exponencial (EMA)
A média móvel exponencial é um tipo de média móvel ponderada em que mais peso é dado aos dados mais recentes e diminui exponencialmente para os pontos de dados mais antigos.
É também chamada de média móvel exponencial ponderada (EWMA)
A diferença entre o WMA e o EMA é que, com o WMA, você pode atribuir pesos com base em qualquer critério. Por exemplo, em uma média móvel de 3 pontos, você pode atribuir uma idade de peso de 60% para o ponto de dados mais recente, 30% para o ponto de dados do meio e 10% para o ponto de dados mais antigo.
Na EMA, um peso maior é dado ao último valor e o peso continua ficando exponencialmente menor para os valores anteriores.
Chega de aula de estatística.
Agora vamos mergulhar e ver como calcular médias móveis no Excel.
Calculando a média móvel simples (SMA) usando o Data Analysis Toolpak no Excel
O Microsoft Excel já possui uma ferramenta embutida para calcular as médias móveis simples.
É chamado de Data Analysis Toolpak.
Antes de usar o pacote de ferramentas de Análise de Dados, você primeiro precisa verificar se ele está na faixa de opções do Excel ou não. Há uma boa chance de você precisar executar algumas etapas para ativá-lo primeiro.
Caso você já tenha a opção Análise de dados na guia Dados, pule as etapas abaixo e consulte as etapas de cálculo de médias móveis.Clique na guia Dados e verifique se você vê a opção Análise de Dados ou não. Caso não o veja, siga as etapas abaixo para disponibilizá-lo na faixa de opções.
- Clique na guia Arquivo
- Clique em Opções
- Na caixa de diálogo Opções do Excel, clique em Suplementos
- Na parte inferior da caixa de diálogo, selecione Suplementos do Excel no menu suspenso e clique em Ir.
- Na caixa de diálogo Add-ins que é aberta, marque a opção Analysis Toolpak
- Clique OK.
As etapas acima habilitariam o Pacote de Ferramentas de Análise de Dados e você verá essa opção na guia Dados agora.
Suponha que você tenha o conjunto de dados conforme mostrado abaixo e deseja calcular a média móvel dos últimos três intervalos.
Abaixo estão as etapas para usar a análise de dados para calcular uma média móvel simples:
- Clique na guia Dados
- Clique na opção Análise de Dados
- Na caixa de diálogo Data Analysis, clique na opção Moving Average (você pode ter que rolar um pouco para alcançá-la).
- Clique OK. Isso abrirá a caixa de diálogo ‘Média móvel’.
- No intervalo de entrada, selecione os dados para os quais deseja calcular a média móvel (B2: B11 neste exemplo)
- Na opção Intervalo, insira 3 (já que estamos calculando uma média móvel de três pontos)
- No intervalo de saída, insira a célula onde deseja os resultados. Neste exemplo, estou usando C2 como o intervalo de saída
- Clique OK
As etapas acima fornecem o resultado da média móvel conforme mostrado abaixo.
Observe que as duas primeiras células na coluna C têm o resultado como erro # N / A. Isso ocorre porque é uma média móvel de três pontos e precisa de pelo menos três pontos de dados para dar o primeiro resultado. Portanto, os valores reais da média móvel começam após o terceiro ponto de dados em diante.
Você também notará que tudo o que este conjunto de ferramentas de Análise de Dados fez é aplicar uma fórmula MÉDIA às células. Portanto, se você quiser fazer isso manualmente sem o pacote de ferramentas de Análise de Dados, certamente poderá fazer isso.
Existem, no entanto, algumas coisas que são mais fáceis de fazer com o pacote de ferramentas de análise de dados. Por exemplo, se você deseja obter o valor do erro padrão, bem como o gráfico da média móvel, tudo o que você precisa fazer é marcar uma caixa e isso fará parte da saída.
Calculando Médias Móveis (SMA, WMA, EMA) usando Fórmulas no Excel
Você também pode calcular as médias móveis usando a fórmula AVERAGE.
Na verdade, se tudo o que você precisa é o valor da média móvel (e não o erro padrão ou gráfico), usar uma fórmula pode ser uma opção melhor (e mais rápida) do que usar o Data Analysis Toolpak.
Além disso, o Toolpak de análise de dados fornece apenas a média móvel simples (SMA), mas se você quiser calcular o WMA ou o EMA, precisará confiar apenas nas fórmulas.
Calculando a média móvel simples usando fórmulas
Suponha que você tenha o conjunto de dados conforme mostrado abaixo e deseja calcular o SMA de 3 pontos:
Na célula C4, insira a seguinte fórmula:
= MÉDIA (B2: B4)
Copie esta fórmula para todas as células e ela lhe dará o SMA para cada dia.
Lembre-se: ao calcular o SMA usando fórmulas, você precisa se certificar de que as referências na fórmula são relativas. Isso significa que a fórmula pode ser = AVERAGE (B2: B4) ou = AVERAGE ($ B2: $ B4), mas não pode ser = AVERAGE ($ B $ 2: $ B $ 4) ou = AVERAGE (B $ 2: B $ 4 ) A parte do número da linha da referência deve estar sem o cifrão. Você pode ler mais sobre referências absolutas e relativas aqui.
Como estamos calculando uma média móvel simples (SMA) de 3 pontos, as duas primeiras células (para os primeiros dois dias) estão vazias e começamos a usar a fórmula a partir do terceiro dia. Se desejar, você pode usar os dois primeiros valores como estão e usar o valor SMA do terceiro em diante.
Calculando a média móvel ponderada usando fórmulas
Para WMA, você precisa saber os pesos que seriam atribuídos aos valores.
Por exemplo, suponha que você precise calcular o WMA de 3 pontos para o conjunto de dados abaixo, onde 60% do peso é dado ao último valor, 30% ao anterior e 10% ao anterior.
Para fazer isso, insira a seguinte fórmula na célula C4 e copie para todas as células.
= 0,6 * B4 + 0,3 * B3 + 0,1 * B2
Como estamos calculando uma média móvel ponderada de 3 pontos (WMA), as duas primeiras células (para os primeiros dois dias) estão vazias e começamos a usar a fórmula a partir do terceiro dia. Se desejar, você pode usar os dois primeiros valores como estão e usar o valor WMA do terceiro em diante.
Calculando a média móvel exponencial usando fórmulas
A média móvel exponencial (EMA) atribui maior peso ao último valor e os pesos continuam diminuindo exponencialmente para os valores anteriores.
Abaixo está a fórmula para calcular a MME para uma média móvel de três pontos:
EMA = [Último valor - Valor EMA anterior] * (2 / N + 1) + EMA anterior
… onde N seria 3 neste exemplo (já que estamos calculando uma MME de três pontos)
Observação: para o primeiro valor da EMA (quando você não tem nenhum valor anterior para calcular a EMA), basta pegar o valor como está e considerá-lo o valor da EMA. Você pode então usar esse valor daqui para frente.Suponha que você tenha o conjunto de dados abaixo e deseja calcular a MME de três períodos:
Na célula C2, insira o mesmo valor que em B2. Isso ocorre porque não há valor anterior para calcular a EMA.
Na célula C3, insira a fórmula abaixo e copie para todas as células:
= (B3-C2) * (2/4) + C2
Neste exemplo, mantive a simplicidade e usei o valor mais recente e o valor anterior da MME para calcular a MME atual.
Outra maneira popular de fazer isso é calcular primeiro a média móvel simples e, em seguida, usá-la em vez do último valor real.
Adicionando Linha de Tendência de Média Móvel a um Gráfico de Colunas
Se você tiver um conjunto de dados e estiver criando um gráfico de barras usando-o, também pode adicionar a linha de tendência da média móvel com alguns cliques.
Suponha que você tenha um conjunto de dados conforme mostrado abaixo:
Abaixo estão as etapas para criar um gráfico de barras usando esses dados e adicionando uma linha de tendência de média móvel de três partes a este gráfico:
- Selecione o conjunto de dados (incluindo os cabeçalhos)
- Clique na guia Inserir
- No grupo Gráfico, clique no ícone "Inserir coluna ou gráfico de barras".
- Clique na opção de gráfico Clustered Column. Isso irá inserir o gráfico na planilha.
- Com o gráfico selecionado, clique na guia Design (esta guia só aparece quando o gráfico é selecionado)
- No grupo Chart Layouts, clique em ‘Add Chart Element’.
- Passe o cursor sobre a opção ‘Trendline’ e clique em ‘More Trendline Options’
- No painel Format Trendline, selecione a opção ‘Moving Average’ e defina o número de períodos.
É isso! As etapas acima adicionariam uma linha de tendência móvel ao gráfico de colunas.
Caso queira inserir mais de uma linha de tendência de média móvel (por exemplo, uma para 2 períodos e outra para 3 períodos), repita as etapas de 5 a 8).
Você também pode usar as mesmas etapas para inserir uma linha de tendência de média móvel em um gráfico de linhas.
Formatando a linha de tendência da média móvel
Ao contrário de um gráfico de linha regular, uma linha de tendência de média móvel não permite muita formatação. Por exemplo, se você deseja destacar um ponto de dados específico na linha de tendência, você não será capaz de fazer isso.
Algumas coisas que você pode formatar na linha de tendência incluem:
- Cor da linha. Você pode usar isso para destacar uma das linhas de tendência, tornando tudo no gráfico em cores claras e fazendo a linha de tendência aparecer com uma cor brilhante
- O grossura da linha
- O transparência da linha
Para formatar a linha de tendência da média móvel, clique com o botão direito sobre ela e selecione a opção Formatar linha de tendência.
Isso abrirá o painel Format Trendline à direita. Este painel contém todas as opções de formatação (em diferentes seções - Preenchimento e Linha, Efeitos e Opções de Linha de tendência).
