Trouver dans une colonne les cellules précédant une recherche

[bomy84]

Bonjour,

J'utilise la fonction =recherche(9^9;b2:b5000) par exemple, pour trouver la dernière cellule non vide de la colonne B entre les cellules b2 et b5000.
Tous les jours cette fonction me permet de trouver cette dernière cellule sachant que tous les jours je rajoute une valeur dans cette colonne.
Quelle serait la formule qui me permettrait de pouvoir récupérer les vingt cellules précédant cette recherche (donc qui changerait tous les jours, décalage d'une écriture par jour).
ex: valeur renvoyée b50
cellules cherchées b50:b30
le lendemain
valeur renvoyée : b51
cellules recherchées b51:b31
Sachant que je dois pouvoir exploiter la valeur de ces cellules
J'espère m'être exprimé correctement.

Merci d'avance.

 

[supercrapaud]

Re : Trouver dans une colonne les cellules précédant une recherche

Bonjour,

A première vue, il faudrait prendre utiliser une fonction décaler mais sans fichier dur de te dire car là on ne comprends pas le résultat que ut cherches.

 

[bomy84]

Re : Trouver dans une colonne les cellules précédant une recherche

En fait je n'ai pas de fichier à joindre parce que je compte sur cette formule pour pouvoir créer mon fichier. En fait il s'agit de créer un graphique sur le calcul d'une mm20 (moyenne mobile sur 20 jours) en bourse. Donc il faut créer ce graphique à partir des 20 dernières cotations. et ce tous les jours, d'où le décalage d'une cellule par jour.

 

[JBOBO]

Re : Trouver dans une colonne les cellules précédant une recherche

Bonjour,

Peut-etre avec cette formule :

=INDIRECT("b"&EQUIV(RECHERCHE(9^9;$B$2:$B$5000);$B$2:$B$5000)-LIGNE()+2)

 

[bomy84]

Re : Trouver dans une colonne les cellules précédant une recherche

oups !!!!
Merci pour la réponse (ou vos réponses) mais je ne comprends pas la formule
=INDIRECT("b"&EQUIV(RECHERCHE(9^9;$B$2:$B$5000);$B $2:$B$5000)-LIGNE()+2)
à part la fonction de recherche de la dernière cellule de la colonne b (RECHERCHE(9^9;$B$2:$B$5000), je ne comprends pas ce que fait la formule - pouvez vous me donner les explications ou me dire où je puis les trouver - la fonction indirect me paraît corsée
Merci pour votre patience - yé né suis qu'oune pauvrrre déboutant !

 

[Tibo]

Re : Trouver dans une colonne les cellules précédant une recherche

Bonjour,

Une tentative avec cette formule, à copier où tu veux :

=DECALER($B$1;EQUIV(9^9;$B$1:$B$5000)-21+LIGNES($1:1);;1)

à recopier vers le bas sur 20 lignes

@+

 

[JBOBO]

Re : Trouver dans une colonne les cellules précédant une recherche

Re,
Pour la fonction "recherche", tu as compris ça te renvoi la derniere valeur de ta colonne. "Equiv" te renvoi la position de cette valeur dans la colonne (si tu pars de B2 et que ta derniere valeur se trouve en B20, il te renverra 19 car c'est la 19eme valeur de ta plage.)
Ensuite "indirect" te permet de renvoyer la valeur contenu dans une cellule indirect("B20") te renverra la valeur contenu en B20.
Apres tu peux combiner si en B20 tu as la valeur 50 alors =indirect("b"&equiv(B2:B5000;50;0) te renvoi la valeur contenu en B19 CAR 50 se trouve en 19eme position et donc indirect va chercher en colonne B19 et te renverra la valeur correspondante. Ensuite, ligne fais reference à la ligne afin d'ajuster la valeur cherche par indirect. Tu peux faire un audit de formule pas à pas pour mieux comprendre la formule.

Edit : Bonjour tibo

 

[supercrapaud]

Re : Trouver dans une colonne les cellules précédant une recherche

Bonjour tout le fil,

Voici un fichier de test. Le principe ; j'ai créé une zone dynamique qui ne reprend que les 20 dernières lignes et un grahique qui te donne la courbe de ces dernières 20 lignes.
Si je'ai bien ccompris ce que tu souhaites cela devrait te convenir. A tester.

 

[bomy84]

Re : Trouver dans une colonne les cellules précédant une recherche

Désolé, j'étais absent - merci pour toutes vos réponses - je vais tester et voir toutes ces fonctions ce soir chez moi à tête reposée - mais aujourd'hui j'ai appris deux choses -
1 / quelques nouvelles fonctions sous excel
2 / que j'étais nul sous excel
Demain je vous tiendrais au courant de mes pérégrinations dans le monde d'excel.
En attendant merci pour vos astuces et commentaires
Bonne continuation

 

[bomy84]

Re : Trouver dans une colonne les cellules précédant une recherche

Bonjour,
j'ai testé le fichier de "supercrapaud" c'est au top - j'ai bien une moyenne sur 20 valeurs dans la même colonne, même en rajoutant des valeurs dans cette colonne, c'est bien toujours les 20 dernières valeurs qui sont prises en compte. (champion du monde !!!!)
Mais maintenant que l'on ma donné la main, j'aimerais le coude
En fait le graphique correspondant est ok, sauf que les 20 valeurs prises en compte pour le graphique sont entrées "à la mano".
Est-il possible ou est-ce infaisable d'avoir le graphique correspondant aux dernières 20 valeurs de la colonne. C'est à dire que le graphique suive la moyenne mobile des 20 dernières valeurs.
Merci (qu'est-ce que je peux dire d'autre), d'avance !!

 

[supercrapaud]

Re : Trouver dans une colonne les cellules précédant une recherche

Bonjour,

Je n'ai pas tout compris. Normalement j'ai créé ce graphique avec une plage dynamique pour que justement il n'y ait pas à faire ce genre de manip mais il semblerait que lorsque l'on regarde la série de données, elle ne reprenne pas le nom de la plage mais uniquement les valeurs qui la composient lors de l'enregistrement du fichier. Je ne comprends pas pourquoi, là ça dépasse mes compétences. Si quelqu'un d'autre sait comment faire je suis aussi preneur.

 

[bomy84]

Re : Trouver dans une colonne les cellules précédant une recherche

Suite,
Par contre la formule de tibo à l'air d'être pas mal, elle reprend effectivement la valeur de la 20 ème cellule de la colonne - il ne me manque donc plus que les 19 autres - pourquoi ?
parce que lorsque je copie sa formule sur les 20 lignes vers le bas à la suite, cela devrait me donner les 19 autres valeurs manquantes - ce qui serait le top pour avoir le graphique correspondant - sauf que lorsque je recopie sa formule vers le bas sur 20 lignes, il me recopie 19 fois la même valeurs et non pas les 19 autres valeurs attendus -
il doit y avoir une histoire de valeur absolue, ou relative à revoir - je cherche !
merci à "tibo" et "supercrapaud" pour leurs formules et au temps passé par les autres également - je vais regarder les explications données pour la compréhension des formules, ce que je n'ai pas encore eu le temps de faire - mais j'avance quand même dans le bon sens.
merci à tous

 

[bomy84]

Re : Trouver dans une colonne les cellules précédant une recherche

C'est bon pour la formule de "tibo"
Ce n'est pas une histoire de valeur relative ou absolue -
=DECALER($B$1;EQUIV(9^9;$B$1:$B$5000)-21+LIGNES($1:1);;1)
c'est uniquement la valeur -21 qu'il faut changer sur les lignes suivantes dans la recopie (-21;-20;-19.........)
Ce qui fait que l'on aura en permanence les 20 dernières lignes de la colonne quelquesoit les valeurs que l'on y rajoute, donc je suppose que l'on peut à partir de cette plage de cellules qui se met à jour en permanence avoir le graphique correspondant qui se met à jour - à tester !!!
On va y arriver - c'est cool