Analyse prédictive dans Excel: Régression linéaire simple

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.

Fonction DroiteReg (Linest)

La fonction DroiteReg ou Linest en anglais est une fonction matricielle (array formula). Dans l’exemple ci-bas, 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

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.

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.

Sophie Marchand, M.Sc., CPA, CGA, MVP
514-605-7112
Experte en modélisation financière et intelligence d’affaires