Ce site est optimisé pour être consulté depuis un navigateur moderne dans lequel JavaScript est activé.

Régression quadratique et cubique dans Excel

ayi

J’ai les informations suivantes :
<pre><code class="lang-auto"> Height Weight

</code></pre>
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.

ayi_2

Vous devez utiliser une astuce non documentée avec la fonction <code>LINEST</code> d’Excel :
<pre><code class="lang-auto">=LINEST(known_y's, [known_x's], [const], [stats])

</code></pre>
<h2><a name="p-35104-contexte-1" class="anchor" href="#p-35104-contexte-1" aria-label="Heading link"></a>Contexte</h2>
Une régression linéaire classique est calculée (avec vos données) comme :
<pre><code class="lang-auto">=LINEST(B2:B21,A2:A21)

</code></pre>
ce qui retourne une seule valeur, la pente linéaire (<code>m</code>) selon la formule :
qui pour vos données :
est :
<h2><a name="p-35104-astuce-non-documente-n1-2" class="anchor" href="#p-35104-astuce-non-documente-n1-2" aria-label="Heading link"></a>Astuce non documentée n°1</h2>
Vous pouvez aussi utiliser Excel pour calculer une régression avec une formule qui utilise un exposant pour <code>x</code> différent de <code>1</code>, par exemple x1.2 :
en utilisant la formule :
<pre><code class="lang-auto">=LINEST(B2:B21, A2:A21¹.2)

</code></pre>
qui pour vos données :
est :
<h2><a name="p-35104-vous-ntes-pas-limit-un-seul-exposant-3" class="anchor" href="#p-35104-vous-ntes-pas-limit-un-seul-exposant-3" aria-label="Heading link"></a>Vous n’êtes pas limité à un seul exposant</h2>
La fonction <code>LINEST</code> d’Excel peut aussi calculer des régressions multiples, avec différents exposants sur <code>x</code> en même temps, par exemple :
<pre><code class="lang-auto">=LINEST(B2:B21,A2:A21^{1,2})

</code></pre>
<blockquote></blockquote>
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 <code>=LINEST(B2:B21;A2:A21^{1\2})</code>
Maintenant Excel calculera les régressions utilisant à la fois x1 et x2 en même temps :
<h2><a name="p-35104-comment-le-faire-concrtement-4" class="anchor" href="#p-35104-comment-le-faire-concrtement-4" aria-label="Heading link"></a>Comment le faire concrètement</h2>
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 :
<ul>
<li>
sélectionner la cellule qui contient votre formule :
</li>
<li>
étendre la sélection de 2 espaces vers la gauche (vous avez besoin que la sélection fasse au moins 3 cellules de large) :
</li>
<li></li>
</ul>
appuyer sur F2
<ul>
<li></li>
</ul>
appuyer sur Ctrl+Shift+Entrée
Vous verrez maintenant vos 3 constantes de régression :
<pre><code class="lang-auto"> y = -0.01777539x² + 6.864151123x + -591.3531443

</code></pre>
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é.
<h2><a name="p-35104-discussion-bonus-5" class="anchor" href="#p-35104-discussion-bonus-5" aria-label="Heading link"></a>Discussion bonus</h2>
J’avais une fonction pour laquelle je voulais effectuer une régression utilisant un certain exposant :
<blockquote></blockquote>
y = m x xk + b
Mais je ne connaissais pas l’exposant. J’ai donc modifié la fonction <code>LINEST</code> pour utiliser une référence de cellule à la place :
<pre><code class="lang-auto">=LINEST(B2:B21,A2:A21^F3, true, true)

</code></pre>
Avec Excel affichant ensuite les statistiques complètes (le 4ème paramètre de <code>LINEST</code>) :
Je dis au Solveur de maximiser R2 :
Et il peut trouver le meilleur exposant. Ce qui pour vos données :
est :