Comment effectuer une régression quadratique dans Excel

La régression est une technique statistique que nous pouvons utiliser pour expliquer la relation entre une ou plusieurs variables prédictives et une variable de réponse. Le type de régression le plus courant est la régression linéaire , que nous utilisons lorsque la relation entre la variable prédictive et la variable de réponse est linéaire .

Autrement dit, lorsque la variable prédictive augmente, la variable réponse a tendance à augmenter également. Par exemple, nous pouvons utiliser un modèle de régression linéaire pour décrire la relation entre le nombre d’heures étudiées (variable prédictive) et la note qu’un étudiant obtient à un examen (variable de réponse).

Cependant, il arrive parfois que la relation entre une variable prédictive et une variable de réponse soit non linéaire . Un type courant de relation non linéaire est la relation quadratique , qui peut ressembler à un U ou à un U à l’envers sur un graphique.

Autrement dit, lorsque la variable prédictive augmente, la variable de réponse a tendance à augmenter également, mais après un certain point, la variable de réponse commence à diminuer à mesure que la variable prédictive continue d’augmenter.

Par exemple, nous pouvons utiliser un modèle de régression quadratique pour décrire la relation entre le nombre d’heures passées à travailler et le niveau de bonheur déclaré par une personne. Peut-être que plus une personne travaille, plus elle se sent épanouie, mais une fois qu’elle atteint un certain seuil, plus de travail entraîne en réalité du stress et une diminution du bonheur. Dans ce cas, un modèle de régression quadratique ajusterait mieux les données qu’un modèle de régression linéaire.

Passons en revue un exemple de la façon d’effectuer une régression quadratique dans Excel.

Régression quadratique dans Excel

Supposons que nous ayons des données sur le nombre d’heures travaillées par semaine et le niveau de bonheur déclaré (sur une échelle de 0 à 100) pour 16 personnes différentes :

Tout d’abord, créons un nuage de points pour voir si la régression linéaire est un modèle approprié pour s’adapter aux données.

Mettez en surbrillance les cellules A2:B17 . Ensuite, cliquez sur l’onglet INSÉRER le long du ruban supérieur, puis cliquez sur Scatter dans la zone Graphiques . Cela produira un nuage de points des données :

Il est facile de constater que la relation entre les heures travaillées et le bonheur déclaré n’est pas linéaire. En fait, il suit une forme en « U », ce qui en fait un candidat parfait pour la régression quadratique .

Avant d’adapter le modèle de régression quadratique aux données, nous devons créer une nouvelle colonne pour les valeurs au carré de notre variable prédictive.

Tout d’abord, mettez en surbrillance toutes les valeurs de la colonne B et faites-les glisser vers la colonne C.

Ensuite, tapez la formule =A2^2 dans la cellule B2. Cela produit la valeur 36 . Ensuite, cliquez sur le coin inférieur droit de la cellule B2 et faites glisser la formule vers le bas pour remplir les cellules restantes de la colonne B.

Ensuite, nous ajusterons le modèle de régression quadratique.

Cliquez sur DONNÉES le long du ruban supérieur, puis cliquez sur l’option Analyse des données à l’extrême droite. Si vous ne voyez pas cette option, vous devez d’abord installer le logiciel gratuit Analysis ToolPak .

Une fois que vous avez cliqué sur Analyse des données , une boîte apparaîtra. Cliquez sur Régression , puis cliquez sur OK .

Ensuite, remplissez les valeurs suivantes dans la zone Régression qui apparaît. Cliquez ensuite sur OK .

Les résultats suivants seront affichés :

Voici comment interpréter les différents nombres de la sortie :

R Square : Également connu sous le nom de coefficient de détermination, il s’agit de la proportion de la variance de la variable de réponse qui peut être expliquée par les variables prédictives. Dans cet exemple, le R carré est de 0,9092 , ce qui indique que 90,92 % de la variance des niveaux de bonheur déclarés peut s’expliquer par le nombre d’heures travaillées et le nombre d’heures travaillées ^2.

Erreur type : l’erreur type de la régression est la distance moyenne entre les valeurs observées et la droite de régression. Dans cet exemple, les valeurs observées s’éloignent en moyenne de 9,519 unités de la droite de régression.

Statistique F : La statistique F est calculée comme MS de régression / MS résiduel. Cette statistique indique si le modèle de régression fournit un meilleur ajustement aux données qu’un modèle qui ne contient aucune variable indépendante. Essentiellement, il teste si le modèle de régression dans son ensemble est utile. Généralement, si aucune des variables prédictives du modèle n’est statistiquement significative, la statistique F globale n’est pas non plus statistiquement significative. Dans cet exemple, la statistique F est de 65,09 et la valeur p correspondante est <0,0001. Puisque cette valeur p est inférieure à 0,05, le modèle de régression dans son ensemble est significatif.

Coefficients de régression : Les coefficients de régression du dernier tableau nous donnent les nombres nécessaires pour écrire l’équation de régression estimée :

y _chapeau = b ₀ + b ₁ x ₁ + b ₂ x ₁ ²

Dans cet exemple, l’équation de régression estimée est :

niveau de bonheur déclaré = -30,252 + 7,173 (Heures travaillées) -0,106 (Heures travaillées) ²

Nous pouvons utiliser cette équation pour calculer le niveau de bonheur attendu d’un individu en fonction de ses heures travaillées. Par exemple, le niveau de bonheur attendu d’une personne qui travaille 30 heures par semaine est :

niveau de bonheur rapporté = -30,252 + 7,173(30) -0,106(30) ² = 88,649 .

Ressources additionnelles

Comment ajouter une ligne de tendance quadratique dans Excel
Comment lire et interpréter un tableau de régression
Qu’est-ce qu’une bonne valeur R au carré ?
Comprendre l’erreur standard de la régression
Un guide simple pour comprendre le test F de signification globale en régression