Excel : comment tracer une courbe de régression

Ce tutoriel explique les bases de l'analyse de régression et montre diverses façons d'effectuer une régression linéaire dans Excel.

La régression linéaire est un graphique de données qui représente la relation linéaire entre une variable indépendante et une variable dépendante. Il est généralement utilisé pour montrer visuellement la force de la relation et la dispersion des résultats - le tout dans le but d'expliquer le comportement de la variable dépendante.

Imaginons que nous voulions tester la force de la relation entre la quantité de crème glacée consommée et l'obésité. Nous prendrons la variable indépendante, la quantité de crème glacée, et la relierons à la variable dépendante, l'obésité, pour voir s'il y a une relation. Étant donné qu'une régression est un affichage graphique de cette relation, plus la variabilité des données est faible, plus la relation est forte et plus l'ajustement à la droite de régression est serré.

Table des matières

Analyse de régression dans Excel

Équation de régression linéaire

Comment effectuer une régression linéaire dans Excel avec Analysis ToolPak ?

Activer le complément Analysis ToolPak

Exécuter une analyse de régression

Interpréter les résultats de l’analyse de régression

Analyse de régression: statistique de la régression

Analyse de régression: analyse de variance

Analyse de régression: coefficients

Analyse de régression: résidus

Faire un graphique de régression linéaire dans Excel

Comment effectuer une régression dans Excel à l'aide des formules ?

Analyse de régression dans Excel

Dans la modélisation statistique, l'analyse de régression est utilisée pour estimer les relations entre deux ou plusieurs variables.

Techniquement, un modèle d'analyse de régression est basé sur la somme des carrés, qui est une manière mathématique de trouver la dispersion des points de données. Le but d'un modèle est d'obtenir la plus petite somme de carrés possible et de tracer une ligne qui se rapproche le plus des données.

Par exemple, prenons les niveaux d’obésité chez une personne normal pour les 8 derniers mois et découvrons la quantité de crème glacée consommée pour la même période. En traçant ces informations sur un graphique, la ligne de régression montrera la relation entre la variable indépendante (quantité de crème glacée consommée) et la variable dépendante (obésité):

Équation de régression linéaire

Mathématiquement, une régression linéaire est définie par cette équation :

y = bx + a + ?

Où:

• ·x est une variable indépendante.
• ·y est une variable dépendante.
• ·a est l'ordonnée à l'origine. Il s’agit de la valeur moyenne attendue de y lorsque toutes les variables xsont égales à 0. Sur un graphique de régression, c'est le point où la ligne croise l'axe Y.
• ·b est la pente d'une droite de régression, qui est le taux de changement pour y lorsque x change.
• ·? est le terme d'erreur aléatoire, qui est la différence entre la valeur réelle d'une variable dépendante et sa valeur prédite.

L'équation de régression linéaire a toujours un terme d'erreur. En effet, dans la vie réelle, les prédicteurs ne sont jamais parfaitement précis. Cependant, certains programmes, dont Excel, effectuent le calcul du terme d'erreur en arrière-plan. Donc, dans Excel, vous effectuez une régression linéaire en utilisant la méthode des moindres carrés et recherchez les coefficients a et b tels que:

y = bx + a

Pour notre exemple, l'équation de régression linéaire prend la forme suivante :

Obésité = b * Crème glacée consommée + a

Il existe plusieurs façons de trouver a et b. Les trois principales méthodes pour effectuer une analyse de régression linéaire dans Excel sont les suivantes:

• ·Outil de régression inclus avec « AnalysisToolPak ».
• ·Diagramme de dispersion avec une ligne de tendance.
• ·Utilisation des formules.

Vous trouverez ci-dessous les instructions détaillées sur l'utilisation de chaque méthode.

Comment effectuer une régression linéaire dans Excel avec Analysis ToolPak ?

La première étape de l'exécution d'une analyse de régression dans Excel consiste à vérifier que le plugin Excel gratuit Data Analysis ToolPak est installé. Ce plugin facilite le calcul d'une gamme de statistiques. Il n'est pas nécessaire pour tracer une ligne de régression linéaire, mais il simplifie la création de tableaux de statistiques.

