Récupérer Equation de droite [Excel]

Kriis · 15/04/2013, 14h44

Bonjour amis laborantin!

Je me retrouve face à un soucis. Le but est le gain de temps...

Je vous explique. J'ai des "gros" tableaux avec +- 1500 valeurs/colonnes, avec lesquelles je les "convertis" en graphique, je le fait de manière rudimentaire avec les macros vus que les points de départs et d'arrivé sont différents selon la feuille de calculs sur laquelle je bosse.

Mais pour synthétiser le tous et obtenir un tableau regroupant tout les résultats de mes tableaux il me faut les équations de droite de mes graphiques.
J'arrive à les faires apparaitre sur mes graphiques mais devoir les importés manuellement 1 à 1 dans une cellule et en modifiant la forme des équations pour obtenir une valeur désiré en fonctions de mes paramètres, c'est relativement long, chiant...etc

En gros j'ai un graph comme ceci, avec une équation de ce style, que je met dans un tableau comme vous le voyer, puis je fait les calcules et tout mais manuellement...

http://img94.imageshack.us/img94/7951/17118557.png

Voyer vous mon "soucis" et avez vous une idée pour soulager mon probleme?

Je tiens juste a préciser que niveau macro je n'y connais rien alors les lignes de code oui, mais faut que je sache a quoi correspond quoi...

P.S : On pourrais pas crée un fil unique pour les problèmes Excel? ^^

Cordialement

cricri · 15/04/2013, 19h43

sinon calcule la
somme des x /nb de valeur =x1
somme des y /nb de valeur =y1
la droite de regression passe pr x1 y1 oblige

attention ca se complique
coef=somme(des x-x1)(des y-y1) /somme(des x-x1)²
coef= 0.0677 dans ton exemple
reste le b =90.99 a calculer en sachant x1 y1 coef te laisse chercher

cricri · 15/04/2013, 19h56

snon t as ausii
les fonctions pente et ORDONNEE.ORIGINE qui te donne le resultat

Melchiorus · 15/04/2013, 20h17

Un exemple avec les valeurs de X dans la colonne A (ligne 1 à 1000) et les valeurs de Y sont dans la colonne B (ligne 1 à 1000).

Sélectionne deux cellules côté à côte où tu veux avoir les paramètres de la droite de régression linéaire, par exemple D1 et E1.
Tape la formule =DROITEREG(B1:B1000;A1:A1000) et valide la formule en faisant Ctrl+Shift+Entrée (très important).

En D1 tu auras le "a" et en E1 tu auras le "b" de la régression linéaire "y=ax+b".

Une autre méthode serait d'intégrer cela dans ta macro en vba en utilisant la fonction LINEST à chaque fois que tu crées un graphique.

harermuir · 15/04/2013, 20h37

Citation :

Publié par Melchiorus

Sélectionne deux cellules côté à côte où tu veux avoir les paramètres de la droite de régression linéaire, par exemple D1 et E1.
Tape la formule =DROITEREG(B1:B1000;A1:A1000) et valide la formule en faisant Ctrl+Shift+Entrée (très important).

Altérnativement, tu peux accéder à ces fonctions dans l'assistant de formule, il y a une case à cocher pour voir les fonctions qui donne une plage de cellule en résultat. Ca te permettra peut être de récupérer des paramètre intéressant type khi2 ou coëfficient de corrélation.

Kriis · 16/04/2013, 07h52

Citation :

Publié par cricri

sinon calcule la
somme des x /nb de valeur =x1
somme des y /nb de valeur =y1
la droite de regression passe pr x1 y1 oblige

attention ca se complique
coef=somme(des x-x1)(des y-y1) /somme(des x-x1)²
coef= 0.0677 dans ton exemple
reste le b =90.99 a calculer en sachant x1 y1 coef te laisse chercher

snon t as ausii
les fonctions pente et ORDONNEE.ORIGINE qui te donne le resultat

J'ai pas bien compris le début... mais peut-etre qu'avec Ordonnee.origine j'arriverais plus facilement...

Citation :

Publié par Melchiorus

Un exemple avec les valeurs de X dans la colonne A (ligne 1 à 1000) et les valeurs de Y sont dans la colonne B (ligne 1 à 1000).

Sélectionne deux cellules côté à côte où tu veux avoir les paramètres de la droite de régression linéaire, par exemple D1 et E1.
Tape la formule =DROITEREG(B1:B1000;A1:A1000) et valide la formule en faisant Ctrl+Shift+Entrée (très important).

En D1 tu auras le "a" et en E1 tu auras le "b" de la régression linéaire "y=ax+b".

