Analyse prédictive : Réaliser une régression linéaire simple avec la fonction DROITEREG dans Excel

Dans cet article, nous verrons pas à pas comment réaliser une régression linéaire simple dans Excel à l’aide de la fonction DROITEREG, afin d’estimer la valeur d’une variable dépendante à partir d’une variable indépendante. Cette méthode est particulièrement utile pour les professionnels qui souhaitent analyser des tendances ou prévoir des résultats à partir de données historiques.

Avant de vous lancer dans la mise en pratique, si ce n’est pas déjà fait, assurez-vous de bien comprendre les principes de base de la régression linéaire : qu’est-ce qu’une relation linéaire, comment la reconnaître et l’interpréter, et quels pièges éviter. Vous pouvez approfondir ces notions en consultant notre article Maîtriser la régression linéaire dans Excel : un levier stratégique pour les décideurs.

1. Préparer vos données

Afin de pouvoir réaliser votre analyse de régression simple, vous aurez besoin d’organiser vos données en deux colonnes :

• La variable dépendante (Y) : la valeur que vous souhaitez prédire placée dans une colonne
• La variable indépendante (X) : la valeur explicative que vous souhaitez utiliser pour prédire Y placée dans une autre colonne

Assurez-vous également que les données sont de type continu et qu’il n’y a pas de cellules vides dans les colonnes sélectionnées.

Dans l’exemple ci-dessous, nous avons compilé le nombre d’appels effectués par une équipe de développement des affaires et le niveau de revenus générés sur une plage de temps donnée afin de prévoir le niveau de revenus de la prochaine journée, sachant que le nombre d’appels s’élèvera à 500.

2. Utiliser la fonction DROITEREG

Après avoir confirmé que nous sommes bien en présence d’une relation de type linéaire, via le calcul du coefficient de corrélation, tel que vu dans l’article Maîtriser la régression linéaire dans Excel : un levier stratégique pour les décideurs, nous utilisons la fonction DROITEREG (ou LINEST en anglais) afin de calculer :

• La pente (coefficient) de la droite de régression
• L’interception (ordonnée à l’origine) de la droite

Nous obtenons le résultat suivant :

3. Interpréter les résultats

La première valeur retournée est le coefficient de régression (la pente). Cette valeur indique l’effet de X sur Y. Ainsi, chaque fois que le nombre d’appels (Y) augmente d’une unité, le revenu augmente de 0,5097 unité.

La deuxième valeur retournée est l’intercept (ordonnée à l’origine). Il s’agit de la valeur de Y lorsque X = 0.

Comme notre projection est de type linéaire, il nous suffit d’utiliser l’équation y = ax+b où y est la valeur recherchée, a est la pente et b est l’ordonnée à l’origine. L’équation de la droite permettant d’estimer le revenu (X) à partir du nombre d’appels (Y) est donc : Y = 0,5097x+262,12.

Nous obtenons ainsi, pour un volume de 500 appels, un niveau de revenus correspondant à 516,98$ (espérant que ce soit des milliers ou des millions de dollars!).

Vous aimeriez être un Ninja Excel et devenir la référence ultime de votre bureau, ou tout le moins ne plus avoir l’impression de stagner ? Développez des solutions innovatrices en explorant des fonctions et fonctionnalités souvent méconnues mais fort utiles avec nos formations pour devenir Ninja d’Excel.

4. Alternatives

Alternative #1

Il existe, dans Excel, deux fonctions qui permettent de trouver le coefficient de régression et l’ordonnée à l’origine. Ces fonctions sont les fonctions PENTE (SLOPE) et ORDONNEE.ORIGINE (INTERCEPT). Alors pourquoi ne pas utiliser directement ces deux fonctions? Tout simplement parce que ces deux fonctions, qui datent des débuts d’Excel, sont moins performantes. En effet, dans certaines situations, elles rencontrent certaines limites.

Alternative #2

La fonction TENDANCE (ou TREND en anglais) agit comme la fonction DROITEREG (ou LINEST en anglais) à l’exception qu’elle effectue la prévision directement, sans isoler les paramètres de pente et d’ordonnée à l’origine. Pour cette raison, je préfère utiliser la fonction DROITEREG, qui fournit davantage d’information et qui permet ainsi d’effectuer une meilleure évaluation des données.

Alternative #3

Une autre option consiste à utiliser le Data Analysis Toolpak d’Excel, qui offre un module de régression accessible depuis le menu Données > Analyse des données. Cependant, dans un contexte de régression simple, cette méthode est souvent jugée plus lourde que nécessaire, car elle génère un rapport complet alors qu’une simple équation suffit généralement. C’est pourquoi la fonction DROITEREG demeure la solution la plus rapide et flexible pour ce type d’analyse.

Par contre, dans un contexte d’analyse de régression multiple (lorsque plusieurs variables X sont incluses dans notre analyse), cette alternative sera souvent à privilégier. Pour découvrir comment mettre à profit le Data Analysis Toolpak d’Excel dans un contexte de régression multiple, consultez notre article Réaliser une régression linéaire multiple avec le Data Analysis Toolpak d’Excel.

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

Développez vos compétences en analyse de données et exploitez les fonctions d’Excel qui permettent de manipuler, gérer et transformer des données qui proviennent de diverses sources et différents formats, sans faire intervenir Power Query ou le VBA, en suivant la formation Excel – Traitement, manipulation et analyse de données.

Voici quelques commentaires d’apprenants ayant suivi cette formation :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.