Recherche V : valeur cherchée multiple séparée par des virgules

M

magicglide

XLDnaute Nouveau
Bonjour,
En colonne A, des cellules contenant plusieurs valeurs numériques par cellule, séparées par des ','.
A l'aide de la fonction 'recherchev', je souhaite afficher en colonne B, la valeur textuelle cette fois (toujours séparée par des ',') trouvée dans un tableau situé en colonne E (numérotation) et F, les valeurs textuelles.

La difficulté est de trouver la bonne formule de dé-concaténation pour le paramètre 'valeur cherchée' de recherchev qui permet de lire X valeurs numériques séparées par les ','

Petite feuille excel jointe qui explique mon propos.
D'avance merci pour vos suggestions.
;)
 

Pièces jointes

  • EDL_ex.xlsx
    12.4 KB · Affichages: 78
Trier par date Trier par votes
Efgé

Efgé

XLDnaute Barbatruc
Re : Recherche V : valeur cherchée multiple séparée par des virgules

Bonjour magicglide

En attendant un tour de magie formulistique, un proposition pas macro:
VB: 
Private Sub CommandButton1_Click()
Dim I&, J&
Dim Dico As Object
Dim Tmp As Variant, Data As Variant, Report As Variant

Set Dico = CreateObject("scripting.dictionary")
With Sheets("Feuil3")
    Data = .Range(.Cells(2, 5), .Cells(.Rows.Count, 6).End(3))
    For I = LBound(Data, 1) To UBound(Data, 1)
        Dico(CStr(Data(I, 1))) = Data(I, 2)
    Next I
    Report = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(3)(1, 2))
    For I = LBound(Report, 1) To UBound(Report, 1)
        Tmp = Split(Report(I, 1), ",")
        For J = LBound(Tmp) To UBound(Tmp)
            Tmp(J) = Dico(Tmp(J))
        Next J
        Report(I, 2) = Join(Tmp, ",")
    Next I
    Cells(2, 2).Resize(UBound(Report, 1), 1) = Application.Index(Report, , 2)
End With
End Sub
Cordialement
 

Pièces jointes

  • EDL_ex(2).xls
    44 KB · Affichages: 73
Vote positif 0 Vote négatif
Efgé

Efgé

XLDnaute Barbatruc
Re : Recherche V : valeur cherchée multiple séparée par des virgules

Bonjour magicglide,
Pour le fun :

Dommage que tu ne veuilles pas de macro, car on peut aller plus vite
Pour 12 900 lignes, le premier code en 0.50 seconde, celui-ci 0.40
VB: 
Private Sub CommandButton1_Click()
Dim I&, J&
Dim Dico As Object
Dim Data As Variant, Report As Variant


Set Dico = CreateObject("scripting.dictionary")
With Sheets("Feuil3")
    Data = .Range(.Cells(2, 5), .Cells(.Rows.Count, 6).End(3))
    Report = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(3))
    
    For I = LBound(Data, 1) To UBound(Data, 1)
        Dico(CStr(Data(I, 1))) = Data(I, 2)
    Next I
    
    For I = LBound(Report, 1) To UBound(Report, 1)
        Report(I, 1) = Split(Report(I, 1), ",")
        For J = LBound(Report(I, 1)) To UBound(Report(I, 1))
            Report(I, 1)(J) = Dico(Report(I, 1)(J))
        Next J
        Report(I, 1) = Join(Report(I, 1), ",")
    Next I
    
    Cells(2, 2).Resize(UBound(Report, 1), 1) = Report
End With
End Sub

Cordialement
 

Pièces jointes

  • EDL_ex(3).xls
    42 KB · Affichages: 60
Vote positif 0 Vote négatif
CISCO

CISCO

XLDnaute Barbatruc
Re : Recherche V : valeur cherchée multiple séparée par des virgules

Bonsoir

Est-ce indispensable de garder le tableau tel que ? Si non, on peut très bien mettre les valeurs dans plusieurs colonnes contiguës très rapidement, puis faire plusieurs RECHEVRCHEV.

