Régressions linéaires, logarithmiques, polynomes

Résumé

Régression linéaire

soient les points expérimentaux : 350

y
12 80 300
14 162 250
16 212
200
18 247
20 331 150
100
et le graphique en nuages de points correspondant
50
0
11 12 13 14 15 16 17 18 19 20 21

Comment récupérer l'équation de la droite de régression linéaire et le coéfficient de corrélation (détermination) ?

Deux méthodes sont possibles :

la méthode graphique
simple à utiliser, suffisante dans bien des cas
pas automatisable

La méthode mathématique
très utile si l'on veut récupérer l'équation exacte d'une droite de régression
ou si l'on a besoin d'automatiser ce type de calcul pour une série de données.

Régression logarithmique

Si les données sont de la forme

600000
X Y
0 60000
500000
24 61250
48 118500
400000
72 346500
120 516250
300000

200000

100000

0
0 20 40 60 80 100 120 140
 300000

200000

100000

0
0 20 40 60 80 100 120 140
On peut de même calculer la régression logarithmique
par une méthode graphique
ou récupérer l'équation de la régression

Equation d'un polynome

et récupérer l'équation d'un polynome
ermination) ?
 Résolution graphique d'une régression linéaire
Première solution : clic droit sur la série et "ajouter une courbe de tendance"
dans les options, cocher afficher l'équation et le coéfficient de détermination sur le graphique

350

300 f(x) = 29.4 x − 263.2

R² = 1.0
250

200

150

100

50

0
11 12 13 14 15 16 17 18 19 20 21

Si le nombre de décimales affichées dans l'équation n'est pas suffisant, cliquez sur l'équation et
dans la barre de format cliquez sur l'icône ajouter des décimales

350

300 f(x) = 29.3500 x − 263.2000

R² = 0.9802
250

200

150

100

50

0
11 12 13 14 15 16 17 18 19 20 21

Pour calculer les y théoriques correspondant à n'importe quelle valeur de x, recopiez la pente a et l'ordonnée à l'origine b
y = a*X + b
a= 29.35
b= -263.2

entrez ensuite la valeur des x en commençant en A64 ici tirez vers le bas la formule en b64 autant de fois que nécessaire
x y
12 89
14 147.7 =$B$43*A48+$B$44
16 206.4 =a*X + b
 18 265.1
20 323.8 les symboles $ permettent de fixer les références des cellules a et b pour qu'elles
ne bougent pas quand on "tire" l'équation vers le bas avec la poignée de recopie.

Pour trouver la valeur de x correspondant à un y expérimental

y x
0 8.96763203
156 14.2827939 =A57-$B$44/$B$43
… =(y-b)/a
l'ordonnée à l'origine b

t de fois que nécessaire

 llules a et b pour qu'elles
ec la poignée de recopie.
 Calcul de l'équation de la droite de régression
x y
12 80
14 162
16 212
18 247
20 331

Pour récupérer l'équation nous allons utiliser la fonction DroiteReg

voici le texte de l'aide :

Calcule les statistiques pour une droite par la méthode des moindres
carrés, afin de calculer une droite qui s'ajuste au plus près à vos
données, puis renvoie une matrice décrivant cette droite. Dans la
mesure où cette fonction renvoie une matrice de valeurs, elle doit
être tapée sous la forme d'une formule matricielle.

La fonction complète renvoie une floppée de paramètres que je te

laisse aller découvrir s'ils t'intéressent.
ça donne ca :

il faut tout d'abord sélectionner une zone de 5 lignes et 2 colonnes (zone jaune)
et entrer ensuite la fonction
= DROITEREG(B5:B9;A5:A9;;1)

B5:B9 représentent les Y connus, A5:A9 les x connus.

aucune valeur n'est entrée pour le paramètre suivant (d'où les deux ;; successifs)
Si on entre la valeur 1 (ou VRAI), la droite passera obligatoirement par l'origine
le dernier paramètre 1 (on peut aussi écrire VRAI) demande l'affichage de tous les paramètres.
On verra que si l'on ne veut que la pente et l'ordonnée à l'origine on peut l'omettre.

