Estatística Avançada do Excel - Função PROJ.LIN

Descrição

A função PROJ.LIN calcula as estatísticas de uma linha usando o método "mínimos quadrados" para calcular uma linha reta que melhor se adapta aos seus dados e retorna uma matriz que descreve a linha.

Você também pode combinar PROJ.LIN com outras funções para calcular as estatísticas para outros tipos de modelos que são lineares nos parâmetros desconhecidos, incluindo polinômios, logarítmicos, exponenciais e séries de potência.

Visto que esta função retorna uma matriz de valores, ela deve ser inserida como uma fórmula de matriz.

Sintaxe

LINEST (known_y's, [known_x's], [const], [stats])

Argumentos

Argumento Descrição Obrigatório / Opcional
conhecidos_y

O conjunto de valores de y que você já conhece na relação y = mx + b.

Se o intervalo de val_conhecidos_y está em uma única coluna, cada coluna de val_conhecidos_x é 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.

 Requeridos
conhecidos_x's

Um conjunto de valores x que você pode já conhecer 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, known_y's e known_x's podem ser intervalos de qualquer formato, desde que tenham dimensões iguais.

Se mais de uma variável for usada, known_y's deve ser um vetor (ou seja, um intervalo com altura de uma linha ou largura de uma coluna).

Se val_conhecidos_x for omitido, assume-se que é a matriz {1,2,3, ...} que tem o mesmo tamanho que val_conhecidos_y.

 Opcional
const

Um valor lógico que especifica se é necessário forçar a constante b para igual a 0.

Se const for TRUE ou omitido, b é calculado normalmente.

Se const for FALSE, b é definido igual a 0 e os valores m são ajustados para caber y = mx.

 Opcional
Estatísticas

Um valor lógico que especifica se deve retornar estatísticas de regressão adicionais.

Se as estatísticas forem TRUE, PROJ.LIN retornará as estatísticas de regressão adicionais. Como resultado, a matriz retornada é {mn, mn-1, ..., m1, b; sen, sen-1, ..., se1, seb; r2, sey; F, df; ssreg, ssresid}.

Se estatísticas for FALSO ou omitido, PROJ.LIN retornará apenas os mcoeficientes e a constante b. As estatísticas de regressão adicionais são apresentadas na Tabela abaixo.

 Opcional

Estatísticas de regressão adicionais

Sr. Não Estatística e descrição
1

se1,se2,...,sen

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

seb

