Régression logarithmique VBA

Y compris Power BI, Power Query et toute autre question en lien avec Excel
S
Sarostique
Jeune membre
Jeune membre
Messages : 20
Inscrit le : 26 mai 2014
Version d'Excel : 2013

Message par Sarostique » 26 mai 2017, 17:15

Bonjour,

J'essai désespéramment d'automatiser une régression logarithmique en VBA.

J'ai un classeur avec deux colonnes : en colonne A les âges et en colonne B des rémunérations.

J'essai d'effectuer la régression en utilisant la formule suivante :
y = droitereg(( plage rému ; ln( plage age)) * ln (x) + ordonnee.origine (plage rému ; ln(plage age))

Cette formule a pour but de me donner une valeur de y (rémunération médiane) pour une valeur de x choisi (qui correspond à l'âge)

J'essaie simplement de traduire cette formule en vba, sachant que j'ai préalablement identifié le numéro de ma dernière ligne pleine (car l'étendue des plages peut varier).

Mon problème est que je suis plutot débutant en vba et je ne parviens pas à sélectionner une plage finie de donnée mais qui peut varier (faut-il faire un tableau ? Utiliser set ?). Ensuite j'essai d'utiliser la fonction Linest mais cela ne fonctionne pas...

Auriez-vous la possibilité de m'aider ?

Je vous remercie d'avance.

Bien cordialement.
Avatar du membre
i20100
Passionné d'Excel
Passionné d'Excel
Messages : 6'036
Appréciations reçues : 352
Inscrit le : 16 mars 2017
Version d'Excel : 2010

Message par i20100 » 27 mai 2017, 03:24

Bonjour Sarostique,

Pourriez-vous donner un exemple avec données et résultat attendu, merci!
Prenons soins de nous et de notre vaisseau spatial, nous n’en n’avons qu’un ...notre planète terre
isabelle
S
Sarostique
Jeune membre
Jeune membre
Messages : 20
Inscrit le : 26 mai 2014
Version d'Excel : 2013

Message par Sarostique » 27 mai 2017, 10:51

Bonjour SabV,

J'ai fait un exemple avec des données aléatoires ci-joint.

Merci de votre aide !
Exemple régression logarithmique.xlsx
(10.21 Kio) Téléchargé 21 fois
Avatar du membre
i20100
Passionné d'Excel
Passionné d'Excel
Messages : 6'036
Appréciations reçues : 352
Inscrit le : 16 mars 2017
Version d'Excel : 2010

Message par i20100 » 27 mai 2017, 16:00

Bonjour Sarostique,

à tester,
Sub test1()
Dim x As Integer, y As Long, Age As Range, Rémun As Range
Set Age = Range("A3:A50")
Set Rémun = Range("B3:B50")
Range("C1").Value = Evaluate("LINEST(" & Rémun.Address & ",LN(" & Age.Address & "))")
Range("D1").Value = Evaluate("INTERCEPT(" & Rémun.Address & ",LN(" & Age.Address & "))")

x = 30
y = Range("C1").Value * Application.Ln(x) + Range("D1").Value
MsgBox y
End Sub

ou bien avec les variables a et b
Sub test2()
Dim x As Double, y As Long, a, b, Age As Range, Rémun As Range
Set Age = Range("A3:A50")
Set Rémun = Range("B3:B50")
a = CVar(Evaluate("LINEST(" & Rémun.Address & ",LN(" & Age.Address & "))"))
b = Evaluate("INTERCEPT(" & Rémun.Address & ",LN(" & Age.Address & "))")
x = 30
y = Evaluate(a) * Application.Ln(x) + b
MsgBox y
End Sub
Prenons soins de nous et de notre vaisseau spatial, nous n’en n’avons qu’un ...notre planète terre
isabelle
S
Sarostique
Jeune membre
Jeune membre
Messages : 20
Inscrit le : 26 mai 2014
Version d'Excel : 2013

Message par Sarostique » 27 mai 2017, 16:30

Merci beaucoup mais mon problème en fait réside dans le fait que la longueur de la série peut varier.

Dans votre exemple :

Set Age = Range("A3:A50")
Set Rémun = Range("B3:B50")

ne permettrait pas un fonctionnement optimale si la série terminait à A48 ou à l'inverse à A90 ?
Avatar du membre
i20100
Passionné d'Excel
Passionné d'Excel
Messages : 6'036
Appréciations reçues : 352
Inscrit le : 16 mars 2017
Version d'Excel : 2010

Message par i20100 » 27 mai 2017, 16:55

pour trouver la dernière cellule renseignée de la colonne A
LastRw = Cells(Rows.Count, 1).End(xlUp).Row
avec cette variable on peut définir les plages Age et Rémun dynamiquement
Sub test2()
Dim x As Double, y As Long, LastRw As Long, a, b, Age As Range, Rémun As Range
LastRw = Cells(Rows.Count, 1).End(xlUp).Row
Set Age = Range("A3:A" & LastRw)
Set Rémun = Range("B3:B" & LastRw)
a = Evaluate("LINEST(" & Rémun.Address & ",LN(" & Age.Address & "))")
b = Evaluate("INTERCEPT(" & Rémun.Address & ",LN(" & Age.Address & "))")
x = 30
y = Evaluate(a) * Application.Ln(x) + b
MsgBox y
End Sub
Prenons soins de nous et de notre vaisseau spatial, nous n’en n’avons qu’un ...notre planète terre
isabelle
Avatar du membre
i20100
Passionné d'Excel
Passionné d'Excel
Messages : 6'036
Appréciations reçues : 352
Inscrit le : 16 mars 2017
Version d'Excel : 2010

Message par i20100 » 27 mai 2017, 17:22

aussi, tous cela peut être fait uniquement par formule,
voici un exemple:
Sarostique-Exemple régression logarithmique.xlsm
(15.7 Kio) Téléchargé 13 fois
Prenons soins de nous et de notre vaisseau spatial, nous n’en n’avons qu’un ...notre planète terre
isabelle
S
Sarostique
Jeune membre
Jeune membre
Messages : 20
Inscrit le : 26 mai 2014
Version d'Excel : 2013

Message par Sarostique » 27 mai 2017, 19:50

Je test cette macro lundi et reviens vers vous.

Merci beaucoup en tout cas !
S
Sarostique
Jeune membre
Jeune membre
Messages : 20
Inscrit le : 26 mai 2014
Version d'Excel : 2013

Message par Sarostique » 29 mai 2017, 09:34

Bonjour,

Je viens d'adapter le code à ma problématique et je l'ai retesté en formule mais le résultat final diffère que ce soit sur la valeur de a ou sur la valeur de b.

Bien cordialement.

Bonjour,

Je viens d'adapter le code à ma problématique et je l'ai retesté en formule mais le résultat final diffère que ce soit sur la valeur de a ou sur la valeur de b.

Bien cordialement.
S
Sarostique
Jeune membre
Jeune membre
Messages : 20
Inscrit le : 26 mai 2014
Version d'Excel : 2013

Message par Sarostique » 29 mai 2017, 09:49

Après vérification j'ai l'impression qu'il s'agit d'un bug.

Les opérations que je demande à ma macro sont les suivantes :
1. copier / coller un onglet existant (nom de l'onglet d'origine extraction)
2. Le renommer (=médian)
3. Supprimer certaines lignes
4. Effectuer la régression logarithmique

Cette partie de code :
Set Age = Sheets("Médian").Range("O2:O" & LastRw)
Set Rémun = Sheets("Médian").Range("Z2:Z" & LastRw)
ne s'effectue pas correctement. En effet la sélection se fait sur l'onglet extraction et non sur l'onglet médian ce qui fausse le résultat.

Auriez-vous une idée de comment contourner ce problème ?
Répondre
  • Sujets similaires
    Réponses
    Vues
    Dernier message