Fonctions statistiques Excel : LOGEST

Résumé

Cet article décrit la fonction LOGEST dans Microsoft Office Excel 2003 et dans les versions ultérieures d’Excel. Il illustre l’utilisation de la fonction et compare les résultats de la fonction dans Excel 2003 et dans les versions ultérieures d’Excel avec les résultats de la fonction dans les versions antérieures d’Excel.

LOGEST est évalué en appelant la fonction associée, LINEST. Les modifications importantes apportées à LINEST pour Excel 2003 et aux versions ultérieures d’Excel sont résumées et leurs implications pour LOGEST sont notées.

Informations microsoft Excel 2004 pour Macintosh

Les fonctions statistiques de Microsoft Excel 2004 pour Macintosh ont été mises à jour à l’aide des mêmes algorithmes qu’Excel 2003 et les versions ultérieures d’Excel. Toutes les informations contenues dans cet article qui décrivent le fonctionnement d’une fonction ou qui décrivent comment une fonction a été modifiée pour Excel 2003 et pour les versions ultérieures d’Excel s’appliquent également à Excel 2004 pour Macintosh.

Informations supplémentaires

La fonction LOGEST(known_y, known_x, constante, statistiques) est utilisée pour effectuer une régression lorsqu’une courbe exponentielle est ajustée. Un critère des moindres carrés est utilisé, et LOGEST tente de trouver la meilleure solution en fonction de ce critère. Known_y représentent des données sur la variable dépendante, et known_x représente des données sur une ou plusieurs variables indépendantes. Le deuxième argument est facultatif. S’il est omis, il est supposé qu’il s’agit d’un tableau de la même taille que known_y contenant les valeurs (1, 2, 3, ...).

En supposant qu’il existe des variables de prédicteur p, LOGEST correspond à une équation de la forme suivante :

y = b * (m1^x1) * (m2^x2) * … * (mp^xp)

Les valeurs des coefficients b, m1, m2, ..., mp qui correspondent le mieux aux données y sont déterminées.

Le dernier argument de LOGEST est défini sur TRUE si vous souhaitez obtenir des statistiques supplémentaires, notamment diverses sommes de carrés, r-squared, f-statistics et des erreurs standard des coefficients de régression. Dans ce cas, LOGEST doit être entré en tant que formule matricielle. Le dernier argument est facultatif. S’il est omis, il est interprété comme FALSE. Les dimensions du tableau sont de cinq lignes par un nombre de colonnes égal au nombre de variables indépendantes plus une si le troisième argument a la valeur TRUE (plus 0 si le troisième argument n’est pas défini sur TRUE).

Si le troisième argument « constante » est défini sur TRUE, vous souhaitez que le modèle de régression inclue la constante b dans l’équation précédente dans son modèle. Si elle est définie sur FALSE, la constante multiplicative b est exclue du modèle de régression en la forçant à être égale à un. Le troisième argument est facultatif. S’il est omis, il est interprété comme VRAI.

Dans cet article, supposons que les données sont organisées en colonnes de sorte que known_y’est une colonne de données y et que known_x est une ou plusieurs colonnes de x données. Les dimensions ou longueurs de chacune de ces colonnes doivent être égales. Toutes les observations suivantes sont également vraies si les données ne sont pas organisées en colonnes, mais il est plus facile de discuter du cas le plus fréquemment utilisé. En outre, supposons que le dernier argument de LOGEST est toujours TRUE et que vous êtes toujours intéressé par la sortie détaillée. Cette supposition permet de révéler des problèmes numériques dans les versions antérieures d’Excel. Certains problèmes numériques sont toujours présents si vous utilisez FALSE pour cet argument.

Cet article utilise les exemples suivants pour montrer comment LOGEST est lié à LINEST et pour signaler les problèmes liés à LINEST dans les versions antérieures d’Excel qui se traduisent par des problèmes avec LOGEST. Bien que le code de LOGEST n’ait pas été réécrit pour Excel 2003 et pour les versions ultérieures d’Excel, des modifications importantes et des améliorations ont été apportées au code pour LINEST. LOGEST appelle efficacement LINEST, exécute LINEST, modifie la sortie LINEST et vous la présente. Par conséquent, vous devez connaître les problèmes liés à l’exécution de LINEST. Pour plus d’informations sur LINEST, cliquez sur le numéro d’article suivant pour afficher l’article dans la Base de connaissances Microsoft :

