Analyse de régression Excel (Table des matières)

  • Analyse de régression dans Excel
  • Explication mathématique de la régression
  • Comment effectuer une régression linéaire dans Excel?
    • # 1 - Outil de régression utilisant Analysis ToolPak dans Excel
    • # 2 - Analyse de régression à l'aide de Scatterplot avec Trendline dans Excel

Analyse de régression dans Excel

La régression linéaire est une technique statistique qui examine la relation linéaire entre une variable dépendante et une ou plusieurs variables indépendantes.

  • Variable dépendante (aka variable réponse / résultat): est la variable qui vous intéresse et que vous vouliez prédire en fonction des informations disponibles sur les variables indépendantes.
  • Variable indépendante (ou variable explicative / prédictive): est / sont la (les) variable (s) dont dépend la variable de réponse. Ce qui signifie que ce sont les variables qui permettent de prédire la variable de réponse.

Une relation linéaire signifie que la modification d'une ou de plusieurs variables indépendantes entraîne une modification de la variable dépendante.

Il existe également deux types de relations linéaires.

  1. Relation linéaire positive: lorsque la variable indépendante augmente, la variable dépendante augmente également.
  2. Relation linéaire négative: lorsque la variable indépendante augmente, la variable dépendante diminue.

Ce sont quelques-unes des conditions préalables avant de procéder à une analyse de régression dans Excel.

Il existe deux méthodes de base pour effectuer une régression linéaire dans Excel en utilisant:

  • Outil de régression via Analysis ToolPak
  • Diagramme de dispersion avec courbe de tendance

Il existe en fait une autre méthode qui utilise des formules manuelles pour calculer la régression linéaire. Mais pourquoi devriez-vous y aller quand Excel fait des calculs pour vous?

Par conséquent, nous allons parler uniquement des deux méthodes décrites ci-dessus.

Supposons que vous ayez des données sur la taille et le poids de 10 personnes. Si vous tracez cette information à travers un graphique, voyons ce qu'elle donne.

Comme le montre la capture d'écran ci-dessus, la relation linéaire peut être trouvée dans la taille et le poids à travers le graphique. Ne vous impliquez pas beaucoup dans le graphique maintenant, nous allons de toute façon le creuser profondément dans la deuxième partie de cet article.

Explication mathématique de la régression

Nous avons une expression mathématique pour la régression linéaire comme ci-dessous:

Y = aX + b + ε

Où,

  • Y est une variable dépendante ou une variable de réponse.
  • X est une variable indépendante ou un prédicteur.
  • a est la pente de la droite de régression. Ce qui représente que lorsque X change, il y a un changement de Y par les unités «a».
  • b intercepte. C'est la valeur que prend Y lorsque la valeur de X est nulle.
  • ε est le terme d'erreur aléatoire. Se produit parce que la valeur prédite de Y ne sera jamais exactement la même que la valeur réelle pour X donné. Ce terme d'erreur, nous n'avons pas à nous inquiéter. Comme il existe des logiciels qui font le calcul de ce terme d'erreur dans le backend pour vous. Excel est l'un de ces logiciels.

Dans ce cas, l'équation devient,

Y = aX + b

Qui peut être représenté comme:

Poids = a * Hauteur + b

Nous allons essayer de découvrir les valeurs de ces a et b en utilisant les méthodes que nous avons discutées ci-dessus.

Comment effectuer une régression linéaire dans Excel?

L'article suivant explique les bases de l'analyse de régression dans Excel et montre quelques façons différentes de faire une régression linéaire dans Excel.

Vous pouvez télécharger ce modèle Excel d'analyse de régression ici - Modèle Excel d'analyse de régression

# 1 - Outil de régression utilisant Analysis ToolPak dans Excel