puis valider la saisie par contrôle+majuscule +entrée (sasie matricielle)

(jusqu'à la fin, tout la zone doit être sélectionnée)
29.35 -263.2
2.40883789 39.1389831 Si la saisie matricielle a été effectuée correctement, des accolades
0.98019241 15.2348285 s'ajoutent automatiquement à la formule de part et d'autre
148.457131 3 {=DROITEREG(B6:B10;A6:A10;;1)}
34456.9 696.3 Sinon il n'y a qu'une seule valeur qui apparaît et il n'y a pas d'accolade

En fait trois valeurs seulement nous intéressent le plus souvent dans ce tableau
La pente ici en A31, l'ordonnée à l'origine en B31 et le coéfficient de régression en A33

Pour ne récupérer QUE les deux premières valeurs, il suffit de sélectionner

deux cellules contigues horizontales et de taper
=DROITEREG(B5:B9;A5:A9;;0)
puis de valider par ctrl+maj+enter
29.35 -263.2
Mais dans ce cas, il manque la valeur du coéfficient de détermination

On peut aussi récupérer ces trois valeurs en faisant référence à leur position dans le tableau
renvoyé par la fonction droite reg :

la pente est dans la première ligne de la première colonne de la matrice

On peut donc la récupérer par =INDEX(DROITEREG(B5:B9;A5:A9;;1);1;1)
dans ce cas, il n'est pas besoin de faire une saisie matricielle puis on ne récupère que 1 résultat

a= 29.35 =INDEX(DROITEREG(B5:B9;A5:A9;;1);1;1)
de même,
b= -263.2 =INDEX(DROITEREG(B5:B9;A5:A9;;1);1;2)
r2= 0.98019241 =INDEX(DROITEREG(B5:B9;A5:A9;;1);3;1)

les valeurs indiquées en rouge peuvent être omises pour a et b mais pas pour calculer r 2

Ensuite, pour récupérer les valeurs théoriques de la droite, ou connaître la valeur de x pour un y connu
procéder comme pour la résolution graphique
ectement, des accolades
e part et d'autre

raît et il n'y a pas d'accolades.

 Résolution graphique d'une régression logarithmiq

600000 Quelques rappels…

Logarithme ne prend pas de
X Y 500000 Logarithmique non plus c'est
1 60000 Ln = log népérien dit aussi "g
400000
24 61250 log = log "à base 10"
48 118500 300000
72 246500 log(a) + log(b) = log(a*b)
120 516250 200000 log(a) - log(b) = log(a/b)
Ln(exp(a*x)) = a*x
100000
Ln(a^b) = bLn(a)
0
0 20 40 60 80 100 120 140

La résolution graphique est similaire à celle effectuée pour la régression linéaire :

Clic droit sur la série, ajouter une courbe de tendance
choisir une courbe exponentielle, afficher l'équation sur le graphique
Comme précédemment, on peut augmenter l'affichage du nombre de décimales
en cliquant sur l'équation puis en cliquant sur l'icône de format de nombre de décimales

C'est particulièrement utile ici

600000
500000f(x) = 49212.66899 exp( 0.01984 x )
400000R² = 0.96009
300000
200000
100000
0
0 20 40 60 80 100 120 140

L'équation dans ce cas est y= b * e (puissance a* X)

ce qu'excel écrit = b*exp(a*x)
on peut aussi l'écrire
Ln(y)= Ln(b) + a*x
a= 0.01984
b= 49212.669

pour trouver la valeur de y quand x est connu :

X Y
48 127545.236 =$B$52*EXP($B$51*A56)

pour trouver la valeur de x correspondant à un y connu (ou mesuré)

Y X
300000 91.1104528 =LN(A61/$B$52)/$B$51

Temps de doublement
si les valeurs de x ci-dessus sont des heures et les Y des nombre de cellules en croissance (exponentielle !)
combien faut-il de temps (G, exprimé en heures) à cette population cellulaire pour doubler ?

34.9368539 G= Ln(2)/a
sion logarithmique

Quelques rappels…
Logarithme ne prend pas de "y" !!
Logarithmique non plus c'est déjà assez compliqué comme ça !
Ln = log népérien dit aussi "grand log" Ln(e)=1
og = log "à base 10" log(10)=1

og(a) + log(b) = log(a*b)

og(a) - log(b) = log(a/b)
Ln(exp(a*x)) = a*x
Ln(a^b) = bLn(a)
 Calcul de l'équation de la régression logarithmique

X Y
1 60000
24 61250
48 118500
72 246500
120 516250

On utilise ici la fonction d'excel LogReg. Son utilisation est assez similaire à celle de DroiteReg.

il faut tout d'abord sélectionner une zone de 5 lignes et 2 colonnes (zone jaune)
et entrer ensuite la fonction
=LOGREG(B6:B10;A6:A10;;1)

B5:B9 représentent les Y connus, A5:A9 les x connus.

aucune valeur n'est entrée pour le paramètre suivant (d'où les deux ;; successifs)
Si on entre la valeur 1 (ou VRAI), l'ordonnée à l'origine b=1
le dernier paramètre 1 (on peut aussi écrire VRAI) demande l'affichage de tous les paramètres.
On verra que si l'on ne veut que la pente et l'ordonnée à l'origine on peut l'omettre.