828533 Description de la fonction LINEST dans Excel 2003 et dans les versions ultérieures d’Excel

Le fichier d’aide LINEST a également été révisé pour Excel 2003 et pour les versions ultérieures d’Excel, et est utile pour plus d’informations. Étant donné que cet article est axé sur les problèmes numériques dans les versions antérieures de Microsoft Excel, cet article n’inclut pas beaucoup d’exemples pratiques de LOGEST. Le fichier d’aide LOGEST contient deux exemples utiles.

Syntaxe

LOGEST(known_y's, known_x's, constant, statistics)

Les arguments, known_y et known_x, doivent être des tableaux ou des plages de cellules qui ont des dimensions associées. Si known_y est une colonne par m lignes, les known_x doivent être des colonnes c par m lignes, où c est supérieur ou égal à un. C est le nombre de variables de prédiction ; m est le nombre de points de données. Des relations similaires doivent être conservées si known_y est disposé dans une seule ligne ; dans ce cas, known_x doit être en r lignes. (R doit être supérieur ou égal à 1.) Constante et statistiques sont des arguments logiques qui doivent être définis sur TRUE ou FALSE. Dans Excel, ces arguments doivent être définis sur 0 ou 1 et sont interprétés par Excel comme FAUX ou VRAI, respectivement. Les trois derniers arguments de LOGEST sont tous facultatifs. Si vous omettez le deuxième argument, LOGEST suppose un prédicteur unique qui contient les entrées {1, 2, 3, ...}. Si vous omettez le troisième argument, il est interprété comme VRAI. Si vous omettez le quatrième argument, il est interprété comme FALSE.

L’utilisation la plus courante de LOGEST inclut deux plages de cellules qui contiennent les données, telles que LOGEST(A1 :A100, B1 :F100, TRUE, TRUE). Étant donné qu’il existe généralement plusieurs variables de prédiction, le deuxième argument de cet exemple contient plusieurs colonnes. Dans cet exemple, il existe 100 sujets, une valeur de variable dépendante, known_y, pour chaque sujet, et cinq valeurs de variables dépendantes, known_x, pour chaque sujet.

Exemple d’utilisation

Deux exemples distincts de feuille de calcul Excel sont fournis pour illustrer les concepts clés suivants :

• Comment LOGEST interagit avec LINEST
• Problèmes dans Microsoft Excel 2002 et dans les versions antérieures d’Excel lorsque le troisième argument LOGEST ou LINEST est défini sur FALSE ou est omis.
• Problèmes dus à la known_x colline dans LOGEST ou LINEST dans Excel 2002 et versions antérieures.

Pour plus d’informations sur les deuxième et troisième concepts de cette liste, cliquez sur le numéro d’article suivant pour afficher l’article dans la Base de connaissances Microsoft :

828533 Description de la fonction LINEST dans Excel 2003 et dans les versions ultérieures d’Excel

Pour illustrer LOGEST avec le troisième argument défini sur FALSE, procédez comme suit :

1. Créez une feuille de calcul Excel vide, puis copiez le tableau plus loin dans cette section.

2. Cliquez sur la cellule A1 dans votre feuille de calcul Excel vide. Ensuite, collez les entrées afin que le tableau remplit les cellules A1 :H17 dans votre feuille de calcul.

3. Après avoir collé le tableau dans votre nouvelle feuille de calcul Excel, cliquez sur Options de collage, puis sur Mettre en forme la destination.

4. Bien que la plage collée soit toujours sélectionnée, utilisez l’une des procédures suivantes, en fonction de la version d’Excel que vous exécutez :

   • Dans Microsoft Office Excel 2007, cliquez sur l’onglet Accueil , sur Format dans le groupe Cellules , puis sur Ajuster automatiquement la largeur des colonnes.
   • Dans Excel 2003 et dans les versions antérieures d’Excel, pointez sur Colonne dans le menu Format , puis cliquez sur Ajuster automatiquement la sélection.

