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.

Excel formation - moteur de recherche 2 fonctions - 01

 

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") 

Excel formation - moteur de recherche 2 fonctions - 02

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")

Excel formation - moteur de recherche 2 fonctions - 03

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 :

Excel formation - moteur de recherche 2 fonctions - 04

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é :

Excel formation - moteur de recherche 2 fonctions - 05

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.

Excel formation - moteur de recherche 2 fonctions - 06

 

 =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 :

Excel formation - moteur de recherche 2 fonctions - 07

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

Excel formation - moteur de recherche 2 fonctions - 08

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 » :

Excel formation - moteur de recherche 2 fonctions - 09

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).

Excel formation - moteur de recherche 2 fonctions - 10

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 :

Excel formation - moteur de recherche 2 fonctions - 11

 

=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() :

Excel formation - moteur de recherche 2 fonctions - 12

 

 =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 :

Excel formation - moteur de recherche 2 fonctions - 13

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() !

Excel formation - moteur de recherche 2 fonctions - 14

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 :

 

Excel formation - moteur de recherche 2 fonctions - 15

 

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 :

Excel formation - moteur de recherche 2 fonctions - 16

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 :

Excel formation - moteur de recherche 2 fonctions - 17

Soit en début de chaîne :

Excel formation - moteur de recherche 2 fonctions - 18

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

Excel formation - moteur de recherche 2 fonctions - 19

 



Articles qui pourraient vous intéresser

Comment créer un sommaire automatique et généré en 1 clic sur Excel

Comment empêcher la suppression ou la modification des cellules sur Excel

Comment créer un formulaire de saisie intégré dans une feuille de calcul sur Excel

Calculer la racine n-ième d’un nombre sur Excel

Commentaires

Il n'y a aucun commentaire...

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.