La fonction DROITEREG dans Excel

Microsoft Excel n’est pas un programme statistique, cependant, il possède un certain nombre de fonctions statistiques. L’une de ces fonctions est DROITEREG( ), qui est conçue pour effectuer une analyse de régression linéaire et renvoyer des statistiques associées.  

1 Fonction DROITEREG ( ) – syntaxe et utilisations de base

La fonction DROITEREG( ) calcule les statistiques d’une ligne droite qui explique la relation entre la variable indépendante et une ou plusieurs variables dépendantes, et renvoie un tableau décrivant la ligne. La fonction utilise la méthode des moindres carrés pour trouver le meilleur ajustement pour vos données. L’équation de la droite est la suivante.

Équation de régression linéaire simple :

y = bx + a

Équation de régression multiple :

y = b1x1+ b2x2+ … + bnxn+ a

Où:

  • y – la variable dépendante que vous essayez de prédire.
  • x – la variable indépendante que vous utilisez pour prédire y .
  • a – l’interception (indique où la ligne coupe l’axe Y).
  • b – la pente (indique la pente de la droite de régression, c’est-à-dire le taux de variation de y lorsque x change).

Dans sa forme de base, la fonction DROITEREG ( ) renvoie l’ordonnée à l’origine (a) et la pente (b) pour l’équation de régression.  Il peut également renvoyer des statistiques supplémentaires pour l’analyse de régression.

La syntaxe de la fonction Excel DROITEREG( ) est la suivante :

DROITEREG(y_connus ; [x_connus] ; [constante] ; [statistique])

Où:

  • y_connus (obligatoire) est une plage de valeurs y dépendantes dans l’équation de régression. Il s’agit généralement d’une seule colonne ou d’une seule ligne.
  • x_connus (facultatif) est une plage de valeurs x indépendantes. S’il est omis, il est supposé être le tableau {1,2,3,…} de la même taille que les y_connus .
  • constante (optionnel) – une valeur logique qui détermine comment l’interception (constante a ) doit être traitée :
    • Si VRAI ou omis, la constante a est calculée normalement.
    • Si FAUX, la constante a est forcée à 0 et la pente ( coefficient b ) est calculée pour s’ajuster à y=bx.
  • statistique (facultatif) est une valeur logique qui détermine si des statistiques supplémentaires doivent être générées ou non :
    • Si VRAI, la fonction DROITEREG renvoie un tableau avec des statistiques de régression supplémentaires.
    • Si FAUX ou omis, DROITEREG ne renvoie que la constante d’interception et le(s) coefficient(s) de pente.

Étant donné que DROITEREG renvoie un tableau de valeurs, il doit être saisi sous forme de formule matricielle en appuyant sur la touche raccourci Ctrl + Maj + Entrée. S’il est entré sous forme de formule régulière, seul le premier coefficient de pente est renvoyé.

2. Statistiques supplémentaires renvoyées par DROITEREG( )

L’ argument statistique défini sur VRAI demande à la fonction DROITEREG de renvoyer les statistiques suivantes pour votre analyse de régression :

Statistique La description
Coefficient de pente b valeur en y = bx + a
Constante d’interception a valeur dans y = bx + a
Erreur type de pente La ou les valeurs d’erreur standard pour le ou les coefficients b.
Erreur standard d’interception La valeur d’erreur standard pour la constante a .
Coefficient de détermination (R 2 ) Indique dans quelle mesure l’équation de régression explique la relation entre les variables.
Erreur standard pour l’estimation Y Affiche la précision de l’analyse de régression.
Statistique F, ou la valeur F-observée Il est utilisé pour effectuer le test F pour l’hypothèse nulle afin de déterminer la qualité globale de l’ajustement du modèle.
Degrés de liberté (dl) Le nombre de degrés de liberté.
Somme des carrés de régression Indique dans quelle mesure la variation de la variable dépendante est expliquée par le modèle.
somme résiduelle des carrés Mesure la quantité de variance dans la variable dépendante qui n’est pas expliquée par votre modèle de régression.

3. Comment utiliser DROITEREG( ) dans Excel : exemples de formules

La fonction DROITEREG( ) peut être délicate à utiliser, en particulier pour les novices, car vous devez non seulement créer correctement une formule, mais également interpréter correctement sa sortie. Ci-dessous, vous trouverez quelques exemples d’utilisation de formules DROITEREG( ) dans Excel qui, espérons-le, aideront à approfondir les connaissances théoriques

3.1 Régression linéaire simple : calculer la pente et intercepter

