Discussion :

[Kimy_Ire]

Bonjour à tous,

Je me permets de recréer un topic pour savoir si quelqu'un connait la fonction VBA (ou excel) qui permet de créer une approximation d'une courbe par la fonction puissance de la forme y = A * x^B.

Je me permet également de préciser que je ne cherche pas l'approximation exponentielle (LogReg ou LogEst) de la forme y = A * exp(b * x).

Sous excel, on voit bien que lors de "l'ajout de courbe de tendance" la fonction "puissance" est différente de l'exponentielle en particulier dans mon cas où le R² est bien meilleur.

Quelqu'un saurait-il quel est la fonction VBA qui permet de récupérer les 2 valeurs (A et B) de cette courbe de tendance de manière automatique ?

Je l'imagine de la forme

WorksheetFunction.Estimation

avec Estimation = nom de la fonction en question.

Merci par avance pour vos réponses ! =)

[mercatog]

y = A * B^x <=> y=A*Exp(C*x) avec C=Ln(B)


Edit

Tu voulais dire l'approximation y=A*x^B


Edit 2

Pour trouver les coefficient A et B de la tendance y=A*x*B

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
A: =EXP(INDEX(DROITEREG(LN(Y);LN(X));1;2))

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

B: =INDEX(DROITEREG(LN(Y);LN(X);;);1)

(Trouvé sur le net)

Soit la fonction matricielle

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Function PuissEst(ByVal X As Range, ByVal Y As Range) As Variant
Dim A As Double, B As Double
 
A = Evaluate("EXP(INDEX(LINEST(LN(" & Y.Address & "),LN(" & X.Address & ")),1,2))")
B = Evaluate("INDEX(LINEST(LN(" & Y.Address & "),LN(" & X.Address & "),,),1)")
PuissEst = Array(A, B)
End Function

A tester par ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Sub Test()
Dim Coef
 
Coef = PuissEst(Range("A2:A16"), Range("B2:B16"))
Debug.Print "a=" & Coef(0), "b= " & Coef(1)
End Sub

[Kimy_Ire]

J'ai écrit une énorme bétise et je tiens à m'en excuser...

Je ne voulais pas y = A * B^x mais y = A * x^B... (ce que tu as bien décelé)
... ce qui, pour le coup, n'est pas du tout pareil... =(

Justement y = A * B^x => y = A * exp( x*ln(B) ) que l'on peut trouver facilement grace à l'approximation exponentielle.
Cependant, y = A * x^B => y = A * exp( B*ln(x) ) n'est pas possible avec l'approximation exponentielle...

Je m'excuse encore de la bétise que j'ai écrite... fatigue surement. ^^

Je vais tester ce que tu m'as proposé.

[issoram]

Bonjour,

Pour en terminer définitivement avec le modèle y = A*B^x et ton post précédent, les résultats obtenus avec LinEst et LogEst sont absolument les mêmes (ce qui me parait normal vu que c'est le même modèle à l'exponentielle près).
Un exemple pour la vérif:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
'On suppose un modèle du type y = A*B^x <=> ln(y)=ln(A)+x*ln(B)
'Les paramètres à estimer sont A et B
Sub test_Regression()
 
    Dim estim As Variant
    Dim x() As Variant, y() As Variant, z() As Variant
    Dim epsilon As Double
    Dim i As Integer
    Randomize
 
    'On remplit les vecteurs des observables
    ReDim x(1 To 20)
    ReDim y(1 To 20)
    ReDim z(1 To 20)
    For i = 1 To 20
        epsilon = i * Rnd               'on définit une erreur proportionnelle à x
        x(i) = i
        y(i) = 3 * 2 ^ i + epsilon
        z(i) = Log(y(i))
    Next i
 
    'Estimation par la méthode de regression LogEst
    estim = WorksheetFunction.LogEst(y, x, True, True)
    Debug.Print estim(1, 1), estim(1, 2), estim(3, 1)           'affiche les coefficients B, A et le R2
 
    'Estimation par la méthode de regression LinEst
    estim = WorksheetFunction.LinEst(z, x, True, True)
    Debug.Print Exp(estim(1, 1)), Exp(estim(1, 2)), estim(3, 1) 'affiche les coefficients B, A et le R2
 
