Suavização de dados de gráficos Excel com uma média móvel fixa funciona quando você tem experiência com os dados e como eles são variáveis. Mas, se os dados mudarem ou se você ou a gerência quiser experimentar um número diferente de períodos na média de suavização.
Neste exemplo, uma fórmula OFFSET é usada para criar uma média através de um intervalo dinâmico. Você digita o número de meses que deseja usar para um período de suavização e a fórmula AVERAGE e OFFSET calcula quantas células para a média através de.
Antes de tornar a AVERAGE dinâmica, vamos começar por entender como OFFSET funciona. Na figura seguinte, a célula G26 contém a fórmula para suavizar os dados na linha 20,
=AVERAGE(OFFSET(G20,0,0,1,-3))
OFFSET especifica o intervalo que a AVERAGE irá suavizar os dados sobre.
Esta fórmula é um passo intermediário para ajudá-lo a entender como a fórmula dinâmica funciona. A função OFFSET retorna um intervalo no qual o AVERAGE irá actuar. Os atributos do OFFSET são,
OFFSET(referência, linhas, colunas,)
Neste exemplo, o intervalo que o OFFSET calcula começa em G20 e tem um deslocamento de linha ou coluna zero. Em outras palavras, o canto superior esquerdo do intervalo que o OFFSET calcula é apenas G20. Ele não será deslocado ou deslocado por nenhuma linha ou coluna. O intervalo calculado será de 1 linha e incluirá a célula G26 actual e mais duas à esquerda (isto é, -3). Então, este intervalo calculado será G20:E20.
Copiar essa fórmula em G26 para a direita e você verá a média da célula de dados acima dela na linha 20 e as duas células anteriores para a esquerda.
Então, aqui está o truque que fará disto uma dinâmica e média através de um intervalo que você escolher. E se você substituir uma referência de célula por aquela -3 na fórmula?
Na figura abaixo a fórmula na célula G26 foi modificada de modo que a é especificada pelo número na célula E24,
AVERAGE(OFFSET(G20,0,0,1,-$E$24))
$E$24 é a célula sombreada verde. O número que você digitar na célula E24 determinará a largura das células usadas para a média.
Entrar um valor em E24 mudará o intervalo usado para suavizar os dados.
Neste ponto você poderia usar um spinner ou menu suspenso para dar ao seu usuário uma escolha de quão largo para fazer a média de suavização. O resultado da sua seleção deve ir na célula E24.
Download the smoothing Excel Chart Data with Fixed or Dynamic Smoothing
Existe um problema com esta disposição. Se o usuário inserir um número maior no E24 do que o número de células à esquerda, então o OFFSET incluirá células em branco e a etiqueta. Isto dará uma média incorrecta. Se o E24 é tão grande que o OFFSET sai da folha, então o AVERAGE produz um erro. O que fazer? Precisamos de uma solução que não pare apenas um erro, como ISERROR, precisamos de uma que pare as respostas incorrectas.
Nesta próxima figura a fórmula é a célula G26 foi ajustada. A linha 25 tem uma série de números que são os limites para os intervalos válidos à esquerda. A nova fórmula na célula G26 olha para a linha 25 e se o número na célula E24 for maior que o limite na linha 25, então NA() é produzido. Esta nova fórmula de correção de erros em G26 é,
=IF($E$24>G25,NA(),AVERAGE(OFFSET(G20,0,0,0,1,-$E$24)))
Quando um gráfico do Excel faz referência a NA() em uma célula, ele não plota nada. Isso evita que a linha de suavização caia a zero.
Modifique a fórmula de suavização dinâmica para evitar erros.
Problemas com dados de suavização e como contorná-los
Agora você pode deixar seu usuário tentar qualquer período de suavização que ele quiser. No entanto, (parece que há sempre um “contudo” ou um “mas, neste caso”) há um problema com o alisamento de dados. Suavizar elimina a volatilidade, mas também significa que você não verá mudanças na direção, pontos de inflexão ou novas tendências até cerca da metade do período de suavização. Você está atrás da curva.
O melhor de dois mundos é usar uma função de suavização média ponderada. Dessa forma você pode dar mais peso aos dados a curto prazo para que você veja tendências, mas você pode suavizar com base em dados mais antigos para eliminar parte da volatilidade. Parece ser um bom caso para outro artigo, suavizando dados de gráficos do Excel usando uma média ponderada.
Baixar o arquivo de exemplo para suavizar dados de gráficos do Excel com suavização móvel ou dinâmica
Artigos relacionados,
- suavizar dados de gráficos do Excel com média móvel
- suavizar dados de gráficos do Excel usando uma média ponderada