Como encontrar desvios residuais no Excel

Desvios residuais oferecem uma medida da variabilidade dos dados nas linhas de regressão. Essas linhas de regressão ilustram uma relação previsível entre duas variáveis, portanto, conhecer a variabilidade dos dados implica em ter um certo nível de confiança em relação às previsões da linha de regressão. Por exemplo, um desvio residual de 20 significa que as informações chegaram muito perto da linha de regressão, oferecendo previsões confiáveis. Um desvio de 2000 significa que os dados foram misturados; portanto, as previsões dessa linha de regressão são duvidosas. Calcular o desvio residual manualmente é entediante, mas se você criar uma planilha com o Microsoft Excel, será muito mais simples.

Passo 1

Abra o Microsoft Excel.

Etapa 2

Digite a primeira série de dados na coluna A e os outros dados relacionados a eles em B. Os estatísticos costumam usar o “X” para representar o primeiro valor e o “Y” para o segundo, portanto, adotaremos essa convenção para Os objetivos deste artigo.

Etapa 3

Digite as tags “Meios (X)”, “Dev Dev [desvio padrão] (X)”, “Dev (desvio padrão) (Y)”, “Dev Dev [desvio padrão] (Y)”, “Covariância”, “b”, ” a “,” Fórmula linear “e” Desvio residual “nas células E1 a E9.

Etapa 4

Digite “= MÉDIA (A: A)” sem as aspas na célula F1 e “= MÉDIA (B: B)” em F3. Dessa forma, você calcula a média de cada coluna.

Etapa 5

Digite “= DESVPAD.S (A: A)” na célula F2 e “= DESVPAD.S (B: B)” em F4. Dessa forma, você calcula o desvio padrão de cada coluna.

Etapa 6

Digite “= COVARIANCE.S (A: A, B: B)” na célula F5. Dessa maneira, você calcula a covariância entre as duas colunas.

Etapa 7

Digite “= F5 / F2 ^ 2” na célula F6. As linhas de regressão usam a equação “Y = bX + a”. Esta fórmula calcula o valor de “b” nessa equação.

Etapa 8

Digite “= F3-F6 * F1” na célula F7 para calcular o valor “a” da equação da linha de regressão.

Etapa 9

Digite a seguinte fórmula na célula F9: = CONCATENATE (“Y =”, ROUND (F6,2), “X”, SE (SINAL (F7) = 1, “+”, “-“), ABS (ROUND ( F7,2))) Isso não é usado para o cálculo, mas mostra a fórmula específica para a linha de regressão dos seus dados, para a qual o desvio residual mostra a variabilidade dos dados.

Etapa 10

Digite a fórmula “= $ F $ 6 * A1 + $ F $ 7” na célula C1 e copie-a da coluna C para o último valor. Dessa forma, você calcula a previsão do valor de “Y” para cada “X” na linha de regressão. Para copiar essas fórmulas facilmente, clique na célula C1 para selecioná-la. Clique e arraste o canto inferior direito da célula para baixo. Quando você soltar o mouse, as fórmulas serão copiadas automaticamente.

Etapa 11

Digite a fórmula “= B1-C1” na célula D1 e copie-a na coluna D para o último valor. Dessa forma, você calcula a diferença entre os valores esperados e reais de “Y”, que é usado para o cálculo do desvio residual.

Etapa 12

Digite “= SUMSQ (D: D) / (COUNT (D: D) -2)” na célula F9 para calcular o desvio residual.

Referências

 

Você pode estar interessado:

Deixe um comentário