Pour obtenir l’ordonnée à l’origine et la pente d’une droite de régression, vous utilisez la fonction DROITEREG( ) dans sa forme la plus simple : fournissez une plage de valeurs dépendantes pour l’argument de y_connus et une plage de valeurs indépendantes pour l’argument de x_connus . Les deux derniers arguments peuvent être définis sur VRAI ou omis.

Par exemple, avec des valeurs y (nombre de ventes) dans C2 : C13 et des valeurs x (coût publicitaire) dans B2 : B13, notre formule de régression linéaire est aussi simple que :

=DROITEREG(C2:C13 ; B2:B13)

Pour l’entrer correctement dans votre feuille de calcul, sélectionnez deux cellules adjacentes dans la même ligne, E2: F2 dans cet exemple, tapez la formule et appuyez sur Ctrl + Maj + Entrée pour le compléter.

La formule renverra le coefficient de pente dans la première cellule (E2) et la constante d’interception dans la deuxième cellule (F2) :

La pente est d’environ 0,52 (arrondie à deux décimales). Cela signifie que lorsque x augmente de 1, y augmente de 0,52.

L’ordonnée à l’origine est négative -4,99. C’est la valeur attendue de y lorsque x=0. S’il est tracé sur un graphique, il s’agit de la valeur à laquelle la ligne de régression croise l’axe des ordonnées.

Fournissez les valeurs ci-dessus à une simple équation de régression linéaire et vous obtiendrez la formule suivante pour prédire le nombre de ventes en fonction du coût publicitaire :

y = 0,52*x – 4,99

Par exemple, si vous dépensez 50 € en publicité, vous devez vendre 21 parapluies :

0.52*50 – 4.99 = 21.01

Les valeurs de pente et d’interception peuvent également être obtenues séparément en utilisant la fonction correspondante ou en imbriquant la formule DROITEREG dans INDEX :

Pente

=PENTE(C2:C13; B2:B13)

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

Ordonnée à l’origine

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

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

3.2 Régression linéaire multiple : pente et interception

Si vous avez deux ou plusieurs variables indépendantes, assurez-vous de les saisir dans des colonnes adjacentes et de fournir toute cette plage à l’ argument de x_connu .

Par exemple, avec des chiffres de ventes (valeurs y) dans D2 : D13, des frais publicitaires (un ensemble de valeurs x) dans B2 : B13 et des précipitations mensuelles moyennes (un autre ensemble de valeurs x ) dans C2 : C13, vous utilisez cette formule :

=DROITEREG(D2:D13 ; B2:C13)

Comme la formule va renvoyer un tableau de 3 valeurs (2 coefficients de pente et la constante d’interception), nous sélectionnons trois cellules contiguës dans la même ligne, saisissons la formule et appuyons sur la Ctrl + Maj + Entrée raccourci.

Veuillez noter que la formule de régression multiple renvoie les coefficients de pente dans l’ordre inverse des variables indépendantes (de droite à gauche), c’est-à-dire b n , b n-1 , …, b 2 , b 1 :

Pour prédire le nombre de ventes, nous fournissons les valeurs renvoyées par la formule DROITEREG à l’équation de régression multiple :

y = 0,3*x2 + 0,19*x1 – 10,74

Par exemple, avec 50 $ dépensés en publicité et une pluviométrie mensuelle moyenne de 100 mm, vous devriez vendre environ 23 parapluies :

0.3*50 + 0.19*100 – 10.74 = 23.26

Régression linéaire simple : prédire la variable dépendante

Outre le calcul des valeurs a et b pour l’équation de régression, la fonction Excel DROITEREG( ) peut également estimer la variable dépendante (y) en fonction de la variable indépendante connue (x). Pour cela, vous utilisez DROITEREG( ) en combinaison avec la fonction SOMME ou SOMMEPROD.

Par exemple, voici comment vous pouvez calculer le nombre de ventes de parapluies pour le mois suivant, disons octobre, sur la base des ventes des mois précédents et du budget publicitaire d’octobre de 50 € :

=SOMME(DROITEREG(C2:C10 ; B2:B10)*{50.1})

Au lieu de coder en dur la valeur x dans la formule, vous pouvez la fournir comme référence de cellule. Dans ce cas, vous devez également saisir la constante 1 dans certaines cellules car vous ne pouvez pas mélanger les références et les valeurs dans une constante matricielle.

Avec la valeur x dans E2 et la constante 1 dans F2, l’une ou l’autre des formules ci-dessous fonctionnera parfaitement :

Formule régulière (saisie en appuyant sur Entrée):

=SOMMEPROD(DROITEREG(C2:C10; B2:B10)*(E2:F2))

Formule matricielle (saisie en appuyant sur Ctrl + Maj + Entrée):

=SOMME(DROITEREG(C2:C10; B2:B10)*(E2:F2))