Pour vérifier s'il est installé, allez sur l’onglet « Données ». Si « Utilitaire d’analyse » est une option, la fonction est installée et prête à l'emploi. S'il n'est pas installé, vous pouvez l’activer.

Activer le complément Analysis ToolPak

Analysis ToolPak est disponible dans toutes les versions d'Excel, mais n'est pas activé par défaut. Vous devez donc l'activer manuellement. Voici comment faire :

1. Accédez à votre programme Excel, puis cliquez sur Fichier > Options.
2. Dans la boîte de dialogue « Options Excel », sélectionnez « Compléments »dans la barre latérale gauche.
3. Assurez-vous que les « compléments Excel »sont sélectionnés dans la zone Gérer, puis cliquez sur « Atteindre ».

4. Dans la boîte de dialogue Compléments, cochez AnalysisToolpak et cliquez sur OK.

Cela ajoutera les outils d'analyse de données à l'onglet Données de votre ruban Excel.

Exécuter une analyse de régression

Dans cet exemple, nous allons faire une simple régression linéaire dans Excel. Nous avons une liste des quantités moyennes de crème glacée consommée pendant les 8 derniers mois dans la colonne B, qui est notre variable indépendante (prédicteur), et le niveau d’obésité correspondant dans la colonne C, qui est la variable dépendante. Bien sûr, de nombreux autres facteurs peuvent affecter l’obésité, mais pour l'instant, nous nous concentrons uniquement sur ces deux variables.

Avec Analysis Toolpak activé, procédez comme suit pour effectuer une analyse de régression dans Excel :

1. Sous l'onglet Données, dans le groupe Analyse, cliquez sur le bouton Utilitaire d’analyse.

2. Sélectionnez Régression linéaireet cliquez sur OK.

3. Dans la boîte de dialogue Régression, configurez les paramètres suivants :

• ·Sélectionnez la plage d'entrée Y, qui est votre variable dépendante. Dans notre cas, ce sont les niveaux d’obésité (C1: C9).
• ·Sélectionnez la plage d'entrée X, c'est-à-dire votre variable indépendante. Dans cet exemple, il s'agit des quantités moyennes consommées de la crème glacée (B1: B9).

Si vous créez un modèle de régression multiple, sélectionnez deux colonnes adjacentes ou plus avec différentes variables indépendantes.

4. Cochez la case « Intitulé présent » si vous avez des en-têtes en haut de vos plages X et Y.
5. Choisissez votre option de sortie préférée, Insérerune nouvelle feuille dans notre cas.
6. Si vous le souhaitez, cochez la case Résiduspour obtenir la différence entre les valeurs prévues et les valeurs réelles.

7. Cliquez sur OKet observez la sortie d'analyse de régression créée par Excel :

Interpréter les résultats de l’analyse de régression

Comme vous venez de le voir, l'exécution de la régression dans Excel est facile car tous les calculs sont préformés automatiquement. L'interprétation des résultats est un peu plus délicate car vous devez savoir ce qui se cache derrière chaque numéro. Vous trouverez ci-dessous une ventilation de 4 parties principales de la sortie de l'analyse de régression.

Analyse de régression : statistique de la régression

Cette partie vous indique dans quelle mesure l'équation de régression linéaire calculée correspond à vos données source.

Voici ce que signifie chaque élément des statistiques :

1. Coefficient de détermination multiple :c'est le coefficient de corrélation qui mesure la force d'une relation linéaire entre deux variables. Le coefficient de corrélation peut être n'importe quelle valeur entre -1 et 1, et sa valeur absolue indique la force de la relation. Plus la valeur absolue est élevée, plus la relation est forte:

• ·1 signifie : une relation positive forte.
• ·-1 signifie : une forte relation négative.
• ·0 signifie : pas de relation du tout.

