Fonctions statistiques Excel : INTERCEPT

Résumé

Cet article décrit la fonction INTERCEPT dans Microsoft Excel, montre comment utiliser la fonction et compare ses résultats pour Excel 2003 et pour les versions ultérieures d’Excel avec ses résultats dans les versions antérieures d’Excel.

Informations supplémentaires

La fonction INTERCEPT(known_y,known_x) retourne l’INTERCEPT de la ligne de régression linéaire utilisée pour prédire les valeurs y à partir de valeurs x .

Syntaxe

INTERCEPT(known_y's,known_x's)

Les arguments, known_y et known_x, doivent être des tableaux ou des plages de cellules qui contiennent un nombre égal de valeurs de données numériques. Intercept inclut 2 plages de cellules contenant les données, telles que INTERCEPT(A1 :A100, B1 :B100).

Exemple d’utilisation

Pour illustrer la fonction INTERCEPT, créez une feuille de calcul Excel vide, copiez le tableau suivant, sélectionnez la cellule A1 dans votre feuille de calcul Excel vide, puis collez les entrées afin que le tableau suivant remplit les cellules A1 :D13 dans votre feuille de calcul.

• 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, pointez sur Colonne dans le menu Format , puis cliquez sur Ajuster automatiquement la sélection.

Vous pouvez mettre en forme les cellules B2 :B7 comme Nombre avec 0 décimales et les cellules A9 :D13 comme Nombre avec 6 décimales.

Les cellules A2 :A7 et B2 :B7 contiennent les valeurs y et x qui appellent INTERCEPT dans la cellule A10.

Dans les versions d’Excel antérieures à Excel 2003, INTERCEPT peut présenter des erreurs de arrondi. Excel 2003 et les versions ultérieures d’Excel améliorent le comportement d’INTERCEPT. INTERCEPT(known_y, known_x) est le résultat de l’évaluation moyenne (known_y) – PENTE(known_y, known_x) * MOYENNE(known_x). Bien que le code d’INTERCEPT n’ait pas été directement modifié pour Excel 2003 et pour les versions ultérieures d’Excel, le comportement d’INTERCEPT est amélioré en raison de l’amélioration du code pour SLOPE.

Si vous disposez d’une version antérieure d’Excel, vous pouvez utiliser la feuille de calcul que vous avez créée précédemment pour exécuter une expérience afin de découvrir quand des erreurs d’arrondi se produisent. L’ajout d’une constante positive à chacune des observations dans B2 :B7 ne doit pas affecter la valeur de SLOPE. Si vous tracez des paires x,y avec x sur l’axe horizontal et y sur l’axe vertical, puis que vous ajoutez une constante positive à chaque valeur x, les données se déplacent simplement vers la droite. La droite de régression la mieux adaptée a toujours la même pente. Toutefois, les données décalées ont une interception différente.

Avec la valeur par défaut 0 dans D3, SLOPE dans A9 est 0,775280899. La cellule A10 affiche la valeur d’INTERCEPT et la cellule A11 la valeur de l’expression qui est évaluée lors du calcul d’INTERCEPT :

MOYENNE(known_y) : PENTE(known_y, known_x) * MOYENNE(known_x)

Les valeurs des cellules A9 et A10 sont toujours d’accord, car la valeur dans A10 est exactement celle renvoyée par INTERCEPT. La pente ne doit pas varier lorsque vous ajoutez différentes constantes positives au known_x. La cellule A11 indique MOYENNE(known_y) – 0,775280899 * MOYENNE(known_x). Étant donné que SLOPE ne doit pas changer et que 0,775280899 est la valeur de SLOPE lorsque D3 = 0, les valeurs de cette expression dans A11 doivent également être d’accord avec les valeurs des cellules A9 et A10.

Si vous augmentez la valeur en D3, vous ajoutez une constante plus grande à B2 :B7. Si D3 <= 7, aucune erreur d’arrondi n’apparaît dans les 6 premières décimales de SLOPE. Mais si vous essayez 7.25, 7.5, 7.75 et 8, la pente dans A9 change. Par conséquent, les valeurs des cellules A11 (qui sont d’accord avec A10) et A12 diffèrent. Toutefois, les valeurs dans A11 (ou A10) et A12 doivent être les mêmes, car l’ajout d’une constante à la known_x ne doit pas affecter LA PENTE.

