Fonction Perso ''Interpolation''

[Bernard de Go Mars]

Bonjour à vous tous.

Me présentant dans le forum d'accueil des nouveaux , j'ai joint aussi sec l'exposé de mon problème.
(Merci à ceux qui m'ont dèjà accueilli d'une boutade...)

Il est sans doute préférable pourtant que je vous soumette directement ce problème sur ce forum :

J'ai bâti, il y a déjà un certain temps, un tableau Excel de ''trajectographie'' des fusées. Vous dire cela est un peu une manière de me présenter.
Mon site, cité au bas de ce message en sera une autre...

Mais revenons à ce tableau de trajectographie...
Après calculs de la trajectoire du modèle choisi (dimensions de la fusée, condition météo) le calcul de la trajectoire se fait. Puis, ceci acquis, plusieurs animations montrent le vol de la fusée sous différents angles (zoom départ, téléobjectif ''de poursuite'' , zoom au passage à l'apogée), le tout en repère sol ou repère air...

En réalisant ce tableau, j'avais été très surpris de ne pas trouver, dans la liste des fonctions disponibles, une fonction ''Interpolation''.

Cette fonction m'étant nécessaire pour aller chercher, à chaque instant, Portance et Traînée de la fusée sur une courbe pré-établie, j'avais dû l'improviser dans les cellules :

Pour un X quelconque (dont on recherche une évaluation raisonable du Y, par interpolation) la fonction RechercheV détermine assez facilement le Xinférieur connu.
Ensuite, en utilisant Equiv et Index et on peut donc en déduire le Yinférieur connu, le Xsupérieur connu ainsi que le Ysupérieur connu.

L'estimation du Y quelconque est alors évidemment :

(Xquelconque - Xinférieur)* (Ysupérieur-Yinférieur) / (Xsupérieur-Xinférieur).


Cela ne présente pas de difficulté, mais ça prend de la place dans la barre de formule et ça ne facilite pas la compréhension du tableau par un éventuel lecteur...

Aussi je me suis mis en devoir récemment de créer ma propre fonction personnalisée Interpolation.
Les deux arguments en serait :
-la valeur du X quelconque (dont on recherche le Y)
-et la plage (éventuellement dollardisée)(est-ce nécessaire et possible) des deux colonnes des X et des Y connus.

Mais voilà :
lorsque j'utilise la fonctions RechercheV (avec le ''conteneur" WorksheetFunction.) et en faisant les adaptations que je crois devoir faire, je n'obtiens que le résultat #VALEUR...

Le seul (maigre) résultat dont je puis me féliciter est la création d'une fonction personnalisée IndexTest (à titre de test) qui s'écrit en Visual Basic :

Dim XInf
Function IndexTest(X, PlageCourbe As Range)
XInf = WorksheetFunction.Index(PlageCourbe, X, 1)

IndexTest = XInf
End Function

Le X en question étant bien sûr une valeur existante dans la première colonne de la PlageCourbe.
Le Xinf , innécessaire, a été introduit par moi à titre de test...

Pour des gens comme vous, me débloquer doit être un jeu d'enfant.

Accepterez-vous de le faire ?

En vous remerciant d'avance,

Bernard de Go Mars! .
Notez à la page Physique de la fusée du site Go Mars! entièrement consacré aux techno-fusées à eau , nos textes originaux (qui valent aussi bien pour les fusées à eau que les fusées .. à feu).

 

[lapix]

Re : Fonction Perso ''Interpolation''

Bonjour, et bienvenue sur ce SUPER forum

J'ai lu et relu ton exposé tres interressant et je passerais surement du temps sur ton site.
Je ne suis pas mathematicien pour 2 sous mais je veux bien te croire. Ce que je retiens, c'est que tu as deja mis ca sous forme de formule.

Pourrais tu joindre ton fichier light ou on retrouve cette formule pour voir comment on peut integrer ca en VBA ?

a te lire

 

[Bernard de Go Mars]

Re : Fonction Perso ''Interpolation''

joindre ton fichier light où on retrouve cette formule pour voir comment on peut integrer ca en VBA ?

Merci pour ta réponse rapide.
Le problème, c'est que le fichier XL n'est pas particulièrement light : les formules prenant en compte la propulsion et l'équilibre de la fusée (en particulier les efforts aérodynamiques auxquels elle est soumise) peuvent être assez compliquées. Et en plus, on les utilise sur 1000 pas ou plus (j'ignore d'ailleurs si le fait d'étendre des formules sur 1000 lignes charge beaucoup la bourrique).

