L'arithmétique en virgule flottante peut fournir des résultats incorrects dans Excel

Cet article décrit comment Microsoft Excel stocke et calcule les nombres à virgule flottante. Ceci peut modifier les résultats de certains nombres ou de certaines formules en raison de l'arrondissement ou de la troncature des données.

Présentation

Microsoft Excel a été conçu sur la base de la spécification IEEE 754 en ce qui concerne le stockage et le calcul de nombres à virgule flottante. IEEE (Institute of Electrical and Electronics Engineers) est un organisme international qui détermine notamment les standards utilisés pour les logiciels et matériels informatiques. La spécification 754 est une norme largement adoptée décrivant la façon dont les nombres à virgule flottante doivent être stockés dans un ordinateur binaire. Son usage est répandu car elle permet de stocker les nombres à virgule flottante dans un espace relativement réduit et d'effectuer des calculs assez rapidement. La norme 754 est utilisée dans les processeurs d'unités de virgule flottante et de données numériques de pratiquement tous les microprocesseurs basés sur PC existant sur le marché aujourd'hui qui implémentent l'arithmétique à virgule flottante, notamment les processeurs Intel, Motorola, Sun et MIPS.

Lors du stockage de nombres, un nombre binaire correspondant peut représenter chaque nombre ou nombre fractionnel. Par exemple, la fraction 1/10e peut être représentée dans un système de numération décimal comme 0,1. Toutefois, ce même nombre converti au format binaire prend la forme du nombre décimal binaire répétitif suivant : qui peut être répété à l'infini. Ce nombre ne peut pas être représenté dans un espace fini (limité). Par conséquent, il est arrondi d'environ -2.8E-17 lorsqu'il est stocké.

Toutefois, la spécification IEEE 754 présente certaines limitations, que l'on peut classer en trois catégories générales :

• limites maximale/minimale ;
• précision ;
• nombres binaires répétitifs.

Limites maximale/minimale

Tous les ordinateurs ont une limite de nombre maximal et minimal qu'ils peuvent traiter. Étant donné que le nombre de bits de mémoire dans lesquels le nombre est stocké est fini, le nombre maximum ou minimum pouvant être stocké est également fini. Pour Excel, le nombre maximum pouvant ê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 précision : un dépassement de précision 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 (avec pour exception que la norme IEEE prend en charge le concept de -0, contrairement à Excel).
• Dépassement de capacité : un dépassement de capacité se produit lorsqu'un nombre généré est trop grand pour être représenté. Excel utilise sa propre représentation dans ce cas (#NOMBRE!).

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

• Nombres dénormalisés : 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 par un nombre 1 implicite. Ceci provoque une perte de précision ; plus le nombre est petit, plus la perte de précision est importante. Les nombres situés dans le bas de cette plage ont un seul chiffre de précision.
  Exemple : un nombre normalisé commence par un nombre 1 implicite. Par exemple, si la mantisse représente 0011001, le nombre normalisé devient 10011001 en raison du nombre 1 implicite au début. Un nombre dénormalisé n'a pas de chiffre 1 implicite au début, 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 normalisé en a cinq (11001), les zéros du début n'étant pas significatifs.
  
  Les nombres dénormalisés sont en fait une solution de contournement permettant de stocker des nombres plus petits que la limite minimale normale. Microsoft n'implémente pas cette partie facultative de la spécification car les nombres dénormalisés ont, par nature, un nombre variable de chiffres significatifs. Ceci peut créer des erreurs importantes lors des calculs.
• Infinis positif/négatif : les infinis résultent d'une division par 0. Excel ne prend pas en charge les infinis et renvoie une erreur #DIV/0!
• NAN : NAN (Not A Number) 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. Excel génère immédiatement à la place une erreur telle que #NOMBRE! 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. 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 de 2 est +1 023 et la puissance minimale de 2 est ?1 022) et la mantisse stocke le nombre lui-même. La zone de stockage finie de la mantisse limite le degré de proximité de deux nombres à virgule flottante adjacents (c'est-à-dire la précision).

La mantisse et l'exposant peuvent tous deux être stockés en tant que composants séparés. Par conséquent, le degré de précision possible peut varier selon la taille du nombre (de la mantisse) à manipuler. Dans le cas d'Excel, bien qu'Excel puisse stocker des nombres de 1,79769313486232E308 à 2,2250738585072E-308, il ne peut le faire qu'avec une précision de 15 chiffres. Cette limitation est le résultat direct d'un strict respect de la spécification IEEE 754 et non une limitation d'Excel. Ce degré de précision est le même que celui trouvé dans d'autres tableurs.

Les nombres à virgule flottante sont représentés sous la forme suivante, où exposant est l'exposant binaire : fraction est la partie fractionnelle normalisée du nombre ; elle est normalisée car l'exposant est ajusté de sorte que le bit de début est toujours un 1. Il ne doit donc pas être stocké et vous disposez d'un bit de précision supplémentaire. C'est la raison pour laquelle il existe un bit « impliqué ». Ceci est similaire à la notation scientifique, dans laquelle vous manipulez l'exposant de façon à avoir un chiffre à gauche de la virgule, si ce n'est que dans le format binaire, vous pouvez toujours manipuler l'exposant de sorte que le premier bit soit un 1, parce que le nombre est composé uniquement de 1 et de 0.

Le biais est la valeur de compensation utilisée pour éviter de stocker les exposants négatifs. Le biais des nombres à simple précision est 127 et celui des nombres à double précision est 1 023 (décimal). Excel stocke les nombres en utilisant la double précision.