D7 :D13 affiche les valeurs retournées par INTERCEPT et les valeurs qu’INTERCEPT aurait dû retourner si PENTE n’avait pas changé. Ces paires de valeurs apparaissent dans les cas où D3 = 7,5 et 8 respectivement. Les erreurs d’arrondi sont devenues si graves que la division par 0 se produit lorsque D3 = 8.

Les versions antérieures d’Excel donnent des réponses incorrectes dans ces cas, car les effets des erreurs d’arrondi sont plus importants avec la formule de calcul utilisée par ces versions. Néanmoins, cette expérience montre que les cas où les erreurs se produisent sont extrêmes.

Si vous avez Excel 2003 ou une version ultérieure d’Excel, il y a peu ou pas de différence entre les valeurs courantes dans A10 et A11 et la valeur dans A12 si vous essayez l’expérience. Toutefois, les cellules D7 :D13 affichent les erreurs d’arrondi que vous obtenez avec les versions antérieures d’Excel.

Résultats dans les versions antérieures d’Excel

L’article sur SLOPE décrit la formule moins robuste numériquement utilisée par les versions antérieures. La formule ne nécessite qu’une seule transmission des données. Seuls les défauts de SLOPE dans ces versions font qu’INTERCEPT génère des erreurs d’arrondi dans les cas extrêmes.

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

Excel 2003 et les versions ultérieures d’Excel utilisent une procédure améliorée pour calculer PENTE. Par conséquent, les performances d’INTERCEPT s’améliorent. La procédure améliorée nécessite deux passages dans les données. Là encore, l’article suivant sur SLOPE décrit l’amélioration.

Pour plus d’informations sur les améliorations apportées à SLOPE pour Excel 2003 et aux versions ultérieures d’Excel, cliquez sur le numéro d’article suivant pour afficher l’article dans la Base de connaissances Microsoft :

828142 fonctions statistiques Excel : SLOPE

Conclusions

Étant donné qu’Excel 2003 et les versions ultérieures d’Excel remplacent une approche en une seule passe par une approche à deux étapes, les performances numériques de SLOPE dans Excel 2003 et dans les versions ultérieures d’Excel sont meilleures que dans les versions antérieures d’Excel. Par conséquent, les performances numériques d’INTERCEPT sont meilleures. Les résultats dans Excel 2003 et dans les versions ultérieures d’Excel ne seront jamais moins précis que les résultats des versions antérieures d’Excel.

En règle générale, il n’y a pas de différence entre les résultats dans Excel 2003 et dans les versions ultérieures d’Excel et les résultats dans les versions antérieures d’Excel, car les données ne se comportent pas fréquemment de la manière inhabituelle illustrée par cette expérience. L’instabilité numérique est plus susceptible d’apparaître dans les versions antérieures d’Excel lorsque les données contiennent de nombreux chiffres significatifs et peu de variation entre les valeurs de données.

La procédure suivante recherche la somme des écarts carrés par rapport à une moyenne d’échantillon :

1. Recherchez l’exemple de moyenne.
2. Calculez chaque écart au carré.
3. Additionnez les écarts carrés.

Cette procédure est plus précise que la procédure alternative suivante (également appelée « formule de calculatrice », car elle convenait à une utilisation sur une calculatrice pour un petit nombre de points de données) :

1. Recherchez la somme des carrés de toutes les observations, la taille de l’échantillon et la somme de toutes les observations.
2. Calculez la somme des carrés de toutes les observations moins ((somme de toutes les observations)^2)/taille de l’échantillon).

En remplaçant cette dernière procédure en une seule passe par la procédure à deux passes qui trouve la moyenne de l’échantillon sur la première passe et calcule la somme des écarts carrés à son sujet sur la deuxième passe, Excel 2003 et les versions ultérieures d’Excel améliorent de nombreuses autres fonctions. Une courte liste de ces fonctions inclut VAR, VARP, STDEV, STDEVP, DVAR, DVARP, DSTDEV, DSTDEVP, FORECAST, SLOPE, INTERCEPT, PEARSON, RSQ et STEYX. Microsoft a apporté des améliorations similaires à chacun des trois outils d’analyse de variance dans l’utilitaire d’analyse.