Récupérer automatiquement coefs régression polynomiale
Bonjour à tous,
Après avoir tracé une courbe sur Excel (nuage de point) j'ai coché l'option "régression polynomiale d'odre 2". Je voudrait maintenant qu'Excel place automatiquement les coefficients "A", "B" et "C" dans les cellules de mon choix (pour ne pas avoir à les recopier à la main, ce qui est une source d'erreurs éventuelles).
Quelqu'un pourrait-il m'indiquer la marche à suivre? je n'ai rien trouvé jusqu'à maintenant!
Merci!
Bonjour
Ayant un peu utilisé les droites de tendance les coeff peuvent théoriquement être récupérés comme suit :
a =INDEX(DROITEREG($A$4:$L$4;$A$2:$L$2);1)
b = =INDEX(DROITEREG($A$4:$L$4;$A$2:$L$2);2)
coeff de détermination R2 =COEFFICIENT.DETERMINATION($A$4:$L$4;$A$2:$L$2)
a4:l4 =Y connus
a2:l2= x connus
Ci joint mon exemple....
Merci de m'indiquer si c'était la bonne approche
Cordialement
FINDRH
Bonjour FindRh; Le forum
Je m'intéresse aussi à ce moyen de récupérer par une formule et je trouve ton exposé très intéressant !!!
Mais il y a une petite erreur Car tu as tracé une courbe de tendance linéaire (Pas Polynomiale)
une croissance régulière se mesure avec une courbe de tendance linéaire, mais une parabole. C'est à dire une courbe dont l'équation est de la forme:
Y=A*x²+B*x+C
Là j'ai remis le graph avec une courbe polynomiale et regardes l'équation n'est plus la même...
J'ai rajouté une courbe en M22 regardes y voir et dis moi ce que tu en penses.
C'est une courbe de Y Prévus. (bon, je l'ai fait avec le peu que je connais sur le sujet)
Je ne maitrise pas la régression mais j'espère en apprendre plus en suivant ce fil...
Par contre, je ne sais pas avec quelle formule calculer C.
Cdlt
-- Jeu Juin 17, 2010 4:44 pm --
Re
Surtout si quelqu'un veut corriger, qu'il n'hésite pas !
Tous les bons conseils sont les bien venus
-- Jeu Juin 17, 2010 6:21 pm --
Encore moi.
Voila! J'ai trouvé
=DROITEREG(M7:M18;L7:L18^{1.2};VRAI;VRAI)regardes le fichier Joint tout est là ! (la version 2)
Et j'ai même trouvé le C
Avec de la bonne volonté... On arrive à tout
Bonjour le Forum
Comprends pas... ça n'intéresse personne !
on demande l'avis du forum puis hop ! plus personne (on part en vacance)
Cela dit j'ai trouvé aussi la formule pour récupérer le coefficient R² en Polynomiale:
=INDEX(DROITEREG(M7:M18;L7:L18^{1.2};;1);3;1)Pour ceux que cela intéressent
Cordialement...
Bonjour ,
Tout le monde ne part pas en vacances, mais il arrive surtout que l'on soit bien occupé et que le forum devienne malheureusement secondaire. Mais même avec retard ceux qui sont intéressés ( par les questions des autres) finissent par regarder !
J'ai vu tes approches et tes formules..... je ne les aurais ni cherchées, ni trouvées car je ne me sers que très peu des stats si ce n'est pour faire des prévisions basiques.
J'utilise la tendance sous forme de droite dite "des moindres carrés" avec calcul des variations saisonnières et un contrôle avec des "epsilon" à zéro....
Je vais me pencher sur tes formules fin aout pour valider mes prévisions initiales....
merci pour tes recherches et ton éclairage, même s'ils ne répondent pas au demandeur initial !
Cordialement
FINDRH
Bonjour Findrh
Ben pourtant le demandeur initial a écrit ceci:
j'ai coché l'option "régression polynomiale d'odre 2". Je voudrait maintenant qu'Excel place automatiquement les coefficients "A", "B" et "C" dans les cellules de mon choix (pour ne pas avoir à les recopier à la main, ce qui est une source d'erreurs éventuelles)
Je pense quand même que cela répond à ce qu'il a demandé non ?
Même si après un mois on a plus de nouvelles Lol
C'est très décevant quand on aide quelqu'un et qu'il ne donne plus aucune nouvelle
Quand on m'aide je réponds et dis merci (c'est la moindre des choses)
Maintenant pour ce qui concerne les formules, il faut savoir la nature de ta prévision.
Si tes données font des hauts et des bas, là, tu auras besoin d'une régression Polynomiale
Mais si tes données sont régulières et ont tendance à monter (ou descendre) là, tu
auras besoin d'une régression "droite" ou voir même Exponentielle.
Note Bien que j'ai aussi du changer la disposition de tes données que tu avais placé en Ligne
alors que pour ce travail il faut les placer en Colonne !! Sinon ça ne fonctionne pas
Cdlt
Bonjour Jimmy,
Votre sujet date de 2008 mais j'espère que vous êtes toujours u habitué de ce site et que vous pourrez me répondre.
Moi je suis intéressé car j'ai une application pratique.
Cependant je fait plutôt les calculs par macros plutôt que par formule dans une cellule.
J'ai 2 problèmes:
D'une part, comment rentrez vous la formule DROITEREG dans la cellule pour obtenir les 3 coefficients dans le fichier R2(2) ?
En principe on tape = puis la formule, mais là il y a des { }, et la même formule dans 3 cellules contiguës donne 3 coefficients différents.
Je suppose que c'est une matrice, mais comment rentrez vous ça ?
D'autre part, quand j'essaie de rentrer la formule dans la macro, ça ne prend pas ^ et les { }.
Dans les macros je sais qu'on doit utiliser le nom Anglais de la fonction, c'est à dire LINEST, et pour obtenir séparément les 3
coefficients on doit utiliser INDEX. Je le fait couramment pour une simple droite, mais pour les fonctions polynominales, Excel m'indique erreur quand je tape ^ et { }.
Merci pour votre réponse, mais depuis le temps êtes vous toujours sur ce site ?
Cordialement
Bonjour Gamassette
Houlà ! depuis le temps j'ai un peu oublié mais en t'aidant ça va me remettre dans le bain
=DROITEREG(C3:C10;D3:D10)en C3:C10 tu as les Y connus et D3:D10 les X connus
si je me souviens bien
Et si tu veux la rentrer de façon matricielle, il faut que tu tapes Ctrl et maj et enter
Voila, j'espère avoir répondu à ta question
Bien cordialement
Ravi de te retrouver sur cette discussion,
En attendant ta réponse, j'ai essayé cette macro sur le fichier R2(2) que tu as créé:
Cells(32, 4).Select
ActiveCell.Formula = "=INDEX(LINEST(M7:M18,L7:L18^{1,2},TRUE,TRUE),1)"
Cells(32, 5).Select
ActiveCell.Formula = "=INDEX(LINEST(M7:M18,L7:L18^{1,2},TRUE,TRUE),2)"
Cells(32, 6).Select
ActiveCell.Formula = "=INDEX(LINEST(M7:M18,L7:L18^{1,2},TRUE,TRUE),3)"
Cells(32, 7).Select
ActiveCell.Formula = "=INDEX(LINEST(M7:M18,L7:L18^{1,2},TRUE,TRUE),4)"
Cells(32, 8).Select
ActiveCell.Formula = "=INDEX(LINEST(M7:M18,L7:L18^{1,2},TRUE,TRUE),5)"
Avec Index 6 ça ne donne rien.
J'ai essayé d'avoir les coefs indépendamment, mais seul le 1er correspond au 1er des tiens, et je ne sais pas pourquoi.
Je vais essayer le ctrl maj Enter avec un truc donné par un autre internaute.
Il faut lancer l'enregistrement d'une macro en tapant la formule comme tu dis, et je vais voir comment Excel l'enregistre dans la macro.
Cordialement
Bonjour,
les stats c'est trop ancien pour moi mais pour vba je peux vous aider.
1) .select est inutile dans 99% des cas et ralenti pour rien.
2) pour mettre une formule matricielle dans une feuille en vba c'est .formulaArray. Donc :
Cells(3, 4).FormulaArray = "=INDEX(LINEST(M7:M18,L7:L18^{1,2},TRUE,TRUE),1)"Si tu veux les 3 paramètres d'un coup :
Cells(2, 4).Resize(, 3).FormulaArray = "=LINEST(M7:M18,L7:L18^{1,2},TRUE,TRUE)"
Et si tu veux le résultat dans une variable et non sur la feuille il faut déclarer la variable Variant qu'elle puisse recevoir une matrice :
Dim a As Variant, i As Long
a = Evaluate("INDEX(LINEST((M7:M18),(L7:L18)^{1,2},TRUE,TRUE),1)")
For i = 1 To 3
Debug.Print a(i) 'résultat dans la fenêtre d'exécution VBE (Ctrl+G)
Next iNB : les références sont considérées comme absolues même s'il n'y a pas les $
Résultats à contrôler, je n'ai pas d'exemple concret pour valider et avec les matrices...
eric
PS j'ai oublié : précise la feuille si tu ne veux pas avoir de surprise :
a = Sheets("Feuil1").Evaluate("INDEX(LINEST((M7:M18),(L7:L18)^{1,2},TRUE,TRUE),1)")Bonjour Eriic,
Je vais cogiter tout ça.
C'est très clair.
Merci pour le coup de main.
Pour clore le sujet et aider de futurs internautes intéressés, voici un fichier d'exemple pour illustrer le sujet.
Ce fichier résume des informations fournies par Jimy et Eriic sur la base d'un exemple conçu par Jimy en 2008.
Bon travail à tous
Bonjour,
je devais avoir une petite fatigue, en fait tu peux avoir les 3 coeff sans boucle :
Sub CalculsCoefs()
Dim a As Variant, i As Long
a = Evaluate("INDEX(LINEST((M7:M18),(L7:L18)^{1,2},TRUE,TRUE),1)")
' en horizontal
Cells(26, 4).Resize(, 3) = a
'en Vertical
Cells(28, 4).Resize(3) = Application.Transpose(a)
End Suberic
Bonjour Eriic,
En fait j'ai un autre problème maintenant car je veux mettre les plages de cellule X et Y en variable comme ci-dessous:
Dim PlageX As Range
Dim PlageY As Range
Dim LignXdeb As Long: LignXdeb = 7
Dim LignXFin As Long: LignXFin = 18
Dim ColXdeb As Long: ColXdeb = 12
Dim ColXfin As Long: ColXfin = 12
Dim LignYdeb As Long: LignYdeb = 7
Dim LignYFin As Long: LignYFin = 18
Dim ColYdeb As Long: ColYdeb = 13
Dim ColYfin As Long: ColYfin = 13
Set PlageX = Range(Cells(LignXdeb, ColXdeb), Cells(LignXFin, ColXfin)): 'L7:L18
Set PlageY = Range(Cells(LignYdeb, ColYdeb), Cells(LignYFin, ColYfin)): 'M7:M18
Dim a As Variant, i As Long
'a = Evaluate("INDEX(LINEST((M7:M18),(L7:L18)^{1,2},TRUE,TRUE),1)"): 'version d'origine sans plage variable OK
'a = Evaluate("INDEX(LINEST((PlageY),(PlageX)^{1,2},TRUE,TRUE),1)")
a = Evaluate("INDEX(LINEST(PlageY,PlageX,TRUE,TRUE),1)")
'a = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(PlageY, PlageX^{1,2}), 1)
For i = 1 To 3
Cells(35, i + 3) = a(i)
Next i
End Sub
Comme tu vois, j'ai essayé plusieurs versions de a mais ça ne marche pas.
La dernière fonctionne si on ne met pas la boucle à la fin, ni le ^{1.2] car j'utilise cette ligne depuis longtemps (fonction linéaire).
Si tu as une idée.
Cordialement
Fait en direct sans tester vue l'heure...
Ta formule dans Evaluate est une chaine. Il faut donc l'éditer et la fabriquer en concatenant comme toutes les chaines.
Ex :
a = Evaluate("INDEX(LINEST((M7:M18),(L7:L18)^{1,2},TRUE,TRUE),1)")
devient pour la 1ère plage :
a = Evaluate("INDEX(LINEST((" & ta_plage.address & "),(L7:L18)^{1,2},TRUE,TRUE),1)")N'oublie pas de fermer la chaine avec " et de la rouvrir après ton insertion de variable avec "
eric
Merci Eriic,
ça fonctionne avec mes 2 plages.
Tout semble au point pour compléter mon programme.
Cordialement
Bonjour Eriic,
J'ai fait fonctionner l'instruction Linest en polynomial tel que tu me l'as indiquée dans plusieurs essais, mais celui-ci dans le fichier joint
me donne du fil à retordre.
La ligne Coefs= avec Evaluate, Index, Linest, etc... indique Error 2015 et entraine un coefficient incompatible à la ligne suivante
car la variable Coefs(1) est vide.
J'ai eu le cas où une cellule était vide dans la plage considérée ce qui entrainait une erreur, et aussi le cas avec une plage de 2
lignes et 2 colonnes, ce qui se réduisait à une droite, et ces 2 cas entrainaient des erreurs que j'ai résolu.
Dans le cas présent je sèche, surtout que si je trace la courbe sans faire appel à une macro et que j'utilise la fonction Tendance avec affichage de l'équation, ça marche, donc je ne comprends pas.
Si tu peux encore m'éclairer sur ce coup, c'est pas de refus.
Il est tard, mais ça peux attendre.
La macro s'active en pressant les touches Ctrl Shift t
Je n'ai mis que la partie de mon programme qui pose problème.
Cordialement
Bonjour,
Quelle proc de quel module ?
eric
La macro s'appelle Calculs et se lance en tapant Ctrl+shift+t.
Je n'ai pas précisé car quand on clique sur affichage puis afficher les macros, on la trouve ; il n'y en a qu'une.
Désolé, j'ai pas l'habitude de penser en terme de modules.
Cordialement
Bonjour,
[B3].Formula = "=INDEX(LINEST((" & PlageY.Address & "),(" & PlageX.Address & ")^{1,2},TRUE,TRUE),1)"
te retoune #Valeur dans la cellule.
Si la formule sur feuille te retourne une erreur, Evaluate ne peut pas te retourner autre chose. Au passage tes données Y ont plutôt l'air d'être en ligne 10, pas 9... Ca ne change rien et je ne sai pas pourquoi
A toi de tester les erreurs et d'agir en conséquence. Ou bien tester en amont les valeurs de PlageX et PlageY.
eric