2. Coefficient de détermination multipleR Carré : c'est le coefficient de détermination, qui est utilisé comme indicateur de la qualité de l'ajustement. Il montre combien de points tombent sur la droite de régression. La valeur R^2 est calculée à partir de la somme totale des carrés. Plus précisément, c'est la somme des écarts au carré des données originales par rapport à la moyenne. Dans notre exemple, R^2 est de 0,99, ce qui est très bon. Cela signifie que 99% de nos valeurs correspondent au modèle d'analyse de régression. En d'autres termes, 99% des variables dépendantes (valeurs y) sont expliquées par les variables indépendantes (valeurs x). En règle générale, R au carré de 95% ou plus est considéré comme un bon ajustement.
3. Coefficient de détermination multipleR ajusté : il s'agit du R carré ajusté pour le nombre de variables indépendantes dans le modèle. Vous pouvez utiliser cette valeur au lieu du R carré pour l'analyse de régression multiple.
4. Erreur-type :il s'agit d'une autre mesure de la qualité de l'ajustement qui montre la précision de votre analyse de régression. Plus le nombre est petit, plus vous pouvez être sûr de votre équation de régression. Alors que R^2 représente le pourcentage de la variance des variables dépendantes qui est expliqué par le modèle, l'erreur standard est une mesure absolue qui montre la distance moyenne entre les points de données et la ligne de régression.
5. Observations : il s'agit simplement du nombre d'observations dans votre modèle.

Analyse de régression: analyse de variance

La deuxième partie de la sortie est l'analyse de la variance :

Fondamentalement, cette partie divise la somme des carrés en composants individuels qui fournissent des informations sur les niveaux de variabilité dans votre modèle de régression:

• ·Degré de liberté :le nombre de degrés de liberté associés aux sources de variance.
• ·Somme des carrés(SS) :Plus le SS résiduel est petit par rapport au SS total, mieux votre modèle correspond aux données.
• ·Moyenne des carrées.
• ·F : utilisé pour tester la signification globale du modèle.
• ·La Valeur critique de F.

La partie analyse de variance est rarement utilisée pour une simple analyse de régression linéaire dans Excel, mais vous devriez certainement regarder de près le dernier composant. La valeur critique de F donne une idée de la fiabilité (statistiquement significative) de vos résultats. Si la signification F est inférieure à 0,05 (5%), votre modèle est OK. Si elle est supérieure à 0,05, vous feriez probablement mieux de choisir une autre variable indépendante.

Analyse de régression: coefficients

Cette section du tableau vous donne des informations très spécifiques sur les composants que vous avez choisi d'intégrer dans votre analyse de données. Par conséquent, la première colonne indiquera quelque chose de différent, selon les données que vous mettez dans la feuille de calcul. Par exemple, elle pourrait indiquer «taille», «revenu» ou toutes les variables que vous avez choisies.

Les colonnes sont :

• ·Coefficients:vous donne l'estimation des moindres carrés.
• ·Erreur-type:estimation des moindres carrés de l'erreur-type.
• ·Statistique T:la statistique T pour l'hypothèse nulle par rapport à l'hypothèse alternative.
• ·Probabilité:vous donne la valeur p pour le test d'hypothèse.
• ·Limite inférieure pour seuil de confiance =95%:limite inférieure de l'intervalle de confiance.
• ·Limite supérieure pour seuil de confiance =95%:limite supérieure de l'intervalle de confiance.

Analyse de régression: résidus

Si vous comparez les pourcentages estimé et réel d’obésité correspondant à la consommation mensuelle de 82g de crème glacée, vous verrez que ces chiffres sont différents:

• ·Estimé: 12,68 (calculé ci-dessus).
• ·Réel: 12,5 (ligne 2 des données source).

Vous vous demandez pourquoi cette différence ? Eh bien, parce que les variables indépendantes ne sont jamais des prédicteurs parfaits des variables dépendantes. Ainsi, les résidus peuvent vous aider à comprendre à quelle distance les valeurs réelles sont éloignées des valeurs prédites:

Pour le premier point de données (consommation de 82g de crème glacée), le résidu est d'environ -0.18. Donc, nous ajoutons ce nombre à la valeur prédite et obtenons la valeur réelle: 12,68 - 0,18 = 12,5.

Faire un graphique de régression linéaire dans Excel

