RECHERCHEV (repérer une suite de caractères) Résolu/Fermé

Question

Bonjour,

Je souhaite appliquer une fonction RECHERCHEV à une plage de cellules : au sein de cette plage, ma formule doit permettre de repérer une suite de caractères au sein des cellules, et non le contenu exhaustif des cellules.

Je vous joins la formule telle qu'elle existe actuellement :

=SI(ESTERREUR(GAUCHE(RECHERCHEV(A4;'$F$19:$AE$868;6;FAUX);3));0;GAUCHE(RECHERCHEV(A4;$F$19:$AE$868;6;FAUX);3))

Elle permet pour l'instant de renvoyer les trois premiers caractères de la sixième colonne de la matrice lorsqu'elle trouve dans la même ligne le contenu de la cellule A4.
Si elle ne le trouve pas, elle renvoie 0.
Or, le contenu de la cellule A4 est une suite de caractères qui peut se trouver plusieurs fois dans une même cellules de la plage concernée : en l'état actuel des choses, ma formule de repère pas ces occurrences et renvoie 0.

Y a-t'il des caractères, comme * par exemple qui pourraient m'aider ?
Est-ce que je peux réutiliser la fonction GAUCHE au sein de la fonction RECHERCHEV ? (ce ne serait cependant pas une solution optimum, car à terme toutes les occurrences doivent être relevées)

N'hésitez pas à me demander plus d'explications si je ne suis pas assez claire.

Merci d'avance pour votre aide !

michel_m · Answer

bonjour,
tu me dis si c'est ce que tu veux

dans une colonne , on recherche une cellule contenant 1 ou plusieurs occurrences de la valeur contenue dans A4

par exemple A4="aaa"

dans la colonne on a en dexcendant 1/="dd  2/= "fgaa1é" 3/ ="ppaaaffaaa"  4/= "a3a4a5"
c'est gagné pour le cas n°3 ?

identifiant-comment · Answer

C'est ça !

Pour être plus précise, la cellule A4 comprend un identifiant (ex: RG-DOV-DOU1) et dans la colonne de recherche, les cellules comprennent chacune un ou plusieurs identifiants. Par exemple :
RG-DOV-DOU1
RG-VRE-BUI6
RG-TRA-DOU5
J'ai besoin que la formule repère à chaque fois que l'une des cellules de la colonne comprend le bon identifiant.
C'est bien ce que tu avais compris ?

michel_m · Answer

DONC,
Si dans ta colonne de recherche, il y a RG OU DOV OU DOU c'est OK et on cherche la valeur dans même ligne colonne 6 ?

identifiant-comment · Answer

Je ne suis pas très douée avec les ET et OU logiques, mais il me semble plutôt que ce que je cherche c'est l'identifiant RG ET DOV ET DOU ET 1 ?

Il faut noter aussi que les formats d'identifiants risquent de changer, ce qui risque de faire planter du même coup toutes les formules.
Mais bon, ça, je m'adapterai : une fois que j'aurais compris le truc, il ne restera plus qu'à reproduire et ça m'intéresse de voir à quoi tu penses. :)

Tout de même, il n'existe pas un truc comme en sql (mais je ne sais même plus quel signe c'est en sql) qui permet de chercher une suite de caractères, quels que soient ceux qui précèdent et ceux qui suivent ?

eriiic · Answer

Bonsoir tout le monde,

Il faudrait que tu sois plus précises.
Veux-tu savoir seulement s'il est présent ?
Veux-tu savoir sa position (cellule) ?
Veux-tu savoir toutes les positions s'il est présent dans plusieurs cellules ?

Si c'est le 3ème cas ça serait plus simple d'ajouter une colonne E et mettre en E19 :
=EQUIV(FAUX;ESTERREUR(CHERCHE($A$4;F19:AE19));0)
(formule matricielle à valider avec shift+ctrl+entrée)

Si A4 est absent de la ligne tu as #N/A.
S'il est présent tu as le n° de colonne

identifiant-comment · Answer

Bonsoir Eriiiic !

Je vais essayer d'être plus précise, mais je dois avouer que j'ai tellement cherché que cela commence à s'embrouiller dans ma tête.
Ce que je veux c'est qu'une formule dans la colonne B(tableau2) dme renvoie des informations  en fonction de l'identifiant présent en colonne A(tableau2) : ex. RG-DOV-DOU1.
Or il se trouve que la colonne de mon tableau 1 (la matrice de ma formule) comprend plusieurs de ces identifiants par cellule.

Est-ce que je suis plus claire comme ça ?
Merci d'avance !

eriiic · Answer

Est-ce que je suis plus claire comme ça ? 
non
me renvoie des informations  tu trouves ça précis toi ?

Dépose un fichier exemple sur cijoint.fr et colle ici le lien fourni.
Et n'aies pas peur de mettre le maximum d'infos
eric

identifiant-comment · Answer

Brouf, t'as raison, ce que j'ai écris ne veut rien dire. Je devrais pas m'y remettre après avoir quitté le boulot :)

Je regarde ça demain matin et j'essaie de t'envoyer le fichier.

identifiant-comment · Answer

Re...

On va essayer de faire ça de façon basique.
Je veux que ma formule dise :
1. récupère la valeur dans la colonne Tableau1A
2. recherche cette valeur dans la colonne Tableau2D
3. lorsque tu trouves cette valeur dans la colonne Tableau2D, récupère le trigramme correspondant dans la colonne Tableau2A
4. affiche ce trigramme dans la colonne qui contient la formule (Tableau2B)

Le problème est que, si la colonne Tableau1A ne contient qu'une valeur (de type identifiant RG-DOV-DOU1) par cellule, la colonne Tableau2D, elle peut en contenir plusieurs.
Exemple de contenu d'un cellule de la colonne Tableau2D :
RG-DOV-DOU1 
RG-VRE-BUI6 
RG-TRA-DOU5 

J'ai donc besoin que, dans l'étape 2, la formule soit capable de dire plus précisément : recherche dans la colonne Tableau2D les cellules qui contiennent entre autres cet identifiant.

Je ne peux malheureusement pas t'envoyer les tableaux tels quels, clause de confidentialité oblige. Je te fais parvenir des modèles simplifiés.
Je suis de moins en moins sûre de pouvoir y parvenir avec des formules Excel. S'il faut passer par le VBA, ma foi, je ferai ce qu'il faut :)

identifiant-comment · Answer

Je tiens les tableaux prêts, mais le lien que tu m'as fourni ne fonctionne pas...

eriiic · Answer

Bonjour,

pour moi www.cijoint.fr fonctionne, sinon tu as aussi www.cjoint.com
ne pas oublier de coller le lien ici
eric

identifiant-comment · Answer

C'est fait, voici les liens !

http://www.cijoint.fr/cjlink.php?file=cj200904/cijqVT7V8J.xls 

http://www.cijoint.fr/cjlink.php?file=cj200904/cijMzGkXnG.xls

eriiic · Answer

Je suppose que tableau1 s'appelle tableau2 et vice-versa ???

identifiant-comment · Answer

J'y crois pas, j'ai fait ça ? Mais arrêtez moi !!
...Tu as raison, c'est bien ça, faut inverser les noms des fichiers.

eriiic · Answer

essaie en B3 :
=INDEX([Tableau1.xls]GGG!$A$1:$A$32;MAX(NON(ESTERREUR(CHERCHE(A4;[Tableau1.xls]GGG!$D$19:$D$32)))*LIGNE($D$19:$D$32)))
formule matricielle à valider par shift+ctrl+entrée, si ok s'entoure de { }

Attention il y a un piège, bien lire $A$1:$A$32 dans le 1er paramètre et ça doit rester $A$1:
S'il y a plusieurs réponses c'est la dernière qui est ramenée pour simplifier la formule

identifiant-comment · Answer

Mon vieux, j'ai pas encore compris comment t'as fait, et pour l'instant je ne suis pas encore sûre que ce soit exactement ce que je cherche, mais j'ai déjà réussi à le faire fonctionner dans une des cellules de l'un des fichiers que je t'avais envoyés et rien que pour ça t'es un génie.
Je te fais signe dès que j'ai fini de voir comment je peux déployer ta formule.
Merci !

eriiic · Answer

Pour comprendre la formule fais 'outils / audit de formules / evaluation de formule' et clique sur 'evaluer'
Elle s'execute pas à pas et te souligne la partie qui sera évaluée au prochain pas

identifiant-comment · Answer

Putain, ça marche. You're my hero.

Mais c'est frustrant, ça marche, mais je ne comprends pas comment. Tu veux bien m'expliquer ce que fait ta formule exactement, stp ?
J'ai fait quelques tests : c'est la dernière valeur trouvée (dans l'ordre des lignes auxquelles les cellules appartiennent) qui est retenue, c'est ça ?

Et pourquoi $A$1?

Il ne me reste plus qu'à faire apparaître un message d'erreur quand aucune valeur n'est trouvée, et je suis ok !
Merci, sincèrement, tu m'as rendu un grand service.

identifiant-comment · Answer

Comment tu fais pour remonter "Recensement des cas de test pour le lot XX" si aucune valeur n'est remontée ? C'est pour ça, la cellule A1 ?
J'ai changé la veleur de cette cellule, et je ne change pas de message d'erreur...

identifiant-comment · Answer

j'ai trouvééé !
S'il te reste deux secondes, ça m'intéresse toujours que tu me décortiques la formule...

(eh non, en fait il reste un bug :(. J'y retourne)

eriiic · Answer

Je veux bien t'expliquer mais avant rassure moi un peu...
Le trigramme de regroupement ce n'est pas systématiquement les caractères entre les 2 - hein ?

identifiant-comment · Answer

:)
Non non, c'est plus compliqué que ça. Les identifiants peuvent parfaitement changer complètement d'un projet à l'autre et ne plus contenir le trigramme. Ceux-là ne s'affichent comme ça que pour le document modèle. Et comme le but c'est de faire un doc qui convienne à tous les projets...
En tout cas, une formule de type =GAUCHE(STXT(A4;4;NBCAR(A4));3) n'aurait pas suffit.

Je suis toujours en train de galérer sur le fait que lorsqu'aucune valeur n'est remontée, il y a "Recensement des cas de test pour le lot XX" qui s'affiche. C'est bizarre, avec une formule de type SI(ESTERREUR(formule);"message d'erreur";(formule)), je n'y arrive pas non plus.
J'ai même essayé de mettre le message d'erreur dans la cellule A1 de l'autre tableau pour voir. :)
Et j'ai modifié dans mon fichier le texte "Recensement des cas de test pour le lot XX", ça n'a rien changé à celui que me remonte ma formule !

Je suppose qu'il doit y avoir une histoire du genre que l'on ne peut pas imbriquer n'importe comment une formule matricielle ?

eriiic · Answer

Chez moi j'ai 0 qui s'affiche si pas trouvé.
Pour simplifier ton test tu peux mettre :
=si(SOMME(NON(ESTERREUR(CHERCHE(A4;[Tableau1.xls]GGG!$D$19:$D$32)))*1);ta formule;"pas trouvé")
tjs en matriciel
L'explication après manger pour ménager le suspense ;-)

identifiant-comment · Answer

:) Ca marche. J'ai pas non plus compris comment tu faisais, mais ça marche !

eriiic · Answer

Allons-y, tu as mangé léger j'espère...
Le but est de trouver le n° de ligne où est ta donnée pour ramener ta valeur avec index(A1:A32,n°ligne)
(au passage voilà l'explication du A1)

Pour chercher une chaine dans une autre c'est cherche().
Si tu saisis =CHERCHE($A4;[Tableau1.xls]GGG!$D$19:$D$32) dans une cellule tu obtiens #valeur!.
Si tu met ton curseur dans la zone d'édition de la formule tu t'apercois en faisant F9 que la vraie réponse est une matrice des réponses de cherche() sur la plage $D$19:$D$32 soit {1;#VALEUR!;#VALEUR!;...;#VALEUR!} 
Retiens bien F9, c'est très pratique pour les formules complexes. Tu sélectionnes la partie de la formule qui t'interesse et avec F9 tu vois si ça te ramène bien ce que tu attends.

Il faut se débarrasser des valeurs d'erreur qui remontent toujours d'où :
NON(ESTERREUR({1;#VALEUR!;#VALEUR!;...;#VALEUR!}))  =>  {VRAI;FAUX;FAUX;...;FAUX}
qu'on multiplie par le n° ligne avec *LIGNE($D$19:$D$32) ce qui donne :
{VRAI;FAUX;FAUX;...;FAUX} * {19;20;...;32} => {19;0;0;...;0} car VRAI=1 et FAUX=0

On y est, les valeurs qui subsistent sont les n° de ligne où la chaine était présente, sur les autres on a 0.
Pour n'avoir plus qu'une valeur > 0 si elle existe au lieu d'une matrice on utilise 
MAX({19;0;0;...;0}) => 19
C'est pour ça qu'on a le dernier n° de ligne où a été trouvé la valeur. Ca rallongerait la formule de ramener la plus petite valeur >0

Il ne reste plus qu'a ramener A19 avec index(A1:A32,19)

That's good ?

Je te laisse trouver avec F9 ou 'evaluation de formule' pourquoi le test marche ;-)

identifiant-comment · Answer

Pffiou ! heureusement que j'ai mangé léger... Ca va me prendre l'aprem pour assimiler tout ça et j'aurais pas perdu ma journée !
Je mets ma question en Résolu et je te remercie !

identifiant-comment · Answer

Bonjour eriiic !

j'espère que tu auras ce message...
Il faut que je complique un peu la formule de l'autre jour.
Je ne dois plus remonter la dernière occurrence (MAX) mais celles qui correspondent à la plus grande valeur dans une tierce colonne.

Voici la formule telle qu'elle existe actuellement.
SI(SOMME(NON(ESTERREUR(CHERCHE(D12;MatFJ)))*1);INDEX(MatM;MAX(NON(ESTERREUR(CHERCHE(D12;MatFJ)))*LIGNE(MatFJ)));Params!$D$19

Crois-tu qu'il est possible de remplacer le MAX par un RECHERCHEV ?
Je ne vois pas très bien comment mettre ça en place...

eriiic · Answer

Bonjour,

Ben oui, soyons fou, compliquons un peu...

=INDEX([Tableau1.xls]GGG!$A$1:$A$32;INDEX((NON(ESTERREUR(CHERCHE(A4;[Tableau1.xls]GGG!$D$19:$D$32)))*LIGNE($D$19:$D$32));EQUIV(MAX([Tableau1.xls]GGG!$E$19:$E$32);[Tableau1.xls]GGG!$E$19:$E$32)))

Je n'ai pas repris tes plages nommées car n'ayant pas le fichier je risquais d'ajouter des erreurs et de chercher pour rien.
Et je t'avoue ne pas avoir eu le courage de faire des tests exhaustifs, je te laisse le soin de me dire si c'est ok ;-)

Et une tite leçon de morale au passage : mefie toi des formules un peu complexes, très difficiles à maintenir en cas d'évolution 6 mois plus tard...

eric

identifiant-comment · Answer

Merci pour ta réponse eriiic...
Et tu as raison, c'est galère à mettre en place.
D'ailleurs je tombe toujours sur un résultat à "pas de cluster associé" (je ne sais toujours pas d'où il sort celui-là...
Je retente avec en mêlant la formule avec ta solution du post 23.

identifiant-comment · Answer

C'est quoi exactement la plage que tu mets dans EQUIV ? La plage tierce dont je cherche la plus grande valeur, c'est ça ?

identifiant-comment · Answer

Re...

Cette formule, soit :
- elle me donne toujours le même résultat quelle que soit la cellule que j'appelle
- elle me renvoie un résultat à 0
- elle m'indique "pas de cluster associé"...
Et j'arrive même pas à établir de règle pour voir ce qui ne va pas.

Le plus beau c'est que la même formule, à deux emplacements différents donne deux résultat différents.
Je nage un peu là...

identifiant-comment · Answer

Et dans LIGNE(), c'est quelle plage que tu appelles ?
Oui, ça en fait des questions... :)

eriiic · Answer

Re,

oui effectivement, si je met plus de valeurs en colonne E (là où on cherche le maxi) ça ne marche plus.
Je regarderai d'un peu plus près plus tard.
C'est le genre de pb qui mériterait une fonction personnalisée pour simplifier...
A+
eric

eriiic · Answer

Re re,

A mettre dans un module :
Function Trigramme(ID As String, PlageReglesGest As Range, OffsetPoids As Long, OffsetTrigramme As Long)
    Dim i As Long, p As Long, poids As Variant, rep As String, c As Range
    Application.Volatile
    rep = "non trouvé"
    For Each c In PlageReglesGest
        p = InStr(c.Value, ID)
        If p > 0 And c.Offset(0, OffsetPoids).Value > poids Then
            poids = c.Offset(0, OffsetPoids).Value
            rep = c.Offset(0, OffsetTrigramme).Value
        End If
    Next c
    Trigramme = rep
End Function

et la formule devient :
=Trigramme(A4;[Tableau1.xls]GGG!$D$19:$D$21;1;-3)

Function Trigramme(ID, PlageReglesGest, OffsetPoids, OffsetTrigramme)
ID : l'ID recherché
PlageReglesGest  : la plage des N° Règle de gestion générale
OffsetPoids : l'offset en colonnes où est le poids par rapport à la colonne PlageReglesGest
OffsetTrigramme : l'offset en colonnes où est le trigramme par rapport à la colonne PlageReglesGest

Je ne fais aucun contrôle sur les parametres saisis. PlageReglesGest doit faire 1 colonne de large bien sûr.
Avec 'inserer / fonctions / fonctions personnalisées' tu peux désigner les plages et cellules à la souris

Comme d'hab je te laisse tester...
eric

identifiant-comment · Answer

Waw, on ne fait plus dans le touristique là...
Je te remercie et comme d'hab je teste et je te tiens au courant !

identifiant-comment · Answer

Arf...
Effectivement, c'est moins lourd, ça réagit plus vite, mais ça me remonte toujours le premier résultat trouvé... Je continue de chercher !

identifiant-comment · Answer

Non non, c'est bon, je comprends mieux.
Je pense que je vais pouvoir y arriver.
Et puis, j'ai trouvé une solution temporaire pour contourner le problème, donc ici, il y a moins de stress.

Merci beaucoup !!

identifiant-comment · Answer

:)
Normal, c'est une réponse de juste-avant-le-WE...
Je n'en peux plus, je raccroche, sinon je vais continuer à raconter n'importe quoi.
je voulais dire qu'effectivement, la solution devait être dans 
"- si offset x >1 => x colonnes à droite de gestion...jenesaisplusquoi 
- si offset x <1 => x colonne à gauche de gestion...jenesaisplusquoi "
parce que ce n'était pas exactement ce que j'avais compris, et que toi en revanche, tu avais bien compris ma demande.

Je te souhaite un très bon WE et see you lundi matin !

RECHERCHEV (repérer une suite de caractères)

38 réponses

Discussions similaires