Une autre méthode serait d'intégrer cela dans ta macro en vba en utilisant la fonction LINEST à chaque fois que tu crées un graphique.

J'ai tester la matrice, mais j'ai toujours une erreur... bref pour le VRAI/FAUX j'ai tout tester voir si c'étais pas là que ça merdais, mais apparament non... Tant pis

Citation :

Publié par harermuir

Altérnativement, tu peux accéder à ces fonctions dans l'assistant de formule, il y a une case à cocher pour voir les fonctions qui donne une plage de cellule en résultat. Ca te permettra peut être de récupérer des paramètre intéressant type khi2 ou coëfficient de corrélation.

Je connais pas, je vais chercher, voir si ça peut m'aider.

Merci! Et si vous avez d'autres idées

Cordialement

Melchiorus · 16/04/2013, 17h11

Citation :

Publié par Kriis™

J'ai tester la matrice, mais j'ai toujours une erreur... bref pour le VRAI/FAUX j'ai tout tester voir si c'étais pas là que ça merdais, mais apparament non... Tant pis

Si ça marche pour les autres, c'est que tu le fais mal.

Il faut entrer les zones exactes où tu fais le DROITEREG, il ne doit pas y avoir de cases vides dans ces zones.

Kriis · 16/04/2013, 19h59

Je vérifie demain au taf, je vous tiens au courant!

EDIT : Oui niquel, petite erreur de cellule effectivement!

Autre question : J'ai 152 classeur excel et a chaqu'un de ces classeur je dois faire un graph.
Deplus ces graph on 3 colonnes : temps ¦ angle Gauche ¦ angle droite; je ferais des graph Temps =f(Angles), cependant chaque classeur contiens un nombres de point différent, y a t'il quand même possibilité d'effectuer une macro pour effectué mes graph?

Question supplémentaire bonus : Si oui, peut on encore allez plus loin et automatiser la création de courbes de tendances linéaire sur chaque courbes de chaques graphique (et afficher en plus l'équation de droite?)

Kriis · 17/04/2013, 14h42

Voila mon début de code un peu foireux...

Code:

Sub Test_graphique()


    Dim nbLigne As Integer
    finColonne = 1
    Do While VarType(ActiveCell.Value) <> 0
       finColonne = finColonne + 1
       Cells(1, finColonne).Select
    Loop


 
'Tu définis tes variables pour la construction du graphe
 


    'Variable stockant le graphique
    Dim objChart As Chart
           
    'Variable stockant la plage de cellules du graphique
    Dim objRange As Range


 
'Tu récupère la plage du graphe à réaliser avec ceci
 

    Set objRange = Worksheets(Copy_of_50.50_1_to_8_bis).Range(Worksheets(sheet_1).Cells(1, 1), Worksheets(Copy_of_50.50_1_to_8_bis).Cells(finLigne - 1, finColonne))


 
Ajout d 'une page de graphique
 


    Set objChart = ThisWorkbook.Charts.Add


 
'Mise en forme du graphique
 


    With ActiveChart
    ...
    End With

    Charts.Add
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = Worksheets(mes(J)).Range("p2:p184")
    ActiveChart.SeriesCollection(1).Values = Worksheets(mes(J)).Range("o2:o184")
    ActiveChart.Location Where:=xlLocationAsObject, Name:=mes(J)
    ActiveChart.HasLegend = False
    ActiveChart.PlotArea.Select
       
    Worksheets(mes(J)).ChartObjects(1).Chart.SeriesCollection(1).Trendlines.Add Type:=xlPolynomial, DisplayEquation:=True, DisplayRSquared:=True

End Sub

Tu récupère la plage du graphe à réaliser avec ceci : Je sais pas quoi y mettre... j'ai pris le nom de mes fichier et tout mais le code est en rouge....

Mise en forme du graphique : Je cherche en nuage de point basique... mais je trouve nul pars...

HELP pls :'(

Melchiorus · 17/04/2013, 17h46

Sub test()
Dim DerniereLigne As Integer
DerniereLigne = Range("A65536").End(xlUp).Row
Dim AreaX(), AreaY()
ReDim AreaX(DerniereLigne - 1)
ReDim AreaY(DerniereLigne - 1)
For p = 1 To DerniereLigne
AreaX(p - 1) = Range("A" & p).Value
AreaY(p - 1) = Range("B" & p).Value
Next p
Range("D1:E1") = WorksheetFunction.LinEst(AreaY(), AreaX())
End Sub

C'est dans le cas où les valeurs de X sont dans la colonne A et que la première valeur est sur la ligne 1.
Si ta première valeur est sur la ligne 2 il faut l'adapter un peu mais le principe reste le même.
On doit pouvoir faire plus simple que ce que j'ai écrit mais je ne m'y connais pas trop en vba.

Kriis · 17/04/2013, 18h48

Donc ton code je le prend tel quel et je le met sous une macro excel et ça biche ou je dois l'intégrer a mon code?

Mais merci bcp pour ta réponse

Melchiorus · 17/04/2013, 19h56

[Edit] En fait je n'avais pas lu que tu demandais un truc différent... je croyais que tu avais toujours ton problème avec les coefficients de la régression linéaire. Oublie mon message précédent.

Kriis · 17/04/2013, 20h15

Citation :

Publié par Melchiorus

[Edit] En fait je n'avais pas lu que tu demandais un truc différent... je croyais que tu avais toujours ton problème avec les coefficients de la régression linéaire. Oublie mon message précédent.

Mais très bien si je peux tout faire en un c'est aussi niquel ^^

Melchiorus · 17/04/2013, 20h17

Citation :

Publié par Kriis™

Mais très bien si je peux tout faire en un c'est aussi niquel ^^

Franchement, c'est trop difficile à faire sans avoir le fichier que tu utilises et un exemple du résultat que tu veux.

Kriis · 17/04/2013, 22h38

Citation :

Publié par Melchiorus

Franchement, c'est trop difficile à faire sans avoir le fichier que tu utilises et un exemple du résultat que tu veux.

Je vous link tout ça demain avec d'autre explication si jamais!

~Boumy~ · 17/04/2013, 23h20

Pourquoi tu t'acharnes à utiliser Excel? Ya des logiciels qui sont prévu pour ça et qui te le font en 2s.

Kriis · 18/04/2013, 07h54

Citation :

Publié par ~Boumy~

Pourquoi tu t'acharnes à utiliser Excel? Ya des logiciels qui sont prévu pour ça et qui te le font en 2s.

Bah parce que c'est le logiciel de l'appareil me permet de l'exporter sous excel(y a d'autre type mais je connais pas et les utilise pas)...
Mais si tu as un logiciel a me proposer je suis aussi preneur!

