XL 2019 Régression à 2 inconnues

Onecraft

XLDnaute Nouveau
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.
1678729633073.png


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


Merci par avance :)
One
 

Onecraft

XLDnaute Nouveau
Bonjour Dranreb,

C'est génial!! La représentation graphique va beaucoup aider :) Et effectivement, si on peut se passer de VBA ce serait mieux. Pourriez vous me fournir un tel excel ?

J'ai une idée pour recoller aux points expérimentaux!!
 

Dranreb

XLDnaute Barbatruc
Bonjour @bsalv.
Comme expliqué au poste #8, je déteste ce DROITEREG à cause de la disposition et de l'ordre dans lequel il restitue ses résultats, qui oblige à les reporter ailleurs, comme vous l'avez fait, d'ailleurs, pour pouvoir appliquer un PRODUITMAT dessus.
Ma fonction perso SolMoindresCarrés n'est quand même pas trop grosse pour que ce soit avantageux de s'en passer.
Remarque: quitte à s'en passer autant se passer aussi des coefficients, avec, au lieu de :
Code:
=PRODUITMAT(TbDon[@[%MEG]:[Cst]];Coefficients)
en TbDon[Tendance], ceci :
Code:
=TENDANCE([Solubilité];TbDon[[%MEG]:[T²]];TbDon[@[%MEG]:[T²]];VRAI)
 
Dernière édition:

bsalv

XLDnaute Occasionnel
@Danreb, je n'avais pas lu toute la conversation, sorry.
on peut utiliser les résultats de "Linest" pour créer en memoire tous les coefficients nécessaires.
Code:
Sub Teste()
     Dim cSource, cDest, aOut, aReg, i, s

     With Sheets("Abaque")
          Set cSource = Range("TbDon[[%MEG]:[T²]]")     'vos X
          Set cDest = Range("TbDon[Solubilité]")     'votre Y
          aReg = WorksheetFunction.LinEst(cDest, cSource, 1, 1)     'matrice droitereg
          .Range("L10").Resize(UBound(aReg), UBound(aReg, 2)).Value = aReg     'coller dans la feuille

          ReDim aOut(1 To UBound(aReg, 2) + 2, 1 To 2)     'matrice pour Output
          For i = 1 To UBound(aReg, 2) - 1
               aOut(i, 1) = cSource(0, i).Value     'nom X
               aOut(i, 2) = aReg(1, UBound(aReg, 2) - i)     'Coeff pour ce X
               s = s & "   " & IIf(aOut(i, 2) >= 0, "+", "") & aOut(i, 2) & " * " & aOut(i, 1)
          Next

          aOut(UBound(aReg, 2), 1) = "Cst"
          aOut(UBound(aReg, 2), 2) = aReg(1, UBound(aReg, 2))

          aOut(UBound(aOut), 1) = "R2"
          aOut(UBound(aOut), 2) = aReg(3, 1)
          s = s & IIf(aOut(UBound(aReg, 2), 2) >= 0, "+", "") & aOut(UBound(aReg, 2), 2) & "    R2=" & Format(aReg(3, 1), "#.##")

          MsgBox s
          .Range("L17").Resize(UBound(aOut, 2), UBound(aOut)).Value = Application.Transpose(aOut)     'coller horizontal
          .Range("L20").Resize(UBound(aOut), UBound(aOut, 2)).Value = aOut     'coller vertical

     End With

End Sub
 

Pièces jointes

  • SolEquMCarOnecraft (2).xlsm
    60.4 KB · Affichages: 0

Dranreb

XLDnaute Barbatruc
Bon, je vais réfléchir à utiliser WorksheetFunction.LinEst dans ma fonction SolMoindresCarrés. Elle renvoie toujours son résultat avec une disposition et un ordre idiots mais qui peuvent se corriger aisément en VBA et évite en contrepartie d'avoir à constituer la matrice carrée pour la résolution par la méthode des moindres carrés …
 
Dernière édition:

Dranreb

XLDnaute Barbatruc
Je tombe sur un truc: WorksheetFunction.LinEst renvoie un tableau d'une seule dimension s'il n'y a qu'une seule colonne de valeurs connues. C'est d'ailleurs pour ça que la fonction DROITEREG d'Excel, qui l'utilise, renvoie son résultat en ligne de cellules. Est-ce à dire qu'elle ne supporterait pas l'étude de plusieurs tendances selon plusieurs séries de valeurs connues appliquées aux mêmes termes ?
À noter que je n'ai pas testé si ça marchait avec ma fonction actuelle, mais elle est écrite de façon à pouvoir le supporter théoriquement …
 
Dernière édition:

Dranreb

XLDnaute Barbatruc
Non je parle de plusieurs colonnes de valeurs Y, pour déterminer plusieurs jeux de coefficients, donc. Possibilité supposée prévue dans la façon dont est écrite ma fonction SolMoindresCarrés actuelle, mais non testée, et non utilisée dans le problème du demandeur.
 
Dernière édition:

bsalv

XLDnaute Occasionnel
j'ai 60 ans, donc mes connaissances théoriques datent de +40 ans, Si vous pouvez me montrer un exemple de cette problème et la résoudre dans une feuille, ce n'est pas difficile de faire le même truc avec VBA. Cela est le max que je peux faire, mes compétences sont limitées à ce niveau. Je ne connait pas les possibilités et les limitations de votre fonction "SolMoindresCarrés"
 

Dranreb

XLDnaute Barbatruc
Pour le faire avec WorksheetFunction.LinEst il suffirait de l'exécuter pour chaque colonne de Y.
Ça ne vaut pas le coup d'ajouter ça pour le peu de besoins qui risquent de se présenter, mais ça vient naturellement en le faisant par construction de la matrice carré et des colonnes de Y correspondantes avec ensuite une seule inversion de la dite matrice et enfin sa multiplication par ces colonnes.
Je vais donc réserver à des cas particuliers le remplacement de cette technique par LinEst.
 
Dernière édition:

bsalv

XLDnaute Occasionnel
bonjour,
méthode "quick and dirty" pour sélectionner une combinaison de vos variables. On doit créer une matrice avec ces variables mais on peut améliorer cette sélection ... . (Si nécessaire, je le ferai plus tard )
Macro "Boucler"
 

Pièces jointes

  • SolEquMCarOnecraft (2).xlsm
    54.3 KB · Affichages: 2
Dernière édition: