Il est très facile d’ ajouter une ligne de tendance dans Excel . Le seul véritable défi est de choisir le type de ligne de tendance qui correspond le mieux au type de données que vous analysez. Dans ce didacticiel, vous trouverez la description détaillée de toutes les options de courbe de tendance disponibles dans Excel et quand les utiliser. Si vous cherchez comment insérer une courbe de tendance dans un graphique Excel, veuillez consulter le didacticiel lié ci-dessus.

Types de courbes de tendance Excel

Lors de l’ajout d’une courbe de tendance dans Excel, vous avez le choix entre 6 options différentes. De plus, Microsoft Excel permet d’afficher une équation de courbe de tendance et une valeur R au carré dans un graphique :

• L’équation de la courbe de tendance est une formule qui trouve une ligne qui correspond le mieux aux points de données.
• La valeur R au carré mesure la fiabilité de la ligne de tendance – plus R ² est proche de 1, plus la ligne de tendance correspond aux données.

Ci-dessous, vous trouverez une brève description de chaque type de ligne de tendance avec des exemples de graphiques.

Ligne de tendance linéaire

Il est préférable d’utiliser la ligne de tendance linéaire avec des ensembles de données linéaires lorsque les points de données d’un graphique ressemblent à une ligne droite. En règle générale, une ligne de tendance linéaire décrit une hausse ou une baisse continue dans le temps.

Par exemple, la ligne de tendance linéaire suivante montre une augmentation constante des ventes sur 6 mois. Et la valeur R ² de 0,9855 indique un assez bon ajustement des valeurs estimées de la ligne de tendance aux données réelles.

Courbe de tendance exponentielle

La ligne de tendance exponentielle est une ligne, courbe qui illustre une hausse ou une baisse des valeurs de données à un rythme croissant. Par conséquent, la ligne est généralement plus courbée d’un côté. Ce type de courbe de tendance est souvent utilisé en sciences, par exemple pour visualiser une croissance de la population humaine ou un déclin des populations d’animaux sauvages.

Veuillez noter qu’une courbe de tendance exponentielle ne peut pas être créée pour les données contenant des zéros ou des valeurs négatives.

Un bon exemple de courbe exponentielle est la décroissance de l’ensemble de la population de tigres sur la terre.

Courbe de tendance logarithmique

La ligne de meilleur ajustement logarithmique est généralement utilisée pour tracer des données qui augmentent ou diminuent rapidement, puis se stabilisent. Il peut inclure des valeurs positives et négatives.

Un exemple de ligne de tendance logarithmique peut être un taux d’inflation, qui augmente d’abord mais se stabilise après un certain temps.

Courbe de tendance polynomiale

La courbe de tendance curviligne polynomiale fonctionne bien pour les grands ensembles de données avec des valeurs oscillantes qui ont plus d’une montée et une descente.

Généralement, un polynôme est classé par le degré du plus grand exposant. Le degré de la ligne de tendance polynomiale peut également être déterminé par le nombre de virages sur un graphique. En règle générale, une ligne de tendance polynomiale quadratique a une courbure (colline ou vallée), un polynôme cubique a 1 ou 2 courbures et un polynôme quartique a jusqu’à 3 courbures.

Lors de l’ajout d’une courbe de tendance polynomiale dans un graphique Excel, vous spécifiez le degré en tapant le nombre correspondant dans la zone Degré  du volet Format de la courbe de tendance , qui est 2 par défaut :

Par exemple, la tendance du polynôme quadratique est évidente sur le graphique suivant qui montre la relation entre le bénéfice et le nombre d’années que le produit a été sur le marché : hausse au début, pic au milieu et baisse vers la fin.

Courbe de tendance de puissance

La ligne de tendance de puissance est très similaire à la courbe exponentielle, sauf qu’elle a un arc plus symétrique. Il est couramment utilisé pour tracer des mesures qui augmentent à un certain rythme.

Une courbe de tendance de puissance ne peut pas être ajoutée à un graphique Excel contenant des valeurs nulles ou négatives.

À titre d’exemple, dessinons une ligne de tendance de puissance pour visualiser le taux de réaction chimique. Notez la valeur R au carré de 0,976, ce qui signifie que notre ligne de tendance correspond presque parfaitement aux données.

Ligne de tendance moyenne mobile

Lorsque les points de données de votre graphique ont beaucoup de hauts et de bas, une ligne de tendance moyenne mobile peut lisser les fluctuations extrêmes des valeurs de données pour montrer un modèle plus clairement. Pour cela, Excel calcule la moyenne mobile du nombre de périodes que vous spécifiez (2 par défaut) et place ces valeurs moyennes sous forme de points sur la ligne. Plus la valeur Période est élevée, plus la ligne est lisse.

