Créer une liste déroulante Excel avec des suggestions de recherche

Nous utilisons tous Google dans le cadre de notre routine quotidienne. L'une de ses fonctionnalités est la suggestion de recherche, où Google agit intelligemment et nous donne une liste de suggestions pendant que nous tapons.

Dans ce didacticiel, vous apprendrez à créer une liste déroulante consultable dans Excel, c'est-à-dire une liste déroulante qui affichera les éléments correspondants au fur et à mesure que vous tapez.

Vous trouverez ci-dessous une vidéo de ce tutoriel (au cas où vous préféreriez regarder une vidéo plutôt que lire le texte).

Liste déroulante consultable dans Excel

Pour les besoins de ce tutoriel, j'utilise les données des 20 premiers pays par PIB.

L'intention est de créer une liste déroulante Excel avec un mécanisme de suggestion de recherche, de sorte qu'elle affiche une liste déroulante avec les options correspondantes lorsque je tape dans la barre de recherche.

Quelque chose comme indiqué ci-dessous :

Pour suivre, téléchargez le fichier d'exemple à partir d'ici

La création de la liste déroulante consultable dans Excel serait un processus en trois parties :

1. Configuration du champ de recherche.
2. Paramétrage des données.
3. Écrire un court code VBA pour le faire fonctionner.

Étape 1 - Configuration de la zone de recherche

Dans cette première étape, je vais utiliser une zone de liste déroulante et la configurer pour que lorsque vous la saisissez, le texte soit également reflété dans une cellule en temps réel.

Voici les étapes à suivre :

1. Accédez à l'onglet Développeur -> Insérer -> Contrôles ActiveX -> Zone de liste déroulante (contrôle ActiveX).
   • Il est possible que vous ne trouviez pas l'onglet développeur dans le ruban. Par défaut, il est masqué et doit être activé. Cliquez ici pour savoir comment obtenir l'onglet développeur dans le ruban dans Excel.
2. Déplacez votre curseur sur la zone de la feuille de calcul et cliquez n'importe où. Il insèrera une zone de liste déroulante.
3. Cliquez avec le bouton droit sur la zone de liste déroulante et sélectionnez Propriétés.
4. Dans la boîte de dialogue des propriétés, apportez les modifications suivantes :
   • Sélection automatique de mots : Faux
   • Cellule Liée : B3
   • ListFillRange : DropDownList (nous allons créer une plage nommée avec ce nom à l'étape 2)
   • Entrée de correspondance : 2 - fmMatchEntryNone

(La cellule B3 est liée à la zone de liste déroulante, ce qui signifie que tout ce que vous tapez dans la zone de liste déroulante est entré dans B3)

4. Allez dans l'onglet Développeur et cliquez sur Mode Conception. Cela vous permettra de saisir du texte dans la zone de liste déroulante. De plus, étant donné que la cellule B3 est liée à la zone de liste déroulante, tout texte que vous entrez dans la zone de liste déroulante serait également reflété dans B3 en temps réel.

Étape 2 - Définition des données

Maintenant que le champ de recherche est défini, nous devons mettre les données en place. L'idée est que dès que vous tapez quelque chose dans le champ de recherche, il n'affiche que les éléments qui contiennent ce texte.

Pour ce faire, nous utiliserons

• Trois colonnes d'aide.
• Une plage nommée dynamique.

Aide Colonne 1

Mettez la formule suivante dans la cellule F3 et faites-la glisser pour toute la colonne (F3:F22)

=--ISNUMBER(IFERREUR(RECHERCHE($B$3,E3,1),""))

Cette formule renvoie 1 lorsque le texte dans la zone de liste déroulante est là dans le nom du pays sur la gauche. Par exemple, si vous tapez UNI, seules les valeurs de UniÉtats-Unis et United Kingdom sont 1 et toutes les valeurs restantes sont 0.

Aide Colonne 2

Mettez la formule suivante dans la cellule G3 et faites-la glisser pour toute la colonne (G3:G22)

=SI(F3=1,COUNTIF($F$3:F3,1),"") 

Cette formule renvoie 1 pour la première occurrence où le texte de la zone de liste déroulante correspond au nom du pays, 2 pour la deuxième occurrence, 3 pour la troisième et ainsi de suite. Par exemple, si vous tapez UNI, la cellule G3 affichera 1 car elle correspond aux États-Unis et G9 affichera 2 car elle correspond au Royaume-Uni. Le reste des cellules sera vide.

Aide Colonne 3

Mettez la formule suivante dans la cellule H3 et faites-la glisser pour toute la colonne (H3:H22)

=SIERREUR(INDICE($E$3:$E$22,MATCH(LIGNES($G$3:G3),$G$3:$G$22,0)),"") 

Cette formule empile tous les noms correspondants sans aucune cellule vide entre eux. Par exemple, si vous tapez UNI, cette colonne affichera 2 et 9 ensemble, et toutes les cellules resteront vides.

Création de la plage nommée dynamique

Maintenant que les colonnes d'aide sont en place, nous devons créer la plage nommée dynamique. Cette plage nommée ne fera référence qu'aux valeurs qui correspondent au texte entré dans la zone de liste déroulante. Nous utiliserons cette plage nommée dynamique pour afficher les valeurs dans la liste déroulante.

Noter: À l'étape 1, nous avons entré DropDownList dans l'option ListFillRange. Maintenant, nous allons créer la plage nommée avec le même nom.

Voici les étapes pour le créer :

1. Allez dans Formules -> Gestionnaire de noms.
2. Dans la boîte de dialogue du gestionnaire de noms, cliquez sur Nouveau. Cela ouvrira une boîte de dialogue Nouveau nom.
3. Dans le champ Nom, entrez DropDownList
4. Dans le champ Refers to, saisissez la formule : =$H$3:INDEX($H$3:$H$22,MAX($G$3:$G$22),1)

Étape 3 - Mettre le code VBA au travail

Nous y sommes presque.

La dernière partie consiste à écrire un court code VBA. Ce code rend la liste déroulante dynamique de sorte qu'elle affiche les éléments/noms correspondants lorsque vous tapez dans la zone de recherche.

Pour ajouter ce code à votre classeur :

1. Cliquez avec le bouton droit sur l'onglet Feuille de calcul et sélectionnez Afficher le code.
2. Dans la fenêtre VBA, copiez et collez le code suivant :

   Sous-combo privé privé1_Change() ComboBox1.ListFillRange = "DropDownList" Moi.ComboBox1.DropDown Fin du sous-marin

C'est ça!!

Vous êtes tous configurés avec votre propre barre de recherche de type Google qui affiche les éléments correspondants au fur et à mesure que vous les saisissez.

Pour une meilleure apparence, vous pouvez couvrir la cellule B3 avec la zone de liste déroulante et masquer toutes les colonnes d'aide. Vous pouvez maintenant montrer un peu avec cette astuce Excel incroyable.

Pour suivre, téléchargez le fichier ici

Qu'en penses-tu? Seriez-vous en mesure d'utiliser cette liste déroulante de suggestions de recherche dans votre travail ? Faites-moi part de vos impressions en laissant un commentaire.

Si vous avez apprécié ce didacticiel, je suis sûr que vous aimeriez également les didacticiels Excel suivants :

• Filtre dynamique - Extrayez les données correspondantes pendant que vous tapez.
• Extraire les données en fonction d'une sélection de liste déroulante.
• Création de listes déroulantes dépendantes dans Excel.
• Le guide ultime pour utiliser la fonction Excel VLOOKUP.
• Comment faire plusieurs sélections dans une liste déroulante dans Excel.
• Comment insérer et utiliser une case à cocher dans Excel.