Comment faire une régression linéaire sur Excel et interpréter les résultats ?

Comprendre la régression linéaire avec Excel

La régression linéaire est une méthode statistique utilisée pour modéliser la relation entre une variable dépendante (celle que l'on cherche à prédire) et une ou plusieurs variables indépendantes (celles que l'on utilise pour la prédiction). En d'autres termes, on cherche à trouver la droite (ou l'hyperplan dans le cas de plusieurs variables) qui s'ajuste le mieux aux données observées.

Qu'est-ce qu'une régression linéaire simple ?

Dans une régression linéaire simple, on a une seule variable indépendante. L'objectif est de trouver l'équation de la droite qui relie cette variable indépendante à la variable dépendante. Cette équation est de la forme : y = ax + b, où :

• y est la variable dépendante
• x est la variable indépendante
• a est la pente de la droite (le coefficient de régression)
• b est l'ordonnée à l'origine (l'intercept)

Qu'est-ce qu'une régression linéaire multiple ?

Dans une régression linéaire multiple, on a plusieurs variables indépendantes. L'équation devient alors : y = a1x1 + a2x2 + ... + anxn + b, où :

• y est la variable dépendante
• x1, x2, ..., xn sont les variables indépendantes
• a1, a2, ..., an sont les coefficients de régression pour chaque variable indépendante
• b est l'ordonnée à l'origine

Réaliser une régression linéaire simple sur Excel : Étape par étape

Voici comment effectuer une régression linéaire simple sur Excel en utilisant l'outil d'analyse de données.

1. Préparer vos données

Organisez vos données dans deux colonnes distinctes sur votre feuille Excel. La première colonne contiendra les valeurs de la variable indépendante (x), et la seconde colonne contiendra les valeurs de la variable dépendante (y). Assurez-vous que les données sont numériques.

Exemple :

2. Activer l'outil d'analyse de données

Si l'onglet "Données" ne contient pas le groupe "Analyse", vous devez activer l'outil d'analyse de données.

1. Cliquez sur l'onglet "Fichier" > "Options" > "Compléments".
2. Dans la liste "Gérer", sélectionnez "Compléments Excel" et cliquez sur "Atteindre...".
3. Cochez la case "Utilitaire d'analyse" et cliquez sur "OK". L'onglet "Données" devrait maintenant afficher le groupe "Analyse".

3. Lancer l'analyse de régression

1. Cliquez sur l'onglet "Données" et sélectionnez "Analyse de données" dans le groupe "Analyse".
2. Dans la boîte de dialogue "Analyse de données", sélectionnez "Régression" et cliquez sur "OK".
3. Dans la boîte de dialogue "Régression" :
   • Plage Y en entrée : Sélectionnez la plage de cellules contenant les valeurs de votre variable dépendante (y). Dans notre exemple, ce serait $B$1:$B$6 (en supposant que les données commencent à la ligne 1 et incluent les en-têtes).
   • Plage X en entrée : Sélectionnez la plage de cellules contenant les valeurs de votre variable indépendante (x). Dans notre exemple, ce serait $A$1:$A$6.
   • Libellés : Cochez cette case si la première ligne de vos plages contient des libellés (noms des colonnes). Dans notre exemple, nous avons des libellés, donc nous cocherons cette case.
   • Niveau de confiance : Laissez la valeur par défaut (95 %) sauf si vous avez une raison spécifique de la modifier.
   • Plage de sortie : Sélectionnez une cellule où vous souhaitez que les résultats de l'analyse soient affichés. Vous pouvez également choisir de créer une nouvelle feuille de calcul ou un nouveau classeur.
   • Résidus : Cochez cette case pour afficher les résidus (la différence entre les valeurs observées et les valeurs prédites par le modèle).
   • Graphique des résidus : Cochez cette case pour afficher un graphique des résidus, utile pour vérifier la validité du modèle.
   • Courbe de régression ajustée : Cochez cette case pour afficher un graphique de la courbe de régression ajustée aux données.
   • Graphiques de probabilité normale : Cochez cette case si vous souhaitez évaluer la normalité des résidus.
4. Cliquez sur "OK" pour lancer l'analyse. Excel générera un rapport de régression dans la plage de sortie que vous avez spécifiée.

4. Interpréter les résultats de la régression

Le rapport de régression généré par Excel contient plusieurs informations importantes. Voici les principales à analyser :

• R multiple : C'est le coefficient de corrélation, qui mesure la force de la relation linéaire entre les variables. Une valeur proche de 1 indique une forte corrélation positive, une valeur proche de -1 indique une forte corrélation négative, et une valeur proche de 0 indique une faible corrélation.
• R carré : C'est le coefficient de détermination, qui représente la proportion de la variance de la variable dépendante qui est expliquée par la variable indépendante. Par exemple, un R carré de 0,80 signifie que 80 % de la variation des ventes est expliquée par la publicité. Plus le R carré est élevé, mieux le modèle s'ajuste aux données.
• R carré ajusté : C'est une version ajustée du R carré qui tient compte du nombre de variables indépendantes dans le modèle. Il est particulièrement utile dans les régressions linéaires multiples pour éviter de surestimer la qualité du modèle.
• Erreur type : C'est une mesure de la précision des estimations des coefficients de régression. Plus l'erreur type est faible, plus les estimations sont précises.
• Observations : Le nombre d'observations utilisées dans l'analyse.
• Analyse de la variance (ANOVA) : Ce tableau fournit des informations sur la significativité statistique du modèle. Le plus important est la valeur de p (Sig.), qui indique la probabilité d'obtenir les résultats observés si le modèle n'était pas significatif. Si la valeur de p est inférieure à un seuil de significativité (généralement 0,05), on rejette l'hypothèse nulle et on conclut que le modèle est significatif.
• Coefficients : Ce tableau contient les estimations des coefficients de régression (la pente et l'ordonnée à l'origine). Dans la colonne "Coefficients", vous trouverez :
  • Intercept : L'ordonnée à l'origine (b) de la droite de régression. C'est la valeur de la variable dépendante (y) lorsque la variable indépendante (x) est égale à zéro.
  • Nom de la variable indépendante (par exemple, Publicité) : Le coefficient de régression (a) pour la variable indépendante. Il représente la variation de la variable dépendante (y) pour chaque unité de variation de la variable indépendante (x). Par exemple, si le coefficient de la publicité est de 8, cela signifie que pour chaque euro supplémentaire dépensé en publicité, les ventes augmentent en moyenne de 8 euros.
• Erreur standard : L'erreur standard associée à chaque coefficient.
• Statistique t : Le résultat du test t pour chaque coefficient.
• Valeur p : La valeur p associée à chaque coefficient. Elle indique la significativité statistique de chaque coefficient. Si la valeur p est inférieure à un seuil de significativité (généralement 0,05), on rejette l'hypothèse nulle et on conclut que le coefficient est significatif.
• Intervalle de confiance à 95 % : L'intervalle de confiance pour chaque coefficient. Il donne une plage de valeurs plausibles pour le coefficient.

5. Écrire l'équation de régression

À partir des coefficients, vous pouvez écrire l'équation de régression. Dans notre exemple, si l'intercept est de 20 et le coefficient de la publicité est de 8, l'équation serait : Ventes = 8 * Publicité + 20.

Exemple concret : Prédiction des ventes

Supposons que nous voulions prédire les ventes si nous investissons 28 en publicité. Nous pouvons utiliser l'équation de régression : Ventes = 8 * 28 + 20 = 244. Nous prédirions donc des ventes de 244.

Réaliser une régression linéaire multiple sur Excel

La procédure pour réaliser une régression linéaire multiple est très similaire à celle pour une régression linéaire simple. La principale différence réside dans la sélection de la plage X en entrée. Vous devez sélectionner toutes les colonnes contenant vos variables indépendantes.

Exemple : Prédiction des ventes avec plusieurs variables

Supposons que nous voulions prédire les ventes en fonction de la publicité et du nombre de commerciaux. Nos données pourraient ressembler à ceci :

Dans la boîte de dialogue "Régression", vous sélectionnerez $A$1:$B$6 comme plage X en entrée (en supposant que les données commencent à la ligne 1 et incluent les en-têtes) et $C$1:$C$6 comme plage Y en entrée.

L'équation de régression multiple sera de la forme : Ventes = a1 * Publicité + a2 * Commerciaux + b

Conseils et astuces pour une régression linéaire réussie sur Excel

• Vérifiez la linéarité : Assurez-vous que la relation entre les variables est approximativement linéaire. Vous pouvez utiliser un nuage de points pour visualiser la relation.
• Vérifiez l'absence de multicolinéarité : Dans une régression linéaire multiple, les variables indépendantes ne doivent pas être fortement corrélées entre elles. La multicolinéarité peut rendre les coefficients de régression instables et difficiles à interpréter.
• Vérifiez l'homoscédasticité : Les résidus doivent avoir une variance constante sur toute la plage des valeurs prédites. L'hétéroscédasticité (variance non constante) peut biaiser les résultats.
• Vérifiez la normalité des résidus : Les résidus doivent être approximativement normalement distribués. Vous pouvez utiliser un histogramme ou un test de normalité pour vérifier cette hypothèse.
• Attention aux valeurs aberrantes : Les valeurs aberrantes (outliers) peuvent avoir une influence importante sur les résultats de la régression. Identifiez et analysez les valeurs aberrantes avant de tirer des conclusions.
• Ne sur-interprétez pas les résultats : La régression linéaire est un outil puissant, mais elle ne prouve pas la causalité. Une corrélation ne signifie pas nécessairement une causalité.
• Utilisez le R carré ajusté : Lorsque vous comparez plusieurs modèles de régression, utilisez le R carré ajusté plutôt que le R carré pour tenir compte du nombre de variables indépendantes.

Erreurs courantes à éviter lors d'une régression linéaire sur Excel

• Oublier d'activer l'outil d'analyse de données : C'est une erreur fréquente pour les débutants.
• Sélectionner les mauvaises plages de données : Vérifiez attentivement que vous avez sélectionné les bonnes colonnes pour les variables indépendantes et dépendantes.
• Ne pas cocher la case "Libellés" : Si vos plages de données incluent des libellés, assurez-vous de cocher la case correspondante.
• Ignorer les hypothèses de la régression linéaire : Ne pas vérifier la linéarité, l'homoscédasticité, la normalité des résidus et l'absence de multicolinéarité peut conduire à des conclusions erronées.
• Sur-interpréter les résultats : N'oubliez pas que la régression linéaire ne prouve pas la causalité.

Conclusion

La régression linéaire sur Excel est un outil puissant pour analyser les relations entre les variables et faire des prédictions. En suivant les étapes décrites dans ce guide et en tenant compte des conseils et astuces, vous pouvez maîtriser cet outil et l'utiliser pour prendre des décisions éclairées dans divers domaines. N'oubliez pas de toujours vérifier les hypothèses de la régression linéaire et d'interpréter les résultats avec prudence. Avec de la pratique, vous deviendrez un expert en régression linéaire sur Excel !