Arithmétique à virgule flottante peut-être donner des résultats incorrects dans Excel

Résumé

Cet article explique comment Microsoft Excel stocke et calcule les nombres à virgule flottante. Cela peut affecter les résultats de certains nombres ou de formules en raison de la troncature d’arrondi ou de données.

Vue d’ensemble

Microsoft Excel a été conçu autour de la spécification IEEE 754 pour déterminer la façon dont il stocke et calcule les nombres à virgule flottante. IEEE est l’Institute of Electrical and Electronics Engineers, un organisme international qui, entre autres, détermine les normes pour les logiciels et le matériel. La spécification 754 est une spécification très largement adoptée décrivant des nombres à virgule flottante de comment doit être stockée dans un ordinateur binaire. Il est populaire car elle permet de nombres à virgule flottante sont stockées dans un montant raisonnable de l’espace et des calculs assez rapidement. La norme 754 est utilisée dans les unités de virgule flottante et les processeurs de données numériques de pratiquement tous les microprocesseurs basés sur PC d’aujourd'hui qui implémentent l’arithmétique à virgule flottante, y compris les processeurs Intel, Motorola, Sun et MIPS.

Lorsque les nombres sont stockés, un nombre binaire correspondant peut représenter chaque nombre ou un nombre décimal. Par exemple, la fraction 1/10 peut être représentée dans un système de numération décimal comme 0,1. Toutefois, le même nombre en format binaire devient le décimal binaire répétitif suivant :
0001100110011100110011 (et ainsi de suite)
Cela peut être répété à l’infini. Ce nombre ne peut pas être représenté dans un espace fini (limité). Par conséquent, cette est arrondi d’environ - 2.8E-17 lorsqu’il est stocké.

Toutefois, il existe certaines limitations de la spécification IEEE 754 qui se répartissent en trois catégories générales :
  • Limites maximum/minimum
  • Précision
  • Nombres binaires répétitifs

Plus d'informations

Limites maximum/Minimum

Tous les ordinateurs possèdent un maximum et un nombre minimal qui peut être géré. Étant donné que le nombre de bits de mémoire dans laquelle le nombre est stocké est fini, il s’ensuit que le nombre maximum ou minimum pouvant être stocké est également fini. Pour Excel, le nombre maximal qui peut être stocké est 1, 79769313486232E + 308 et le nombre positif minimum pouvant être stocké est 2.2250738585072E-308.

