Função PROJ.LIN

Este artigo descreve a sintaxe da fórmula e o uso da função (função: um fórmula pré-desenvolvida que assume um valor ou vários valores, executa uma operação e retorna um valor ou vários valores. Use as funções para simplificar e reduzir fórmulas em uma planilha, especialmente aquelas que executam cálculos longos e complexos.) PROJ.LIN no Microsoft Office Excel. Encontre links para informações adicionais sobre como criar gráficos e realizar uma análise de regressão na seção Consulte Também.

Descrição

A função PROJ.LIN calcula as estatísticas para uma linha usando o método "quadrados mínimos" para calcular uma linha reta que melhor se ajusta aos seus dados e, em seguida, retorna uma matriz que descreve essa linha. Você também pode combinar a função PROJ.LIN com outras funções para calcular as estatísticas de outros tipos de modelos que são lineares nos parâmetros desconhecidos, incluindo séries polinomiais, logarítmicas, exponenciais e de potência. Como essa função retorna uma matriz de valores, ela deve ser inserida como uma fórmula de matriz. Instruções acompanham os exemplos neste artigo.

A equação para a linha é:

y = mx + b

–ou–

y = m1x1 + m2x2 + ... + b (se existirem múltiplos intervalos de valores de x)

em que os valores y dependentes são uma função dos valores x independentes. Os valores m são coeficientes que correspondem a cada valor x e b é um valor constante. Observe que y, x e m podem ser vetores. A matriz retornada pela função PROJ.LIN é {mn,mn-1,...,m1,b}. PROJ.LIN também pode retornar estatísticas de regressão adicionais.

Sintaxe

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

A sintaxe da função PROJ.LIN tem os seguintes argumentos (argumento: um valor que fornece informações a uma ação, um evento, um método, uma função ou um procedimento.):

  • val_conhecidos_y    Necessário. O conjunto de valores y que você já conhece na relação y = mx + b.
    • Se o intervalo de val_conhecidos_y estiver em uma única coluna, cada coluna de val_conhecidos_x será interpretada como uma variável separada.
    • Se o intervalo de val_conhecidos_y estiver contido em uma única linha, cada linha de val_conhecidos_x será interpretada como uma variável separada.
  • val_conhecidos_x    Opcional. Um conjunto opcional de valores x que talvez você já conheça na relação y = mx + b.
    • O intervalo de val_conhecidos_x pode incluir um ou mais conjuntos de variáveis. Se apenas uma variável for usada, val_conhecidos_y e val_conhecidos_x poderão ser intervalos de qualquer forma, desde que tenham dimensões iguais. Se mais de uma variável for usada, val_conhecidos_y deverá ser um vetor (ou seja, um intervalo com altura de uma linha ou largura de uma coluna).
    • Se val_conhecidos_x for omitido, pressupõe-se que ele seja a matriz {1,2,3,...} com o mesmo tamanho que val_conhecidos_y.
  • constante    Opcional. Um valor lógico que especifica se a constante b será ou não forçada a se igualar a 0.
    • Se constante for VERDADEIRO ou for omitido, b será calculado normalmente.
    • Se constante for FALSO, b será definido como igual a 0 e os valores m serão ajustados para se adaptarem a y = mx.
  • estatísticas    Opcional. É um valor lógico que especifica se estatísticas de regressão adicionais serão retornadas.
    • Se estatísticas for VERDADEIRO, PROJ.LIN retornará as estatísticas de regressão adicionais; como resultado, a matriz retornada será {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}.
    • Se estatísticas for FALSO ou estiver omitido, PROJ.LIN retornará somente os coeficientes m e a constante b.

Os dados estatísticos de regressão adicionais são:

Dados estatísticos Descrição
se1.se2.....sem 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 é FALSO).
r2 O coeficiente de determinação. Compara os valores y estimados e reais e os intervalos no valor de 0 a 1. Se for 1, existe uma correlação perfeita no exemplo — não há diferente entre o valor y estimado e o valor y real. No outro extremo, se o coeficiente de determinação for 0, a equação de regressão não será útil na previsão de um valor y. Para obter informações sobre como r2 é calculado, consulte "Comentários", mais adiante neste tópico.
Sey O valor de erro para a estimativa de y.
F A estatística F, ou o valor de F observado. Use 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. Use os graus de liberdade para ajudar a encontrar os valores F críticos em uma tabela estatística. Compare os valores encontrados na tabela com a estatística F retornada 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 "Comentários", mais adiante neste tópico. O Exemplo 4 mostra o uso de F e df.
Ssreg A soma dos quadrados da regressão.
Ssresid A soma residual dos quadrados. Para obter informações sobre como ssreg e ssresid são calculados, consulte "Comentários" mais adiante neste tópico.

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

