Comment créer un moteur de recherche… avec deux formules et sans VBA sur Excel

Dans ce tutoriel, nous allons créer un moteur de recherche pour retrouver facilement des données au sein d’une base. Pour cela nous n’allons pas avoir à développer une seule ligne de macro VBA, mais à la place nous allons utiliser deux formules Excel !  Lisez bien cet article jusqu’à la fin, car je vous réserve une surprise : nous y verrons en effet comment effectuer des recherches approximatives, c’est-à-dire qui vont permettre de retrouver un élément dont nous ne connaissons qu’une partie seulement du texte. 

Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier en suivant le lien disponible ci-dessous :

Téléchargement

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

1. Présentation du cas

Dans un précédent tutoriel, nous avions vu dans le détail comment construire un moteur de recherche au sein d’une feuille de calcul Excel.

Ici, nous allons voir une nouvelle manière de procéder, encore plus simple à mettre en place, même si les formules que nous allons utiliser vont être assez complexes. Mais pas de panique, nous allons détailler chacune d’entre elles !

Nous allons repartir du même fichier départ, c’est-à-dire que nous allons avoir une liste de tous les pays, classés par continent, et pour chacun d’entre eux le nom de sa capitale. Vous pouvez télécharger le fichier de travail dans le lien disponible dans la description de la vidéo, pour réaliser les manipulations en même temps que moi.

2. La formule LIEN_HYPERTEXTE()

Pour construire le moteur de recherche, nous allons utiliser un ensemble de plusieurs formules, la première d’entre elles étant la formule LIEN_HYPERTEXTE(). Il s’agit d’une formule très particulière, qui contrairement aux autres formules d’Excel ne va pas permettre d’effectuer un calcul, mais va avoir pour effet de créer un lien vers un autre endroit. Ce dernier peut ainsi pointer vers un fichier, un site internet, une adresse mail, etc … mais également vers une cellule de la feuille de calcul ! Et c’est justement ce qui va nous intéresser.

Pour que cette formule puisse permettre d’atteindre effectivement une cellule de la feuille de calculs active, il faut saisir l’adresse de cette cellule, en insérant juste avant un symbole dièse (« # »), le tout entre guillemets.

Par exemple, pour atteindre la cellule « B13 » de la feuille de calcul courante, nous allons utiliser la formule :

 =LIEN_HYPERTEXTE("#B13") 

Dans notre exemple, cette formule nous permet d’accéder directement à la cellule contenant le nom du pays « Burkina Faso ».

Le second paramètre de la formule est facultatif, et permet d’afficher un texte spécifique à la place de la cible du lien hypertexte :

 =LIEN_HYPERTEXTE("#B13";UNICAR(128269)&" Rechercher")

Vous noterez au passage l’utilisation de la formule UNICAR(), qui permet d’afficher un caractère UNICODE, dont le numéro est spécifié en paramètre (ici, nous affichons le symbole loupe). Vous trouverez un tutoriel complet sur cette formule en suivant ce lien.

3. Retrouver les coordonnées de la cellule recherchée

Maintenant que nous savons comment nous allons procéder pour atteindre la cellule contenant le texte recherché, il ne nous reste plus… qu’à identifier les coordonnées de cette dernière, et c’est là que les choses sérieuses vont pouvoir commencer !

Pour l’exemple, nous souhaitons retrouver la cellule correspond à Paris :

Pour cela, nous allons ajouter une ligne au-dessus de l’en-tête du tableau pour rechercher dans chacune des colonnes si l’une des cellules contient le texte recherché :

Pour identifier dans quelle ligne se trouve éventuellement la cellule ayant pour valeur le texte recherché, nous allons utiliser la formule imbriquée INDEX(EQUIV()), cliquez ici pour tout savoir sur celle-ci.

 =EQUIV($B$5;A8:A205;0)

Dans cette formule, la référence absolue à la cellule $B$5 permet de récupérer le nom recherché (« Paris »), recherche que nous souhaitons réaliser sur les cellules du tableau situées juste en dessous (A8:A205). Nous utilisons ici une référence relative pour pouvoir décaler par la suite les formules sur les autres colonnes du tableau. Et enfin le dernier paramètre à zéro permet de ne récupérer un résultat que lorsqu’une cellule STRICTEMENT identique est identifiée :

Si nous validons la formule, celle-ci va nous retourner une erreur #N/A, car aucun Continent n’a pour nom Paris :

Nous pouvons étendre cette formule au-dessus des deux autres colonnes et nous rendre compte que Paris est bien présent dans la colonne « Capitale » :

Attention, le résultat retourné (152) correspond au nombre de lignes séparant le haut du tableau (le titre de la colonne) de la cellule contenant le résultat recherché : en d’autres termes, Paris est la 152^ème cellule de la colonne « Capitale » (titre inclus).

Pour obtenir le numéro de la ligne correspondante dans la feuille de calculs, nous allons ajouter le nombre de lignes qui se trouvent au-dessus du tableau, c’est-à-dire sept lignes :

=EQUIV($B$5;A8:A205;0)+7

Maintenant que nous connaissons le numéro de la ligne dans laquelle se trouve le résultat recherché, nous allons pouvoir l’imbriquer au sein de la formule ADRESSE(), laquelle permet de récupérer l’adresse d’une cellule en fonction de la ligne et de la colonne de celle-ci, sachant que la colonne s’obtient à l’aide de la formule COLONNE() :

 =ADRESSE(EQUIV($B$5;A8:A205;)+7;COLONNE())

Et voilà, c’est tout pour la première cellule !

4. Récupérer l’adresse de la première cellule identifiée

Enfin, lorsque nous disposons des coordonnées de la cellule correspondante, il ne nous reste plus qu’à l’imbriquer dans la formule LIEN_HYPERTEXTE().

Pour cela, nous allons utiliser la formule INDEX-EQUIV-INDEX() que nous avons découverte dans un cours précèdent, mais que nous allons légèrement modifier pour récupérer la première valeur qui n’est pas une erreur :

 =LIEN_HYPERTEXTE("#"&INDEX(A7:C7;EQUIV(FAUX;INDEX(ESTERREUR(A7:C7););));UNICAR(128269)&"
 Rechercher") 

Ici, la formule INDEX(ESTERREUR(A7:C7);) va retourner une matrice composée de valeurs VRAI lorsque la cellule analysée est une erreur et FAUX dans le cas contraire :

La formule EQUIV(FAUX ;INDEX(…)) permet de récupérer la position du premier élément FAUX de cette matrice, dont nous récupérons la valeur grâce à la formule INDEX() !

La cellule contenant la valeur Paris est donc située aux coordonnées C159.

Attention de ne pas omettre le symbole dièse en début de formule, que nous concaténons avec la formule INDEX-EQUIV-INDEX().

Il suffit à présent de cliquer sur le « bouton » Rechercher pour accéder directement à cette cellule :

5. Bonus : effectuer une recherche approximative

Si vous avez effectué quelques tests sur le moteur de recherche, peut-être avez-vous remarqué son incapacité à effectuer une recherche lorsque le terme saisi est incomplet :

En effet, cela aura systématiquement pour effet de retourner une valeur #N/A.

Heureusement une solution simple existe : il suffit d’utiliser le caractère joker étoile (« * ») pour remplacer n’importe quelle chaîne de caractères.

Soit en fin de chaîne :

Soit en début de chaîne :

Soit en début et en fin de chaîne :