✅ Função PROJ.LIN Excel - Estatísticas de regressão linear

Índice

Visão geral da função PROJ.LIN
Função PROJ.LIN Sintaxe e entradas
O que é PROJ.LIN?
Como usar PROJ.LIN
Previsão com PROJ.LIN (Regressão Simples)
Previsão com PROJ.LIN (Regressão Linear Múltipla)
Dicas de LINEST
Interessado em mais previsões?
PROJETO função no Google Sheets
Exemplos de PROJ.LIN em VBA

Baixar exemplo de pasta de trabalho

Baixe a apostila de exemplo

Este tutorial demonstra como usar o Função LINEST do Excel no Excel para calcular estatísticas sobre uma linha de tendência.

Visão geral da função PROJ.LIN

A função PROJ.LIN Calcula estatísticas sobre uma linha de tendência ajustada a pontos de dados conhecidos usando o método dos mínimos quadrados.

Para usar a função de planilha do Excel PROJ.LIN, selecione uma célula e digite:

(Observe como as entradas da fórmula aparecem)

Função PROJ.LIN Sintaxe e entradas

1	= PROJ.LIN (s_conhecidos, xs_conhecidos, const, estatísticas)

know_y’s - Uma matriz de valores Y conhecidos.

conhecidos_x - Uma matriz de valores X conhecidos.

const - OPCIONAL. Valor lógico que indica se deve calcular B (a interceptação em y = mx + b) usando o método dos mínimos quadrados (VERDADEIRO ou omitido) ou definir manualmente B = 0 (FALSO).

Estatísticas - OPCIONAL. Retorna estatísticas adicionais (VERDADEIRO) ou retorna apenas m (inclinação) e b (interceptar) (FALSO ou Omitido)

O que é PROJ.LIN?

A função PROJ.LIN no Excel é uma função usada para gerar estatísticas de regressão para um modelo de regressão linear. PROJ.LIN é uma fórmula de matriz e pode ser usada sozinha ou com outras funções para calcular estatísticas específicas sobre o modelo.

A regressão linear é um método em estatísticas usado para prever dados seguindo uma linha reta usando dados conhecidos. A regressão é usada para prever valores como crescimento de vendas, requisitos de estoque ou tendências de mercado simples.

PROJ.LIN é como FORECAST no sentido de que atinge um resultado semelhante, mas com muito mais informações sobre seu modelo de regressão, bem como a opção de ajustar mais de uma variável independente.

Suponha que eu tenha uma tabela de dados com x e y valores onde x é a variável independente e y é a variável dependente:

Eu quero saber qual é a equação de regressão dos dados acima. Usando PROJ.LIN:

1	= PROJ.LIN (B3: B7, C3: C7, VERDADEIRO, FALSO)

O valor da interceptação y aqui é equivalente a 0, em notação científica.

A equação da reta é y = 2x + 0. Observe que LINEST retorna Ambas a inclinação e a interceptação da linha. Para retornar os dois valores, a fórmula deve ser inserida como uma fórmula de matriz. Mais informações sobre fórmulas de matriz posteriormente.

Como usar PROJ.LIN

A função PROJ.LIN leva quatro argumentos:

1	= PROJ.LIN (known_y’s, known_x’s, const, stats)

Onde,

Argumento	Descrição
know_y’s e conhecidos_x	É o x e y dados em sua tabela de dados
const	Opção TRUE / FALSE para se a interceptação y deve ser forçada para 0 ou calculada normalmente
Estatísticas	Opção TRUE / FALSE se estatísticas de regressão adicionais devem ser retornadas

Usando nosso primeiro exemplo, a função é escrita como:

1	= PROJ.LIN (B3: B7, C3: C7, VERDADEIRO, FALSO)

Quando o Estatísticas opção é definida como TRUE, a organização das estatísticas de regressão é a seguinte:

Você pode estar se perguntando o que cada variável significa.

Estatística	Descrição
m_n	Coeficientes de inclinação para x variáveis
b	interceptar y
se_n	Erro padrão para cada coeficiente de inclinação
se_b	Erro padrão para a interceptação y
r²	Coeficiente de determinação
se_y	Erro padrão para o y estimativa
F	A estatística F (para determinar se a relação das variáveis ocorre por acaso)
d_f	Graus de liberdade
WL_reg	Soma da regressão dos quadrados
WL_resid	Soma residual dos quadrados

