[Résolu] Regression linéaire : problème si valeurs manquantes

[Sirca]

Bonjour,

J'aimerais effectuer une regression lineaire sur une plage de calcul (Epaisseur [ou Y] sur rayon [ou X]. Cette partie ne me pause pas de problème.

Par contre, imaginons que l'utilisateur de ce programme ne puisse pas faire les 30 mesures mais que 20. Comment faire pour calculer la fonction de ma courbe de tendance (polynomiale) et utiliser celle-ci pour completer les mesures manquantes ? Car si je soustrais une seule mesure (valeur Y), ma fonction droitereg ne marche plus...

Malheureusement pour la suite de mon tableur, je vais avoir besoin des 30 valeurs.


Merci d'avance pour votre réponse.

Ci-joint une partie de mon tableau excel.

 

[Misange]

Re : Regression linéaire : problème si valeurs manquantes

Bonjour
1) comment détermines tu que ta régression est une équation du 5° degré ? Si c'est parce que tu sais que la relation entre tes deux facteurs est de ce type, OK. Mais les exemples dans la vie réelle de relation du 5° ordre entre deux variables sont assez rares...

Si c'est juste parce que dans ton exemple c'est ce qui donne le meilleur coeff de régression, alors c'est une erreur. Je te propose de lire ceci :
Ce lien n'existe plus
Il faut toujours partir de ce que tu sais être le TYPE de relation entre tes deux variables pour choisir le type de régression appropriée et ne pas faire l'inverse.

Si tu n'as que 20 couples X,y de valeurs, tu calcules tes coeff de régression à partir de ces valeurs, tu en déduis l'équation de la régression et tu calcules les valeurs qui te manquent. Il faut que tes 20 couples soient saisis sans "trous". Tu seras donc au besoin contraint de procéder en deux temps, avec deux tableaux.

 

[Sirca]

Re : Regression linéaire : problème si valeurs manquantes

Bonjour,

En effet je me suis un peu emballé pour l'ordre 5. C'etait juste un test que j'avais fait avant pour des valeurs extrêmes lors des différentes prises de mesures.

Pour revenir au problème, j'aurais preferé pouvoir le faire "avec trous" ^^ En effet chaque personne qui va utiliser ce logiciel ne prendra pas le même nombre de points et pas aux mêmes endroits. Cela sera du aux différents process utilisés pour la "création" de cette épaisseur..

Par exemple quelqu'un pourrait avoir un profil d'épaisseur homogène sur les 10 premiers points puis un profil qui 'chute" après. Pour essayer d'avoir un gain de temps (une prise de valeur = 5-7 min), celle-ci voudra prendre 4points (sur les 10 de l'exemple) au départ puis être plus précis pour la suite.
Autre exemple, pour un profil homogène sur le rayon, 15 points suffiront.

Pour résumé j'ai deux problèmes importants :
- Retrouver 30 points à partir d'un nombre X de points (passage par une régression)
- Détermination de la régression => Je suis en train d'essayer de régler ce problème la en regroupant les différents process utilisés et en regroupant les différents profils d'épaisseur pour cadrer au mieux les besoins des utilisateurs (quitte à créer différentes feuilles excel)

Merci pour ta réponse en tout cas 🙂

 

[Misange]

Re : Regression linéaire : problème si valeurs manquantes

Je comprends bien ton problème mais certaines fonctions d'excel nécessitent des plages sans trous. Tu peux toujours parler gentiment à excel, je doute que ça le fasse changer d'avis 🙂
Il te reste la solution de développer une macro qui fasse le calcul en VBA.

 

[Sirca]

Re : Regression linéaire : problème si valeurs manquantes

Et moi qui pensait que la douceur et des mots doux résolvaient tous les problèmes :'(
Dommage j'aurais espérer une petite combine mais ca ne peut pas marcher à chaque fois.
Je vais voir pour une macro alors ! Dès que j'aurais commencé/finis les tutos 😛

Néanmoins si quelqu'un à une autre idée ou un début de macros possible, je suis preneur ! 🙂

 

[Sirca]

Re : Regression linéaire : problème si valeurs manquantes

Bonjour,

je me permets de revenir vers vous pour savoir comment je pourrais copier juste mes valeurs non nulles. J'ai essayé avec un filtre mais cela ne marche pas pour la suite : ma courbe de tendance va quand même utiliser les valeurs nulles pour son calcul.

Il y a t il un autre moyen de copier que mes valeurs non nulles? (via INDEX ou autre?)
ou puis-je sélectionner que les valeurs filtrées dans ma formule DROITEREG?

Ci-joint le fichier (feuil2 : valeurs rentrées / feuil1 :valeurs copié "sans trous"/courbe tendance/valeurs complétées)

Merci d'avance pour vos réponses

 

[Misange]

Re : Regression linéaire : problème si valeurs manquantes

Droitereg a besoin de plages continues pour travailler. Tu ne peux en aucun cas laisser des valeurs nulles ou des trous, même en filtrant ou en masquant les lignes vides.
Il te faut donc copier tes valeurs non nulles vers une autre plage et utiliser cette plage continue pour faire ton calcul.
IL est beaucoup plus simple à mon avis de demander aux utilisateurs de saisir eux-même dans une plage de données les couples X, Y, de faire le calcul de l'équation de régression à partir de là en sortant les paramètres (encore faut-il que tu saches le type de régression, j'y reviens...) et puis à partir de l'équation compléter ton tableau avec tous les X que tu souhaites représenter sur ton graphique.

 

[Sirca]

Re : Regression linéaire : problème si valeurs manquantes