Les données de LOGEST se trouvent dans les cellules A4 :B6. Les résultats détaillés pour Excel 2002 et pour les versions antérieures d’Excel et pour Excel 2003 et pour les versions ultérieures d’Excel sont présentés dans les cellules D4 :E8 et G4 :H8, respectivement. Les cellules A11 :B13 affichent les mêmes known_x, mais les known_y dans les cellules A4 :A6 ont été transformées en prenant leur logarithme naturel à l’aide de la fonction Excel LN. LINEST est ensuite appelé sur ces données transformées, et les résultats sont affichés dans les cellules G11 :H15. Notez que les résultats dans les cellules G12 :H15 sont les mêmes que les résultats LOGEST dans les cellules G5 :H8. Les coefficients LINEST dans les cellules G11 :H11 sont transformés par l’exponentiation. Autrement dit, les coefficients LINEST dans les cellules sont transformés en utilisant essentiellement la fonction EXP Excel pour calculer les coefficients LOGEST dans les cellules G4 :H4. Vous pouvez vérifier cette relation à l’aide de la fonction EXP dans les cellules G17 :H17. Pour résumer l’interaction entre LOGEST et LINEST, observez la séquence d’étapes suivante :

1. Vous appelez LOGEST(known_y, known_x, constante, TRUE).
2. LOGEST appelle LINEST(LN of known_y’s, known_x’s, constant, TRUE).
3. LOGEST reçoit la table de résultats de cet appel à LINEST.
4. LOGEST modifie les coefficients LINEST dans la première ligne de la table de résultats LINEST par exposant. Par exemple, remplacez chaque coefficient LINEST, m, par EXP(m).
5. LOGEST renvoie cette table de résultats LINEST modifiée en tant que table de résultats LOGEST.

Si LOGEST doit retourner les résultats appropriés, LINEST doit générer les résultats appropriés à l’étape 3. Examinez les cellules D13 :D15. La cellule D13 contient une valeur r-carrée, la cellule D14 une statistique f et la cellule D15 la somme de carrés de régression LINEST.

Dans Excel 2002 et dans les versions antérieures d’Excel, lorsque LINEST est appelé avec son troisième argument défini sur FALSE, il calcule toujours une somme de régression de carrés qui n’est pas correcte, car elle utilise une formule qui n’est pas correcte. Ce problème a été corrigé dans Excel 2003 et dans les versions ultérieures d’Excel. Notez que les valeurs des deux premières lignes de la table de sortie ne sont pas affectées par ce problème. Dans Excel 2002 et dans les versions antérieures d’Excel, l’article LINEST décrit une solution de contournement pour générer les valeurs appropriées dans les trois dernières lignes du tableau de sortie LINEST. Si vous utilisez une version antérieure d’Excel et que vous souhaitez définir le troisième argument sur LOGEST sur FALSE, nous vous recommandons d’exécuter explicitement les étapes 2 et 3 de la procédure précédente, puis d’utiliser la solution de contournement de l’article LINEST pour modifier les trois dernières lignes de la table de sortie LINEST.

Les problèmes se produisent en raison de la known_x colline dans LOGEST ou LINEST dans Excel 2002 et dans les versions antérieures d’Excel. Les colonnes prédictrices known_x sont collineaires si au moins une colonne, c, peut être exprimée sous la forme d’une somme de multiples d’autres (c1, c2 et d’autres valeurs). La colonne c est appelée redondante, car les informations qu’elle contient peuvent être construites à partir des colonnes (c1, c2 et d’autres valeurs). Le principe fondamental en présence de collinearité est que les résultats ne doivent pas être affectés par le fait qu’une colonne redondante soit incluse dans les données d’origine ou supprimée des données d’origine. Étant donné que la version de LINEST dans Excel 2002 et dans les versions antérieures d’Excel ne recherchait pas la collinearité, ce principe a été facilement violé. Les colonnes de prédiction sont presque collineaires si au moins une colonne, c, peut être exprimée comme presque égale à une somme de multiples d’autres (c1, c2 et d’autres valeurs). Dans ce cas, « presque égal » signifie une très petite somme d’écarts carrés des entrées en c par rapport aux entrées correspondantes dans la somme pondérée de c1, c2 et d’autres valeurs. Par exemple, « très petit » peut être inférieur à 10^(-12).

Pour illustrer la colline de LOGEST, procédez comme suit :