As principais estatísticas a serem compreendidas são os coeficientes de inclinação, intercepto y e o coeficiente de determinação ou r²valor do modelo.

Usando o exemplo acima e selecionando VERDADEIRO para o Estatísticas parâmetro:

As células destacadas mostram a inclinação = 2, interceptar = 0 e r² = 1.

O r²valor é um indicador da força da correlação do modelo. Pode ser considerado um indicador de ajuste. Um r baixo²valor significaria uma correlação fraca entre suas variáveis dependentes e independentes, e o oposto é verdadeiro para r alto²valores, com r²= 1 sendo um ajuste perfeito.

Nas versões seguintes a janeiro de 2022 do Excel no Microsoft 365 (anteriormente Office 365), as matrizes dinâmicas mudaram a forma como as fórmulas de matriz são avaliadas. Não é mais necessário usar CTRL + SHIFT + ENTER ou destacar a área de células que a matriz ocupará. Basta inserir a fórmula e clicar em Enter e as células resultantes irão “transbordar” na matriz.

No restante deste artigo, faremos referência ao uso de PROJ.LIN com relação a matrizes dinâmicas no Microsoft 365 Excel.

Previsão com PROJ.LIN (Regressão Simples)

A combinação das funções PROJ.LIN e SOMA pode ser usada para prever o valor de uma variável dependente y, dado conhecido x e y dados. Abaixo está um exemplo que mostra o que y o valor será quando x = 14.

1	= SOMA (PROJ.LIN (C3: C7, B3: B7) * {14,1})

O modelo vem na forma y = mx + b. Isso é o mesmo que y = a + bx, apenas uma maneira diferente de representar a equação. Uma dica a ter em mente para equações lineares é a variável ao lado de x é sempre a inclinação, e a variável após um sinal de mais ou menos é sempre a interceptação, independentemente das letras usadas na equação.

Usando a fórmula: = SOMA (PROJ.LIN (C3: C7, B3: B7) * {14,1}) retorna o resultado de 28. Como este é um único resultado, não é necessário inserir como uma matriz.

O final da fórmula acima * {14,1} especifica a variável independente a ser usada para prever a variável dependente, neste caso, 14.

Podemos verificar isso inserindo x = 14 na equação da reta, y = 2x + 0.

Previsão com PROJ.LIN (Regressão Linear Múltipla)

A tabela de dados a seguir vem da página PROJ.LIN do site de Suporte da Microsoft.

Em alguns casos, há mais de uma variável independente que deve ser considerada ao criar um modelo de regressão linear. Isso é chamado de regressão linear múltipla (ou seja, várias variáveis independentes). Se eu quiser estimar o custo de um prédio de escritórios, coisas como espaço, número de entradas do prédio, idade do prédio e o número de escritórios fariam parte da equação. Vamos ver um exemplo.

Digitando a fórmula PROJ.LIN na célula G29 e executando-a, obtemos:

1	= PROJ.LIN (E3: E13, A3: D13, VERDADEIRO, VERDADEIRO)

O modelo vem no formato:

Lembre-se de que a matriz de resultados PROJ.LIN está na ordem inversa da equação. No exemplo acima, 52.317,8 é nossa interceptação, b, e 27,6 é nosso m₁ou o valor de inclinação para a variável de espaço do piso, x₁.

Usando a função PROJ.LIN com os dados fornecidos, nosso modelo de regressão é:

Com um r² valor de 0,997, o que indica um modelo forte ou altamente correlacionado. Usando o modelo, agora você pode prever qual será o valor avaliado de um prédio de escritórios com base em qualquer combinação das variáveis independentes acima.

Dicas de LINEST

Certifique-se de ter a versão mais atualizada do Microsoft 365 para utilizar PROJ.LIN com matrizes dinâmicas. Pode ser necessário habilitar o Canal Atual do Office Insider (Visualização) para utilizar funções de matriz dinâmica. Na página da conta:
Se você estiver em uma versão que não seja do Microsoft 365, terá que usar o método herdado CTRL + SHIFT + ENTER (CSE) de avaliação de fórmulas de matriz.
Se estiver usando o método legado, o número de colunas a destacar ao inserir uma função de matriz PROJ.LIN é sempre o número de x variáveis em seus dados mais 1. O número de linhas a serem selecionadas para a matriz é 5.
Se você for compartilhar sua versão do Excel habilitada para matriz dinâmica com alguém que está usando uma versão não Microsoft 365, use o método CSE herdado para evitar problemas de compatibilidade.