Microsoft 365 Sélectionner une plage à partir d'une date jusqu'à la dernière date enregistré dans la fonction droitereg

Djfab23

XLDnaute Nouveau
Bonjour à tous,

J'aurai besoin d'aide svp. je n'arrive pas à trouver la solution à mon problème. Certainement que je dois mal formuler ce que je recherche.

En utilisant la fonction Droitereg je voudrai que ma sélection de plage démarre à partir d'une date que je rentre dans une autre cellule (E5) et quelle se termine à la dernière date connue.

Exemple : appliquer la fonction droitereg sur les cours de la colonne B du 01/07/2022 au 22/07/2022 en utilisant la cellule E5 pour démarrer la plage.

En rentrant moi même comme je l'ai fait en E8 pas de problème mais étant donné que j'ai une quarantaine de tableau avec les dates qui se mettent à jours ce n'est plus gérable. Ca me ferai gagner énormément de temps.

Cordialement.
 

Pièces jointes

  • Test.xlsx
    9.9 KB · Affichages: 6
Solution
Bonjour Djfab, JHA,
Certainement que je dois mal formuler ce que je recherche.
Entièrement d'accord.
Déjà, la formulation de Droitereg n'est pas celle formulée mais :
VB:
=Droitereg(MatriceY;MatriceX) pour le coef a
et
=Index(Droitereg(MatriceY;MatriceX);2) pour le coef b
En fait Droitereg renvoie une matrice de 4 valeur avec a, b pour les deux premiers éléments.
Donc pour vous la formule est :
=DROITEREG(B5:B20;A5:A20) sans être matricielle.
Ensuite il faut définir la plage avec la date donnée.
La plage est définie avec :
Code:
Matrice X ...

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour Djfab, JHA,
Certainement que je dois mal formuler ce que je recherche.
Entièrement d'accord.
Déjà, la formulation de Droitereg n'est pas celle formulée mais :
VB:
=Droitereg(MatriceY;MatriceX) pour le coef a
et
=Index(Droitereg(MatriceY;MatriceX);2) pour le coef b
En fait Droitereg renvoie une matrice de 4 valeur avec a, b pour les deux premiers éléments.
Donc pour vous la formule est :
=DROITEREG(B5:B20;A5:A20) sans être matricielle.
Ensuite il faut définir la plage avec la date donnée.
La plage est définie avec :
Code:
Matrice X :   ="A"&EQUIV(E5;A:A;0)&":A"&NBVAL(A:A)[CODE]=DROITEREG(INDIRECT("B"&EQUIV(E5;A:A;0)&":B"&NBVAL(A:A));INDIRECT("A"&EQUIV(E5;A:A;0)&":A"&NBVAL(A:A)))*G17+INDEX(DROITEREG(INDIRECT("B"&EQUIV(E5;A:A;0)&":B"&NBVAL(A:A));INDIRECT("A"&EQUIV(E5;A:A;0)&":A"&NBVAL(A:A)));2)
Matrice Y : ="B"&EQUIV(E5;A:A;0)&":B"&NBVAL(A:A)
[/CODE]
Et donc utiliser ces deux plages avec :
Code:
=DROITEREG(INDIRECT(E7);INDIRECT(E6))

Ou encore en une seule formule :
Code:
Coef a :  =DROITEREG(INDIRECT("B"&EQUIV(E5;A:A;0)&":B"&NBVAL(A:A));INDIRECT("A"&EQUIV(E5;A:A;0)&":A"&NBVAL(A:A)))
Coef b :  =INDEX(DROITEREG(INDIRECT("B"&EQUIV(E5;A:A;0)&":B"&NBVAL(A:A));INDIRECT("A"&EQUIV(E5;A:A;0)&":A"&NBVAL(A:A)));2)

On peut avoir ainsi le cours à une date donnée directement, mais plus compliquée , si la date est en G17 :
Code:
=DROITEREG(INDIRECT("B"&EQUIV(E5;A:A;0)&":B"&NBVAL(A:A));INDIRECT("A"&EQUIV(E5;A:A;0)&":A"&NBVAL(A:A)))*G17+INDEX(DROITEREG(INDIRECT("B"&EQUIV(E5;A:A;0)&":B"&NBVAL(A:A));INDIRECT("A"&EQUIV(E5;A:A;0)&":A"&NBVAL(A:A)));2)
Voir PJ.
 

Pièces jointes

  • Test (19).xlsx
    9.2 KB · Affichages: 7

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour JHA,
Votre formule donne 0.468, mais ça ne correspond pas au coefficient directeur de Cours=f(Dates)
Ce coef qui est =Droitereg(B5:B20;A5:A20) vaut 0.328.
C'est d'ailleurs ce qui est donné si on trace la courbe et qu'on demande la courbe de tendance.

1658675720699.png
 

Dranreb

XLDnaute Barbatruc
Ma formule ne va pas à cause des dates de weekend absentes.
Il faut En E8:F8 validé par Ctrl+Maj+Entrée :
Code:
=DROITEREG(DECALER($B$1;EQUIV(E$5;$A$2:$A$1000000;1);0;NBVAL($A$2:$A$1000000)-EQUIV(E$5;$A$2:$A$1000000;1)+1;1))
Remarque: Équivaut en l'occurrence à :
Code:
=DROITEREG($B$5:$B$20;{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16})
 

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour Dranreb,
Il semble que Djfab veut le coef directeur de la droite de la colonne B fonction de la colonne A, soit le cours en fonction de la date.
Exemple : appliquer la fonction droitereg sur les cours de la colonne B du 01/07/2022 au 22/07/2022 en utilisant la cellule E5 pour démarrer la plage.
Dans ce cas le coef directeur vaut 0.328 comme le montre le graph du post #5, et non 0.468.
Ce serait bien que Djfab intervienne, on aurait peut être un début de réponse.:)
 

Djfab23

XLDnaute Nouveau
Je vous remercie de votre aide pour mon problème et je suis encore à analyser vos réponses en demandant si je n'ai pas fait une erreur de mon côté. Je recherche à tracer la droite de la colonne B en fonction de la colonne A. Donc mon résultat n'est pas bon. Le but est d'obtenir les coef a et b.
 

sylvanu

XLDnaute Barbatruc
Supporter XLD
Just for the fun ...
Une fonction perso en VBA :
VB:
Function TrouverCours(PlageX As Range, PlageY As Range, Datedébut, DateCherchée)
    MatX = "A" & Application.Match(Datedébut, PlageX, 0) & ":A" & Application.CountA(PlageX)
    MatY = "B" & Application.Match(Datedébut, PlageX, 0) & ":B" & Application.CountA(PlageX)
    MatDroitereg = Application.LinEst(Range(MatY), Range(MatX))
    TrouverCours = MatDroitereg(1) * CDate(DateCherchée) + MatDroitereg(2)
End Function
Vous entrez les matrices X, Y, la date de début et la date cherchée, et la fonction renvoie la valeur du cours à cette date. Par exemple :
Code:
=TrouverCours(A:A;B:B;E5;E7)
donne 81.852
C'est à dire qu'à la date du 25/07/2022
 

Pièces jointes

  • Test (19) (1).xlsm
    18.3 KB · Affichages: 7

Discussions similaires