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

Définition

Espace de noms:

Microsoft.Office.Interop.Excel

Assembly:

Microsoft.Office.Interop.Excel.dll

Calcule les statistiques pour une droite à l'aide de la méthode des « moindres carrés » pour calculer une droite qui correspond mieux à vos données et renvoie une matrice qui décrit la droite. Comme cette fonction renvoie une matrice de valeurs, elle doit être entrée sous forme de formule matricielle.

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

Arg1

Object

y_connus - ensemble de valeurs y que vous connaissez dans la relation y = mx + b.

Arg2

Object

x_connus - ensemble de valeurs x facultatives que vous connaissez peut-être déjà dans la relation y = mx + b.

Arg3

Object

Const - valeur logique indiquant si la constante b doit être forcée pour être égale à 0.

Arg4

Object

Stats - valeur logique qui spécifie si des statistiques de régression supplémentaires doivent être renvoyées.

Retours

Object

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 retourner 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 const est true ou omis, b est calculé normalement.

Si const a la valeur false, b est défini sur 0 et les valeurs m sont ajustées pour ajuster y = mx.

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

Si les statistiques sont false ou omises, LinEst retourne uniquement les coefficients m et la constante b.

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

se1,se2,...,sen	Les valeurs d'erreur type pour les coefficients m1,m2,...,mn.
seb	Valeur d’erreur standard pour la constante b (seb = #N/A quand const a la valeur false).
R2	Coefficient de détermination. Compare les valeurs y estimées et réelles, et les plages de valeur comprises entre 0 et 1. S’il est 1, il y a une corrélation parfaite dans l’échantillon : il n’y a aucune différence entre la valeur y estimée et la valeur y réelle. À l’autre extrême, si le coefficient de détermination est 0, l’équation de régression n’est pas utile pour prédire une valeur y.
sey	L'erreur type pour l'estimation de y.
F	La statistique F ou valeur F observée. Utilisez la statistique F pour déterminer si la relation observée entre les variables dépendantes et les variables indépendantes est le fruit du hasard.
df	Les degrés de liberté. Utilisez les degrés de liberté pour vous aider à trouver les valeurs critiques F dans un tableau de statistiques. Comparez les valeurs que vous trouvez dans la table à la statistique F retournée par LinEst pour déterminer un niveau de confiance pour le modèle.
ssreg	La somme des carrés de régression.
ssresid	La somme des carrés résiduelle.

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 ligne droite avec la pente et l’intercept y :

Pente (m) :

Pour trouver la pente d’une ligne, souvent écrite sous la forme m, prenez deux points sur la ligne, (x1,y1) et (x2,y2) ; la pente est égale à (y2 - y1)/(x2 - x1).

Interception Y (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 .

Lorsque vous n’avez qu’une seule variable x indépendante, vous pouvez obtenir les valeurs de pente et d’interception y directement à l’aide des formules suivantes :

Pente:

=INDEX(LINEST(known_y’s,known_x),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 line- et curve-fitting fonctionnent LineEst et LogEst(Object, Object, Object, Object) peuvent calculer la meilleure courbe droite ou 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 quadratique entre la valeur y estimée pour ce point et sa valeur y réelle. La somme de ces différences carrées est appelée somme résiduelle de carrés, ssresid. Microsoft Excel calcule ensuite la somme totale des carrés, sstotal. Lorsque const = true, ou omis, la somme totale des carrés est la somme des différences carrées entre les valeurs y réelles et la moyenne des valeurs y. Lorsque const = false, la somme totale des carrés est la 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, est disponible à 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 grande, ce 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 colonnes X (supposons que 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é « collinearité », car toute colonne X redondante peut être exprimée sous la forme d’une somme de multiples des colonnes X non redondantes. LinEst vérifie la collinearité 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 de 0 et 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. Toutefois, un cas où cela est le plus probable est lorsque certaines colonnes X contiennent uniquement 0 et 1 comme indicateurs indiquant si un sujet d’une expérience est ou non membre d’un groupe particulier. Si const = true ou omis, LinEst insère effectivement une colonne X supplémentaire des 1 pour modéliser l’interception. Si vous avez une colonne avec un 1 pour chaque sujet s’il est masculin, ou 0 si ce n’est pas le cas, et que vous avez également une colonne avec un 1 pour chaque sujet si féminin, ou 0 si ce n’est pas le cas, cette dernière colonne est redondante, car les entrées qu’elle contient 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 quand aucune colonne X n’est supprimée du modèle en raison de la collinearité : s’il existe k colonnes de known_x et const = true ou omis, df = n – k – 1. Si const = false, alors df = n - k. Dans les deux cas, chaque colonne X supprimée en raison de la collinearité 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 de séparation peuvent être différents en fonction de vos paramètres régionaux dans Options régionales et linguistiques dans 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 LinEst 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 retourne la valeur 0. L’algorithme LinEst est conçu pour retourner des résultats raisonnables pour les données collineaires, et dans ce cas, au moins une réponse peut être trouvée. Slope(Object, Object) et Intercept(Object, Object) retourner un #DIV/0 ! Erreur. L’algorithme Slope(Object, Object) et Intercept(Object, Object) est conçu pour rechercher une seule réponse, et dans ce cas, il peut y avoir plusieurs réponses.