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

 

[bsalv]

avec toutes les combinaisons

 

[Dranreb]

Bonjour.
Vous n'avez pas compris, je crois, pourquoi je ne reproposerait plus, sauf ici, une réécriture de ma fonction SolMoindreCarrés utilisant WorksheetFunction.LinEst.
Supposons qu'on ait 3 grandeurs complètement indépendantes à ceci près qu'elles sont mesurées aux mêmes instants et qu'elle sont présumées les trois avoir une dérive linéaire dans le temps mais avec des variations saisonnières.
Ma fonction actuelle est écrite de sorte à permettre le calcul des trois jeux de coefficients en une seule formule matricielle.
Tout naturellement en disant que je pourrais la simplifier en utilisant LinEst, je m'attendais à pouvoir faire la même chose avec, et là, c'est cuit, apparemment.

 

[bsalv]

il y avait le problème "date" pour la colonne A. Quand on utilise ".value2" au lieu de ".Value" (=date>>>"double"), alors c'est facile.
Les 2 dernières lignes, vous pouvez les supprimer.

 

[Dranreb]

Oui c'est bien ce que je pensais, on ne peut pas éviter de lui faire refaire 3 fois la génération de la matrice carrée puis son inversion …
Curieux, je n'ai pas de problème avec la date chez moi en utilisant .Value.

 

[bsalv]

Curieux, je n'ai pas de problème avec la date chez moi en utilisant .Value.

Private Sub CalcMatMoindresCarrés(Mat() As Double, TY() As Double, TDonX(), TDonY())
     Dim NbInc As Integer, NbY As Integer, LDon As Integer, L As Integer, X As Double, C As Integer

Vous n'avez pas ce problème parce que vous faites cette conversion avec ces déclarations, surtout "X as Double", mais surtout, vous n'utilisez pas "Worksheetfunction.Linest", qui a besoin des doubles.

C'est louable, chapeau, mais je ne comprend pas pourquoi vous imitez la fonction native Excel "Linest" avec "SolMoindresCarrés". Oui, je dois le refaire 3 fois et inverser la séquence, mais si on inspecte ma macro, il n'y a qu'une ligne pour la regression et le reste (12 lignes) est pour construire la matrice finale, donc une charge minimale sur le processeur.

On peut calculer tout avec Excel mais je me demande par exemple la nécessité du variable "T2" qui n'ajoute que 0.00005 à R2 (de 0,99726 à 0,99731). (voir #31) Je ne connais plus la théorie, mais il faut tester si cela a une significance minimale. Ce sont les autres chiffres de aReg pour vérifier cela, mais j'ai oublié le système.

 

[Dranreb]

Cette version simplifiée pour une seule colonne de Y marche avec des dates :

Function SolMoindresCarrésLinEst(ByVal DonX, ByVal DonY, Optional ByVal Constante As Boolean = True)
   Dim TRég(), TSol(), LCst As Integer, L As Integer
   TRég = WorksheetFunction.LinEst(DonY, DonX, Constante)
   ReDim TSol(1 To UBound(TRég), 1 To 1)
   LCst = UBound(TSol, 1) - -Constante + 1
   For L = 1 To LCst - 1: TSol(L, 1) = TRég(LCst - L): Next L
   If Constante Then TSol(L, 1) = TRég(L)
   SolMoindresCarrésLinEst = TSol
   End Function

C'est principalement à cause de l'ordre dans lequel il renvoie les coefficients. La disposition, à la rigueur, ne serait guère gênante, il suffirait de prendre ensuite SOMMEPROD plutôt que PRODUITMAT.
Je n'ai pas compris à quoi vous faites allusion T2 ? Voulez vous dire T² ? Le carré de la température joue peu en effet mais c'est aussi parce que son ordre de grandeur va jusqu'à 10000. Mais il joue, c'est sûr, rien d'autre n'expliquerait que les courbes sont légèrement incurvées comme des paraboles !

 

[bsalv]

il n'y a pas de problème de date avec Linest en VBA quand le DonX est une plage, mais dès qu'on utilise une matrice, tout le contenu doit être Double, Date est interdit !
(voir ici dessous) Ici DonXX est une plage, mais avec Donx = DonXX.Value on le lit dans une matrice et les dates restent des dates, mais en utilisant Value2 les dates (et currency) sont conversé en double et à partir de ce moment, tout fonctionne, donc plage avec date = okay, matrice avec double = okay, matrice avec date = faux.
Ce sont ces petits détails qui provoquent des maux de tête ...

Function SolMoindresCarrésLinEst(ByVal DonXX, ByVal DonY, Optional ByVal Constante As Boolean = True)
   Dim TRég(), TSol(), LCst As Integer, L As Integer
   Donx = DonXX.Value2  
   ...

Concernant ce T2, (je ne sais pas comment en T², maintenant je l'ai collé), on voit que c'est légèrement incurvées, mais il y a des testes statistiques à faire pour vraiment approuver qu'ajouter le variable T² a une influence suffisant et que ce n'est pas un coup de chance. Mais, fin de discussion, je me rappelle plus ces outils, donc, affecté ...

 

[Dranreb]

Chez moi ça marche écrit comme ça avec les dates.
Pourtant j'ai bien fini par intégrer à mon SolMoindresCarrés l'utilisation de WorksheetFunction.LinEst quand DonY n'a qu'une colonne, en y spécifiant un TDonX() As Variant récupérés de DonX.Value. Chez vous ça ne marche pas non plus si ce sont des Currency ?
De toute façon que ce soit Date ou Currency dans Excel c'est toujours en réalité enregistré en Double.

 

[bsalv]

dans votre fichier, si vous avez plus qu'une série Y, vous n'utilisez plus "Linest" ! Donc en passant vers l'autre macro et le variant MAT(), qui est "double", il n'y a pas de problème.

Je l'ai modifié votre "SolMoindresCarrés" pour que ça marche avec toutes les séries avec Linest.
Ici, cela n'a pas importance, mais dans la cellule R2, la regression sans constante.

 

[Dranreb]

Ah oui, j'avais complètement oublié ça. Et vous avez raison, avec Value2 ça marche.
Corrigé.
Remarque: Ça ne peut pas se voir sur le graphique mais sans constante les courbes passent près du
1er janvier 1900 !

 

[bsalv]

Remarque: Ça ne peut pas se voir sur le graphique mais sans constante les courbes passent près du
1er janvier 1900 !

je le sais, mais c'était pour complèter la méthode, qu'on pouvait aussi utiliser cette option, inutile dans ce cas ici.