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

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 :

 



Articles qui pourraient vous intéresser

Créer un formulaire avec des champs dynamiques et auto-générés sur Excel

Comment créer un indicateur graphique-silhouette (Homme/Femme) dynamique sur Excel

[VIDEO]Comment ajuster automatique la taille cellule en fonction de son contenu

Comment créer un indicateur de performance en forme de barre de progression dynamique sur Excel

Commentaires

Pascal commented

formation intéressante

Excelformation.fr commented

Merci Pascal :)

Benoit commented

Merci là encore pour la formation. Un autre point puisque je viens de travailler dessus, si tu as un champ vide dans ton tableau _baseDeDonnees tu te retrouves avec des 0 dans le Tableau2 ce qui perturbe l'ensemble. Si tu choisis le mot Afrique et que tu valides tu as bien la bonne liste avec les infos Si tu choisie le mot Afrique et que tu séléctionnes une ligne, qui comporte un 0 tu n'as rien qui s'affiche alors... Et je n'ai pas encore trouvé de solution, si tu en as une je suis preneur. Mais merci pour ce beau travail.

Excelformation.fr commented

Bonjour, et merci pour votre message ! La solution la plus simple et la plus rapide à mettre en place pour masquer les zéros dans Tableau2 consiste à modifier le format des cellules. Pour cela, sélectionner toutes les cellules de Tableau2 (sélectionner une des cellules, puis Ctrl+A) > Clic Droit > Format de cellule > dans la liste des catégories, choisir Personnalisée > et saisir : ";;;Standard". De cette manière les chiffres seront masqués ("0;-0;;Standard" pour afficher les chiffres différents de zéro). Ensuite pour le second problème, modifier la formule de la colonne Index du tableau _baseDeDonnees (en colonne "D") : "=[@Continent]&SI([@Pays]="";"";" - "&[@Pays])&SI([@Capitale]="";"";" - "&[@Capitale])" et du tableau Tableau2 (en colonne "L") : "=[@Continent]&SI([@Pays]=0;"";" - "&[@Pays])&SI([@Capitale]=0;"";" - "&[@Capitale])". Cela répond à votre question ? À bientôt

Poster un commentaire

Pseudo
Commentaire

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.