Sinon

Citation :

Publié par Melchiorus

Franchement, c'est trop difficile à faire sans avoir le fichier que tu utilises et un exemple du résultat que tu veux.

Voila le type de fichier que je souhaiterais avoir (PJ)
Donc j'ai 152 fichier excel contenant le même genre de feuille intitulé "tableau", je souhaiterais que chaque feuille me donne un "Graph" pour qu'ensuite je puisse faire un "Calcul" avec mes différentes équation de droite.

Voilou

~Boumy~ · 18/04/2013, 09h30

Citation :

Publié par Kriis™

Bah parce que c'est le logiciel de l'appareil me permet de l'exporter sous excel(y a d'autre type mais je connais pas et les utilise pas)...
Mais si tu as un logiciel a me proposer je suis aussi preneur!

Sinon

Voila le type de fichier que je souhaiterais avoir (PJ)
Donc j'ai 152 fichier excel contenant le même genre de feuille intitulé "tableau", je souhaiterais que chaque feuille me donne un "Graph" pour qu'ensuite je puisse faire un "Calcul" avec mes différentes équation de droite.

Voilou

Prend un vrai logiciel de stats: R.

Alors ça va peut-être te prendre un peu de temps pour apprendre le langage mais tu va en gagner beaucoup en automatisation. Au pire je te pond le code et tu essaie de le comprendre.

PS: je ne comprend pas comment tu obtiens la 2eme courbe (celle qui monte).

Exemple de code vite fait pour te donner une idée:

Code:

data = read.table("../Downloads/555.csv", sep="\t") #j'ai triché en enregistrant le tableau dans un fichier à part mais ya moyen de le lire directement d'un feuillet Excel

plot(data[,1], data[,2], col="red", pch=19, cex=0.5, xlim=c(0,200), ylim=c(0,120), xlab="Time (sec)", ylab="Angle (Degree)") #affiche la courbe 1
par(new=T); plot(data[,1], data[,3], col="blue", pch=19, cex=0.5, xlim=c(0,200), ylim=c(0,120), xlab="", ylab="")#affiche la courbe 2

lm1 = lm(data[,2] ~data[,1]) #coeff. de regression de la courbe 1
lm2 = lm(data[,3] ~data[,1]) #et de la courbe 2

abline(lm1, col="red", lwd=2) #trace la droite de regression pour la courbe 1
abline(lm2, col="blue", lwd=2)

Kriis · 18/04/2013, 10h09

