XL 2016 Régression linéaire ou droitereg forcer les coefficients en positif

Tonio Tonio

XLDnaute Nouveau
Bonjour à tous,

Dans un travail statistique, je dois définir la teneur en élément d'alliage de catégories de ferrailles grâce à l'analyse chimique de ce mélange de ferraille une fois fondu.

En gros deux possibilités; passer par la droite de régression (droitereg) ou utiliser le module de régression linéaire directement.

Mais voilà, ces deux méthodes me donnent le même résultat (normal) mais avec des coefficients négatifs. Cette donnée est correcte mathématiquement parlant, par contre il est impossible d'avoir une teneur négative en élément d'alliage.

En fouillant dans ces deux fonctions et dans XLstat, je n'ai trouvé aucun moyen de dire à excel de trouver la meilleure corrélation avec des coefficients strictement positifs.
Connaissez vous un moyen pour arriver à mes fins?

Merci d'avance
Tonio
 

Tonio Tonio

XLDnaute Nouveau
Bonjour Tonio,
Vous auriez un petit fichier test pour illustrer vos propos ?
Voici mon exemple
Dans le tableau, colonne A, ce que j'ai obtenu. colonnes B à G les quantités de produit ajoutées pour avoir ce résultat.
L'étape suivante est de calculer le régression linéaire de telle sorte que le résidu soit nul (constante nulle)
y=XA*MA+XB*MB+XC*MC+XD*MD+XE*ME+XF*MF
avec MA, MB, MC, MD, ME, et MF les données du tableau dans les colonnes B à G et XA, XB, XC, XD, XE, XF les facteurs positifs (notion de teneur qui ne peut être négative) que l'on cherche

Le résultat des XA, XB, XC, XD, XE, XF sont reportés dans la ligne 2.

Le calcul de régression est bon. Le graphique permettant de vérifier le y en fonction du y calculé avec les données de de la régression (on obtiens y=x, parfait!). Par contre le coefficient XC est négatif et ne peut l'être.

Ma question est de savoir s'il y a un moyen de faire une régression avec une liberté sur la nature des coefficients. Une case à cocher pour forcer les coefficients en positif par exemple, ou en passant par une étape annexe?

Je dispose d'Excel et aussi XL stat il y a donc possibilité de passer par XL Stat si besoin.

Merci d'avance pour l'aide
 

Pièces jointes

  • Regression linéaire exemple.xlsx
    134.4 KB · Affichages: 6

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour TonioTonio, Job,
En PJ quelques travaux.
Lorsqu'on dispose d'un grand nombre de mesures et que l'on désire en tirer des règles générales, il faut absolument analyser la cohérence des données.

1- J'ai extrait de votre base tous les Y où A=B=C=D=E=F=0 c'est à dire sans rien. ( onglet Tout nul )
J'obtiens un Y compris entre 518 et 533000 soit une erreur de -99% et +687% aux extremums.

2- J'extrait toute valeur de Y quand seul E est utilisé ( onglet E seul ) j'obtiens des erreurs de +/-50%

J'en déduis donc que vos mesures ne sont pas reproductibles, et qu'il ne sera pas possible d'en tirer une quelconque régression qui soit réaliste.

Pour illustrer mon propos, regardez l'onglet ALEA.
Je tire 1000 échantillons par =ALEA(), donc 1000 nombres compris entre 0 et 1 avec une distribution uniforme.
Je demande la moyenne, XL trouve 0.5, évidemment.
Je lui demande l'écart type, il trouve 0.29. Car mathématiquement on peut toujours calculer un écart type.
Je calcule la distribution à +/-3 écart type et je trouve .... 1.38 / -0.37.
Ce qui est mathématiquement correct, mais physiquement aberrant.

Vous vous heurtez au même phénomène. Vos mesures ne sont pas reproductibles. toute tentative de recherche de corrélation sera voué à l'échec car il y a trop de dispersion sur les différent paramètres.
Désolé.
 

Pièces jointes

  • Regression linéaire exemple.xlsx
    238.3 KB · Affichages: 3

