[XL-2007] Erreur de calculs dans Excel [Résolu]

[GOLDINGMAROC]

*BONJOUR*,
je suis entrain de développer un logiciel de calcul scientifique en VBA sous EXCEL2007 mais j'ai rencontré un problème
majeur qui risque de mettre en cause tous les logiciels développés en VBA. J'essai de résumé se problème à travers
ce qui suit:
1 - Créant la fonction personnalisée suivante en VBA sous EXCEL

Code :

1
2
3
4
5
6
7
8
9
Function TOTO(x) As Variant					
Dim alpha As Double					
Dim i As Integer					
alpha = 0					
For i = 1 To 20					
 alpha = alpha + x / 20					
Next i					
If alpha = x Then TOTO = alpha Else TOTO = "Faux"					
End Function

Normalement à l'exécution de cette fonction alpha devra être égale à x et par conséquent TOTO=alpha
Malheureusement le résultat renvoyer par TOTO est Faux ce qui n'est pas normal
2 - Recherche de la différence entre alpha et x
En recherchant la différence entre alpha et x en modifiant la fonction personalisée comme suit :

Code :

1
2
3
4
5
6
7
8
9
Function TOTO(x) As Variant					
Dim alpha As Variant					
Dim i As Integer					
alpha = 0					
For i = 1 To 20					
 alpha = alpha + x / 20					
Next i					
If alpha = x Then TOTO = alpha Else TOTO = x - alpha					
End Function

TOTO nous renvoi la valeur suivante : -3.552E-15
D'où vient alors cette différence c'est ici la question????

3 - modification du processus de calcul de la fonction personnalisée
En modifiant le processus de calcul de la fonction TOTO comme suit :

Code :

1
2
3
4
5
6
7
8
Function TOTO(x) As Variant					
Dim alpha As Variant					
Dim i As Integer					
For i = 1 To 21					
 alpha = (i - 1) * x / 20					
Next i					
If alpha = x Then TOTO = alpha Else TOTO = "Faux"					
End Function

La fonction s'exécute normalement et TOTO prend la valeur alpha qui est égal éffectivement à x
De même en peut modifier le processus de calcul d'une autre manière comme suit:

Code :

1
2
3
4
5
6
7
8
9
Function TOTO(x) As Variant					
Dim alpha As Variant					
Dim i As Integer					
alpha = 0					
For i = 1 To 20					
 alpha = alpha + x / 20					
Next i					
If WorksheetFunction.RoundDown(alpha - x, 10) = 0 Then TOTO = alpha Else TOTO = "Faux"					
End Function

La plus part des logiciels ou programmes développés en VBA utilisent des tests d'égalité ou d'inégalité
entre deux variables numériques. Si ce test ne s'exécute pas correctement comme en la vue ci dessus
tous les logiciels développés en VBA seront mis en cause. Que faire alors ? C'est un problème de fond

[loufab]

Bonjour,

Code :

If Val(alpha) = val(x) then

ça résoud le problème.

C'est le problème des approximations.

Cordialement,

A noter également que VB n'a pas vocation à faire du calcul scientifique.

[GOLDINGMAROC]

Envoyé par loufab

A noter également que VB n'a pas vocation à faire du calcul scientifique.