Alors je tiens déjà à te remercier pour tes réponses!

Je vais un peu me renseigner sur le logiciel que tu m'as fournis!

De plus les 2courbes correspondent l'une à l'angle droit et l'autre à l'angle gauche et les 2 droites sont les courbes de tendances associé à chaque courbes.
C'est de cela que tu parle?

AAAHH l'extension .csv, ça me parle, mon logiciel de la machine me permet aussi d'exporter sous ce type de fichier...

Je comprend le code vite fait, je vois quoi correspond à quoi, mais le refaire... ^^

~Boumy~ · 18/04/2013, 10h20

Citation :

Publié par Kriis™

Alors je tiens déjà à te remercier pour tes réponses!

Je vais un peu me renseigner sur le logiciel que tu m'as fournis!

De plus les 2courbes correspondent l'une à l'angle droit et l'autre à l'angle gauche et les 2 droites sont les courbes de tendances associé à chaque courbes.
C'est de cela que tu parle?

Bah, tu as du faire une erreur dans le graph dans le fichier que tu as mis. Je vois une courbe en violet/rouge nommée "right" mais elle ne correspond pas aux données.

Citation :

AAAHH l'extension .csv, ça me parle, mon logiciel de la machine me permet aussi d'exporter sous ce type de fichier...

Je comprend le code vite fait, je vois quoi correspond à quoi, mais le refaire... ^^

Peut importe le type de fichier, R peut à peu-près tout lire. Faut juste installer une extension pour lire les fichiers xls.

Les autres font peut être venir avec une solution pour automatiser tout ça sous Excel, mais si c'est quelque chose que tu penses avoir à refaire alors je te conseil fortement d'apprendre à te servir de R. Ca te simplifiera grandement la vie.

Questions: tu as un fichier par tableau ou c'est dans un même fichier dans différent feuillets ?

Kriis · 18/04/2013, 10h45

Citation :

Publié par ~Boumy~

Bah, tu as du faire une erreur dans le graph dans le fichier que tu as mis. Je vois une courbe en violet/rouge nommée "right" mais elle ne correspond pas aux données.

J'ai ça quand je check le graph...

http://img443.imageshack.us/img443/9476/82085325.png

Citation :

Publié par ~Boumy~

Les autres font peut être venir avec une solution pour automatiser tout ça sous Excel, mais si c'est quelque chose que tu penses avoir à refaire alors je te conseil fortement d'apprendre à te servir de R. Ca te simplifiera grandement la vie.

J'en aurais encore des fichiers oui, mais peut-être plus autant, mais bon ça peut tj servir!

Citation :

Publié par ~Boumy~

Questions: tu as un fichier par tableau ou c'est dans un même fichier dans différent feuillets ?

j'ai 1 fichier par tableau... Mais je les vide manuellement aussi car de bases sans travailler les fichier excel j'ai 13 colonnes que je restreint a 3, vus que c'est les seules utiles... Et supprimes des valeurs abérantes et inutiles, car le tableau n'est pas complet.

Navrée si je vois pas de quoi tu parle vis a vis de ma courbe...

~Boumy~ · 18/04/2013, 18h57

Citation :

Publié par Kriis™

J'ai ça quand je check le graph...

J'en aurais encore des fichiers oui, mais peut-être plus autant, mais bon ça peut tj servir!

j'ai 1 fichier par tableau... Mais je les vide manuellement aussi car de bases sans travailler les fichier excel j'ai 13 colonnes que je restreint a 3, vus que c'est les seules utiles... Et supprimes des valeurs abérantes et inutiles, car le tableau n'est pas complet.

Navrée si je vois pas de quoi tu parle vis a vis de ma courbe...

Bah chez moi j'ai une courbe différente. Ca doit venir de LibreOffice ou de formules manquantes.

Tu clean manuellement 150 tableaux de 1500 lignes ?

T'as bien du courage.

En fait, je ne voit pas de solution convenable avec Excel (bon je suis pas un pro). A ma connaissance, il ne sais pas gérer des formules inter fichiers. Donc soit tu copies manuellement la formule dans 150 fichiers, soit tu groupes les tableaux dans un seul fichier (là encore tu dois le faire à la main) mais je sais pas si Excel va bien apprécier 150 graphs dans un seul fichier.

Melchiorus · 18/04/2013, 19h03

En fait je ne comprends pas à quoi sert les graphes.
Ce que tu veux c'est les coefficients de la régression linéaire et il n'y a pas du tout besoin de faire le graphique.

En vba, je pense qu'on peut automatiser l'ouverture et le traitement de tous les fichiers d'un répertoire donné.