job75

XLDnaute Barbatruc
Bonjour Tonio Tonio, sylvanu,

Je n'ai pas essayé de comprendre ce que vous voulez faire avec vos statistiques.

J'ai simplement créé 6 graphiques avec les droites de régression linéaire.

Aucune valeur de ces droites n'est négative.

Bien sûr les coefficients de détermination sont très mauvais mais rien n'empêche d'utiliser les équations des courbes de tendance.

A+
 

Pièces jointes

  • Regression linéaire(1).xlsx
    177.4 KB · Affichages: 7

sylvanu

XLDnaute Barbatruc
Supporter XLD
Re,
@job,
XL donne toujours des résultats, c'est dans sa nature, il applique les équations mathématiques.

Cependant à l'analyse on voit qu'il y a une telle dispersion des mesures que de telles courbes n'ont peu d'intérêts au niveau réel.
je dois définir la teneur en élément d'alliage de catégories de ferrailles grâce à l'analyse chimique de ce mélange
Au vu des résultats, TonioTonio n'arrivera pas a extraire la composition de son métal fondu avec ces mesures, il y a beaucoup trop d'incertitudes.
 

Tonio Tonio

XLDnaute Nouveau
Bonjour TonioTonio, Job,
En PJ quelques travaux.
Lorsqu'on dispose d'un grand nombre de mesures et que l'on désire en tirer des règles générales, il faut absolument analyser la cohérence des données.

1- J'ai extrait de votre base tous les Y où A=B=C=D=E=F=0 c'est à dire sans rien. ( onglet Tout nul )
J'obtiens un Y compris entre 518 et 533000 soit une erreur de -99% et +687% aux extremums.

2- J'extrait toute valeur de Y quand seul E est utilisé ( onglet E seul ) j'obtiens des erreurs de +/-50%

J'en déduis donc que vos mesures ne sont pas reproductibles, et qu'il ne sera pas possible d'en tirer une quelconque régression qui soit réaliste.

Pour illustrer mon propos, regardez l'onglet ALEA.
Je tire 1000 échantillons par =ALEA(), donc 1000 nombres compris entre 0 et 1 avec une distribution uniforme.
Je demande la moyenne, XL trouve 0.5, évidemment.
Je lui demande l'écart type, il trouve 0.29. Car mathématiquement on peut toujours calculer un écart type.
Je calcule la distribution à +/-3 écart type et je trouve .... 1.38 / -0.37.
Ce qui est mathématiquement correct, mais physiquement aberrant.

Vous vous heurtez au même phénomène. Vos mesures ne sont pas reproductibles. toute tentative de recherche de corrélation sera voué à l'échec car il y a trop de dispersion sur les différent paramètres.
Désolé.
Bonjour Sylvanu,

oui c'est parce que je n'ai pas envoyé la base complète. Certaines données sont considérées comme connues. Voici une nouvelle version en les considérants inconnues.

Je prend note de la non reproductibilité, mais dans l'application c'est très compliqué. On parle ici d'analyse de ferrailles qui viennent de plusieurs fournisseurs aux 4 coins de l'Europe.
 

Pièces jointes

  • Regression linéaire exemple.xlsx
    134.4 KB · Affichages: 5

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour,
Je prend note de la non reproductibilité, mais dans l'application c'est très compliqué.
Si vos mesures sont non reproductibles alors vos tentatives de régression sont vouées à l'échec.
Si, avec un métal pur, vous obtenez un Y compris entre 500 et 500 000, alors que pur +E vous obtenez entre 45000 et 120000, vous voyez bien qu'il vous sera impossible de faire un quelconque calcul de composition.

En fait votre bruit de sortie est largement supérieur au signal d'entrée, votre système est chaotique.
Sauf à connaitre d'autres lois qui régissent cette mesure, il est vain d'imaginer s'en sortir.
 

Statistiques des forums

Discussions
311 730
Messages
2 081 981
Membres
101 855
dernier inscrit
alexis345