Vlookup qui cherche des strings partiels

Version imprimable

13/03/2013, 12h35
kilogram76

Vlookup qui cherche des strings partiels

Bonjour,

Je bloque sur le problème suivant:

Colonne A: données texte (nb de lignes pouvant varier!)
Colonne B: vlookup qui cherche des extaits de texte dans la colonne A et renvoie un numéro correspondant à un tableau sur les colonnes C et D
Colonne C: extaits de texte à rechercher
Colonne D: numéro correspondant à chaque extrait de texte

Exemple:
Colonne A: "Prélèvement SFR"
Colonne C: "SFR"
Colonne D: 1

Je voudrais que la colonne B renvoie donc 1 (par vlookup je pense). Attention, le nombre de lignes de la colonne A et du tableau en C et D varient à chaque utilisation. Je ne sais pas si il faut nécessairement coder en VBA ou s'il y a une fonction excel directement utilisable (genre un if avec un vlookup dedans).

Je bute la dessus depuis deux jours et ça me rends très triste :(

Merci pour votre aide!

Kilogram76 (enfin j'essaie...)
13/03/2013, 12h45
Philippe Tulliez

Bonjour,
Une petite remarque sur

Citation:

Colonne B: vlookup qui cherche des extaits de texte dans la colonne A et renvoie un numéro correspondant à un tableau sur les colonnes C et D

Une formule dans Excel ne peut envoyer un résultat dans une autre cellule que celle où elle se trouve seul le VBA le permet.
Toutefois, rien ne t'interdit d'utiliser RECHERCHEV dans une cellule en colonne C et une en colonne D pour obtenir la valeur recherchée.
C'est ce que l'on fait dans une facturation pour avoir le nom, l'adresse, et localité d'une personne.

Bonjour,
Pourquoi n'utilises-tu pas les filtres élaborés ou avancés d'excel. Je pense d'après ta question que cela devrait répondre à ta demande
Dans ta zone des critères tu mets l'étiquette de la colonne A et comme critère (cellule en dessous de l'étiquette) *SFR*
Si tu veux le n° de ligne de la ligne trouvée, tu ajoutes une colonne avec la formule LIGNE() et tu donnes comme titre à la colonne par exemple Ligne
et si tu veux exporter les données dans la zone d'exportation Le nom de l'étiquette de la colonne A et Ligne.
Voir tutoriel sur Les filtres avancés ou élaborés dans Excel
13/03/2013, 13h48
kilogram76

1 pièce(s) jointe(s)

Recherche de strings partiels et recherchev

Re-bonjour,

Merci pour tes réponses et le tuto sur les filtres avancés. Je suis en train de le lire et je devine le potentiel de l'outil! Mais je ne pense pas qu'il soit nécessaire de les utiliser pour mon problème.

Quand tu dis:

Citation:

Envoyé par Philippe Tulliez

Une formule dans Excel ne peut envoyer un résultat dans une autre cellule que celle où elle se trouve seul le VBA le permet.

en fait, je ne souhaite pas envoyer le résultat dans C ou D. C et D contiennent juste le tableau de correspondance (SFR -> 1). Je voudrais donc que la formule if/recherchev (?) dans la colonne B renvoie le 1 (toujours dans la colonne B) parce que excel a trouvé SFR dans le texte de la colonne A.

Je me permets de joindre un tout petit fichier qui rendra tout clair.

Merci!
JC
13/03/2013, 14h10
Philippe Tulliez

Bonjour,
Le problème c'est que malgré ton classeur, l'explication que tu donnes ne m'éclaire pas plus parce-que tu ne dis pas quelle est la liste de données de référence des données.
Si c'est que tu as en rouge dans la plage A3:A6 qui doit aller voir dans la plage C11:D15 et renvoyer la valeur qui est en colonne 2. Difficile de le faire sans VBA.
Si c'est partir des chaînes de caractères se trouvant en C11:C15 et qui sont des mots se trouvant dans la base de données en A3:A6 oui c'est possible.
Si j'ai compris la fonction doit renvoyer 5 si la chaîne n'est pas trouvée
Une formule qui renvoie la position dans A3:A6 de la chaîne partielle se trouvant en C11
Exemple compatible 2003-2010

Code:

=SI(ESTNA(EQUIV("*" & C11 & "*";$A$3:$A$6;0));5;EQUIV("*" & C11 & "*";$A$3:$A$6;0))

Exemple plus léger mais utilisable à partir de la version 2007

Code:

=SIERREUR(EQUIV("*" & C11 & "*";$A$3:$A$6;0);5)
13/03/2013, 15h08
kilogram76

Bonjour,

Pardon pour le manque de clarté. Ce que tu dis est exactement l'énoncé du problème et tu me confirmes donc qu'il n'y a pas d'autres solutions que VBA.

Citation:

Envoyé par Philippe Tulliez

quelle est la liste de données de référence des données.
Si c'est que tu as en rouge dans la plage A3:A6 qui doit aller voir dans la plage C11:D15 et renvoyer la valeur qui est en colonne 2. Difficile de le faire sans VBA

Mais ta réponse me donne des idées pour le faire en VBA. Un code du genre: tester avec equiv les lignes de la colonne A, une par une, contre les lignes de la colonne C, et renvoyer les nombres de la colonne D quand il y a une équivalence. Je vais le tenter avec mes bases rudimentaires en VBA.

Mais si toi ou quelqu'un d'autre a le courage d'essayer en VBA, ce serait super. J'attends deux ou trois jours pour voir et si pas de réponse, je marquerai le post comme résolu.

Merci!
JC

Bonjour,
Si j'ai bien compris ton problème voici une solution qui demande encore à être améliorée (test si plusieurs mots sont en rouge, possibilité de placer dans la formule une constante plutôt que faire référence à une cellule, tester qu'une seule cellule est référencée, etc...)
Nous partons du fait que la fonction fait référence à une cellule contenant une valeur alphanumérique dont un mot est en couleur (par défaut Rouge) et qu'il faut renvoyer la position de ce mot qui est en rouge dans une plage.
Exemple d'une fonction personnalisée
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Function FindWord(Text As Range, Table As Range, Optional color As Double = vbRed) As Long Dim c As Integer, Word As String Dim fn As WorksheetFunction: Set fn = Application.WorksheetFunction For c = 1 To Len(Text) With Text If .Characters(c, 1).Font.color = vbRed Then Word = Word & Mid(Text, c, 1) End With Next On Error Resume Next FindWord = fn.Match(Trim(Word), Table, 0) If Err Then FindWord = 0 End Function
On l'appelle depuis excel