Résultat : ça fait qq méga (5 ou 6, je ne sais plus).


Mais je te laisse ça où tu veux avec plaisir (et un soupçon de fierté).

La question est donc : où puis-je attacher les méga en question ?

------------------------

D'autre part, je n'ai justement pas intégré une telle formule personnalisée dans ce tableau animé : j'ai écrit les interpolations in extenso (en fait il y en a plusieurs, puisque selon qu'on monte en angle d'incidence ou qu'on descend, certaines parties de courbes ne sont pas les mêmes (il y a comme deux aiguillages sur la courbes).

Pour ce qui est de mon problème de la création de fonction perso, on peut donc travailler dans l'absolu sur un petit fichier de circonstance et en imaginant une courbe comme Y = X² (dont on chercherait à trouver les Y pour des valeurs non entières de X)...
------------------------

Amicalement,

Bernard

 

[lapix]

Re : Fonction Perso ''Interpolation''

Bon,
plus je te lis et plus je me demande. On peut tout de meme faire un essai pour voir ce qu'il en est.(la fonction ne devrait pas me poser de vrai probleme, ce qui m'interroge c'est ma comprehension fine de TON probleme)

Je te propose deux solutions :

1 : Accueil de Cjoint.com où tu vas mettre ton fichier (ca a l'avantage que je ne serais pas seul à le voir et d'autres plus aptes que moi pourraient te proposer une excel-lente solution (pas trop lente j'espere)

2 : une adresse msn : p_a_63@hotmail.fr

Je te laisse le choix meme si ma preference va au premier.
Je ne te cache pas qu'apres lecture j'aurais surement bon nombre de questions...

A te lire

 

[ODVJ]

Re : Fonction Perso ''Interpolation''

Bonsoir à tous,

une fonction générique d'interpolation :

Function interpol(monX, maplage)
xinf = Evaluate("VLookup(" & monX.Address & "," & maplage.Address & ", 1, True)")
xsup = Evaluate("INDEX(" & maplage.Address & ",1+MATCH(VLOOKUP(" & monX.Address & "," & maplage.Address & ",1,TRUE),INDEX(" & maplage.Address & ",,1),0),1)")
yinf = Evaluate("VLOOKUP(" & monX.Address & "," & maplage.Address & ",2,TRUE)")
ysup = Evaluate("VLOOKUP(INDEX(" & maplage.Address & ",1+MATCH(VLOOKUP(" & monX.Address & "," & maplage.Address & ",1,TRUE),INDEX(" & maplage.Address & ",,1),0),1)," & maplage.Address & ",2,FALSE)")
interpol = yinf + (monX - xinf) * (ysup - yinf) / (xsup - xinf)
End Function

cordialement

 

[Bernard de Go Mars]

Re : Fonction Perso ''Interpolation''

Merci pour vos conseils magistraux.

J'ai envoyé mon tableau (2,7 Mo) à Lapix. Il s'en fera peut-être l'écho...

La fonction définie par ODVJ fonctionne effectivement parfaitement.

L'inconvénient c'est que je n'arrive pas à trouver de l'aide sur les fonctions élémentaires qu'il utilise... Sont-ce des fonctions d'une strate si profonde d'Excel qu'elles soient inconnues de l'Aide et l'Explorateur d'Objets ? Y a-t-il une autre aide que je ne connais pas...
-----------------------------------------------------------------------

De plus, comme j'ai toujours derrière la tête de faire de la pédagogie, je préfère utiliser une rédaction plus batarde (qui sera évidemment effectuée plus lentement) mais qui soit compréhensible plus facilement des praticiens ordinaires d'Excel, de façon à les initier à la rédaction des fonctions personnalisées...

Aussi, je soumets à votre sagacité la rédaction suivante que j'avais effectuée, sans résultat, il y a quelques jours à partir de fonctions banales de feuille de calculs : où est-ce qu'elle coince ? (je l'ai recréée rapidement) :

Dim XInf (as ?)
Dim XSup idem
Dim YSup idem
Dim YSup idem
Dim NumLigne idem

Function InterpolCourbe(X, PlageCourbe)

'L'abscisse inférieure est XInf:

XInf = WorksheetFunction.RECHERCHEV(X, PlageCourbe, 1, VRAI)

'En passant, on note que le N° de ligne de cette abscisse inférieure dans le tableau
'de deux colonnes définissant la courbe est NumLigne :

NumLigne = WorksheetFunction.EQUIV(X, PlageCourbe, 0)

'L'ordonnée inférieure est

YInf = WorksheetFunction.RECHERCHEV(XInf, PlageCourbe, 2, VRAI)


'L'abscisse Supérieure peut être trouvée à la ligne qui suit NumLigne, c'est donc :

XSup = WorksheetFunction.Index(PlageCourbe, NumLigne + 1, 1)

'et l'ordonnée supérieure est :

YSup = WorksheetFunction.RECHERCHEV(XSup, PlageCourbe, 2, VRAI)

'La valeur interpolée de Y en découle naturellement. C'est :

InterpolPlage = YInf + (X - XInf) * (YSup - YInf) / (XSup - XInf)

End Function
------------------------------------------------------------------
Ben oui, il faut quand même que je comprenne mes erreurs : je ne suis pas là pour consommer, je suis là pour progresser.
--------------------------------------------------------------

Il reste également des questions intéressantes :

-Comment faire apparaître les conseils de saisie des arguments d'une fonction perso? En particulier, il doit y être précisé qu'il faut dollardiser la plage Maplage.

-Est-il possible de construire une fonction personnalisée qui réaliserait l'interpolation d'une courbe devenant ''biunivoque" (je crois qu'on dit comme ça) ceci dans une certaine zone de x ?

Expliquons-nous :

En fait la colonne simple des Y devient double :
Lorsque les X montent depuis zéro, on reste dans la première colonne des Y jusqu'à une certaine valeur (nommons-la valeur de ''décrochage'') valeur où l'on saute (horizontalement) dans la deuxième colonne.
Inversement, lorsque les X redescendent depuis le maxi, on reste dans la deuxième colonne de Y jusqu'à une (autre) certaine valeur (appelons-là la valeur de ''raccrochage'') valeur où l'on saute (horizontalement) pour revenir dans la première colonne...

Vous l'avez deviné, c'est ainsi que se comporte la portance d'une aile aux alentour de son ''décrochage'' et de son ''raccrochage''...

En vous remerciant à nouveau,

amicalement,

Bernard de Go Mars !

 

[Bernard de Go Mars]

Re : Fonction Perso ''Interpolation''

Grâce à Lapix et ODVJ j'ai progressé en VBA bilingue.

Voilà la Fonction Personnalisée qPédagogique que je propose :

------------------------------------------------
Function InterpolCourbeGoMars(X, PlageX, PlageY)

'Attention : PlageX et Plage Y sont deux colonnes qui doivent
'commencer à la même ligne !!


'Nous nommons cette fonction GoMars non pas par vanité irrépresible,
'mais pour que ce nom n'entre pas en concurrence avec d'autres noms
'formés à partir de InerpolCourbe.
'Au reste, la rédaction de cette fonction personnelle ne peut se
'prévaloir que de qualités pédagogiques.

'Appelons X le X quelconque dont nous recherchons le Y.
'En général ce X est situé entre deux valeurs connues présentes
'dans une colonne PlageX. Nous appellerons XInf et XSup ces valeurs
'connues.
'De même, le Y que nous recherchons est compris entre les valeurs
'YInf et YSup de la colonne PlageY.
'Les colonnes PlageX et PlageY constituent, on le devine, notre tableau
'de chiffres de référence. Si elle peuvent ne pas être contigües
'(si elle peuvent ne pas "se toucher"), il faut quand-même qu'elles
'soient placées à la même hauteur (c'est à dire qu'elles commencent à la
'même ligne et qu'elles progressent ensemble vers le bas.

'Déclarons donc nos variables ''internes"" (elles n'apparaîtront que
'dans ces lignes):

Dim XInf
'c'est le X de notre tableau de chiffres immédiatement inférieur à
'notre X quelconque.

Dim XSup
'c'est le X de notre tableau de chiffres immédiatement supérieur à
'notre X quelconque

Dim YInf
'C'est le Y correspondant au XInf et trouvé facilement
'dans notre tableau de chiffres.

Dim YSup
'C'est le Y correspondant au XSup,et trouvé facilement
'dans notre tableau de chiffres.

Dim NumLigne
'Ce sera le numéro de ligne du XInf dans notre tableau de chiffres

'Recherchons tout d'abord dans la plage PlageX
'des X connus(cette plage peut être une colonne, par ex.), la valeur de
'X imédiatement inférieure au X quelconque dont nous recherchons
'le Y.
'Nous allons effectuer cette recherche à l'aide de la fonction
'RechercheV (nommée VLookup en américain).Il faut juste faire
'précéder cette fonction de la mention Application.WorksheetFunction, ce
'qui la place dans un genre de conteneur .
'Le troisième argument,'comme dans la fonction RechercheV, entraîne
'la recherche de la valeurimmédiatement inférieure à X :

XInf = Application.WorksheetFunction.VLookup(X, PlageX, 1)

'Au passage recherchons la position de cet XInf dans la colonne des X
'Nous allons utiliser la fonction Equiv (traduction US : Match):

NumLigne = Application.WorksheetFunction.Match(X, PlageX)

'(on aurait également pu chercher directement la position de XInf,
'en frappant Match(XInf, PlageX)


'Trouvons à présent le Y correspondant à XInf, qu'on a appelé YInf.
'Puisque nous connaissons sa position dans sa plage, c'est facile :

YInf = Application.WorksheetFunction.Index(PlageY, NumLigne)

'Puisque nous connaissons NumLigne, la position de XInf, il nous est
'facile de trouver XSup, en ajoutant 1 à NumLigne :

XSup = Application.WorksheetFunction.Index(PlageX, NumLigne + 1)

'et de même YSup

YSup = Application.WorksheetFunction.Index(PlageY, NumLigne + 1)

'Dans ces conditions, la valeur du Y interpolée est facile à calculer.
'c'est :

InterpolCourbeGoMars = YInf + (X - XInf) * (YSup - YInf) / (XSup - XInf)

End Function
------------------------------
Il doit y avoir pas mal de dysgraciosités : vous pouvez (devez ?) me les signaler.

Amicalement,

Bernard de Go Mars!

 

[Bernard de Go Mars]

Re : Fonction Perso ''Interpolation''

J'ajoute qu'un ''problème d'extrémité'' se pose lorsque, par hasard, le X quelconque vaut le Xmaximum. L'incrémentation NumLigne + 1 est alors impossible, ce qui met le système en échec.
Je constate que la solution proposée par ODTV est ainsi caduque dans ce cas particulier (il faudrait sans doute rajouter une ligne particulière aux colonnes des X et des Y recopiant les X et Y de l'origine, mais ce n'est pas très élégant).
Il est donc préférable de mettre en oeuvre une fonction logique pour résoudre ce cas peut-être très particulier mais qui planterait sans scrupule tout un tableau...

Je mettrai bientôt en ligne sur mon site cette Fonction Personnalisée pédagogique ...

Amicalement,

Bernard de Go Mars !

 

[Bernard de Go Mars]

Re : Fonction Perso ''Interpolation''

J'ajoute qu'un ''problème d'extrémité'' se pose lorsque, par hasard, le X quelconque vaut le Xmaximum. L'incrémentation NumLigne + 1 est alors impossible, ce qui met le système en échec.
Je constate que la solution proposée par ODTV est ainsi caduque dans ce cas particulier (il faudrait sans doute rajouter une ligne particulière aux colonnes des X et des Y recopiant les X et Y de l'origine, mais ce n'est pas très élégant).
Il est donc préférable de mettre en oeuvre une fonction logique pour résoudre ce cas peut-être très particulier mais qui planterait sans scrupule tout un tableau...

Je mettrai bientôt en ligne sur mon site cette version pédagogique d'une Fonction Personnalisée d'Interpolation ...

Amicalement,

Bernard de Go Mars !

 

[Bernard de Go Mars]

Re : Fonction Perso ''Interpolation''

Je voulais supprimer un envoi en double, mais je ne trouve pas la commande de suppression totale d'un message...
Bernard de Go Mars ![/QUOTE]

 

[Hervé]

Re : Fonction Perso ''Interpolation''

bonjour

pour ton souci d'extrémité, si la plage X est triée, on pourrait tester X avec la derniere valeur de cette plage, et si sup alors on renvoi la derniere valeur de PlageY, est-ce jouable ?

je te propose donc un aménagement à ta fonction (j'ai enlevé les commentaires )

Function InterpolCourbeGoMars(X As Double, PlageX As Range, PlageY As Range)
Dim XInf As Double
Dim XSup As Double
Dim YInf As Double
Dim YSup As Double
Dim NumLigne As Long
Dim dernierevaleur As Double

dernierevaleur = PlageX(PlageX.Rows.Count) 'stock derniere valeur de la plage X

If X > dernierevaleur Then 'si x sup à derniere valeur
    InterpolCourbeGoMars = PlageY(PlageY.Rows.Count) 'resultat=derniere ligne de la plageY
    Exit Function 'sort de la function
End If

XInf = Application.WorksheetFunction.VLookup(X, PlageX, 1)

NumLigne = Application.WorksheetFunction.Match(X, PlageX)


YInf = Application.WorksheetFunction.Index(PlageY, NumLigne)

XSup = Application.WorksheetFunction.Index(PlageX, NumLigne + 1)


YSup = Application.WorksheetFunction.Index(PlageY, NumLigne + 1)


InterpolCourbeGoMars = YInf + (X - XInf) * (YSup - YInf) / (XSup - XInf)

End Function

je me suis egalement permis de typer les variables afin d'eviter le type variant pas tres gracieux.

salut

 

[Bernard de Go Mars]

Re : Fonction Perso ''Interpolation''

Merci cher Hervé, pour cette contribution à la résolution du pb d'extrèmité.

Entretemps, je l'avais résolu de mon côté.

Merci également d'avoir corrigé mes déclarations naîves. Je n'y connais rien (il faut que je m'y mette...)
Attention, je pars du principe que tu es dans le vrai et il se pourrait que j'utilise ces corrections pour une publiction sur mon site...

En effet, j'ai commencé la rédaction d'un texte où je livrerai le code très pédagogique de cette fonction Interpolation Pédagogique. Il faudrait aussi pouvoir adjoindre un code ''efficace'' (et donc non pédagogique), malheureusement ce n'est pas dans mes cordes...

Mais bon, Excel peut se permettre d'utiliser des routines un peu bavardes...
-----------------------------------------------
Il reste encore un problème :
Comment faire apparaître des conseils de saisie à l'ouverture de cette fonction perso ?

Sans ces conseils, le risque d'erreur est grand (en particulier il faut dollardiser les colonnes des X et des Y au moment de la saisie)...

Amicalement,

Bernard

 

[Hervé]

Re : Fonction Perso ''Interpolation''

bonjour bernard

j'ai pas bien compris le sens de cette phrase :

Attention, je pars du principe que tu es dans le vrai et il se pourrait que j'utilise ces corrections pour une publiction sur mon site...

comme toutes réponses données bénévolement par un membre de xld, celle-ci n'engage que la personne qui à posé la question (article L352-1 du code de procédure du ptitours).

et puis, entre nous, il ne s'agit que d'une function remplacant un index equiv.

si cette function est appelée à etre utilisé sur de multiples version d'excel il faudrait quand meme verifier la compatibilité.

quant à ajouter un fichier d'aide à une function, il me semble que c'est possible, en utilisant des fichiers HLP et des DLL. mais je ne l'ai jamais fait et je passe donc la main.

salut

 

[Bernard de Go Mars]

Re : Fonction Perso ''Interpolation''

Merci Hervé.
Evidemment, si je publie cette fonction perso sur mon site, ce ne sera pas la ruée des excellistes. Mais ça m'a tellement énervé qu'une telle fonction de base n'existe pas dans Excel que je me fais un devoir de combler ce manque...
Pour les fichier HLP et DLL, ce qu'il faudrait savoir c'est comment les programmer. Il me semble que cette nécessité de conseils de saisie doit être partagée par tous ceux qui créent des fonction perso, non ?

Amicalement,

Bernard