End Sub

Par contre effectivement ce n'est pas la même chose si tu considères le modèle y = A*x^B!
Concernant ce dernier modèle, tu peux effectivement comme te l'a indiqué Mercatog utiliser LinEst. Mais tu peux tout aussi bien utiliser LogEst (cf. exemple ci dessous).
Encore une fois les estimations ainsi que le coefficient de détermination R2 sont les mêmes via les 2 méthodes.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
'On suppose un modèle du type:
' y = A*x^B. Les paramètres à estimer sont A et B
' <=> ln(y)=ln(A)+B*ln(x)               : modèle linéaire, solvable par LinEst
' <=> y = A*C^X avec C=exp(B) et X=ln(x): modèle "exponentiel" solvable par LogEst
Sub test_Regression2()
 
    Dim estim As Variant
    Dim x() As Variant, y() As Variant, z() As Variant
    Dim epsilon As Double
    Dim i As Integer
    Randomize
 
    'On remplit les vecteurs des observables
    ReDim x(1 To 20)
    ReDim y(1 To 20)
    ReDim z(1 To 20)
    For i = 1 To 20
        epsilon = 0.2 * i * Rnd             'on définit une erreur proportionnelle à x
        x(i) = Log(i)
        y(i) = 3 * i ^ 2 + epsilon
        z(i) = Log(y(i))
    Next i
 
    'Estimation par la méthode de regression LogEst
    estim = WorksheetFunction.LogEst(y, x, True, True)
    Debug.Print Log(estim(1, 1)), estim(1, 2), estim(3, 1)     'affiche les coefficients B, A et le R2
 
    'Estimation par la méthode de regression LinEst
    estim = WorksheetFunction.LinEst(z, x, True, True)
    Debug.Print estim(1, 1), Exp(estim(1, 2)), estim(3, 1)      'affiche les coefficients B, A et le R2
 
End Sub

Cordialement.

[Kimy_Ire]

Si j'ai bien compris, la fonction test_Regression2() transforme les x et les y en ln et réalise un LogEst ou LinEst du tout. Je dois donc prendre ma série de x (pour le coup assez simple (1, 2, 3, 4, etc...) dans la tableau x() et ma série de y (qui ressemble à une exponnentielle décroissance mais qui se match beaucoup mieux avec l'approximation puissance) dans le tableau y().

C'est bien cela ?

Merci bcp pour vos implications ! =)

[issoram]

Alors:

- Pour LinEst:

y = A*x^B <=> ln(y)=ln(A)+B*ln(x) <=> Y = C + B*X avec X=ln(X), Y=ln(y) et C =ln(A).

Ton vecteur X sera défini par Xi=ln(xi)
Ton vecteur Y sera défini par Yi=ln(yi)
LinEst(Y,X) te renvoie les coefs C et B. A est donné par A= exp(C).

- Pour LogEst:

y = A*x^B <=> y = A*C^X avec C=exp(B) et X=ln(x)

Ton vecteur X sera défini par Xi=ln(xi)
Ton vecteur Y ne bouge pas (ici 1,2,...20 pour l'exemple)
LogEst(Y,X) te renvoie les coefs C et A. B est donné par B= ln(C).

J'espère que c'est plus clair.

NB: en passant le dernier argument des 2 fonctions (LinEst et RegEst) à True, tu obtiens les statistiques liées à l'estimation dans une matrice à 5 lignes et 2 colonnes (le R2 étant l'élément de la 3eme ligne première colonne: cf. l'aide vba)

[Kimy_Ire]

Messieur, vous êtes des champions !!!

Comme vous pouvez le voir dans l'image :



1) Les correspondances entre les courbes de tendance d'excel et celle que je trace via VBA sont absolument identique
2) Que l'approximation puissance est bien meilleure ! ^^

Je vous remercie INFINIMENT pour votre aide ! =) Je suis arrivé à bout de cette fameuse courbe que je n'arrivais pas à approximer !

Merci merci et merci encore !

Petite précision dont je viens de me rendre compte au cas où ce post serve à quelqu'un d'autre : j'ai utilisé la fonction "Ln" plutôt que "Log".