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

Djfab23 · 24 Juillet 2022

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.

sylvanu · 24 Juillet 2022

Bonjour Djfab, JHA,

Djfab23 à dit:
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.

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 :

Code:

Matrice X ...

Dranreb · 24 Juillet 2022

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

Code:

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

JHA · 24 Juillet 2022

Bonjour à tous,

Un début de piste

VB:

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

JHA

sylvanu · 24 Juillet 2022

Bonjour Djfab, JHA,

Djfab23 à dit:
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.

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 :

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.

sylvanu · 24 Juillet 2022

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 · 24 Juillet 2022

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 · 24 Juillet 2022

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.

Djfab23 à dit:
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 · 24 Juillet 2022

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 · 24 Juillet 2022

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

Djfab23 à dit:
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 · 24 Juillet 2022

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 · 24 Juillet 2022

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 · 24 Juillet 2022

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

Djfab23 · 24 Juillet 2022

sylvanu à dit:
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 · 24 Juillet 2022

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

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

Djfab23

XLDnaute Nouveau

Pièces jointes

sylvanu

Fonction DROITEREG - Support Microsoft

Dranreb

XLDnaute Barbatruc

JHA

XLDnaute Barbatruc

Pièces jointes

sylvanu

XLDnaute Barbatruc

Fonction DROITEREG - Support Microsoft

Pièces jointes

sylvanu

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

sylvanu

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

sylvanu

XLDnaute Barbatruc

Djfab23

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

sylvanu

XLDnaute Barbatruc

Pièces jointes

Djfab23

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

Discussions similaires