Pesquisar este blog

sábado, 6 de outubro de 2012

Função de Estatística: PROJ.LIN - Parte II

Fórmulas e Funções do Excel: PROJ.LIN


A ilustração a seguir mostra a ordem em que os dados estatísticos adicionais são fornecidos.








Comentários
  • Você pode descrever qualquer linha reta com a inclinação e o intercepto de y:
    Inclinação (m):
Para calcular a inclinação de uma linha, freqüentemente representada por m, use dois pontos da linha, (x1,y1) e (x2,y2); a inclinação será igual a (y2 - y1)/(x2 - x1). Intercepto de y (b):


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