Faire des recherches sur plusieurs mots clés en VBA Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Fonction d'extractions multiples

Dans ce nouveau volet, nous proposons de créer une fonction VBA Excel capable de réaliser des extractions multiples dans une même cellule, sur plusieurs lignes et sur plusieurs colonnes.



Sur l'exemple illustré par la capture, dans un tableau placé sur la gauche de la feuille, des équipes de commerciaux réalisent des ventes à des dates précises. Sur la droite, une petite base de données référence certains de ces commerciaux avec leurs villes d'origine et leurs niveaux. Dans les deux colonnes jaunes du premier tableau, une fonction VBA Excel effectue le regroupement des origines et des niveaux de ces commerciaux sur toute la hauteur du tableau.

Classeur Excel à télécharger
Nous suggérons d'appuyer l'étude sur ce classeur à récupérer.

• Télécharger le fichier extractions-multiples.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Dans le tableau de gauche, nous trouvons les équipes avec leurs résultats à des dates précises, comme nous l'avons découvert en préambule. Dans le tableau de droite, certains de ces commerciaux sont référencés dans des cellules indépendantes, avec pour chacun, leur ville et leur niveau. Les données de ce tableau portent l'intitulé personnes. Vous pouvez le constater en déployant la zone Nom en haut à gauche de la feuille Excel. Nous exploiterons ce nom dans la construction de la formule exploitant la fonction VBA Excel, une fois que nous aurons conçu cette dernière.

Créer la fonction
Nous proposons tout d'abord de créer la fonction et sa signature dans un module du projet actif. Bien sûr et comme vous le savez, si vous souhaitiez que cette fonction soit portable pour toutes les utilisations d'Excel, elle doit être créée dans un module du modèle Excel, le Personal.xlsb.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• En haut de l'éditeur, cliquer sur le menu Insertion,
• Dans les propositions, choisir l'option Module,

Ainsi, nous créons un nouveau module de code VBA et sa feuille de code, vierge pour l'instant, apparaît au centre de l'écran.

• Dans cette feuille, créer la fonction RechMult, comme suit :

Function RechMult(equipe As String, leTab As Range, numCol As Byte) As String

End Function

Nous la typons comme un texte (As String). Son rôle est en effet de retourner un ensemble de valeurs à concaténer dans une même cellule, celle du retour. Dans sa signature, le premier paramètre attend l'équipe. En deuxième paramètre, il s'agit de mentionner le tableau dans lequel les différents membres doivent être trouvés. Il s'agit donc d'une plage (As Range), celle qui est nommée personnes. Enfin, le dernier paramètre est numérique (As Byte). Il attend le numéro de la colonne de retour pour réunir les villes ou les niveaux des membres de l'équipe passée en premier argument de la fonction.

Les variables
Ensuite, nous avons besoin de variables. Il s'agit tout d'abord de récupérer les personnes de l'équipe passée en paramètre, indépendamment dans un tableau de variables. Il s'agit également de représenter le tableau de recherche, à parcourir précisément à la recherche de correspondances. Enfin, nous avons besoin de deux variables de boucles pour parcourir ces deux tableaux.

• Dans les bornes de la fonction, ajouter les déclarations et affectations suivantes :

...
Dim chaquePersonne: Dim tableau: Dim retour As String
Dim i As Byte: Dim j As Byte

chaquePersonne = Split(equipe, ",")
tableau = leTab
...

Nous ne typons pas les deux premières variables pour qu'elles prennent leur type implicitement au moment des affectations. Nous déclarons ensuite les deux variables de boucles comme des entiers courts (As Byte). Nous exploitons la fonction Split sur la cellule de l'équipe passée en premier paramètre pour découper les noms sur la virgule. C'est ainsi que la variable chaquePersonne est transformée en tableau de variables pour stocker ces noms indépendamment, dans des rangées différentes. Avec l'affectation suivante, nous faisons pointer la variable tableau sur la plage leTab, passée en deuxième paramètre, pour qu'elle en prenne possession par le code VBA. Enfin, nous exploiterons la variable retour pour concaténer les résultats.

Parcourir les équipiers
L'idée consiste ensuite à parcourir chaque personne de l'équipe dans le tableau de variables. Ainsi, nous pourrons les confronter à chaque personne du second tableau pour ressortir toutes les données attenantes à consolider et à rassembler dans la cellule appelante.

• A la suite du code VBA, créer la boucle For Next suivante :

...
For i = LBound(chaquePersonne) To UBound(chaquePersonne)

Next i
...

Nous exploitons les fonctions VBA LBound et UBound pour parcourir le tableau de variables du premier au dernier élément, soit du premier au dernier équipier séparé d'un autre par une virgule.

Rechercher dans le tableau
C'est désormais une seconde boucle que nous devons imbriquer dans la première pour rechercher chaque équipier dans le tableau des personnes. Ainsi, nous pourrons finalement concaténer les données associées pour les rassembler dans la cellule d'appel de la fonction construite par l'utilisateur.

• Dans la première boucle, ajouter la seconde boucle For Next suivante :

