Réunir dans une cellule les données d'un critère

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


Sujets que vous pourriez aussi aimer :


Recherches et extractions multiples

Dans ce nouveau chapitre, nous proposons de réunir dans une même cellule toutes les informations d'une liste de données correspondant à un critère défini dynamiquement.



Sur l'exemple illustré par la capture, un premier tableau liste des participants, avec leur pays d'origine. Dans un second tableau sur la droite, c'est une fonction VBA Excel qui réunit les participants d'un même pays dans une même cellule.

Classeur Excel à télécharger
Pour créer cette nouvelle fonction, nous suggérons d'appuyer les travaux sur un classeur abritant cette liste de personnes, à regrouper par pays.

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

La fonction à créer doit donc être appliquée en colonne G, entre les cellules G4 et G8.

La fonction de recherche multiple
Avant toute chose, nous devons commencer par créer la fonction avec ses paramètres et ses variables.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur sur la gauche, double cliquer sur l'élément Module1,
• Dans sa feuille de code au centre de l'écran, créer la fonction MultRech comme suit :

Function MultRech(aChercher As String, tabR As Range, colR As Integer) As String
Dim i As Byte: Dim j As Byte
Dim retour As String

End Function

Nous la déclarons donc avec trois paramètres en attente. Ils représentent respectivement la valeur cherchée (Le pays ici), le tableau de recherche et la colonne de retour comme pour une rechercheV. Dans les bornes de la fonction, nous déclarons alors des variables de boucle (i et j). Nous les utiliserons pour parcourir les cellules du tableau de recherche. Enfin, nous exploiterons la variable retour pour consolider les résultats concordants à retourner.

Parcourir le tableau de recherche
Puisque les variables nécessaires sont désormais déclarées, nous pouvons les exploiter notamment pour parcourir les cellules du tableau de recherche.

• A la suite du code, ajouter les instructions VBA suivantes :

...
For i = 1 To tabR.Cells.Count
If tabR.Cells(i, 1) = aChercher Then

echap:
End If
Next i
...

Nous parcourons toutes les cellules du tableau de recherche en partant de la première (1) et en prolongeant jusqu'à la dernière (tabR.Cells.Count). A chaque passage, nous cherchons à savoir si l'information portée par la cellule de la première colonne pour la ligne en cours (tabR.Cells(i, 1)) correspond bien à la donnée sur le pays cherché (aChercher). Bref, nous préparons le terrain. Vous notez aussi la présence d'une étiquette (echap:) que nous utiliserons pour échapper une boucle imbriquée dans le cas de doublons à ne pas cumuler.

Ignorer les doublons
Maintenant, pour ne pas cumuler un nom déjà importé, nous devons de nouveau parcourir les cellules du tableau, de la première à celle en cours d'analyse. L'objectif est de déceler si la cellule correspondante et en cours d'analyse ne correspond pas à un nom déjà regroupé dans la consolidation.

• Dans le If et avant l'étiquette, ajouter la nouvelle boucle suivante :

...
If tabR.Cells(i, 1) = aChercher Then
For j = 1 To i - 1
If tabR.Cells(j, 1) = aChercher Then
If tabR.Cells(j, colR) = tabR.Cells(i, colR) Then
GoTo echap
End If
End If
Next j
echap:
End If
...

En repartant de la première cellule jusqu'à celle en cours (For j = 1 To i - 1), nous testons tout d'abord les correspondances avec la valeur cherchée, donc sur les cellules antérieures. Si cette correspondance sur le pays est avérée et que dans le même temps le nom a déjà été sorti (tabR.Cells(j, colR) = tabR.Cells(i,colR)), nous sortons de la boucle imbriquée (echap:) pour ne pas réaliser le cumul. Cumul que nous n'avons d'ailleurs pas encore entrepris.

Consolider les concordances
Ce cumul doit être réalisé avant l'échappement de l'étiquette et bien sûr dans les bornes de la première boucle.

• Avant l'étiquette, ajouter l'instruction VBA suivante :

...
End If
Next j
retour = retour & " " & tabR.Cells(i, colR) & ","
echap:
End If
...

Très simplement, nous consolidons (&) l'actuelle concordance non doublée avec les précédentes dans la variable retour.

Retourner les extractions réunies
Pour le final, il ne nous reste plus qu'à renvoyer les valeurs consolidées, correspondant au pays recherché. Ces données cumulées sont regroupées dans la variable retour que nous devons affecter au nom même de la fonction VBA comme vous le savez, mais une fois l'analyse complètement terminée.

• Avant la clôture de la fonction, ajouter l'instruction de retour suivante :

...
echap:
End If
Next i
MultRech = Left(retour, Len(retour) - 1)
End Function
...

Nous exploitons la fonction Left pour supprimer le dernier caractère de la virgule de séparation, puisque plus aucun autre nom n'est attendu en fin de chaîne. La fonction Len est celle qui renseigne sur la longueur de la chaîne de retour. Donc, nous prélevons jusqu'à l'avant dernier caractère (-1), avant la dernière virgule issue de la concaténation.

Regrouper les données à extraire
Il est maintenant temps de tester le bon fonctionnement de notre fonction destinée à extraire les données à regrouper sur une même valeur de recherche.

• Enregistrer les modifications (CTRL + S) et revenir sur la feuille Excel (Alt + Tab),
• Sélectionner la première cellule d'extraction en cliquant sur sa cellule G4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire le nom de la fonction de recherche multiple, suivi d'une parenthèse, soit : MultRech(,
• Désigner le pays cherché en cliquant sur sa cellule F4,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner toutes les données du tableau source en sélectionnant la plage C4:D15,
• Enfoncer la touche F4 du clavier pour la figer complètement, ce qui donne : $C$4:$D$15,

En effet, ce calcul est destiné à être reproduit sur les cellules du dessous. Au gré de la réplication, les bornes de la zone de recherche ne doivent pas bouger.

• Taper un nouveau point-virgule (;) pour passer dans l'argument de la colonne de retour,
• Inscrire le chiffre 2 pour désigner la seconde colonne du tableau de recherche,
• Fermer la parenthèse de la fonction MultRech,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

De cette manière, nous conservons active la cellule du résultat. Et comme vous pouvez le voir, une personne semble appartenir au premier pays (l'Allemagne), ce qui apparaît tout à fait cohérent à la lecture du tableau source.

• Double cliquer sur la poignée du résultat,

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



Comme vous pouvez l'apprécier, toutes les informations éparpillées à l'origine et correspondant à un même critère, sont regroupées dans une même cellule et ce, tout en excluant les répétitions.