Le tutoriel explique les bases de l’analyse de régression et montre quelques façons différentes de faire une régression linéaire dans Excel.

Imaginez ceci: on vous fournit beaucoup de données différentes et on vous demande de prédire les chiffres de ventes de l’année prochaine pour votre entreprise. Vous avez découvert des dizaines, peut-être même des centaines, de facteurs qui peuvent éventuellement affecter les chiffres. Mais comment savez-vous lesquels sont vraiment importants? Exécutez une analyse de régression dans Excel. Cela vous donnera une réponse à cela et à bien d’autres questions: Quels facteurs comptent et lesquels peuvent être ignorés? Dans quelle mesure ces facteurs sont-ils liés les uns aux autres? Et dans quelle mesure pouvez-vous être certain des prédictions?

• Analyse de régression dans Excel
• Régression linéaire dans Excel avec Analysis ToolPak
• Dessinez un graphique de régression linéaire
• Analyse de régression dans Excel avec des formules

Analyse de régression dans Excel – les bases

Dans la modélisation statistique, l’analyse de régression est utilisée pour estimer les relations entre deux variables ou plus:

La variable dépendante (ou variable critère) est le facteur principal que vous essayez de comprendre et de prédire.

Les variables indépendantes (ou variables explicatives ou prédicteurs) sont les facteurs qui peuvent influencer la variable dépendante.

L’analyse de régression vous aide à comprendre comment la variable dépendante change lorsque l’une des variables indépendantes varie et permet de déterminer mathématiquement laquelle de ces variables a vraiment un impact.

Techniquement, un modèle d’analyse de régression est basé sur la somme des carrés, ce qui est un moyen 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.

En statistique, ils font la différence entre une régression linéaire simple et multiple. La régression linéaire simple modélise la relation entre une variable dépendante et une variable indépendante à l’aide d’une fonction linéaire. Si vous utilisez deux variables explicatives ou plus pour prédire la variable dépendante, vous traitez de régression linéaire multiple. Si la variable dépendante est modélisée comme une fonction non linéaire car les relations de données ne suivent pas une ligne droite, utilisez plutôt la régression non linéaire. L’accent de ce tutoriel sera mis sur une simple régression linéaire.

À titre d’exemple, prenons les chiffres des ventes de parapluies pour les 24 derniers mois et découvrons les précipitations mensuelles moyennes pour la même période. Tracez ces informations sur un graphique, et la ligne de régression démontrera la relation entre la variable indépendante (précipitations) et la variable dépendante (ventes parapluie):

Équation de régression linéaire

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

Où :

• x est une variable indépendante .
• y est une variable dépendante.
• a est l’ordonnée à l’origine, qui est la valeur moyenne attendue de y lorsque toutes les variables x sont é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 comme 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 car, dans la vie réelle, les prédicteurs ne sont jamais parfaitement précis. Cependant, certains programmes, y compris Excel, effectuent le calcul du terme d’erreur en coulisses. Ainsi, 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:

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

Umbrellas sold = b * rainfall + a

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

• Outil de régression inclus avec Analysis ToolPak
• Graphique en nuage de points avec une ligne de tendance
• Formule de régression linéaire

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

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

Cet exemple montre comment exécuter une régression dans Excel à l’aide d’un outil spécial inclus avec le complément Analysis ToolPak.

Activer le module complémentaire Analysis ToolPak

Analysis ToolPak est disponible dans toutes les versions d’Excel 2019 à 2003 mais n’est pas activé par défaut. Vous devez donc l’allumer manuellement. Voici comment:

1. Dans votre Excel, cliquez sur Fichier > Options.
2. Dans la boîte de dialogue Options Excel, sélectionnez Compléments dans la barre latérale gauche, assurez-vous que les compléments Excel sont sélectionnés dans la zone Gérer, puis cliquez sur Go.
   
3. Dans la boîte de dialogue Compléments, cochez Analysis Toolpak 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 régression linéaire simple dans Excel. Ce que nous avons, c’est une liste des précipitations mensuelles moyennes des 24 derniers mois dans la colonne B, qui est notre variable indépendante (prédicteur), et le nombre de parapluies vendus dans la colonne C, qui est la variable dépendante. Bien sûr, il y a beaucoup d’autres facteurs qui peuvent affecter les ventes, mais pour l’instant nous nous concentrons uniquement sur ces deux variables:

Avec Analysis Toolpak ajouté activé, effectuez ces étapes pour effectuer une analyse de régression dans Excel:

1. Dans l’onglet Données, dans le groupe Analyse, cliquez sur le bouton Analyse de données.
   
