XL 2019 Régression à 2 inconnues

Onecraft · 13 Mars 2023

Bonjour,

Malgré mes recherches, je ne trouve pas comment créer une équation (régression) à 2 inconnues.
J'aimerais pouvoir, à partir de données expérimentales telles que ci-après (solubilité du Na2SO4 en fonction de la température et de la teneur en éthylène glycol), créer une équation qui me permettre de retrouver cette solubilité selon l'inconnue x=température et y=tenneur en éthylène glycol.

Savez vous si faire une telle régression est possible ? Quelles seraient les fonctions qui permettraient cela ?

Merci par avance
One

sylvanu · 13 Mars 2023

Bonsoir Onecraft,
Et donc pour tenter de vous répondre, on va ressaisir ce tableau sous XL pour pouvoir tester.... ou ne pas répondre.
Vous n'auriez pas son petit frère sous le bon format par ex ? Car mon XL refuse de traiter des png.
En fait il peut exister des solutions, mais cela dépend de l'interdépendance des deux variables.

Onecraft · 13 Mars 2023

Bonsoir Sylvanu,

Excel manque vraiment de fonctionnalités ma foi!!! Vous avez raison, je finis de rentrer l'intégralité des valeurs demain (il y en a plus).

sylvanu · 13 Mars 2023

Re,
De toute façon vous seriez obligé à un moment ou à un autre de la faire. Donc je vous évite de procrastiner.

Je pense qu'il y a une solution, car si on trace à T° constante ( par ex 25°C et 100°C ) alors on voit que la variation est linéaire en fonction du % d' éthylène glycol. Donc on doit pouvoir trouver Y en fonction de T° et de %EG.

Dranreb · 14 Mars 2023

Bonsoir.
Pourriez vous mettre vos données en 3 colonnes X, Y => Z ?
Avec ça il serait possible de calculer 4 coefficients, un pour X, un pour Y, un pour X×Y et enfin une constante.
DROITEREG devrait pouvoir le faire, mais je préfère utiliser une petite fonction perso en VBA.

Dranreb · 14 Mars 2023

Une ébauche.
J'ai mis des valeurs aléatoires.
Vous pouvez changer dans les titres "X", "Y" et "Z".

Onecraft · 14 Mars 2023

Bonjour

En pièce jointe, les valeurs. Je les ai mises sous deux formes. Sur excel, il faut forcément trouver une relation linéaire pour pouvoir faire une équation à 2 inconnues ?

Pour le fichier, c'est cela que je recherche. Je vais y intégrer mes valeurs et voir ce que j'arrive à obtenir. Merci !!

Edit : C'est une solubilité avec un point eutectique, il semblerait qu'enlever le premier point soit meilleur pour avoir une régression fiable dans la 2 eme partie de la courbe

Dranreb · 14 Mars 2023

Vous pouvez aussi utiliser la fonction TENDANCE.
Il faut poser X * Y en plus mais 1 n'est plus nécessaire pour la constante.
Noter la disposition aberrante des résultats de la fonction DROITEREG qui ne permet ni PRODUITMAT parce que les coéfficients trouvés sont disposés horizontalement, ni SOMMEPROD parce qu'ils sont disposés de droite à gauche par rapport aux termes donnés.

Dranreb · 14 Mars 2023

Ça ne semble pas être la bonne forme de tendance en ajoutant le terme x * y

Dranreb · 14 Mars 2023

Onecraft à dit:
Sur excel, il faut forcément trouver une relation linéaire pour pouvoir faire une équation à 2 inconnues

Il n'y a qu'une inconnue mais deux données
Oui il y a forcément une relation linéaire entre les fonctions (pas forcément linéaires) des données d'entrées et les coefficients qu'on leur applique. Mais c'est vrai que ce nom DROITEREG n'est pas très heureux car il suggère qu'il ne permet de calculer que des coefficients de droites. Au moins TENDANCE fait sa cuisine de la même façon des deux cotés, dans les connus et les nouveaux. Mais il faut poser tous les termes, donc toutes les fonctions des données d'entrée.

Dranreb · 14 Mars 2023

Voyez-vous qu'est ce qui pourrait convenir comme ensemble de fonctions combinant les deux données d'entrée, solubilité et température, affectées de coefficients appropriés à calculer et dont la somme donnerait alors votre pourcentage ?
Sans définir cela on n'avancera pas.

Onecraft · 14 Mars 2023

Je souhaite avoir la solubilité en fonction du %massique en MEG et de la température. Une fonction LOG permet, pour un pourcentage d'éthylène glycol donnée, de très bien coller aux points expérimentaux (solub = f(temperature)). Une fonction qui combinerait le % d'ethylene glycol et la température ? Je ne vois pas à première vue, ce sont des paramètre indépendants mais je peux me débrouiller pour en trouver une.

EDIT : en pièce jointe, les points expérimentaux rectifiés (retrait de la série de points a 25°C)

Dranreb · 14 Mars 2023

Ah, c'est la solubilité, pas la teneur qui est le résultat à trouver ?
C'est vrai que vos données n'étaient vraiment pas clair.
Peut on la reporter à droite plutôt qu'entre les deux ?
Et donc vous disiez, c'est quoi la somme des fonctions de %Mass et Temp affectées de coefficients
à trouver ?

Si on en reste à VBA, compte tenu des aberrations de la fonction DROITEREG, définissez les fonctions dans la Sub CalcTermes du module UsTermes.
Vous pouvez en mettre plus de 4 si vous voulez. 1 à la fin c'est pour déterminer une constante qui s'ajoute à l'ensemble.

On en est là avec le terme %MassEG×Temp°C qui ne convient pas …

Dranreb · 14 Mars 2023

Il y a du mieux en ajoutant les termes des carrés des deux données.
Des Log népériens pourraient être appliqués sur les données, si vous pensez que ça conviendrait, mais moi j'ai plutôt vaguement vu des décroissances exponentielles …

Dranreb · 15 Mars 2023

Bonjour.
Avec un graphique pour mieux voir la précision de la tendance.
Préfèreriez vous, plutôt que d'être calculés en VBA, que les termes supplémentaires le soient par des formules en nouvelles colonnes du tableau TbDon ?

XL 2019 Régression à 2 inconnues

Onecraft

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

Onecraft

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

Pièces jointes

Onecraft

XLDnaute Nouveau

Pièces jointes

Dranreb

XLDnaute Barbatruc

Pièces jointes

Dranreb

XLDnaute Barbatruc

Pièces jointes

Dranreb

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

Onecraft

XLDnaute Nouveau

Pièces jointes

Dranreb

XLDnaute Barbatruc

Pièces jointes

Dranreb

XLDnaute Barbatruc

Pièces jointes

Dranreb

XLDnaute Barbatruc

Pièces jointes