WorksheetFunction.LinEst(Object, Object, Object, Object) Méthode

public object LinEst(object Arg1, object Arg2, object Arg3, object Arg4);

Public Function LinEst (Arg1 As Object, Optional Arg2 As Object, Optional Arg3 As Object, Optional Arg4 As Object) As Object

Paramètres

Retours

Remarques

L'équation pour la droite est la suivante :

y = mx + b ou

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

où la valeur y dépendante est une fonction des valeurs x indépendantes. Les valeurs_m sont des coefficients correspondant à chaque valeur x et b est une valeur constante. Notez que x, y et m peuvent être des vecteurs. Le tableau retourné par LinEst est {mn,mn-1,...,m1,b}. LinEst peut également renvoyer des statistiques de régression supplémentaires.

Si la matrice y_connus comporte une seule colonne, chaque colonne de x_connus est interprétée comme une variable distincte.

Si la matrice y_connus comporte une seule ligne, chaque ligne de x_connus est interprétée comme une variable distincte.

La matrice x_connus peut inclure un ou plusieurs ensembles de variables. Si une seule variable est utilisée, les matrices y_connus et x_connus peuvent être des plages de valeurs de toute forme, tant que leurs dimensions sont égales. Si plusieurs variables sont utilisées, la matrice y_connus doit être un vecteur (c'est-à-dire, une plage de valeurs avec une hauteur d'une ligne ou une largeur d'une colonne).

Si la matrice x_connus est omise, c'est la matrice {1,2,3,...} qui est supposée avoir la même taille que la matrice y_connus.

Si l’argument constante est vrai ou omis, la constante b est calculée normalement.

Si constante est faux, b se voit attribuer la valeur 0 et les valeurs m sont ajustées de manière à ce que y = mx.

Si stats est truuue, LinEst renvoie les statistiques de régression supplémentaires, de sorte que le tableau renvoyé est {mn,mn-1,...,m1,b ; sen,sen-1,...,se1,seb ; r2,sey ; F,df ; ssreg,ssresid}.

Si statistiques est FAUX ou omis, LinEst renvoie uniquement les coefficients m et la constante b.

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

L'illustration ci-dessous indique l'ordre dans lequel les statistiques de régression supplémentaires sont renvoyées.

Figure 1 : Feuille de calcul

Vous pouvez décrire n’importe quelle droite avec la pente et l’ordonnée à l’origine :

Pente (m) :

Pour trouver la pente d’une droite, souvent écrite m, prenez deux points sur la droite, (x1,y1) et (x2,y2) ; La pente est égale à (Y2 - Y1)/(X2 - X1).

Ordonnée à l’origine (b) :

L'intersection y d'une droite, souvent désignée par b, est la valeur de y au point auquel la droite croise l'axe y.

L'équation d'une droite est y = mx + b. Une fois que vous connaissez les valeurs de m et b, vous pouvez calculer les points sur la droite en insérant la valeur y ou x dans l'équation. Vous pouvez également utiliser la Trend(Object, Object, Object, Object) fonction.

Si vous n’avez qu’une variable indépendante x, vous pouvez obtenir directement les valeurs de pente et d’ordonnée à l’origine à l’aide des formules suivantes :

Pente :

=INDEX(LINEST(known_y ;known_x’s) ;1)

Interception Y :

=INDEX(LINEST(known_y's,known_x's),2)

La précision de la ligne calculée par LineEst dépend du degré de nuage de points dans vos données. Plus les données sont linéaires, plus le modèle LineEst est précis. LineEst utilise la méthode des moindres carrés pour déterminer le meilleur ajustement pour les données. Lorsque vous ne disposez qu'une variable x indépendante, le calcul de m et de b se base sur les formules suivantes :

Figure 2 : Équation

Figure 3 : Équation

où x et y sont des moyennes de l'échantillon, c'est-à-dire x = AVERAGE(x_connus) et y = AVERAGE(y_connus).

Les fonctions d’ajustement linéaire et de courbe LineEst et LogEst(Object, Object, Object, Object) peuvent calculer la meilleure droite ou courbe exponentielle qui correspond à vos données. Cependant, vous devez déterminer le résultat qui est le plus ajusté à vos données. Vous pouvez calculer TREND(y_connus,x_connus) pour une droite ou GROWTH(y_connus,x_connus) pour une courbe exponentielle. Ces fonctions, sans l'argument nouvel x, renvoient une matrice de valeurs y prévues sur cette droite ou cette courbe à vos points de données réels. Vous pouvez comparer les valeurs prévues avec les valeurs réelles. Vous pouvez les représenter graphiquement pour effectuer une comparaison visuelle.

Dans l’analyse de régression, Microsoft Excel calcule pour chaque point la différence au carré entre la valeur y estimée pour ce point et sa valeur y réelle. La somme de ces différences au carré est appelée la somme résiduelle des carrés, ssresid. Microsoft Excel calcule ensuite la somme totale des carrés, sstotal. Lorsque constante = vrai ou omis, la somme totale des carrés correspond à la somme des différences au carré entre les valeurs y réelles et la moyenne des valeurs y. Lorsque constante = faux, la somme totale des carrés correspond à la somme des carrés des valeurs y réelles (sans soustraire la valeur y moyenne de chaque valeur y individuelle). Ensuite, la somme des carrés de régression, ssreg, peut être trouvée à partir de : ssreg = sstotal - ssresid. Plus la somme résiduelle des carrés est petite par rapport à la somme totale des carrés, plus la valeur du coefficient de détermination, r2, est élevée, qui est un indicateur de la façon dont l’équation résultant de l’analyse de régression explique la relation entre les variables. R2 est égal à SSREG/SSTOTAL.

Dans certains cas, une ou plusieurs des colonnes X (en supposant que les colonnes Y et X se trouvent dans des colonnes) peuvent n’avoir aucune valeur prédictive supplémentaire en présence des autres colonnes X. En d'autres termes, la suppression d'une ou de plusieurs colonnes X peut permettre d'obtenir des valeurs Y avec une précision égale. En pareil cas, ces colonnes X redondantes doivent être omises du modèle de régression. Ce phénomène est appelé « colinéarité » car toute colonne X redondante peut être exprimée comme une somme de multiples des colonnes X non redondantes. LinEst vérifie la colinéarité et supprime toutes les colonnes X redondantes du modèle de régression lorsqu’il les identifie. Les colonnes X supprimées peuvent être reconnues dans la sortie LinEst comme ayant des coefficients aussi bien que 0 se. Si une ou plusieurs colonnes sont supprimées parce qu'elles sont redondantes, df est affecté car df dépend du nombre de colonnes X effectivement utilisées pour des prévisions. Si df est modifié parce que des colonnes X sont supprimées, la valeur de sey et de F est également affectée. Dans la pratique, la colinéarité est relativement rare. Cependant, un cas où cela est plus susceptible de se produire est lorsque certaines colonnes X ne contiennent que des 0 et des 1 comme indicateurs pour savoir si un sujet d’une expérience est ou n’est pas membre d’un groupe particulier. Si const = vrai ou omis, LinEst insère effectivement une colonne X supplémentaire de tous les 1 pour modéliser l’interception. Si vous avez une colonne avec un 1 pour chaque sujet s’il s’agit d’un homme, ou 0 dans le cas contraire, et que vous avez également une colonne avec un 1 pour chaque sujet s’il s’agit d’une femme, ou 0 dans le cas contraire, cette dernière colonne est redondante car les entrées qui y figurent peuvent être obtenues en soustrayant l’entrée de la colonne « indicateur masculin » de l’entrée dans la colonne supplémentaire de tous les 1 ajoutés par LineEst.

df est calculé 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 de known_x et const = vrai ou omis, alors df = n – k – 1. Si const = false, alors df = n - k. Dans les deux cas, chaque colonne X supprimée en raison de la colinéarité augmente df de 1.

Les formules qui renvoient des matrices doivent être saisies sous forme de formules matricielles. Lorsque vous entrez une constante matricielle comme x_connus comme argument, utilisez des virgules pour séparer les valeurs sur la même ligne et des points-virgules pour séparer les lignes. Les caractères séparateurs peuvent être différents selon votre paramètre régional dans les options régionales et linguistiques du Panneau de configuration.

Notez que les valeurs y prévues par l'équation de régression peuvent être incorrectes si elles se trouvent en dehors de la plage de valeurs y utilisées pour déterminer l'équation. L’algorithme sous-jacent utilisé dans la fonction EstDroite est différent de l’algorithme sous-jacent utilisé dans les Slope(Object, Object) fonctions et Intercept(Object, Object) . La différence entre ces algorithmes peut conduire à des résultats différents lorsque les données ne sont pas déterminées et qu'elles sont colinéaires. Par exemple, si les points de données de l'argument y_connus prennent la valeur 0 et que ceux de l'argument y_connus prennent la valeur 1 :

LineEst renvoie la valeur 0. L’algorithme LinEst est conçu pour renvoyer des résultats raisonnables pour les données colinéaires, et dans ce cas, au moins une réponse peut être trouvée. Slope(Object, Object) et Intercept(Object, Object) renvoie un #DIV/0 ! erreur. L’algorithme Slope(Object, Object) et Intercept(Object, Object) est conçu pour rechercher une seule et unique réponse, et dans ce cas, il peut y avoir plusieurs réponses.