Planilha

Comentários

  • Você pode descrever qualquer linha reta com a inclinação e o ponto de origem 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ê pode calcular qualquer ponto na linha inserindo o valor de y ou de x nessa equação. Você também pode usar a função 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)

Ponto de origem y:
ÍNDICE(PROJ.LIN(val_conhecidos_y;val_conhecidos_x);2)

  • A precisão da linha calculada pela função 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:

Equação

Equação

onde x e y são exemplos de média, 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 ajustam 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. Dessa forma, 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 Excel calcula a diferença de quadrados entre o valor y estimado e o valor y real para cada ponto. A soma dessas diferenças de quadrados é chamada de soma dos quadrados de resíduo, ssresid. O Excel calcula então a soma total dos quadrados. Quando o argumento constante for VERDADEIRO ou for omitido, a soma total dos quadrados será a soma das diferenças dos quadrados entre os valores y reais e a média dos valores y. Quando o argumento constante for FALSO, a soma total dos quadrados será a soma de quadrados dos próprios valores de y (sem subtrair a média dos valores de y de cada valor de y individual). A soma da regressão dos quadrados, ssreg, pode ser obtida de: ssreg = sstotal - ssresid. Quanto menor for a soma de quadrados de resíduo, comparada com a soma de quadrados total, 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. O valor de r2 é igual a ssreg/sstotal.
  • Em alguns casos, uma ou mais colunas de X (supondo que os Ys e Xs estejam em colunas) podem ter valores previsíveis adicionais na presença de outras colunas de X. Em outras palavras, se forem eliminadas uma ou mais colunas de X, poderemos chegar a valores previsíveis de Y com a mesma precisão. Nesse caso, as colunas de X redundantes devem ser omitidas no modelo de regressão. Esse fenômeno é chamado de “colinearidade” porque qualquer coluna de X redundante pode ser expressa como uma soma dos múltiplos das colunas de X não-redundantes. A função PROJ.LIN verifica a colinearidade e remove as colunas de X redundantes do modelo de regressão quando as identifica. As colunas de X removidas podem ser reconhecidas no resultado de PROJ.LIN como tendo 0 coeficiente além de valores 0 se. Se uma ou mais colunas forem removidas como redundantes, df será afetada porque depende do número de colunas de X realmente usadas para fins previsíveis. Para obter mais detalhes sobre o cálculo de df, consulte o Exemplo 4. Se df for alterada porque as colunas de X redundantes foram removidas, os valores de sey e F também serão afetados. Na prática, a colinearidade é relativamente rara. Contudo, um caso em que sua ocorrência será mais provável é quando algumas colunas de X contiverem somente valores 0 e 1 indicando se um dado é um experimento ou se não faz parte de um determinado grupo. Se constante for VERDADEIRO ou se for omitido, a função PROJ.LIN irá inserir uma coluna de X adicional de valores 1 para modelar a interceptação. Se você tiver uma coluna com um 1 para cada dado se masculino, ou 0 se não for, você também terá uma coluna com um 1 para cada dado se for feminino e 0 se não for. Essa segunda coluna será redundante porque suas informações poderão ser obtidas pela subtração da entrada da coluna “indicador de masculino” da entrada da coluna adicional de valores 1 adicionada pela função PROJ.LIN.
  • O valor de df é calculado da seguinte maneira, quando nenhuma coluna de X é removida do modelo devido à colinearidade: se houver k colunas de val_conhecidos_x e constante for VERDADEIRO ou estiver omitido, então df = n – k – 1. Se constante for FALSO, então df = n - k. Em ambos os casos, cada coluna de X removida devido à colinearidade aumentará df em 1.
  • As fórmulas que retornam matrizes devem ser inseridas como fórmulas de matriz.
  • Ao inserir a constante de uma matriz (como val_conhecidos_x) como um argumento, use pontos para separar valores contidos na mesma linha e pontos-e-vírgulas para separar linhas. Os caracteres de separação podem ser diferentes dependendo das configurações de localidade em Opções Regionais e de Idiomas no Painel de Controle.
  • 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.
  • O algoritmo subjacente usado na função PROJ.LIN é diferente do algoritmo subjacente usado nas funções INTERCEPÇÃO e INCLINAÇÃO. A diferença entre esses algoritmos pode levar a diferentes resultados 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 retornará um valor de 0. O algoritmo da função PROJ.LIN foi desenvolvido para retornar resultados razoáveis para dados colineares e, nesse caso, pelo menos uma resposta será encontrada.
    • INCLINAÇÃO e INTERCEPÇÃO retornarão um erro #DIV/0!. O algoritmo das funções INCLINAÇÃO e INTERCEPÇÃO foi desenvolvido para procurar somente uma resposta e, nesse caso, pode haver mais de uma resposta.
  • Além de usar PROJ.LOG para calcular estatísticas para outros tipos de regressão, você deve usar PROJ.LIN para calcular o intervalo de outros tipos de regressão ao inserir funções de variáveis x e y como séries x e y de PROJ.LIN. Por exemplo, a fórmula a seguir:

