Microsoft 365 Problème #valeur DROITEREG() avec RECHERCHEX() en paramètres de valeurs

Atiroocky · 26 Janvier 2023

Bonjour,

J’ai besoin de réaliser toute une série de régressions linéaires sur des tableaux de taille conséquente.
Ça fonctionne très bien quand je lui indique en paramètres, une liste de valeurs.
Mais il m’est impossible de rentrer une liste "manuellement"

+DROITEREG(A1:A3;B1:B3) -> OK

Mon but est d’incorporer une liste de deux nombres, dont les valeurs sont issues d’un résultat de la fonction RECHERCHEX(Tableau), et RECHERCHEX(FILTRE(Tableau)).
Ça donne un truc du genre

+DROITEREG(RECHERCHEX(FILTRE):RECHERCHEX(FILTRE);RECHERCHEX(…):RECHERCHEX(…)) -> erreur #Valeur

En y regardant de plus près, en extrayant la partie "RECHERCHEX(…):RECHERCHEX(…)" dans une autre cellule, ça me renvoie bien une plage de valeur.
En revanche la partie RECHERCHEX(FILTRE):RECHERCHEX(FILTRE) me renvoie toujours une erreur, bien que les termes RECHERCHEX(FILTRE) incorporés indépendamment dans des cellules distinctes me retournent chacun une valeur correcte. Donc tous les sous-ensemble de fonctions sont bons et retournent bien une valeur. Alors d’où vient l’erreur ?
J’ai trouvé une parade, mais c’est lourd :

CNUM(FRACTIONNER.TEXTE(CONCAT(RECHERCHEX(FILTRE);"-";RECHERCHEX(FILTRE));"-")

J’aimerai trouver une syntaxe plus élégante et moins lourde car je vais devoir appliquer ce type de formule sur des centaines de milliers de lignes…

Merci à vous.

Gégé-45550 · 26 Janvier 2023

Bonjour,
avez-vous essayé INDIRECT(RECHERCHEX(FILTRE):RECHERCHEX(FILTRE)) ?
en transformant le résultat de RECHERCHEX(FILTRE):RECHERCHEX(FILTRE) en texte car INDIRECT attend du texte en paramètre.
Cordialement,

Hasco · 26 Janvier 2023

Atiroocky à dit:
J’aimerai trouver une syntaxe plus élégante et moins lourde car je vais devoir appliquer ce type de formule sur des centaines de milliers de lignes…

Bonjour et bienvenue sur XLD,

Nous nous aimerions un fichier exemple anonymisé et sans données confidentielles pour ne pas naviguer à tâtons.

Cordialement

Atiroocky · 26 Janvier 2023

Merci pour vos retours.
Je vous joins le fichier en p.j.
J’ai des données qui forment une courbe dont la régression linéaire, même avec un polynôme, ne donne pas satisfaction.
L’idée est de faire une droite linéaire entre chaque point. Plutôt d’avoir une seule équation pour toute la courbe, j’en ai une par tronçon.

Hasco · 26 Janvier 2023

Re,

Merci de ce fichier. Je n'ai pas 365 et ne suis pas suffisamment matheux pour vous donner une solution au moins correcte.

Si vous n'obtenez pas de réponse, faites remonter votre fil en répondant à votre propre discussion. Regardez, si vous ne pouvez pas éclaircir certains points de la questions qui pourraient paraître obscures.

Cordialement

Gégé-45550 · 26 Janvier 2023

Bonsoir,
à tester ... sans garantie
Toute la question consiste à renvoyer en référence une plage au format texte plutôt que 2 valeurs.
La formule est "pire" encore à lire que la vôtre bidouillée mais elle ne renvoie pas d'erreur (contrairement à la vôtre en D35) et elle donne des résultats identiques au début et différents ensuite (sans doute le calcul des coordonnées des deux valeurs en paramètres ... qu'il vous faudra vérifier).
Cordialement

Atiroocky · 27 Janvier 2023

Gégé-45550 à dit:
Bonsoir,
à tester ... sans garantie
Toute la question consiste à renvoyer en référence une plage au format texte plutôt que 2 valeurs.
La formule est "pire" encore à lire que la vôtre bidouillée mais elle ne renvoie pas d'erreur (contrairement à la vôtre en D35) et elle donne des résultats identiques au début et différents ensuite (sans doute le calcul des coordonnées des deux valeurs en paramètres ... qu'il vous faudra vérifier).
Cordialement