Kriis · 18/04/2013, 20h51

Citation :

Publié par Melchiorus

En fait je ne comprends pas à quoi sert les graphes.
Ce que tu veux c'est les coefficients de la régression linéaire et il n'y a pas du tout besoin de faire le graphique.

En vba, je pense qu'on peut automatiser l'ouverture et le traitement de tous les fichiers d'un répertoire donné.

Si tu veux les graph c'est juste pour se donner un avis visuelle sur le sujet!
Sinon y a une fonction sous excel j'en doute... Mais je l'as connais pas

Je suis en train de bosser sur un code avec une amie; donc dès que j'ai qqchose je vous le link!

Citation :

Publié par ~Boumy~

Bah chez moi j'ai une courbe différente. Ca doit venir de LibreOffice ou de formules manquantes.

Tu clean manuellement 150 tableaux de 1500 lignes ? T'as bien du courage.

En fait, je ne voit pas de solution convenable avec Excel (bon je suis pas un pro). A ma connaissance, il ne sais pas gérer des formules inter fichiers. Donc soit tu copies manuellement la formule dans 150 fichiers, soit tu groupes les tableaux dans un seul fichier (là encore tu dois le faire à la main) mais je sais pas si Excel va bien apprécier 150 graphs dans un seul fichier.

Je regroupe mes fichier manuellement aussi par paquet de 8 vus qu'il ont un lien entre eux...

cricri Alpha & Oméga	sinon calcule la somme des x /nb de valeur =x1 somme des y /nb de valeur =y1 la droite de regression passe pr x1 y1 oblige attention ca se complique coef=somme(des x-x1)(des y-y1) /somme(des x-x1)² coef= 0.0677 dans ton exemple reste le b =90.99 a calculer en sachant x1 y1 coef te laisse chercher
15/04/2013, 19h43

cricri Alpha & Oméga	snon t as ausii les fonctions pente et ORDONNEE.ORIGINE qui te donne le resultat
15/04/2013, 19h56

Melchiorus Alpha & Oméga	Citation : Publié par Kriis™ J'ai tester la matrice, mais j'ai toujours une erreur... bref pour le VRAI/FAUX j'ai tout tester voir si c'étais pas là que ça merdais, mais apparament non... Tant pis Si ça marche pour les autres, c'est que tu le fais mal. Il faut entrer les zones exactes où tu fais le DROITEREG, il ne doit pas y avoir de cases vides dans ces zones.
16/04/2013, 17h11

Kriis Alpha & Oméga	Donc ton code je le prend tel quel et je le met sous une macro excel et ça biche ou je dois l'intégrer a mon code? Mais merci bcp pour ta réponse
17/04/2013, 18h48

Melchiorus Alpha & Oméga	[Edit] En fait je n'avais pas lu que tu demandais un truc différent... je croyais que tu avais toujours ton problème avec les coefficients de la régression linéaire. Oublie mon message précédent. Dernière modification par Melchiorus ; 17/04/2013 à 20h14.
17/04/2013, 19h56

Kriis Alpha & Oméga	Citation : Publié par Melchiorus [Edit] En fait je n'avais pas lu que tu demandais un truc différent... je croyais que tu avais toujours ton problème avec les coefficients de la régression linéaire. Oublie mon message précédent. Mais très bien si je peux tout faire en un c'est aussi niquel ^^
17/04/2013, 20h15

Melchiorus Alpha & Oméga	Citation : Publié par Kriis™ Mais très bien si je peux tout faire en un c'est aussi niquel ^^ Franchement, c'est trop difficile à faire sans avoir le fichier que tu utilises et un exemple du résultat que tu veux.
17/04/2013, 20h17

Kriis Alpha & Oméga	Citation : Publié par Melchiorus Franchement, c'est trop difficile à faire sans avoir le fichier que tu utilises et un exemple du résultat que tu veux. Je vous link tout ça demain avec d'autre explication si jamais!
17/04/2013, 22h38

~Boumy~ Alpha & Oméga	Pourquoi tu t'acharnes à utiliser Excel? Ya des logiciels qui sont prévu pour ça et qui te le font en 2s.
17/04/2013, 23h20

Melchiorus Alpha & Oméga	En fait je ne comprends pas à quoi sert les graphes. Ce que tu veux c'est les coefficients de la régression linéaire et il n'y a pas du tout besoin de faire le graphique. En vba, je pense qu'on peut automatiser l'ouverture et le traitement de tous les fichiers d'un répertoire donné.
18/04/2013, 19h03

Récupérer Equation de droite [Excel]

Connectés sur ce fil