Regressão linear no Excel (Sumário)

  • Introdução à regressão linear no Excel
  • Métodos para usar regressão linear no Excel

Introdução à regressão linear no Excel

A regressão linear é uma técnica / método estatístico usado para estudar a relação entre duas variáveis ​​quantitativas contínuas. Nesta técnica, variáveis ​​independentes são usadas para prever o valor de uma variável dependente. Se houver apenas uma variável independente, será uma regressão linear simples e, se um número de variáveis ​​independentes for mais de uma, será uma regressão linear múltipla. Os modelos de regressão linear têm uma relação entre variáveis ​​dependentes e independentes, ajustando uma equação linear aos dados observados. Linear refere-se ao fato de que usamos uma linha para ajustar nossos dados. As variáveis ​​dependentes usadas na análise de regressão também são chamadas de resposta ou variáveis ​​preditas, e variáveis ​​independentes também são chamadas de variáveis ​​explicativas ou preditores.

Uma linha de regressão linear tem uma equação do tipo: Y = a + bX;

Onde:

  • X é a variável explicativa,
  • Y é a variável dependente,
  • b é a inclinação da linha,
  • a é interceptação em y (ou seja, valor de y quando x = 0).

O método dos mínimos quadrados é geralmente usado na regressão linear que calcula a melhor linha de ajuste para os dados observados, minimizando a soma dos quadrados do desvio dos pontos de dados da linha.

Métodos para usar regressão linear no Excel

Este exemplo ensina os métodos para executar a Análise de regressão linear no Excel. Vejamos alguns métodos.

Você pode fazer o download deste modelo de regressão linear do Excel aqui - Modelo de regressão linear do Excel

Método # 1 - Gráfico de Dispersão com uma Linha de Tendência

Digamos que temos um conjunto de dados de alguns indivíduos com idade, índice de massa biológica (IMC) e o valor gasto por eles em despesas médicas em um mês. Agora, com uma visão das características dos indivíduos, como idade e IMC, desejamos descobrir como essas variáveis ​​afetam as despesas médicas e, portanto, usá-las para realizar regressões e estimar / prever as despesas médicas médicas de alguns indivíduos específicos. Vamos primeiro ver como apenas a idade afeta as despesas médicas. Vamos ver o conjunto de dados:

Valor das despesas médicas = b * idade + a

  • Selecione as duas colunas do conjunto de dados (x e y), incluindo os cabeçalhos.

  • Clique em 'Inserir' e expanda o menu suspenso 'Gráfico de dispersão' e selecione a miniatura 'Dispersão' (primeira)

  • Agora, um gráfico de dispersão aparecerá e desenharemos a linha de regressão sobre isso. Para fazer isso, clique com o botão direito do mouse em qualquer ponto de dados e selecione 'Adicionar linha de tendência'

  • Agora, no painel 'Formatar linha de tendência', à direita, selecione 'Linha de tendência linear' e 'Exibir equação no gráfico'.

  • Selecione 'Exibir equação no gráfico'.

Podemos improvisar o gráfico conforme nossos requisitos, como adicionar títulos de eixos, alterar a escala, a cor e o tipo de linha.

Depois de melhorar o gráfico, este é o resultado que obtemos.

Nota: Nesse tipo de gráfico de regressão, a variável dependente deve sempre estar no eixo y e independente no eixo x. Se o gráfico for plotado na ordem inversa, alterne os eixos em um gráfico ou troque as colunas no conjunto de dados.

Método 2 - Método Add-In do Analysis ToolPak

O Analysis ToolPak às vezes não está ativado por padrão e precisamos fazê-lo manualmente. Para fazer isso:

  • Clique no menu 'Arquivo'.

Depois disso, clique em 'Opções'.

  • Selecione 'Suplementos do Excel' na caixa 'Gerenciar' e clique em 'Ir'

  • Selecione 'Analysis ToolPak' -> 'OK'