Je vous remercie pour le tuyau que tu ma donner. Mais j’ai une précision à vous demander :
Quelle est la différence si j’utilise If Val(alpha)=val(x) au lieu de If WorksheetFunction.RoundDown(alpha - x, 10) = 0. Combien de chiffres après la virgule prend l’instruction Val( ) pour comparer alpha à x ?
Aussi, je ne suis pas d’accord avec toi sur votre opinion où VB n’a pas vocation à faire du calcul scientifique pour les raisons suivantes :
- Qu’est ce qu’un calcul scientifique ? : tout simplement c’est un procéder qui utilise les opérateurs + , - , / , * , ect… pour aboutir à une valeur alphanumérique reconnue sous le nom de résultat. Le langage VB mais à la disposition de l’utilisateur tout un package d’instructions de calcul scientifique tel que le calcul matriciel, fonctions logarithmiques, fonction exponentiels, fonctions statistiques, fonction de la logique mathématique etc… Ce package d’instructions ne fait qu’utiliser les opérateurs sus cités pour renvoyer un résultat fiable c’est donc bien un calcul scientifique.
- Si VB est un langage de programmation objets il ne faut pas oublier que derrière ces objets il y a un staff de procédures qui s’exécutent en utilisant les opérateurs de calcul scientifique et pas mal de choses pour renvoyer l’objet attendu. Par exemple si vous comparer deux variables de type objet if objet1=objet2 où de type numérique if valeur1 = valeur2 vous avez utilisé l’opérateur = qui n’est autre que l’opérateur de source purement scientifique.
Le problème que j’ai mis en discussion n’a rien n’avoir avec le calcul scientifique mais plutôt et c’est là le problématique la fiabilité du résultat attendu.
Maintenant, supposant que votre opinion est VRAIE et laissant le langage VB de coté. Revenant à EXCEL : c’est bien outil de calcul scientifique du moment où il mais à notre disposition tout un package de fonctions mathématiques avec lesquelles ont peut créer une variété de feuille de calcul scientifique.
Alors Ouvrant un classeur EXCEL : (voir classeur TOTO en pièce jointe à la présente note de réponse pour discussion)
Saisissant les valeurs numériques de 0 à 3 par pas de 0.15 dans la colonne C de la cellule C4 à C24 je dis bien saisissant se sont des valeurs d’entrée c.à.d. les données du problème.
Dans la cellule I4 saisissant la valeur -16.5 dans la cellule I5 introduisant la formule suivante =I4+0.15 pour obtenir les formules des cellules I6 à I24 faite glissé la formule de la cellule I5 jusqu’au I24
Les formules des colonnes H, G, F, E et D depuis la ligne 4 jusqu‘au la ligne 24 s’obtiennent comme suit :
- La colonne H = la colonne I+4.5
- La colonne G = la colonne H+1.5
- La colonne F = la colonne G+4.5
- La colonne E = la colonne F+4.5
- La colonne D = la colonne E+1.5
Maintenant faisant le test d’égalité entre les valeurs des colonnes C et D et le test de la différence éventuellement.
Je vous renvoi au classeur ci-joint pour de plus amples détails.
La conclusion : on a fait un calcul scientifique moyennant un outil informatique de calcul scientifique en utilisant des valeurs numériques à seulement deux chiffres après la virgule; le problème persiste
Quelle est la source en langage informatique proprement dit à l’origine de cette erreur de calcul ?
En utilisant seulement des valeurs numériques à deux chiffres après la virgule EXCEL fait des calculs en rajoutant des epsilons pourquoi ? Comment peut ont justifié le surplus des epsilons dans les calculs ?
Cordialement

[loufab]

Bonjour,

Il ne faut pas tout mélanger. Excel est a VBA ce que le Saucisson est au Pain. Juste un accompagnant.

La règle qui veut que le langage qui a servi à écrire VBA permet le calcul sc. donc VBA le permet est fausse. C'est de la pure logique.

si vous comparer deux variables de type objet if objet1=objet2

Apparement tu n'es pas très familiarisé avec la manipulation d'objets.

Val vs. Rounddown :
Val est une instruction purement VBA donc la méthode est applicable à l'ensemble des produits utilisant ce langage sans chargement de bibliothèque. Il y a surement un gain en temps machine. Dans l'exemple c'est purement anecdotique.

Pour répondre à toutes les questions que tu te poses sur les epsi. il te faut te renseigner sur les spécificités du type Double. Connaitre les types natifs c'est la base de la programmation.

Cordialement,

[GOLDINGMAROC]

Envoyé par loufab

Bonjour,

Code :

If Val(alpha) = val(x) then

ça résoud le problème.

C'est le problème des approximations.

Cordialement,

Bonjour,
j'ai testé l'instruction

Code :

Val( )

elle ne fait que comparer deux variables numériques à 14 chiffres après la virgule c'est juste le seuil inférieur de l'erreur que commit EXCEL et VB. C'est une sorte d'équivalence de l'instruction

Code :

WorksheetFunction.RoundDown(number, 14)