puis valider la saisie par contrôle+majuscule +entrée (sasie matricielle)

(jusqu'à la fin, tout la zone doit être sélectionnée)
1.02003742 49212.668988
0.00233532 0.1565359685 Si la saisie matricielle a été effectuée correctement, des accolades
0.96009089 0.2142900376 s'ajoutent automatiquement à la formule de part et d'autre
72.1708088 3 {=LOGREG(B6:B10;A6:A10;;1)}
3.31409943 0.1377606606 Sinon il n'y a qu'une seule valeur qui apparaît et il n'y a pas d'accolad

L'équation renvoyée par cette fonction est de type y= b* m^x

En fait trois valeurs seulement nous intéressent le plus souvent dans ce tableau
m (ici en A27). Par rapport à la détermination graphique, on notera que m = exp(a), ou Ln(m)=a,
b cic en B27 et R2 en A29.

Pour ne récupérer QUE les deux premières valeurs, il suffit de sélectionner

deux cellules contigues horizontales et de taper
=LOGREG(B6:B10;A6:A10)
puis de valider par ctrl+maj+enter
1.02003742 49212.668988

Mais dans ce cas, il manque la valeur du coéfficient de détermination

On peut aussi récupérer ces trois valeurs en faisant référence à leur position dans le tableau
renvoyé par la fonction LOGREG :
la pente est dans la première ligne de la première colonne de la matrice
On peut donc la récupérer par =INDEX(DROITEREG(B5:B9;A5:A9;;1);1;1)
dans ce cas, il n'est pas besoin de faire une saisie matricielle puis on ne récupère que 1 résultat

m= 1.0200374244 =INDEX(LOGREG(B6:B10;A6:A10;;1);1;1)
de même,
b= 49212.668988 =INDEX(LOGREG(B6:B10;A6:A10;;1);1;2)
r2= 0.960090891 =INDEX(LOGREG(B6:B10;A6:A10;;1);3;1)

L'équation dans ce cas est y= b * m puissance x

ce qu'excel écrit au choix y =b*PUISSANCE(m;x)
ou y =b*m^x Le signe puissance est obtenu en tapant sur l'accent circonflexe. Il n'
pour être sur de l'avoir tapé, tapez deux fois et enlevez en un !
on peut aussi l'écrire
Pour trouver Y quand X est connu
X Y
48 127541.05541 =$B$56*$B$54^A67

pour trouver la valeur de x correspondant à un y connu (ou mesuré)

Y X
300000 91.113588529 =LN(A72/$B$56)/LN($B$54) =LN(Y/b)/LN(m)

Temps de doublement
si les valeurs de x ci-dessus sont des heures et les Y des nombre de cellules en croissance (exponentielle !)
combien faut-il de temps (G, exprimé en heures) à cette population cellulaire pour doubler ?

34.9380563 G= Ln(2)/Ln(m)
ogarithmique

ectement, des accolades

e part et d'autre

raît et il n'y a pas d'accolades.

ur l'accent circonflexe. Il n'apparaît pas tout de suite…
is et enlevez en un !
 Equation d'un polynome
Merci à Denis Michon, MPFE

Soient les données suivantes, calculées à partir d'un polynome que vous ne connaissez pas

x y
0 4
1 14
2 60
4 380
8 2772
10 5324

Comment récupérer l'équation du polynome ?

première solution de flemmard, déterminez le degré de ce polynome par tatonnement !
tracez un graphique en nuage de points, sélectionnez la série et demandez une régression polynomiale en faisant varier le de
Demandez l'affichage de la fonction et du coeffécient de détermination

6000 6000

5000 5000 f(x) = 5 x³ + 3 x² + 2 x + 4

f(x) = 75.62387 x² − 249.1806 x + 132.1833
R² = 1
R² = 0.996105510613288
4000 4000

3000 3000

2000 2000

1000 1000

0 0
0 2 4 6 8 10 12 0 2 4 6 8 10 12
degré 2

Au-delà du degré 3, avec ces données, le coeff de détermination est toujours = à 1 alors que les coeffs des x

Si tu sais qu'il s'agit d'un polynome du troisième degré et que voulez "juste" récupérer les coeff
sélectionne horizontalement 4 cellules adjacentes (1 de plus que le degré du polynome)
et tapez la formule ci-dessous que vous saisissez de façon matricielle (ctrl+maj+entrée)
=DROITEREG(B10:B15;A10:A15^{1\2\3})

a b c d
5 3 2 4

et si maintenant tu veux calculer la valeur de Y pour un x donné

X Y
9 3910 =($A$46*A51^3)+($B$46*A51^2)+($C$46*A51)+$D$46
sion polynomiale en faisant varier le degré

2x+4

8 10 12

s que les coeffs des x 4, x5, x6… tendent vers 0

A51)+$D$46
 Résumé

