XL 2010 [Résolu] création d'une fonction utilisateur...

[BioTop]

Bonjour,
Si j'utilise depuis longtemps Excell, je en me suis jamais réellement lancé dans le VBA, sauf pour modifier quelques macros...
depuis quelques jours je travaille sur la recherche de tendances . pour cela je dois essayer plusieurs jeux de données de contrôles ( jeux de valeurs pour l'axe des X).
Excel propose des graphes de points avec des courbes de tendances ( linéaires, exponentielles, logarithmiques,etc...). on peut toujours faire un copier coller de la formule de la courbe de tendance qui apparait sur le graphique, mais c'est fastidieux.

je me suis donc penché sur le problème, pour calculer les coefficients des courbes et afficher le coefficient de détermination pour les 4 principaux types d'extrapolation.
Mais ça reste fastidieux d'essayer chaque jeux de données...

Voici donc l'objet de mon post:
De même qu'il existe une fonction TENDANCE(valeurs des Y, Valeurs des X, nouveau X) pour l'extrapolation linéaire, je voudrais créer 3 autres fonctions pour les extrapolation exponentielles, Logarithmiques et puissances:

• TENDANCE_EXP(valeurs des Y, Valeurs des X, nouvel X)
• TENDANCE_LOG (valeurs des Y, Valeurs des X, nouvel X)
  
• TENDANCE_POW(valeurs des Y, Valeurs des X, nouvel X)

et j'avoue ne pas trop savoir comment m'y prendre.

Voici un appel de la fonction TENDANCE :
=TENDANCE( I6:L6; W6:Z6; AC6; VRAI) qui permet de calculer le point d’abscisse AC6 ...

et voici ce que je voudrai faire :

Public Function TENDANCES (TypTend as ENTIER; Valx() As Variant, Valy() As Variant, Newx As Variant) As Variant
switch(typtend)

case 0 : 'Extrapolation Linéaire

TENDANCES= TENDANCE( Valy, Valx, Newx, VRAI)​

case 1 : 'Extrapolation Exponentielle

TENDANCES= TENDANCE_EXP( Valy, Valx, Newx)​

case 0 : 'Extrapolation Logarithmique

TENDANCES= TENDANCE_LOG( Valy, Valx, Newx)​

case 1 : 'Extrapolation Puissance

TENDANCES= TENDANCE_POW( Valy, Valx, Newx)​

End Switch​

End Function

Public Function TENDANCE_EXP (ValY() As Variant, ValX() As Variant, NewX As Variant) As Variant

A =EXP( INDEX( DROITEREG( LN( ValY) ; (ValX)); 2))
B= DROITEREG( LN( ValY);(ValX))

TENDANCE_EXP = A*EXP( B*NewX)​

End Function

Public Function TENDANCE_LOG (ValY() As Variant, ValX() As Variant, NewX As Variant) As Variant

B = INDEX( DROITEREG( ( ValY) ; LN( ValX)); 2)
A = DROITEREG( ( ValY);LN( ValX))

TENDANCE_EXP = A*LOG( NewX) + B ​

End Function

Public Function TENDANCE_POW (ValY() As Variant, ValX() As Variant, NewX As Variant) As Variant

A =EXP( INDEX( DROITEREG( LN ( ValY) ; LN( ValX)); 2))
B= DROITEREG( LN( ValY);LN( ValX))

TENDANCE_EXP = A* NewX^ B ​

End Function

Merci pour votre aide , en attendant je continue à chercher ....
Jean-luc

 

[vgendron]

Hello

à priori tu as déjà presque tout..enfin. je crois
le switch devient en VBA select case
les ? disparaissent
les ; deviennent des ,

dans ta fonction TENDANCES, tu as deux fois le case 0 et le case 1
ce qui te donne ceci à mettre dans un module standard
alt + F11 pour ouvrir l'éditeur

 Function TENDANCES(TypTend As ENTIER, Valx() As Variant, Valy() As Variant, Newx As Variant) As Variant
Select Case TypTend
    Case 0:  'Extrapolation Linéaire
        TENDANCES = TENDANCE(Valy, Valx, Newx, VRAI)
    Case 1:  'Extrapolation Exponentielle
        TENDANCES = TENDANCE_EXP(Valy, Valx, Newx)
    Case 0:  'Extrapolation Logarithmique
        TENDANCES = TENDANCE_LOG(Valy, Valx, Newx)
    Case 1:  'Extrapolation Puissance
        TENDANCES = TENDANCE_POW(Valy, Valx, Newx)
End Select
End Function

Public Function TENDANCE_EXP(Valy() As Variant, Valx() As Variant, Newx As Variant) As Variant

A = Exp(Index(DROITEREG(Ln(Valy), (Valx)), 2))
B = DROITEREG(Ln(Valy), (Valx))

TENDANCE_EXP = A * Exp(B * Newx)
End Function

Public Function TENDANCE_LOG(Valy() As Variant, Valx() As Variant, Newx As Variant) As Variant

B = Index(DROITEREG((Valy), Ln(Valx)), 2)
A = DROITEREG((Valy), Ln(Valx))

TENDANCE_EXP = A * Log(Newx) + B
End Function

Public Function TENDANCE_POW(Valy() As Variant, Valx() As Variant, Newx As Variant) As Variant

A = Exp(Index(DROITEREG(Ln(Valy), Ln(Valx)), 2))
B = DROITEREG(Ln(Valy), Ln(Valx))

TENDANCE_EXP = A * Newx ^ B
End Function

 

[vgendron]

bon en fait, non tu n'as pas tout..
il faut traduire les fonctions en VBA..
voir par ici..
https://www.developpez.net/forums/d...roximation-d-courbe-fonction-puissance-y-x-b/

 

[vgendron]

un début de piste
Par exemple. pour la fonction Tendance_Exp

Function TENDANCE_EXP(Valy As Range, Valx As Range, Newx As Integer)
Application.Volatile

formuleA = "EXP(INDEX(linest(LN(" & Valy.Address & "),(" & Valx.Address & ")),2))"
formuleB = "linest(ln(" & Valy.Address & ")," & Valx.Address & ")"


A = Evaluate(formuleA)
B = Evaluate(formuleB)

TENDANCE_EXP = A * Exp(B * Newx)

End Function

Ce que je ne comprend pas:
la fonction Droitereg devient linEst
pour la formule A, ca fonctionne très bien, le A est bien évalué
pour la formule B (pourtant plus simple) ca ne marche pas !!
donc 0 retour pour la fonction...

 

[vgendron]

Re
Voici un début de fichier...

 

[BioTop]

Merci Vgendron pour ton aide...
je vais de ce pas mettre en oeuvre ta contribution...

et faire un grand pas dans ma connaissance avec VBA ...

A+
Jean-Luc

 

[BioTop]

Je dois avouer que je n'ai pas tout compris.

Parce que finalement je ne vais pas utiliser les extrapolation puissance et exponentielle, je me suis limité à mettre au point l'extrapolation logarithmique.

Et ça a fini par tomber en marche ! Voici la fonction qui "fonctionne":

Public Function TENDANCE_LOG(Valy As Range, Valx As Range, Newx As Double)
Application.Volatile

formuleB = "index(linest((" & Valy.Address & "),ln(" & Valx.Address & ")),1)"
ValueA = Evaluate(formuleB)

formuleB = "index(linest((" & Valy.Address & "),ln(" & Valx.Address & ")),2)"
ValueB = Evaluate(formuleB)

'B = ValueA * Log(Newx) + ValueB
TENDANCE_LOG = ValueA * Log(Newx) + ValueB

End Function

Merci encore pour ce coup de pouce sans lequel je galérerai encore !!

 

[BioTop]

Bonjour,

Une remarque toutefois, la réalisation des fonctions TENDANCE_EXP et TEANDANCE_POW impose respectivement de déplacer ou d'ajouter une fonction LN sur le paramètre VALY ...

Et quand je fais cela, la fonction à cela prêt identique à TENDANCE_LOG, retourne une erreur.

si quelqu'un à une idée....
A+
Jean-Luc