Recherche valeur dans cellule ou plage en fonction des critères de mois et d'année

[fanThomas]

Bonjour à tous,

Je sollicite votre aide pour résoudre une équation trop difficile pour mes humbles connaissances.

Cela fait des jours que je cherche et teste différentes formules (MAX / GRANDE.VALEUR / RECHERCHE(H/V)...) sans aucun succès (je n'ai pas essayer via une Macro...je n'y connais rien ! mais c'est peut-être la seule solution ?).

Mon problème viens du fait que je cherche à trouver une valeur dans une table que je ne peux pas trier et dont les plages ne sont pas égales en nombre de lignes.

Je vous joint le fichier avec les précisions de ce que je cherche a afficher dans chacune des cellules.

Je désespère...merci pour votre aide
Cordialement,
fanThomas

 

[Modeste]

Re : Recherche valeur dans cellule ou plage en fonction des critères de mois et d'ann

Bonjour fanThomas et bienvenue à toi!

Il y a, sur ce forum, de vrais spécialistes (dont je ne suis pas ... du tout!) des calculs sur les dates. En attendant que l'un d'eux nous dise comment faire plus simple, une proposition en pièce jointe.
Je suis parti du principe que chacun des mois de l'année était présent dans ta feuille "cotation source". Comme je ne suis pas certain d'être dans le bon, je n'ai complété qu'une partie des colonnes B et C (1ère année).

En colonne H, une piste possible pour obtenir la dernière date de chaque mois ... piste à explorer pour simplifier peut-être les autres calculs!?

A tester avec précaution 🙄 et en espérant que ça stimulera ta réflexion

 

[fanThomas]

Re : Recherche valeur dans cellule ou plage en fonction des critères de mois et d'ann

Bonjour Modeste,

Merci de m’accueillir aussi chaleureusement sur ce forum et avec une réponse si rapide.

Ce que tu me proposes comme solution correspond exactement à ce que je recherchais.
J'ai d'ailleurs pu transposer une de test formules à une autre colonne (F). Cela en fait 3 sur 6 !

Cependant, je ne connais pas toutes les syntaxes de formules utilisées (LIGNE / EQUIV) et l'environnement dans lequel tu les développes.
Par exemple, et comme tu le fais remarquer, les résultats obtenus ne s'appliquent qu'à la première année.
Donc, étant donné que la valeur recherchée dans ta formule semble être uniquement celle ayant la plus petite année et le plus petit mois. Comment étendre cette formule aux années suivantes pour obtenir quelque chose comme : Ta formule + 1 année ?

Merci de ton aide.
PS: en Pièce-jointe la version actualisée de ta feuille.

 

[Modeste]

Re : Recherche valeur dans cellule ou plage en fonction des critères de mois et d'ann

Re-bonjour,

Toujours à défaut d'autre proposition, j'ai continué sur mon idée de tout à l'heure
En H5, recopie de la première date renseignée en colonne B de cotation source
En I5 et suivantes, la formule qui figurait en colonne H dans mon exemple précédent
En H6 et suivantes, le premier jour de chaque mois (fin du mois précédent + 1)

On obtient ainsi les bornes supérieures et inférieures, mois par mois ... qui permettent les autres calculs (colonnes D et E)
... Le tout si je n'ai pas commis d'impair!?

 

[fanThomas]

Re : Recherche valeur dans cellule ou plage en fonction des critères de mois et d'ann

Re bonjour Modeste,

Merci beaucoup pour l'aide que tu m'apportes...tout semble allez si vite !

C'est bien ce que je veux obtenir...(j'ai "corrigé" la référence aux colonnes de "Cotations sources" pour tes formules en colonnes D et E (Cotations_sources!$C$2:$C$2827 remplacés par Cotations_sources!$D$2:$D$2827 et Cotations_sources!$E$2:$E$2827 ... rien de bien glorieux pour moi 😱, tu as fais tout le travail !)).
Il me reste toujours la dernière colonne a renseigner, mais maintenant que tu as "borné" les valeurs, je peux peut-être y arriver (euh, je viens de réussir).