Régression linéaire
x y
12 80
14 162
16 212
18 247
20 331

pente a= 29.35 =INDEX(DROITEREG(les y;les x);1)

ordonnée origine b= -263.2 =INDEX(DROITEREG(les y;les x);1;2)
Coeff détermination R2= 0.98019241 =INDEX(DROITEREG(les y;les x;;1);3;1)
équation de la droite de régression y=a*x + b

calcul de y avec un x connu y=a*x + b

x y
15 177.05 =a*A21+b

calcul de x avec y connu x= (y-b)/a

y x
200 15.781942078 =(A25-b)/a

Régression Logarithmique
X Y
1 60000
24 61250
48 118500
72 246500
120 516250

pente m= 1.02003742 =INDEX(LOGREG(les y;les x);1)

ordonnée orogine b= 49212.669 =INDEX(LOGREG(les y;les x);1;2)
Coeff détermination R2= 0.96009089 =INDEX(LOGREG(les y;les x;;1);3;1)
équation de la régression y= b* m^x
ou encore

AVEC LES VALEURS DETERMINEES PAR LA FONCTION LOGREG :

calcul de y avec un x connu y= b* m^x
x y
48 127541.05541 =b*m^A46

calcul de x avec y connu x= ln(y/b)/ln(m)

y x
 300000 91.113588529 =LN(A50/b)/LN(a)

Temps de doublement :
G= 34.938056254 =LN(2)/LN(m)

on note une petite différence avec les résultats calculés par la

fonction LOGREG qui sont PLUS précis que ceux renvoyés par

AVEC LES VALEURS DETERMINEES AVEC LE GRAPHIQUE (REGRESSION EXPONENTIELLE)

le graphique renvoie une équation de type y = 49212.66899e0.01984x
a= 0.01984
b= 49212.66899

calcul de y avec un x connu

x y
48 127545.23553 =b*EXP(a*A64)

calcul de x avec un y connu :

y x
300000 91.110452848 =LN(A70/b)/a

temps de doublement
G= 34.936853859 LN(2)/a

Polynome
Pour un polynome de degré n, sélectionnez n+1 cellules horizontales et tapez
=DROITEREG(plageY;plageX^{1\2\...\n})
Validation matricielle
ceci renvoie les coéfficients du polynome