Merci pour ce retour.
Effectivement ça alourdit pas mal la formule. En revanche, la modification du paramètre de T°C renvoie une erreur #NA.
De mon côté en restant sur cette approche (FRACTIONNER.TEXTE(CONCAT())), j’ai corrigé l’erreur qui provenait en fait du résultat d’une division nulle ( 1/(RECHERCHEX(par valeur sup) - RECHERCHEX(par valeur inf)), puisque les résultats RECHERCHEX renvoyaient la même valeur.
Par ailleurs, mon réaliser mon calcul, je n’ai pas vraiment besoin d’une régression linéaire. Je peux me débrouiller avec des produits en croix, mais toujours besoin d’avoir recours à la fonction RECHERCHEX. Je verrai quelle formule s’en sort le mieux sur 3 millions de cellules…

ps : J’aimerai tellement installer la version 64bit car j’ai souvent fait planter Excel par manque de RAM, mais pour l’avoir fait une fois, j’avais beaucoup de macros indispensables qui ne fonctionnaient plus. J’avais été obligé de revenir en arrière.

Dranreb · 27 Janvier 2023

Bonjour.
Ça se fait très bien une tendance en ligne brisée. Il suffit de connaitre les valeurs x des points en dehors des deux extrêmes et de prévoir, en plus du terme x et de 1 (constante), pour chacun de ces points xn un terme Abs(x - xn).
Je préfère l'écrire en VBA pour n'avoir pas à poser tout ça, et parce que bien que plus puissants que je ne l'avais compris les outils EXCEL ne me satisfont guère. Ils ont une bizarre tendance à inverser l'ordre des coefficients calculés notamment. Même le nom DROITEREG prète à confusion je trouve en laissant croire qu'il n'est possible de calculer que des coefficients de droites, alors qu'elle peut calculer des coefficients pour n'importe quelle série de fonctions pas forcément linéaires. Une petite fonction perso de service SolMoindresCarrés et une autre fonction perso applicative pour poser les termes et c'est bon !
Mais je ne peux pas exploiter votre classeur parce que j'ai des #NOM? sur _xlfn.XLOOKUP de sorte que je n'arrive pas à comprendre où est la série des x,y dont on veut éventuellement une tendance en ligne brisée.
Alors je joint ça, que j'avais fait pour seulement 2 points intermédiaires. il est même possible de faire calculer par le solveur les x de ces points !

Gégé-45550 · 27 Janvier 2023

Atiroocky à dit:
Merci pour ce retour.
Effectivement ça alourdit pas mal la formule. En revanche, la modification du paramètre de T°C renvoie une erreur #NA.
De mon côté en restant sur cette approche (FRACTIONNER.TEXTE(CONCAT())), j’ai corrigé l’erreur qui provenait en fait du résultat d’une division nulle ( 1/(RECHERCHEX(par valeur sup) - RECHERCHEX(par valeur inf)), puisque les résultats RECHERCHEX renvoyaient la même valeur.
Par ailleurs, mon réaliser mon calcul, je n’ai pas vraiment besoin d’une régression linéaire. Je peux me débrouiller avec des produits en croix, mais toujours besoin d’avoir recours à la fonction RECHERCHEX. Je verrai quelle formule s’en sort le mieux sur 3 millions de cellules…

ps : J’aimerai tellement installer la version 64bit car j’ai souvent fait planter Excel par manque de RAM, mais pour l’avoir fait une fois, j’avais beaucoup de macros indispensables qui ne fonctionnaient plus. J’avais été obligé de revenir en arrière.

Bonjour,
Au sujet de la variation du paramètre de température, c'est cette partie de la formule (en E7) :

VB:

ADRESSE(LIGNE(INDEX($Q$4:$AA$44;EQUIV(RECHERCHEX(B7;$R$3:$AA$3;EXCLURE(FILTRE($Q$4:$AA$44;$Q$4:$Q$44=$A$6);;1);;-1);$Q$4:$AA$4;0);1))-1;EQUIV(RECHERCHEX(B7;$R$3:$AA$3;EXCLURE(FILTRE($Q$4:$AA$44;$Q$4:$Q$44=$A$6);;1);;-1);$Q$4:$AA$4;0)+16) & ":" & ADRESSE(LIGNE(INDEX($Q$4:$AA$44;EQUIV(RECHERCHEX(B7;$R$3:$AA$3;EXCLURE(FILTRE($Q$4:$AA$44;$Q$4:$Q$44=$A$6);;1);;-1);$Q$4:$AA$4;0);1))-1;EQUIV(RECHERCHEX(B7;$R$3:$AA$3;EXCLURE(FILTRE($Q$4:$AA$44;$Q$4:$Q$44=$A$6);;1);;1);$Q$4:$AA$4;0)+16)

qui renvoie le champ $R$4:$S$4 (les valeurs 34 et 72 des cellules H3 et H4), nécessaire à la fonction DROITEREG (il lui est passé via la fonction INDIRECT) dont je n'ai pas su comment elle doit évoluer en fonction des différents autres paramètres.
C'est pourquoi je vous disais de vérifier cette partie.
Pour la version Excel 64bits, les seules macros susceptibles de ne pas fonctionner sont celles qui font appel à des API. Il suffit généralement de modifier les déclarations pour les adapter en 64 bits et ça marche.
Cordialement,

Dranreb · 27 Janvier 2023

Remarque: si la fonction est une hauteur z en fonction de x,y elle peut aussi être approximée par une tendance en somme de cônes Sqr((x - xn)^2 + (y - yn)^2) en plus des coins 1, x, y et x×y. Mais je ne suis pas sûr que ce soit le sens de vos données et de toute façon je ne saurais alors pas où seraient les positions x,y des sommets des cônes de la tendance à chercher.

Dranreb · 27 Janvier 2023

Si c'est bien en 3D j'espère que vous ne chercher pas tout simplement par interpolation linéaire un point intermédiaire n'étant pas porté dans les données. Ce serait beaucoup plus simple à calculer, surtout en VBA.

Atiroocky · 30 Janvier 2023

Dranreb à dit:
Si c'est bien en 3D j'espère que vous ne chercher pas tout simplement par interpolation linéaire un point intermédiaire n'étant pas porté dans les données. Ce serait beaucoup plus simple à calculer, surtout en VBA.

Le tableau représente des données avec un "pas" grossier, notamment en X.
J’ai besoin de recalculer une valeur assez précise pour une donnée x3 située entre deux pas connus.
J’aurai pu tout modéliser avec une seule équation "3D" via l’outil d’analyse d’Excel, mais le coefficient d’erreur est trop important.
Pour améliorer la précision, j’ai fait un polynôme pour chaque ligne (T°C). La régression est meilleur (0.98), mais par moment les écarts sont également trop importants.
L’idée finale étant de faire, pour chaque ligne de T°C, autant d’équation linéaire qu’il y a de pas, pour coller parfaitement aux segments de droite décrits par les données.
J’ai opté pour la méthode avec le DROITEREG d’Excel, j’ai réussi à calculer mes 3 millions de cellules, mais j’ai été obligé de le faire une une dizaine d’étapes, sinon plantage assuré.

J’ai également essayé en le calculant manuellement :
- y3 étant la valeur à rechercher pour la donnée x3, et connaissant les points intermédiaires y2,y1,x2,x1, ça me donne y3=(y2-y1)*(x3-x1)/(x2-x1)+y1

On a donc y2 = recherchex… ; y1 = recherchex…

Bref, en utilisation la fonction LET, j’ai pu alléger la formule, ainsi que le calcul, puisque cette fonction ne calcule qu’une seule fois le paramètre appelé (y1 et x2 apparaissent 2 fois) Sur 1000000 lignes, ça peut faire une petite différence, pas encore essayé sur l’ensemble du tableau.

Dranreb · 30 Janvier 2023

J'utilise beaucoup ce que vous dites, aussi bien en formules qu'en VBA, sous forme de fonction perso toute simple :

VB:

Function IntpoLin(ByVal X As Double, ByVal X1 As Double, ByVal Y1 As Double, _
                                     ByVal X2 As Double, ByVal Y2 As Double) As Double
   IntpoLin = Y1 + (Y2 - Y1) * (X - X1) / (X2 - X1)
   End Function

Pourrait se combiner à une recherche avec WorksheetFunction.Match genre comme ça, si les valeurs sont disposées horizontalement :

VB:

Function RechIntH(ByVal X As Double, ByVal RX, ByVal RY) As Double
   Dim C As Integer
   If TypeOf RX Is Range Then RX = RX.Value
   If TypeOf RY Is Range Then RY = RY.Value
   C = WorksheetFunction.Match(X, RX, 1)
   RechIntH = IntpoLin(X, RX(C), RY(C), RX(C + 1), RY(C + 1))
   End Function

Si ça peut aussi être intermédiaire en Y, ça peut se calcule également assez facilement.

Microsoft 365 Problème #valeur DROITEREG() avec RECHERCHEX() en paramètres de valeurs

Atiroocky

XLDnaute Nouveau

Gégé-45550

XLDnaute Accro

Hasco

XLDnaute Barbatruc

Atiroocky

XLDnaute Nouveau

Pièces jointes

Hasco

XLDnaute Barbatruc

Gégé-45550

XLDnaute Accro

Pièces jointes

Atiroocky

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

Pièces jointes

Gégé-45550

XLDnaute Accro

Dranreb

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

Atiroocky

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

Discussions similaires