Oui je suis d'accord avec toi. Cest pour ca que :
feuil2 : l'utilisateur va rentrer ses données.
feuil1 : je voudrais copier que les valeurs non nulles (automatiquement). Puis faire la courbe de tendance et recalculer mes points.

Mais je n'arrive pas à faire ce copier automatiquement ..!
Filtrage ce n'est pas bon.
INDEX : je n'arrive pasà mettre une condition pour sauter la valeur nulle et reprendre les valeurs d'après sans qu'il ne me prenne pas deux fois la même valeurs..
Certains ont réussi à priori avec la fonction SOMMEPROD mais cela me parait un peu bancale :/

Je peux pas me permettre de faire à l'utilisateur ce calcul car il aura déjà beaucoup de choses à faire ^^' Cette feuille est une partie courte du programme excel en général.

Merci d'avance si quelqu'un a une solution à mon problème !

 

[Sirca]

Re : Regression linéaire : problème si valeurs manquantes

Finalement j'ai commencé à trouver une solution à mon problème! 😎
Je vous mets ci-joint la solution. Il me reste plus qu'à aller chercher mon rayon correspondant à ma valeur d'épaisseur et de "cutter" après ma dernière valeur (si j'y arrive ^^)

 

[Misange]

Re : Regression linéaire : problème si valeurs manquantes

Je reprends encore une fois :
ton utilisateur ne DOIT pas rentrer les données dans un tableau prérempli par toi ! il doit le rentrer en mettant les coupmes X,Y dans deux conloinnes en entrant lui même et les X et les Y et sans laisser de valeurs vides ou nulles.

 

[ROGER2327]

Re : Regression linéaire : problème si valeurs manquantes

Bonjour à tous.

Si vous êtes sûr de votre surprenant modèle d'ajustement polynômial, quelques lignes de code et plages nommées peuvent être utiles. Un essai en pièce jointe.
Les données incomplètes sont prises dans l'onglet Onglet 1.
Elles sont traitées dans l'onglet Onglet 2. Les valeurs estimées sont ensuite renvoyées dans l'onglet Onglet 1.​

Bonne journée.


ℝOGER2327
#7493

Mardi 24 Tatane 141 (Saint Pissedoux, caporal des hommes libres - fête Suprême Quarte)
19 Thermidor An CCXXII, 0,2095h - gentiane
2014-W32-3T00:30:10Z


P.s. : Ne disposant pas d'Excel 2003, je n'ai évidemment pas vérifié le fichier .xls dans cette configuration. Merci de signaler les éventuels problèmes.

 

[Sirca]

Re : Regression linéaire : problème si valeurs manquantes

Bonjour,

Merci beaucoup pour ces "quelques lignes de code" en tout cas ca m'enlève une énorme épine du pied !! 🙂
Mon ajustement polynomial laissé sur ce site n'est pas le bon mais il est rectifié dans ma feuille excel!

Votre fichier m'indique l'erreur suivante "une erreur imprévue s'est produite" mais il fait bien le calcul, renvoit les bonnes valeurs...
Donc dans l'absolue, ce n'est pas grave. Je vais pouvoir continuer mon travail après avoir intégrer tout ça!

Encore merci de ton aide !

 

[ROGER2327]

Re : Regression linéaire : problème si valeurs manquantes

Re...

(...)
Votre fichier m'indique l'erreur suivante "une erreur imprévue s'est produite" mais il fait bien le calcul, renvoit les bonnes valeurs...
(...)

Difficile d'expliquer l'erreur à coup sûr ; toutefois, si les calculs s'effectuent correctement, il est probable que l'erreur survient dans cette partie du code :​

    Worksheets(Tbl(1)).Activate
    With Selection
      .Parent.ChartObjects(Grph).Activate
      ActiveChart.SeriesCollection(1).XValues = "='" & Tbl(1) & "'!" & [Tbl_2].Columns(2).Address
      ActiveChart.SeriesCollection(1).Values = "='" & Tbl(1) & "'!" & [Tbl_2].Columns(3).Address
      .Select
    End With

Cette partie met à jour le graphique de l'onglet Onglet 2. Elle peut être supprimée si ce graphique ne vous intéresse pas.
Pour fonctionner, il faut que le nom de ce graphique soit "Graphique 1". Si le graphique porte un autre nom, il faut modifier la ligne​

  Grph = "Graphique 1"

Sans voir le classeur réel, je ne saurais en dire plus...​

Bon courage.


ℝOGER2327
#7494

Mardi 24 Tatane 141 (Saint Pissedoux, caporal des hommes libres - fête Suprême Quarte)
19 Thermidor An CCXXII, 2,8417h - gentiane
2014-W32-3T06:49:12Z

 

[Sirca]

Re : [Résolu] Regression linéaire : problème si valeurs manquantes

Rebonjour,

En effet l'erreur venait bien de la mise à jour du deuxième graphique !

Merci encore pour ton aide précieuse et rapide! Cela marche niquel sur mes feuilles excel 🙂

Bonne journée !

 

[ROGER2327]

Re : [Résolu] Regression linéaire : problème si valeurs manquantes

Re...

Puisque la proposition vous convient, autant donner une version plus propre du code précédent, très brut de décoffrage.
Dans le classeur joint, vous trouverez un code commenté permettant une adaptation plus simple.​

Bonne journée.


ℝOGER2327
#7497

Jeudi 26 Tatane 141 (Saint Glé, neurologue-aliéniste - fête Suprême Quarte)
21 Thermidor An CCXXII, 0,4150h - carline
2014-W32-5T00:59:46Z