L’arithmétique en virgule flottante peut produire des résultats incorrects dans Excel

Notes

Office 365 ProPlus est renommé applications Microsoft 365 pour les entreprises. Pour plus d’informations sur cette modification, voir ce billet de blog.

Résumé

Cet article explique comment Microsoft Excel stocke et calcule les nombres à virgule flottante. Cela peut avoir une incidence sur les résultats de certains nombres ou formules en raison de l’arrondissement ou de la troncation des données.

Vue d’ensemble

Microsoft Excel a été conçu sur la spécification IEEE 754 afin de déterminer comment il stocke et calcule les nombres à virgule flottante. IEEE est l’Institut d’ingénieurs électriques et électroniques, un organisme international qui, entre autres, détermine les normes pour les logiciels et le matériel informatiques. La spécification 754 est une spécification très largement adoptée qui décrit comment les nombres à virgule flottante doivent être stockés sur un ordinateur binaire. Elle est populaire car elle permet de stocker des nombres à virgule flottante dans une quantité d’espace raisonnable et des calculs relativement rapides. La norme 754 est utilisée dans les unités à virgule flottante et les processeurs de données numériques de quasiment tous les microprocesseurs PC d’aujourd’hui qui implémentent la fonction Math à virgule flottante, y compris les processeurs Intel, Motorola, Sun et MIPS.

Lorsque des nombres sont stockés, un nombre binaire correspondant peut représenter chaque nombre ou nombre fractionnaire. Par exemple, la fraction 1/10 peut être représentée dans un système de chiffres décimaux comme 0,1. Toutefois, le même nombre au format binaire devient le séparateur décimal binaire répétitif suivant :

0001100110011100110011 (et ainsi de suite)

Cela peut être répété de façon infinie. Ce nombre ne peut pas être représenté dans un espace fini (limité). Par conséquent, ce nombre est arrondi d’approximativement-2,8 E-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 maximale/minimale
  • Dell
  • Nombres binaires répétitifs

Informations supplémentaires

Limites maximale/minimale

Tous les ordinateurs ont un nombre maximal et un nombre minimal pouvant être gérés. Étant donné que le nombre de bits de mémoire dans lequel le nombre est stocké est fini, il en résulte que le nombre maximal ou minimal pouvant être stocké est également fini. Pour Excel, le nombre maximal pouvant être stocké est de 79769313486232e308 E + 308 et le nombre minimal de chiffres positifs pouvant être stockés est de 2.2250738585072 E-308.

Cas dans lesquels nous adhérons à l’IEEE 754

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

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

  • Numéros 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 la mantisse n’a pas d’interlignage implicite 1. Par conséquent, vous perdez la précision et plus le nombre est faible, plus la précision est perdue. Les numéros à la petite extrémité de cette plage n’ont qu’un seul chiffre de précision.

    Exemple : un nombre normalisé est implicite 1. Par exemple, si la mantisse représente 0011001, le nombre normalisé devient 10011001 en raison de l’élément 1 de début implicite. Un nombre dénormalisé n’a pas d’interlignage implicite, donc dans notre exemple de 0011001, le nombre dénormalisé reste le même. Dans ce cas, le nombre normalisé comporte huit chiffres significatifs (10011001) tandis que le nombre dénormalisé comporte cinq chiffres significatifs (11001) avec des zéros non significatifs.

    Les numéros dénormalisés sont fondamentalement une solution de contournement permettant de stocker des nombres plus petits que la limite inférieure normale. Microsoft n’implémente pas cette partie facultative de la spécification car les nombres dénormalisés par leur nature possèdent un nombre variable de chiffres significatifs. Cela peut entraîner une erreur importante lors de l’entrée de calculs.

  • Infinis positifs/négatifs : les infinis se produisent lorsque vous divisez par 0. Excel ne prend pas en charge les infinis, mais il donne une #DIV/0 ! erreur dans ces cas.

  • Not-a-Number (NaN) : NaN est utilisé pour représenter des opérations non valides (par exemple, l’infini/infini, l’infini-infini ou la racine carrée de-1). Les Nan permettent à un programme de continuer après une opération non valide. Excel génère immédiatement une erreur telle que #NUM ! ou #DIV/0 !.

Dell

Un nombre à virgule flottante est stocké en trois parties dans une plage de 65 bits : le signe, l’exposant et la mantisse. ||||| |---|---|---|---| |1 bit de signe|Exposant 11 bits|1 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 réel. La zone de stockage finie pour la mantisse limite la fermeture de deux nombres à virgule flottante adjacente (autrement dit, la précision).

La mantisse et l’exposant sont tous deux stockés en tant que composants distincts. Par conséquent, la quantité de précision possible peut varier en fonction de la taille du nombre (la mantisse) en cours de manipulation. Dans le cas d’Excel, bien qu’Excel puisse stocker des nombres à partir de 79769313486232e308 E308 vers 2.2250738585072 E-308, il ne peut le faire que dans les 15 chiffres de précision. Cette limitation est un résultat direct du respect de la spécification IEEE 754 et n’est pas une limitation d’Excel. Ce niveau de précision est également trouvé dans les autres programmes de feuille de calcul.

Les nombres à virgule flottante sont représentés sous la forme suivante, où Exponent est l’exposant binaire :

X = fraction * 2 ^ (exposant-Bias)

