Les mystères d'Excel

[Trinity]

Celà fait un moment que j'ai remarqué un problème dans Excel ( toute version confondue) mais jusqu'à présent je ne pouvais pas le déterminer précisément. Dans un bon nombre de documents je compare le résultat d'une soustraction à une valeur de référence min. et une référence max. Dans le cas ou le résultat de cette soustraction n'est pas dans la zone cible je fais apparaître un texte d'alerte. Pourtant, sans que je puisse l'expliquer parfois un résultat égale à la valeur min. me faisait apparaître mon message d'erreur. Après quelques essai j'ai trouvé un truc bizarre.

Pour ceux que ca tente essayez !

Ouvrez un document vierge. Dans la cellule C1 tapez la formule "=A1-B1" Etendez cette bête formule sur une vingtaine de ligne. Selectionnez toute la colonne "C" et cliquez sur le bouton droit de la souris ( ou "ctrl + clic ") Choisissez "format de cellule". Dans l'onglet "nombre" sélectionnez la catégorie "nombre" et augmentez le nombre de décimale à 20, cliquez sur "OK"

Ensuite entrez la valeur 0.96 dans la cellule A1 et 0.93 dans la cellule B1. Puis entrez la valeur 0.97 dans la cellule A2 et 0.94 dans la cellule B2. Entendez jusqu'à la ligne 20 de façon à avoir toujours une soustraction dont le résultat est 0.03. Dans toutes les versions d'Excel que j'ai pu testé j'obtiens plus ou moins ca :

A1 -> 0.96 B1 -> 0.93 C1 -> 0.02999999999999990000
A2 -> 0.97 B2 -> 0.94 C2 -> 0.03000000000000000000
A3 -> 0.98 B2 -> 0.95 C3 -> 0.03000000000000000000
A4 -> 0.99 B3 -> 0.96 C4 -> 0.03000000000000000000
A5 -> 1 B5 -> 0.97 C5 -> 0.03000000000000000000
A6 -> 1.01 B6 -> 0.98 C6 -> 0.03000000000000000000
A7 -> 1.02 B7 -> 0.99 C7 -> 0.03000000000000100000
A8 -> 1.03 B8 -> 1 C8 -> 0.03000000000000100000
A9 -> 1.04 B9 -> 1.01 C9 -> 0.03000000000000000000
A10 -> 1.05 B10 -> 1.02 C10 -> 0.03000000000000000000
A11 -> 1.06 B11 -> 1.03 C11 -> 0.03000000000000000000
A12 -> 1.07 B12 -> 1.04 C12 -> 0.03000000000000000000
A13 -> 1.08 B13 -> 1.05 C13 -> 0.03000000000000000000
A14 -> 1.09 B14 -> 1.06 C14 -> 0.03000000000000000000
A15 -> 1.10 B15 -> 1.07 C15 -> 0.03000000000000000000
A16 -> 1.11 B16 -> 1.08 C16 -> 0.03000000000000000000
A17 -> 1.12 B17 -> 1.09 C17 -> 0.03000000000000000000
A18 -> 1.13 B18 -> 1.10 C18 -> 0.02999999999999980000
A19 -> 1.14 B19 -> 1.11 C19 -> 0.02999999999999980000
A20 -> 1.15 B20 -> 1.12 C20 -> 0.02999999999999980000

Ce ne sont que des petites erreurs mais dans mon cas ou j'utilise le signe ">" ou "<" cela pose problème puisque par exemple sur la ligne 20 le ( faux ) résultat de la soustraction me provoque une alerte.
Etonnant ... non ?

 

[Luc G]

La représentation binaire et la précision finie des nombres sur l'ordinateur rendent inévitables des problèmes d'arrondi dans nombre de cas et excel n'y est pas pour grand chose (c'est l'unité de calcul du processeur qui gère ça, au moins en première approche). En règle générale, on ne peut éviter ces problèmes que par les techniques suivantes :

- travailler en nombre entiers (mais on est limité par une valeur maximale relativement faible) ;
- dans certains logiciels ou langages : travailler en nombres décimaux "exacts", par exemple en DCB (même limitation que ci-dessu)
- travailler en arithmétique exacte sur des grands entiers ou des réels à précision aussi grande qu'on veut, mais ce n'est disponible que dans des outils assez spécialisés (mathematica ou équivaelnt, bibliothèques pour certains langages)

Donc, en général, faut faire avec. Il est donc essentiel de ne pas écrire de tests sur des nombres non entiers basés sur l'égalité : on risque, comme tu l'as vu, d'avoir des surprises.

La vieille technique toujours valable est de remplacer les tests du type (a = b) par des tests du type (abs(a-b) < epsilon) en prenant epsilon petit évidemment.

Plus simple, dans les tableurs (mais dans le fond, ça revient au même) utiliser la fonction ARRONDI qui te permet d'arrondir les nombres à la précision que tu veux. Dans ton cas, tu arrondis avec disons 5 ou 10 décimales et tu pourras alors comparer sans problème.