2. Sélectionnez Régression et cliquez sur OK.
   
3. Dans la boîte de dialogue de régression, configurez les paramètres suivants :
   • Sélectionnez la plage Y d’entrée, qui est votre variable dépendante. Dans notre cas, il s’agit de ventes parapluie (C1: C25).
   • Sélectionnez la plage X d’entrée, c’est-à-dire votre variable indépendante. Dans cet exemple, il s’agit de la pluviométrie mensuelle moyenne (B1:B25).

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

   • Cochez la case Étiquettes s’il y a des en-têtes en haut de vos plages X et Y.
   • Choisissez votre option de sortie préférée, une nouvelle feuille de calcul dans notre cas.
   • En option, cochez la case Résidus pour obtenir la différence entre les valeurs prédites et réelles.
     
4. Cliquez sur OK et observez la sortie d’analyse de régression créée par Excel.

Interpréter la sortie 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.

Sortie d’analyse de régression: Sortie sommaire

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

Voici ce que chaque information signifie:

Multiple R. 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 comprise entre -1 et 1, et sa valeur absolue indique la force de la relation. Plus la valeur absolue est grande, plus la relation est forte:

• 1 signifie une relation positive forte
• -1 signifie une relation négative forte
• 0 signifie aucune relation du tout

R 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 ligne de régression. La valeur R2 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 d’origine par rapport à la moyenne.

Dans notre exemple, R2 vaut 0,91 (arrondi à 2 chiffres), ce qui est une bonne fée. Cela signifie que 91% de nos valeurs correspondent au modèle d’analyse de régression. En d’autres termes, 91% des variables dépendantes (valeurs y) sont expliquées par les variables indépendantes (valeurs x). Généralement, un R au carré de 95% ou plus est considéré comme un bon ajustement.

Carré R ajusté. C’est le carré R ajusté pour le nombre de variables indépendantes dans le modèle. Vous voudrez utiliser cette valeur au lieu de R carré pour l’analyse de régression multiple.

Erreur standard. Il s’agit d’une autre mesure d’ajustement qui montre la précision de votre analyse de régression – plus le nombre est petit, plus vous pouvez être certain de votre équation de régression. Alors que R2 représente le pourcentage de la variance des variables dépendantes expliquée par le modèle, l’erreur type est une mesure absolue qui montre la distance moyenne à laquelle les points de données se trouvent par rapport à la ligne de régression.

Observations. C’est simplement le nombre d’observations dans votre modèle.

Sortie de l’analyse de régression: ANOVA

La deuxième partie de la sortie est l’Analyse de la variance (ANOVA):

Fondamentalement, elle divise la somme des carrés en composantes individuelles qui donnent des informations sur les niveaux de variabilité dans votre modèle de régression:

• df est le nombre de degrés de liberté associés aux sources de variance.
• SS est la somme des carrés. Plus les SS résiduels sont petits par rapport aux SS totaux, mieux votre modèle s’adapte aux données.
• MS est le carré moyen.
• F est la statistique F, ou F-test pour l’hypothèse nulle. Il est utilisé pour tester la signification globale du modèle.
• Signification F est la valeur P de F.

La partie ANOVA est rarement utilisée pour une analyse de régression linéaire simple dans Excel, mais vous devriez certainement regarder de près le dernier composant. La valeur de signification 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.

Sortie de l’analyse de régression: coefficients

Cette section fournit des informations spécifiques sur les composantes de votre analyse :

La composante la plus utile de cette section est les coefficients. Il vous permet de construire une équation de régression linéaire dans Excel:

Pour notre ensemble de données, où y est le nombre de parapluies vendus et x est une pluviométrie mensuelle moyenne, notre formule de régression linéaire va comme suit:

Y = Rainfall Coefficient * x + Intercept

Équipé de valeurs a et b arrondies à trois décimales, il se transforme en:

Y=0.45*x-19.074

Par exemple, avec une pluviométrie mensuelle moyenne égale à 82 mm, les ventes de parapluies seraient d’environ 17,8:

0.45*82-19.074=17.8

De la même manière, vous pouvez savoir combien de parapluies seront vendus avec toute autre pluviométrie mensuelle (variable x) que vous spécifiez .

Résultat de l’analyse de régression: résidus

Si vous comparez le nombre estimé et réel de parapluies vendus correspondant à la pluviométrie mensuelle de 82 mm, vous verrez que ces chiffres sont légèrement différents:

• Estimé: 17.8 (calculé ci-dessus)
• Réel: 15 (ligne 2 des données source)

Pourquoi la différence? Parce que les variables indépendantes ne sont jamais des prédicteurs parfaits des variables dépendantes. Et les résidus peuvent vous aider à comprendre à quelle distance les valeurs réelles sont des valeurs prédites:

Comment créer 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. C’est très facile! Voici comment :

1. Sélectionnez les deux colonnes contenant vos données, y compris les en-têtes.
2. Dans l’onglet Encart, dans le groupe Chats, cliquez sur l’icône du graphique en nuage de points et sélectionnez la vignette en nuage de points (la première):
   

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

3. Maintenant, nous besoin de tracer la ligne de régression des moindres carrés. Pour ce faire, faites un clic droit sur n’importe quel point et choisissez Ajouter une ligne de tendance… dans le menu contextuel.
   
4. Dans le volet de droite, sélectionnez la forme de la ligne de tendance linéaire et, éventuellement, vérifiez l’équation d’affichage sur le graphique pour obtenir votre formule de régression:
   

   Comme vous le remarquerez peut-être, l’équation de régression qu’Excel a créée pour nous est la même que la formule de régression linéaire que nous avons construite en fonction des coefficients de sortie.

5. Passez à l’onglet Fill& 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 continue dans la zone Type de tiret):
   

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

Encore, vous voudrez peut-être apporter quelques améliorations supplémentaires:

• Faites glisser l’équation où bon vous semble.
• Ajouter des titres d’axes (bouton Éléments de graphique > Titres d’axes).
• Si vos points de données commencent au milieu de l’axe horizontal et / ou vertical comme dans cet exemple, vous voudrez peut-être vous débarrasser de l’espace blanc excessif. L’astuce suivante explique comment procéder : Mettez à l’échelle les axes du graphique pour réduire les espaces blancs.

  Et voici à quoi ressemble notre graphique de régression amélioré:
  

  Note importante! Dans le graphique de régression, la variable indépendante doit toujours être sur l’axe des abscisses et la variable dépendante sur l’axe des ordonnées. Si votre graphique est tracé dans l’ordre inverse, échangez les colonnes de votre feuille de calcul, puis dessinez à nouveau le graphique. Si vous n’êtes pas autorisé à réorganiser les données source, vous pouvez basculer les axes X et Y directement dans un graphique.

Comment effectuer une régression dans Excel à l’aide de formules

Microsoft Excel dispose de quelques fonctions statistiques qui peuvent vous aider à effectuer une analyse de régression linéaire, telles que LINEST, SLOPE, INTERCPET et CORREL.

La fonction LINEST utilise la méthode de régression par les moindres carrés pour calculer une ligne droite qui explique le mieux la relation entre vos variables et renvoie un tableau décrivant cette ligne. Vous trouverez l’explication détaillée de la syntaxe de la fonction dans ce tutoriel. Pour l’instant, faisons simplement une formule pour notre exemple d’ensemble de données:

=LINEST(C2:C25, B2:B25)

Parce que la fonction LINEST renvoie un tableau de valeurs, vous devez l’entrer sous forme de formule de tableau. Sélectionnez deux cellules adjacentes dans la même ligne, E2:F2 dans notre cas, tapez la formule et appuyez sur Ctrl + Maj + Entrée pour la compléter.

La formule renvoie le coefficient b (E1) et la constante a (F1) pour l’équation de régression linéaire déjà familière:

y = bx + a

Si vous évitez d’utiliser des formules de tableau dans vos feuilles de calcul, vous pouvez calculer a et b individuellement avec des formules régulières:

>

Obtenez l’interception Y(a):

=INTERCEPT(C2:C25, B2:B25)

Obtenez la pente (b):

=SLOPE(C2:C25, B2:B25)

De plus, vous pouvez trouver le coefficient de corrélation (Multiple R dans la sortie du résumé de l’analyse de régression) qui indique la force des deux variables liées l’une à l’autre:

=CORREL(B2:B25,C2:C25)

La capture d’écran suivante montre toutes ces formules de régression Excel en action:

C’est ainsi que vous effectuez une régression linéaire dans Excel. Cela dit, veuillez garder à l’esprit que Microsoft Excel n’est pas un programme statistique. Si vous devez effectuer une analyse de régression au niveau professionnel, vous pouvez utiliser des logiciels ciblés tels que XLSTAT, RegressIt, etc.

Téléchargements disponibles:

Pour regarder de plus près nos formules de régression linéaire et d’autres techniques discutées dans ce tutoriel, vous pouvez télécharger notre exemple d’analyse de régression dans le classeur Excel.

• Comment utiliser le solveur dans Excel avec des exemples
• Comment calculer l’intérêt composé dans Excel
• Comment calculer le TCAC (taux de croissance annuel composé) dans Excel