Afficher tout

Masquer tout

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.

L'équation de la droite est la suivante :

y = mx + b ou

y = m1x1 + m2x2 + ... + b (s'il existe plusieurs plages de valeurs x)

où la valeur dépendante y est une fonction des valeurs indépendantes x. Les valeurs m sont des coefficients correspondant à chaque valeur x et b est une valeur constante. Vous remarquerez que y, x et m peuvent être des vecteurs. La matrice renvoyée par la fonction DROITEREG est de la forme {mn.mn-1.....m1.b}. La fonction DROITEREG peut également renvoyer des statistiques de régression supplémentaires.

Syntaxe

DROITEREG(y_connus;x_connus;constante;statistiques)

y_connus   est la série des valeurs y déjà connues par la relation y = mx + b.

• Si la matrice définie par l'argument y_connus occupe une seule colonne, chaque colonne de l'argument x_connus est interprétée comme étant une variable distincte.

• Si la matrice définie par l'argument y_connus occupe une seule ligne, chaque ligne de l'argument x_connus est interprétée comme étant une variable distincte.

x_connus   est une série de valeurs x facultatives, éventuellement déjà données par la relation y = m x + b.

• La matrice définie par l'argument x_connus peut contenir une ou plusieurs séries de variables. Si vous utilisez une seule variable, les arguments y_connus et x_connus peuvent être des plages de forme différente, à condition qu'elles aient la même dimension. Si vous utilisez plusieurs variables, l'argument y_connus doit être un vecteur (en d'autres termes, une plage comportant une seule ligne ou une seule colonne).

• Si l'argument x_connus est omis, il est supposé égal à la matrice {1.2.3....}, de même ordre que l'argument y_connus.

constante   représente une valeur logique précisant si la constante b doit être forcée à 0.

• Si l'argument constante est VRAI ou omis, la constante b est calculée normalement.

• Si l'argument constante est FAUX, b est égal à 0 et les valeurs m sont ajustées de sorte que y = mx.

statistiques   représente une valeur logique indiquant si d'autres statistiques de régression doivent être renvoyées.

• Si l'argument statistiques est VRAI, la fonction DROITEREG renvoie des statistiques de régression supplémentaires et la matrice renvoyée devient : {mn.mn-1.....m1.b; sen.sen-1.....se1.seb; r2.sey; F.df; ssreg.ssresid}.

• Si l'argument statistiques est FAUX ou omis, la fonction DROITEREG renvoie uniquement les coefficients m et la constante b.

Les statistiques de régression supplémentaires sont les suivantes :

L'illustration suivante montre l'ordre dans lequel les statistiques de régression supplémentaires sont renvoyées.

Notes

• Toute droite peut être décrite par sa pente et son ordonnée à l'origine :

  Pente (m) :
  Pour déterminer la pente d'une droite, généralement désignée par la lettre m, prenez deux points de la droite, (x1,y1) et (x2,y2). La pente est alors égale à (y2 - y1)/(x2 - x1).

  Ordonnée à l'origine :
  L'ordonnée à l'origine d'une droite, généralement désignée par la lettre b, est la valeur de y au point d'intersection de la droite avec l'axe des ordonnées (y).

  L'équation d'une droite est y = mx + b. Une fois connues les valeurs de m et de b, chaque point de la droite peut être calculé en fixant la valeur x ou y dans l'équation. Vous pouvez également utiliser la fonction TENDANCE.

• Si vous utilisez une seule variable indépendante x, vous pouvez obtenir directement les valeurs de la pente et de l'ordonnée à l'origine de la droite à l'aide des formules suivantes :

  Pente :
  =INDEX(DROITEREG(y_connus; x_connus); 1)

  Ordonnée à l'origine :
  =INDEX(DROITEREG(y_connus; x_connus); 2)

• L'exactitude de la droite calculée par la fonction DROITEREG dépend du degré de dispersion de vos données. Le modèle de la fonction DROITEREG sera d'autant plus exact que les données seront plus linéaires. La fonction DROITEREG utilise la méthode des moindres carrés pour calculer le meilleur ajustement à vos données. Lorsque vous ne disposez que d'une seule variable indépendante x, les calculs de m et b s'appuient sur les formules suivantes :

  

  

  où x et y sont des moyennes d'échantillon, à savoir x = MOYENNE(x_connus) et y = MOYENNE(y_connus).

