IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Voir le flux RSS

Pierre Fauconnier

Erreur d'arrondi avec Excel (calcul en virgule flottante)

Noter ce billet
par , 29/09/2021 à 09h00 (1954 Affichages)
Salut.

Analyse du cas (issu de cette discussion)

290 543.05 - 300 141.26 = -9 598.21 (Oui oui, vous pouvez vérifier...)

D'ailleurs, Excel semble calculer cela correctement...

Nom : 2021-09-28_104012.png
Affichages : 845
Taille : 2,9 Ko


"Semble" calculer cela correctement? Pourquoi semble??

Parce que Excel nous trompe en ajustant l'affichage à ce qu'il "pense" être notre souhait... Soustraire une valeur avec 2 décimales d'une autre valeur avec 2 décimales suppose que l'on souhaite recevoir la réponse avec 2 décimales... Et les mathématiques sont formelles sur ce point => Un truc à 2 décimales - un autre truc à 2 décimales, ça ne peut que donner un truc avec 2 décimales. Il est RI-GOU-REU-SE-MENT impossible que cela donner un truc à 10 ou 11 décimales...

Et donc, en format standard, Excel ajuste l'affichage à 2 décimales... Mais, si on ajoute des décimales ( Nom : 2021-09-28_104321.png
Affichages : 224
Taille : 287 octets ), on arrive à une bizarrerie...

Nom : 2021-09-28_104517.png
Affichages : 223
Taille : 3,1 Ko


De cette bizarrerie en découle une autre...

Nom : 2021-09-28_103836.png
Affichages : 224
Taille : 2,8 Ko


Ben oui... 9 598.21 n'est pas égal à 9 598.21000000002 => FAUX

Solution? Arrondir aux nombre de décimales souhaitées par fonction (*)

Nom : 2021-09-28_104943.png
Affichages : 226
Taille : 3,0 Ko

Ouf... On a eu chaud!

Explications

Excel traite les valeurs décimales en "virgule flottante" car cela permet d'utiliser moins d'octets pour stocker un nombre, mais surtout, cela rend les calculs beaucoup plus rapides car les nombres sont exprimés en valeur binaire (la seule façon de "parler" au processeur). Mais cette rapidité de calcul se paie en précision, car cela veut dire que les parties décimales d'un nombre ne sont pas exprimées par les chiffres de 0 à 9, mais bien par des puissances négatives de 2 (puisque la valeur est exprimée par des bits dans des octets).

Nom : 2021-09-28_105414.png
Affichages : 259
Taille : 10,8 Ko

Du coup, si on veut exprimer 0.140, il faut additionner les puissances négatives de 2 jusqu'à arriver au résultat... Sur l'illustration suivante, on voit que même en additionnant 16 puissances négatives de 2, on n'arrive pas encore à 0.140 mais à 0.1399999999.

Nom : 2021-09-28_210130.png
Affichages : 246
Taille : 215,9 Ko



Et comme Excel ne va pas calculer "à l'infini" et qu'il boucle un nombre limité de fois pour ajouter une valeur de plus en plus petite, il se fait que la valeur restituée par somme des puissances négatives de 2 n'est pas toujours tout à fait égale à celle exprimée après le nombre d'itérations maximum. Mais après l'addition de ces x puissances négatives de 2, on arrive très rarement à un écart. C'est pourquoi l'on ne remarque pas très souvent le problème. Manque de pot, voilà aussi pourquoi 290 543.05 - 300 141.26 ne vaut pas, pour Excel, tout à fait -9 598.21...

CQFD



(*) Rappel: Sauf à modifier une fonction d'Excel à laquelle je vous déconseille de toucher, l'arrondi d'affichage ne modifie pas la valeur utilisée par Excel pour ses calculs.



.

Envoyer le billet « Erreur d'arrondi avec Excel (calcul en virgule flottante) » dans le blog Viadeo Envoyer le billet « Erreur d'arrondi avec Excel (calcul en virgule flottante) » dans le blog Twitter Envoyer le billet « Erreur d'arrondi avec Excel (calcul en virgule flottante) » dans le blog Google Envoyer le billet « Erreur d'arrondi avec Excel (calcul en virgule flottante) » dans le blog Facebook Envoyer le billet « Erreur d'arrondi avec Excel (calcul en virgule flottante) » dans le blog Digg Envoyer le billet « Erreur d'arrondi avec Excel (calcul en virgule flottante) » dans le blog Delicious Envoyer le billet « Erreur d'arrondi avec Excel (calcul en virgule flottante) » dans le blog MySpace Envoyer le billet « Erreur d'arrondi avec Excel (calcul en virgule flottante) » dans le blog Yahoo

Mis à jour 29/09/2021 à 12h42 par Pierre Fauconnier

Catégories
Excel , MS Office , Trucs & Astuces

Commentaires

  1. Avatar de patmicro
    • |
    • permalink
    Bonjour Mr Pierre : Merci beaucoup pour cette argumentation.
  2. Avatar de Pierre Fauconnier
    • |
    • permalink
    Citation Envoyé par patmicro
    Bonjour Mr Pierre : Merci beaucoup pour cette argumentation.
    Avec plaisir. Ca ne change pas la vie de comprendre le pourquoi du comment, mais sur un plan intellectuel, c'est toujours intéressant de connaître l'envers du décor...
    Mis à jour 29/09/2021 à 14h11 par Pierre Fauconnier