Regrouper les données trouvées dans une seule cellule

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


Sujets que vous pourriez aussi aimer :


Regrouper les valeurs trouvées

Dans ce nouveau volet, nous proposons de bâtir une nouvelle fonction VBA Excel capable de réunir les résultats de recherche dans une même cellule.



Sur l'exemple illustré par la capture, l'utilisateur travaille sur les données d'un parc automobile. Sur la droite du tableau, il désigne un constructeur à l'aide d'une liste déroulante. Aussitôt, dans une cellule du dessous, ce sont tous les modèles de la marque qui sont extraits du tableau et réunis.

Classeur Excel à télécharger
Nous suggérons d'appuyer l'étude sur ce parc automobile.

• Télécharger le fichier reunir-valeurs-cherchees.xlsm en cliquant sur ce lien,
• Cliquer droit sur le fichier réceptionné,
• En bas du menu contextuel, choisir la commande Propriétés,
• Dans la boîte de dialogue, cocher la case Débloquer puis valider par Ok,
• Double cliquer sur le fichier pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous trouvons le tableau du parc automobile entre les colonnes B et G. Sur la droite, plus précisément en cellule I4, l'utilisateur peut choisir l'un des constructeurs référencés, à l'aide d'une liste déroulante. La fonction VBA à construire doit donc exercer sa recherche dans la colonne C du tableau. Les modèles de la marque choisie doivent être réunis en-dessous, en cellule I7. La fonction VBA doit donc réaliser ses extractions concordantes à partir de la colonne D du tableau.

La fonction et ses paramètres
Nous devons donc créer une fonction VBA devant retourner une chaîne d'informations (As String) à partir de trois paramètres. Il s'agit du terme cherché, de la colonne de recherche et de la colonne de retour pour l'extraction.

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

Function RechTous(valCherche As String, colRech As Range, colRetour As Range) As String

End Function

Nous typons fort logiquement le premier paramètre, celui de la valeur cherchée comme un texte (As String). Les deux suivants, pour la colonne de recherche et pour la colonne d'extraction, sont typés comme des objets de plages de cellules (As Range). La fonction elle-même est typée comme un texte (As String) puisqu'elle doit répondre par un assemblage de modèles.

La déclaration des variables
Malgré ces paramètres dont l'utilisateur passera les valeurs au moment de la construction de la formule sur la feuille, quelques variables supplémentaires sont nécessaires.

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

...
Dim plage As Range: Dim chaine As String: Dim i As Byte

Set plage = colRech: chaine = ""
...

Nous déclarons l'objet plage comme un Range. Sa vocation est de représenter la colonne de recherche pour en parcourir tous les éléments. C'est la raison pour laquelle nous déclarons aussi une variable de boucle (i) que nous typons comme un entier court (Byte). Enfin, nous déclarons la variable chaine comme un texte pour pouvoir consolider les modèles concordants trouvés.

Puis, nous initialisons (Set) l'objet plage sur la colonne de recherche.

Parcourir la colonne de recherche
Pour trouver tous les modèles à consolider dans une même cellule, nous devons parcourir toutes les cellules de la colonne de recherche pour détecter chaque ligne hébergeant le constructeur choisi avec la liste déroulante.

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

...
For i = 1 To colRech.Count

Next i
...

Un objet de type Range offre notamment la propriété Count pour compter les éléments d'une plage de cellules. Grâce à elle, engagée dans une boucle sur la colonne de recherche (colRech), nous parcourons toutes les cellules de la colonne de recherche.

Eliminer les doublons
Désormais, un double critère doit être émis. En même temps que la cellule en cours d'analyse dans la colonne de recherche porte le constructeur demandé, le modèle dans la colonne voisine, ne doit pas avoir déjà été consolidé dans la variable chaine. Nous ne souhaitons pas répéter un même modèle. Il est donc question d'éliminer les doublons.

• Dans la boucle, créer l'instruction conditionnelle suivante :

...
If plage(i) = valCherche And InStr(1, chaine, colRetour(i) & " ") = 0 Then
chaine = chaine & colRetour(i) & " "
End If
...

Pour chaque cellule passée en revue dans la plage (plage(i)), nous cherchons la concordance avec le constructeur choisi (valCherche). Dans le même temps, nous nous assurons que le modèle correspondant n'a pas déjà été consolidé dans la variable chaine, à l'occasion d'un précédent passage. Pour cela, nous exploitons la fonction VBA InStr pour trouver sa présence (colRetour(i)) dans cette consolidation (chaine). Si le premier critère est vrai et que le modèle n'a pas déjà été intégré (=0), alors nous l'ajoutons à la suite des autres correspondances (chaine = chaine & colRetour(i) & " "), avec un espace de séparation.

Retourner la chaîne consolidée
Pour finir, nous devons retourner le résultat consolidé par cette fonction, donc une fois la boucle terminée. Comme vous le savez en VBA, une fonction répond par son propre nom. C'est donc dans ce nom que nous devons enregistrer le résultat final.

• Après la boucle, ajouter l'affectation suivante :

...
Next i

RechTous = chaine

End Function
...

Il ne nous reste plus qu'à tester cette fonction.

• Enregistrer les modifications (CTRL + S) et revenir sur la feuille Excel (ALT + Tab),
• Cliquer sur I7 pour sélectionner la cellule de l'extraction à produire,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Taper le nom de la nouvelle fonction, suivi d'une parenthèse ouvrante, soit : RechTous(,
• Cliquer sur la cellule I4 pour indiquer le constructeur cherché,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Désigner la colonne des marques, soit la plage de cellules C4:C91,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de retour,
• Désigner la colonne des modèles, soit la plage de cellules D4:D91,
• Fermer la parenthèse de la fonction RechTous,
• Puis, valider la formule par la touche Entrée du clavier,

Comme vous pouvez l'apprécier, tous les modèles de la marque choisie apparaissent consolidés dans la cellule du résultat et ce, sans doublons. Bien entendu, si vous changez de constructeur avec la liste déroulante, la consolidation s'ajuste aussitôt, en parfaite cohérence.

Remarque : Pour éliminer parfaitement les doublons et éviter les soucis de différences de casses, il est opportun d'exploiter la fonction UCASE, pour transformer les chaînes dans les comparaisons :

If colRech(i) = valCherche And InStr(1, UCase(chaine), UCase(colRetour(i)) & " ") = 0 Then...