...
For i = LBound(chaquePersonne) To UBound(chaquePersonne)
For j = LBound(tableau) To UBound(tableau)

Next j
Next i
...

Là encore, nous exploitons les fonctions LBound et UBound pour parcourir toutes les cellules du tableau de recherche. C'est de cette façon que nous allons pouvoir déceler la potentielle présence de chaque personne de l'équipe afin de restituer et d'assembler ses données avec celles des autres, dans ce traitement récursif.

Chercher chaque équipier
Grâce à cette double boucle, nous allons pouvoir comparer chaque équipier de la cellule passée en premier paramètre de la fonction avec chaque personne du tableau.

• A l'intérieur de la double boucle, ajouter l'instruction VBA suivante :

...
If UCase(chaquePersonne(i)) = UCase(tableau(j, 1)) Then retour = retour & tableau(j, numCol) & ","
...

Nous exploitons la fonction UCase à deux reprises pour effectuer des comparaisons en majuscules. Ainsi, nous lissons les potentielles problèmes qui pourraient survenir à cause de différences de casses. Lorsque l'équipier en cours d'analyse par la première boucle (chaquePersonne(i)) est trouvé en première colonne, dans la cellule en cours d'analyse (tableau(j, 1)) par la seconde boucle, nous ajoutons l'information associée aux potentielles précédentes données trouvées, dans la variable retour. Pour cela, nous pointons sur la colonne (numCol) dont l'indice est passé en troisième paramètre de la fonction, lors de sa construction depuis la feuille.

Retourner les résultats
Une fois la double boucle terminée, la variable retour est censée avoir assemblé toutes les données attachées aux équipiers cherchés. Nous devons remonter ces informations sur la feuille. Vous le savez, une fonction VBA répond par son propre nom. C'est donc ce nom que nous devons affecter aux résultats.

• Après la double boucle, ajouter les deux lignes VBA suivantes :

...
If retour <> "" Then retour = Left(retour, Len(retour) - 1)
RechMult = retour
...

Nous réalisons d'abord un traitement intermédiaire. Comme nous assemblons les informations en les séparant avec une virgule, il en subsiste nécessairement une en bout de chaîne. Pour la supprimer, nous tronquons le résultat. Nous prélevons tous les caractères en partant du début (Left), jusqu'à l'avant dernier (Len(retour) - 1). En effet, la fonction Len renvoie le nombre de caractères d'une chaîne. Enfin, nous passons les informations concaténées à la fonction RechMult pour qu'elle puisse restituer le travail dans la cellule appelante.

Produire les extractions multiples
Le développement est terminé. Il est donc temps de tester cette fonction.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Sélectionner la première case des origines à regrouper en cliquant sur la cellule E4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Appeler la nouvelle fonction par son nom, suivie d'une parenthèse, soit : RechMult(,
• Désigner la cellule des premiers équipiers à chercher en cliquant sur C4,
• Puis, enfoncer trois fois de suite la touche F4 du clavier, ce qui donne : $C4,

De cette manière, nous figeons la colonne et libérons la ligne. En effet, nous allons répliquer la formule sur la colonne de droite et sur les lignes du dessous. Pour la colonne de droite, la recherche doit toujours être faite sur les équipiers. Donc, il ne doit pas y avoir de décalage en colonne au moment de la réplication. En revanche, au gré de la reproduction sur les lignes du dessous, ce sont bien les autres équipes qui doivent être analysées tour à tour.

• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Le désigner par son nom, soit : personnes,
• Taper un dernier point-virgule (;) pour passer dans l'argument de la colonne de retour,
• Inscrire la fonction donnant l'indice de colonne suivie d'une parenthèse, soit : Colonne(,
• Cliquer sur une cellule de la deuxième colonne, comme par exemple B2,

Ainsi, pour l'extraction des origines, nous allons bien pointer sur la deuxième colonne du tableau de recherche. Puis, avec la réplication sur la droite, nous allons naturellement pointer sur la troisième colonne de ce tableau (Colonne(C2)) pour extraire les niveaux.

• Fermer la parenthèse de la fonction Colonne,
• Fermer la parenthèse de la fonction RechMult,
• Enfin, valider la formule par CTRL + Entrée pour garder la cellule du résultat active,

Comme vous pouvez le voir, nous obtenons bien l'extraction des trois villes pour les trois commerciaux de la première équipe.

• Tirer la poignée du résultat sur la colonne de droite,

Cette fois ce sont bien les niveaux respectifs des commerciaux dont nous réalisons l'extraction.

• Enfin, double cliquer sur la poignée de la sélection,

De cette manière et pour les deux colonnes, nous répandons la logique de la formule d'extraction sur la hauteur du tableau.



Toutes les extractions multiples sont idéalement réalisées. Et vous pouvez très facilement en vérifier la parfaite cohérence, par rapport au tableau de recherche.

Dans certains cas, vous remarquez que les extractions produisent moins de résultats que l'équipe n'offre de commerciaux. Cela tient simplement au fait que certains équipiers ne sont pas référencés dans le tableau de recherche. Comme ils ne sont pas trouvés, leurs données sont naturellement ignorées par la double boucle de notre fonction VBA.