Approximation d'une courbe "particulière" sous Excel par une droite affine

SERIEUXETCOOL

XLDnaute Occasionnel
Bonjour à tous, bonjour le Forum,

Bien bien, me voici une nouvelle fois ici pour un nouveau besoin. Je donnerais un peu plus loin des éléments de contexte ainsi que les besoins qui animent ma venue ici.

Cette fois-ci, je pense que ma demande est un peu plus complexe que d'habitude. Je pense également, qu'elle s'adressera plus particulièrement à des développeurs expérimentés (en tout cas bien plus expérimentés que moi ça c'est sur^^). Je rappel que je suis débutant sous Vba mais ayant acquis de bonnes bases tout de même. Bien évidement tout le monde peut participer et je prends tous les conseils !

Bien maintenant entrons un peu plus dans le sujet... Je vous conseil de télécharger le fichier Excel (2003) que j'ai mis en en téléchargement en bas de page pour que vous compreniez beaucoup mieux au fur et à mesure de la lecture ;)

Je travail sous Excel 2007, et je cherche à développer une Macro. Dans mon fichier Excel et dans la première feuille intitulée "DONNÉES" il y a un graphique. Ce graphique est tracé grâce à deux colonnes. Une première colonne pour les abscisses et une autre pour les ordonnées. En ce qui concerne les valeurs qui se trouvent dans les colonnes, on va dire qu'elles sont acquises de manière expérimentale et donc qu'il n'y a pas de formule pour trouver ces points. Pas de formule...donc pas d'équation de courbe non plus !
De plus comme vous le remarquerez très bien, cette courbe ne respecte pas l'unicité de solution. C'est à dire que pour une valeur d'abscisse X, il n'existe pas une et une seule solution. C'est bien la un des problèmes qui me pousse à développer une macro évoluée.

Maintenant que j'ai dit ça, je vais pouvoir exprimer la problématique. Voici ce que je cherche à faire sur ce graphique.

Dans une tierce colonne, il y a des valeurs qui représentent des abscisses. Et mon but va être d'associer les bonnes ordonnées qui correspondent à la colonne des abscisses par lecture graphique. Ce travail est fastidieux car il peut y avoir une cinquantaine de valeurs à lire. De plus, pour chaque abscisse il peut y avoir de multiples intersections et donc qu'il faut à chaque fois lire plusieurs ordonnées pour une même abscisse. J'ai mis en évidence ce problème sur la feuille numéro deux du fichier Excel intitulée "EXEMPLE". On y voit la multi-intersection d'une courbe par une unique valeur d'abscisse.
J'ajouterais que pour ne rien faciliter, mon graphique se trouve sur une feuille Excel, et ma colonne qui contient les valeurs des abscisses en attente de réception d'ordonnées se trouve sur une autre feuille. Du coup il faut sans arrêt jongler entre les feuilles Excel. Pas cool !

Du coup je me suis dit qu'il serait sans doute jouable via une macro Excel de faire en sorte de venir lire les valeurs sur le graphique à la place de l'utilisateur. Malheureusement, ce sont des données expérimentales et il n'y a pas d'équation de courbe qui tienne pour éventuellement calculer les valeurs d'ordonnées en fonction des abscisses. De plus, apparemment Excel ne gère pas l'intersection de courbes sur un graphique pour remonter à la cordonnée d’intersection. Pour lui, les courbes se superposent mais il ne détecte pas le passage de l'une sur l'autre. Vraiment dommage ! A moins que je me trompe ???

Du coup la seule solution que j'ai trouvé actuellement est de travailler par petits intervalles de valeurs d'abscisses et de faire une approximation linéaire par une droite affine de la forme "Y = a*X + b". Du coup pour une valeur d'abscisse données, on va chercher à encadrer cette abscisse par la première immédiatement plus petite, et la première immédiatement plus grande que la valeur d'abscisse désiré. De cette façon, on encadre l'abscisse voulu par deux autres abscisses dont on connait les ordonnées respectives. Il ne reste plus qu'a faire l’approximation linéaire et à en déduire l'ordonnée qui correspond à la valeur d’abscisse qui était demandée initialement.
Pour que sa soit plus clair pour tous, j'ai mis en feuille trois intitulée "MODÉLISATION DU PROBLÈME" la visualisation de ce que je viens de dire. Vous verrez ce n'est pas compliqué quand on voit l'exemple.

Évidement, comme pour une abscisse il peut y avoir plusieurs intersections possible, il ne faut pas oublier de boucler sur toutes les valeurs du graphique pour mettre en évidence toutes les possibilités possibles. Ce sont des choses très simples que je demande mais qui mis bout à bout deviennent vites complexes (pour moi en tout cas !)

