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

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 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 vários intervalos de valores 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:

Sintaxe

• 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. O 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;r²,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:

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 ponto de origem y:

  Inclinação (m):
  Para calcular a inclinação de uma linha, frequentemente representada por m, use dois pontos da linha, (x1,y1) e (x2,y2); a inclinação será igual a (y2 - y1)/(x2 - x1).

  Interceptação de y (b):
  O intercepto de y de uma linha, frequentemente 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)

  Interceptação 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:

  

  

  em que 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 curva PROJ.LIN e PROJ.LOG podem calcular a melhor linha reta ou curva exponencial que se ajusta aos seus dados. Entretanto, você precisa decidir qual dos dois resultados se ajusta melhor aos seus dados. Você pode calcular o TREND(known_y's,known_x's) para uma linha reta ou GROWTH(known_y's, known_x's) a uma curva exponencial. Essas funções, sem o new_x do, retornam uma matriz de valores y previstos ao longo dessa linha ou curva nos seus pontos de dados reais. Em seguida, você pode comparar os valores previstos com os valores reais. Talvez você queira catálogo em destaque para 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 residual de quadrados, em comparação com a soma total de quadrados, maior será o valor do coeficiente de determinação, r², que é um indicador de quão bem a equação resultante da análise de regressão explica a relação entre as variáveis. O valor de r² igual a ssreg/sstotal.

• Em alguns casos, uma ou mais colunas de X (supondo que os Ys e Xs estejam em colunas) podem não ter nenhum valor previsível adicional na presença das 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 do 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.

• Ao inserir a constante de uma matriz (como val_conhecidos_x) como um argumento, use vírgulas para separar valores contidos na mesma linha e pontos-e-vírgulas para separar linhas. Os caracteres de separadores poderão ser diferentes dependendo de suas configurações regionais.

• 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 retornam um #DIV/0! #REF!. 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ê pode usar PROJ.LIN para calcular um intervalo de outros tipos de regressão inserindo 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.

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

Exemplos