A ilustração a seguir mostra a ordem em que os dados estatísticos
adicionais são fornecidos.
- Você pode descrever qualquer linha reta com a
inclinação e o intercepto de y:
Inclinação (m):
O intercepto de y de uma linha, freqüentemente representado por b,
é o valor de y no ponto em que a linha cruza o eixo y.
A equação de uma linha reta é y = mx + b. Uma vez fornecidos os
valores de m e de b, você poderá calcular qualquer ponto da linha inserindo o
valor de y ou de x nessa equação. Você também pode usar a função TENDÊNCIA.
Para obter mais informações, consulte TENDÊNCIA.
- Quando você tiver apenas uma variável de x
independente, poderá obter os valores de inclinação e de intercepto de y
diretamente, usando as fórmulas a seguir:
Inclinação:
ÍNDICE(PROJ.LIN(val_conhecidos_y;val_conhecidos_x);1)
Intercepto de y:
ÍNDICE(PROJ.LIN(val_conhecidos_y;val_conhecidos_x);2)
- A precisão da linha calculada por PROJ.LIN
dependerá do grau de dispersão dos seus dados. Quanto mais lineares forem
os dados, mais preciso será o modelo de PROJ.LIN. PROJ.LIN usa o método
dos mínimos quadrados para determinar o ajuste perfeito aos seus dados.
Quando você tiver apenas uma variável independente, os cálculos para m e b
serão baseados nas fórmulas a seguir:
- As funções de ajuste de linha e de curva PROJ.LIN e PROJ.LOG podem calcular a linha reta ou a curva exponencial que se ajustem perfeitamente aos seus dados. No entanto, você terá que decidir qual dos dois resultados melhor se adapta aos seus dados. Você pode calcular TENDÊNCIA(val_conhecidos_y;val_conhecidos_x) para uma linha reta, ou CRESCIMENTO(val_conhecidos_y;val_conhecidos_x) para uma curva exponencial. Essas funções, sem o argumento novos_valores_x, retornarão uma matriz dos valores de y estimados ao longo da linha ou da curva, de acordo com seus pontos de dados reais. Então, você poderá comparar os valores previstos com os valores reais. Além disso, é possível representá-los graficamente para uma comparação visual.
- Na análise de regressão, o Microsoft Excel
calcula a diferença de quadrados entre o valor de y estimado e o valor de
y real para cada ponto. A soma dessas diferenças de quadrados é chamada de soma dos quadrados do resíduo.
Então, o Microsoft Excel calcula a soma das diferenças de quadrados entre
os valores reais de y e a média dos valores de y, que é chamada de soma
total de quadrados (soma de quadrados da regressão + soma dos quadrados do
resíduo). Quanto menor a soma dos quadrados do resíduo for comparada com a
soma total de quadrados, maior será o valor do coeficiente de
determinação, r2, que indica a precisão com que a equação resultante da
análise de regressão descreve a relação entre as variáveis.
- As fórmulas que retornam matrizes devem ser inseridas
como fórmulas matriciais.
- Ao inserir a constante de uma matriz como
valores_conhecidos_x em forma de argumento, use pontos para separar
valores na mesma linha e pontos-e-vírgulas para separar linhas. Os
caracteres de separação podem ser diferentes dependendo das definições do
país.
- Você deve observar que os valores de y estimados
pela equação de regressão podem não ser válidos se estiverem fora do
intervalo de valores de y usado para determinar a equação.
Exemplo 1 Inclinação e Intercepto de Y
PROJ.LIN({1.9.5.7}.{0.4.2.3})
é igual a {2.1}, a inclinação = 2 e o intercepto de y
= 1.
Exemplo 2 Regressão Linear Simples
Considere uma pequena empresa com vendas de $3.100, $4.500,
$4.400, $5.400, $7.500, e $8.100 nos últimos seis meses do ano fiscal. Supondo
que os valores tenham sido inseridos no intervalo B2:B7, respectivamente, você
poderá usar o seguinte modelo de regressão linear simples para obter uma
estimativa de vendas para o nono mês.
SOMA(PROJ.LIN(B2:B7)*{9.1})
é igual a SOMA({1000.2000}*{9.1})
é igual a $11.000
Geralmente, SOMA({m.b}*{x.1}) é igual a mx + b, o valor de y
estimado para um determinado valor de x.
Você também pode usar a função TENDÊNCIA.
Exemplo 3 Regressão Linear Múltipla
Suponha que um empresário esteja pensando em comprar um grupo de
prédios de salas comerciais em um bairro comercial.
O empresário pode usar a análise de regressão linear múltipla para
fazer uma estimativa do valor de um prédio em uma determinada área, de acordo
com as variáveis a seguir.
Variável | Refere-se a |
---|---|
y | Valor estimado do prédio |
x1 | Área útil em metros quadrados |
x2 | Número de salas |
x3 | Número de entradas |
x4 | Idade do prédio em anos |
Este exemplo considera que existe uma relação de linha reta entre
cada uma das variáveis independentes (x1, x2, x3, e x4) e a variável dependente
(y), o valor dos prédios comerciais no bairro.
O empresário escolhe aleatoriamente uma amostra de 11 prédios a
partir de um conjunto de 1500 prédios possíveis e obtém os seguintes dados.
"Meia entrada" significa que o prédio só dispõe de uma entrada para
entregas. Quando for inserido como uma matriz, a fórmula a seguir:
LINEST(E2:E12,A2:D12,TRUE,TRUE)
retornará o seguinte resultado.
A equação de regressão múltipla, y = m1*x1 + m2*x2 + m3*x3 - m4*x4 +
b, pode ser obtida usando os valores da linha 14:
y = 27,64*x1 + 12.530*x2 + 2.553*x3 - 234,24*x4 + 52.318
Agora, o empresário poderá fazer uma estimativa do valor para um
prédio na mesma área com 272 metros quadrados, três salas e duas entradas, e que
tem 25 anos de idade, usando a seguinte equação:
y = 27,64*272 + 12.530*3 + 2.553*2 - 234,24*25 + 52.318 =
$158.261
Você também pode usar a função TENDÊNCIA para calcular este valor.
Para obter mais informações, consulte TENDÊNCIA.
Exemplo 4 Usando os Dados Estatísticos F e R2
No exemplo anterior, o coeficiente de determinação, ou r2, é 0,99675
(consulte a célula A16 no resultado para PROJ.LIN), o que indica uma forte
relação entre as variáveis independentes e o preço de venda. Você pode usar a
estatística F para determinar se esses resultados, com um valor de r2 tão alto,
ocorreram por acaso.
Suponha, por agora, que na verdade não há relação entre as variáveis,
mas que você selecionou uma amostra rara de 11 prédios que fará com que a
análise estatística demonstre uma forte relação. O termo "Alfa" é usado para
indicar a probabilidade de se concluir erroneamente que existe uma relação.
Há uma relação entre as variáveis se o valor de F observado for maior
que o valor de F crítico. O valor de F crítico pode ser obtido através de uma
tabela de valores de F críticos existente em diversos livros de estatística.
Para ler a tabela, considere um teste uni-caudal, use um valor Alfa igual a
0,05, e para graus de liberdade (abreviado na maioria das tabelas como v1 e v2),
use v1 = k = 4 e v2 = n - (k + 1) = 11 - (4 + 1) = 6, onde k é o número de
variáveis na análise de regressão e n é o número de pontos de dados. O valor de
F crítico é 4,53.
O valor de F observado é 459,753674 (célula A17), que é
substancialmente maior que o valor de F crítico de 4,53. Dessa maneira, a
equação de regressão será útil para prever o valor estimado para os prédios
dessa área.
Exemplo 5 Calculando o Dado Estatístico T
Outro teste hipotético pode determinar se um coeficiente de
inclinação é útil para prever o valor estimado de um prédio no exemplo 3. Por
exemplo, para testar o coeficiente de idade para significância estatística,
divida -234,24 (coeficiente de idade da inclinação) por 13,268 (o valor de erro
estimado para os coeficientes de idade na célula A15). A equação a seguir
representa o valor de t observado:
t = m4 ÷ se4 = -234,24 ÷ 13,268 = -17,7
Se você consultar uma tabela num manual de estatística, descobrirá
que o valor crítico de t, uni-caudal, com 6 graus de liberdade e Alfa = 0,05 é
1,94 . Na medida em que o valor absoluto de t, 17,7, é maior que 1,94, a idade
será uma variável importante para prever o valor estimado de um prédio. Cada uma
das outras variáveis independentes pode ser testada para significância
estatística de maneira semelhante. Na tabela a seguir, encontram-se os valores
de t observados para cada variável independente:
Variável | valor de t observado |
---|---|
Área útil | 5,1 |
Número de salas | 31,3 |
Número de entradas | 4,8 |
Idade | 17,7 |
Todos esses valores apresentam um valor absoluto maior que 1,94;
dessa forma, todas as variáveis usadas na equação de regressão serão úteis para
prever o valor estimado dos prédios dessa área.
Até a Próxima!!
Nenhum comentário:
Postar um comentário