Excel fonctions statistiques : tendance

Cet article décrit la fonction tendance dans Microsoft Office Excel 2003 et versions ultérieures d'Excel, illustre comment la fonction est utilisée et compare les résultats de la fonction dans Excel 2003 et versions ultérieures d'Excel avec les résultats de tendance de plus haut versions d'Excel.

TENDANCE est évaluée en appelant la fonction associée, la fonction DROITEREG. Des modifications importantes à la fonction DROITEREG dans Excel 2003 et versions ultérieures d'Excel sont résumées et leurs implications pour la tendance sont notées.

Microsoft Excel 2004 pour Macintosh d'informations

Les fonctions statistiques dans Microsoft Excel 2004 pour Macintosh ont été mis à jour en utilisant les mêmes algorithmes que Excel 2003 et versions ultérieures d'Excel. Toutes les informations dans cet article qui décrit comment fonctionne une fonction ou comment une fonction a été modifiée pour Excel 2003 et versions ultérieures d'Excel s'applique également à Excel 2004 pour Macintosh.

La fonction tendance (y_connus, x_connus, l'argument x_nouveaux, constante) est utilisé pour effectuer une régression linéaire. Un critère de moindres carrés est utilisé et TENDANCE essaie de trouver le meilleur ajustement sous ce critère. Y_connus représente des données sur la « variable dépendante » et l'argument x_connus représentent des données sur un ou plusieurs « variables indépendantes ». Le fichier d'aide de tendance présente les rares cas où le deuxième ou troisième argument peut être omis.

Si le dernier argument « constante » est défini sur TRUE, vous souhaitez que le modèle de régression à inclure un coefficient de l'intersection dans le modèle de régression. Si le dernier argument a la valeur FALSE, aucun terme intercept n'est inclus ; la régression ajustée est forcée à franchir l'origine. La dernière argument est facultatif ; en cas d'omission elle est interprétée comme TRUE.

Afin de faciliter la des spécifications dans le reste de cet article, supposons que les données sont organisées en colonnes de sorte que l'argument y_connus est une colonne de données y et x_connus est une ou plusieurs colonnes de données x. Bien entendu les dimensions (longueur) de chacun de ces colonnes doit être égale. L'argument x_nouveaux sont supposées également disposés en colonnes et il doit être le même nombre de colonnes pour l'argument x_nouveaux que pour l'argument x_connus. Toutes les les observations dans cet article sont également vraies si les données ne sont pas organisées en colonnes, mais Il est simplement plus facile discuter de cet unique (le plus fréquemment utilisé) cas.

Après le calcul le meilleur modèle de régression fit (par essentiellement l'appel de fonction de la fonction DROITEREG d'Excel), TREND renvoie les valeurs prédites associés avec l'argument x_nouveaux.

Cet article utilise des exemples pour montrer comment la tendance est lié à La fonction DROITEREG et de signaler des problèmes avec la fonction DROITEREG dans Microsoft Excel 2002 et versions antérieures d'Excel. Ces problèmes traduire des problèmes avec les tendances. Alors que le code de tendance n'était pas réécrit pour Excel 2003 et versions ultérieures d'Excel, des modifications importantes (et améliorations) dans le code de la fonction DROITEREG ont été apportées.

TENDANCE efficacement appelle la fonction DROITEREG, exécute la fonction DROITEREG, utilise coefficients de régression dans la sortie de DROITEREG dans son calcul de la valeur y prévue les valeurs associées à chaque ligne de l'argument x_nouveaux et présente cette colonne de valeurs y prévues pour vous. Par conséquent, vous devez connaître à propos des problèmes dans le exécution de la fonction DROITEREG.

En complément de cet article, l'article suivant de la fonction DROITEREG est fortement recommandé. Il contient plusieurs exemples et des documents problèmes avec la fonction DROITEREG dans Excel 2002 et versions antérieures d'Excel.

Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :

Étant ici le focus sur des problèmes numériques dans Excel 2002 et versions antérieures d'Excel, cet article n'a pas de nombreux exemples pratiques pour savoir comment utiliser la fonction tendance. Fichier d'aide de tendance contient des exemples utiles.

Syntaxe

Les arguments y_connus, x_connus et x_nouveaux doivent être des tableaux ou plages de cellules avec des dimensions associées. Si y_connus occupe une seule colonne par m lignes, puis l'argument x_connus sont c colonnes de m lignes où c est supérieur ou égal à un. Notez que c est le nombre de variables explicatives ; m est le nombre de données points. L'argument x_nouveaux doit ensuite être c colonnes de r lignes où r est supérieur ou égal à un. (Des relations similaires dans les dimensions doivent contenir si les données sont stockées dans les lignes au lieu de colonnes.) La constante est un argument logique doit être la valeur TRUE ou FALSE (0 ou 1 qu'Excel interprète comme FALSE ou TRUE, respectivement). Les trois derniers arguments de tendance sont tous facultatifs ; consultez le Fichier d'aide de tendance pour les options d'omettant le deuxième argument, le troisième argument ou les deux. Si vous omettez le quatrième argument est interprété comme TRUE.

L'utilisation courante de tendance inclut deux plages de cellules qui contient les données, telles que les tendances (a1: A100, B1: F100, B101:F108, TRUE). Notez que comme il existe généralement plusieurs variables explicatives, la seconde l'argument dans cet exemple contient plusieurs colonnes. Dans cet exemple, il y a cent sujets, une valeur de variable dépendante (y_connus) pour chaque sujet, et cinq valeurs de variable dépendante (x_connus) pour chaque sujet. Il n'y a huit sujets hypothétiques supplémentaires où vous souhaitez utilisent la fonction tendance pour calculer valeurs y prévues.

Exemple d'utilisation

Un exemple de feuille de calcul Excel est fourni pour illustrer la clé suivante concepts :

• Comment tendance interagit avec la fonction DROITEREG
• Des problèmes qui se produisent en raison de colinéaires x_connus dans tendance (ou la fonction DROITEREG) pour Excel 2002 et pour les versions antérieures d'Excel

Description détaillée de la deuxième puce dans le contexte de la fonction DROITEREG est fournie dans l'article sur la fonction DROITEREG.

Pour illustrer la tendance colinéarité, créer une feuille de calcul Excel vide, copier le tableau suivant, sélectionnez la cellule A1 dans votre espace feuille de calcul Excel, puis collez les entrées de sorte que le tableau suivant remplit les cellules A1:K35 dans votre feuille de calcul. Une fois que vous collez cette table dans votre nouvelle feuille de calcul Excel, cliquez sur Options de collage, puis cliquez sur Correspondance Mise en forme de destination. Avec la plage collée toujours sélectionnée, utilisez une des procédures suivantes, en fonction de la version d'Excel que vous exécutez :

• Dans Microsoft Office Excel 2007 et 2010, cliquez sur leAccueil Cliquez sur Format dans le Cellules de groupe, puis cliquez sur Largeur de colonne d'ajustement automatique.
• Dans Excel 2003, pointez sur Colonne sur la Format menu, puis cliquez sur Ajustement automatique.

Données de tendance sont dans les cellules A1:C8. (Les entrées dans les cellules D2: D6 ne font pas partie des données, mais sont utilisées pour illustration de cet article.) Résultats de tendance pour les deux modèles différents pour les versions antérieures d'Excel et versions ultérieures d'Excel sont présentées dans les cellules E10:E16 et cellules I10:116, respectivement. Résultats dans les cellules A10:A16 correspond à la version d'Excel que vous utilisez. Pour l'instant, cet article se concentre sur les résultats dans Excel 2003 et versions ultérieures d'Excel lors de l'investigation comment tendance appelle DROITEREG et comment tendance utilise la fonction DROITEREG résultats.

TENDANCES et la fonction DROITEREG peuvent être considéré comme interaction comme suit :

1. Vous appelez tendance (y_connus, x_connus et x_nouveaux, constante).
2. TENDANCE appelle DROITEREG (y_connus, x_connus, constante, TRUE).
3. Coefficients de régression à partir de cet appel à la fonction DROITEREG sont obtenu ; Ces coefficients apparaissent dans la première ligne de sortie de DROITEREG table.
4. Pour ligne chaque x_nouveaux, la valeur y prévue est calculée en fonction de ces coefficients DROITEREG et valeurs de l'argument x_nouveaux dans ce ligne.
5. La valeur calculée à l'étape 4 est renvoyée dans le cellule appropriée pour la sortie de tendance correspondant à ligne l'argument x_nouveaux.

Si la tendance est de renvoyer les résultats appropriés, DROITEREG avait mieux générer résultats appropriés dans l'étape 3. Proviennent de problèmes ici colonnes de variables explicatives colinéaires.

Colonnes Predictor (x_connus) sont colinéaires si au moins une colonne c peut être exprimée comme la somme de multiples de d'autres personnes, c1, c2 et autres colonnes. Colonne c est souvent nommé redondants Étant donné que les informations qu'il contient peuvent être construites à partir des colonnes C1, c2 et autres colonnes. Le principe fondamental en présence de colinéarité est que les résultats devraient être affectées en incluant ou en supprimant un colonne redondante à partir des données d'origine. Étant donné que la fonction DROITEREG dans Excel 2002 et du plus haut les versions d'Excel ne recherchent pas la colinéarité, ce principe a été facilement a violé. Colonnes de variables explicatives sont quasiment colinéaires si au moins une colonne c peut exprimée comme étant presque égale à une somme de multiples d'autres personnes, c1, c2 et d'autres colonnes. Dans ce cas « presque égale » signifie une très petite somme des carrés écarts entre les entrées de c à partir des entrées correspondantes dans la somme pondérée des C1, c2 et autres colonnes ; « très petite » peut être signifier pour inférieure à exemple.

Le premier modèle dans les lignes 10 à 12, utilise les colonnes B et C en tant que PRÉDICTEURS et demandes Excel pour modéliser la constante (dernière argument défini sur TRUE). Excel insère alors une colonne de variables explicatives supplémentaire qui ressemble comme les cellules D2: D6. Il est facile à remarquer que les entrées dans la colonne C dans les lignes 2 6 sont précisément égales à la somme des entrées correspondantes dans les colonnes B et D. Par conséquent, il est colinéarité présente parce que la colonne C est une somme de multiples de :

• Colonne B
• Colonne supplémentaire de Microsoft Excel de 1 qui est inséré dans la mesure où le troisième argument de DROITEREG (identique au quatrième argument de tendance) a été omis ou TRUE (le cas « normal »)

Cela provoque des problèmes numériques que Microsoft Excel 2002 et versions antérieur les versions d'Excel ne peut pas calculer les résultats et la table de sortie de tendance est remplie avec #NUM!.

Le deuxième modèle, dans les lignes 14 à 16, est un qui n'importe quelle version de Excel peut gérer avec succès. Il n'y a aucune colinéarité et vous pouvez à nouveau demander à Excel pour modéliser la constante. Ce modèle est pour deux raisons.

Tout d'abord, il est sans doute plus typique de cas pratiques : aucun colinéarité présente. Ces cas sont gérées correctement dans toutes les versions d'Excel. Il devrait être rassurant de savoir que numérique problèmes ne sont pas susceptibles de se produire dans la pratique la plus commun cas si vous avez une version antérieure d'Excel.

Ensuite, cet exemple est utilisé pour comparer le comportement d'Excel 2003 et des versions ultérieures d'Excel dans les deux modèles. La plupart des grands packages statistiques analysent la colinéarité, supprimer une colonne qui est une somme de multiples d'autres à partir du modèle et vous avertissent avec un message tel que « colonne C est donc linéairement dépendante d'autres colonnes de variables explicatives et a été supprimée de l'analyse. »

Dans Excel 2003 et versions ultérieures d'Excel, un tel message est transmis pas dans une alerte ou une chaîne de texte, mais dans la table de sortie de DROITEREG. TENDANCE n'a pas de mécanisme de distribution de ce type de message. Dans la fonction DROITEREG table de sortie, un coefficient de régression qui est zéro et dont l'erreur standard est zéro correspond à un coefficient d'une colonne qui a été supprimé de la modèle. Tables de sortie de DROITEREG sont inclus dans les lignes 23 à 35 correspondant à la TENDANCE de sortie dans les lignes 10 à 16. Les entrées dans les cellules I24:I25 afficher tout colonne predictor redondante. Dans ce cas, DROITEREG a choisi de supprimer la colonne c (coefficients dans les cellules I24, J24, POLYPEPTIDES K24 correspondent aux colonnes C, B et de Microsoft Excel constante colonne, respectivement). Cas de colinéarité présente, l'un des les colonnes impliquées peuvent être supprimées et le choix est arbitraire.

Dans le deuxième modèle dans les lignes 30 à 35, il y a aucune colinéarité et aucune colonne supprimée. Vous pouvez voir que les valeurs y prévues sont le même dans les deux modèles. Ce problème se produit car la suppression d'une colonne redondante C'est une somme de multiples d'autres ne réduit pas le degré d'ajustement de la modèle qui en résulte. Ces colonnes sont précisément supprimées parce qu'elles représentent non valeur ajoutée en cherchant les moindres carrés meilleures.

En outre, si vous examinez Sortie de DROITEREG dans Excel 2003 et versions ultérieures d'Excel dans les cellules I23:K35, vous remarquerez que les trois dernières lignes des tables de sortie sont identiques et que les entrées dans les cellules I31:J32 et J24:K25 de cellules coïncident. Cela démontre que les mêmes résultats sont obtenu lorsque la colonne C est incluse dans le modèle mais trouvé pour être redondants (sortie dans les cellules I24:K28) comme lors de la colonne C a été éliminé avant la fonction DROITEREG a été exécutée (sortie I31:J35 de cellules). Cela satisfait le principe fondamental dans le présence de la colinéarité.

Dans les cellules A18:C21, cet article utilise des données pour Excel 2003 et versions ultérieures d'Excel pour illustrer la tendance prend la sortie de DROITEREG et calcule la pertinentes valeurs y prévues. En examinant les formules dans les cellules A20:A21 et des cellules C20:C21, vous pouvez voir comment les coefficients DROITEREG sont combinées avec des données de l'argument x_nouveaux dans pour chacun des deux modèles (avec les colonnes B, C comme variables explicatives ; en utilisant les cellules B7:C8 uniquement colonne B comme une predictor).

Colinéarité est identifiée dans la fonction DROITEREG dans Excel 2003 et versions ultérieures d'Excel en raison d'une approche complètement différente à résolution des coefficients de régression. Cette approche est appelée la décomposition QR. L'article de la fonction DROITEREG décrit une procédure pas à pas de l'algorithme de décomposition QR pour un petit exemple.

Résumé des résultats dans les versions antérieures d'Excel

Présentation de tendances est altérées par des résultats inexacts dans la fonction DROITEREG dans Excel 2002 et versions antérieures versions d'Excel.

La fonction DROITEREG a été calculée en en utilisant une approche qui l'ont pas fait attention aux problèmes de colinéarité. L'existence de colinéarité a provoqué des erreurs d'arrondi, inappropriés erreurs standard de coefficients de régression et inappropriés degrés de liberté. Problèmes parfois arrondi étaient suffisamment importants que DROITEREG remplissait son tableau de sortie avec #NUM!.

Si, comme dans la grande majorité des cas dans la pratique, vous pouvez être assuré qu'il n'existe pas colonnes de variables explicatives colinéaires (ou presque colinéaires), puis La fonction DROITEREG donne généralement des résultats satisfaisants. Par conséquent, si vous utilisez tendance, vous peut être de même reassured si vous êtes sûr qu'il ne sont pas colinéaires (ou quasiment colinéaires) colonnes de variables explicatives.

Résumé des résultats dans Excel 2003 et versions ultérieures d'Excel

Améliorations apportées à DROITEREG incluent le passage à la décomposition QR méthode de détermination des coefficients de régression. La décomposition QR présente les avantages suivants :

• Meilleure stabilité numérique (généralement plus petit arrondi erreurs)
• Analyse des problèmes de colinéarité

Tous les problèmes avec Excel 2002 et les versions antérieures d'Excel sont illustrées Dans cet article ont été corrigées pour Excel 2003 et versions ultérieures d'Excel.

Conclusions

Performances de tendance ont été améliorées, car la fonction DROITEREG a été grandement améliorées pour Excel 2003 et versions ultérieures d'Excel. Si vous utilisez une version antérieure d'Excel, vérifiez que colonnes de variables explicatives ne sont pas colinéaires avant d'utiliser des tendances.

Proportion de la matériau est présenté dans cet article et dans l'article de la fonction DROITEREG peut au premier abord apparaître alarmant aux utilisateurs d'Excel 2002 et versions antérieures d'Excel. Toutefois, notez que colinéarité est un problème dans un petit pourcentage de cas. Donnent des versions antérieures d'Excel résultats de tendance acceptables lorsqu'il n'y a aucune colinéarité.

Heureusement, améliorations apportées à DROITEREG affectent également positivement régression linéaire d'Analysis ToolPak outil (cela s'appelle la fonction DROITEREG) et deux autres liées fonctions Excel : LOGREG et CROISSANCE.


Mots clés : Formule tendance croissance DROITEREG LOGREG XL2003 XL2007 XL2010