Pour vérifier le résultat, vous pouvez obtenir l’ordonnée à l’origine et la pente pour les mêmes données, puis utiliser la formule de régression linéaire pour calculer y :

=E2*G2+F2

Où E2 est la pente, G2 est la valeur x et F2 est l’ordonnée à l’origine :

3.3 Régression multiple : prédire la variable dépendante

Si vous avez affaire à plusieurs prédicteurs, c’est-à-dire à quelques ensembles différents de valeurs x , incluez tous ces prédicteurs dans la constante matricielle. Par exemple, avec un budget publicitaire de 50 $ (x2 ) et une pluviométrie mensuelle moyenne de 100 mm (x 1 ), la formule est la suivante :

=SOMME(DROITEREG(D2:D10; B2:C10)*{50.100.1})

Où D2:D10 sont les valeurs y connues et B2:C10 sont deux ensembles de valeurs x :

Veuillez faire attention à l’ordre des valeurs x dans la constante matricielle. Comme indiqué précédemment, lorsque la fonction Excel DROITEREG( )est utilisée pour effectuer une régression multiple , elle renvoie les coefficients de pente de droite à gauche. Dans notre exemple, le coefficient Publicité est renvoyé en premier, puis le coefficient Pluie . Pour calculer correctement le nombre de ventes prévu, vous devez multiplier les coefficients par les valeurs x correspondantes , vous placez donc les éléments de la constante de tableau dans cet ordre : {50.100.1}. Le dernier élément est 1, car la dernière valeur renvoyée par DROITEREG( )est l’interception qui ne doit pas être modifiée, vous la multipliez donc simplement par 1.

Au lieu d’utiliser une constante de tableau, vous pouvez entrer toutes les variables x dans certaines cellules et référencer ces cellules dans votre formule comme nous l’avons fait dans l’exemple précédent.

Formule régulière :

=SOMMEPROD(DROITEREG(D2:D10; B2:C10)*(F2:H2)) Formule matricielle :

=SOMME(DROITEREG(D2:D10; B2:C10)*(F2:H2))

Où F2 et G2 sont les valeurs x et H2 est 1 :

4. Formule DROITEREG : statistiques de régression supplémentaires

Comme vous vous en souvenez peut-être, pour obtenir plus de statistiques pour votre analyse de régression, vous mettez VRAI dans le dernier argument de la fonction DROITEREG( ). Appliquée à nos données d’échantillon, la formule prend la forme suivante :

=DROITEREG(D2:D13 ; B2:C13 ; VRAI ; VRAI)

Comme nous avons 2 variables indépendantes dans les colonnes B et C, nous sélectionnons une plage composée de 3 colonnes (deux valeurs x + intercept) et 5 lignes, entrez la formule ci-dessus, appuyez sur Ctrl + Maj + Entrée, et obtenez ce résultat :

Pour vous débarrasser des erreurs #N/A, vous pouvez imbriquer DROITEREG( )dans SIERREUR(  ) comme ceci :

=SIERREUR(DROITEREG(D2:D13; B2:C13; VRAI; VRAI); “”) La capture d’écran ci-dessous montre le résultat et explique la signification de chaque chiffre :

Les coefficients de pente et l’ordonnée à l’origine ont été expliqués dans les exemples précédents, alors jetons un coup d’œil aux autres statistiques.

Coefficient de détermination (R2 ). La valeur de R 2 est le résultat de la division de la somme des carrés de régression par la somme totale des carrés. Il vous indique combien de valeurs y sont expliquées par des variables x . Il peut s’agir de n’importe quel nombre compris entre 0 et 1, c’est-à-dire de 0 % à 100 %. Dans cet exemple, R 2 est d’environ 0,97, ce qui signifie que 97 % de nos variables dépendantes (ventes de parapluies) sont expliquées par les variables indépendantes (publicité + pluviométrie mensuelle moyenne), ce qui est un excellent ajustement !

Erreurs types . Généralement, ces valeurs montrent la précision de l’analyse de régression. Plus les nombres sont petits, plus vous pouvez être sûr de votre modèle de régression.

statistique F. Vous utilisez la statistique F pour confirmer ou rejeter l’hypothèse nulle. Il est recommandé d’utiliser la statistique F en combinaison avec la valeur P pour décider si les résultats globaux sont significatifs.

Degrés de liberté (dl). La fonction DROITEREG( )dans Excel renvoie les degrés de liberté résiduels , c’est-à-dire le df total moins le df de régression . Vous pouvez utiliser les degrés de liberté pour obtenir des valeurs critiques F dans un tableau statistique, puis comparer les valeurs critiques F à la statistique F pour déterminer un niveau de confiance pour votre modèle.

