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

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 as estatísticas forem VERDADEIRAs, PROJ.LIN devolve as estatísticas de regressão adicionais; como resultado, a matriz devolvida é {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; r^2,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.

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

Observações

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

  Declive (m):
  Para localizar o declive de uma linha, muitas vezes escrita como m, tome dois pontos na linha (x1,y1) e (x2,y2); o declive é igual a (y2 - y1)/(x2 - x1).

  Interceção Y (b):
  A interceção y de uma linha, muitas vezes escrita como b, é o valor de y no ponto em que a linha cruza o eixo y.

  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.LINEST(known_y,known_x);1)

  Interceção Y:
  =ÍNDICE(PROJ.LINEST(known_y,known_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:

  

  

  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, em comparação com a soma total dos quadrados, maior é 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 das colunas X (suponha que Y e X estão em colunas) podem não ter nenhum valor preditivo adicional na presença das outras colunas X. Por outras palavras, eliminar uma ou mais colunas X pode levar 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 chama-se "collinearidade" porque qualquer coluna X redundante pode ser expressa como uma soma de múltiplos das colunas X não redundantes. A função PROJ.LIN verifica a collinearidade e remove todas as colunas X redundantes do modelo de regressão quando as identifica. As colunas X removidas podem ser reconhecidas na saída PROJ.LINEST como tendo 0 coeficientes, além de 0 valores se. Se uma ou mais colunas forem removidas como redundantes, df será afetado porque df depende do número de colunas X realmente utilizadas para fins preditivos. Para obter detalhes sobre a computação de df, veja Exemplo 4. Se df for alterado porque as colunas X redundantes são removidas, os valores de sey e F também são afetados. A collinearidade deve ser relativamente rara na prática. No entanto, um caso em que é mais provável que surja é quando algumas colunas X contêm apenas 0 e 1 valores como indicadores de se um assunto numa experimentação é ou não membro de um grupo específico. Se constante = VERDADEIRO ou for omitido, a função PROJ.LIN insere efetivamente uma coluna X adicional de todos os 1 valores para modelar a interceção. Se tiver uma coluna com um 1 para cada assunto se for homem ou 0 caso contrário, e também tiver uma coluna com um 1 para cada assunto se for feminino ou 0 caso contrário, esta última coluna será redundante porque as entradas na mesma podem ser obtidas ao subtrair a entrada na coluna "indicador masculino" da entrada na coluna adicional de todos os 1 valores 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.

• 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 #DIV/0! . O algoritmo das funções DECLIVE e INTERCETAR foi concebido para procurar apenas uma resposta e, neste caso, pode haver 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