Cas dans lesquels nous adhérons à la norme IEEE 754

  • Dépassement de capacité négatif : Dépassement de capacité négatif se produit lorsqu’un nombre généré est trop petit pour être représenté. Dans la norme IEEE et Excel, le résultat est 0 (à l’exception que IEEE a un concept de - 0, contrairement à Excel).
  • Dépassement de capacité : Dépassement de capacité se produit lorsqu’un nombre est trop grand pour être représenté. Excel utilise sa propre représentation dans ce cas (#NUM !).

Cas dans lesquels nous n’adhérons pas à la norme IEEE 754

  • Dénormalisées numéros : un nombre dénormalisé est indiqué par un exposant de 0. Dans ce cas, le nombre entier est stocké dans la mantisse et celle-ci ne commence pas 1 implicite. Par conséquent, vous perdez la précision et plus le nombre est petit, le plus de précision est perdu. Les nombres dans le bas de cette plage ont un seul chiffre de précision.
    Exemple : Un nombre normalisé a un 1 implicite. Par exemple, si la mantisse représente 0011001, le nombre normalisé devient 10011001 en raison de la 1 implicite au début. Un nombre dénormalisé n’a pas un leader implicite, donc dans notre exemple 0011001, le nombre dénormalisé reste le même. Dans ce cas, le nombre normalisé a huit chiffres significatifs (10011001) tandis que le nombre a cinq (11001) avec des zéros non significatifs n’étant pas significatifs.

    Les nombres dénormalisés sont en fait une solution de contournement pour permettre les numéros plus petite que la limite minimale normale doivent être stockées. Microsoft n’implémente pas cette partie facultative de la spécification car les nombres dénormalisés par leur nature ont un nombre variable de chiffres significatifs. Cela peut permettre des erreurs importantes lors des calculs.
  • Les infinis positif/négatif : Les infinis division par 0. Excel ne prend pas en charge les infinis, il donne une valeur d’erreur #DIV/0 ! erreur dans ces cas.
  • N’est pas un nombre (NaN) : NaN est utilisé pour représenter les opérations non valides (par exemple infini/infini, infini-infini ou la racine carrée de -1). Les NaN permettent à un programme de poursuivre après une opération non valide. Au lieu de cela, Excel génère immédiatement une erreur telle que #NUM ! ou #DIV/0 !.

Précision

Un nombre à virgule flottante est stocké au format binaire en trois parties dans une plage de 65 bits : le signe, l’exposant et la mantisse.
1 Bit de signe11 bits d’exposant1 Bit impliquéMantisse 52 bits
Le signe stocke le signe du nombre (positif ou négatif), l’exposant stocke la puissance de 2 à laquelle le nombre est élevé (la puissance maximale/minimale de 2 est +1,023 et-1,022), et la mantisse stocke le nombre lui-même. La zone de stockage finie des mantisse limite comment fermer deux adjacentes nombres à virgule flottante (c'est-à-dire la précision).

La mantisse et l’exposant sont stockés en tant que composants distincts. Par conséquent, le degré de précision possible peut varier en fonction de la taille du nombre (de la mantisse) manipulée. Dans le cas d’Excel, bien qu’Excel puisse stocker des nombres de 1, 79769313486232E308 à 2.2250738585072E-308, il ne peut le faire qu’au sein de 15 chiffres de précision. Cette limitation est le résultat direct d’un strict respect de la spécification IEEE 754 et n’est pas une limitation d’Excel. Ce niveau de précision est trouvé dans d’autres tableurs.

Les nombres à virgule flottante sont représentés sous la forme suivante, où exposant est l’exposant binaire :
X = Fraction * 2 ^ (exposant - biais)
Fraction est la partie fractionnelle normalisée du nombre, normalisée car l’exposant est ajusté afin que le premier bit est toujours 1. De cette façon, il n’a pas à être stockés, et vous obtenez un bit de plus de précision. C’est pourquoi il existe un bit implicite. Ceci est similaire à la notation scientifique, dans laquelle vous manipulez l’exposant d’avoir un chiffre à gauche de la virgule décimale ; à l’exception de binaire, vous pouvez toujours manipuler l’exposant afin que le premier bit soit un 1, car il n’y a seulement 1 et0.

Décalage est la valeur de compensation utilisée pour éviter d’avoir à stocker les exposants négatifs. L’écart pour les nombres en simple précision est 127 et 1 023 (décimal) pour les nombres à double précision. Excel stocke les nombres en utilisant la double précision.

Exemple d’utilisation de très grands nombres

Dans un nouveau classeur, entrez ce qui suit :
   A1: 1.2E+200
B1: 1E+100
C1: =A1+B1
La valeur résultante dans la cellule C1 serait 1, 2E + 200, la même valeur que la cellule A1. En fait, si vous comparez les cellules A1 et C1 à l’aide de la fonction si, par exemple IF(A1=C1), le résultat sera TRUE. Cela est dû à la spécification IEEE de stockage à 15 chiffres significatifs de précision. Pour être en mesure de stocker le calcul ci-dessus, Excel nécessiterait au moins 100 chiffres de précision.

Exemple utilisant des nombres très petits

Dans un nouveau classeur, entrez ce qui suit :
   A1: 0.000123456789012345
B1: 1
C1: =A1+B1
La valeur résultante dans la cellule C1 serait 1,00012345678901 au lieu de 1,000123456789012345. Cela est dû à la spécification IEEE de stockage à 15 chiffres significatifs de précision. Pour être en mesure de stocker le calcul ci-dessus, Excel nécessiterait au moins 19 chiffres de précision.

Correction des erreurs de précision

Excel offre deux méthodes de base pour compenser les erreurs d’arrondi : la fonction arrondi et précision au format affiché ou
Option de classeur ensemble la précision au format affiché .

Méthode 1 : La fonction arrondi

Utilisez les données précédentes, l’exemple suivant utilise la fonction arrondi pour forcer un nombre à cinq chiffres. Cela vous permet de comparer correctement le résultat à une autre valeur.
   A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5)
Le résultat 1, 2E + 200.
   D1: =IF(C1=1.2E+200, TRUE, FALSE) 
Ainsi, la valeur TRUE.

Méthode 2 : Précision comme affiché

Dans certains cas, vous ne pourrez pas empêcher les erreurs d’arrondi d’affecter votre travail à l’aide de l’option précision au format affiché . Cette option force chaque nombre dans la feuille de calcul à la valeur affichée. Pour activer cette option, procédez comme suit.



 
  1. Dans le menu fichier , cliquez sur Options, puis cliquez sur la catégorie Avancé .
  2. Dans la section lors du calcul de ce classeur , sélectionnez le classeur que vous souhaitez, puis activez la case à cocher ensemble la précision au format affiché .

Par exemple, si vous choisissez un format numérique affichant deux décimales, et puis vous activez l’option précision au format affiché , toute précision au-delà des deux décimales est perdue lorsque vous enregistrez votre classeur. Cette option affecte le classeur actif, y compris toutes les feuilles de calcul. Vous ne pouvez pas annuler cette option et récupérer les données perdues. Nous vous recommandons d’enregistrer votre classeur avant d’activer cette option.

Nombres binaires répétitifs et calculs dont les résultats proches de zéro

Un autre problème lié qui affecte le stockage des nombres à virgule flottante au format binaire est que certains nombres sont des nombres finis et non répétitifs en base décimale 10, infinie, répétition des nombres de binaire. L’exemple le plus courant de cette est la valeur 0,1 et ses variantes. Bien que ces nombres puissent être représentés parfaitement en base 10, le même nombre en format binaire devient le nombre binaire répétitif suivant lorsqu’il est stocké dans la mantisse :
000110011001100110011 (et ainsi de suite)
La spécification IEEE 754 ne fait aucune provision spéciale pour un nombre quelconque. Elle stocke ce qu’elle peut dans la mantisse et tronque le reste. Il en résulte en une erreur d’environ - 2.8E-17, ou que les 0,000000000000000028, lorsqu’il est stocké.

Les fractions décimales même courantes, comme le nombre décimal 0,0001, ne peut pas être représentées exactement en format binaire. (0,0001 est une fraction binaire se répétant qui dispose d’une période de 104 bits). Ceci est similaire à la raison pour laquelle la fraction 1/3 ne peut pas être représentée exactement en décimal (un nombre décimal répétitif 0,33333333333333333333).

Par exemple, considérez l’exemple simple suivant dans Microsoft Visual Basic pour Applications :
   Sub Main()      MySum = 0
For I% = 1 To 10000
MySum = MySum + 0.0001
Next I%
Debug.Print MySum
End Sub
Cela IMPRIMERA 0,999999999999996 comme sortie. La petite erreur de représentation de 0,0001 en base binaire est propagée dans la somme.

Exemple : Ajout d’un nombre négatif

  1. Dans un nouveau classeur, entrez ce qui suit :
       A1: =(43.1-43.2)+1 
  2. Avec le bouton droit de la cellule A1, puis cliquez sur Format de cellule. Sous l’onglet nombre , cliquez sur scientifique sous catégorie. Définir nombre de décimales à 15.
Au lieu d’afficher 0,9, Excel affiche 0.899999999999999. Étant donné que (43.1-43,2) est calculé en premier, -0,1 est stocké temporairement et l’erreur de stockage de -0,1 est introduite dans le calcul.

Exemple lorsqu’une valeur atteint zéro

  1. Dans Excel 95 ou version antérieure, entrez ce qui suit dans un nouveau classeur :
       A1: =1.333+1.225-1.333-1.225 
  2. Droite-clickcell A1, puis cliquez sur Format de cellule. Sous l’onglet nombre , cliquez sur scientifique sous catégorie. Définir nombre de décimales à 15.
Au lieu d’afficher 0, Excel 95 affiche - 2.22044604925031E-16.

Toutefois, Excel 97, introduit une optimisation pour résoudre ce problème. Une opération d’addition ou de soustraction en une valeur à ou très proche de zéro, Excel 97 et version ultérieure compense toute erreur due à la conversion d’un opérande vers et à partir du fichier binaire. L’exemple ci-dessus dans Excel 97 et version ultérieure correctement affiche 0 ou 0, 000000000000000E + 00 en notation scientifique. Pour plus d’informations, cliquez sur les numéros ci-dessous pour afficher les articles correspondants dans la Base de connaissances Microsoft :
172911 un résultat incorrect atteignant 10 à une puissance très élevée/petite
214373 un résultat incorrect atteignant 10 à une puissance très élevée/petite
Pour plus d’informations sur les nombres à virgule flottante et la spécification IEEE 754, veuillez consulter les sites Web suivants :

Références

Pour plus d’informations sur la façon de contourner ces erreurs, cliquez sur le numéro ci-dessous pour afficher l’article correspondant dans la Base de connaissances Microsoft :
214118 comment corriger les erreurs d’arrondi dans l’arithmétique en virgule flottante
Remarque Cet article s’applique également à Microsoft Excel pour Mac pour Office 365.
Propriétés

ID d'article : 78113 - Dernière mise à jour : 27 janv. 2017 - Révision : 2

Commentaires