=PROJ.LIN(valores y, valores x^COLUNA($A:$C))

funciona quando você tem uma única coluna de valores y e uma única coluna de valores x para calcular a aproximação cúbica (polinomial de ordem 3) do formato:

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

Você pode ajustar essa fórmula para calcular outros tipos de regressão, mas em alguns casos é necessário o ajuste dos valores de saída e outras estatísticas.

Exemplo 1

Inclinação e Intercepção de Y

Talvez seja mais fácil entender o exemplo se você copiá-lo em uma planilha em branco.

MostrarComo copiar um exemplo?

  1. Selecione o exemplo deste artigo. Se estiver copiando o exemplo no Excel Web App, copie e cole uma célula de cada vez.

 Importante   Não selecione os cabeçalhos de linha ou de coluna.

Selecionando um exemplo na Ajuda

Selecionando um exemplo na Ajuda
  1. Pressione CTRL+C.
  2. Crie uma pasta de trabalho ou planilha em branco.
  3. Na planilha, selecione a célula A1 e pressione CTRL+V. Se estiver trabalhando no Excel Web App, repita a ação de copiar e colar para cada célula do exemplo.

 Importante   Para que o exemplo funcione corretamente, é preciso colá-lo na célula A1 da planilha.

  1. Para alternar entre a exibição de resultados e a exibição das fórmulas que retornam os resultados, pressione CTRL+` (acento grave) ou, na guia Fórmulas, no grupo Auditoria de Fórmulas, clique no botão Mostrar Fórmulas.

Depois de copiar o exemplo em uma planilha em branco, você poderá adaptá-lo às suas necessidades.


 
1
2
3
4
5
6
7
A B C
 y conhecido  x conhecido
1 0
9 4
5 2
7 3
Fórmula Fórmula Resultado
=PROJ.LIN(A2:A5;B2:B5;FALSO) A7=2, B7=1

 Importante   A fórmula no exemplo deve ser inserida como fórmula de matriz. Após copiar o exemplo para uma planilha em branco, selecione o intervalo A7:B7 iniciando com a célula de fórmula. Pressione F2 e, em seguida, pressione CTRL+SHIFT+ENTER. Se a fórmula não for inserida como uma fórmula de matriz, o único resultado será 2.

Quando inserida como uma matriz, a inclinação (2) e o ponto de origem y (1) são retornados.

Exemplo 2

Regressão Linear Simples

Talvez seja mais fácil entender o exemplo se você copiá-lo em uma planilha em branco.

MostrarComo copiar um exemplo?

  1. Selecione o exemplo deste artigo. Se estiver copiando o exemplo no Excel Web App, copie e cole uma célula de cada vez.

 Importante   Não selecione os cabeçalhos de linha ou de coluna.

Selecionando um exemplo na Ajuda

Selecionando um exemplo na Ajuda
  1. Pressione CTRL+C.
  2. Crie uma pasta de trabalho ou planilha em branco.
  3. Na planilha, selecione a célula A1 e pressione CTRL+V. Se estiver trabalhando no Excel Web App, repita a ação de copiar e colar para cada célula do exemplo.

 Importante   Para que o exemplo funcione corretamente, é preciso colá-lo na célula A1 da planilha.

  1. Para alternar entre a exibição de resultados e a exibição das fórmulas que retornam os resultados, pressione CTRL+` (acento grave) ou, na guia Fórmulas, no grupo Auditoria de Fórmulas, clique no botão Mostrar Fórmulas.

Depois de copiar o exemplo em uma planilha em branco, você poderá adaptá-lo às suas necessidades.


 
1
2
3
4
5
6
7
8

9
A B C
Mês Vendas
1 3100
2 4500
3 4400
4 5400
5 7500
6 8100
Fórmula Descrição Resultado
=SOMA(PROJ.LIN(B2:B7; A2:A7)*{9;1}) Vendas estimadas para o nono mês 11000

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.

Talvez seja mais fácil entender o exemplo se você copiá-lo em uma planilha em branco.

MostrarComo copiar um exemplo?

  1. Selecione o exemplo deste artigo. Se estiver copiando o exemplo no Excel Web App, copie e cole uma célula de cada vez.

 Importante   Não selecione os cabeçalhos de linha ou de coluna.

Selecionando um exemplo na Ajuda