Isso adicionará as ferramentas de 'Análise de dados' à guia 'Dados'. Agora, executamos a análise de regressão:

  • Clique em 'Análise de dados' na guia 'Dados'

  • Selecione 'Regressão' -> 'OK'

  • Uma caixa de diálogo de regressão será exibida. Selecione o intervalo de entrada Y e o intervalo de entrada X (despesas médicas e idade, respectivamente). No caso de regressão linear múltipla, podemos selecionar mais colunas de variáveis ​​independentes (como se desejamos ver o impacto do IMC também nas despesas médicas).
  • Marque a caixa 'Etiquetas' para incluir cabeçalhos.
  • Escolha a opção 'saída' desejada.
  • Marque a caixa de seleção 'resíduos' e clique em 'OK'.

Agora, nossa saída da análise de regressão será criada em uma nova planilha, indicando as estatísticas de regressão, a ANOVA, os resíduos e os coeficientes.

Interpretação da saída:

  • Estatísticas de regressão informa quão bem a equação de regressão se ajusta aos dados:

  • R múltiplo é o coeficiente de correlação que mede a força do relacionamento linear entre duas variáveis. Está entre -1 e 1, e seu valor absoluto representa a força do relacionamento com um valor grande indicando relacionamento mais forte, valor baixo indicando valor negativo e valor zero indicando nenhum relacionamento.
  • R Square é o coeficiente de determinação usado como um indicador de qualidade do ajuste. Fica entre 0 e 1, com um valor próximo de 1 indicando que o modelo é um bom ajuste. Nesse caso, 0, 57 = 57% dos valores-y são explicados pelos valores-x.
  • O quadrado R ajustado é o quadrado R ajustado para o número de preditores em caso de regressão linear múltipla.
  • Erro padrão descreve a precisão da análise de regressão.
  • As observações mostram o número de observações do modelo.
  • Anova diz o nível de variabilidade dentro do modelo de regressão.

Isso geralmente não é usado para regressão linear simples. No entanto, os 'valores de significância F' indicam quão confiáveis ​​são nossos resultados, com um valor maior que 0, 05 sugerindo a escolha de outro preditor.

  • Coeficientes é a parte mais importante usada para construir a equação de regressão.

Assim, nossa equação de regressão seria: y = 16, 891 x - 355, 32. É o mesmo que o feito pelo método 1 (gráfico de dispersão com uma linha de tendência).

Agora, se desejarmos prever despesas médicas médicas com 72 anos de idade:

Então y = 16, 891 * 72 -355, 32 = 860, 832

Desta forma, podemos prever valores de y para quaisquer outros valores de x.

  • Os resíduos indicam a diferença entre os valores reais e previstos.

O último método para regressão não é tão comumente usado e requer funções estatísticas como inclinação (), interceptação (), correl () etc. para realizar a análise de regressão.

Lembretes sobre a regressão linear no Excel

  • A análise de regressão é geralmente usada para verificar se há um relacionamento estatisticamente significativo entre dois conjuntos de variáveis.
  • É usado para prever o valor da variável dependente com base nos valores de uma ou mais variáveis ​​independentes.
  • Sempre que desejamos ajustar um modelo de regressão linear a um grupo de dados, o intervalo de dados deve ser cuidadosamente observado, como se usássemos uma equação de regressão para prever qualquer valor fora desse intervalo (extrapolação), pois isso pode levar a resultados incorretos.

Artigos recomendados

Este é um guia para regressão linear no Excel. Aqui discutimos como fazer a regressão linear no Excel, além de exemplos práticos e modelo de excel para download. Você também pode consultar nossos outros artigos sugeridos -

  1. Como preparar a folha de pagamento no Excel?
  2. Uso da fórmula MAX no Excel
  3. Tutoriais sobre referências de célula no Excel
  4. Criando análise de regressão no Excel
  5. Programação linear no Excel

Categoria:

Dicas do Excel