Erreur arrondi Excel

[H2H2]

Bonjour,
je suis utilisateur d'Excel depuis pas mal de temps, j'ai déjà trouvé des limites à ce logiciel mais LA je ne vois pas, je vous soumets donc le problème. Il s'agit d'une petite comptabilité d'asso.
Tout se passe bien jusqu'à la ligne 43. Je recopie vers le bas mes formules qui me permettent de masquer les valeurs nulles ...
et LA je tombe sur une erreur d'arrondi ! Excel me rajoute 12E-13 à mon résultat ... serait-ce une erreur d'arrondi, cela ressemble à un % du résultat ...
En fermant et en ré-ouvrant Excel 2010 - 32 bits, c'est la même chose.
Je n'ai pas encore essayé en redémarrant Windows 7 ...
Vous en pensez quoi ?
H2H2

 

[TooFatBoy]

Re : Erreur arrondi Excel

Bonjour,

Pour commencer, un fichier avec des coordonées perso, c'est pas une bonne idée...

En suite, dans quelle cellule as-tu ce pb ? Est-ce en F44 ?
Je n'ai pas trouvé de formule utilisant l'arrondi, et je ne vois pas de pb dans les résultats mais je suis sous 2013.

Et enfin, je n'ai pas bien compris : s'il y a un arrondi, comment peux-tu voir qu'Excel ajoute 12E-13 au résultat ?

 

[Staple1600]

Re : Erreur arrondi Excel

Bonsoir à tous

Pour commencer, un fichier avec des coordonées perso, c'est pas une bonne idée...

C'est plus qu'une mauvaise idée
C'est carrément problématique

- DONNEES CONFIDENTIELLES : Ne laissez AUCUNE donnée personnelle ou confidentielle (noms, adresses, N° de téléphone, entreprises...) dans vos pièces jointes. Vous mettriez Excel-Downloads en contravention avec la loi Informatique et Liberté et votre fichier devrait être enlevé.

H2H2 [Bienvenue sur le forum]
Retournes sur ton premier message et passe en mode EDITION (en cliquant sur Modifier le message puis Gérer les pièces jointes)
Supprime ta pièce jointe, stp, et et remplaces là par une version anonymisée.

 

[H2H2]

Re : Erreur arrondi Excel

Bonjour,

OK, c'est noté, j'ai bien compris que laisser des infos personnelles cela ne se faisait pas ! Désolé, d'habitude je suis plus regardant sur ce problème.

J'ai trouvé le moyen d'éditer mon message initial et je reprends donc le cours de ma question :
Lorsque j'étire une formule, il se passe des choses inattendues, à savoir que certaines valeurs sont entachées d'une erreur de l'ordre de 1 E-12, ce n'est pas beaucoup mais cela me gêne dans un test qui suit pour l'affichage.
Le fichier ci-joint (anonyme, celui-là ;-)
1- Sur le 1° onglet "Problème Excel" vous voyez que le problème arrive aux lignes 23,24, 25, 30, 44, 47 et 48
2- Sur le 2° onglet "Test" je reprends les mêmes formules en les retapant et c'est la même chose aux mêmes lignes. Je reprends les mêmes données que précédemment.
3- Sur le 3° onglet "Test (2)" les mêmes données conduisent au même problème MAIS si je créé des données aléatoire arrondies à 2 chiffres après la virgule le problème disparait ! Essayez, copiez-coller -valeurs de la colonne G dans les colonnes B puis C ... il n'y a plus de problème !
4- Je sais qu'Excel calcule avec 14 chiffres significatifs ... le problème ne viendrait-il pas de là ? Je n'ai jamais vu cela auparavant et cela ne se produit que pour certaines lignes ! la fonction "ESTVIDE" ne semble pas y être pour quelque chose.

7- j'ai arrêté mon PC et au redémarrage j'ai le même problème.
8- Je suis sur Windows 7 Edition Familiale Premium ‎(X64)‎ Service Pack 1
9- le processeur est : AMD C-60 Cadencé à 1 GHz
Regarde la pièce jointe 14-11-19_Problème Excel.xlsx

Ha j'ai un souci avec le site qui me déconnecte et je perds à chaque fois mon message !

Merci de votre temps pour me guider !
A bientôt et bien cordialement
H2H2

 

[Dranreb]

Re : Erreur arrondi Excel

Bonsoir.
Depuis longtemps je sais que pour tester l'égalité pratique de 2 nombres flottants obtenu par des voies différentes, il faut en réalité tester si la valeur absolue de leur différence est inférieure à un seuil très petit. Cela vient de ce que toute division par un nombre qui n'est pas une puissance de 2 perd des bits en numération binaire, tout comme toute division par un nombre qui n'est pas une puissance de 2, 5 ou 10 perd des chiffres en numération décimale. La représentation binaire d'un nombre ayant ne serait-ce qu'une décimale autre que 5 nécessiterait un nombre infini de bits et sa représentation interne est donc imparfaite. Tout comme en décimal une division par 3 nécessiterait une infinité de 3.

 

[TooFatBoy]

Re : Erreur arrondi Excel

Effectivement, je vois bien le problème chez moi aussi (Windows 7 Pro 64 bits + Excel 2013 64 bits)

