[VIDEO] Comment créer un moteur de recherche ? (filtres élaborés)
Aujourd'hui, pour ce nouveau tutoriel, nous allons voir comment construire simplement un moteur de recherche sur Excel. C'est-à-dire qu’en partant d'une base de données quelconque, nous allons venir extraire les lignes qui correspondent à un critère que nous allons venir saisir dans une cellule, afin de les présenter dans un tableau de résultats.
Tout au long de ce tutoriel, nous allons utiliser une base de données constituée de trois colonnes, dont chacune d’entre elles reprend les éléments suivants :
- Dans une première colonne : Les cinq continents du monde,
- Dans une seconde colonne : Les différents pays membres de ces continents
- Et enfin dans la troisième et dernière colonne : Les capitales de chacun de ces pays
Notre fichier comprend également une cellule de couleur jaune dans laquelle nous allons venir saisir un mot clé qui servira à effectuer notre recherche.
À titre d'exemple nous allons commencer par saisir un mot clé (par exemple le mots-clés « Afrique », ce qui va vouloir signifier que nous allons souhaiter récupérer l'ensemble des pays du continent africain).
1. Transformation de la base en Tableau de données
Pour pouvoir travailler simplement sur cette base de données nous allons commencer par la transformer en tableau. Pour cela nous allons nous rendre dans l’onglet « Insérer » du ruban (en haut de la fenêtre d’Excel) et cliquer sur le bouton « Tableau ».
Dans la fenêtre qui s’affiche, nous sélectionnons l’option « Mon tableau comporte des en-têtes » (la première ligne des titres de notre tableau), puis nous validons en cliquant sur le bouton « OK ».
Notre tableau est maintenant transformé en tableau de données, ce qui va nous simplifier l’utilisation de ces données. Pour en savoir plus sur les tableaux de données d’Excel, je vous invite à consulter cet article qui présente tous les avantages liés à cette fonctionnalité avancée d’Excel.
2. Concaténation des cellules du tableau
Nous allons maintenant pouvoir ajouter une nouvelle colonne à notre tableau que nous allons appeler « Index ». Dans celle-ci nous allons concaténer les cellules de chaque ligne afin de pouvoir effectuer notre recherche non pas sur une seule colonne, mais bien sur l’ensemble constitué par les trois colonnes de notre tableau (Continent, pays, capitale).
Concaténer des cellules consiste à mettre les valeurs contenues dans chacune d’entre elle les unes à la suite des autres.
Pour concaténer des cellules, nous procédons de la manière suivante :
- Cliquons sur la première cellule de notre colonne « Index », c’est-à-dire la cellule $D$2,
- Saisissons le signe « = », pour informer Excel que nous allons insérer une formule de calcul,
- Cliquons sur la première cellule à concaténer (il s’agit de la première cellule de la colonne « Continent », soit $A$2). Notons au passage, qu’Excel a remplacé les coordonnées de la cellule (« $A$2 ») par sa représentation textuelle dans notre tableau de données (« [@Continent] »), ce qui est bien plus parlant.
- Saisissons une esperluette (symbole « & », également appelée « et commercial »),
- Puis nous pouvons cliquer sur la deuxième cellule (dans la colonne « Pays »),
- Saisissons une seconde esperluette,
- Et enfin cliquons sur la troisième cellule à concaténer (dans la colonne « Capitale »)
Ainsi en effectuant la recherche sur cette colonne « Index », nous pourrons à la fois effectuer la recherche
- Sur le Continent (par exemple sur le mot « Afrique »),
- Sur le Pays (par exemple sur le mot « Bénin »,
- Et sur le nom de la Capitale (par exemple sur le mot « Paris »)
Pour cela nous allons regarder si le mot-clé saisi dans la cellule Jaune (qui se trouve maintenant aux coordonnées « $H$1 ») fait parti de la nouvelle chaîne de caractères que nous retrouvons dans chacune des lignes de notre nouvelle colonne « Index ».
3. Recherche des lignes contenant le mot-clé
Pour déterminer si les cellules de la colonne « Index » comprennent le mot-clé saisi, nous allons créer une nouvelle colonne que nous allons appeler « Trouve ». Dans celle-ci, nous allons utiliser la fonction « =TROUVE() », qui permet d’identifier si un texte se trouve ou pas dans une chaîne de caractère :
- Si une chaîne se trouver effectivement dans une autre chaîne de caractère, la formule « =TROUVE() » retournera alors la position correspondante,
- Dans le cas contraire, la formule « =TROUVE() » retournera une erreur (« #VALEUR »)
Le premier paramètre de la formule « =TROUVE() » correspond à la chaîne de caractère que nous souhaitons retrouver. Il s’agit ici du texte saisi en $H$1 (« Afrique »). Nous allons ici utiliser la référence absolue de cette cellule (avec le symbole « $ » devant la lettre et devant le chiffre des coordonnées), afin que lorsque la cellule sera étendue vers le bas, le mot clé sera toujours dans notre cellule $H$1. Pour passer de la référence relative à la référence absolue, appuyons sur la touche « F4 » du clavier, après avoir cliqué sur la cellule.
Le second paramètre correspond au texte dans lequel nous souhaitons effectuer la recherche. Il s’agit donc de la cellule contenue dans la colonne « Index » que nous venons de créer. Nous pouvons alors valider notre formule.
Si nous regardons maintenant les résultats de notre formule contenue dans la colonne « Trouve », tous les pays du continent africain ont une valeur égale à « 1 », alors qu'il y a une erreur lorsque le mot clé n'est pas retrouvé.
Nous avons effectué ici un essai sur le continent, nous pouvons aussi effectuer un essai sur une lettre (par exemple la lettre « A ») ou sur une série de lettres (par exemple « al » : Alger est bien identifié dans le cadre de notre recherche, il n'y a pas d'erreur).
3. Numérotation des résultats trouvés
Nous allons maintenant vouloir connaître l'ordre dans lequel chaque résultat identifié arrive dans notre base lorsque la valeur a été trouvé.
Dans notre recherche de mot-clé « al » par exemple, nous allons vouloir que :
- La cellule qui correspond à l'Algérie prenne la valeur « 1 »,
- Et lorsqu'un nouveau résultat sera effectivement trouvé, celui prenne la valeur « 2 » (pour l’Albanie),
- Puis le « 3 » (pour l’Allemagne),
- etc …
Pour cela nous allons à nouveau créer une colonne que nous allons appeler « Ordre ».
Nous allons effectuer un test grâce à la fonction « =SI() » pour déterminer si la chaîne de notre mot-clé a été identifié dans notre colonne « Index » (qui ne doit alors pas renvoyer d’erreur). Cette fonction permet d’effectuer un test, puis de retourner un résultat différent en fonction de la valeur retournée par ce test. Pour en savoir plus sur la fonction SI(), vous pouvez consulter cet article.
=SI(ESTERREUR([@Trouve]);"";NB.SI($E$2:$E2;">0"))
Nous utilisons la formule SI() de la manière suivante :
- Premier paramètre : Nous souhaitons savoir si la colonne « Index » est une erreur, ce qui signifie alors que le mot clé n’a pas été trouvé. Pour déterminer si une cellule est une erreur, nous imbriquons une seconde formule, qui est la formule ESTERREUR() (qui retourne « VRAI » si la cellule spécifiée en paramètre est une erreur, et « FAUX » dans le cas contraire).
- Deuxième paramètre : Si la condition vue dans le premier paramètre est une erreur, alors nous souhaitons ne rien afficher. Nous retournons alors une chaîne vide, en saisissant des doubles guillemets ("").
- Troisième paramètre : si par contre ce n'est pas une erreur, nous allons vouloir connaître l'ordre de la ligne. Pour cela nous allons utiliser la formule NB.SI() qui permet de retourner le nombre de cellules qui répondent à une condition.
La plage de cellules sur laquelle nous allons effectuer le test commence en haut de notre table, dans la colonne « Trouve » (donc $E$2, en référence absolue), jusqu’à la ligne actuelle de cette même colonne. Voici quelques exemples pour mieux comprendre :
- En cellule F15, le test est effectué sur la plage $E$2:$E15,
- En cellule F8, le test est effectué sur la plage $E$2:$E8,
- En cellule F2, le test est effectué sur la plage $E$2:$E2,
- …
Pour saisir cette plage, procédons de la manière suivante :
- Cliquons sur la cellule F2 pour la sélectionner,
- Saisissons le symbole « = »,
- Cliquons sur la cellule juste à gauche (E2), puis appuyons une fois sur la touche F4 pour passer en référence absolue ($E$2),
- Appuyons pour sur la touche « : » pour saisir la seconde borne de notre plage,
- Cliquons à nouveau sur la cellule E2 (sans cliquer sur la touche F4, nous souhaitons conserver la référence relative, ou alors cliquons jusqu'à obtenir la référence semi-absolue qui les colonnes, mais garde les lignes liberées : $E2)
Maintenant que notre plage est correctement précisée, nous voulons compter le nombre de fois un résultat numérique est retourné, nous allons pour cela utiliser la condition (« ">0" »).
Nous pouvons valider notre formule après avoir fermé les deux parenthèses, et constater dans notre exemple (avec le mot-clé « al ») que l’Algérie est bien le premier résultat que nous retrouvons dans notre tableau, puis nous allons descendre jusqu'à l'Albanie (qui est bien le numéro deux) et enfin l’Allemagne qui est le numéro trois.
La colonne que nous venons d'ajouter ici (la colonne « Ordre ») est la colonne la plus importante, car c'est elle qui va nous permettre de récupérer nos résultats donc dans notre tableau de résultats.
4. Présentation des résultats
Pour afficher les résultats de notre recherche, nous allons créer un nouveau tableau avec les mêmes en-têtes de colonne, à savoir « Continent », « Pays » et « Capitale ».
Nous allons insérer un numéro d'identification, en saisissant les trois première valeurs « 1 », « 2 » et « 3 », que nous allons étendre (en faisant glisser le petit carré noir situé dans le coin inférieur-droit de la dernière cellule, qui apparaît lorsque nous sélectionnons les trois cellules). Nous pouvons descendre autant que nous voulons. Pour récupérer un résultat qui comprendrait toute la base des données nous pourrions prévoir un nombre de lignes égale dans nos deux tableaux. Mais nous pouvons également décider de limiter le nombre de résultats à un nombre précis.
Dans notre exemple, nous nous limitons à 80 résultats.
Pour récupérer les lignes de résultats, nous allons maintenant utiliser le mélange la fonction INDEX() (qui permet de récupérer dans une colonne (« Continent » par exemple) le n-ième élément) et de la fonction et EQUIV() (qui va retourner la position de la cellule qui contient une valeur dans une plage. Par exemple pour la deuxième ligne de notre tableau de présentation des résultats, lorsque l'identification est égale à « 2 », nous voulons savoir quelle est la ligne de la colonne « Ordre » dont le résultat est aussi égal à « 2 »).
Si nous avions voulu utiliser la fonction RECHERCHEV(), qui est la fonction la plus connue en termes de recherche de données, il aurait fallu que la colonne « Ordre » soit au début de notre tableau et que nos données soient classées par ordre ce qui n'est pas toujours pratique, et qui n'est pas le cas ici.
Attention, les plages de cellules de la formule INDEX() et la formule EQUIV(), doivent être de taille identique.
=INDEX(_baseDeDonnees[Continent];EQUIV($H5;_baseDeDonnees[Ordre];0))
- Tableau1[Continent] : il s’agit de la matrice qui contient les résultats que nous allons vouloir récupérer (ici la colonne Continent),
- $H4 : nous souhaitons savoir maintenant dans cette colonne à quelle ligne correspond le premier résultat (lorsque la colonne « Ordre » vaut « 1 ». Attention de bien utiliser des références semis-absolues : nous bloquons le glissement dans les colonnes, mais libérons le glissement dans les lignes.
- Tableau1[Ordre] : La valeur de $H4 doit être retrouvée dans la colonne « Ordre ».
- 0 : le type de résultat retourné par la fonction EQUIV() doit être 0
Nous pouvons maintenant étendre notre formule vers la droite en la modifiant légèrement. En effet, dans la seconde colonne, ce n'est plus le continent mais c'est le pays que nous cherchons, et dans la troisième colonne, il s’agit de la capitale.
Puis, nous pouvons étendre nos formules vers le bas et constater que nous avons bien nos trois résultats sur les trois premières lignes, mais qu’à partir de la quatrième ligne, nous avons une erreur car il n’existe pas de valeur supérieure à trois dans la colonne « Ordre ».
Pour éviter ce retour disgracieux, nous allons encapsuler notre formule INDEX(EQUIV()) dans une formule SIERREUR() qui permet de retourner un résultat lorsqu’une formule est en erreur.
=SIERREUR(INDEX(_baseDeDonnees[Continent];EQUIV($H5;_baseDeDonnees[Ordre];0)) ;"")
- INDEX(Tableau1[Continent];EQUIV($H5;Tableau1[Ordre];0)) : cette formule reste inchangée,
- "" : si la formule INDEX(EQUIV(…)) retourne une erreur, alors nous affichons une cellule vide, grâce aux doubles guillemets
Nous validons chaque colonne en maintenant la touche Ctrl du clavier enfoncée pour valider l'ensemble des cellules de la colonne, et ainsi les erreurs disparaissent.
Notre moteur de recherche est maintenant terminé, et il suffit de modifier le mot clé de recherche pour modifier les résultats affichés, puis de valider en appuyant sur la touche entrée du clavier.
Si nous souhaitons connaître la capitale d'un pays il suffit saisir son nom.
Les colonnes « Index », « Trouve » et « Ordre » sont désormais inutiles nous pouvons les masquer.
5. 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 :