Fraction est la partie fractionnaire normalisée du nombre, normalisée car l’exposant est ajusté de sorte que le bit de début est toujours 1. De cette manière, il n’est pas nécessaire de le stocker et vous obtenez un plus de précision. C’est pourquoi il existe un bit implicite. Ceci est similaire à la notation scientifique, où vous manipulez l’exposant de manière à ce qu’il ait un chiffre à gauche du séparateur décimal ; à l’exception de Binary, vous pouvez toujours manipuler l’exposant de sorte que le premier bit soit 1, car il n’y a que 1 et 0.

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

Exemple utilisant des nombres très grands

Entrez les informations suivantes dans un nouveau classeur :

A1: 1.2E+200
B1: 1E+100
C1: =A1+B1 

La valeur résultante dans la cellule C1 est 1,2 E + 200, la même valeur que celle de la cellule a1. En fait, si vous comparez les cellules a1 et C1 à l’aide de la fonction IF, par exemple si (a1 = C1), le résultat sera TRUE. Cela est dû à la spécification IEEE permettant de stocker seulement 15 chiffres significatifs de précision. Pour pouvoir stocker le calcul ci-dessus, Excel nécessite au moins 100 chiffres de précision.

Exemple utilisant de très petits nombres

Entrez les informations suivantes dans un nouveau classeur :

A1: 0.000123456789012345
B1: 1
C1: =A1+B1 

La valeur résultante dans la cellule C1 est 1.00012345678901 au lieu de 1.000123456789012345. Cela est dû à la spécification IEEE permettant de stocker seulement 15 chiffres significatifs de précision. Pour pouvoir stocker le calcul ci-dessus, Excel doit avoir au moins 19 chiffres de précision.

Correction des erreurs de précision

Excel propose deux méthodes de base pour compenser les erreurs d’arrondi : la fonction ROUND et la précision telle qu' elle est affichée ou définissez la précision comme le classeur affiché.

Méthode 1 : la fonction ROUND

À l’aide des données précédentes, l’exemple suivant utilise la fonction ROUND 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) 

Il en résulte 1,2 e + 200.

D1 : = SI (C1 = 1.2 E + 200, TRUE, FALSE)

Cela aboutit à la valeur TRUE.

Méthode 2 : précision telle qu’affichée

Dans certains cas, il se peut que vous puissiez empêcher les erreurs d’arrondi d’affecter votre travail à l’aide de l’option * * précision comme indiqué * *. Cette option force la valeur de chaque numéro de la feuille de calcul à être la valeur affichée. Pour activer cette option, procédez comme suit.

  1. Dans le menu fichier , cliquez sur options, puis sur la catégorie avancé .
  2. Dans la section lors du calcul de ce classeur , sélectionnez le classeur de votre choix, puis activez la case à cocher définir la précision comme affichée .

Par exemple, si vous choisissez un format de nombre qui affiche deux décimales, puis que vous activez l’option précision au format affiché   , toutes les précisions au-delà de deux décimales sont perdues lors de l’enregistrement du 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écurrents et calculs ayant des résultats proches de zéro

Un autre problème de confusion affectant le stockage des nombres à virgule flottante au format binaire est que certains nombres qui sont des nombres finis, non répétitifs en base décimale 10, sont infinis et des nombres répétitifs en binaire. L’exemple le plus courant 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 prévoit aucune allocation spéciale pour aucun nombre. Il stocke ce qu’il peut dans la mantisse et tronque le reste. Cela provoque une erreur de-2,8 E-17, ou 0,000000000000000028 lorsqu’il est stocké.

Même les fractions décimales courantes, telles que le décimal 0,0001, ne peuvent pas être représentées exactement dans le format binaire. (0,0001 est une fraction binaire répétitive qui a une période de 104 bits). Cela est similaire à la raison pour laquelle la fraction 1/3 ne peut pas être exactement représentée en tant que décimale (0.33333333333333333333 extensible).

Par exemple, prenons 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

Cette opération imprime 0.999999999999996 en tant que sortie. La petite erreur en représentant 0,0001 en binaire se propage à la somme. 

Exemple : ajout d’un nombre négatif

  1. Entrez les informations 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, cliquez sur scientifique sous catégorie. Définissez le nombre de décimales sur 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-0,1 est introduite dans le calcul. 

Exemple lorsqu’une valeur atteint zéro

  1. Dans Excel 95 ou une version antérieure, entrez les informations suivantes dans un nouveau classeur :

    A1 : = 1.225 +-cellule-1.225

  2. Cliquez avec le bouton droit clickcell a1, puis cliquez sur format de cellule. Sous l’onglet nombre, cliquez sur scientifique sous catégorie. Définissez le nombre de décimales sur 15.

Au lieu d’afficher 0, Excel 95 affiche-2.22044604925031 E-16.

Excel 97, toutefois, a introduit une optimisation qui tente de corriger ce problème. Si une opération d’ajout ou de soustraction aboutit à une valeur égale ou supérieure à zéro, Excel 97 et les versions ultérieures compensent toute erreur introduite suite à la conversion d’un opérande vers et à partir de binaire. L’exemple ci-dessus, lorsqu’il est effectué dans Excel 97 et versions ultérieures, affiche correctement 0 ou 0.000000000000000 E + 00 en notation scientifique.

Pour plus d’informations sur les nombres à virgule flottante et la spécification IEEE 754, reportez-vous aux sites Web suivants :