Pour notre exemple, nous essaierons d'ajuster la régression pour les valeurs de poids (qui est une variable dépendante) à l'aide des valeurs de hauteur (qui est une variable indépendante).

  • Dans la feuille de calcul Excel, cliquez sur Analyse des données (présente sous Groupe d' analyse ) sous Données.

  • Recherchez la régression . Sélectionnez-le et appuyez sur ok.

  • Utilisez les entrées suivantes sous le volet de régression qui s'ouvre.

  • Plage d'entrée Y : sélectionnez les cellules qui contiennent votre variable dépendante (dans cet exemple B1: B11)

  • Input X Range : sélectionnez les cellules qui contiennent votre variable indépendante (dans cet exemple A1: A11).

  • Cochez la case intitulée Étiquettes si vos données ont des noms de colonnes (dans cet exemple, nous avons des noms de colonnes).

  • Le niveau de confiance est défini à 95% par défaut, qui peut être modifié selon les besoins des utilisateurs.

  • Sous Options de sortie, vous pouvez personnaliser où vous souhaitez voir la sortie de l'analyse de régression dans Excel. Dans ce cas, nous voulons voir la sortie sur la même feuille. Par conséquent, étant donné la portée en conséquence.

  • Sous l'option Résiduels, vous avez des entrées optionnelles comme les résidus, les parcelles résiduelles, les résidus standardisés, les tracés d'ajustement de ligne que vous pouvez sélectionner selon vos besoins. Dans ce cas, cochez la case Résiduels afin que nous puissions voir la dispersion entre les valeurs prévues et réelles.

  • Sous l' option Probabilité normale, vous pouvez sélectionner des tracés de probabilité normale qui peuvent vous aider à vérifier la normalité des prédicteurs. Cliquez sur OK .

  • Excel calculera pour vous une analyse de régression en une fraction de secondes.

Jusqu'ici, c'était facile et pas si logique. Cependant, interpréter cette sortie et en tirer des informations précieuses est une tâche délicate.

Une partie importante de toute cette sortie est le carré R / le carré R ajusté sous le tableau SOMMAIRE DE SORTIE. Qui fournit des informations sur la qualité de notre modèle. Dans ce cas, la valeur R au carré est 0, 9547. Ce qui signifie que le modèle a une précision de 95, 47% (bon ajustement). Ou dans une autre langue, les informations sur la variable Y sont expliquées à 95, 47% par la variable X.

L'autre partie importante de l'ensemble de la production est un tableau de coefficients. Il donne des valeurs de coefficients qui peuvent être utilisées pour construire le modèle pour les prédictions futures.

Maintenant, notre équation de régression pour la prédiction devient:

Poids = 0, 6746 * Hauteur - 38, 45508 (La valeur de pente pour la hauteur est de 0, 6746… et l'interception est de -38, 45508…)

Avez-vous obtenu ce que vous avez défini? Vous avez défini une fonction dans laquelle il ne vous reste plus qu'à mettre la valeur de Hauteur et vous obtiendrez la valeur Poids.

# 2 - Analyse de régression à l'aide de Scatterplot avec Trendline dans Excel

Maintenant, nous verrons comment, dans Excel, nous pouvons ajuster une équation de régression sur un nuage de points lui-même.

  • Sélectionnez l'ensemble de vos données à deux colonnes (y compris les en-têtes).
  • Cliquez sur Insérer et sélectionnez Nuage de points sous la section graphiques, comme indiqué dans l'image ci-dessous.

  • Voir le graphique de sortie.

  • Maintenant, nous devons avoir une ligne de régression la moins carrée sur ce graphique. Pour ajouter cette ligne, cliquez avec le bouton droit sur l'un des points de données du graphique et sélectionnez l'option Ajouter une ligne de tendance .

  • Cela vous permettra d'avoir une ligne de tendance du moindre carré de régression comme ci-dessous.

  • Sous l'option Format Trendline, cochez la case pour afficher l'équation sur le graphique.

  • Il vous permet de voir l'équation de la ligne de régression la moins carrée sur le graphique.

Il s'agit de l'équation à l'aide de laquelle nous pouvons prédire les valeurs de poids pour tout ensemble donné de valeurs de hauteur.

Choses à retenir sur l'analyse de régression dans Excel

  • Vous pouvez modifier la disposition de la ligne de tendance sous l'option Formater la ligne de tendance dans le nuage de points.
  • Il est toujours recommandé de consulter les graphiques résiduels pendant que vous effectuez une analyse de régression à l'aide de Data Analysis ToolPak dans Excel. Il vous donne une meilleure compréhension de la répartition des valeurs Y réelles et des valeurs X estimées.
  • La régression linéaire simple dans Excel n'a pas besoin d'ANOVA et de carré R ajusté pour vérifier. Ces caractéristiques peuvent être prises en considération pour la régression linéaire multiple. Ce qui dépasse le cadre de cet article.

Articles recommandés

Cela a été un guide pour l'analyse de régression dans Excel. Ici, nous discutons de la façon de faire une analyse de régression dans Excel avec des exemples Excel et un modèle Excel téléchargeable. Vous pouvez également consulter nos autres articles suggérés -

  1. Outil Excel pour l'analyse des données
  2. Calculer l'ANOVA dans Excel
  3. Comment trouver les moyennes mobiles d'Excel
  4. Exemples de test Z dans Excel

Catégorie:

Conseils Excel