Análise de regressão do Excel (sumário)

  • Análise de regressão no Excel
  • Explicação matematicamente da regressão
  • Como executar regressão linear no Excel?
    • # 1 - Ferramenta de regressão usando o Analysis ToolPak no Excel
    • # 2 - Análise de regressão usando gráfico de dispersão com linha de tendência no Excel

Análise de regressão no Excel

A regressão linear é uma técnica estatística que examina a relação linear entre uma variável dependente e uma ou mais variáveis ​​independentes.

  • Variável dependente (também conhecida como variável resposta / resultado): é a variável de seu interesse e que você deseja prever com base nas informações disponíveis das variáveis ​​independentes.
  • Variável independente (também conhecida como variável explicativa / preditora): É / são as variáveis ​​das quais a variável de resposta depende. O que significa que essas são as variáveis ​​que usam a variável de resposta que pode ser prevista.

Relação linear significa que a alteração em uma (s) variável (s) independente (s) causa uma alteração na variável dependente.

Existem basicamente dois tipos de relações lineares também.

  1. Relacionamento linear positivo: Quando a variável independente aumenta, a variável dependente também aumenta.
  2. Relacionamento linear negativo: quando a variável independente aumenta, a variável dependente diminui.

Esses foram alguns dos pré-requisitos antes de você prosseguir para a análise de regressão no Excel.

Existem duas maneiras básicas de executar a regressão linear no Excel usando:

  • Ferramenta de regressão através do Analysis ToolPak
  • Gráfico de dispersão com linha de tendência

Na verdade, existe mais um método que usa fórmulas manuais para calcular a regressão linear. Mas por que você deveria fazer isso quando o Excel faz cálculos para você?

Portanto, falaremos apenas dos dois métodos discutidos acima.

Suponha que você tenha dados de altura e peso de 10 indivíduos. Se você plotar essas informações em um gráfico, vamos ver o que elas fornecem.

Como mostra a captura de tela acima, a relação linear pode ser encontrada em Altura e Peso através do gráfico. Não se envolva muito no gráfico agora, de qualquer forma, vamos aprofundar na segunda parte deste artigo.

Explicação matematicamente da regressão

Temos uma expressão matemática para regressão linear como abaixo:

Y = aX + b + ε

Onde,

  • Y é uma variável dependente ou variável de resposta.
  • X é uma variável ou preditor independente.
  • a é a inclinação da linha de regressão. O que representa que, quando X muda, há uma alteração em Y por unidades "a".
  • b é interceptador. É o valor que Y assume quando o valor de X é zero.
  • ε é o termo de erro aleatório. Ocorre porque o valor previsto de Y nunca será exatamente igual ao valor real para um determinado X. Nesse termo de erro, não precisamos nos preocupar. Como existem alguns softwares que calculam esse termo de erro no back-end para você. O Excel é um desses softwares.

Nesse caso, a equação se torna,

Y = aX + b

Que pode ser representado como:

Peso = a * Altura + b

Vamos tentar descobrir os valores desses a e b usando os métodos que discutimos acima.

Como executar regressão linear no Excel?

O artigo adicional explica os conceitos básicos da análise de regressão no Excel e mostra algumas maneiras diferentes de fazer a regressão linear no Excel.

Você pode fazer o download deste modelo do Excel para análise de regressão aqui - Modelo do Excel para análise de regressão

# 1 - Ferramenta de regressão usando o Analysis ToolPak no Excel