Enfin, il y aura également une partie "visuelle" à traiter. En effet, comme il y a plusieurs solutions pour une abscisse l'idée serait de faire afficher dans une fenêtre toutes les solutions trouvées et de demander à l'utilisateur laquelle il désire garder. Que la solution choisie soit ensuite copiée dans la cellule en face de l’abscisse initiale. Et puis que le programme recommence avec une autre abscisse qui se trouve dans la cellule des abscisses suivantes...
Pour info, les valeurs d'abscisses seront étudiées dans l'intervalle [0;1] UNIQUEMENT et les valeurs d'ordonnées seront étudiées dans l'intervalle [0;500E-9] UNIQUEMENT. Les autres valeurs ne m'intéressement pas.

C'est pourquoi je viens faire appel à votre aide. Cette demande n'est pour moi pas vraiment prioritaire car ce n'est pas indispensable pour la suite de mes idées. Néanmoins, maintenant que j'ai eu l'idée d'aller plus loin et de proposer une solution envisageable ; et bien je suis vraiment curieux de voir ce que sa peut donner une fois codé. Je vais tout faire pour tenter l'expérience mais je n'ai pas le temps à me casser la tête sur ce code. C'est dommage mais c'est comme sa. Voila pourquoi je vous demande un petit coup de main pour essayer d'avancer plus vite.

Je reste disponible pour toutes vos questions que vous pourriez avoir, et évidement je reste dans le coin du mieux que je peux pour aider moi aussi.

Voila c'est finit pour le moment. J'avais averti, c'est un peu plus costaud cette fois-ci^^

Cordialement,

André
 

Pièces jointes

  • MODELISATION DU PROBLEME APPROXIMATION.xls
    59.5 KB · Affichages: 120
  • MODELISATION DU PROBLEME APPROXIMATION.xls
    59.5 KB · Affichages: 131
  • MODELISATION DU PROBLEME APPROXIMATION.xls
    59.5 KB · Affichages: 131

tbft

XLDnaute Accro
Re : Approximation d'une courbe "particulière" sous Excel par une droite affine

Bonjour

Je suis en vacance...Mais de là à lire un romans en 4 tomes de 600pages...
Serait-il possible d'avoir la version résumé???

sinon il me reste un vieux reste de math.
il faut minimiser l'écart quadratique entre les points de la courbe bleu et les points de la courbe d'interpolation....
 
Dernière édition:

SERIEUXETCOOL

XLDnaute Occasionnel
Re : Approximation d'une courbe "particulière" sous Excel par une droite affine

Bonsoir tbft,

Oui c'est un peu longuet je l'avoue. Mais il faut bien tout ça pour essayer d'être clair. Par contre ce qui est possible de faire pour faciliter la tache, c'est d'avancer petit à petit.

Je vais poser la première pierre de ce que je souhaite faire (mais que je n'y arrive pas !).
On devrait y arriver ainsi.

Voila la chose :

J'ai une colonne Excel qui contient des données numériques. Je choisis une valeur arbitraire pour une variable. On va nommer cette variable X et je lui attribue la valeur que je désire (mettons 10 pourquoi pas).

Quel code utiliser pour parcourir TOUTES les cellules de ma colonne et identifier quelle cellule est la plus proche par valeur inférieure et celle qui est la plus proche par valeur supérieure par rapport à la variable X ?

Il devrait y avoir un compteur pour parcourir toutes les cellules, il devrait également y avoir la fonction min qui devra être utilisée pour identifier la plus petite différence par soustraction.

Voila une version raccourcie pour commencer le travail^^

Si tu as le courage tbft je suis preneur !

André ;)
 
Dernière édition:

tbft

XLDnaute Accro
Re : Approximation d'une courbe "particulière" sous Excel par une droite affine

Re...

Je suis de retour
As tu essayé la fonction =DROITEREG()
Par contre elle renvoi un tableau de deux chiffres.
Pense à sélectionner deux cellules, de taper la formule et de valider an tapant ctrl+shift+Entrées...
 

eriiic

XLDnaute Barbatruc
Re : Approximation d'une courbe "particulière" sous Excel par une droite affine

Re,

Une proposition avec une fonction personnalisée matricielle.
=interpolation(datasXY, valeur)

ex:
sélectionner la plage EXEMPLE!C36:G36,
saisir : =interpolation(DONNÉES!$B$4:$C$131;B36)
formule matricielle à valider avec shift+ctrl+entrée

Je te laisse contrôler si les calculs te paraissent bons.