donc un cas particulier de cette dernière. Et je ne vois pas quelle est sont utilité en VB du moment où c'est un cas particulier d'un cas général. Elle ne fait donc que camouflet l'erreur spécifique qui constitue le point fort de notre discussion.

[mercatog]

Autrement
Au lieu de tester une égalité sur des doubles

Code :

If X = Y Then

, mettre une inégalité avec choix de l'approximation

Code :

1
2
Epsilon = 10 ^ -14 'par exemple
If Abs(X-Y)<Epsilon

[GOLDINGMAROC]

BONSOIR,
Il ne s'agit pas d'un problème de test d'égalité sur des doubles, même si les variables X et Y sont de type single ou variant le problème persiste.
La notion de double ou single d'une variable de type numérique est identique à celle quant utilisait en langage FORTRAN. Pourquoi en langage FORTRAN ont n'avait pas de problème avec les doubles et singles. Cette notion de double et single a été introduite à l'époque dans un objectif de gain de l'espace mémoire qui est dépassé actuellement (les ordinateurs de la nouvelle génération tournent avec 4 processeurs et des multitâches).
En langage Pascal ou Turbo Pascal plus évoluer que le Fortran la notion des doubles et single a été supprimée et remplacée par du type réelle.
je vais essayé d'exécuter le code avec le langage Turbo Pascal pour voir est ce un problème de langage où un problème lié au système.
Maintenant, même si on arrive à résoudre le problème du teste d'égalité des deux variables X et Y par votre astuce ou par l'astuce de monsieur loufab ou autres rien ne nous garantie ce qui se passe à l'intérieur des instructions qui se trouvent entre le IF et END IF du moment où l'une des deux variables X et Y retient une valeur erronée. A SUIVRE
Cordialement

[mercatog]

Je te propose un premier lien qui permettrait de distinguer chaque type de donnée (notamment ceux à virgule flottante et les arrondis qui en découlent)
http://msdn.microsoft.com/fr-fr/libr...=vs.80%29.aspx

Et fais une recherche sur les opérations sur les nombres à virgule flottante.
http://perso.ens-lyon.fr/jean-michel...CoursVF-4p.pdf

rien ne nous garantie ce qui se passe à l'intérieur des instructions qui se trouvent entre le IF et END IF du moment où l'une des deux variables X et Y retient une valeur erronée

C'est pas une valeur erronée mais une valeur approchée.
C'est comme si tu veux matérialiser (physiquement) un nombre réel avec une approximation infinie.

[Ormonth]

Envoyé par mercatog

Et fais une recherche sur les opérations sur les nombres à virgule flottante.
http://perso.ens-lyon.fr/jean-michel...CoursVF-4p.pdf

-

[GOLDINGMAROC]

C'est pas une valeur erronée mais une valeur approchée.

Soit x un nombre réel appartenant à R peut on considérer que x + Epsilon comme valeur approchée de x ????

[mercatog]

Si x appartient à R, il sera représenté par x* avec une précision Epsilon. C'est à dire |x-x*|<=Epsilon

Pi en réalité 3,141592653589793238462.. Pi sera représenté par 3.141592653589790000 0000

[GOLDINGMAROC]

Bonjour,
OUI votre raisonnement est tout à fait logique quant à la représentativité de x par x* mais qu'en est il du nouveau né |x-x*|? normalement il devra être représenté par un ZERO et par conséquent l'instruction

Code :

IF X=Y Then

reçoit la valeur True ce qui n'est pas le cas.
A notre niveau nous les humains on n'a pas de problème avec les nombres le problème est au niveau de la machine. Il faut rectifier cette représentativité de |x-x*| au niveau de la machine.
En fin, je vous pris de bien vouloir consulté mon nouveau sujet encours de préparation sur la fonction

Code :

Int(x)

qui revoie la partie entière d'un nombre tel que définie par VB un problématique de classe.

[mercatog]

Relisez le document mis plus haut concernant les nombres à virgule flottante.

[GOLDINGMAROC]

Envoyé par mercatog

Relisez le document mis plus haut concernant les nombres à virgule flottante.

BONJOUR,
c'est très intéressant comme document je vous remercie infiniment

Envoyé par loufab

Bonjour,

Code :

If Val(alpha) = val(x) then