No nosso exemplo, tentaremos ajustar a regressão para os valores de Peso (que é variável dependente) com a ajuda dos valores de Altura (que é uma variável independente).

  • Na planilha do Excel, clique em Análise de Dados (presente em Grupo de Análise ) em Dados.

  • Pesquise Regressão . Selecione e pressione ok.

  • Use as seguintes entradas no painel Regressão que é aberto.

  • Intervalo Y de entrada : selecione as células que contêm sua variável dependente (neste exemplo B1: B11)

  • Intervalo de entrada X : selecione as células que contêm sua variável independente (neste exemplo A1: A11).

  • Marque a caixa denominada Etiquetas se seus dados tiverem nomes de colunas (neste exemplo, temos nomes de colunas).

  • O nível de confiança é definido como 95% por padrão, que pode ser alterado conforme os requisitos dos usuários.

  • Em Opções de saída, você pode personalizar onde deseja ver a saída da análise de regressão no Excel. Nesse caso, queremos ver a saída na mesma folha. Portanto, dado o intervalo em conformidade.

  • Na opção Residuais, você tem entradas opcionais como Residuais, Gráficos Residuais, Residuais Padronizados, Gráficos de Ajuste de Linha que podem ser selecionados conforme sua necessidade. Nesse caso, marque a caixa de seleção Residuals para que possamos ver a dispersão entre os valores previstos e reais.

  • Na opção Probabilidade normal, você pode selecionar Gráficos de probabilidade normal, que podem ajudá-lo a verificar a normalidade dos preditores. Clique em OK .

  • O Excel calculará a análise de regressão para você em uma fração de segundos.

Até aqui, era fácil e não tão lógico. No entanto, interpretar essa saída e extrair informações valiosas é uma tarefa complicada.

Uma parte importante de toda essa saída é R Square / R Square ajustado na tabela SUMMARY OUTPUT. Que fornece informações, quão bom é o nosso modelo. Nesse caso, o valor de R Square é 0, 9547. O que interpreta que o modelo tem uma precisão de 95, 47% (bom ajuste). Ou em outro idioma, as informações sobre a variável Y são explicadas 95, 47% pela variável X.

A outra parte importante de toda a produção é uma tabela de coeficientes. Ele fornece valores de coeficientes que podem ser usados ​​para construir o modelo para previsões futuras.

Agora, nossa equação de regressão para previsão se torna:

Peso = 0, 6746 * Altura - 38, 45508 (o valor da inclinação para Altura é 0, 6746… e Interceptação é -38, 45508…)

Você conseguiu o que definiu? Você definiu uma função na qual agora apenas precisa colocar o valor de Height e obterá o valor de Weight.

# 2 - Análise de regressão usando gráfico de dispersão com linha de tendência no Excel

Agora, veremos como no Excel podemos ajustar uma equação de regressão em um gráfico de dispersão.

  • Selecione seus dois dados em colunas inteiros (incluindo cabeçalhos).
  • Clique em Inserir e selecione Gráfico de dispersão na seção de gráficos, como mostrado na imagem abaixo.

  • Veja o gráfico de saída.

  • Agora, precisamos ter uma linha de regressão ao quadrado mínimo neste gráfico. Para adicionar essa linha, clique com o botão direito do mouse em qualquer um dos pontos de dados no gráfico e selecione a opção Adicionar linha de tendência .

  • Isso permitirá que você tenha uma linha de tendência de menor quadrado de regressão, como abaixo.

  • Na opção Formatar linha de tendência, marque a caixa Exibir equação no gráfico.

  • Permite ver a equação da linha de regressão ao quadrado mínimo no gráfico.

Esta é a equação usando a qual podemos prever os valores de peso para qualquer conjunto de valores de altura.

Lembretes sobre a análise de regressão no Excel

  • Você pode alterar o layout da linha de tendência na opção Formatar linha de tendência no gráfico de dispersão.
  • É sempre recomendável dar uma olhada nos gráficos residuais enquanto você faz a análise de regressão usando o Data Analysis ToolPak no Excel. Ele fornece uma melhor compreensão da propagação dos valores Y reais e dos valores X estimados.
  • A regressão linear simples no Excel não precisa de ANOVA e R quadrado ajustado para verificar. Esses recursos podem ser levados em consideração para a regressão linear múltipla. O que está além do escopo deste artigo.

Artigos recomendados

Este foi um guia para a análise de regressão no Excel. Aqui discutimos como fazer a análise de regressão no Excel, juntamente com exemplos do Excel e modelo de Excel para download. Você também pode consultar nossos outros artigos sugeridos -

  1. Ferramenta Excel para análise de dados
  2. Calcular ANOVA no Excel
  3. Como encontrar as médias móveis do Excel
  4. Exemplos de teste Z no Excel

Categoria: