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?

 

[job75]

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

Bonjour guainflo, bienvenue sur XLD

Utilisez l'enregistreur de macro (menu Outils-Macro-Nouvelle macro) pendant que vous créez la courbe de tendance : vous obtiendrez le code VBA.

Pour les coefficients de cette courbe, seules les courbes de régression linéaires ou logarithmiques (à ma connaissance) sont calculables par des fonctions (DROITEREG et LOGREG, voir l'aide Excel).

Pour les courbes polynomiales, il faudra vous taper le calcul de la régression...

Bon courage.

A+

 

[guainflo]

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

Tous d'abord merci.

J'ai entré quelques points, la colonne A : les X et la colonne B : les Y, puis j'ai enregistré une macro : création d'une courbe (nuage de points avec courbe lissée), puis traçage de la courbe de tendance polynomiale avec affichage de l'équation. Le code la macro donne cela :

Range("A1:B9").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("Feuil1!$A$1:$B$9")
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add
ActiveSheet.ChartObjects("Graphique 2").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).Select
With Selection
.Type = xlPolynomial
.Order = 2
End With
With Selection
.Type = xlPolynomial
.Order = 3
End With
Selection.DisplayEquation = True

J'ai quelques questions :

* Quand je relance la macro (je supprime avant la courbe qui est créé a chaque fois), il y a une erreur à cette ligne :
ActiveSheet.ChartObjects("Graphique 2").Activate
Je crois que l'erreur viens du fait qu'il active quelque chose sur le graphique 2, mais le nouveau graphique créé ne s'apelle plus comme ca?

* Comment récupérer l'équation dans une variable à la fin de la macro?
Par exemple : equation_polyn = xlPolynomial?

* Quand je relance la macro, il me trace 2 courbes (si je supprime cette ligne pour que la macro marche : ActiveSheet.ChartObjects("Graphique 2").Activate), en abcisse il me met 1,2,3,4 ... (les valeurs des lignes ou se trouvent les valeurs que j'ai entré) et en ordonnée de la première courbe les valeurs de la colonne A et pour la deuxième, les valeurs de la colonne B.
Comment modifier les lignes de code pour mettre en entrée de la selection un tableau de n valeurs (tab1(i,j) ou tab2(i) et tab3(j)) représentant X et Y?

Pardonnez moi, je débute en vba et c'est pour mon boulot.

 

[guainflo]

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

Si pour la dernière questions cela n'est pas possible, admettons pour simplifier que je créé une nouvelle feuille dans mon programme que je n'affiche pas, et que je copie les valeurs de mon tableau dans des cellules de cette nouvelle feuille, par exemple de A1 à B9. Le problème et qu'il va me tracer 2 courbes, quelles sont les nouvelles lignes de codes?

 

[guainflo]

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

Quelqu'un a une idée?

 

[job75]

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

Bonjour guainflo, le forum,

On vous a abandonné...

Voici un exemple de ce que l'on peut faire.

La macro est dans le code de la feuille (clic droit sur l'onglet et Visualiser le code).

Edit : j'ai simplifié la macro.

A+

 

[mécano41]

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

Bonjour,

Une solution...

Le calcul s'effectue à chaque modification d'une valeur dans les colonnes de données, dans la limite de la plage jaune.

Les plages et cellules peuvent être déplacées mais doivent rester nommées.

La formule dans la colonne verte n'est pas écrite par le code (on pourrait le faire) il faudra donc la protéger pour qu'elle ne risque pas d'être effacée. Le graphique n'est pas fait non plus par le code...

Il ne faut pas de trou dans les données et évidemment, il faut suffisamment de données et tant qu'il n'y a pas à la fois une valeur X et une valeur Y sur la même ligne, une erreur apparaît.

Cordialement

 

[job75]

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

Re, salut mécano41

J'avais fait un peu vite : en effet, il est bon que tout s'ajuste si l'on modifie les données des plages.

J'ai modifié le fichier en conséquence.

A+

 

[guainflo]

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

Merci à vous 2, c'est très gentil.

Mais j'ai toujours un problème avec cette ligne de code :
ActiveSheet.ChartObjects("Graphique 1").Activate

Par exemple, ce programme est très bien pour mon application, mais il plante à cette ligne car je pense qu'il ne connais pas le graphique 1.
Comment débuter le programme pour créer un graphique et le mettre dans la feuille 1, sachant que ce graphe sera effacé plus tard dans le programme (donc à chaque nouveau lancement du programme, ce graphe n'existe pas)?



Sub graph1()

Application.ScreenUpdating = False

Charts.Add
ActiveChart.Location _
Where:=xlLocationAsObject, Name:="Feuil1"

ActiveSheet.ChartObjects("Graphique 1").Activate
ActiveChart.SetSourceData Source:=[A1:B1].Resize([E1] + 1)
With ActiveChart.SeriesCollection(1).Trendlines
.Item(1).Delete
With .Add
.Type = xlPolynomial
.Order = 3
.Border.ColorIndex = 5 'couleur bleu
.DisplayEquation = True
[G3] = Replace(Replace(.DataLabel.Text, " ", ""), "x", "*x")
[G3] = Replace(Replace([G3], "x3", "x^3"), "x2", "x^2")
End With
End With
Target.Select

End Sub

 

[guainflo]

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

J'ai trouvé quelque chose pour que le programme ne bug pas :
ActiveSheet.ChartObjects(1).Activate
Apparement, l'indice du graphe créé est 1 car c'est le seul donc avec cette ligne sa marche (mais si j'ai plusieurs graphes, ca va planter, il serait mieux de mettre le nom de ce graphe dans cette ligne).

Donc le programme s'effectue sans planter, mais les résultats sont pas bon (voir graphe en pièce jointe, et il ne me met pas dans G3 l'aquation).

Il me trace 3 courbes, 2 histogrammes 2D avec comme abcisses 1,2,3,4,5,6,7 et comme ordonnées pour la 1ère la colonne B et la 2ème la colonne C, la troisième courbe et la courbe de tendance créée à partir de la deuxième courbe (pour rappel, moi je veux qu'il affiche que la courbe de tendance créée à partir des points X (colonne B) et Y (colonne C).



Sub Graph1()

Application.ScreenUpdating = False
Charts.Add
ActiveChart.Location _
Where:=xlLocationAsObject, Name:="Feuil1"
ActiveSheet.ChartObjects(1).Activate
ActiveChart.SetSourceData Source:=[B2:C2].Resize([F1] + 1)
With ActiveChart.SeriesCollection(1).Trendlines
With .Add
.Type = xlPolynomial
.Order = 3
.Border.ColorIndex = 5 'couleur bleu
.DisplayEquation = True
[G3] = Replace(Replace(.DataLabel.Text, " ", ""), "x", "*x")
[G3] = Replace(Replace([G3], "x3", "x^3"), "x2", "x^2")
End With
End With

End Sub

 

[job75]

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

Bonsoir,

Mais j'ai toujours un problème avec cette ligne de code :
ActiveSheet.ChartObjects("Graphique 1").Activate

Pour pouvoir manipuler un graphique (l'effacer et le recréer par exemple), il faut le renommer avec une instruction comme :

ActiveChart.Parent.Name = "toto"

Voir ce fil :

https://www.excel-downloads.com/thr...n-graphique-precis-a-laide-dune-macro.123845/

A+

 

[guainflo]

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

Je te remercie, je l'ai renommé, ca marche, mais j'ai tjs le problème pour afficher que la courbe de tendance avec les bons X et Y.

 

[liloucmoi]

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

Hello Tous,

Guainflo, pourquoi faire cela par macro alors que la formule DROITEREG peut faire cela.
Tu as un nombre de valeurs maximum, donc c'est facile à coder.
Avec un graphique en fixe ca passe.

Cf l'xls joint.

Xldment.

 

[guainflo]

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

Je te remercie liloucmoi
Mais je veut que tous s’effectue sous VBA avec seulement un graphe pour vérification, en fait ce programme ne sera qu’une petite partie d’un programme principal (c’est de l’électricité et la courbe représente la caractéristique à vide d’un alternateur synchrone, je dois avoir cette équation pour mes calculs dans le programme principal).
Donc il est demandé à l’utilisateur, entre autre, d’entrer les points disponibles de la caractéristique à vide, et ce sous programme renvoi l’équation. Mais là, comme je ne connais pas trop VBA (je débute), je test comme ça.

 

[mécano41]

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

Bonjour à tous,

Voici l'application modifiée. Elle fonctionne ici pour deux feuilles. Pour ajouter la seconde, j'ai simplement fait une copie de la première, et dans le code :

Private Sub Worksheet_Change(ByVal Cible As Range)
Dim PlageCoeff As Range
Application.ScreenUpdating = False
Set F1 = Worksheets([COLOR="Red"]Flo2[/COLOR].Name)
With F1
...

j'ai simplement remplacé Flo1 par Flo2 (c'est le nom de code des feuilles, que j'ai utilisé afin que tu puisses modifier les noms d'onglets sans que cela perturbe l'exécution).


Pour Job75 :

Si tu as un instant, regarde le coefficient de corrélation (et le coefficient de détermination qui doit être son carré) en le sortant par une formule et directement en option de la courbe de régression. Je me suis rendu compte que cela ne correspondait pas (aussi bien dans ton ficher que dans le mien)...je ne comprends pas pourquoi...si tu peux faire un essai et me dire où je me trompe...

Cordialement

EDIT : J'ai oublié de préciser que le graphique s'efface et se reconstruit à chaque frappe d'une valeur dans la zone jaune, donc inutile d'essayer de modifier quelque chose dedans manuellement...la modif sera effacée au coup suivant