Excel se trompe dans une simple soustraction

[LPandre]

Bonjour,
cherchant à faire quelques cadrages, je découvre qu'Excel se trompe dans une "bête" soustraction.
Si quelqu'un(e) peut m'expliquer le résultat des cellules <> de 0 en colonne D !!??

NB : en fonction de la valeur de la cellule B1 qui sert de référence, les cellules en erreur sont plus ou moins nombreuses.

Par avance merci.

 

[Dranreb]

Re : Excel se trompe dans une simple soustraction

Bonjour.
L'explication tient en des pertes de bits en système de numération binaires sur les divisions par des nombres non puissances de 2, tout comme il y a des pertes de chiffres en système décimal sur, par exemple, des remultiplications par 3 de nombres divisés par 3: vous perdez une infinité de "3" au delà d'un certain nombre de décimales de sorte que vous retrouvez 0.999999… au lieu de 1, là c'est pareil.

Edit: Bonjour chris. Et j'ai oublié de préciser ce n'est pas de la faute d'Excel. N'importe quel logiciel est soumis aux composant électroniques d'un ordinateur ! Il est vrai qu'Excel aurait pu exiger que les calculs soit effectués en décimal, l'UC sait faire, mais c’eut été au détriment certain de la performance globale.

 

[chris]

Re : Excel se trompe dans une simple soustraction

Bonjour

C'est un problème connu : Excel travaille en virgule flottante d'où des pouillèmes. Utiliser la fonction arrondi pour les éviter.

Edit : Coucou Dranreb 😉

 

[LPandre]

Re : Excel se trompe dans une simple soustraction

Merci Dranreb, mais là il n'y a aucune multiplication / division. Ou alors je dois comprendre que pour Excel le problème de "perte binaire" est le même quelle que soit l'opération arithmétique utilisée ?

Dans le logiciel Calc de Libre office le problème existe mais est moins étendu, alors que l'ordi est le même !

Reste que du coup je suis obligé de borner mes contrôles avec une marge de tolérance.

@ chris : La fonction arrondi ne me convient pas, je dois contrôler toutes mes formules, et j'avais mis un message d'alerte si le résultat est différent de 0.
Mais je vais faire avec le bornage.

 

[Dranreb]

Re : Excel se trompe dans une simple soustraction

Il y a division implicite par 5 (10, mais le 2 qu'il contient ne compte donc pas) dans la représentation interne du nombre, donc imparfaite, dès lors que des décimales sont spécifiées.

 

[Roland_M]

Re : Excel se trompe dans une simple soustraction

bonjour à tous,

essais comme ceci, exemple ici en ligne 3:
=C3-(B3+A3)

 

[Modeste geedee]

Re : Excel se trompe dans une simple soustraction

Bonsour®
utiliser l'option :
Options >> Options avancées
cocher effectuer les calculs au format affiché


selon le format décimal choisi pour le résultat, le calcul sera considéré exact ou pas ...
dans le cas présent (exemple) l'erreur apparait si l'on souhaite un affichage avec une précision supérieure à la 13éme décimale...

😕 quand on effectue un calcul au 100éme pourquoi vouloir afficher plus précis ???

 

[LPandre]

Re : Excel se trompe dans une simple soustraction

Merci à tous pour les précisions et solutions !

 

[Cedcedpro]

Re : Excel se trompe dans une simple soustraction

Bonjour.
L'explication tient en des pertes de bits en système de numération binaires sur les divisions par des nombres non puissances de 2, tout comme il y a des pertes de chiffres en système décimal sur, par exemple, des remultiplications par 3 de nombres divisés par 3: vous perdez une infinité de "3" au delà d'un certain nombre de décimales de sorte que vous retrouvez 0.999999… au lieu de 1, là c'est pareil.

Edit: Bonjour chris. Et j'ai oublié de préciser ce n'est pas de la faute d'Excel. N'importe quel logiciel est soumis aux composant électroniques d'un ordinateur ! Il est vrai qu'Excel aurait pu exiger que les calculs soit effectués en décimal, l'UC sait faire, mais c’eut été au détriment certain de la performance globale.

Bonjour Dranreb,

Je pense que ton explication justifie que des suites de type U(n+1) = a U(n) + b avec U(0) = un nombre décimal à une décimale entre entre 0 et 1, et a et b tels que U(1) = U(0) piègent le tableur excel au bout de quelques itérations, l'arrondi d'excel étant au bout d'un moment amplifié par la multiplication par a.
En revanche, la question que je me pose est la suivante : pourquoi dans le cas d'un U(0) = 0.5 le tableur excel ne se fait pas piéger ? Autrement dit il n'y aurait en apparence pas d'erreur d'arrondi de la part de excel dans ce cas précis ?

Merci par avance de ton retour si tu as la réponse.

Cedced

 

[Dranreb]

Bonjour.
On ne peut reprocher qu'une chose à Excel, c'est de ne pas utiliser le type de donnée Currency pour les montants. À part cela il n'y est pour rien, il ne fait qu'utiliser assez normalement les possibilités de calcul du microprocesseur en binaire virgule flottante double précision.
Des informations et essais possibles dans le classeur joint.

Et pour répondre à votre question, du moment qu'il n'y a pas de dépassement de capacité, ce type de donnée numérique peut représenter avec exactitude un entier <= 9007199254740991 divisé par une puissance de 2 ce qui est le cas de 0.5 (= 2^-1) comme vous pouvez le vérifier en tapant 0,5 en C18 de la feuille "Valeurs Excel".

 

[Cedcedpro]

Compris, on code les décimaux en puissances négatives de 2 hormis quelques uns, beaucoup vont avoir une représentation avec une infinité de décimales d'où l'arrondi par l'ordinateur qui code sur un nombre limité d'octets. Merci pour la réponse.

 

[eriiic]

Bonjour à tous,

pour les curieux, la représentation des flottants en binaire et des biais engendrés est bien expliquée ici : https://fr.wikipedia.org/wiki/IEEE_754
eric