Il ne subsiste qu'un seul obstacle comment étendre tes formules aux années suivantes pour obtenir quelque chose comme : Ta formule + 1 année ?

Encore merci,
fanThomas

 

[Modeste]

Re : Recherche valeur dans cellule ou plage en fonction des critères de mois et d'ann

Bonsoir,

En temps normal, je n'ai déjà pas toujours les yeux en face des trous ... la journée a été "dense" une proposition à revérifier avec la plus extrême attention.
Trouver un système pour passer aux années postérieures aurait sans doute rendu les formules plus lourdes encore. Je les ai donc modifiées (en tout cas celles qui devaient l'être!) pour qu'on puisse utiliser les fameuses bornes aussi. J'ai recopié les formules au-delà de la première année pour vérifier que ça semblait fonctionner, tu peux recopier nettement plus bas.
En colonne H, la formule n'inscrit plus rien quand la dernière date de "cotations source" est dépassée. On devrait pouvoir en faire autant avec les autres formules, mais vois déjà si le reste est OK.

Attention, j'ai supposé que -même si tu ne peux pas trier la liste de données- les dates sont, malgré tout, en ordre croissant!?
Les formules matricielles risquent de "plomber" le temps de recalcul ... mais je n'ai pas mieux, en ce qui me concerne 🙁

 

[fanThomas]

Re : Recherche valeur dans cellule ou plage en fonction des critères de mois et d'ann

Bonsoir Modeste,

Wouaahhh !!! (que dire de plus ?)

J'ai passé plusieurs jours sur cette feuille sans réponse et tu viens d'y mettre fin en à peine une journée (et je pense bien que tu n'as pas fais que ça !).
J'ai appliqué une de tes formules en colonne B (Date); la seule encore en sursis et ça fonctionne sans problème.
Tu supposais bien concernant l'ordre croissant des dates...du coup ce que tu proposes réponds EXACTEMENT à ce que je voulais obtenir...ne me reste plus qu'a extrapoler tes solutions (si j'y arrive 😕) aux feuilles hebdomadaire et annuelle (j'ai déjà une feuille quotidienne).
Je vais essayer, comme tu l'as fais, de masquer les autres formules lorsque la dernière date de "cotations source" est dépassée...cela rendra la feuille plus clair et permettra actualisation des valeurs dès que la feuille "Cotations source" sera mise à jour.
Un très grand MERCI pour ton aide précieuse... si je peux t'aider en t'expliquant comment additionner 2 chiffres dans Excel, n'hésites pas 😛, c'est de mon niveau !

fanThomas

PS: En PJ la dernière version avec l'adaptation de ta formule en colonne D à la colonne B (Date), pour ceux que cette feuille intéresserait.

 

[fanThomas]

Re : Recherche valeur dans cellule ou plage en fonction des critères de mois et d'ann

"...ne me reste plus qu'a extrapoler tes solutions (si j'y arrive 😕) aux feuilles hebdomadaire et annuelle"

Bonjour,

J'ai essayer de modifier les bornes "mensuelles" du fichier "BDD_CAC40_Mensuelle_V4(2).xlsx' pour obtenir des bornes annuelles et hebdomadaires et conserver ainsi un maximum de formules déjà en place...mais bien sur sans succès 🙁.

Peux-tu (pouvez-vous) m'aider à nouveau pour ces 2 dernières feuilles ?

Cordialement,
fanThomas

 

[Modeste]

Re : Recherche valeur dans cellule ou plage en fonction des critères de mois et d'ann

Bonjour le petit monde du forum,
Bonjour fanThomas (oui, c'est de nouveau moi!)

Si je comprends bien ce que tu veux faire, dans l'exemple que tu as déposé,
- H22 ne change pas!
- H23 et suivantes ne changent pas non plus
- I22 devient

=DATE(ANNEE(H22);12;31)

... à recopier vers le bas, pour obtenir le dernier jour de chaque année qui suit.

Le reste ne se met pas à jour automatiquement? ... ou alors je saute encore une étape??

 

[fanThomas]

Re : Recherche valeur dans cellule ou plage en fonction des critères de mois et d'ann

Bonjour Modeste,

Je suis en train de perdre les dernières miettes d'estime que j'avais de moi avec Excel, snif !

J'avais bien essayé DATE(ANNEE(H22) mais pas avec ;12;31 ...ce qui semble évident maintenant que tu m'as mis les yeux dessus ! Et je t'en remercie...le reste s'actualise bien automatiquement.

Par contre la feuille "Hebdomadaire" me parait insurmontable !
Aurais-tu une piste ?

Merci pour toute l'aide que tu m'apportes et le temps passé.

 

[Modeste]

Re : Recherche valeur dans cellule ou plage en fonction des critères de mois et d'ann

Re-bonjour,

Par contre la feuille "Hebdomadaire" me parait insurmontable !

Iîîîîkk ... je ne l'avais même pas vue! Ceci dit, ce doit être un acte manqué: je déteste les calculs sur des semaines (et puis d'ailleurs les calculs tout court aussi, je les hais! ... Au fond, c'est mon côté maso qui fait que j'aime Excel!?)

Pour tes semaines, deux choses:
- on est partis pour avoir (à vue de nez) environ 500 lignes (10 années de 52 semaines) avec des formules matricielles! Ca va devenir du lourd de chez lourd, je le crains!
- il faudrait que tu précises quelle date on est censé prendre en compte, si une semaine est "à cheval" sur 2 mois? On redémarre au 1er du mois ou on prend toutes les dates de lundi en dimanche successivement?

Attention j'insiste sur la lourdeur probable, d'une part et même si tu réponds à ma dernière question, d'autre part, je ne me propose absolument pas d'essayer d'y répondre: la migraine me gagne déjà 😡

 

[fanThomas]

Re : Recherche valeur dans cellule ou plage en fonction des critères de mois et d'ann

Re-bonjour Modeste,

J'ai bien conscience de la lourdeur de la feuille...je commence à la ressentir lors de mes tests; d'autant plus que je n'ai fourni que certaines dates dans les exemples de pièces-jointes (début des cotations en 1965 !!).
Toutefois, la feuille n'a pas pour but une utilisation quotidienne, mais la mise en place de statistiques évolutives (surement quotidiennement).

Concernant ta 2ème question, tu as bien cerné mon problème :
Certaines semaines sont à cheval sur 2 mois et il me faut donc prendre en compte, non pas le numéro de la semaine, mais la suite des jours de cotations de lundi à vendredi (dimanche)... le numéro de la semaine est une piste que j'ai explorée pour obtenir mes résultats.

Je comprends tout a fait ton mal de crâne...le mien est totalement vide depuis que je travail sur cette feuille...un vrai zombie !

En tous cas, merci de ton aide... j'ai déjà bien avancé dans mon travail... euh ! "TU as bien avancé mon travail" serait une meilleur formulation !

Cordialement,
fanThomas

 

[Modeste]

Re : Recherche valeur dans cellule ou plage en fonction des critères de mois et d'ann

Bonsoir,

J'ai bien conscience de la lourdeur de la feuille...je commence à la ressentir lors de mes tests; d'autant plus que je n'ai fourni que certaines dates dans les exemples de pièces-jointes (début des cotations en 1965 !!).

Aaaaargh! Après les problèmes de lourdeur, vient le temps de l'instabilité du fichier!

Toutefois, la feuille n'a pas pour but une utilisation quotidienne, mais la mise en place de statistiques évolutives (surement quotidiennement).

Somme toute, ce sera quotidien ... sans l'être 😉 Tu as l'intention de leurrer Excel? Tu passes à portée en sifflotant et en disant "non, non c'est pas le jour", puis tu lui sautes sur la râble et tu ouvres le fichier par surprise?

Concernant ta 2ème question, tu as bien cerné mon problème :
Certaines semaines sont à cheval sur 2 mois et il me faut donc prendre en compte, non pas le numéro de la semaine, mais la suite des jours de cotations de lundi à vendredi (dimanche)... le numéro de la semaine est une piste que j'ai explorée pour obtenir mes résultats.

Là, j'ai prévenu que j'avais du mal, hein! En clair, tu veux quoi? Prenons janvier 2001: la dernière cotation était le 31/01/01 ... c'était un mercredi. Faut-il un décompte séparé
- du lundi 29/01/01 au mercredi 31/01/01 et un autre pour la période du jeudi 01/02/01 au dimanche 04/02/01?
- ou alors on considère la semaine de lundi à dimanche en bloc (autrement dit on crée une ribambelle de blocs de 7 jours)?

Note à l'usage du lecteur: si tu choisis la seconde hypothèse, ce n'est pas compliqué, en fait. Si tu optes pour la première, je pars en Alaska ce soir même ... mais bon fais ton choix en toute liberté, bien sûr!

 

[fanThomas]

Re : Recherche valeur dans cellule ou plage en fonction des critères de mois et d'ann

Bonsoir,

Tu as l'intention de leurrer Excel? Tu passes à portée en sifflotant et en disant "non, non c'est pas le jour", puis tu lui sautes sur la râble et tu ouvres le fichier par surprise?

Tu as surement raison Modeste; un petit coup d’œil sur la feuille Excel pour confirmer une stratégie en fonction de l'actualisation des statistiques...et hop ! le fichier me lâche au plus mauvais moment (là, c'est l'expérience qui parle). Mea culpa 😱 😛

En clair, tu veux quoi? Prenons janvier 2001: la dernière cotation était le 31/01/01 ... c'était un mercredi. Faut-il un décompte séparé
- du lundi 29/01/01 au mercredi 31/01/01 et un autre pour la période du jeudi 01/02/01 au dimanche 04/02/01?
- ou alors on considère la semaine de lundi à dimanche en bloc (autrement dit on crée une ribambelle de blocs de 7 jours)?

C'est effectivement la 2ème solution qu'il me faut appliquer. Il peut alors arriver qu'une semaine de cotations soit "à cheval" sur 2 mois. Dans ton exemple de janvier 2001, la semaine que je cherche a obtenir est celle allant du lundi 29/01/01 au dimanche 04/02/01 (en réalité au vendredi 02/02/2011 car pas de cotations le Weekend).

Note à l'usage du lecteur: si tu choisis la seconde hypothèse, ce n'est pas compliqué, en fait. Si tu optes pour la première, je pars en Alaska ce soir même ... mais bon fais ton choix en toute liberté, bien sûr!

Je t'épargne donc un voyage en Alaska...mais finalement, est-ce le bon choix pour ta santé mentale ?

fanThomas

 

[Modeste]

Re : Recherche valeur dans cellule ou plage en fonction des critères de mois et d'ann

Et re-bonsoir,

Tout est bien qui finit bien, alors (enfin j'espère):
comme pour la version annuelle, les formules en colonne H ne changent pas.
En I22:

=H22+7-JOURSEM(H22;2)

... donne la date du dimanche qui suit la date en H22. Tu peux recopier cette formule jusqu'en bas (ou alors, à partir de I23, =I22+7 ... à recopier vers le bas aussi)

En C22, il me semble qu'un résultat cohérent est obtenu avec:

=INDEX(Cotations_sources!$C$2:$C$2827;EQUIV(MIN(SI((Cotations_sources!$B$2:$B$2827>=H22)*(Cotations_sources!$B$2:$B$2827<=I22);Cotations_sources!$B$2:$B$2827));Cotations_sources!$B$2:$B$2827;0))

toujours en formule matricielle, je le crains! Vérifie quand même soigneusement!!
Pour le reste, il me semble que tu devrais pouvoir adapter ... sauf s'il n'y a rien à changer 😉

... Mais si tu testes avec les cotations depuis 1965, je décline toute responsabilité 😛