XL 2019 Régression à 2 inconnues

[Onecraft]

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

 

[Onecraft]

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]

Je n'ai pas proposé de nous passer de VBA, seulement de nous passer de la fonction applicative Termes, et de mettre des formules à la place dans le tableau TbDon.

 

[Onecraft]

Si c'est possible, oui, ce serait mieux J'essaie d'appliquer la formule avec les coefficients mais je n'y arrive pas (je ne trouve pas de bons résultats), sauf quand le taux de MEG = 0. J'ai mal compris quelque chose ?

 

[Dranreb]

Ben c'est la formule en colonne Tendance avec PRODUITMAT

 

[Dranreb]

La version qui n'utilise plus la fonction Termes.

 

[bsalv]

avec la fonction "Droitereg", calculation sans VBA

 

[Dranreb]

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 :

=PRODUITMAT(TbDon[@[%MEG]:[Cst]];Coefficients)

en TbDon[Tendance], ceci :

=TENDANCE([Solubilité];TbDon[[%MEG]:[T²]];TbDon[@[%MEG]:[T²]];VRAI)

 

[bsalv]

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

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

 

[Dranreb]

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 …

 

[Dranreb]

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 …

 

[bsalv]

re,
je ne comprend pas, s'il n' a qu'un variable x, cela fonctionne encore, non ?

 

[Dranreb]

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.

 

[bsalv]

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]

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.

 

[bsalv]

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"