[VIDEO] Comment ajouter la saisie semi-automatique à votre moteur de recherche Excel

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

Dans ce billet, nous allons voir comment créer un menu déroulant pour une saisie semi-automatique, à insérer dans un moteur de recherche sur Excel. Bien que cela ne soit pas absolument nécessaire, il est vivement conseillé d’avoir au préalable suivi ce précédent article sur la création d’un moteur de recherche dans Excel pour bien suivre ce tutoriel.

 

Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier de cet article dans le lien disponible en fin d’article (cliquez ici pour y accéder).

 

 

1. Rappel du fonctionnement du moteur de recherche

 

En guise de piqure de rappel voici le fonctionnement du moteur de recherche :

  • Dans la base de données source, nous retrouvons une liste des pays qui sont répartis dans les continents et pour lesquels nous affichons la capitale,
  • Ensuite nous retrouvons une zone de recherche dans laquelle nous venons saisir un mot clé ou une partie de mots clés,
  • Et enfin les résultats de la recherche sont retournés dans un second tableau

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

Par exemple si nous effectuons une recherche sur la lettre « a », nous allons retrouver tous les termes qui contiennent la lettre « a », que ce terme soit présent dans le continent, le pays, ou encore dans la capitale.

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

2. Nouvelle colonne Index

 

Pour créer notre liste déroulante, nous allons utiliser la « Validation de données », en venant récupérer une liste de résultats.

Pour commencer, nous allons créer une colonne « Index » qui sera la même que celle que nous avions déjà créée dans le tableau source (voir dans le premier article). Dans cette colonne, nous allons donc retrouver l'ensemble des termes de chacune des lignes. Nous allons toutefois la modifier légèrement pour en améliorer la présentation. Dans cette colonne « Index », nous allons séparer chacun des termes par des tirets.

 

=[@Continent]&" - "&[@Pays]&" - "&[@Capitale]

 

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

Maintenant, nous allons pouvoir ajouter une « validation de données » dans la cellule de saisie des mots clés. Pour cela nous allons cliquer sur le bouton « Validation des données » présent dans le menu « Données » du Ruban.

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

Dans la fenêtre qui s’affiche à l’écran :

  • Dans la zone « Autoriser », nous choisissons l’option « Liste »,
  • Dans la zone « Source », nous allons sélectionner la liste source, qui correspond à la colonne « Index » que nous venons de créer (en cliquant sur l’en-tête de la colonne, lorsque le curseur prend la forme d’une flèche noire qui pointe vers le bas)
  • Nous pouvons maintenant valider en cliquant sur le bouton [OK].

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

Désormais, lorsque nous cliquons sur le bouton qui apparaît à droite de la cellule de recherche, nous retrouvons bien la liste des résultats. Il suffit ensuite de sélectionner un élément pour que le moteur de recherche puisse retrouver cet élément exact.

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

Pour terminer, nous allons devoir modifier légèrement la colonne « Index » du tableau source, en ajoutant ici aussi des tirets pour les deux colonnes « Index » correspondent exactement :

=[@Continent]&" - "&[@Pays]&" - "&[@Capitale]

 

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

Nous allons maintenant opérer quelques petits changements dans le fonctionnement du moteur de recherche pour le rendre plus performant encore.

 

3. Recherche de champs incomplets

 

En premier lieu, la validation de données que nous avons insérée permet de valider que les saisies insérées dans les cellules correspondent exactement à la liste que nous lui avons donnée. Or dans notre moteur de recherche, nous pouvons être amenés à saisir uniquement une partie de champ.

Par exemple le « ani » (pour Mauritanie, Tanzanie, Lituanie, Australie, ...)

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

Nous nous retrouvons dans ce cas-là avec un message d'erreur, car la donnée n'est pas validée exactement. Pour cela nous allons modifier la « Validation des données », en cliquant à nouveau sur le bouton « Validation des données » du ruban.

Dans l'onglet « Alerte d'erreurs » nous allons décocher le message d'erreur qui s'affiche. Puis nous validons en cliquant sur le bouton [Ok].

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

Désormais, si nous tapons « ani », la recherche s'effectue correctement.

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

 

4. Minuscules / Majuscules

 

Un autre souci vient de l'utilisation des majuscules dans les données sources, ou dans la cellule de recherche. En effet, imaginons que nous voulions taper le terme « mau » pour « Mauritanie » mais sans mettre la majuscule de la première lettre, alors le moteur de recherche ne retrouvera pas le terme.

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

