PROJ.LIN (função PROJ.LIN)

Este artigo descreve a sintaxe da fórmula e a utilização da função PROJ.LIN no Microsoft Excel. Poderá encontrar mais informações sobre como criar gráficos e efetuar uma análise de regressão através das ligações na secção Consulte Também.

Descrição

A função PROJ.LIN calcula os dados estatísticos de uma linha utilizando o método dos "quadrados mínimos", de modo a calcular uma reta que se adapte melhor aos dados e, em seguida, devolve uma matriz que descreve a linha. Também pode combinar a função PROJ.LIN com outras funções para calcular a estatística de outros tipos de modelos que sejam lineares nos parâmetros desconhecidos, incluindo polinomial, logarítmica, exponencial e série de potência. Como esta função devolve uma matriz de valores, tem de ser introduzida como uma fórmula de matriz. As instruções seguem os exemplos apresentados neste artigo.

A equação para a linha é:

y = mx + b

–ou–

y = m1x1 + m2x2 + ... + b

se existirem vários intervalos de valores de x em que os valores de y dependentes são uma função dos valores de x independentes. Os valores de m são coeficientes que correspondem a cada um dos valores de x e b é um valor constante. Deve ter-se em consideração que y, x e m podem ser vetores. A matriz devolvida pela função PROJ.LIN é {mn;mn-1;...;m1;b}. PROJ.LIN também pode devolver a estatística de regressão adicional.

Sintaxe

PROJ.LIN(val_conhecidos_y, [val_conhecidos_x], [constante], [estatística])

A sintaxe da função PROJ.LIN tem os seguintes argumentos:

Sintaxe

  • val_conhecidos_y    obrigatório. O conjunto de valores que já conhece na relação y = mx + b.

    • Se o intervalo de val_conhecidos_y estiver numa única coluna, cada coluna de val_conhecidos_x será interpretada como uma variável separada.

    • Se o intervalo de val_conhecidos_y estiver contido numa única linha, cada linha de val_conhecidos_x será interpretada como uma variável separada.

  • val_conhecidos_x    opcional. Um conjunto de valores de x que já deve conhecer na relação y = mx + b.

    • O intervalo de val_conhecidos_x pode incluir um ou mais conjuntos de variáveis. Se for utilizada apenas uma variável, val_conhecidos_y e val_conhecidos_x podem ser intervalos de qualquer formato, desde que tenham dimensões iguais. Se for utilizada mais de uma variável, val_conhecidos_y tem de ser um vetor (ou seja, um intervalo com a altura de uma linha ou a largura de uma coluna).

    • Se val_conhecidos_x for omitido, é considerada a matriz {1;2;3;...}, que é do mesmo tamanho que val_conhecidos_y.

  • constante    opcional. Um valor lógico que determina se deve forçar-se a constante b a ser igual a 0.

    • Se constante for VERDADEIRO ou omitida, b é calculado normalmente.

    • Se constante for FALSO, b é definido como igual a 0 e os valores de m são ajustados de forma a que y = mx.

  • estatística    opcional. Um valor lógico que especifica se as estatísticas de regressão adicionais são devolvidas.

    • Se estatística for VERDADEIRO, PROJ.LIN devolve a estatística de regressão adicional; consequentemente, a matriz devolvida é {mn;mn-1;...;m1;b;sen;sen-1;...;se1;seb;r2;sey;F;df;ssreg;ssresid}.

    • Se estatística for FALSO ou omitida, PROJ.LIN só devolve os coeficientes m e a constante b.

      As estatísticas de regressão adicionais são as seguintes.

Estatística

Descrição

se1;se2;...;sen

Os valores de erro-padrão para os coeficientes m1,m2,...,mn.

seb

