Régression quadratique et cubique dans Excel

Microsoft 365 & Office Excel & VBA
1

J’ai les informations suivantes :

  Height    Weight

    170     65
    167     55
    189     85
    175     70
    166     55
    174     55
    169     69
    170     58
    184     84
    161     56
    170     75
    182     68
    167     51
    187     85
    178     62
    173     60
    172     68
    178     55
    175     65
    176     70

Je veux construire une analyse de régression quadratique et cubique dans Excel. Je sais comment faire une régression linéaire dans Excel, mais qu’en est-il de la quadratique et de la cubique ? J’ai cherché beaucoup de ressources, mais je n’ai rien trouvé d’utile.

2

Vous devez utiliser une astuce non documentée avec la fonction LINEST d’Excel :

=LINEST(known_y's, [known_x's], [const], [stats])

Contexte

Une régression linéaire classique est calculée (avec vos données) comme :

=LINEST(B2:B21,A2:A21)

ce qui retourne une seule valeur, la pente linéaire (m) selon la formule :

qui pour vos données :

est :

Astuce non documentée n°1

Vous pouvez aussi utiliser Excel pour calculer une régression avec une formule qui utilise un exposant pour x différent de 1, par exemple x1.2 :

en utilisant la formule :

=LINEST(B2:B21, A2:A21^1.2)

qui pour vos données :

est :

Vous n’êtes pas limité à un seul exposant

La fonction LINEST d’Excel peut aussi calculer des régressions multiples, avec différents exposants sur x en même temps, par exemple :

=LINEST(B2:B21,A2:A21^{1,2})

Note : si les paramètres régionaux sont définis en européen (symbole décimal “,”), alors la virgule doit être remplacée par un point-virgule et un antislash, c’est-à-dire =LINEST(B2:B21;A2:A21^{1\2})

Maintenant Excel calculera les régressions utilisant à la fois x1 et x2 en même temps :

Comment le faire concrètement

La partie incroyablement délicate est qu’il n’y a pas de moyen évident de voir les autres valeurs de régression. Pour ce faire, vous devez :

  • sélectionner la cellule qui contient votre formule :

  • étendre la sélection de 2 espaces vers la gauche (vous avez besoin que la sélection fasse au moins 3 cellules de large) :

appuyer sur F2

appuyer sur Ctrl+Shift+Entrée

Vous verrez maintenant vos 3 constantes de régression :

  y = -0.01777539x^2 + 6.864151123x + -591.3531443

Note : À un moment donné depuis la dernière mise à jour de cette publication, Excel (au moins Excel 365 Version 2410) ne nécessite plus cette solution de contournement pour voir les 3 constantes de régression. La formule produira naturellement les 3 constantes comme souhaité.

Discussion bonus

J’avais une fonction pour laquelle je voulais effectuer une régression utilisant un certain exposant :

y = m x xk + b

Mais je ne connaissais pas l’exposant. J’ai donc modifié la fonction LINEST pour utiliser une référence de cellule à la place :

=LINEST(B2:B21,A2:A21^F3, true, true)

Avec Excel affichant ensuite les statistiques complètes (le 4ème paramètre de LINEST) :

Je dis au Solveur de maximiser R2 :

Et il peut trouver le meilleur exposant. Ce qui pour vos données :

est :