1. Créez une feuille de calcul Excel vide, puis copiez le tableau suivant.

2. Cliquez sur la cellule A1 dans votre feuille de calcul Excel vide. Ensuite, collez les entrées afin que le tableau remplisse les cellules A1 :N27 de votre feuille de calcul.

3. Après avoir collé le tableau dans votre nouvelle feuille de calcul Excel, cliquez sur Options de collage, puis sur Mettre en forme la destination.

4. Bien que la plage collée soit toujours sélectionnée, utilisez l’une des procédures suivantes, en fonction de la version d’Excel que vous exécutez :

   • Dans Excel 2007, cliquez sur l’onglet Accueil , sur Format dans le groupe Cellules , puis sur Ajuster automatiquement la largeur des colonnes.
   • Dans Excel 2003 et dans les versions antérieures d’Excel, pointez sur Colonne dans le menu Format , puis cliquez sur Ajuster automatiquement la sélection.

Les données sont incluses dans les cellules A1 :C6. Les entrées dans les cellules D2 :D6 ne font pas partie des données. Ces entrées sont utilisées pour l’illustration dans le paragraphe suivant. Les résultats de deux appels différents à LOGEST sont affichés pour Excel 2002 et pour les versions antérieures d’Excel dans les cellules E8 :G20, et pour Excel 2003 et pour les versions ultérieures d’Excel dans les cellules I8 :K20.

Le premier modèle, dans les lignes 8 à 13, utilise les colonnes B et C comme prédicteurs et demande à Excel de modéliser la constante où le troisième argument a la valeur TRUE. Excel insère ensuite efficacement une colonne de prédicteur supplémentaire qui ressemble aux cellules D2 :D6. Notez que les entrées de la colonne C dans les lignes 2 à 6 sont exactement égales à la somme des entrées correspondantes dans les colonnes B et D. Par conséquent, la collinearité se produit parce que la colonne C est une somme de multiples de la colonne B, et la colonne supplémentaire Excel de 1 est insérée parce que le troisième argument de LOGEST a été omis ou TRUE qui est le cas « normal ». Cette collinearité entraîne des problèmes numériques, et Excel 2002 et les versions antérieures d’Excel ne peuvent pas calculer les résultats et le tableau de sortie LOGEST est rempli avec #NUM !.

N’importe quelle version d’Excel peut gérer le deuxième modèle dans les lignes 15 à 20. La collinearité ne se produit pas avec ce modèle, et l’utilisateur demande à Excel de modéliser la constante. Cet exemple est inclus ici pour les deux raisons suivantes :

• Cet exemple est le plus typique des cas pratiques : aucune collinearité n’est présente et le troisième argument de LOGEST est TRUE ou omis. Si vous avez une version antérieure d’Excel, les problèmes numériques ne sont pas susceptibles de se produire dans le cas pratique le plus courant.
• Deuxièmement, 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 packages statistiques principaux analysent la collinearité, suppriment une colonne qui est une somme de multiples d’autres du modèle et vous avertit avec un message comme « La colonne C dépend linéairement d’autres colonnes de prédiction et a été supprimée de l’analyse ».

Dans Excel 2003 et dans les versions ultérieures d’Excel, vous recevez les informations dans le tableau de sortie LOGEST, et non dans un message ou dans une chaîne de texte. Un coefficient de régression qui est un et dont l’erreur type est « zéro » correspond à un coefficient pour une colonne qui a été supprimée du modèle. (Consultez les entrées des cellules I9 :I10 pour obtenir un exemple.) Dans ce cas, LOGEST supprime la colonne C (les coefficients dans les cellules I9, J9, K9 correspondent aux colonnes C, B et dans la colonne constante Excel, respectivement). En cas de collinearité, l’une des colonnes concernées peut être supprimée.