Lorsque j'étire une formule, il se passe des choses inattendues, à savoir que certaines valeurs sont entachées d'une erreur de l'ordre de 1 E-12, ce n'est pas beaucoup mais cela me gêne dans un test qui suit pour l'affichage.

Pour une gestion de compte courant ça peut éventuellement ne pas être gênant, mais si tu gères des centaines voire des milliers d'actions par exemple ça risque d'être plus problématique...

Apparamment une solution serait de faire un arrondi pour chaque ligne :
=ARRONDI(F5+E6-D6;2)

Ha j'ai un souci avec le site qui me déconnecte et je perds à chaque fois mon message !

Le forum enregistre les brouillons de façon régulière et fréquente.
Tu ne retrouves pas ton bout de message dans tes brouillons ?

 

[H2H2]

Re : Erreur arrondi Excel

Merci Dranreb pour ta rapidité !
j'ai bien compris ton approche avec la représentation de snombres en informatique mais "pourquoi à ces lignes et pas à d'autres ? "
Je continue à chercher ...
@+ H2H2
Bien EXCELlement.

 

[H2H2]

Re : Erreur arrondi Excel

Bonjour Marcel,
merci pour ton idée de correction mais je cherche à savoir pourquoi ...
Pour les sauvegardes par le serveur ... je me suis fait avoir 2 fois, tout n'est pas sauvegardé !
Enfin EXCELlente journée à toi !
H2H2

 

[TooFatBoy]

Re : Erreur arrondi Excel

merci pour ton idée de correction mais je cherche à savoir pourquoi ...

Les erreurs d'approximation du codage des chiffres à virgule dans Excel est un problème connu puisqu'il y a quelques années quand je cherchais des informations sur ce problème je retombais tout le temps sur le même exemple (sur des pages web différentes) qui montrait que dans une suite de calculs tout d'un coup la valeur 0 (zéro) n'était plus égale à 0 (zéro), si je me rappelle bien.

Pour l'explication, je pense Dranreb a tout dit.
Et pour la solution, à part utiliser systématiquement l'ARRONDI pour recaler le résultat sur la valeur "exacte", je ne vois pas trop.


Bonne soirée

 

[H2H2]

Re : Erreur arrondi Excel

Merci Marcel32,
je vois bien le problème MAIS dans mon cas il n'y a aucune division qui puisse faire apparaître des chiffres avec des décimale éloignées ... je n'avais jamais vu cela dans les exemples que j'ai donné à mes élèves, c'était avec Excel 97 ou 2003 il est vrai ...
Comme tu le vois, je ne suis pas satisfait et je vais continuer à creuser ce point.
Merci et bonne fin de soirée !

 

[Dranreb]

Re : Erreur arrondi Excel

Il y a des divisions implicites à partir du moment où vous spécifiez des décimales. Sauf si elles spécifient des 1/2, /4, /8 etc. elles entrainent des représentations internes inexactes. 12,25 est représenté exactement, 12,2 ne l'est pas.

Sub test()
MsgBox 1225 - 12.25 * 100
MsgBox 1220 - 12.2 * 100
End Sub

 

[TooFatBoy]

Re : Erreur arrondi Excel

je vois bien le problème MAIS dans mon cas il n'y a aucune division qui puisse faire apparaître des chiffres avec des décimale éloignées ...

Je n'ai pas parlé de "division dans les calculs de la feuille", mais de "chiffres à virgule" car je ne sais pas comment les µP représentent ces nombres en interne et donc quelle approximation cela peut engendrer.

Ceci dit, le problème ne vient peut-être pas exactement de là car avec les colonnes de nombres aléatoires le problème disparait.
Peut-être vient-ce tout simplement de la façon de calculer utilisée par Excel lui-même (un genre d'optimisation qui introduirait un bug) ?...

 

[H2H2]

Re : Erreur arrondi Excel

Bonsoir Dranreb,
on avance, on avance, ton dernier post me paraît intéressant, c'est sûrement là le problème.
En cherchant à la fin du forum j'ai trouvé un sujet :

Erreur d'arrondi
Par defluc dans le forum Forum Excel
Réponses: 7
Dernier post: 13/07/2006, 09h12

et dans une réponse il est fait allusion à :
"Menu Outils / Option / Calcul / cocher : Calcul avec la précicion au format affiché" qui serait plus simple que "=ARRONDI(...;2).
Mais je ne sais pas où ce paramétrage a atterri dans la succession des versions Excel, ce devait être du 2003, j'ai du 2010 et le 2013 est d'actualité. Il faut que je retrouve une ancienne version d'Excel pour voir si le problème y était aussi ... à suivre

Bien cordialement,

@+ H2H2

 

[TooFatBoy]

Re : Erreur arrondi Excel

dans une réponse il est fait allusion à :
"Menu Outils / Option / Calcul / cocher : Calcul avec la précicion au format affiché" qui serait plus simple que "=ARRONDI(...;2).
Mais je ne sais pas où ce paramétrage a atterri dans la succession des versions Excel, ce devait être du 2003, j'ai du 2010 et le 2013 est d'actualité. Il faut que je retrouve une ancienne version d'Excel pour voir si le problème y était aussi ... à suivre

Dans 2013 ça semble être dans "Fichier \ Options \ Options avancées \ Lors du calcul de ce classeur".
C'est sûrement très proche de ça dans 2010.

 

[GGPS58]

Re : Erreur arrondi Excel

OK merci, j'essaie cela asap.