ça résoud le problème.

C'est le problème des approximations.

Cordialement,

A noter également que VB n'a pas vocation à faire du calcul scientifique.

Bonjour,
j'ai utilisé votre proposition relative à instruction

Code :

Val()

mais malheureusement je me suit confronté avec un autre problème comme suit :

Code :

If Val(alpha) = val(x) then

est Vraie seulement pour les 290 itérations de l'instruction For :
[code]

Code :

1
2
3
4
5
6
7
8
9
10
Function TOTO(x As Double) As Variant
Dim alpha As Double
Dim i, m As Integer
m = 290
alpha = 0
For i = 1 To m
 alpha = alpha + x / m
Next i
If Val(alpha) = Val(x) Then TOTO = "Vraie" Else TOTO = "Faux"
End Function

au delà de 290 itérations TOTO="Faux" c'est normale puisque l'erreur est cumulative et devient au delà de 290 itérations supérieure à 10-14
Par contre on peut aller jusqu'au seuil de l'instruction For qu'est 30000 itérations en modifiant la fonction TOTO comme suit :
[code]

Code :

1
2
3
4
5
6
7
8
9
10
Function TOTO(x As Double) As Variant
Dim alpha As Double
Dim i, m As Integer
m = 30000
alpha = 0
For i = 1 To m
 alpha = alpha + x / m
Next i
If WorksheetFunction.RoundDown(alpha - x, 10) = 0 Then TOTO = "Vraie" Else TOTO = "Faux"
End Function

Que pensé vous ?
Cordialement

Envoyé par mercatog

Si x appartient à R, il sera représenté par x* avec une précision Epsilon. C'est à dire |x-x*|<=Epsilon

Pi en réalité 3,141592653589793238462.. Pi sera représenté par 3.141592653589790000 0000

Bonjour,
Quelle valeur alors peut on donnée à la précision Epsilon : 10 ^ -14 ou une valeur supérieure "l'erreur est cumulative" ? Je vous prie de bien vouloir consulter ma citation que j'ai envoyé ce jour à Monsieur loufab

[mercatog]

Envoyé par GOLDINGMAROC

Bonjour,
Quelle valeur alors peut on donnée à la précision Epsilon : 10 ^ -14 ou une valeur supérieure "l'erreur est cumulative" ? Je vous prie de bien vouloir consulter ma citation que j'ai envoyé ce jour à Monsieur loufab

Que dire encore, c'est un problème d'approximation

Si x1 approxime x à ε près, et y1 approxime y à η près, alors x1 + y1 approxime x + y à ε + η près. C'est une évidence géométrique : x1 + y1 est au maximum à la distance ε + η de x + y.

Beaucoup de littérature sur ce sujet et notamment ce petit extrait où la petite introduction est plus que parlante
http://jfpc2010.greyc.fr/articles/26.pdf

[GOLDINGMAROC]

Bonjour,
je vous remercie bcp sur le lien de l'article en question

Pour trancher et pouvoir progresser dans mon travail de développement. J'ai développé une fonction personnalisée à l’intérieure de mon programme qui me permet de comparer deux variables x et y de type double précision avec une erreur d'approximation Epsilon = 10^-10 largement suffisante pour mon cas.
Je vous soumis ci après le code de cette fonction en vous prions de bien vouloir que vous me donner vos remarques et suggestions.
Voici le code :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Public Function Précision_Approche(x As Double, y As Double, test As Variant) As Variant
Dim Epsilon As Double
Epsilon = 10 ^ -10
If test = "=" Then 'Pour tester si x = y
 If Abs(x - y) <= Epsilon Then Précision_Approche = "Vraie"
End If
If test = "<" Then 'Pour tester si x < y
 If x - y < 0 And Abs(x - y) > Epsilon Then Précision_Approche = "Vraie"
End If
If test = ">" Then 'Pour tester si x > y
 If x - y > 0 And Abs(x - y) > Epsilon Then Précision_Approche = "Vraie"
End If
If test = "<=" Then 'Pour tester si x <= y
 If (x - y < 0 And Abs(x - y) > Epsilon) Or Abs(x - y) <= Epsilon Then Précision_Approche = "Vraie"