• Les fonctions de régression DROITEREG et LOGREG calculent, l'une la droite et l'autre la courbe exponentielle, qui s'ajuste au plus près à vos données. Cependant, il vous appartient de décider laquelle de ces deux méthodes s'ajuste le mieux. Pour ce faire, vous pouvez calculer TENDANCE(y_connus;x_connus) pour une droite ou CROISSANCE(y_connus; x_connus) pour une courbe exponentielle. Ces fonctions, qui ne comportent pas d'argument nouveaux_x, renvoient une matrice de valeurs y prédites le long de la droite ou de la courbe correspondant à vos observations réelles. Vous pouvez alors comparer les valeurs prédites avec les valeurs réelles. Il se peut que vous souhaitiez les représenter sous forme de graphique pour effectuer une comparaison visuelle.
• Dans une analyse de régression, Microsoft Excel calcule, pour chaque point, le carré de la différence entre les valeurs y estimée et réelle. La somme de ces différences quadratiques est appelée « somme résiduelle des carrés », ssresid. Microsoft Excel calcule ensuite la somme totale des carrés, sstotal. Si l'argument constante est VRAI ou omis, la somme totale des carrés est la somme des différences quadratiques entre les valeurs y réelles et la moyenne des valeurs y. Si l'argument constante est FAUX, la somme totale des carrés (= somme des carrés des valeurs y réelles (sans soustraire la valeur y moyenne de chaque valeur y individuelle). Ensuite, la somme de régression des carrés, ssreg, peut être trouvée en faisant : ssreg = sstotal - ssresid. Plus la somme résiduelle des carrés est petite comparée à la somme totale des carrés et plus la valeur du coefficient de détermination r2 est élevée, ce qui indique que l'équation résultant de l'analyse de régression explique la relation entre les variables de façon satisfaisante. Le coefficient r2 est égal à ssreg/sstotal.
• Dans certains cas, une ou plusieurs colonnes X (supposons que les Y et les X sont dans des colonnes) peuvent n'avoir aucune valeur prédictive supplémentaire en présence d'autres colonnes X. En d'autres termes, l'élimination d'une ou plusieurs colonnes X peut mener à des valeurs Y prédites aussi précises. Dans ce cas, les colonnes X redondantes doivent être omises du modèle de régression. Ce phénomène s'appelle « collinéarité » parce que n'importe quelle colonne X redondante peut être exprimée comme une somme de multiples des colonnes X non redondantes. DROITEREG vérifie la colinéarité et supprime toutes les colonnes X redondantes détectées dans le modèle de régression. Les colonnes X supprimées se distinguent dans les résultats de la fonction LINEST par le fait qu'elles ont 0 coefficient et 0 se. Si une ou plusieurs colonnes sont supprimées comme redondantes, les df sont influencés puisqu'ils dépendent du nombre de colonnes X utilisées à des fins prédictives. L'exemple 4 ci-dessous vous donne plus d'informations sur le calcul de df. Si la valeur de df est modifiée en raison de la suppression de colonnes X redondantes, les valeurs de sey et de F sont également influencées. Dans la pratique, la colinéarité devrait être relativement rare. Toutefois, un des cas où elle pourrait se produire est lorsque certaines colonnes X ne contiennent que des 0 et des 1 comme indicateurs de l'appartenance ou de la non-appartenance d'un sujet dans une expérience à un groupe en particulier. Si l'argument constante est VRAI ou omis, DROITE.REG insère une colonne X supplémentaire avec tous les 1 pour modéliser l'ordonnée à l'origine. Si vous avez une colonne avec un 1 pour chaque sujet mâle, ou 0 si vous n'en avez pas, ou si vous avez une colonne avec un 1 pour chaque sujet femelle, ou 0 si vous n'en avez pas, cette colonne est redondante parce que les entrées qui la composent peuvent être obtenues par la soustraction de l'entrée dans la colonne d' « indicateurs mâles de l'entrée ajoutée par DROITE.REG dans la colonne supplémentaire de tous les 1.
• La valeur de df est calculée comme suit lorsqu'aucune colonne X n'est supprimée du modèle en raison de la colinéarité : s'il y a k colonnes et x_connus et que l'argument constante est VRAIE ou omise, alors df = n – k – 1. Si l'argument constante est FAUX, alors df = n - k. Dans les deux cas, chaque colonne X supprimée en raison de la colinéarité augmente la valeur de df de 1.
• Les formules qui renvoient des matrices doivent être tapées sous forme matricielle en validant avec CTRL+MAJ+ENTRÉE
• Lorsque vous tapez comme argument une constante matricielle telle que x_connus, utilisez le point pour séparer les valeurs d'une même ligne et le point-virgule pour séparer les lignes. Les caractères séparateurs peuvent varier selon les paramètres régionaux définis dans Paramètres Régionaux ou Options régionales du Panneau de configuration.
• Notez que les valeurs y prédites par l'équation de régression peuvent ne pas être valides si elles se trouvent en dehors de la plage des valeurs y utilisées pour déterminer cette équation.

Exemple 1   Pente et ordonnée à l'origine

L'exemple sera plus compréhensible si vous le copiez dans une feuille de calcul vide.

Remarque  La formule de l'exemple doit être tapée sous forme de formule matricielle. Après avoir copié l'exemple dans une feuille de calcul vide, sélectionnez la plage A7:B7 qui commence par la cellule de formule. Appuyez sur F2 et sur CTRL+MAJ+ENTRÉE. Si la formule n'est pas saisie sous forme de formule matricielle, le seul résultat est 2.

Si elles sont tapées sous forme de matrice, la pente (2) et l'ordonnée à l'origine (1) sont renvoyées.

Exemple 2   Régression linéaire simple

L'exemple sera plus compréhensible si vous le copiez dans une feuille de calcul vide.

En règle générale, SOMME({m.b}*{x.1}) égale mx + b, soit la valeur y estimée pour une valeur x donnée. Vous pouvez également utiliser la fonction TENDANCE.

Exemple 3   Régression linéaire multiple

Supposons qu'un promoteur envisage d'acquérir un ensemble de petits immeubles de bureaux dans un quartier d'affaires.

Ce promoteur peut utiliser une analyse de régression linéaire multiple pour estimer la valeur d'un immeuble de bureaux dans un quartier donné, en fonction des variables suivantes :

Cet exemple suppose qu'il existe une relation linéaire entre chaque variable indépendante (x1, x2, x3 et x4) et la variable dépendante (y) représentant la valeur immobilière des immeubles de bureaux dans le quartier considéré.

Le promoteur choisit au hasard un échantillon de 11 immeubles de bureaux sur une population de 1 500 et obtient les données suivantes : Les « demi-entrées » correspondent à des entrées réservées aux fournisseurs.

L'exemple sera plus compréhensible si vous le copiez dans une feuille de calcul vide.

Remarque  La formule de l'exemple doit être tapée sous forme de formule matricielle. Après avoir copié l'exemple dans une feuille de calcul vide, sélectionnez la plage A14:E18 qui commence par la cellule de formule. Appuyez sur F2 et sur CTRL+MAJ+ENTRÉE. Si la formule n'est pas saisie sous forme de formule matricielle, le seul résultat est -234,2371645.

Si la formule est tapée sous forme de matrice, les statistiques de régression suivantes sont renvoyées. Utilisez cette combinaison de touches pour identifier la statistique souhaitée.

L'équation de régression multiple, y = m1*x1 + m2*x2 + m3*x3 - m4*x4 + b, est alors obtenue à l'aide des valeurs de la ligne 14 :

y = 27,64*x1 + 12 530*x2 + 2 553*x3 - 234,24*x4 + 52 318

Le promoteur peut désormais utiliser l'équation suivante pour estimer la valeur immobilière, dans ce même quartier, d'un immeuble de bureaux vieux de 25 ans, occupant une superficie de 800 mètres carrés et comportant trois bureaux et deux entrées :

y = 494*800 + 75 013*3 + 15 593*2 - 1 400*25 + 316 229 = 932 654 F

Ou vous pouvez copier la table suivante dans la cellule A21 du classeur de l'exemple.

Vous pouvez également utiliser la fonction TENDANCE pour calculer cette valeur.

Exemple 4   Utilisation des statistiques F et r2

Dans l'exemple précédent, le coefficient de détermination r2 est égal à 0,99645 (cellule A17 de la matrice des résultats de la fonction DROITEREG), ce qui laisse supposer une relation étroite entre les variables indépendantes et le prix de vente. La statistique F vous permet de déterminer si les résultats présentant cette valeur de r2 élevée sont le fruit du hasard.

Supposons un instant qu'il n'existe pas de relation véritable entre les variables, mais que l'échantillon de 11 immeubles de bureaux constitué est tel que son analyse statistique démontre une relation étroite. On appelle Alpha la probabilité de se tromper en concluant à l'existence d'une relation.

Vous pouvez utiliser F et df dans les résultats de la fonction DROITE.REG pour évaluer la possibilité d'obtenir une valeur F supérieure par hasard. F peut être comparé avec les valeurs critiques dans les tables de distribution F publiées ou vous pouvez utiliser la fonction LOI.F d'Excel pour calculer la probabilité qu'une valeur F plus élevée se produise par hasard. La distribution F appropriée a les degrés de liberté v1 et v2. Si n est le nombre d'observations et que l'argument constante est VRAI ou omis, alors v1 = n – df – 1 et v2 = df. (Si l'argument constante = FAUX, alors v1 = n – df et v2 = df.) La LOI.F d'Excel (F;v1;v2) renverra la probabilité qu'une valeur F plus élevée se produise par hasard. Dans l'exemple 4, df = 6 (cellule B18) et F = 459.753674 (cellule A18).

