[Catégorie modifiée VB6 -- VBA] Courbe de tendance linéaireRésolu

Question

Bonjour à tous,

Sous Excel 2003 (VBA) j'essaie de "monter" une macro qui ferait ce que fait excel dans "ajouter graphique + plage de données + courbe de tendance linéaire sur ensemble de point + afficher équation (y = ax + b) + coefficient de corrélation R² ".

J'ai trouvé comment faire pour que la macro "lance" ces différentes opérations avec l'éditeur de macro : création du graphique, récupération de l'équation et de R² (pas simple à cause du saut de ligne!), puis effacement du graphique (je n'en ai pas besoin)... mais c'est très lourd...

On doit évdemment pouvoir faire beaucoup plus simple en construisant une sub function, mais je ne sais pas par où commencer.

Pour information, le but du programme est de trouver les facteurs de corrections (a et b) à apporter sur des plages de différence, entre une valeur lue sur un appareil de mesure et la valeur réelle obtenue en laboratioire. 
Exemple avec un manomètre: 
On lit  2,5 Pa alors que la valeur réelle est de 2,3 et de même on a 5,9 Pa pour 5,6 ; quel correctif donnerait la valeur réelle pour une valeur lue de 3,2 Pa? et avec quelle erreur ? 

Avant d'encombrer ce post avec une floppée de lignes de code, je souhaite avoir un avis, ne serait-ce que pour savoir si je poste sur le bon forum.

Merci pour votre aide ou commentaire.

Bien cordialement

Rataxes64

CerberusPau · Accepted Answer

Bonjour,

Avant de te répondre, j'ai pris le temps de "maniper".

Tu as entièrement raison : Effectivement, cette série d'itérations pour trouver un R² le plus grand possible, n'est pas forcément bien venue!

Mieux vaut passer par des matices. C'est ce que j'ai fait et ça permet d'être bien plus précis.
Pour info, les matrices en question sont constituées sur 2 colonnes, l'une comprenant les valeurs lues sur le manomètre à contrôler, et l'autre, celles (réelles) du manomètre étalon.

Ensuite, je traite par "étage" de 2 lignes (ou +) et cela reste malgré tout suffisemment raisonnable (dans mon cas) pour me passer de macro for-next applées par WorkSheet_change... Je reste donc "simplement" en mode formule.

Quant à l'info "corrélation", je l'ai tout simplement remplacée par un calcul d'erreur par matrice.

En tout cas , merci beaucoup pour ton aide.


Cordialement

Rataxes64

cs_Jack · Answer

Salut

C'est la rentrée : une révision s'impose !

Equation d'une droite : y =  a.x + b
où y représentera la valeur réelle et x, la valeur lue
J'ai deux points :
2,3 = a.2,5 + b
5,6 = a.5,9 + b
Faisons la différence des deux équations pour éliminer la constante :
5,6 - 2,3 = a . (5,9 - 2,5)
donc a (5,6-2,3) / (5,9-2,5) 0,9705

Reprenons une des équations pour calculer B :
5,6 = 0,9705 . 5,9 + b
donc b 5,6 - (0,9705 . 5,9) -0,126

ce qui donne l'équation finale :
Valeur_Réelle = Valeur_Lue * 0,9705 - 0,126

Essai pour vérif :
5.9 * 0,9705 - 0,126 = 5.6  

Donc, pour une valeur réelle de 3.2 :
3.2 * 0,9705 - 0.126 =  2.98 

Et vous arrivez à mesurer 3 Pa ?
Ça fait pas bien lourd ...

Vala
Jack, =fr MVP VB
NB : Je ne répondrai pas aux messages privés

Le savoir est la seule matière qui s'accroit quand on la partage (Socrate)

CerberusPau · Answer

Bonjour Jack,

Merci pour ta réponse, on ne peut plus claire, car si je te comprends bien : fi de macro , on passe par des formules dans des cellules!

C'est bien sûr intéressant, mais je ne sais pas comment extraire le facteur de corrélation R² ; et dans mon appli, je dois m'assurer qu'il est suppérieur à une certaine valeur pour vérifier que les correctifs calculés soient acceptables.

Par ailleurs, Excel ne donne pas les mêmes résultats avec sa méthode "graphique"...??

Une petit exemple valant mieux qu'un long discours, je souhaiterais pouvoir joindre une feuille excel à cette réponse (en tant que fichier joint), mais je ne sais pas comment faire... il y a bien le bouton URL, mais je dois donner une adresse et non le nom de mon fichier...

Peux-tu me donner la marche à suivre STP, ou préfères-tu qu'on continue en MP.

Bien cordialement.

Rataxes64

PS: effectivement, 3Pa c'est pas grand chose ; mais c'est ENORME quand on travaille avec une précision entre 0,5 et 0,7 Pa sur la plage 0-5Pa... D'où la nécessité de "bons" facteurs correctifs!

cs_Jack · Answer

En ce qui concerne le coefficient de corrélation, comme expliquer clairement dans l'aide, ce coeff se calcule à partir des deux matrices de données à ta disposition : X et Y.

Dans ton cas, je suppose que l'axe des X est le temps.
Si ce temps est exprimé en date-heure, ta variable 'b' (décalage d'origine) sera elle aussi dans la même unité --> Pas vraiment pratique pour une réutilisation. Il faut donc, avant tout, générer une nouvelle série qui représentera un chiffre = le nombre d'échantillons (période de scrutation de ta mesure, en fait) dont le "zéro" correspondra à ta première mesure.
Sinon, le temps sous Excel, se représente par un Double, que les fonctions de formatage utilisent pour afficher des dates ou des heures.
Dans le VBA, les valeurs brutes des cellules (hors formatage) se lisent dans .Value2 et pas .Value.

Alors, quelle échelle pour les X ?

cs_Jack · Answer

En fait, calculer les a et b ne peuvent se baser que sur les données.
Or, une courbe de tendance générée par l'outil du graphisme, fait une série d'itération pour déplacer l'origine et la fin de la courbe afin de trouver un facteur de corrélation le plus important possible.
Refaire la même chose directement en VBA est faisable, mais surement long en temps de calcul - à approfondir.

[Catégorie modifiée VB6 --> VBA] Courbe de tendance linéaire

5 réponses

Votre réponse

Discussions similaires