Somme des carrés de régression (alias somme des carrés expliquée ou somme des carrés du modèle ). C’est la somme des différences au carré entre les valeurs y prédites et la moyenne de y, calculée avec cette formule : =∑(ŷ – ȳ) 2 . Il indique la part de la variation de la variable dépendante expliquée par votre modèle de régression.

Somme des carrés résiduelle . C’est la somme des différences au carré entre les valeurs y réelles et les valeurs y prédites. Il indique la part de la variation de la variable dépendante que votre modèle n’explique pas. Plus la somme résiduelle des carrés est petite par rapport à la somme totale des carrés, plus votre modèle de régression s’adapte à vos données.

5 choses que vous devez savoir sur la fonction DROITEREG( )

Pour utiliser efficacement les formules DROITEREG( ) dans vos feuilles de calcul, vous souhaiterez peut-être en savoir un peu plus sur les “mécanismes internes” de la fonction :

  1. Y_connuset x_connus . Dans un modèle de régression linéaire simple avec un seul ensemble de variables x, les y_connus et les x_connus peuvent être des plages de n’importe quelle forme tant qu’ils ont le même nombre de lignes et de colonnes. Si vous effectuez une analyse de régression multiple avec plus d’un ensemble de variables x indépendantes, les y_connus doivent être un vecteur, c’est-à-dire une plage d’une ligne ou d’une colonne.
  2. Forcer la constante à zéro. Lorsque l’ argument constante est VRAI ou est omis, la constante a (ordonnée à l’origine) est calculée et incluse dans l’équation : y=bx + a. Si constante est défini sur FAUX, l’ordonnée à l’origine est considérée comme égale à 0 et omise de l’équation de régression : y=bx.

Dans les statistiques, on se demande depuis des décennies s’il est logique de forcer la constante d’interception à 0 ou non. De nombreux praticiens crédibles de l’analyse de régression pensent que si la définition de l’ordonnée à l’origine sur zéro (constante=FAUX) semble être utile, la régression linéaire elle-même est un mauvais modèle pour l’ensemble de données. D’autres supposent que la constante peut être forcée à zéro dans certaines situations, par exemple, dans le contexte de plans de discontinuité de régression. En général, il est recommandé d’utiliser la valeur par défaut constante=VRAI ou omise dans la plupart des cas.

  1. Précision. La précision de l’équation de régression calculée par la fonction DROITEREG( ) dépend de la dispersion de vos points de données. Plus les données sont linéaires, plus les résultats de la formule DROITEREG( ) sont précis.
  2. Valeurs x redondantes. Dans certaines situations, une ou plusieurs variables x indépendantes peuvent n’avoir aucune valeur prédictive supplémentaire, et la suppression de ces variables du modèle de régression n’affecte pas la précision des valeurs y prédites. Ce phénomène est connu sous le nom de « colinéarité ». La fonction Excel DROITEREG( ) vérifie la colinéarité et omet toutes les variables x redondantes qu’elle identifie dans le modèle. Les variables x omises peuvent être reconnues par 0 coefficients et 0 valeurs d’erreur standard.
  3. DROITEREG ou PENTE et ORDONNEE.ORIGINE . L’algorithme sous-jacent de la fonction DROITEREG( ) diffère de l’algorithme utilisé dans les fonctions PENTE() et ORDONNEE.ORIGINE . Par conséquent, lorsque les données source sont indéterminées ou colinéaires, ces fonctions peuvent renvoyer des résultats différents.

5 La fonction Excel DROITEREG( ) ne fonctionne pas

Si votre formule DROITEREG génère une erreur ou produit une mauvaise sortie, il y a de fortes chances que ce soit pour l’une des raisons suivantes :

  1. Si la fonction DROITEREG( ) ne renvoie qu’un seul nombre (coefficient de pente), vous l’avez probablement saisi sous la forme d’une formule normale et non d’une formule matricielle. Assurez-vous d’appuyer sur Ctrl + Maj + Entrée pour bien compléter la formule. Lorsque vous faites cela, la formule est enfermée entre les {accolades} qui sont visibles dans la barre de formule.
  2. #REF! Erreur. Se produit si les plages des x_connuset des y_connus ont des dimensions différentes.
  3. #VALEUR! Erreur. Se produit si les x_connusou les y_connus contiennent au moins une cellule vide, une valeur de texte ou une représentation textuelle d’un nombre qu’Excel ne reconnaît pas comme valeur numérique. En outre, l’erreur #VALUE se produit si l’ argument constante ou statistique ne peut pas être évalué sur VRAI ou FAUX.

C’est ainsi que vous utilisez ROITEREG( )  dans Excel pour une analyse de régression linéaire simple et multiple. 

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x