O valor de erro-padrão para a constante b (seb = #N/D quando constante for FALSO).

r2

O coeficiente de determinação. Compara os valores previstos e reais de y e os intervalos de 0 a 1. Se for igual a 1, existe uma correlação perfeita no exemplo — não existem diferenças entre o valor previsto de y e o valor real de y. Por outro lado, se o coeficiente de determinação for igual a 0, a equação de regressão não é útil para a previsão de um valor de y. Para obter mais informações sobre a forma como é calculado r2, consulte "Observações", posteriormente neste tópico.

sey

O erro padrão para a estimativa de y.

F

A estatística F ou o valor de F observado. Utilize a estatística F para determinar se a relação observada entre as variáveis dependentes e independentes ocorre por acaso.

df

Os graus de liberdade. Utilize os graus de liberdade para localizar os valores críticos de F numa tabela estatística. Compare os valores que encontrar na tabela com a estatística F devolvida por PROJ.LIN, de modo a determinar um nível de confiança para o modelo. Para obter informações sobre como df é calculado, consulte "Observações" mais à frente neste tópico. O Exemplo 4 mostra a utilização de F e df.

ssreg

A soma da regressão dos quadrados.

ssresid

A soma residual dos quadrados. Para obter informações sobre como ssreg e ssresid são calculados, consulte "Observações", posteriormente neste tópico .

A seguinte ilustração mostra a ordem pela qual as estatísticas de regressão adicionais são devolvidas.

O grupo Criar Gráfico no separador Estrutura em Ferramentas de SmartArt

Observações

  • Pode descrever qualquer linha reta com o declive e a interceção de y:

    Declive (m):
    Para encontrar o declive de uma linha, frequentemente representado por m, utilize dois pontos da linha, (x1,y1) e (x2,y2); o declive é igual a (y2 - y1)/(x2 - x1).

    Interceção de y (b):
    A interceção de y de uma linha, frequentemente representada por b, é o valor de y no ponto em que a linha atravessa o eixo dos YY.

    A equação para uma linha reta é y = mx + b. Quando conhecer os valores de m e b, pode calcular qualquer ponto da linha, ligando o valor de y ou x nessa equação. Também pode utilizar a função TENDÊNCIA.

  • Quando tiver apenas uma variável de x independente, pode obter diretamente os valores de declive e de interceção de y, utilizando as seguintes fórmulas:

    Declive:
    =ÍNDICE(PROJ.LIN(val_conhecidos_y;val_conhecidos_x);1)

    Interceção de Y:
    =ÍNDICE(PROJ.LIN(val_conhecidos_y;val_conhecidos_x);2)

  • A precisão da linha calculada pela função PROJ.LIN depende do grau de dispersão dos dados. Quanto mais lineares forem os dados, mais precisão tem o modelo PROJ.LIN. PROJ.LIN utiliza o método dos quadrados mínimos para determinar o melhor modo de ajustar os dados. Quando existir apenas uma variável de x independente, os cálculos para m e b baseiam-se nas seguintes fórmulas:

    Equação

    Equação

    em que x e y são médias da amostra, ou seja, x = MÉDIA(val_conhecidos_x) e y = MÉDIA(val_conhecidos_y).

  • 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 ajuste melhor aos dados. No entanto, tem de decidir qual dos dois resultados se adapta melhor aos dados. 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. Estas funções, sem o argumento novos_valores_x devolvem uma matriz dos valores de y previstos ao longo dessa linha ou curva, de acordo com os pontos de dados reais. Pode então comparar os valores previstos com os valores reais. Pode representá-los graficamente para fazer uma comparação visual.

  • Na análise de regressão, o Excel calcula para cada um dos pontos a diferença quadrática entre o valor de y previsto para esse ponto e o valor de y real. A soma dessas diferenças quadráticas é denominada a soma residual dos quadrados, ssresid. O Excel calcula, em seguida, a soma total dos quadrados, sstotal. Quando o argumento constante = VERDADEIRO ou é omitido, a soma total dos quadrados é a soma das diferenças quadráticas entre os valores de y reais e a média dos valores de y. Quando o argumento constante = FALSO, a soma total dos quadrados é a soma dos quadrados dos valores de y reais (sem subtrair a média dos valores de y de cada valor de y individual). Então, é possível encontrar a soma de regressão dos quadrados, ssreg, através de: ssreg = sstotal - ssresid. Quanto menor for a soma residual dos quadrados, comparada com a soma total de quadrados, maior é o valor do coeficiente de determinação, r2, que é um indicador da precisão com que a equação resultante da análise de regressão descreve a relação entre as variáveis. O valor de r2 é igual a ssreg/sstotal.

  • Em alguns casos, uma ou mais colunas X (assumindo que os valores Y e os valores X estão em colunas) podem não ter valor previsível adicional na presença das outras colunas X. Ou seja, a eliminação de uma ou mais colunas X poderá conduzir a valores Y previstos que são igualmente precisos. Nesse caso, estas colunas X redundantes devem ser omitidas do modelo de regressão. Este fenómeno é denominado “colinearidade” porque qualquer coluna X redundante pode ser expressa como uma soma de múltiplos de colunas X não redundantes. A função PROJ.LIN verifica a colinearidade e remove todas as colunas X redundantes do modelo de regressão quando as identifica. As colunas X removidas podem ser reconhecidas no resultado da função PROJ.LIN por terem coeficientes 0, bem como 0 valores se. Se uma ou mais colunas forem removidas como redundantes, df é afetado porque df depende do número de colunas X efetivamente utilizadas para a previsão. Para obter detalhes sobre o cálculo de df, consulte o Exemplo 4. Se df for alterado porque as colunas X redundantes são removidas, os valores de sey e F são também afetados. Na prática, a colinearidade deverá ser relativamente rara. No entanto, existe uma situação em que é provável a sua ocorrência, ou seja, quando algumas colunas X contêm apenas valores 0 e valores 1 como indicadores que determinam se um sujeito numa experiência é ou não um membro de um determinado grupo. Se constante = VERDADEIRO ou for omitido, a função PROJ.LIN insere, na realidade, uma coluna X adicional de todos os valores 1 para modelar a interseção. Se tiver uma coluna com um 1 para cada sujeito no caso de ser masculino ou 0 no caso de não ser e, ainda, se tiver uma coluna com um 1 para cada sujeito se for feminino ou 0 se não for, esta última coluna é redundante porque as entradas nela contidas podem ser obtidas subtraindo a entrada na coluna do “indicador masculino” pela entrada na coluna adicional de todos os valores 1 adicionados pela função PROJ.LIN.

  • O valor de df é calculado da seguinte forma quando não são removidas do modelo colunas X devido à colinearidade: se existirem colunas k de val_conhecidos_x e constante = VERDADEIRO ou omitido, df = n – k – 1. Se constante = FALSO, df = n - k. Em ambos os casos, cada coluna X removida devido à colinearidade aumenta o valor de df em 1.

  • As fórmulas que devolvem matrizes têm de ser introduzidas como fórmulas de matriz.

    Nota:  No Excel Online não é possível criar fórmulas de matriz.

  • Ao introduzir uma constante de matriz (como val_conhecidos_x) como um argumento, utilize vírgulas para separar os valores contidos na mesma linha e pontos e vírgulas para separar linhas. Os carateres de separação podem ser diferentes consoante a definição regional.

  • Repare que os valores de y previstos pela equação de regressão podem não ser válidos se estiverem fora do intervalo de valores de y que utilizou para determinar a equação.

  • O algoritmo subjacente utilizado na função PROJ.LIN é diferente do algoritmo subjacente utilizado nas funções DECLIVE e INTERCETAR. A diferença entre estes algoritmos pode causar resultados diferentes quando os dados forem indeterminados e colineares. Por exemplo, se os pontos de dados do argumento val_conhecidos_y forem 0 e os pontos de dados do argumento val_conhecidos_x forem 1:

    • PROJ.LIN devolve um valor de 0. O algoritmo da função PROJ.LIN foi concebido para devolver resultados razoáveis para dados colineares e, neste caso, pode ser encontrada pelo menos uma resposta.

    • DECLIVE e INTERCETAR devolvem um erro #DIV/0!. O algoritmo das funções DECLIVE e INTERCETAR foram concebidos para procurar apenas uma resposta e, neste caso, pode existir mais do que uma resposta.

  • Para além de utilizar a função PROJ.LOG para calcular a estatística de outros tipos de regressão, pode utilizar a função PROJ.LIN para calcular um intervalo de outros tipos de regressão introduzindo funções das variáveis x e y como as séries x e y da função PROJ.LIN. Por exemplo, a seguinte fórmula:

    =PROJ.LIN(valoresy; valoresx^COL($A:$C))

    funcionará quando tiver uma única coluna de valores de y e uma única coluna de valores de x para calcular a aproximação cúbica (polinomial de ordem 3) da forma:

    y = m1*x + m2*x^2 + m3*x^3 + b

    Pode ajustar esta fórmula para calcular outros tipos de regressão, no entanto, em alguns casos, é necessário ajustar os valores de saída e outras estatísticas.

  • O valor de teste F que é devolvido pela função PROJ.LIN é diferente do valor de teste F que é devolvido pela Função TESTEF. PROJ.LIN devolve a estatística F, enquanto que TESTEF devolve a probabilidade.

Exemplos

Exemplo 1 - Declive e interceção de Y

Copie os dados de exemplo que se encontram na seguinte tabela e cole-os na célula A1 de uma nova folha de cálculo do Excel. Para que as fórmulas mostrem os resultados, selecione-as, prima F2 e, em seguida, prima Enter. Caso seja necessário, pode ajustar a largura das colunas para ver todos os dados.

Val_conhecidos_y

Val_conhecidos_x

1

0

9

4

5

2

7

3

Resultado (declive)

Resultado (interceção de y)

2

1

Fórmula (fórmula de matriz nas células A7:B7)

=PROJ.LIN(A2:A5;B2:B5;;FALSO)

Exemplo 2 - Regressão Linear Simples

Copie os dados de exemplo que se encontram na seguinte tabela e cole-os na célula A1 de uma nova folha de cálculo do Excel. Para que as fórmulas mostrem os resultados, selecione-as, prima F2 e, em seguida, prima Enter. Caso seja necessário, pode ajustar a largura das colunas para ver todos os dados.

Mês

Vendas

1

3.100 €

2

4.500 €

3

4.400 €

4

5.400 €

5

7.500 €

6

8.100 €

Fórmula

Resultado

=SOMA(PROJ.LIN(B1:B6; A1:A6)*{9;1})

11.000 €

Calcula a estimativa das vendas no nono mês, com base nas vendas dos meses 1 a 6.

Exemplo 3 - Regressão linear múltipla

Copie os dados de exemplo que se encontram na seguinte tabela e cole-os na célula A1 de uma nova folha de cálculo do Excel. Para que as fórmulas mostrem os resultados, selecione-as, prima F2 e, em seguida, prima Enter. Caso seja necessário, pode ajustar a largura das colunas para ver todos os dados.

Área útil (x1)

Escritórios (x2)

Entradas (x3)

Idade (x4)

Valor estimado (y)

2310

2

2

20

142.000 €

2333

2

2

12

144.000 €

2356

3

1,5

33

151.000 €

2379

3

2

43

150.000 €

2402

2

3

53

139.000 €

2425

4

2

23

169.000 €

2448

2

1,5

99

126.000 €

2471

2

2

34

142.900 €

2494

3

3

23

163.000 €

2517

4

4

55

169.000 €

2540

2

3

22

149.000 €

-234,2371645

13,26801148

0,996747993

459,7536742

1732393319

Fórmula (fórmula de matriz introduzida em A14:A18)

=PROJ.LIN(E2:E12;A2:D12;VERDADEIRO;VERDADEIRO)

Exemplo 4 - Utilizar as estatísticas F e r2

No exemplo anterior, o coeficiente de determinação, ou r2, é de 0,99675 (consulte a célula A17 no resultado para PROJ.LIN), o que indica uma forte relação entre as variáveis independentes e o preço de venda. Pode utilizar a estatística F para determinar se estes resultados, com um valor de r2 tão alto, surgem por acaso.

Suponha, por agora, que na verdade não existe relação entre as variáveis, mas que 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" é utilizado para indicar a probabilidade de se concluir erroneamente que existe uma relação.

Os valores F e df no resultado da função PROJ.LIN podem ser utilizados para avaliar a probabilidade de ocorrer por acaso um valor F mais elevado. F pode ser comparado com com valores críticos nas tabelas de distribuição F publicadas ou pode ser utilizada a função DISTF do Excel para calcular a probabilidade de ocorrer por acaso um valor F mais elevado. A distribuição F apropriada tem os graus de liberdade v1 e v2. Se n for o número de pontos de dados e constante = VERDADEIRO ou omitido, v1 = n – df – 1 e v2 = df. (Se constante = FALSO, v1 = n – df e v2 = df.) A função DISTF — com a sintaxe DISTF(F,v1,v2) — devolverá a probabilidade de ocorrer por acaso um valor F mais elevado. Neste exemplo, df = 6 (célula B18) e F = 459,753674 (célula A18).

Admitindo um valor Alfa de 0,05, v1 = 11 – 6 – 1 = 4 e v2 = 6, o nível crítico de F é 4,53. Uma vez que F = 459,753674 é muito maior que 4,53, é extremamente improvável que tenha ocorrido por acaso um valor F tão elevado . (Com Alfa = 0,05, a hipótese de que não existe nenhuma relação entre val_conhecidos_y e val_conhecidos_x é rejeitada quando F excede o nível crítico, 4,53.) É possível obter a probabilidade de um valor F tão elevado ocorrer por acaso utilizando a função DISTF do Excel. Por exemplo, DISTF(459,753674, 4, 6) = 1,37E-7, uma probabilidade extremamente pequena. É possível concluir, tanto localizando o nível crítico de F numa tabela como utilizando a função DISTF do Excel, que a equação de regressão é útil na previsão do valor estimado para os prédios de escritórios dessa área. Não se esqueça de que é fundamental que utilize valores corretos de v1 e v2, calculados no parágrafo anterior.

Exemplo 5 - Calcular as estatísticas t

Outro teste hipotético pode determinar se cada coeficiente de declive é útil para prever o valor estimado de um prédio no Exemplo 3. Por exemplo, para testar o coeficiente de idade para a significância estatística, divida -234,24 (coeficiente do declive de idade) por 13,268 (o valor de erro-padrão previsto 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 o valor absoluto de t for suficientemente alto, é possível concluir que o coeficiente de declive é útil na estimativa do valor estimado de um prédio no Exemplo 3. A tabela seguinte mostra os valores absolutos dos valores 4 t observados.

Se consultar uma tabela num manual de estatística, verificará que o valor crítico de t, bicaudal, com 6 graus de liberdade e Alfa = 0,05, é de 2,447. Este valor crítico também pode ser encontrado utilizando a função INVT do Excel. INVT(0.05,6) = 2,447. Uma vez que o valor absoluto de t (17,7) é maior que 2,447, a idade é uma variável importante para prever o valor estimado de um prédio. Cada uma das outras variáveis independentes pode ser testada relativamente à significância estatística de maneira semelhante. Os valores que se seguem são os valores de t observados para cada uma das variáveis independentes.

Variável

Valor de t observado

Área útil

5,1

Número de escritórios

31,3

Número de entradas

4,8

Idade

17,7

Todos estes valores têm um valor absoluto superior a 2,447, pelo que todas as variáveis utilizadas na equação de regressão são úteis para prever o valor estimado dos prédios nessa área.

Aumente os seus conhecimentos do Office
Explore as formações
Seja o primeiro a obter novas funcionalidades
Adira ao Office Insider

As informações foram úteis?

Obrigado pelos seus comentários!

Obrigado pelo seu feedback! Parece que poderá ser benéfico reencaminhá-lo para um dos nossos agentes de suporte do Office.

×