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

[Djfab23]

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.

 

[Dranreb]

Bonjour.
Essayez, en E8:F8 validé par Ctrl+Maj+Entrée :

=DROITEREG(DECALER($B$2;E$5-$A$2;0;NBVAL($B$2:$B$1000000)+$A$2-E$5;1))

 

[JHA]

Bonjour à tous,

Un début de piste

=DROITEREG(INDIRECT(ADRESSE(EQUIV($E$5;A:A;0);2)&":"&ADRESSE(EQUIV(9^9;A:A;1);2)))

JHA

 

[sylvanu]

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 :

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

Fonction DROITEREG - Support Microsoft

La fonction DROITEREG calcule les statistiques d’une droite par la méthode des moindres carrés afin de calculer une droite s’ajustant au plus près de vos données, puis renvoie une matrice qui décrit cette droite.

support.microsoft.com

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 :

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 :

=DROITEREG(INDIRECT(E7);INDIRECT(E6))

Ou encore en une seule formule :

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 :

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

 

[sylvanu]

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.

 

[Dranreb]

Ma formule ne va pas à cause des dates de weekend absentes.
Il faut En E8:F8 validé par Ctrl+Maj+Entrée :

=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 à :

=DROITEREG($B$5:$B$20;{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16})

 

[sylvanu]

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.

 

[Dranreb]

J'ai considéré qu'il voulait les jours ouvrés commençant par 1 à partir de la date choisie.
C'est d'ailleurs la droite de régression calculée par sa formule d'origine.

 

[sylvanu]

Re,
Mais c'est ça qui m'a mis la puce à l'oreille :

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.

On dirait qu'il vaut calculer un cours pour une date donnée, donc trouver les coef a et b puis ensuite trouver le cours.

 

[Djfab23]

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]

Re,
Voir post #4 et post #5.
En fait que cherchez vous ? Un cours pour une date donnée ? ou simplement un graphique ?
( Ya plus simple si vous accepter le VBA. )

 

[sylvanu]

Just for the fun ...
Une fonction perso en VBA :

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 :

=TrouverCours(A:A;B:B;E5;E7)
donne 81.852

C'est à dire qu'à la date du 25/07/2022

 

[Djfab23]

Re,
Voir post #4 et post #5.
En fait que cherchez vous ? Un cours pour une date donnée ? ou simplement un graphique ?
( Ya plus simple si vous accepter le VBA. )

Oui, un cours pour une date donnée.

 

[sylvanu]

Alors restez la PJ#12.
En pensant à valider les macros si ce n'est déjà fait.