Si vous devez visualiser rapidement la relation entre les deux variables, dessinez un graphique de régression linéaire. Voici comment faire :

1. Sélectionnez les deux colonnes avec vos données, y compris les en-têtes.
2. Dans l'onglet « Insertion », dans le groupe « Graphiques », cliquez sur l'icône « Insérer un graphique en nuages de points(X,Y) ou en bulles » et sélectionnez la miniature « Nuages de points » (la première).

3. Cela insérera un nuage de points dans votre feuille de calcul, qui ressemblera à celui-ci :

4. Maintenant, nous devons tracer la droite de régression des moindres carrés. Pour le faire, faites un clic droit sur n'importe quel point et choisissez « Ajouter une courbe de tendance… »dans le menu contextuel.

5. Dans le volet droit, sélectionnez la forme de ligne de tendance linéaireet, éventuellement, cochez « Afficher l'équation sur le graphique » pour obtenir votre formule de régression:

6. Basculez vers l'onglet « Remplissage et ligne» et personnalisez la ligne à votre guise. Par exemple, vous pouvez choisir une couleur de ligne différente et utiliser une ligne continue au lieu d'une ligne pointillée (sélectionnez ligne « Uni » dans la zone « Type de tiret »):

À ce stade, votre graphique ressemble déjà à un graphique de régression décent:

Néanmoins, vous souhaiterez peut-être apporter quelques améliorations supplémentaires:

7. Faites glisser l'équation où vous souhaitez la placer sur le graphique.
8. Ajoutez des titres d'axes. Pour ce faire, cliquez sur le graphique et puis cliquez sur onglet « Création » ® « Ajouter un élément de graphique »® « Titres des axes ».

Comment effectuer une régression dans Excel à l'aide des formules ?

Pour obtenir l'ordonnée à l'origine et la pente d'une droite de régression, utilisez la fonction DROITEREG dans sa forme la plus simple: fournissez une plage de valeurs dépendantes pour l'argument de y_connus et une plage de valeurs indépendantes pour l'argument de x_connus. Les deux derniers arguments peuvent être définis sur VRAI ou omis.

Par exemple, avec les valeurs y (Pourcentages d’obésité) dans C2: C9 et les valeurs x (crème glacée consommée) dans B2: B9, notre formule de régression linéaire est aussi simple que:

=DROITEREG(C2:C9;B2:B9)

Pour saisir la formule correctement dans votre feuille de calcul, sélectionnez deux cellules adjacentes dans la même ligne, E2: F2 dans cet exemple, tapez la formule et appuyez sur Ctrl + Shift + Entrée pour terminer.

La formule renverra le coefficient de pente dans la première cellule (E2) et la constante d'interception dans la deuxième cellule (F2):

La pente est d'environ 0,14 (arrondie à deux décimales). Cela signifie que lorsque x augmente de 1, Y augmente de 0,14.

L'ordonnée à l'origine 0,80. Il s'agit de la valeur attendue de Y lorsque x = 0. S'il est tracé sur un graphique, il s'agit de la valeur à laquelle la ligne de régression croise l'axe des y.

Fournissez les valeurs ci-dessus à une simple équation de régression linéaire et vous obtiendrez la formule suivante pour prédire le pourcentage d’obésité en fonction de la quantité de crème glacée consommée:

y = 0,14 * x + 0,80

Par exemple, si vous consommez 50g de crème glacée, vous êtes censé avoir 7,8% d’obésité:

0,14 * 50 + 0,80 = 7,8

Les valeurs de pente et d'interception peuvent également être obtenues séparément en utilisant la fonction correspondante ou en imbriquant la formule DROITEREG dans INDEX:

Pente :

=PENTE(C2:C9 ; B2:B9)

Ou :

=INDEX(DROITEREG(C2:C9; B2:B9); 1)

Interception :

=ORDONNEE.ORIGINE(C2:C9; B2:B9)

Ou :

=INDEX(DROITEREG(C2:C9; B2:B9); 2)

Comme le montre la capture d'écran ci-dessous, les trois formules donnent en fait les mêmes résultats :

Télécharger le fichier excel de ce tutoriel