L’arithmétique à virgule flottante peut entraîner des résultats imprécis 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 formules en raison de l’arrondi ou de la troncation des données.
Aperçu
Microsoft Excel stocke et calcule les nombres à virgule flottante selon la norme IEEE 754. IEEE désigne l’Institute of Electric and Electronics Engineers, un organisme international qui, entre autres choses, détermine les normes relatives aux logiciels et au matériel informatique. La norme 754, largement adoptée, décrit comment les nombres à virgule flottante doivent être stockés sur un ordinateur utilisant un système binaire. Elle est populaire car elle permet de stocker des nombres à virgule flottante dans une quantité d’espace raisonnable et offre une durée de calcul relativement rapide. La norme 754 est utilisée dans les unités à virgule flottante et les processeurs de données numériques de presque tous les microprocesseurs PC actuels qui implémentent des calculs en 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 par 0,1 dans un système de nombres décimaux. Toutefois, le même nombre au format binaire devient le nombre décimal binaire répétitif suivant :
0001100110011100110011 (etc.)
Cela peut se répéter indéfiniment. Ce nombre ne peut pas être représenté dans une quantité d’espace finie (limitée). Par conséquent, ce nombre est arrondi à -2,8E-17 environ lorsqu’il est stocké.
Toutefois, la norme IEEE 754 comporte certaines limitations qui relèvent de trois catégories générales :
- Limitations maximales/minimales
- Précision
- Nombres binaires répétitifs
Informations supplémentaires
Limitations maximales/minimales
Tous les ordinateurs ont un nombre maximal et minimal qui peut être géré. Comme le nombre de bits de mémoire dans lesquels le nombre est stocké est fini, le nombre maximal ou minimal qui peut être stocké est également fini. Pour Excel, le nombre maximal qui peut être stocké est 1,79769313486232E+308 et le nombre positif minimal qui peut être stocké est 2,2250738585072E-308.
Situations où la norme IEEE 754 est respectée
- Dépassement de capacité négatif : le dépassement de capacité négatif se produit lorsqu’un nombre généré est trop petit pour être représenté. Dans IEEE et Excel, le résultat est 0 (à l’exception du fait que IEEE a un concept de -0 et Excel non).
- Dépassement de capacité : le dépassement de capacité se produit lorsqu’un nombre est trop grand pour être représenté. Excel utilise sa propre représentation spéciale dans cette situation (#NUM!).
Situations où la norme IEEE 754 nʼest pas respectée
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, qui n’a pas de premier 1 implicite. Par conséquent, vous perdez de la précision. Plus le nombre est petit, plus la perte de précision est importante. Les nombres situés à lʼextrémité inférieure de cette plage nʼont quʼun seul chiffre de précision.
Exemple : un nombre normalisé a un 1 implicite en tête. Par exemple, si la mantisse représente 0011001, le nombre normalisé devient 10011001 en raison du 1 initial implicite. Un nombre dénormalisé n’a pas de un initial implicite, donc dans notre exemple de 0011001, le nombre dénormalisé reste le même. Dans ce cas, le nombre normalisé a huit chiffres significatifs (10011001) tandis que le nombre dénormalisé a cinq chiffres significatifs (11001), les zéros de tête nʼétant pas significatifs.
Les nombres dénormalisés sont essentiellement une solution de contournement permettant le stockage de 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, de par leur nature même, ont un nombre variable de chiffres significatifs. Cela peut entraîner des erreurs importantes dans les calculs.
Nombres infinis positifs/négatifs : les nombres infinis se produisent lorsque vous divisez par 0. Excel ne prend pas en charge les nombres infinis (il renvoie une erreur #DIV/0! dans ces situations).
Not-a-Number (NaN) : NaN est utilisé pour représenter des opérations non valides (telles que nombre infini / nombre infini, nombre infini - nombre 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!.
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.
| Signe | Exposant | Mantisse |
|---|---|---|
| 1 bit de signe | Exposant de 11 bits | 1 bit implicite + fraction de 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é ou abaissé (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 de la mantisse limite la proximité de deux nombres à virgule flottante adjacents (c’est-à-dire, la précision).
La mantisse et l’exposant sont tous deux stockés en tant que composants distincts. Par conséquent, la précision possible peut varier en fonction de la taille du nombre (la mantisse) manipulée. Dans le cas d’Excel, bien qu’il puisse stocker des nombres de 1,79769313486232E308 à 2,2250738585072E-308, il ne peut le faire qu’avec 15 chiffres de précision. Il ne s’agit pas d’une limitation d’Excel, mais du résultat direct du respect strict de la norme IEEE 754. Ce niveau de précision est également appliqué dans d’autres tableurs.
Les nombres à virgule flottante sont représentés sous la forme suivante, où « exposant » correspond à l’exposant binaire :
X = fraction * 2^(exposant - biais)
La fraction est la partie fractionnaire normalisée du nombre, normalisée car l’exposant est ajusté de sorte que le bit de début corresponde toujours à 1. De cette façon, il ne doit pas être stocké et vous obtenez un bit de précision de plus. C’est pourquoi il y a un bit implicite. Ce principe est similaire à la notation scientifique, où vous manipulez l’exposant pour qu’il ait un chiffre à gauche de la décimale. À l’exception du format binaire, il est toujours possible de manipuler l’exposant afin que le premier bit corresponde à 1, car il n’y a que 1 et des 0.
Le biais est la valeur de biais utilisée pour éviter de devoir stocker des exposants négatifs. Le biais de nombres simple précision est de 127 et de 1 023 (décimal) pour les nombres double précision. Excel stocke les nombres à l’aide de la double précision.
Exemple d’utilisation de très grands nombres
Entrez les éléments ci-dessous dans un nouveau classeur :
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 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), le résultat est VRAI. Cela est dû à la spécification IEEE qui ne stocke que 15 chiffres de précision significatifs. Pour pouvoir stocker le calcul ci-dessus, Excel nécessite au moins 100 chiffres de précision.
Exemple d’utilisation de très petits nombres
Entrez les éléments ci-dessous dans un nouveau classeur :
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 qui ne stocke que 15 chiffres de précision significatifs. Pour pouvoir stocker le calcul ci-dessus, Excel nécessite 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 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 : Fonction ARRONDI
À l’aide des données précédentes, l’exemple suivant utilise la fonction ARRONDI pour forcer un nombre à cinq chiffres. Vous pouvez ainsi comparer correctement le résultat à une autre valeur.
A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5)
La valeur résultante est 1,2E+200.
D1 : =SI(C1=1,2E+200, VRAI, FAUX)
Cela entraîne la valeur VRAI.
Méthode 2 : Calcul avec la précision au format affiché
Dans certains cas, vous pouvez empêcher les erreurs d’arrondi à l’aide de l’option Calcul avec la précision au format affiché. Cette option impose la valeur de chaque nombre de la feuille de calcul comme étant la valeur affichée. Pour activer cette option, procédez comme suit :
- Dans le menu Fichier, cliquez sur Options, puis sur la catégorie Options avancées.
- Dans la section Lors du calcul de ce classeur, sélectionnez le classeur de votre choix, puis activez la case Définir le calcul avec la précision au format affiché.
Par exemple, si vous choisissez un format numérique qui affiche deux décimales, puis que vous activez l’option Calcul avec la précision au format affiché, la précision au-delà de deux décimales est perdue quand vous enregistrez votre classeur. Cette option s’applique au classeur actif, y compris à l’ensemble des feuilles de calcul. Vous ne pouvez pas annuler cette option pour récupérer les données perdues. Il est dès lors recommandé d’enregistrer votre classeur avant d’utiliser cette option.
Nombres binaires périodiques et calculs ayant des résultats proches de zéro
Un autre problème déroutant qui concerne le stockage des nombres à virgule flottante au format binaire est que certains nombres qui sont des nombres périodiques finis en base décimale 10, sont des nombres non périodiques infinis au format binaire. L’exemple le plus courant est la valeur 0,1 et ses variantes. Bien que ces nombres puissent être parfaitement représentés en base 10, le même nombre au format binaire devient le nombre binaire périodique suivant quand il est stocké dans la mantisse :
000110011001100110011 (et ainsi de suite)
La spécification IEEE 754 ne prévoit aucune tolérance particulière pour les nombres. Elle stocke ce qu’elle peut dans la mantisse et tronque le reste. Cela entraîne une erreur d’environ -2,8E-17 ou 0,000000000000000028 lors du stockage.
Même les fractions décimales courantes, telles que la décimale 0,0001, ne peuvent pas être représentées exactement au format binaire. (0,0001 est une fraction binaire périodique d’une période de 104 bits.) Il s’agit d’une raison similaire à celle pour laquelle la fraction 1/3 ne peut pas être représentée exactement au format décimal (nombre périodique 0,33333333333333333333).
Prenons l’exemple ci-dessous 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
Cet exemple génère la sortie PRINT 0,999999999999996. La faible erreur dans la représentation de 0,0001 au format binaire se propage à la somme.
Exemple : ajout d’un nombre négatif
Entrez les éléments ci-dessous dans un nouveau classeur :
A1 : =(43,1-43,2)+1
Cliquez avec le bouton droit sur la cellule A1, puis cliquez sur Format de cellule. Dans l’onglet Nombre, sous Catégorie, cliquez sur Scientifique. 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, la valeur -0,1 est stockée temporairement et l’erreur liée au stockage de -0.1 est introduite dans le calcul.
Exemple quand une valeur atteint zéro
Dans Excel 95 ou une version antérieure, entrez les éléments suivants dans un nouveau classeur :
A1 : =1,333+1,225-1,333-1,225
Cliquez avec le bouton droit sur la cellule A1, puis cliquez sur Format de cellule. Dans l’onglet Nombre, sous Catégorie, cliquez sur Scientifique. Définissez le nombre de décimales sur 15.
Au lieu d’afficher 0, Excel 95 affiche -2,22044604925031E-16.
Toutefois, Excel 97 a introduit une optimisation qui tente de corriger ce problème. Si une opération d’ajout ou de soustraction a pour résultat une valeur égale ou très proche de zéro, Excel 97 et les versions ultérieures compensent toute erreur introduite à la suite de la conversion d’un opérande vers et à partir du format binaire. En cas d’exécution dans Excel 97 et les versions ultérieures, l’exemple ci-dessus affiche correctement 0 ou 0,000000000000000E+00 en notation scientifique.
Pour plus d’informations sur les nombres à virgule flottante et sur la norme IEEE 754, consultez les sites web suivants :