Code:

=FindWord(B2;$H$2:$H$6)

La formule renvoie 0 si aucun mot dans la cellule B2 n'est en rouge et si le mot qui était en rouge n'est pas trouvé dans la plage $H$2:$H$6
Pour l'instant si plusieurs mots sont en rouge, la variable Word contient la concaténation des mots en couleur.

1 pièce(s) jointe(s)

Bonjour Jean-Christophe,
Bonjour Philippe,

Si j'ai bien compris, il ne s'agit pas de rechercher des mots avec une police en rouge mais la présence de mots clés dans les textes de la feuille 1 et de noter un numéro associé à ces mots clés.
Une solution avec la méthode Find

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sub Test()
Dim PlageS As Range, PlageC As Range, Cel As Range, C As Range
Dim firstAddress As String
    With Worksheets("Feuil2")
        Set PlageS = .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With
    With Worksheets("Feuil1")
        Set PlageC = .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With
    For Each Cel In PlageS
        Set C = PlageC.Find(Cel, LookIn:=xlValues, lookat:=xlPart)
        If Not C Is Nothing Then
            firstAddress = C.Address
            Do
                C.Offset(0, 1).Value = Cel.Offset(0, 1).Value
                Set C = PlageC.FindNext(C)
            Loop While Not C Is Nothing And C.Address <> firstAddress
        End If
    Next Cel
    For Each Cel In PlageC
        If Cel.Offset(0, 1).Value = "" Then Cel.Offset(0, 1).Value = 5
    Next Cel
End Sub

Cordialement.

13/03/2013, 17h44
Philippe Tulliez

Bonjour gFZT82,
C'est ce que j'avais cru comprendre au départ d'où ma première proposition mais la réponse donnée ensuite m'a laissé supposer le contraire.
Juste pour le fun, une solution sans VBA qui suppose qu'un mot de la liste ne peut pas être repris deux fois dans la phrase donc ne s'applique pas dans ce cas puisque la dernière phrase contient deux mots de cette liste.

Code:

=SOMMEPROD( (NON(ESTERR(CHERCHE(ListMots;A3)>1))) * (LIGNE(ListMots)))-LIGNE(ListMots)+1

ListMots est la plage contenant les mots cherchés et fait référence à C11:C15, la phrase est dans la cellule A3.
Si pas trouvé renvoie un chiffre négatif qui est égal à la première ligne de la plage ListMots
[EDIT]
Petite variante avec les mêmes contraintes mais qui renvoie le nombre de lignes de la plage ListMots si un des mots de cette plage n'est pas trouvé (donc 5 dans le cas présent).

Code:

=SI(SOMMEPROD( (NON(ESTERR(CHERCHE(ListMots;A3)>1))) * (LIGNE(ListMots)));SOMMEPROD( (NON(ESTERR(CHERCHE(ListMots;A3)>1))) * (LIGNE(ListMots)))-LIGNE(ListMots)+1;LIGNES(ListMots))
13/03/2013, 19h55
kilogram76

:ccool:

Merci beaucoup, Philippe etgFZT82!!!

A vous deux, vous avez plus que résolu mon problème en me donnant des alternatives qui marchent!