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

BioTop

XLDnaute Nouveau
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

XLDnaute Barbatruc
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
VB:
 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

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

VB:
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...
 

BioTop

XLDnaute Nouveau
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

XLDnaute Nouveau
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
 

Membres actuellement en ligne

Statistiques des forums

Discussions
312 922
Messages
2 093 644
Membres
105 775
dernier inscrit
assen