Dans le deuxième modèle des lignes 16 à 20, la colline ne se produit pas et aucune des colonnes n’est supprimée. Les valeurs y prédites sont les mêmes dans les deux modèles, car la suppression d’une colonne redondante qui est une somme de multiples d’autres (le premier et le deuxième modèle) ne réduit pas l’adéquation du modèle résultant. Ces colonnes sont supprimées précisément parce qu’elles ne représentent aucune valeur ajoutée en essayant de trouver les meilleurs moindres carrés adaptés. En outre, dans la sortie d’Excel 2003 et des versions ultérieures d’Excel dans les cellules I8 :K20, les trois dernières lignes des tableaux de sortie sont les mêmes, et les entrées dans les cellules I16 :J17 et les cellules J9 :K10 coïncident. Cela montre que les mêmes résultats sont obtenus lorsque la colonne C est incluse dans le modèle, mais qu’elle est redondante (sortie dans I9 :K13) que lorsque la colonne C a été supprimée avant l’exécution de LOGEST (sortie dans I16 :J20). Cette sortie répond au principe fondamental en présence d’une collinearité.

La collinearité est identifiée dans LINEST dans Excel 2003 et dans les versions ultérieures d’Excel à l’aide d’une approche complètement différente, la décomposition QR, pour résoudre les coefficients de régression. L’article LINEST 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

Les résultats LOGEST sont affectés négativement dans Excel 2002 et dans les versions antérieures d’Excel par des résultats dans LINEST qui ne sont pas exacts.

LINEST a utilisé une formule qui n’est pas correcte pour la somme totale des carrés lorsque le troisième argument dans LINEST est défini sur FALSE. Cette formule a abouti à des valeurs de la somme de régression de carrés qui ne sont pas correctes. En outre, les valeurs qui dépendent de la somme de régression des carrés, r au carré et la statistique f, ne sont pas correctes. (Voir la solution de contournement dans l’article LINEST si vous utilisez une version antérieure d’Excel.) Par conséquent, les utilisateurs de LOGEST doivent utiliser cette solution de contournement lorsqu’ils appellent LOGEST avec le troisième argument défini sur FALSE.

Quelle que soit la valeur du troisième argument, LINEST a été calculé à l’aide d’une approche qui ne répondait pas aux problèmes de collinearité. Le collinearité provoquait des erreurs d’arrondi, des erreurs types de coefficients de régression qui n’étaient pas appropriés et des degrés de liberté qui n’étaient pas appropriés. Dans certains cas, les erreurs d’arrondi étaient suffisamment graves pour que la table de sortie LINEST soit remplie de #NUM !. LINEST fournit généralement des résultats acceptables si les conditions suivantes sont remplies :

• Les utilisateurs sont convaincus que les colonnes de prédicteur ne sont pas collineaires (ou presque collineaires).
• Le troisième argument de LINEST est TRUE ou est omis.

Par conséquent, LOGEST fournit généralement des résultats acceptables si les colonnes de prédicteur ne sont pas collineaires (ou presque collineaires) et si le troisième argument de LOGEST est TRUE ou est omis.

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

Les améliorations suivantes dans LINEST ont été apportées :

• La formule de la somme totale des carrés où le troisième argument de LINEST est défini sur FALSE a été corrigée.
• La méthode de décomposition QR est utilisée pour déterminer les coefficients de régression.

La décomposition QR présente deux avantages :

• Meilleure stabilité numérique (ou généralement des erreurs d’arrondi plus petites).
• Analyse des problèmes de collinearité.

Tous les problèmes liés à Excel 2002 et aux versions antérieures d’Excel abordés dans cet article ont été corrigés dans Excel 2003 et dans les versions ultérieures d’Excel.

Conclusions

Les performances de LOGEST ont été améliorées, car LINEST a été considérablement amélioré dans Excel 2003 et dans les versions ultérieures d’Excel. Si vous utilisez une version antérieure d’Excel, vérifiez que les colonnes de prédicteur ne sont pas collineaires avant d’utiliser LOGEST. Veillez également à utiliser la solution de contournement présentée dans l’article LINEST lorsque le troisième argument logEST est défini sur FALSE. Bien que ces informations contenues dans cet article et dans l’article LINEST puissent sembler alarmantes pour les utilisateurs d’Excel 2002 et des versions antérieures d’Excel, la collinearité est un problème dans un petit pourcentage de cas. Les appels à LOGEST avec le troisième argument défini sur FALSE sont probablement également relativement rares dans la pratique. Les versions antérieures d’Excel donnent des résultats LOGEST acceptables en l’absence de collinearité et lorsque le troisième argument de LOGEST est TRUE ou omis.