Equiv Recherche

Bonjour forum,

Bonne nouvelle, grâce à vous je progresse en excel, oui, n'en doutez-pas ! J'utilise des fonctions qui semblaient hors de portée de mon esprit simple.

Pour preuve un fichier avec fonction equiv et qui me donne ce que je veux.

Lorsqu'une valeur est mentionnée dans la colonne E, je souhaitais qu'il recherche dans la colonne B la valeur équivalente ou la plus proche dans la colonne B dans le temps imparti (73 lignes). J'y suis arrivé.

Je coince pour la prochaine étape et votre aide sera grandement appréciée comme d'habitude.

En l'absence d'une valeur proche dans le temps imparti, excel affiche 74 dans la colonne G ou H. Dans ce cas, j'aimerai dans une autre colonne qu'excel m'affiche la valeur de la colonne B.

Exemple, dans le fichier joint, j'ai la valeur 74 en H62. Je souhaitera que si H62=74 alors J62=(H62+n74) c'est à dire H136 (173.279).

Idem pour G91. Si G91=74 alors I91=(G91+n74) c'est à dire H165 (173.163).

Merci beaucoup.

Max

Bonjour maxwin,

si j'ai bien interprété votre demande les formules pourraient être

en I1 :

=SI(G1=74;INDIRECT("B"&SOMME(LIGNE();G1);1);"")

en J1 :

=SI(H1=74;INDIRECT("B"&SOMME(LIGNE();H1);1);"")

A étendre vers le bas tant que nécessaire

Salut,

En toute honnêteté, je n'ai pas vraiment compris le but de ta demande.

La comme ca, je mettrai la formule ci-dessous (en J62 pour l'exemple) :

=INDEX(B62:B250;H62+1;1)

Par contre, concernant ta formule en G, elle ne remonte pas la valeur demandée.

Par exemple en cellule G28, ta formule

=SIERREUR(EQUIV(E28;B28:B101;1);"")

devrait indiquer la valeur la plus élevée qui est inférieure à E28 (172.987) dans la plage B28:B101.

Ta formule indique comme résultat 7, soit la cellule B34 (172.749). Ce qui ne correspond pas au résultat exact.

J'ai pas regardé toutes les valeurs de la plage mais la cellule B30 (172,856) par exemple est déjà dans ce cas.

Cela provient du fait que la plage devrait être triée du + petit au + grand afin de ressortir le bon résultat.

Dans l'attente de tes commentaires

Jers

Bonjour maxwin,

si j'ai bien interprété votre demande les formules pourraient être

en I1 :

=SI(G1=74;INDIRECT("B"&SOMME(LIGNE();G1);1);"")

en J1 :

=SI(H1=74;INDIRECT("B"&SOMME(LIGNE();H1);1);"")

A étendre vers le bas tant que nécessaire

Bonjour njhub,

Je vous remercie pour les formules, elles me semblent parfaites. Mais ma formule initiale est foireuse comme l'a fait justement remarqué Jers19. Je vais essayer de remédier à cela (c'est pas gagné hein ) puis je reviens vers vous pour confirmer définitivement votre formule.

Merci et très bonne journée.

Max

Salut,

En toute honnêteté, je n'ai pas vraiment compris le but de ta demande.