End If
If test = ">=" Then 'Pour tester si x >= y
 If (x - y > 0 And Abs(x - y) > Epsilon) Or Abs(x - y) <= Epsilon Then Précision_Approche = "Vraie"
End If
End Function

Exemple d'application :

Code :

1
2
3
4
5
6
7
8
9
10
Public Function TOTO(x As Double) As Variant
Dim alpha As Double
Dim i, m As Integer
m = 32767
alpha = 0
For i = 1 To m
 alpha = alpha + x / m
Next i
If Précision_Approche(x, alpha, "=") = "Vraie" Then TOTO = "Vraie" Else TOTO = "Faux"
End Function

Cordialement

[mercatog]

Je ne comprends pas le cas >= et <= !!
Pour la fonction, elle peut être écrite comme ceci et en retour tu as un booléen

Code :

1
2
3
4
5
6
7
8
9
Public Function Precision_Approche(ByVal x As Double, ByVal y As Double, ByVal Test As String) As Boolean
Const Epsilon As Double = 10 ^ -10
 
Select Case Test
    Case "=": Precision_Approche = Abs(x - y) <= Epsilon
    Case "<": Precision_Approche = y - x > Epsilon
    Case ">": Precision_Approche = Precision_Approche(y, x, "<")
End Select
End Function

[GOLDINGMAROC]

Bonjour,
Merci pour votre suggestion efficace. >= et <= ça veut dire respectivement si x est inférieure ou égale à y et si x est supérieure ou égale à y
Aussi, on peut suggérer le code comme suit :

Code :

1
2
3
4
5
6
7
Public Function Precision_Approche(ByVal x As Double, ByVal y As Double, ByVal Test As String, Const Epsilon as double) As Boolean
Select Case Test
    Case "=": Precision_Approche = Abs(x - y) <= Epsilon
    Case "<": Precision_Approche = y - x > Epsilon
    Case ">": Precision_Approche = Precision_Approche(y, x, "<",Epsilon)
End Select
End Function

[/QUOTE]
En laissant ainsi au développeur de choisir la valeur de Epsilon suivant le cas juger par lui même.
Cordialement

[mercatog]

On peut pas écrire la fonction comme tu l'as écrite.

Sinon, une proposition est de mettre Epsilon en étant paramètre optionnel

Code :

1
2
3
4
5
6
7
8
Public Function Precision_Approche(ByVal x As Double, ByVal y As Double, ByVal Test As String, Optional ByVal Epsilon As Double = 10 ^ -10) As Boolean
 
Select Case Test
    Case "=": Precision_Approche = Abs(x - y) <= Epsilon
    Case "<": Precision_Approche = y - x > Epsilon
    Case ">": Precision_Approche = x - y > Epsilon
End Select
End Function

Pour le 4ème paramètre Epsilon:
Precision_Approche(123, 122.999999999, "="), Prend l'Epsilon par défaut (10^-10)
Precision_Approche(123, 122.999999999, "=", 0.00000001), Prend l'Epsilon imposé

[GOLDINGMAROC]

Bonjour,
Maintenant je n'ai plus de soucis pour les poussières à virgules flottantes. Mon problème est résolut.
Cordialement

Bonjour,
pour ne pas alourdir l'écriture du code on peut généraliser comme suit :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 Public Function Precision_Approche(ByVal x As Double, ByVal y As Double, ByVal Test As String, Optional ByVal Epsilon As Double = 10 ^ -10) As Boolean
Select Case Test
    Case "=": Precision_Approche = Abs(x - y) <= Epsilon
    Case "<": Precision_Approche = y - x > Epsilon
    Case ">": Precision_Approche = x - y > Epsilon
    Case ">="
           Precision_Approche = x - y > Epsilon
           If Precision_Approche = False Then Precision_Approche = Abs(x - y) <= Epsilon
    Case "<="
           Precision_Approche = y - x > Epsilon
           If Precision_Approche = False Then Precision_Approche = Abs(x - y) <= Epsilon
    Case "<>"
           Precision_Approche = y - x > Epsilon
           If Precision_Approche = False Then Precision_Approche = x - y > Epsilon
End Select
End Function

pas de remarque j’espère
Cordialement