Exemple d'utilisation de très grands nombres

Entrez le texte suivant dans un nouveau classeur : La valeur résultante de la cellule C1 serait 1,2E+200, la même que celle de la cellule A1. En fait, si vous comparez les cellules A1 et C1 à l'aide de la fonction SI, par exemple SI(A1=C1), vous obtenez le résultat VRAI. Ceci est dû à la spécification IEEE, qui limite le degré de précision à 15 chiffres significatifs pour le stockage. Pour stocker le calcul ci-dessus, Excel nécessiterait au moins 100 chiffres de précision.

Exemple d'utilisation de très petits nombres

Entrez le texte suivant dans un nouveau classeur : La valeur résultante de la cellule C1 serait 1,00012345678901 au lieu de 1,000123456789012345. Ceci est dû à la spécification IEEE, qui stocke uniquement 15 chiffres significatifs de précision. Pour 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 l'option de classeur Calcul avec la précision au format affiché ou Définir le calcul avec la précision au format affiché.

Méthode 1 : La fonction ARRONDI

L'exemple suivant, utilisant les données indiquées plus haut, utilise la fonction ARRONDI pour forcer un nombre à n'avoir que cinq chiffres. Ceci vous permet de comparer correctement le résultat à une autre valeur.

Méthode 2 : Calcul avec la précision au format affiché

Dans certains cas, vous serez peut-être en mesure d'utiliser l'option Calcul avec la précision au format affiché pour empêcher les erreurs d'arrondi d'affecter votre travail. Cette option force chaque nombre du classeur à prendre la valeur affichée. Pour activer cette option, procédez comme suit :

1. Dans Excel 2003 et dans les versions antérieures d'Excel, cliquez sur Options dans le menu Outils.
2. Sous l'onglet Calcul, activez la case à cocher Calcul avec la précision au format affiché.

1. Dans Excel 2007, cliquez sur le Bouton Microsoft Office, sur Options Excel, puis sur la catégorie Options avancées.
2. Dans la section Lors du calcul de ce classeur, sélectionnez le classeur souhaité, puis activez la case à cocher Définir le calcul avec la précision au format affiché.

Par exemple, si vous choisissez un format numérique affichant deux décimales, puis que vous activez l'option Calcul avec la précision au format affiché, toute précision au-delà des deux décimales sera perdue lorsque vous enregistrerez votre classeur. Cette option concerne l'ensemble du classeur, avec 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 sont proches de zéro

Un autre problème lié au stockage des nombres à virgule flottante au format binaire est que certains nombres, qui sont finis et non répétitifs en base décimale, deviennent des nombres infinis et répétitifs en base binaire. L'exemple le plus connu de ce problème est la valeur 0,1 et ses variantes. Bien que ces nombres puissent être représentés parfaitement en base décimale, le même nombre en format binaire devient le nombre binaire répétitif suivant lorsqu'il est stocké dans la mantisse : La spécification IEEE 754 ne contient pas de disposition spéciale pour un nombre spécifique ; elle stocke ce qu'elle peut dans la mantisse et tronque le reste. Ceci résulte en une erreur d'environ -2,8E-17, ou 0,000000000000000028, lors du stockage.

Même les fractions décimales courantes, telles que le nombre décimal 0,0001, ne peuvent pas être représentées exactement en format binaire. (0,0001 est une fraction répétitive binaire avec 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 base décimale (nombre décimal répétitif 0,33333333333333333333).

Ceci explique pourquoi le simple exemple suivant dans Microsoft Visual Basic pour Applications imprimera 0,999999999999996 comme sortie. La petite erreur de représentation de 0,0001 en base binaire est propagée dans la somme.

Exemple lors de l'ajout d'un nombre négatif

1. Entrez les données suivantes dans un nouveau classeur :

      A1 : =(43.1-43.2)+1

2. Cliquez avec le bouton droit sur la cellule A1, puis cliquez sur Format de cellule. Sous l'onglet Nombre, sous Catégorie, cliquez sur Scientifique. Définissez le Nombre de décimales avec la valeur 15.

Plutôt que d'afficher 0,9, Excel affiche 0,899999999999999. Étant donné que (43,1-43,2) est le premier calcul effectué, -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 les données suivantes dans un nouveau classeur :

      A1 : =1.333+1.225-1.333-1.225

2. Cliquez avec le bouton droit sur la cellule A1, puis cliquez sur Format de cellule. Sous l'onglet Nombre, sous Catégorie, cliquez sur Scientifique. Définissez le Nombre de décimales avec la valeur 15.

Plutôt que d'afficher 0, Excel 95 affiche -2,22044604925031E-16.

Excel 97, toutefois, introduit une optimisation pour résoudre ce problème. Si une opération d'addition ou de soustraction résulte en une valeur égale à zéro ou très proche de zéro, Excel 97 et version ultérieure compense toute erreur due à la conversion d'un opérande à partir du format binaire ou vers le format binaire. L'exemple ci-dessus, dans Excel 97 et version ultérieure, affiche correctement 0 ou 0,000000000000000E+00 en notation scientifique. Pour plus d'informations, cliquez sur les numéros ci-dessous pour afficher les articles correspondants de la Base de connaissances Microsoft. Pour plus d'informations sur les nombres à virgule flottante et la spécification IEEE 754, reportez-vous aux sites Web suivants  :

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.