Il existe deux fonctions dans Excel, utiles à connaître pour faire de la régression linéaire simple. Ces deux fonctions sont la fonction DroiteReg (Linest en anglais) et la fonction Tendance (Trend en anglais). Cet article vous explique comment vous servir de ces deux fonctions et vous informe sur les avantages et limites de chacune d’entre elles.
La fonction DroiteReg (Linest) pour effectuer une régression linéaire simple
Pour effectuer notre régression linéaire simple, nous allons utiliser la fonction DroiteReg ou Linest en anglais est une fonction matricielle (array formula). 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é. Nous souhaitons maintenant prévoir le niveau de revenus de la prochaine journée, sachant que le nombre d’appels s’élèvera à 500.
Dans un premier temps, nous avons sélectionné les cellules H12 et I12 et nous avons inséré la fonction DroiteReg (Linest) dans ces deux cellules. Nous avons d’abord sélectionné les Y connus, i.e. les revenus, dans notre exemple et ensuite, nous avons sélectionné les X connus, i.e. le nombre d’appels, dans notre exemple.
Dans un deuxième temps, au lieu de cliquer sur Enter, nous avons appuyé sur CTRL+SHIFT+ENTER, puisque la fonction DroiteReg (Linest) est une fonction matricielle, tel que spécifié plus tôt (d’où l’apparition des accollades de part et d’autre de la formule).
Ce faisant, nous avons obtenu deux valeurs, soit le coefficient de régression (la pente) et l’ordonnée à l’origine (intercept). 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. Nous obtenons ainsi, pour un volume de 500 appels, un niveau de revenus correspondant à 382.80$ (espérant que ce soit des milliers ou des millions de dollars!).
Pente et ordonnée à l’origine de notre cas de régression linéaire simple
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.
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. |
|---|
Fonction Tendance (Trend)
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 (ou Linest en anglais), qui fournit davantage d’information et qui permet ainsi d’effectuer une meilleure évaluation des données.
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é.
Très inintéressant encore un outil de plus que je viens d’apprendre sur excel. merci Sophie.
Ceci est exact à la condition express que les 2 valeurs soient reliées par une fonction affine, i.e. du type ax+b. Or dans les domaines sociaux ou des affaires la liaison est très souvent et plutôt du type exponentiel ou logarithmique. Votre utilisation de trend ou de droite de régression n’est acceptable que pour une prévision à très court terme pour laquelle l’erreur est négligeable. Sinon il est prudent de tracer d’abord un graphique puis d’utiliser la fonction “courbe de tendance” afin de vérifier quelle fonction relie les valeurs, et donc l’extrapolation à employer.
Amicalement.
Bonjour Bernard,
Cet article porte expressément sur la régression simple. D’autres articles suivront pour traiter d’autres cas, comme celui des données qui suivent une trajectoire courbe.
Merci,
Sophie