Weighted moving average using excel

Média móvel Este exemplo ensina como calcular a média móvel de uma série temporal no Excel. Um avarge movente é usado para suavizar irregularidades (picos e vales) para reconhecer facilmente as tendências. 1. Primeiro, vamos dar uma olhada em nossa série temporal. 2. Na guia Dados, clique em Análise de dados. Nota: não é possível localizar o botão Análise de dados Clique aqui para carregar o suplemento Analysis ToolPak. 3. Selecione Média Móvel e clique em OK. 4. Clique na caixa Intervalo de entrada e selecione o intervalo B2: M2. 5. Clique na caixa Interval (Intervalo) e digite 6. 6. Clique na caixa Output Range (Intervalo de saída) e selecione a célula B3. 8. Plote um gráfico desses valores. Explicação: porque definimos o intervalo para 6, a média móvel é a média dos 5 pontos de dados anteriores e do ponto de dados atual. Como resultado, picos e vales são suavizados. O gráfico mostra uma tendência crescente. O Excel não pode calcular a média móvel dos primeiros 5 pontos de dados porque não há pontos de dados anteriores suficientes. 9. Repita os passos 2 a 8 para o 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 dos pontos de dados reais. Calcular Médias Ponderadas no Excel com SUMPRODUCT Ted French tem mais de quinze anos de experiência em ensino e redação sobre programas de planilhas como Excel, Google Spreadsheets e Lotus 1-2-3. . Leia mais Atualizado em 07 de fevereiro de 2016. Média ponderada versus não ponderada Visão geral Geralmente, ao calcular a média ou média aritmética, cada número tem igual valor ou peso. A média é calculada adicionando um intervalo de números e, em seguida, dividindo esse total pelo número de valores no intervalo. Um exemplo seria (2433434435436) / 5, que dá uma média não ponderada de 4. No Excel, esses cálculos são facilmente executados usando a função MÉDIA. Uma média ponderada, por outro lado, considera que um ou mais números no intervalo valem mais ou têm um peso maior do que os outros números. Por exemplo, certas notas na escola, como exames de meio termo e final, geralmente valem mais do que testes ou tarefas regulares. Se a média for usada para calcular a nota final de um aluno, os exames intercalar e final receberiam um peso maior. No Excel, as médias ponderadas podem ser calculadas usando a função SUMPRODUCT. Como funciona a função SUMPRODUCT O que o SUMPRODUCT faz é multiplicar os elementos de dois ou mais arrays e depois adicionar ou somar os produtos. Por exemplo, em uma situação onde duas matrizes com quatro elementos cada são inseridas como argumentos para a função SUMPRODUCT: o primeiro elemento de array1 é multiplicado pelo primeiro elemento em array2 o segundo elemento de array1 é multiplicado pelo segundo elemento de array2 o terceiro elemento de array1 é multiplicado pelo terceiro elemento de array2 o quarto elemento de array1 é multiplicado pelo quarto elemento de array2. Em seguida, os produtos das quatro operações de multiplicação são somados e retornados pela função como resultado. Sintaxe e argumentos da função SUMPRODUCT do Excel Uma sintaxe da função refere-se ao layout da função e inclui o nome, colchetes e argumentos da função. A sintaxe da função SUMPRODUCT é: 61 SUMPRODUCT (array1, array2, array3. Array255) Os argumentos para a função SUMPRODUCT são: array1: (obrigatório) o primeiro argumento da matriz. array2, array3. array255: (opcional) arrays adicionais, até 255. Com dois ou mais arrays, a função multiplica os elementos de cada array e adiciona os resultados. - os elementos da matriz podem ser referências de célula para a localização dos dados na planilha ou números separados por operadores aritméticos - como sinais de mais (43) ou menos (-). Se os números forem inseridos sem serem separados pelos operadores, o Excel os tratará como dados de texto. Essa situação é abordada no exemplo abaixo. Todos os argumentos da matriz devem ter o mesmo tamanho. Ou, em outras palavras, deve haver o mesmo número de elementos em cada matriz. Caso contrário, SUMPRODUCT retornará o valor de erro VALUE. Se quaisquer elementos da matriz não forem números, como dados de texto, o SUMPRODUCT os tratará como zeros. Exemplo: Calcular Média Ponderada no Excel O exemplo mostrado na imagem acima calcula a média ponderada para a classificação final de um aluno usando a função SUMPRODUCT. A função faz isso: multiplicando as várias marcas pelo fator de peso individual, somando os produtos dessas operações de multiplicação, divide a soma acima pelo total do fator de ponderação 7 (1431432433) para as quatro avaliações. Inserindo a fórmula de ponderação Como a maioria das outras funções no Excel, o SUMPRODUCT é normalmente inserido em uma planilha usando a caixa de diálogo da função. No entanto, como a fórmula de ponderação usa SUMPRODUCT de maneira não padrão - o resultado da função é dividido pelo fator de peso - a fórmula de ponderação deve ser digitada em uma célula da planilha. As etapas a seguir foram usadas para inserir a fórmula de ponderação na célula C7: Clique na célula C7 para torná-la a célula ativa - o local onde a marca final do aluno será exibida Digite a seguinte fórmula na célula: Pressione a tecla Enter no teclado A resposta 78.6 deve aparecer na célula C7 - sua resposta pode ter mais casas decimais A média não ponderada para as mesmas quatro notas seria 76.5 Como o aluno obteve melhores resultados em seus exames intercalar e final, ponderar a média ajudou a melhorar sua nota geral. Variações de Fórmula Para enfatizar que os resultados da função SUMPRODUCT são divididos pela soma dos pesos para cada grupo de avaliação, o divisor - a parte que faz a divisão - foi inserido como (1431432433). A fórmula de ponderação geral pode ser simplificada inserindo o número 7 (a soma dos pesos) como o divisor. A fórmula seria: Essa opção é válida se o número de elementos na matriz de ponderação for pequeno e eles puderem ser facilmente adicionados juntos, mas se tornará menos eficaz à medida que o número de elementos na matriz de ponderação aumentar, dificultando sua adição. Outra opção, e provavelmente a melhor escolha - pois usa referências de célula em vez de números ao totalizar o divisor - seria usar a função SUM para totalizar o divisor com a fórmula: Geralmente, é melhor inserir referências de célula em vez de números reais em fórmulas, uma vez que simplifica a sua atualização se os dados da fórmula forem alterados. Por exemplo, se os fatores de ponderação de Atribuições foram alterados para 0,5 no exemplo e para Testes para 1,5, as duas primeiras formas da fórmula teriam que ser editadas manualmente para corrigir o divisor. Na terceira variação, somente os dados nas células B3 e B4 precisam ser atualizados e a fórmula recalculará o resultado. Média Móvel Ponderada No Exemplo 1 da Previsão Média Móvel Simples. os pesos dados aos três valores anteriores eram todos iguais. Agora consideramos o caso em que esses pesos podem ser diferentes. Esse tipo de previsão é chamado de média móvel ponderada. Aqui nós atribuímos m pesos w 1. w m. onde w 1. w m 1 e defina os valores previstos como segue Exemplo 1. Refazer o Exemplo 1 da Previsão da Média Móvel Simples, onde assumimos que observações mais recentes são mais pesadas do que observações mais antigas, usando os pesos w1.6, w2.3 e w3.1 (como mostrado na faixa G4: G6 da Figura 1 ). Figura 1 Médias Móveis Ponderadas As fórmulas na Figura 1 são as mesmas da Figura 1 da Previsão Média Móvel Simples. exceto para os valores y previstos na coluna C. a fórmula na célula C7 é agora SUMPRODUCT (B4: B6, G4: G6). A previsão para o próximo valor na série temporal é agora 81,3 (célula C19), usando a fórmula SUMPRODUCT (B16: B18, G4: G6). Ferramenta de análise de dados estatísticos reais. O Excel não fornece uma ferramenta de análise de dados de médias móveis ponderadas. Em vez disso, você pode usar a ferramenta de análise de dados Médias Ponderadas com Estatísticas Reais. Para usar essa ferramenta no Exemplo 1, pressione Ctr-m. escolha a opção Série temporal no menu principal e, em seguida, a opção Métodos básicos de previsão na caixa de diálogo exibida. Preencha a caixa de diálogo que aparece conforme mostrado na Figura 5 da Previsão Média Móvel Simples. mas desta vez escolha a opção Médias Móveis Ponderadas e preencha o Intervalo de Pesos com G4: G6 (note que nenhum cabeçalho de coluna está incluído para a faixa de pesos). Nenhum dos valores de parâmetro é usado (essencialmente de Lags será o número de linhas no intervalo de pesos e de Seasons e de Previsões será padronizado como 1). A saída será semelhante à saída da Figura 2 da Previsão Média Móvel Simples. exceto que os pesos serão usados ​​no cálculo dos valores de previsão. Exemplo 2 Use o Solver para calcular os pesos que produzem o menor erro quadrático médio MSE. Usando as fórmulas na Figura 1, selecione Data gt AnalysisSolver e preencha a caixa de diálogo como mostrado na Figura 2. Figura 2 Caixa de diálogo Solver Note que precisamos restringir a soma dos pesos para ser 1, o que fazemos clicando no Botão Adicionar. Isso traz a caixa de diálogo Add Constraint, que preenchemos como mostra a Figura 3 e depois clica no botão OK. Figura 3 Caixa de diálogo Add Constraint Em seguida, clique no botão Solve (na Figura 2), que modifica os dados na Figura 1, como mostra a Figura 4. Figura 4 Otimização do Solver Como pode ser visto na Figura 4, o Solver altera os pesos para 0 223757 e .776243 para minimizar o valor de MSE. Como você pode ver, o valor minimizado de 184.688 (célula E21 da Figura 4) é pelo menos menor que o valor MSE de 191.366 na célula E21 da Figura 2). Para bloquear esses pesos, você precisa clicar no botão OK da caixa de diálogo Resultados do Solver mostrada na Figura 4.