comme il y a plusieurs solutions pour une abscisse l'idée serait de faire afficher dans une fenêtre toutes les solutions trouvées et de demander à l'utilisateur laquelle il désire garder. Que la solution choisie soit ensuite copiée dans la cellule en face de l’abscisse initiale.
Là je n'ai pas compris où était la cellule en face de l’abscisse initiale

eric
 

Pièces jointes

  • MODELISATION DU PROBLEME APPROXIMATION.xls
    80.5 KB · Affichages: 112
  • MODELISATION DU PROBLEME APPROXIMATION.xls
    80.5 KB · Affichages: 114
  • MODELISATION DU PROBLEME APPROXIMATION.xls
    80.5 KB · Affichages: 108
Dernière édition:

SERIEUXETCOOL

XLDnaute Occasionnel
Re : Approximation d'une courbe "particulière" sous Excel par une droite affine

tbft j'ai essayé de travailler sur la fonction DROITEREG() mais je ne suis pas parvenu à l'exploiter de manière correcte.
Je doit mal m'y prendre. Et comme je ne m'en suis jamais encore servi...ben c'est pas facile^^ L'aide sur la fonction est pourtant abondante !

En tout cas je visualise bien l'idée. C'est pas mal en effet. Sa pourrait être utile pour la suite du problème...si toute fois je parviens à l'exploiter correctement.

Je poursuis...

Merci,

André
 

SERIEUXETCOOL

XLDnaute Occasionnel
Re : Approximation d'une courbe "particulière" sous Excel par une droite affine

eriiiic,

J'ai bien visualisé le fichier Excel et notamment sur les changements que tu as apporté. Malheureusement je ne suis pas sur d'avoir bien saisi comment tout cela fonctionne. Tout d'abord pour valider avec shift+ctrl+entrée ??? Jamais utilisé par le passé, et rien ne se passe quand je sélectionne la plage et que je fais la manip.
Je dois mal m'y prendre la aussi (je suis débutant !)

De plus, sur ton exemple il y a un truc que je ne comprends pas. Dans la feuille "EXEMPLE" tu as remplis le tableau des ordonnées...Mais par exemple avec l'abscisse 0,4 tu trouve 5 intersections avec la courbe bleue alors qu'il n'y en a strictement aucune ! Je suis perdu, car je ne sais pas du tout sur quoi opèrent tes calculs.

Je ne peux pas plus commenter pour le moment. Dsl.

Pour éclaircir la citation que tu mets, en fait pour une abcisse il peut y avoir plusieurs ordonnées à faire afficher. Donc en face des ordonnées que l'on cherche à trouver, il y a des cellules à remplir. Ces cellules sont sur la feuille "EXEMPLE" dans le tableau "Données à remplir". Il y est écrit "ordonnée1" etc.

Voila.

Merci pour vos éléments de réponses déjà. Je vais voir si je peux avancer avec ça. Sa me parais encore floue.

André
 

eriiic

XLDnaute Barbatruc
Re : Approximation d'une courbe "particulière" sous Excel par une droite affine

Si je regarde sur 0.4 moi je vois bien 5 intersections... (???)
Pour commencer est-ce que les valeurs que tu lis sur 0.6 te paraissent correctes ?

Voilà le code :
Code:
Option Base 1
Function interpolation(datasXY As Range, valeur As Double) As Variant
    Dim lig As Long, pente As Double, ordonnéeOri As Double, ordonnéePt As Double, tablOrd As Variant, ptr As Long
    ReDim tablOrd(10)
    ptr = 1
    For lig = 1 To datasXY.Rows.Count - 1
        If (datasXY(lig, 1) >= valeur And datasXY(lig + 1, 1) <= valeur) Or (datasXY(lig, 1) <= valeur And datasXY(lig + 1, 1) >= valeur) Then
            pente = Application.WorksheetFunction.Slope(datasXY(lig, 2).Resize(2, 1).Value, datasXY(lig, 1).Resize(2, 1).Value)
            ordonnéeOri = WorksheetFunction.Intercept(datasXY(lig, 2).Resize(2, 1).Value, datasXY(lig, 1).Resize(2, 1).Value)
            ordonnéePt = pente * valeur + ordonnéeOri
            If ordonnéePt <= 0.0000005 And ordonnéePt >= 0 Then
                tablOrd(ptr) = ordonnéePt
                ptr = ptr + 1
                If ptr > UBound(tablOrd) Then Exit For
            End If
        End If
    Next lig
    interpolation = tablOrd
End Function
Pour reprendre l'exemple du 0.6, si dans le tableau j'ai 2 valeurs successives encadrant 0.6 je fais une régression linéaire sur ces 2 pts pour évaluer l'ordonnée de 0.6.
C'est ce que j'avais compris...

