Resumo
Para calcular uma média móvel ou móvel, você pode usar uma fórmula simples baseada na função AVERAGE com referências relativas. No exemplo mostrado, a fórmula em E7 é:
=AVERAGE(C5:C7)
Conforme a fórmula é copiada, ela calcula uma média móvel de 3 dias com base no valor de vendas do dia atual e dos dois dias anteriores.
Abaixo está uma opção mais flexível baseada na função OFFSET que lida com períodos variáveis.
Sobre médias móveis
Uma média móvel (também chamada de média móvel) é uma média baseada em subconjuntos de dados em determinados intervalos. O cálculo de uma média em intervalos específicos suaviza os dados, reduzindo o impacto das flutuações aleatórias. Isso torna mais fácil ver as tendências gerais, especialmente em um gráfico. Quanto maior o intervalo usado para calcular uma média móvel, mais suavização ocorre, uma vez que mais pontos de dados são incluídos em cada média calculada.
Explicação
Todas as fórmulas mostradas no exemplo usam a função AVERAGE com uma referência relativa configurada para cada intervalo específico. A média móvel de 3 dias em E7 é calculada alimentando MÉDIA com uma faixa que inclui o dia atual e os dois dias anteriores como este:
=AVERAGE(C5:C7) // 3-day average
As médias de 5 e 7 dias são calculadas da mesma maneira. Em cada caso, o intervalo fornecido para AVERAGE é ampliado para incluir o número necessário de dias:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Todas as fórmulas usam uma referência relativa para o intervalo fornecido para a função AVERAGE. Conforme as fórmulas são copiadas na coluna, o intervalo muda em cada linha para incluir os valores necessários para cada média.
Quando os valores são plotados em um gráfico de linha, o efeito de suavização é claro:
Dados insuficientes
Se você iniciar as fórmulas na primeira linha da tabela, as primeiras fórmulas não terão dados suficientes para calcular uma média completa, porque o intervalo se estenderá acima da primeira linha de dados:
Isso pode ou não ser um problema, dependendo da estrutura da planilha e se é importante que todas as médias sejam baseadas no mesmo número de valores. A função AVERAGE irá ignorar automaticamente os valores de texto e células vazias, então continuará a calcular uma média com menos valores. É por isso que "funciona" em E5 e E6.
Uma maneira de indicar claramente dados insuficientes é verificar o número da linha atual e cancelar com #NA quando houver menos de n valores. Por exemplo, para a média de 3 dias, você pode usar:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
A primeira parte da fórmula simplesmente gera um número de linha "normalizado", começando com 1:
ROW()-ROW($C$5)+1 // relative row number
Na linha 5, o resultado é 1, na linha 6, o resultado é 2 e assim por diante.
Quando o número da linha atual é menor que 3, a fórmula retorna # N / A. Caso contrário, a fórmula retorna uma média móvel como antes. Isso imita o comportamento da versão do Analysis Toolpak da média móvel, que produz # N / A até que o primeiro período completo seja alcançado.
No entanto, à medida que o número de períodos aumenta, você acabará ficando sem linhas acima dos dados e não será capaz de inserir o intervalo necessário dentro de AVERAGE. Por exemplo, você não pode configurar uma média móvel de 7 dias com a planilha conforme mostrado, uma vez que você não pode inserir um intervalo que se estenda 6 linhas acima de C5.
Períodos variáveis com OFFSET
Uma forma mais flexível de calcular uma média móvel é com a função OFFSET. OFFSET pode criar uma faixa dinâmica, o que significa que podemos configurar uma fórmula onde o número de períodos é variável. A forma geral é:
=AVERAGE(OFFSET(A1,0,0,-n,1))
onde n é o número de períodos a serem incluídos em cada média. Como acima, OFFSET retorna um intervalo que é passado para a função AVERAGE. Abaixo você pode ver esta fórmula em ação, onde "n" é o intervalo nomeado E2. Começando na célula C5, OFFSET constrói um intervalo que se estende de volta às linhas anteriores. Isso é feito usando uma altura igual a n negativo. Quando E5 é alterado para outro número, a média móvel recalcula em todas as linhas:
A fórmula em E5, copiada, é:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Como a fórmula original acima, a versão com OFFSET também terá o problema de dados insuficientes nas primeiras linhas, dependendo de quantos períodos são dados em E5.
No exemplo mostrado, as médias são calculadas com êxito porque a função AVERAGE ignora automaticamente os valores de texto e células em branco e não há outros valores numéricos acima de C5. Portanto, embora o intervalo passado para AVERAGE em E5 seja C1: C5, há apenas um valor para fazer a média, 100. No entanto, conforme os períodos aumentam, OFFSET continuará a criar um intervalo que se estende acima do início dos dados, eventualmente atingindo parte superior da planilha e retornando um erro #REF.
Uma solução é "limitar" o tamanho do intervalo ao número de pontos de dados disponíveis. Isso pode ser feito usando a função MIN para restringir o número usado para a altura, conforme visto abaixo:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Isso parece muito assustador, mas na verdade é muito simples. Estamos limitando a altura passada para OFFSET com a função MIN:
MIN(ROW()-ROW($C$5)+1,n)
Dentro de MIN, o primeiro valor é um número de linha relativo, calculado com:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
O segundo valor dado a MIN é o número de períodos, n. Quando o número da linha relativa é menor que n, MIN retorna o número da linha atual para OFFSET para a altura. Quando o número da linha é maior que n, MIN retorna n. Em outras palavras, MIN simplesmente retorna o menor dos dois valores.
Um bom recurso da opção OFFSET é que n pode ser facilmente alterado. Se mudarmos n para 7 e plotarmos os resultados, obteremos um gráfico como este:
Observação: uma peculiaridade com as fórmulas OFFSET acima é que elas não funcionam no Planilhas Google, porque a função OFFSET no Planilhas não permite um valor negativo para altura ou largura. A planilha em anexo contém fórmulas alternativas para planilhas do Google.