O valor do erro padrão para a constante b (seb = # N / A quando const é FALSE).
3

r2

O coeficiente de determinação. Compara os valores y estimados e reais e varia em valor de 0 a 1. Se for 1, há uma correlação perfeita na amostra - não há diferença 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 é útil para prever um valor y. Para obter informações sobre como r2 é calculado, consulte as notas abaixo.
4

sey

O erro padrão para a estimativa de y.
5

F

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

df

Os graus de liberdade. Use os graus de liberdade para ajudá-lo a encontrar os valores críticos de F em uma tabela estatística. Compare os valores encontrados na tabela com a estatística F retornada por PROJ.LIN para determinar um nível de confiança para o modelo. Para obter informações sobre como o df é calculado, consulte as notas abaixo.
7

ssreg

A soma dos quadrados da regressão.
8

ssreg

A soma residual dos quadrados. Para obter informações sobre como ssreg e ssresid são calculados, consulte as notas abaixo.

Notas

  • A equação da reta é -

    y = mx + b

    ou

    y = m1x1 + m2x2 + ... + b

  • Se houver vários intervalos de valores x, onde os valores y dependentes são uma função dos valores x independentes, então -

    • Os valores m são coeficientes correspondentes a cada valor x e b é um valor constante.

    • Observe que y, x e m podem ser vetores.

  • A matriz que a função PROJ.LIN retorna é {mn, mn-1… m1, b}.

  • PROJ.LIN também pode retornar estatísticas de regressão adicionais

  • Você pode descrever qualquer linha reta com a inclinação e a interceptação y -

    • Slope(m) -

      Para encontrar a inclinação de uma linha, geralmente escrita como m, pegue dois pontos na linha, (x1, y1) e (x2, y2). A inclinação é igual a

      (–2 - y1) / (- 2 - x1).

    • Y-intercept(b) -

      A interceptação y de uma linha, geralmente escrita como b, é o valor de y no ponto onde a linha cruza o eixo y.

  • A equação de uma linha reta é y = mx + b. Depois de saber os valores de m e b, você pode calcular qualquer ponto na linha inserindo o valor y ou x nessa equação. Você também pode usar a função TREND.

  • Quando você tem apenas uma variável x independente, você pode obter os valores de inclinação e interceptação y diretamente usando as seguintes fórmulas -

    • Slope -

      = INDEX (PROJ.LIN ((conhecidos_y, conhecidos_x), 1)

    • Y-intercept -

      = INDEX (PROJ.LIN ((conhecidos_y, conhecidos_x), 2)

  • A precisão da linha calculada pela função PROJ.LIN depende do grau de dispersão em seus dados. Quanto mais lineares os dados, mais preciso é o modelo PROJ.LIN.

  • PROJ.LIN usa o método dos mínimos quadrados para determinar o melhor ajuste para os dados. Quando você tem apenas uma variável x independente, os cálculos para m e b são baseados nas seguintes fórmulas -

    $$ m = \ frac {\ sum \ left (x- \ bar {x} \ right) \ left (y- \ bar {y} \ right)} {\ sum \ left (x- \ bar {x} \ direita) ^ 2} $$

    Onde x e y são médias amostrais. ie

    x = MÉDIA (x's conhecidos)

    y = AVERAGE (known_y's)

  • 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. No entanto, você deve decidir qual dos dois resultados se ajusta melhor aos seus dados. Você pode calcular TREND (known_y's, known_x's) para uma linha reta, ou GROWTH (known_y's, known_x's) para uma curva exponencial. Essas funções, sem o argumento known_x's omitido, retornam uma matriz de valores y previstos ao longo dessa linha ou curva em seus pontos de dados reais. Você pode então comparar os valores previstos com os valores reais. Você pode querer fazer um gráfico de ambos para uma comparação visual.

  • Na análise de regressão, o Excel calcula para cada ponto a diferença quadrática entre o valor y estimado para aquele ponto e seu valor y real. A soma dessas diferenças quadradas é chamada de soma residual dos quadrados, ssresid. O Excel então calcula a soma total dos quadrados, sstotal. Quando o argumento const = TRUE ou é omitido, a soma total dos quadrados é a soma das diferenças quadradas entre os valores y reais e a média dos valores y.

  • Quando o argumento const = FALSE, a soma total dos quadrados é a soma dos quadrados dos valores y reais (sem subtrair o valor y médio de cada valor y individual). Então a soma dos quadrados da regressão, ssreg, pode ser encontrada em: ssreg = sstotal - ssresid. Quanto menor for a soma residual dos quadrados, em comparação com a soma total dos quadrados, maior será o valor do coeficiente de determinação, r2, 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 r2 é igual a ssreg / sstotal.

  • Em alguns casos, uma ou mais das colunas X (suponha que Y's e X's estão nas colunas) podem não ter nenhum valor preditivo adicional na presença das outras colunas X. ou seja, a eliminação de uma ou mais colunas X pode levar a valores Y previstos que são igualmente precisos. Nesse caso, essas colunas X redundantes devem ser omitidas do modelo de regressão. Este fenômeno é chamado de “colinearidade” 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 colinearidade e remove quaisquer colunas X redundantes do modelo de regressão quando as identifica. As colunas X removidas podem ser reconhecidas na saída PROJ.LIN como tendo 0 coeficientes além de 0 valores se. Se uma ou mais colunas forem removidas como redundantes, df é afetado porque df depende do número de colunas X realmente usadas para fins preditivos.

  • Se df for alterado porque colunas X redundantes foram removidas, os valores de sey e F também são afetados. A colinearidade 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 sujeito em um experimento é ou não membro de um determinado grupo. Se const = TRUE ou for omitido, a função PROJ.LIN insere efetivamente uma coluna X adicional de todos os 1 valores para modelar a interceptação

  • O valor de df é calculado da seguinte forma, quando há k colunas de known_x's e nenhuma coluna X é removida do modelo devido à colinearidade -

    • Se const = TRUE ou for omitido, df = n - k - 1

    • Se const = FALSE, df = n - k

    Em ambos os casos, cada coluna X removida devido à colinearidade aumenta o valor de df em 1.

  • Ao inserir uma constante de matriz (como val_conhecidos_x) como argumento, use vírgulas para separar os valores contidos na mesma linha e ponto-e-vírgulas para separar as linhas. Os caracteres separadores podem ser diferentes dependendo das configurações regionais.

  • Observe que os valores y previstos pela equação de regressão podem não ser válidos se estiverem fora da faixa dos valores y usados ​​para determinar a equação.

  • O algoritmo subjacente usado na função PROJ.LIN é diferente do algoritmo subjacente usado nas funções SLOPE e INTERCEPT. A diferença entre esses algoritmos pode levar a resultados diferentes quando os dados são indeterminados e colineares.

  • 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 das variáveis ​​xey como as séries xey para PROJ.LIN. Por exemplo, a seguinte fórmula -

    = 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 (polinômio de ordem 3) de -

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

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

  • O valor do teste F retornado pela função PROJ.LIN difere do valor do teste F retornado pela função FTEST. PROJ.LIN retorna a estatística F, enquanto FTEST retorna a probabilidade.

  • Se a matriz de val_conhecidos_x não tiver o mesmo comprimento que a matriz de val_conhecidos_y, PROJ.LIN retornará o #REF! valor de erro.

  • Se qualquer um dos valores nas matrizes de known_x ou known_y não forem numéricos (isso pode incluir representações de texto de números, pois a função PROJ.LIN não os reconhece como números), PROJ.LIN retornará o #VALUE! valor de erro.

  • Se nenhum dos argumentos const ou stats não puderem ser avaliados como TRUE ou FALSE, PROJ.LIN retornará o erro #VALUE! valor de erro.

Aplicabilidade

Excel 2007, Excel 2010, Excel 2013, Excel 2016

Exemplo