Na análise estatística, o variância entre os membros de um conjunto de dados mostra quão distantes os pontos de dados são de uma linha de tendência, também conhecida como linha de regressão. Quanto maior a variação, mais espalhados os pontos de dados são. O estudo da análise de variância mostra quais partes da variância podem ser explicadas pelas características dos dados e quais podem ser atribuídas a fatores aleatórios. A parte da variância que não pode ser explicada é chamada de variância residual.
Usando planilhas do Excel para calcular a variação residual
A fórmula para calcular a variância residual envolve numerosos cálculos complexos. Para pequenos conjuntos de dados, o processo de calcular a variância residual manualmente pode ser entediante. Para grandes conjuntos de dados, a tarefa pode ser exaustiva. Usando uma planilha do Excel, você só precisa inserir os pontos de dados e selecionar a fórmula correta. O programa lida com os cálculos complexos e entrega um resultado rapidamente.
Os pontos de dados
Abra uma nova planilha do Excel e insira os pontos de dados em duas colunas. As linhas de regressão exigem que cada ponto de dados tenha dois elementos. Estatísticos geralmente rotulam esses elementos "X" e "Y". Por exemplo, a Generic Insurance Co. quer encontrar a variação residual da altura e do peso de seus funcionários. A variável X representa a altura e a variável Y representa o peso. Insira as alturas na coluna A e os pesos na coluna B.
Encontrando a média
o significar representa a média de cada elemento no conjunto de dados. Neste exemplo, a Generic Insurance deseja encontrar a média, o desvio padrão e a covariância de 10 alturas e pesos dos funcionários. A média das alturas listadas na Coluna A pode ser encontrada inserindo a função "= AVERAGE (A1: A10)" na célula F1. A média dos pesos listados na Coluna B pode ser encontrada inserindo a função "= AVERAGE (B1: B10)" na célula F3.
Encontrando o desvio padrão e a covariância
o desvio padrão mede a que distância os pontos de dados estão espalhados a partir da média. o covariância mede o quanto os dois elementos do ponto de dados mudam juntos. O desvio padrão das alturas é encontrado inserindo a função "= STDEV (A1: A10)" na célula F2. O desvio padrão dos pesos é encontrado inserindo a função "= STDEV (B1: B10)" na célula F4. A covariância entre as alturas e os pesos é encontrada inserindo a função "= COVAR (A1: A10; B1: B10)" na célula F5.
Encontrando a linha de regressão
o linha de regressão representa uma função linear que segue a tendência dos pontos de dados. A fórmula da linha de regressão é semelhante a: Y = aX + b.
O usuário pode encontrar os valores para "a" e "b" usando os cálculos das médias, desvios-padrão e covariância. O valor para "b" representa o ponto em que a linha de regressão intercepta o eixo Y. O valor pode ser encontrado tomando-se a covariância e dividindo-a pelo quadrado do desvio padrão dos valores-X. A fórmula do Excel vai para a célula F6 e se parece com isto: = F5 / F2 ^ 2.
O valor para "a" representa a inclinação da linha de regressão. A fórmula do Excel vai para a célula F7 e se parece com isto: = F3-F6 * F1.
Para ver a fórmula da linha de regressão, insira essa concatenação de string na célula F8:
= CONCATENAR ("Y ="; ROUND (F6; 2); "X"; SE (SINAL (F7) = 1; "+"; "-"); ABS (ROUND (F7; 2)))
Calcular Y valores
A próxima etapa envolve o cálculo dos valores Y na linha de regressão para os valores X fornecidos no conjunto de dados. A fórmula para encontrar os valores Y vai para a coluna C e se parece com isso:
= $ F $ 6 * A (i) + $ F $ 7
Onde A (i) é o valor da coluna A na linha (i). As fórmulas são assim na planilha:
= $ F $ 6 * A1 + $ F $ 7
= $ F $ 6 * A2 + $ F $ 7
= $ F $ 6 * A3 + $ F $ 7 e assim por diante
As entradas na coluna D mostram as diferenças entre os valores esperado e real de Y. As fórmulas são assim:
= B (i) -C (i), Onde B (i) e C (i) são os valores na Linha (i) nas Colunas B e C, respectivamente.
Encontrando a variância residual
o fórmula para variância residual entra na célula F9 e se parece com isso:
= SUMSQ (D1: D10) / (CONTAGEM (D1: D10) -2)
Onde SUMSQ (D1: D10) é a soma dos quadrados das diferenças entre os valores Y real e esperado e (COUNT (D1: D10) -2) é o número de pontos de dados, menos 2 para graus de liberdade no dados.