Comme la fonction doit retourner plusieurs valeurs, il faut sélectionner la plage recevant les valeurs et valider par shift+ctrl+entrée (les 3 touches en même temps. Si c'est bien fait la formule s'encadre de { }

Mais d'abord le calcul est-il bon ?
Est-ce que les valeurs que tu lis sur 0.6 te paraissent correctes ?

eric

PS: ne tiens pas compte de ce que j'ai ajouté sur la feuille Données, c'était un début par formules pour débroussailler. Ca ne sert plus à rien.

edit2: j'ai compris, j'ai laissé un filtre activé (sur 0.6) sur la feuille Données, il faut l'enlever pour retrouver la courbe complète (tupeux même supprimer ces colonnes: E à .... J'ajoute le fichier corrigé
 

Pièces jointes

  • MODELISATION DU PROBLEME APPROXIMATION.xls
    80.5 KB · Affichages: 114
  • MODELISATION DU PROBLEME APPROXIMATION.xls
    80.5 KB · Affichages: 92
  • MODELISATION DU PROBLEME APPROXIMATION.xls
    80.5 KB · Affichages: 91
Dernière édition:

tbft

XLDnaute Accro
Re : Approximation d'une courbe "particulière" sous Excel par une droite affine

Bonsoir

Un petit essai
 

Pièces jointes

  • MODELISATION DU PROBLEME APPROXIMATION.xls
    66.5 KB · Affichages: 99
  • MODELISATION DU PROBLEME APPROXIMATION.xls
    66.5 KB · Affichages: 100
  • MODELISATION DU PROBLEME APPROXIMATION.xls
    66.5 KB · Affichages: 106

SERIEUXETCOOL

XLDnaute Occasionnel
Re : Approximation d'une courbe "particulière" sous Excel par une droite affine

Bonsoir eriiiic,

En effet pour une abscisse valant 0,4 on est bien censé voir apparaitre 5 intersections. Sauf que moi j'ai utilisé le fichier Excel que tu proposais...Et dans ton fichier tu as modifié la courbe que j'avais donné. Du coup, dans ton fichier Excel il n'y a pas d'intersections pour la valeur d'abscisse 0,4. C'est pour sa que je ne comprenais pas tout alors. Il fallait utiliser les valeur de ton fichier Excel avec la courbe du fichier Excel initial. Pas très intuitif quand même^^

J'ai donc vérifié les valeurs que tu propose pour 0,6. J'en trouve 2 justes et 3 fausses. Bilan : C'est pas si mal quand même ! De plus j'ajouterais que parmi les deux valeurs justes, elles sont d'une très bonne précision. C'est très encourageant pour la suite tout sa.
Quand aux 3 valeurs fausses je pense savoir le pourquoi du comment. Je ne sais pas si tu as remarqué, mais la courbe est tracée à partir de deux colonnes qui ne sont pas du tout ordonnées. Bilan il ne faut pas faire une boucle de ligne en ligne car sa n'a pas de sens si les données ne sont pas ordonnées.

En effet, pour Excel, que les données soient ordonnées ou non sa ne change rien. Il trace des points définis par des coordonnées X et Y. Il relie par la suite tous ces points. Ton code serait beaucoup plus juste (je pense) si l'on parvenait ) à ordonner la colonne des abscisses. J'en suis à ce stade personnellement. Mais je ne parviens pas à ordonnée du plus petit au plus grand car la colonne des ordonnées dépends de la colonne des abscisses. Les deux colonnes vont de pair pour former un couple de cordonnées.

Donc ci mon raisonnement est juste, les 3 valeurs fausses que ton code trouve s'expliquent par le fait que la valeur d'abscisse 0,6 est mal encadrée. Du coup tu extrapole la courbe originale par une courbe linéaire dont les deux points en entrée ne sont pas les plus précis. Et donc l'erreur qui en suit est juste aberrante. Pour moi sa ne me choque pas si ce que je dis est juste.

Faut tester pour le vérifier, mais je galère à essayer d'ordonnées ma colonne Abscisse de la plus petite valeur à la plus grande tout en faisant suivre la valeur associée des ordonnées.


Je pense en tout cas que tu es sur la bonne voie eriiiic !

tbft, je jette un oeuil à ce que tu propose. Je ferais un retour sur mon constat.

Merci à vous deux de vous pencher sur mon casse tête^^

C'est sympa.

André
 

SERIEUXETCOOL

XLDnaute Occasionnel
Re : Approximation d'une courbe "particulière" sous Excel par une droite affine

Je reviens de suite faire un autre petit retour...

Concernant tbft, j'ai eu un peu de mal à trouver ou ce cachait tes modifs mais j'ai mis la main dessus quand même^^. On joue à cache cache maintenant ??? Bref, J'ai pu testé ton exemple. J'ai trouvé comment sa marche en jouant avec. Pas très intuitif dis donc. Mais sa marche très bien en tout cas ! J'ai enfin compris comment modifier la matrice avec la combinaison de touche shift+ctrl+entrée. La encore faut se casser la tête on dirais^^. Mais bon le plus dur c'est de le faire la première fois. Maintenant je sais modifier les formules et valider les changements.
Bilan, l'utilisation de la fonction DROITEREG me semble plus que bien adaptée. Il suffirait maintenant d'encadrer correctement une valeur d'abscisse par deux couple de points et de faire DROITEREG pour en extraire la droite affine. Mais il ne faut pas oublier qu'ensuite il faudra extrapoler la droite affine pour un valeur d'abscisse souhaitée.
Chaque chose en son temps. Sa avance déjà pas mal je trouve. C'est fou je trouve.

Affaire à suivre donc...


En ce qui concerne eriiiic, ah ben je comprends mieux pourquoi la courbe n'avait plus du tout la même allure maintenant^^. C'était donc des filtres que tu avais appliqué sur la première feuille. j'avais pas percuté. Donc oui, normal que je ne "voyais" pas d'intersection pour la valeur 0,4^^

Autre chose que j'ai remarqué maintenant que tu as rectifié le fichier Excel : Pour commencer, le nombre d'intersection que ton code détecte est le bon ! Ça c'est super. De plus, pour les valeurs d'abscisses 0,1 / 0,2 et 0,3 les valeurs d'ordonnées retournées sont d'une excellente précisions dis donc ! Mieux que par lecture graphique de nos petits yeux. Je suis bluffé par ces premiers résultats dis donc. Sa me semble maintenant pertinent de passer un peu de temps à développer cette application car c'est du temps et de la précision gagnée pour la suite.

Il y a bien une chose que tu pourrais faire pour mettre en évidence l'erreur d'encadrement que je soulignais dans le post précédent. Eriiiic, te serait il possible d'aficher une "MsgBox" pour chaque couple d'ordonnée ? Je m'explique, il faudrait parvenir à voir quels sont les deux points qui te servent de référence pour tracer tes droites linéaires. De cette manière, il suffirait de voir les points que ton code utilise pour tracer les droites et de les comparer sur le graphique. Je pense qu'il serait évident que les couples utilisés par ton code ne sont pas les bons.

Solution que je propose donc : Ordonner au préalable les couples (X,Y) en fonction de la colonne Abscisse.


André
 

SERIEUXETCOOL

XLDnaute Occasionnel
Re : Approximation d'une courbe "particulière" sous Excel par une droite affine

De retour encore.

Mille excuses Eric...Mais j'ai dit des bêtises à l'égard de ton code !

J'ai commencé à me pencher activement sur ton code (tu vas trop vite pour moi^^) et il me semble pertinent bien que trop compliqué pour moi. Je ne maitrise pas toutes les lignes. Pourrais tu mettre des commentaires pour me faciliter la lecture éventuellement ?

Une fois que j'ai constaté que ce code était très bien écrit j'ai donc cherché à savoir pourquoi il n'y avait que 2 bons résultats sur 5 pour la valeur d'abscisse 0,6. Et en fait en refaisant la lecture graphique je me suis apperçu que ton code ne trouve non pas 2 bons résultats sur 5...Mais bien 5 sur 5 !

Et en plus d'une précision redoutable. Déjà que j'étais sur le cul que sa fonctionne si bien pour au moins 2 résultats sur 5. Maintenant c'est carrément 5 sur 5 ! J'ai vérifié également les autres valeurs d'abscisses du tableau et elles sont toutes justes. Et bé, sa commence très bien. Je ne sais pas ou j'avais les yeux tout à l'heure pour avoir prétendu trouver seulement 2 bonnes valeurs sur 5.

Donc excuse moi.

Ton code semble bon.

Par contre sa remet également en cause mes dires de tout à l'heure. Quand je disais qu'il fallait sans doute ordonner les valeurs. Est juste, est ce inutile, y a t'il un risque ?

Je vais quand même réfléchir sur ce point et essayer de mettre en évidence un contre exemple pour prouver qu'il faut ordonner les valeurs. Je ne sais pas pourquoi mais j'ai la sensation qu'il faut les ordonnées.

Bref bien joué Eric !
 
Dernière édition:

Discussions similaires