génération par VBA d'une courbe de tendance à partir d'un tableau de couple de points

[guainflo]

Bonjour,

J'écris un programme sous vba excel 2000.
L'utilisateur tape des couples de points dans une feuille excel (X et Y), le nombre de couple est variable (jusqu'à 13 maximum).
Ensuite lorsque l'utilisateur clic sur un bouton qui ce trouve sur cette feuille, mon programme VBA doit à partir de ces couples de points (nuage de points) calculer la courbe de tendance polynomiale (rang 3) et la mettre dans une variable ou la copier dans une cellule de la première feuille.

Dans un excel, il suffit de tracer le graphe puis choisir courbe de tendance et il l'affiche mais moi je veut que tous ce passe dans vba (l'utilisateur ne doit rien voir).

Je me demande si il existe une instruction pour cela (en entrée on a le tableau (X,Y) ou 2 tableaux (tab1(X) et tab2(Y), et en sortie dans une variable ou une cellule : y=AX^3+BX^2+CX+D).
Existe t'il une ligne de code sous vba pour cela?
Et existe t'il la même chose pour une équation de courbe de tendance logarithmique?

 

[guainflo]

Re : génération par VBA d'une courbe de tendance à partir d'un tableau de couple de p

Bonjour mécano41 et merci, ton programme marche nickel.

La seule chose est que je n'ai pas besoin de mettre à jour le graphe et l'équation (l'utilisateur tape sur la feuille d'accueil les points et d'autres valeurs nécessaire au programme principal et il appui sur un bouton pour effectuer le programme d'une seule traite, il affiche quand même le graphe pour vérifier les points et l'équation).

Donc je veut que le programme pour l'équation polynomiale est un sous programme que l'on appel par "call"
J’ai mis le programme qui se trouve sur la feuille "régression" dans le programme qui se trouve dans le module (voir le programme en pièce jointe), tous ces lignes forment un sous programme « call () » que l’on appelle lorsque l’on appui sur un bouton :

Private Sub Valider_Click()
Call creer_graphe
End Sub

J’ai remplacé xrel et yrel par B3 et C3 :
Set PlageX = Range(.Range("B3").Offset(1, 0), .Range("B3").End(xlDown))
Set PlageY = Range(.Range("C3").Offset(1, 0), .Range("C3").End(xlDown))

Ja'i ajouté, je ne sais pas si c'est au bonne endroit :
[G3] = Replace(Replace(.DataLabel.Text, " ", ""), "x", "*x")
[G3] = Replace(Replace([G3], "x3", "x^3"), "x2", "x^2")

Mon programme plante à ce niveau :
Set PlageCoeff = .Range("PlageCoeff")

Je ne sais pas comment faire pour supprimer ce bug, peut-tu regarder mon programme en pièce jointe et le modifier pour qu’il fonctionne ou me dire comment changer les lignes de code.
Si tu veut remettre xrel et yrel, dit moi comment tu fait parce que je n’y arrive pas. Et enfin par besoin de supprimer le graphe à la fin du programme.
Merci par avance.

 

[liloucmoi]

Re : génération par VBA d'une courbe de tendance à partir d'un tableau de couple de p

re,

Mon programme plante à ce niveau :
Set PlageCoeff = .Range("PlageCoeff")

Il y a un probleme, avant cette ligne , plagecoeff n'est pas initialisé, et la tu assayes de l'initialiser en fonction de lui-même, d'où le bug.

Je pencherais pour un truc comme ceci :

Set PlageCoeff = .Range("###")

Où ### est une plage de 4 colonnes et 3 lignes (pour prendre les coeff et les statistiques de la régression).

Enfin Cf ci joint, j'ai modif deux/trois trucs dans la création des séries aussi.

Xldment

 

[guainflo]

Re : génération par VBA d'une courbe de tendance à partir d'un tableau de couple de p

Merci liloucmoi, ce programme me convient.

Maintenant pour mon programme principal, il me faut exactement la même chose mais avec une courbe de tendance du type logarithmique du type (je crois) y=A*log(X)+B (comme dans excel).

Que faut t'il changer dans le dernier programme que tu m'a fournis pour faire cela?

Set PlageCoeff = .Range("$E$3:$G$3")
Sélectionner seulement 3 colonnes?

Dans cette ligne :
Trendlines.Add(Type:=xlPolynomial, Order:=3, Forward:=0, Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select
Changer xlPolynomial par xl... ?

Merci par avance.

 

[guainflo]

Re : génération par VBA d'une courbe de tendance à partir d'un tableau de couple de p

Je n'ai pas trouvé sur le net, quelqu'un connais les lignes de codes pour la courbe de tendance logarithmique permettant de sortir les 2 coeffs.

 

[liloucmoi]

Re : génération par VBA d'une courbe de tendance à partir d'un tableau de couple de p

Bonjour,

Petit extrait de l'aide sur la méthode add de l'objet trendlines : (juste à faire F1 dans l'éditeur VBA )

>Méthode Add telle qu'elle s'applique à l'objet Trendlines.

Cette méthode crée une nouvelle courbe de tendance. Elle renvoie un objet Trendline.

expression.Add(Type, Order, Period, Forward, Backward, Intercept, DisplayEquation, DisplayRSquared, Name)
expression Obligatoire. Expression qui renvoie un objet Trendlines.

Type Argument de type XlTrendlineType facultatif. Type de la courbe de tendance.

XlTrendlineType peut être l'une de ces constantes XlTrendlineType.
xlExponential
xlLinear par défaut
xlLogarithmic
xlMovingAvg
xlPolynomial
xlPower

Je te laisse choisir ce que tu souhaites

Sinon pour les paramètres, il ne faut que deux colonnes puisque tu cherches uniquement 2 paramètres (A.X+B). Avant tu cherchais 4 paramètres (A.X^3+B.X^2+C.X+D), donc il fallait 4 colonnnes.

Xldment.

 

[guainflo]

Re : génération par VBA d'une courbe de tendance à partir d'un tableau de couple de p

Merci liloucmoi de t'interessé à mon problème.

Mon aide sous vba ne fonctionne pas, elle fonctionne seulement sur excel (mais il ne parle pas de vba dedans).

J'ai changé le xlpolynomial par xlLogarithmic, ca marche très bien, mais le problème est pour les coeffs (il s'affiche dans le tableau car j'ai écris DisplayEquation:=True), mais je n'arrive pas à les mettre dans la feuille comme pour le xlpolynomial :

J'ai tapé cela, en prenant que 2 colonnes et 1 ligne (pour le polynomiale, j'ai pris 4 colonnes et 1 seul ligne et ça fonctionne), en faisant cela il me met tjs les coeffs du polynomial (la le x^3 et x^2), faut t'il prendre autre chose que plagecoeff :

Set PlageCoeff = .Range("$E$12:$F$12")
PlageCoeff.Select
Selection.FormulaArray = "=LINEST(" & PlageY.Address & "," & PlageX.Address & "^{1,2,3})" ' Met la formule matricielle des coeff.

 

[liloucmoi]

Re : génération par VBA d'une courbe de tendance à partir d'un tableau de couple de p

Re,

Selection.FormulaArray = "=LINEST(" & PlageY.Address & "," & PlageX.Address & "^{1,2,3})" ' Met la formule matricielle des coeff.

Je te propose de changer LINEST par LOGEST c'est l'équivalent en VO de respectivement DROITEREG et LOGREG.

Xldment

 

[guainflo]

Re : génération par VBA d'une courbe de tendance à partir d'un tableau de couple de p

En remplacant LINEST par LOGEST, ca ne me donne pas non plus les bons coeffs, mais dans cette ligne il est écrit PlageX.Address & "^{1,2,3})", es-ce bon "^{1,2,3}" ?

Selection.FormulaArray = "=LOGEST(" & PlageY.Address & "," & PlageX.Address & "^{1,2,3})" ' Met la formule matricielle des coeff.

 

[liloucmoi]

Re : génération par VBA d'une courbe de tendance à partir d'un tableau de couple de p

Re,

Oups en effet, j'avais pas vu ça...

Remplace le "^{1,2,3})" par : ")".

Xldment.

 

[guainflo]

Re : génération par VBA d'une courbe de tendance à partir d'un tableau de couple de p

Je suis embêtant, mais les coeffs ne sont tjs pas les bons, voici les lignes changées par rapport au polynomial :

Set PlageCoeff = .Range("$E$12:$F$12")
PlageCoeff.Select
Selection.FormulaArray = "=LOGEST(" & PlageY.Address & "," & PlageX.Address & ")"

.Trendlines.Add(Type:=xlLogarithmic, Order:=3, Forward:=0, Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select

Et voici les résultats :
L'équation dans le graphe données par excel : y = 2,5067Ln(x) + 0,5757 (bon)
Et les coeffs données : 1,313492243 1,061640934 (mauvais)

 

[liloucmoi]

Re : génération par VBA d'une courbe de tendance à partir d'un tableau de couple de p

re,

Ok I got it. ^^

Donc en fait l'idée c'est que LOGREG c'est pas ce que tu fais quand tu fais une courbe de tendance logarithmique.

Remplace :

"=LOGEST(" & PlageY.Address & "," & PlageX.Address & ")"

Par :

"=LINEST(" & PlageY.Address & ",LN(" & PlageX.Address & "))"

Ca devrait marcher.

Xldment

 

[guainflo]

Re : génération par VBA d'une courbe de tendance à partir d'un tableau de couple de p

Cool ca marche, merci énormément.