@ plus
 
Vote positif 0 Vote négatif
CISCO

CISCO

XLDnaute Barbatruc
Re : Recherche V : valeur cherchée multiple séparée par des virgules

Bonsoir

Le début d'une solution en pièce jointe. Il faut ajouter d'autres SIERREUR(DECALER... pour que la formule soit correcte dans tous les cas. En plus, cela ne prend pas bien en compte les nombres <10... A modifier donc.

@ plus
 

Pièces jointes

  • virgules.xlsx
    13.3 KB · Affichages: 63
  • virgules.xlsx
    13.3 KB · Affichages: 61
  • virgules.xlsx
    13.3 KB · Affichages: 63
Vote positif 0 Vote négatif
CISCO

CISCO

XLDnaute Barbatruc
Re : Recherche V : valeur cherchée multiple séparée par des virgules

Bonsoir

Ce n'est qu'une approche incomplète. Je peux faire avec le dernier principe proposé, mais cela donne des formules à rallonge peu polyvalentes... Je cherche une autre idée...

@ plus
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : Recherche V : valeur cherchée multiple séparée par des virgules

Bonjour magicglide, Efgé, CISCO,

Avec cette solution on peut traiter jusqu'à 20 nombres dans une cellule en colonne A.

La matrice M est définie par la fonction macro Excel 4.0 EVALUER :

Code: 
=EVALUER("{"&SUBSTITUE(Feuil3!$A2;",";";")&"}")
Nota : il y a toujours une virgule à la fin du résultat.

On pourrait la retirer mais la formule est assez longue comme ça...

A+
 

Pièces jointes

  • EDL_ex(1).xlsm
    13.5 KB · Affichages: 60
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : Recherche V : valeur cherchée multiple séparée par des virgules

Re,

Bon allez on retire la dernière virgule :cool:

Le 1er SIERREUR évite la valeur d'erreur en B29 et B30.

Fichier (2).

A+
 

Pièces jointes

  • EDL_ex(2).xlsm
    14.4 KB · Affichages: 57
Vote positif 0 Vote négatif
CISCO

CISCO

XLDnaute Barbatruc
Re : Recherche V : valeur cherchée multiple séparée par des virgules

Bonjour à tous

Une autre possibilité en pièce jointe qui ne fonctionne que pour 16 nombres au max dans la cellule de la colonne A, ces nombres étant tous compris entre 0 et 100. La formule ne fonctionne avec les DECALER que si les nombres dans la colonne D sont dans l'ordre exact, sans trou...

Il est assez facile de rajouter un 17 ème nombre en ajoutant un &SIERREUR(DECALER.... La formule est longue, mais bon, n'ayant pas trouvé d'autres méthodes, en passant par exemple par un PETITE.VALEUR ou une formule matricielle...

@ plus

P.S : Pour prendre en compte simplement les nombres compris entre 0 et 10, j'ai défini 2 noms, qui transforment ",1," en ",01,", ",2," en ",02," et ainsi de suite.
 

Pièces jointes

  • virgules.xlsx
    15.7 KB · Affichages: 53
  • virgules.xlsx
    15.7 KB · Affichages: 60
  • virgules.xlsx
    15.7 KB · Affichages: 65
Dernière édition:
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : Recherche V : valeur cherchée multiple séparée par des virgules

Re,

On peut aussi créer autant de noms définis V_1 V_2 V_3 etc... qu'il y a de valeurs en colonnes E et F.

Voici les formules, B1 étant sélectionnée :

V_1 => =SUBSTITUE(","&Feuil3!$A1&",";","&Feuil3!$E$2&",";","&Feuil3!$F$2&",")

V_2 => =SUBSTITUE(V_1;","&Feuil3!$E$3&",";","&Feuil3!$F$3&",")

V_3 => =SUBSTITUE(V_2;","&Feuil3!$E$4&",";","&Feuil3!$F$4&",")

Bien entendu, plutôt que de s'embêter à les créer tous à la main, exécuter cette macro :

Code: 
Sub CréerNoms()
Dim n
ThisWorkbook.Names.Add "V_1", "=SUBSTITUTE("",""&Feuil3!$A1&"","","",""&Feuil3!$E$2&"","","",""&Feuil3!$F$2&"","")"
For n = 2 To 48
ThisWorkbook.Names.Add "V_" & n, "=SUBSTITUTE(V_" & n - 1 & ","",""&Feuil3!$E$" & n + 1 & "&"","","",""&Feuil3!$F$" & n + 1 & "&"","")"
Next
End Sub
Ensuite c'est simple, formule en B2 à tirer vers le bas :

Code: 
=STXT(V_48;2;NBCAR(V_48)-2)
Fichier (3), en .xlsx cette fois.

A+
 

Pièces jointes

  • EDL_ex(3).xlsx
    15.1 KB · Affichages: 55
Vote positif 0 Vote négatif
M

magicglide

XLDnaute Nouveau
[Résolu] : Recherche V : valeur cherchée multiple séparée par des virgules

Efgé, Cisco, Job75, merci à tous les 3 pour vos suggestions et solutions.
Je vais retenir une des formules pour laquelle j'aurai un meilleur contrôle que le VBA - je ne programme pas.
Encore un Grand Merci.
;)
 
Dernière édition:
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : Recherche V : valeur cherchée multiple séparée par des virgules

Re,

Pour terminer ce fil intéressant, un peu de VBA avec cette fonction très simple :

Code: 
Function RemplaceNombre$(s, r As Range, Optional sep$ = ",")
Dim i%
On Error Resume Next 'si la recherche n'aboutit pas
s = Split(s, sep)
For i = 0 To UBound(s)
  s(i) = Application.VLookup(Val(s(i)), r, 2, 0)
Next
RemplaceNombre = Join(s, sep)
End Function
Fichier joint.

A+
 

Pièces jointes

  • EDL_ex VBA(1).xlsm
    21.1 KB · Affichages: 75
Dernière édition:
Vote positif 0 Vote négatif

Discussions similaires

L
  • Question
XL 2019 Filtre avancé recherche multiple, remplir des champs manuellement
Réponses
6
Affichages
289
LoshiMoshi
L
K
  • Résolu(e)
XL 2016 Créer une formule qui permette de vérifier si une colonne est composée d'une ou plusieurs valeurs
Réponses
7
Affichages
836
Kelbret
K
chaelie2015
  • Résolu(e)
XL 2013 (RESOLU) Affichage des en-têtes de tableau selon l'ordre
2
Réponses
20
Affichages
791
chaelie2015
chaelie2015
R
  • Question
XL 2019 liste triée et séparée par des virgules
Réponses
3
Affichages
721
rocornet
R
Naced
  • Question
Microsoft 365 Equiv Plusieurs colonnes, ou plusieurs valeurs dans une cellule
Réponses
10
Affichages
882
TooFatBoy
TooFatBoy
oguruma
POWERQUERY :: Equivalent des fonctions Excel INDEX et EQUIV
Réponses
0
Affichages
285
oguruma
oguruma
oguruma
POWERQUERY :: Combinaisons (Table.Combine) de dossiers contenant des fichiers .CSV ou des fichiers Excel - V2
Réponses
0
Affichages
278
oguruma
oguruma
B
  • Résolu(e)
XL 2016 Selectionner date la plus proche au supérieur de la date d'aujourd'hui
Réponses
5
Affichages
864
babacool22
B
M
  • Résolu(e)
XL 2013 Empiler des valeurs de cellule dans une même colonne
Réponses
6
Affichages
1 K
Michel J.
M
G
  • Question
XL 2013 Excel VBA
Réponses
2
Affichages
1 K
Golfolies
G

Membres actuellement en ligne

9 membres et 65 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 329
Messages
2 087 334
Membres
103 519
dernier inscrit
Thomas_grc11