Statistiques Excel avancées - Fonction LINEST

La description

La fonction LINEST calcule les statistiques pour une ligne à l'aide de la méthode des «moindres carrés» pour calculer une ligne droite qui correspond le mieux à vos données, puis renvoie un tableau qui décrit la ligne.

Vous pouvez également combiner LINEST avec d'autres fonctions pour calculer les statistiques pour d'autres types de modèles linéaires dans les paramètres inconnus, y compris les séries polynomiales, logarithmiques, exponentielles et puissantes.

Étant donné que cette fonction renvoie un tableau de valeurs, elle doit être saisie sous forme de formule matricielle.

Syntaxe

LINEST (known_y's, [known_x's], [const], [stats])

Arguments

Argument	La description	Obligatoire / facultatif
connu_y	L'ensemble des valeurs y que vous connaissez déjà dans la relation y = mx + b. Si la plage de known_y est dans une seule colonne, chaque colonne de known_x est interprétée comme une variable distincte. Si la plage de known_y's est contenue dans une seule ligne, chaque ligne de known_x est interprétée comme une variable distincte.	Obligatoire
known_x's	Un ensemble de valeurs x que vous connaissez peut-être déjà dans la relation y = mx + b. La plage de known_x peut inclure un ou plusieurs ensembles de variables. Si une seule variable est utilisée, known_y's et known_x's peuvent être des plages de n'importe quelle forme, tant qu'elles ont des dimensions égales. Si plusieurs variables sont utilisées, known_y's doit être un vecteur (c'est-à-dire une plage avec une hauteur d'une ligne ou une largeur d'une colonne). Si known_x's est omis, il est supposé être le tableau {1,2,3, ...} qui est de la même taille que known_y's.	Optionnel
const	Une valeur logique spécifiant s'il faut forcer la constante b à égaler 0. Si const est TRUE ou omis, b est calculé normalement. Si const est FALSE, b est égal à 0 et les valeurs m sont ajustées pour s'adapter à y = mx.	Optionnel
Statistiques	Une valeur logique spécifiant s'il faut renvoyer des statistiques de régression supplémentaires. Si stats est TRUE, LINEST renvoie les statistiques de régression supplémentaires. En conséquence, le tableau renvoyé est {mn, mn-1, ..., m1, b; sen, sen-1, ..., se1, seb; r2, sey; F, df; ssreg, ssresid}. Si stats est FALSE ou omis, LINEST renvoie uniquement les mcoefficients et la constante b. Les statistiques de régression supplémentaires sont indiquées dans le tableau ci-dessous.	Optionnel

Statistiques de régression supplémentaires

Sr. Non	Statistiques et description
1	se1,se2,...,sen Les valeurs d'erreur standard pour les coefficients m1, m2, ..., mn.
2	seb La valeur d'erreur standard pour la constante b (seb = # N / A lorsque const est FALSE).
3	r2 Le coefficient de détermination. Compare les valeurs y estimées et réelles, et varie de 0 à 1. Si la valeur est 1, il existe une corrélation parfaite dans l'échantillon - il n'y a pas de 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 égal à 0, l'équation de régression n'est pas utile pour prédire une valeur y. Pour plus d'informations sur le calcul de r2, reportez-vous aux remarques ci-dessous.
4	sey L'erreur standard pour l'estimation y.
5	F La statistique F, ou la valeur F-observée. Utilisez la statistique F pour déterminer si la relation observée entre les variables dépendantes et indépendantes se produit par hasard.
6	df Les degrés de liberté. Utilisez les degrés de liberté pour vous aider à trouver les valeurs F-critiques dans un tableau statistique. Comparez les valeurs que vous trouvez dans le tableau à la statistique F renvoyée par LINEST pour déterminer un niveau de confiance pour le modèle. Pour plus d'informations sur le calcul de df, reportez-vous aux remarques ci-dessous.
sept	ssreg La somme des carrés de régression.
8	ssreg La somme résiduelle des carrés. Pour plus d'informations sur le calcul de ssreg et ssresid, reportez-vous aux remarques ci-dessous.

Remarques

L'équation de la ligne est -

y = mx + b

ou

y = m1x1 + m2x2 + ... + b
S'il existe plusieurs plages de valeurs x, où les valeurs y dépendantes sont une fonction des valeurs x indépendantes, alors -
- Les valeurs m sont des coefficients correspondant à chaque valeur x et b est une valeur constante.
- Notez que y, x et m peuvent être des vecteurs.
Le tableau renvoyé par la fonction LINEST est {mn, mn-1… m1, b}.
LINEST peut également renvoyer des statistiques de régression supplémentaires
Vous pouvez décrire n'importe quelle ligne droite avec la pente et l'ordonnée à l'origine -
- Slope(m) -
  
  Pour trouver la pente d'une ligne, souvent écrite en m, prenez deux points sur la ligne, (x1, y1) et (x2, y2). La pente est égale à
  
  (–2 - y1) / (- 2 - x1).
- Y-intercept(b) -
  
  L'ordonnée à l'origine d'une ligne, souvent écrite sous la forme b, est la valeur de y au point où la ligne croise l'axe des y.

L'équation d'une ligne droite est y = mx + b. Une fois que vous connaissez les valeurs de m et b, vous pouvez calculer n'importe quel point sur la ligne en branchant la valeur y ou x dans cette équation. Vous pouvez également utiliser la fonction TREND.
Lorsque vous n'avez qu'une seule variable x indépendante, vous pouvez obtenir les valeurs de pente et yintercept directement en utilisant les formules suivantes -
- Slope -
  
  = INDEX (LINEST (known_y's, known_x's), 1)
- Y-intercept -
  
  = INDEX (LINEST (known_y's, known_x's), 2)
La précision de la ligne calculée par la fonction LINEST dépend du degré de dispersion de vos données. Plus les données sont linéaires, plus le modèle LINEST est précis.
LINEST utilise la méthode des moindres carrés pour déterminer le meilleur ajustement pour les données. Lorsque vous n'avez qu'une seule variable x indépendante, les calculs pour m et b sont basés sur les formules suivantes -

$$ m = \ frac {\ sum \ left (x- \ bar {x} \ right) \ left (y- \ bar {y} \ right)} {\ sum \ left (x- \ bar {x} \ droite) ^ 2} $$

Où x et y sont des moyennes d'échantillon. c'est à dire

x = MOYENNE (x connus)

y = MOYENNE (y_connus)
Les fonctions d'ajustement de ligne et de courbe LINEST et LOGEST peuvent calculer la meilleure ligne droite ou courbe exponentielle qui correspond à vos données. Cependant, vous devez décider lequel des deux résultats correspond le mieux à vos données. Vous pouvez calculer TREND (known_y's, known_x's) pour une ligne droite, ou GROWTH (known_y's, known_x's) pour une courbe exponentielle. Ces fonctions, sans omettre l'argument de known_x, renvoient un tableau de valeurs y prédites le long de cette ligne ou de cette courbe à vos points de données réels. Vous pouvez ensuite comparer les valeurs prévues avec les valeurs réelles. Vous voudrez peut-être les représenter tous les deux pour une comparaison visuelle.
Dans l'analyse de régression, 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 somme résiduelle des carrés, ssresid. Excel calcule ensuite la somme totale des carrés, sstotal. Lorsque l'argument const = TRUE ou est omis, la somme totale des carrés est la somme des différences au carré entre les valeurs y réelles et la moyenne des valeurs y.
Lorsque l'argument 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 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, 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, est grande. La valeur de r2 est égale à ssreg / sstotal.
Dans certains cas, une ou plusieurs des colonnes X (supposons que Y et X sont dans des colonnes) peuvent n'avoir aucune valeur prédictive supplémentaire en présence des autres colonnes X. c'est-à-dire que l'élimination d'une ou plusieurs colonnes X peut conduire à des valeurs Y prévues qui sont tout aussi précises. Dans ce 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.
La fonction LINEST vérifie la colinéarité et supprime toutes les colonnes X redondantes du modèle de régression lorsqu'elle les identifie. Les colonnes X supprimées peuvent être reconnues dans la sortie LINEST comme ayant 0 coefficients en plus de 0 valeurs se. Si une ou plusieurs colonnes sont supprimées comme redondantes, df est affecté car df dépend du nombre de X colonnes réellement utilisées à des fins prédictives.
Si df est modifié parce que les colonnes X redondantes sont supprimées, les valeurs de sey et F sont également affectées. La colinéarité devrait être relativement rare en pratique. Cependant, un cas où cela est plus susceptible de se produire est lorsque certaines colonnes X contiennent uniquement des valeurs 0 et 1 comme indicateurs de savoir si un sujet dans une expérience est ou non membre d'un groupe particulier. Si const = TRUE ou est omis, la fonction LINEST insère effectivement une colonne X supplémentaire de toutes les 1 valeurs pour modéliser l'interception
La valeur de df est calculée comme suit, lorsqu'il y a k colonnes de known_x et qu'aucune colonne X n'est supprimée du modèle en raison de la colinéarité -
- Si const = TRUE ou est omis, df = n - k - 1
- Si const = FALSE, 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.
Lorsque vous entrez une constante de tableau (telle que known_x's) comme argument, utilisez des virgules pour séparer les valeurs contenues dans la même ligne et des points-virgules pour séparer les lignes. Les caractères de séparation peuvent être différents selon vos paramètres régionaux.
Notez que les valeurs y prédites par l'équation de régression peuvent ne pas être valides si elles sont en dehors de la plage des valeurs y que vous avez 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 fonctions SLOPE et INTERCEPT. La différence entre ces algorithmes peut conduire à des résultats différents lorsque les données sont indéterminées et colinéaires.
En plus d'utiliser LOGEST pour calculer des statistiques pour d'autres types de régression, vous pouvez utiliser LINEST pour calculer une plage d'autres types de régression en entrant les fonctions des variables x et y comme séries x et y pour LINEST. Par exemple, la formule suivante -

= LINEST (valeurs y, valeurs x ^ COLONNE ($ A: $ C))

Fonctionne lorsque vous avez une seule colonne de valeurs y et une seule colonne de valeurs x pour calculer l'approximation cubique (polynôme d'ordre 3) de -

y = m1 * x + m2 * x ^ 2 + m3 * x * 3 + b

Vous pouvez ajuster cette formule pour calculer d'autres types de régression, mais dans certains cas, elle nécessite l'ajustement des valeurs de sortie et d'autres statistiques.
La valeur de test F qui est retournée par la fonction LINEST diffère de la valeur de test F qui est renvoyée par la fonction FTEST. LINEST renvoie la statistique F, tandis que FTEST renvoie la probabilité.
Si le tableau de known_x's n'est pas de la même longueur que le tableau de known_y's, LINEST renvoie le #REF! valeur d'erreur.
Si l'une des valeurs des tableaux de known_x ou known_y fournis n'est pas numérique (cela peut inclure des représentations textuelles de nombres, car la fonction LINEST ne les reconnaît pas comme des nombres), LINEST renvoie la #VALUE! valeur d'erreur.
Si l'un des arguments const ou stats ne peut pas être évalué à TRUE ou FALSE, LINEST renvoie #VALUE! valeur d'erreur.

Applicabilité

Excel 2007, Excel 2010, Excel 2013, Excel 2016

Exemple

↰ Previous page