En partant de l'hypothèse d'une valeur Alpha de 0,05, avec v1 = 11 – 6 – 1 = 4 et v2 = 6, le niveau critique de F est 4,53. La valeur de F est 459,753674, à savoir plus que 4,53. Il est fort probable que ce chiffre élevé soit le fruit du hasard. (Avec Alpha = 0,05, l'hypothèse qu'il n'y a pas de relation entre les y_connus et les x_connus doit être rejetée lorsque F dépasse le niveau critique de 4,53). En utilisant la LOI.F d'Excel, vous pouvez obtenir la probabilité qu'une valeur F si élevée soit le fruit du hasard. LOI.F(459,753674; 4; 6) = 1,37E-7, une probabilité extrêmement faible. Vous pouvez conclure, soit en déterminant le niveau critique de F dans un tableau, soit en utilisant la LOI.F d'Excel, que l'équation de régression est utile dans l'estimation de la valeur immobilière d'un immeuble de bureau dans ce quartier. N'oubliez pas qu'il est essentiel d'utiliser des valeurs correctes de v1 et v2 telles qu'elles sont calculées au paragraphe précédent.

Exemple 5   Calcul de la statistique T

Un autre test d'hypothèse permet de déterminer si chaque coefficient de pente intervient dans l'estimation de la valeur immobilière d'un immeuble de bureaux proposée dans l'exemple 3. Par exemple, pour tester la signification statistique du coefficient d'âge, divisez -1400,23 (le coefficient de la pente âge) par 82,896 (l'erreur type estimée des coefficients d'âge renvoyée dans la cellule A15). Cela donne la valeur t observée suivante :

t = m4 ÷ se4 = -1400,23 ÷ 82,896 = -16,891

Si la valeur absolue de t est suffisamment élevée, vous pouvez conclure que le coefficient de pente est utile dans l'estimation de la valeur immobilière d'un immeuble de bureaux dans l'exemple 3. Le tableau ci-dessous illustre les valeurs absolues des 4 valeurs t observées.

Si vous vous reportez à la table correspondante d'un manuel de statistique, vous trouverez que la valeur critique t, bilatérale, pour 6 degrés de liberté et Alpha = 0,05 est 2 447. Cette valeur critique peut également être trouvée au moyen de la fonction LOI.STUDENT.INVERSE d'Excel. Dans la mesure où la valeur absolue de t (16,89) est supérieure à 1,94, l'âge est une variable significative dans l'estimation de la valeur immobilière d'un immeuble de bureaux. On peut ainsi tester la signification statistique de chacune des autres variables indépendantes. Le tableau suivant récapitule les valeurs t observées pour chaque variable indépendante.

Toutes ces valeurs sont supérieures à 2,447 en valeur absolue. Par conséquent, toutes les variables utilisées dans l'équation de régression sont utiles pour prédire la valeur immobilière des immeubles de bureaux de ce quartier.