La comme ca, je mettrai la formule ci-dessous (en J62 pour l'exemple) :

=INDEX(B62:B250;H62+1;1)

Par contre, concernant ta formule en G, elle ne remonte pas la valeur demandée.

Par exemple en cellule G28, ta formule

=SIERREUR(EQUIV(E28;B28:B101;1);"")

devrait indiquer la valeur la plus élevée qui est inférieure à E28 (172.987) dans la plage B28:B101.

Ta formule indique comme résultat 7, soit la cellule B34 (172.749). Ce qui ne correspond pas au résultat exact.

J'ai pas regardé toutes les valeurs de la plage mais la cellule B30 (172,856) par exemple est déjà dans ce cas.

Cela provient du fait que la plage devrait être triée du + petit au + grand afin de ressortir le bon résultat.

Dans l'attente de tes commentaires

Jers

Bonjour Jers,

Vous avez complètement cassé ma joie d'avoir utilisé, tout seule comme un grand, la fonction Equiv.

Bien évidemment vous avez pleinement raison. Bien vu l'aveugle !

Comme je suis nul, je ne comprends pas bien la solution que vous me proposez pour y remedier.

"Cela provient du fait que la plage devrait être triée du + petit au + grand afin de ressortir le bon résultat."

Comment fait-on pour que la plage soit triée dans l'ordre croissant ? Votre aide serait appréciée grandement.

Merci d'avoir relevé mon erreur.

Je vous souhaite une très bonne journée,

Max.

Salut,

=INDEX(B62:B250;H62+1;1)

Par contre, concernant ta formule en G, elle ne remonte pas la valeur demandée.

Cela provient du fait que la plage devrait être triée du + petit au + grand afin de ressortir le bon résultat.

Dans l'attente de tes commentaires

Jers

Bonjour Jers,

J'ai solutionné en ajoutant deux nouvelles colonnes - ordre croissant et décroissant - pour chaque valeur à rechercher dans B.

Merci pour votre aide et je vous souhaite un bon week end.

Cordialement,

Max.

Bonjour maxwin,

si j'ai bien interprété votre demande les formules pourraient être

en I1 :

=SI(G1=74;INDIRECT("B"&SOMME(LIGNE();G1);1);"")

en J1 :

=SI(H1=74;INDIRECT("B"&SOMME(LIGNE();H1);1);"")

A étendre vers le bas tant que nécessaire

Bonjour njhub,

Je vous remercie pour votre aide. J'ai solutionné le problème soulevé à juste titre par Jers et par conséquent tout est en ordre grâce à vous deux.

Je vous souhaite un très bon week end.

Cordialement,

Max

Merci pour ton retour.

A+

Jers

Merci pour ton retour.

A+

Jers

RE bonjour Jers et forums,

J'ai parlé trop vite. Le problème persiste toujours, excel ne me donne qu'un résultat approximatif (bon c'est mieux que rien) mais je prefère obtenir un résultat précisément exact. C'est forcément à la portée d'excel, donc il doit y avoir une solution mais je n'y arrive pas. Help please.

Ci joint un nouveau fichier pour partir sur de bonnes bases.

En A la valeur de référence. En D et E valeurs à rechercher.

En F,G,H et I mes formules foireuses.

Ligne 32 valeurs à rechercher sont 172.882 (D32) et 171.445 (E32).

Pour D32 excel me donne comme equiv 42 lignes plus bas. Or, la valeur recherchée est dépassé 9 lignes plus bas.

En revanche pour l'equivalent de la valeur en E32, c'est tout bon. Il m'indique 2 lignes plus bas et c'est exact.

Pour la ligne 38, tout est parfait, les deux equiv sont identifiés précisement et il me donne les bonnes reférences.

J'aimerai que ce soit aussi précis dans tous les cas. Pourquoi je n'y arrive pas. Qu'est qui tourne pas rond dans ma tête? et aussi dans la tête d'Excel parceque si la formule est bonne pour la ligne 38, elle devrait l'être également pour les autres lignes non?

Merci pour votre aide,

Max.

5equiv-2.xlsx (19.46 Ko)

Re bonjour forum,

j'ai progressé mais je pense avoir dépassé mon seuil de compétences et je nage pleinement dans l'océan d'incompétence.

Ci joint un nouveau fichier pour vous montrer le progrès extra ordinaire que j'ai fait

J'ai donc trié les deux valeurs à rechercher à l'aide de deux nouvelles colonnes G et H

Ensuite j'ai appliqué 1 et -1 pour qu'il me trouve les bonnes cellules.

Mystère d'Excel qui dépasse largement mon intelligence, cela fonctionne pour la colonne H mais que dalle, nada, nothingness, le vide sidéral, enfin du grand nimportenawak, il n'y a rien qui s'affiche dans la colonne G. J'ai eassayé toutes les combinaisons de 1 de 0 et de -1.

Toujours rien.

Si une bonne âme veut bien m'aider à résoudre ce problème se serait vraiment génial.

Bonne nouvelle, j'ai appliqué la formule de njhub pour qu'excel me donne la valeur exacte de la cellule plutot que de dérouler la feuille pour y aller voir. Petite victoire. J'y suis arrivé.

Merci à tous.

5equiv-3.xlsx (25.86 Ko)

Maxwin,

Pourrais-tu stp m'exposer exactement ce que tu veux faire ?

A quoi correspondent les données initiales ? Quels sont les informations que tu veux récupérer ?

Maxwin,

Pourrais-tu stp m'exposer exactement ce que tu veux faire ?

A quoi correspondent les données initiales ? Quels sont les informations que tu veux récupérer ?

Bonjour Jers, bonjour forum,

Il s'agit de déterminer si les objectifs sont atteints ou pas et ce dans le temps imparti. Voilà le but de ce que j'essaie de faire.

Concrêtement, dans la première colonne A tu as l'évolution des prix au cours du temps. Les données s'accumulent au fil du temps et cela ne s'arrête jamais. Et cette colonne A ne présente aucun intérêt en soi si ce n'est de servir de références pour la suite.

Sauf que au cours de l'activité on se fixe des limites de prix avec un seuil haut et un seuil bas (colonnes D et E de mon fichier Equiv3). C'est cela qui m'importe et je veux savoir si ces seuils sont respectés tout en sachant qu'au moins un des deux seuils ne sera pas respecté. Et c'est précisément cela que je veux identifier. Lequel des deux seuils a été dépassé en premier.

Par exemple, dans mon fichier equiv3 à la ligne 58 le prix de référence est 173.99 (A58). A ce moment précis, un seuil haut 174.857 (D58) et un seuil bas 173.496 (E58) ont été déterminé. Je veux donc savoir à quel moment de l'évolution des prix (à partir de A59) le seuil haut a été atteint et/ou le seuil bas.

Grâce à toi j'ai réalisé que ma formule initiale n'identifier pas correctement ces seuils parce qu'il fallait que ce soit dans l'ordre croissant ou décroissant. J'ai donc par la suite créé deux nouvelles colonnes G et H pour que le seuil haut soit toujours dans la colonne G et le seuil bas dans la colonne H. Et j'ai appliqué la fonction Equiv.

Cela fonctionne parfaitement pour la colonne H. Pour la ligne 58, par exemple, le seuil bas est de 173.496 (E58 et H58) et j'obtiens le bon résultat dans la colonne J puisque le prix est 173.11 à la ligne 60.

Le problème est que je n'arrive pas à obtenir de ce satané Excel la même chose pour le seuil haut.

Par exemple à la ligne 83 le seuil haut est 174.398 (D83 et G83). Le seuil est dépassé à la ligne 85 à 174.420. Je souhaiterai donc très concrètement qu'en I83 il me renvoie à A85 puisque le prix est de 174.42 donc supérieur à 174.39. Mystère et boule de gomme il le fait très bien pour le seuil bas puisqu'en J83 il me donne 33 comme résultat et j'ai vérifié c'est juste.

A défaut de maitriser les formules excel j'espère avoir formulé clairement ma requête. Identifier dans la colonne I le dépassement des seuils hauts qui figurent en D et G.

En te remerciant pour ta disponibilité et ton soutien,

Cordialement,

Max

Comme suite à ta demande en mp ...

=SI(H62=74;DECALER(B62;74;);"")

Comme suite à ta demande en mp ...

=SI(H62=74;DECALER(B62;74;);"")

Bonsoir Steelson,

Je te remercie d'avoir répondu à ma demande.

Ce problème a été résolu grâce à la contribution de Jers et njhub.

Le problème que je n'arrive pas à résoudre se rapporte à la fonction équivalente qui n'arrive pas à idenfier correctement la valeur la plus proche supérieure à la valeur de référence. En revanche, la fonction équivalente identifie parfaitement la valeur la plus proche inférieure à la valeur de référence.

Ci joint à nouveau le fichier equiv3. Les valeurs à identifier se trouvent dans les colonnes D et E. Je souhaite qu'excel m'indique à quel moment chacune de ces valeurs ont été atteint.

Jers m'a très justement fait remarquer que pour cette fonction il était nécessaire de trier les valeurs. Ce que j'ai fait dans les colonnes G (la valeur supérieure) et H (valeur inférieure).

Ensuite j'ai appliqué la fonction equiv dans les colonnes I et J.

Cela fonctionne parfaitement pour les valeurs situées dans la colonne J, mais je n'obtiens riens pour la colonne I. Cela dépasse mon entendement.

Donc pourrais-tu s'il te plaît m'aider à ce que les valeurs situées dans la colonne G soient correctement identifier dans la colonne A et me donner en I leur rang dans la colonne A.

En bref, et de façon simple, il faut reproduire en I pour la colonne G, la même opération qu'en J pour la colonne H.

En I32, on devrait normalement obtenir comme résultat 8 puisque la valeur en G32 (172.882) est dépassée pour la première fois en A40 (173.300)

Merci encore pour ton aide et bon dimanche.

Max

3equiv-3.xlsx (23.38 Ko)

Jers m'a très justement fait remarquer que pour cette fonction il était nécessaire de trier les valeurs. Ce que j'ai fait dans les colonnes G (la valeur supérieure) et H (valeur inférieure).

Oui ... mais les valeurs en colonne A !! sinon EQUIV ne trouvera pas sur la valeur élevée

Du reste, comment sont établies les valeurs de D et E

Dans ton cas, soit tu fais une fonction matricielle, soit tu fais une fonction personnalisée

Function RecoupeA(cel As Range) As Long
RecoupeA = 0
For i = cel.Row To cel.Row + 74
    If Cells(i, "A") <= cel.Value And Cells(i + 1, "A") >= cel.Value Then
        RecoupeA = i - cel.Row
        Exit For
    End If
Next
End Function
6equiv-3.xlsm (30.23 Ko)

Bonjour maxwin,

En feuille F2 vous trouverez en colonne :

  • A, les valeurs croissantes de la colonne A de la Feuil1.
  • B, la ligne correspondante en Feuil1
  • D et E, les valeurs correspondantes
  • F, la valeur la plus proche
  • G, la ligne correspondante à la valeur la plus proche en Feuil1
  • H, la valeur correspondante en Feuil1

@njhub

technique intéressante qui consiste à trier la liste dans un second onglet ! bravo

Bonjour maxwin,

En feuille F2 vous trouverez en colonne :

  • A, les valeurs croissantes de la colonne A de la Feuil1.
  • B, la ligne correspondante en Feuil1
  • D et E, les valeurs correspondantes
  • F, la valeur la plus proche
  • G, la ligne correspondante à la valeur la plus proche en Feuil1
  • H, la valeur correspondante en Feuil1maxwin-Equiv 3.xlsx

Bonsoir njhub,

J'ai pris le temps de travailler sur votre modèle et celui de steelson. Je n'ai pas retenu votre modèle car les fichiers à traiter sont assez volumineux et je me serai vite perdu entre les feuilles et les correspondances.

Toutefois, je vous livre sans fioriture, la reflexion que je me suis faite en ayant vu le travail que vous avez fourni :

"Nom de Dieu, ils en ont de la chance ceux qui ont comme collègue ou collaborateur njhub !"

Parce que franchement, il faut être sacrément persevérant et ingénieux pour parvenir à atteindre l'objectif fixé avec des outils inadaptés. Vous avez réussi à faire ce qui était supposé impossible avec la fonction equiv.

Bravo et mille mercis.

All the best.

Max

Dans ton cas, soit tu fais une fonction matricielle, soit tu fais une fonction personnalisée

Function RecoupeA(cel As Range) As Long
RecoupeA = 0
For i = cel.Row To cel.Row + 74
    If Cells(i, "A") <= cel.Value And Cells(i + 1, "A") >= cel.Value Then
        RecoupeA = i - cel.Row
        Exit For
    End If
Next
End Function

Bonsoir Steelson,

Je ne comprends franchement rien à votre formule =SI(G32="","",RecoupeA(G32)) mais qu'est-ce qu'elle est efficace.

J'accolerai volontiers à votre pseudo "Magic" Steelson

J'ai donc retenu votre modèle pour traiter mes fichiers.

Votre solution présente toutefois un inconvénient lorsque le résultat en I est 0.00

Lorsque le seuil a été dépassé dès la ligne suivante, il est logique que le résultat soit 0.00 mais d'après ma compréhension, il semblerait qu'il affiche également 0.00 lorsque le seuil n'est pas atteint dans le délai imparti (74).

Exemples dans fichier Equiv 3:

1) Lignes 40 et 41. Ligne 40, le résultat 0 en I40 est justifié puisque le seuil 174.18 est atteint en A41. En revanche, pour la ligne 41, I41 affiche également 0 car les seuil 176.10 n'est atteint qu'en A166.

2)Idem pour lignes 53 et 54.

Avec la fonction index, que vous m'avez appris et que j'arrivais à comprendre, j'ai réussi à l'adapter pour traiter mon fichier sans vous embêter par la suite, ici je n'ose toucher à rien de crainte de provoquer la colère des Dieux d'Excel.

La seule idée qui me vient pour remédier à la confusion lorsque le résultat affiche 0 serait peut-être d'éliminer le délai de 74 et qu'il affiche par exemple 64789 si le seuil n'est atteint que 64789 lignes plus bas ou le numéro de la dernière ligne du fichier si le seuil n'est jamais atteint. Ou toute autre solution que vous me proposerez.

Avec mes remerciements et cordialement,

Max.

Rechercher des sujets similaires à "equiv recherche"