Un bon exemple pratique consiste à utiliser la ligne de tendance de la moyenne mobile pour révéler les fluctuations du cours d’une action qui, autrement, seraient difficiles à observer.

Équations et formules de la ligne de tendance Excel

Cette section décrit les équations utilisées par Excel pour différents types de courbes de tendance. Vous n’avez pas besoin de créer ces formules manuellement, dites simplement à Excel d’ afficher l’équation de la courbe de tendance dans un graphique .

Nous discuterons également de la formule pour trouver la pente d’une ligne de tendance et d’autres coefficients. Les formules supposent que vous avez 2 ensembles de variables : la variable indépendante x et la variable dépendante y . Dans vos feuilles de calcul, vous pouvez utiliser ces formules pour obtenir les valeurs y prévues pour toutes les valeurs données de x .

Par souci de cohérence, nous utiliserons le même ensemble de données avec des valeurs légèrement différentes pour tous les exemples. Cependant, veuillez garder à l’esprit que ce n’est qu’à des fins de démonstration. Dans vos feuilles de calcul réelles, vous devez choisir le type de courbe de tendance correspondant à votre type de données.

Remarque: Les formules de courbe de tendance ne doivent être utilisées qu’avec des graphiques en nuage de points XY, car seul ce graphique trace les axes x et y sous forme de valeurs numériques. Pour plus d’informations, veuillez consulter Pourquoi l’équation de la courbe de tendance d’Excel peut être erronée .

Équation et formules de la ligne de tendance linéaire

L’équation de la courbe de tendance linéaire utilise les méthodes des moindres carrés pour rechercher la pente et les coefficients d’ interception tels que :

y = bx + a

Où:

• b est la pente d’une ligne de tendance.
• 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, c’est le point où la ligne de tendance croise l’ axe y .

Pour la régression linéaire, Microsoft Excel fournit des fonctions spéciales pour obtenir les coefficients de pente et d’interception.

Pente de la ligne de tendance
b :=PENTE(y_connus ;x_connus)

Ordonnée à l’origine
a :=ORDONNEE.ORIGINE(y_connus ;x_connus)

En supposant que la plage x est B2 : B13 et que la plage y est C2 : C13, les formules réelles sont les suivantes :

=PENTE(C2:C13 ;B2:B13)

= ORDONNEE.ORIGINE (C2:C13 ;B2:B13)

Les mêmes résultats peuvent être obtenus en utilisant la fonction DROITEREG( ) comme formule matricielle . Pour cela, sélectionnez 2 cellules adjacentes dans la même ligne, entrez la formule et appuyez sur Ctrl + Maj + Entréepour le compléter :

= DROITEREG(C2:C13 ;B2:B13)

Comme le montre la capture d’écran ci-dessous, les coefficients de pente et d’interception renvoyés par les formules sont parfaitement en ligne avec les coefficients de l’équation de la ligne de tendance linéaire affichée dans le graphique, seuls ces derniers sont arrondis à 4 décimales :

Équation et formules de la ligne de tendance exponentielle

Pour la courbe de tendance exponentielle, Excel utilise l’équation suivante :

y = ae^bx

Où a et b sont des coefficients calculés et e est la constante mathématique e (la base du logarithme népérien).

Les coefficients peuvent être calculés en utilisant ces formules génériques :

a:= EXP(INDEX(DROITEREG(LN(y) ; x) ;1 ;2))

b := INDEX(DROITEREG(LN (y) ; x) ; 1)

Pour notre exemple d’ensemble de données, les formules prennent la forme suivante :

a:=EXP(INDEX(DROITEREG(LN(C2:C13);B2:B13);1;2))

b :=INDEX(DROITEREG(LN(C2:C13);B2:B13);1)

Équation et formules de la courbe de tendance logarithmique

Voici l’équation de la courbe de tendance logarithmique dans Excel :

y = a*ln(x)+b

Où a et b sont des constantes et ln est la fonction logarithme naturel.

Pour obtenir les constantes, utilisez ces formules génériques, qui ne diffèrent que par le dernier argument :

a:= INDEX(DROITEREG (y ;LN(x)) ; 1)

b := INDEX(DROITEREG (y ; LN(x)) ; 1 ; 2)

Pour notre exemple d’ensemble de données, nous utilisons ceux-ci :

a=INDEX(DROITEREG(C2:C13; LN(B2:B13)); 1)