Il considère que le « m » et le « M » (en majuscule) sont deux caractères différents. Pour cela nous allons venir modifier la formule insérée dans la colonne « Trouve » du tableau contenant les sources de données pour ne comparer que des données transformées en minuscules (nous pourrions également comparer des majuscules). Pour cela nous allons utiliser la formule Excel MINUSCULE() :

 

=TROUVE($J$1;[@Index])

     devient ainsi :

=TROUVE(MINUSCULE($J$1);MINUSCULE([@Index]))

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

Nous comparons ainsi notre chaîne saisie en mot clé en minuscule, la valeur de chaque ligne contenue dans la colonne « Index », en minuscule également.

À présent, le moteur de recherche trouve correctement l’entrée « Île Maurice », ainsi « Mauritanie », peu lui importe que la majuscule été saisis ou non (nous pourrions également saisir l'ensemble du terme en majuscules le résultat de recherches aura toujours le même).

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

 

5. Limiter les résultats au nombre d’occurrences trouvées

 

Ensuite si nous regardons les résultats retournés par le menu déroulant, nous retrouvons bien les deux termes adéquats. Par contre toutes les autres cellules sont également retournées (sous forme de tirets).

Pour éviter cela notre liste doit être dynamique, c’est à dire que sa longueur doit correspondre au nombre de résultats trouvés.

Pour cela nous allons avoir besoin d'insérer une formule dans le « Gestionnaire de noms », présent dans le menu « Formules » du ruban Excel.

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

La formule à insérer est la formule DECALER(). Celle-ci permet de récupérer une plage de données en spécifiant une cellule de départ, à partir de laquelle nous opérons un décalage vertical (en nombre de ligne), puis horizontal (en nombre de colonnes). Ensuite, nous allons pouvoir demander à ce que la plage retournée ait une certaine hauteur (en nombre de ligne, c’est justement ce qui va nous intéresser ici), et une certaine largeur (en nombre de colonnes).

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

Dans le « Gestionnaire de noms » (qui se trouve dans le menu « Formules » du ruban), nous allons créer un nouveau nom (en cliquant sur le bouton [Nouveau], que nous allons appeler « _liste ».

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

Dans le champs « Fait référence à », nous allons saisir non pas une plage de cellules comme habituellement, mais la formule suivante :

=DECALER($L$3;1;0;MAX(_baseDeDonnees[Ordre]))

 

  • $L$3 : c’est la cellule de départ, correspond à l’en-tête de la colonne « Index » du tableau de résultats
  • 1 : décalage d’une cellule vers le bas pour arriver au début de la plage contenant les données
  • 0 : nous restons sur la même colonne
  • MAX(_baseDeDonnees[Ordre]) : Il s’agit de la hauteur (en nombre de lignes) à retourner dans nos résultats. Pour obtenir ce nombre de résultats, nous allons utiliser la fonction MAX() qui permet de récupérer la valeur maximale d'une plage de données (ici c’est une colonne). Nous allons appliquer cette formule sur la colonne « Ordre » de la source de données (qui précise l'ordre d'affichage dans nos résultats, et donc le nombre maximal est égal au nombre d’enregistrements).

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

Une fois cette formule saisie dans le champ « Fait référence à », nous pouvons valider la création du nom en cliquant sur le bouton [Ok].

Ensuite, si nous revenons dans la « Validation des données » que nous avons appliqué sur notre champ de recherche, nous pouvons modifier le champ source en saisissant le nom que nous venons de créer (« _liste ») à la place des coordonnées de la colonne « Index ».

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

Désormais, seuls les résultats adéquats sont retournés par notre liste de saisie semi-automatique.

Excel formation - video comment ajouter la saisie semi automatique  votre moteur de recherche excel

Nous pouvons maintenant masquer les colonnes inutiles.

6. Télécharger le fichier d'exemple

 

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 



Articles qui pourraient vous intéresser

Comment identifier les cellules identiques en 1 clic sur Excel (couleur selon valeur) ?
Comment créer un tableau de bord qui s’actualise et identifie automatiquement les meilleures performances sur Excel !
L’erreur cachée qui plombe vos tableaux Excel (et comment l’éviter)
Comment entourer automatiquement cellules non conformes Excel sans MF conditionnelle ni formule ?
Comment créer un tableau de bord commercial intelligent avec Excel ?
Comment créer un graphique dessiné à la main avec Excel ?
Comment créer un graphique de distribution en 5 minutes chrono ?
L’astuce Excel pour imprimer le tableau que vous voulez en un clic !
Comment gérer ses comptes sur Excel avec le « Suivi du budget familial »
Comment créer facilement des QR codes sans VBA avec Excel ?
Transformez vos données en graphique tornade interactif facilement sur Excel !
Comment créer un graphique Iceberg dans Excel ?