Selecionando um exemplo na Ajuda
  1. Pressione CTRL+C.
  2. Crie uma pasta de trabalho ou planilha em branco.
  3. Na planilha, selecione a célula A1 e pressione CTRL+V. Se estiver trabalhando no Excel Web App, repita a ação de copiar e colar para cada célula do exemplo.

 Importante   Para que o exemplo funcione corretamente, é preciso colá-lo na célula A1 da planilha.

  1. Para alternar entre a exibição de resultados e a exibição das fórmulas que retornam os resultados, pressione CTRL+` (acento grave) ou, na guia Fórmulas, no grupo Auditoria de Fórmulas, clique no botão Mostrar Fórmulas.

Depois de copiar o exemplo em uma planilha em branco, você poderá adaptá-lo às suas necessidades.


 

1
2
3
4
5
6
7
8
9
10
11
12
13

14
A B C D E
Área útil (x1) Salas (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
Fórmula
=PROJ.LIN(E2:E12;A2:D12;VERDADEIRO;VERDADEIRO)

 Importante   A fórmula no exemplo deve ser inserida como uma fórmula de matriz. Após copiar o exemplo para uma planilha em branco, selecione o intervalo A14:E18 iniciando com a célula de fórmula. Pressione F2 e, em seguida, pressione CTRL+SHIFT+ENTER. Se a fórmula não for inserida como uma fórmula de matriz, o único resultado será -234,2371645.

Quando inseridas como uma matriz, as estatísticas de regressão a seguir são retornadas. Use esta tecla para identificar a estatística desejada.

Uma chave de estatísticas de regressão

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

Como alternativa, é possível copiar a tabela a seguir na célula A21 da planilha criada para este exemplo.

Área útil (x1) Salas (x2) Entradas (x3) Idade (x4) Valor estimado (y)
2500 3 2 25 =D14*A22 + C14*B22 + B14*C22 + A14*D22 + E14

Você também pode usar a função TENDÊNCIA para calcular esse valor.

Exemplo 4

Usando as Estatísticas de F e r2

No exemplo anterior, o coeficiente de determinação, ou r2, é 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. 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.

Os valores F e df no resultado da função PROJ.LIN podem ser usados para avaliar a probabilidade de ocorrer um valor mais alto de F por acaso. F pode ser comparado a valores críticos em tabelas de distribuição de F publicadas ou a função DISTF no Excel pode ser usada para calcular a probabilidade de ocorrer um valor de F maior por acaso. A distribuição apropriada de F possui graus v1 e v2 de liberdade. Se n for o número de pontos de dados e constante for VERDADEIRO ou estiver omitido, então v1 = n – df – 1 e v2 = df. (Se constante for FALSO, então v1 = n – df e v2 = df). A função DISTF  — com a sintaxe DISTF(F,v1,v2)  — retornará a probabilidade de um valor mais alto de F ocorrer por acaso. Nesse exemplo, df = 6 (célula B18) e F = 459.753674 (célula A18).

Supondo um valor de Alpha igual a 0.05, v1 = 11 – 6 – 1 = 4 e v2 = 6, o nível crítico de F e 4.53. Como F = 459.753674 é muito mais alto do que 4.53, é extremamente improvável que um valor de F tão alto tenha ocorrido por acaso (Com Alpha = 0.05, a hipótese de que não há relação entre val_conhecidos_y e val_conhecidos_x deve ser rejeitada quando F excede o nível crítico, 4.53). Você pode usar a função DISTF no Excel pode obter a probabilidade de que um valor F dessa magnitude tenha ocorrido por acaso. Por exemplo, DISTF(459.753674, 4, 6) = 1.37E-7, uma probabilidade extremamente baixa. Você pode concluir, seja encontrando o nível crítico de F em uma tabela ou usando a função DISTF, que a equação de regressão é útil na previsão do valor avaliado de prédios comerciais nesta área. Lembre-se que é crítica a utilização dos valores corretos de v1 e v2 calculados no parágrafo anterior.

Exemplo 5

Calculando as estatísticas de 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 o valor absoluto de t for suficientemente alto, poderemos concluir que que o eficiente da curva é útil na estimativa do valor avaliado de um prédio comercial no Exemplo 3. A tabela a seguir mostra os valores absolutos dos 4 valores observados de t.

Se você consultar uma tabela em um manual de estatística, você descobrirá que o valor t crítico e bicaudal com 6 graus de liberdade e Alfa = 0,05 é 2,447. Este valor crítico também pode ser obtido por meio da função INVT no Excel. INVT(0.05,6) = 2.447. Na medida em que o valor t absoluto (17,7) é maior que 2,447, 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 valor estatístico de maneira semelhante. Na tabela a seguir, encontram-se os valores 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 2,447; 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.

 
 
Aplica-se a:
Excel 2007