b :=INDEX(DROITEREG(C2:C13;LN(B2:B13));1;2)

Équation et formules de la courbe de tendance polynomiale

Pour calculer la courbe de tendance polynomiale, Excel utilise cette équation :

y = b ₆ x ⁶ + … + b ₂ x ² + b ₁ x + a

Où b ₁ … b ₆ et a sont des constantes.

Selon le degré de votre courbe de tendance polynomiale, utilisez l’un des ensembles de formules suivants pour obtenir les constantes.

Courbe de tendance polynomiale quadratique (2ème ordre)

Équation : y = b ₂ x ² + b ₁ x + a

b ₂ :=INDEX(DROITEREG(y ; x^{1.2}) ; 1)

b ₁ :=INDEX(DROITEREG(y ; x^{1.2}) ; 1 ; 2)

a:=INDEX(DROITEREG(y ; x^{1.2}) ;1 ; 3)

Courbe de tendance polynomiale cubique (3ème ordre)

Équation : y = b ₃ x ³ + b ₂ x ² + b ₁ x + a

b ₃ :=INDEX(DROITEREG(y ; x^{1.2.3}) ; 1)

b ₂ :=INDEX(DROITEREG(y ; x^{1.2.3}) ; 1 ;2)

b ₁ :=INDEX(DROITEREG(y ; x^{1.2.3}) ; 1 ;3)

a:=INDEX(DROITEREG(y ;x^{1.2.3}) ; 1 ;4)

Les formules pour les lignes de tendance polynomiales de degré supérieur peuvent être construites en utilisant le même modèle.

Pour notre ensemble de données, la courbe de tendance polynomiale du 2ème ordre est meilleure, nous utilisons donc ces formules ^:

b ₂ : =INDEX(DROITEREG(C2:C13;B2:B13^{1.2});1)

b ₁ : =INDEX(DROITEREG(C2:C13;B2:B13^{1.2});1;2)

a:  =INDEX(DROITEREG(C2:C13;B2:B13 ^ {1.2});1;3)

Équation et formules de la courbe de tendance de puissance

Une courbe de tendance de puissance dans Excel est dessinée sur la base de cette simple équation :

y = axe ^b

Où a et b sont des constantes, qui peuvent être calculées avec ces formules :

a:=EXP(INDEX(DROITEREG(LN(y); LN(x) ; 😉 ; 1 ; 2))

b :=INDEX(DROITEREG(LN(y) ; LN(x) ; 😉 ; 1)

Dans notre cas, les formules suivantes fonctionnent à merveille :

a: =EXP(INDEX(DROITEREG(LN(C2:C13);LN(B2:B13); ;);1;2))

b : =INDEX(DROITEREG(LN(C2:C13);LN(B2:B13); ;);1)

L’équation de la ligne de tendance Excel est erronée – raisons et correctifs

Si vous pensez qu’Excel a tracé une ligne de tendance de manière incorrecte ou que la formule de la ligne de tendance affichée dans votre graphique est erronée, les deux points suivants peuvent éclairer la situation.

L’équation de la ligne de tendance Excel n’est correcte que dans les graphiques en nuage de points

Les formules de courbe de tendance Excel ne doivent être utilisées qu’avec des graphiques XY (nuages), car ce n’est que dans ce type de graphique que les axes y et x sont tracés sous forme de valeurs numériques.

Dans les graphiques linéaires , les diagrammes et les histogrammes , les valeurs numériques sont tracées uniquement sur l’axe des ordonnées. L’axe des x est représenté par une série linéaire (1, 2, 3,…), que les étiquettes de l’axe soient des nombres ou du texte. Lorsque vous créez une courbe de tendance dans ces graphiques, Excel utilise ces valeurs x supposées dans la formule de la courbe de tendance.

Les nombres sont arrondis dans l’équation de la ligne de tendance Excel

Pour occuper moins d’espace dans le graphique, Excel affiche très peu de chiffres significatifs dans une équation de courbe de tendance. Agréable en termes de conception, cela réduit considérablement la précision de la formule lorsque vous fournissez manuellement des valeurs x dans l’équation.

Une solution simple consiste à afficher plus de décimales dans l’équation . Vous pouvez également calculer les coefficients à l’aide d’une formule correspondant à votre type de courbe de tendance et formater les cellules de la formule afin qu’elles affichent un nombre suffisant de décimales. Pour cela, cliquez simplement sur le bouton Augmenter la décimale de l’ onglet Accueil du groupe Nombre .

C’est ainsi que vous pouvez créer différents types de courbes de tendance dans Excel et obtenir leurs équations.