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

Tonio Tonio · 1 Avril 2021

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

sylvanu · 1 Avril 2021

Bonjour Tonio,
Vous auriez un petit fichier test pour illustrer vos propos ?

job75 · 1 Avril 2021

Bonjour Tonio Tonio, bienvenue sur XLD, sylvanu,

Essayez en utilisant la fonction LOGREG.

A+

job75 · 1 Avril 2021

Voyez le fichier joint avec les données traitées de 2 manières différentes.

Tonio Tonio · 7 Avril 2021

sylvanu à dit:
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

sylvanu · 8 Avril 2021

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é.

job75 · 8 Avril 2021

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 R² sont très mauvais mais rien n'empêche d'utiliser les équations des courbes de tendance.

A+

sylvanu · 8 Avril 2021

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.

Tonio Tonio à dit:
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 · 9 Avril 2021

sylvanu à dit:
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.

job75 · 9 Avril 2021

Les données du post #9 sont les mêmes que celles du post #5.

sylvanu · 9 Avril 2021

Bonjour,

Tonio Tonio à dit:
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.

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

Tonio Tonio

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

Tonio Tonio

XLDnaute Nouveau

Pièces jointes

sylvanu

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

sylvanu

XLDnaute Barbatruc

Tonio Tonio

XLDnaute Nouveau

Pièces jointes

job75

XLDnaute Barbatruc

sylvanu

XLDnaute Barbatruc