Comment effectuer une recherche en temps réel sur une base Excel, sans VBA ?

Dans ce tutoriel, je vais vous montrer comment effectuer une recherche en temps réel sur une base de données Excel sans utiliser de développement en VBA. Cette technique nous permettra ainsi de trouver rapidement les informations dont nous avez besoin, que votre feuille de calcul contienne quelques centaines ou des milliers de lignes.

En effet, comme nous allons le constater, la recherche en temps réel est un moyen efficace de filtrer les données d'une feuille Excel pour trouver rapidement ce que nous recherchons, qu’il s’agisse d’une liste de contacts, d’un inventaire, ou toute autre forme de données, cette méthode nous permettra de gagner du temps et de simplifier notre travail.

Ici, pour cet exemple, la recherche va nous permettre d’obtenir rapidement des informations sur les membres du personnel d’une entreprise.

Ces informations sont en effet présentées dans une grande base de données, et nous allons souhaiter extraire les informations en fonction de critères de recherche.

Téléchargement

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

Tutoriel Vidéo

Partie 1 :

Partie 2 :

1. Préparation de la feuille de recherche

Pour effectuer la recherche, nous allons souhaiter insérer une nouvelle feuille de calcul que nous allons nommer « Recherche ».

Sur cette feuille, nous allons nous placer sur la cellule A2 dans laquelle nous allons insérer un émoji loupe.

Pour cela, nous sélectionnons la cellule, puis nous affichons la fenêtre de sélection des émojis en appuyant sur les touches [Win]+[ ;].

Ensuite, nous saisissons le terme « loupe » et nous cliquons sur l’émoji souhaité :

De cette manière, nous viendrons saisir le terme à rechercher dans la cellule B2.

Pour simplifier la recherche, nous allons sélectionner cette cellule, puis saisir le nom « critere » Directement dans la zone des noms, situéée juste à gauche de la barre de formule :

Nous saisissons par exemple « Jean » dans cette cellule, ce qui nous permettra d’effectuer la première recherche.

Ensuite, pour afficher les résultats de la recherche, nous allons simplement reprendre la ligne 8 de la feuille principale, en la sésectionnant en cliquant sur le numéro de la ligne correspondante, puis en appuyant sur les touches [Ctrl]+[c] :

Nous revenons sur la feuille « Rechercher, nous sésectionnons la ligne 4 et nous appuyons sur les touches [Ctrl]+[v] :

2. Sélection des lignes à afficher

Maintenant, pour sélectionner rapidement les lignes qui correspondent à l’élément recherché, nous allons revenir sur la feuille principale, afin de transformer la base en un tableau structuré.

Comme nous avons déjà pu le découvrir lors de tutoriels précédents, il s’agit d’une fonctionnalité très puissante d’Excel qui permet de gérer plus efficacement les données en apportant de nombreux avantages sur une base de données Excel.

En effet, les tableaux structurés simplifient grandement la gestion des données dans Excel, en associant automatiquement un nom à chaque colonne, ce qui facilite l'identification des données et la création de formules. Ces derniers sont également dynamiques, ce qui signifie que lorsque nous ajoutons de nouvelles données, le tableau s'ajuste automatiquement pour les inclure. Il sera ainsi inutile de mettre à jour manuellement les plages de cellules.

Lorsque nous travaillons avec des tableaux structurés, nous pouvons utiliser la fonction de filtre de manière plus intuitive, en quelques clics, ce qui est particulièrement utile pour la recherche en temps réel que nous mettons en place dans ce tutoriel.

Les formules dans un tableau structuré sont également plus faciles à créer et à comprendre, en utilisant directement les noms de colonnes pour référencer les données, ce qui rend les formules plus lisibles.

Pour effectuer cette transformation, il suffit de sélectionner l’une des cellules de la base, puis d’utiliser le raccourci clavier [Ctrl]+[l] :

Excel nous affiche alors une boîte de dialogue, qui permet de valider la plage des cellules sélectionnée automatiquement.

Si celle-ci n’est pas correcte, nous pouvons éventuellement la corriger.

Dans le cas contraire, il ne reste plus qu’à vérifier que l’option « Mon tableau comporte des en-têtes » est bien cochée, puis de valider la transformation en appuyant sur le bouton [OK].

Maintenant, pour identifier rapidement les cellules qui correspondent au critère de recherche, nous insérons une nouvelle colonne dans le tableau, en saisissant simplement le nom de cette colonne (par exemple « Recherche ») dans la cellule G8 :

Comme vous pouvez le constater, l’une des fonctionnalités très puissante des tableaux structurés, c’est que la plage des cellules est dynamique, elle va donc s’adapter automatiquement pour tenir compte de cette nouvelle colonne.

Ensuite, nous sélectionnons la cellule G9, afin d’y insérer la formule suivante :

=CONCAT(Tableau1[@[Nom complet]:[Adresse]])

Cette formule permet de concaténer les données de chaque ligne du tableau en une seule colonne, c’est-à-dire ajouter les valeurs de chaque cellule les unes à la suite des autres, afin de regrouper toutes les informations des employés en une seule cellule. Nous utilisons cette approche pour simplifier notre recherche en temps réel. Au lieu de rechercher chaque élément (nom, poste, département, etc.) individuellement, nous pouvons chercher dans cette colonne unique de texte pour trouver des correspondances avec notre critère de recherche. Ainsi, la recherche en sera plus flexible et plus efficace, car elle regroupe toutes les données pertinentes dans une seule colonne.

Maintenant que toutes les cellules de chaque ligne sont regroupées dans une seule colonne, nous allons pouvoir utiliser cette colonne pour déterminer sur quelles lignes se trouve éventuellement le critère de recherche.

Pour ce faire, nous allons utiliser la fonction CHERCHE(), qui nous permettra d'identifier si le critère de recherche est présent dans la colonne de texte que nous venons de créer.

=CHERCHE(critere;CONCAT(Tableau1[@[Nom  complet]:[Adresse]]))

Maintenant, quand le critère est identifié dans la ligne, Excel nous retourne une valeur numérique, et dans le cas contraire une erreur #VALEUR! :

Maintenant, pour rendre ce résultat plus facile à interpréter, nous souhaitons le convertir en une valeur booléenne, c'est-à-dire une valeur qui indique simplement si la recherche a abouti (Vrai) ou non (Faux).

Pour effectuer cette conversion, nous utilisons la fonction ESTNUM(). Cette fonction vérifie si la valeur qu'elle examine est un nombre ou non. Si la valeur est un nombre (c'est-à-dire que le critère de recherche a été trouvé), ESTNUM() renvoie Vrai. Si la valeur n'est pas un nombre (c'est-à-dire que le critère de recherche n'a pas été trouvé), ESTNUM() renvoie Faux.

Ainsi, en encapsulant notre résultat de recherche dans ESTNUM(), nous obtenons une réponse binaire simple : Vrai si le critère de recherche est présent dans la ligne, et Faux s'il ne l'est pas.

Pour finir ce travail préparatoire, nous allons nommer les cellules du tableau en deux parties :

• Les cellules « base » : qui correspondent aux cellules reprenant les informations sur le personnel de l’entreprise
• Les cellules « recherche » qui correspondent à cette dernière colonne, c’est-à-dire la colonne dans laquelle nous avons déterminé si la ligne possède le critère de recherche ou non.

Nous commençons donc par positionner le curseur de la souris au niveau de la première cellule de l’en-tête du tableau, pour que celui-ci prenne la forme d’une flèche qui pointe vers le bas !

Puis, sans rel&cirque;cher le curseur de la souris, nous faisons glisser celle-ci jusqu’à l’en-tête « Adresse » :

Ensuite, nous saisissons le nom « base » dans la zone des noms :

Nous répétons l’opération pour nommer la dernière colonne en « recherche » :

Ici, étant donné que ces cellules nommées repose sur un tableau structuré, celles-ci sont automatiquement dynamiques, et si nous ajoutons de nouvelles lignes à la suite de la base, celles-ci seront ajoutées automatiquement.

3. Récupérer les lignes de résultat

Pour récupérer les lignes correspondantes au critère de recherche, il ne reste plus qu’à revenir sur la seconde feuille de calcul, afin de saisir cette simple formule :

=FILTRE(base;recherche)

Cette formule est le cœur de notre recherche en temps réel.

Elle utilise la fonction FILTRE() pour extraire les lignes de la base de données qui correspondent au critère de recherche que nous avons saisi dans la cellule B2.

Voici comment cette formule fonctionne qui a été introduite sur les versions récentes d’Excel :

FILTRE() prend deux arguments :

• la première est la plage de données à filtrer (dans notre cas, la plage nommée « base »),
• et la seconde est le critère de filtrage (dans notre cas, la plage nommée « recherche »).

Ainsi, la fonction FILTRE() parcourt la colonne « recherche » et examine chaque valeur booléenne (Vrai ou Faux) résultant de notre recherche :

• Si la valeur est Vrai, cela signifie que le critère de recherche a été trouvé dans la ligne correspondante de la base de données, et cette ligne est incluse dans les résultats filtrés.
• Si la valeur est Faux, cela signifie que le critère de recherche n'a pas été trouvé dans la ligne correspondante, et cette ligne est exclue des résultats filtrés.

4. Rendre la recherche dynamique

Ici, la recherche est pleinement fonctionnelle, mais contrairement à ce que peut laisser penser le titre de ce tutoriel, elle ne s’effectue pas en temps réel, mais uniquement lors de la validation de la cellule « critere » :

Pour la rendre pleinement dynamique, nous allons devoir insérer un objet au-dessus de la cellule B2.

Et pour cela, si ce n’est pas déjà fait, nous allons tout d’abord devoir ajouter un nouveau menu dans le Ruban d’Excel, il s’agit du menu « Développeur ».

Je ne vais pas revenir sur ce menu, vous trouverez toutes les informations le concernant en suivant ce lien.

Si vous n'avez pas encore activé le menu « Développeur », voici comment le faire :

• Effectuez un clic-droit sur le menu Ruban,
• Dans le menu qui apparaît, sélectionnez « Personnaliser le ruban ».
• Excel affiche alors une boîte de dialogue sur laquelle il ne reste plus qu’à Cocher la case « Développeur » dans la colonne de droite, puis cliquer sur le bouton « OK » pour fermer la fenêtre des options.

Une fois celui-ci présent dans le ruban, nous allons pouvoir insérer une zone de texte dynamique en déroulant le menu « Insérer » du groupe « Contrôles » et en choisissant « Zone de texte » depuis la liste des contrôles ActiveX :

Ensuite, nous allons dessiner celui-ci directement sur la feuille de calcul, à côté de la cellule B2 :

Pour lier cette zone de texte avec la cellule, nous cliquons sur celui-ci, puis sur le bouton « Propriétés » du menu « Développeur » :

Excel affiche alors une boîte de dialogue avec les propriétés correspondantes, et nous allons rechercher la propriété « LinkedCell » dans laquelle nous allons saisir le nom de la cellule « critere », puis valider en appuyant sur la touche [Entrée] du clavier :

Pour finir, nous allons placer ce bouton au-dessus de la cellule B2 en utilisant les poignées de redimensionnement :

Pour simplifier l’opération, nous pouvons appuyer sur la touche [Alt] du clavier.

Enfin, nous pouvons rendre la zone de texte fonctionnelle en appuyant sur le bouton « Mode Création » du menu Ruban.

Nous pouvons maintenant saisir des éléments de recherche pour qu’elle celle-ci s’effectue en temps réel !

4. Solution alternative pour toutes les versions d'Excel

   4.1. Modification de la base de données

Continuons ce tutoriel, afin de découvrir une solution alternative capable de fonctionner sur les versions les plus anciennes d’Excel.

Pour rappel, la recherche en temps réel reposait sur une base de données des informations du personnel d’une entreprise :

Dans cette base de données, nous avions ajouté une colonne permettant de définir chaque ligne faisant figurer les informations saisies dans une cellule de recherche :

 =ESTNUM(CHERCHE(critere;CONCAT(Tableau1[@[Nom  complet]:[Adresse]]))) 

Pour cela, nous utilisons la fonction CONCAT() elle aussi réservé aux dernières versions d’Excel.

Pour commencer, nous allons donc la modifier légèrement pour utiliser de simples esperluettes à la place de cette fonction.

La fonction présentée au-dessus devient donc :

 =ESTNUM(CHERCHE(critere;[@[Nom  complet]]&[@Poste]&[@Département]&[@[Date  d''embauche]]&[@[Salaire mensuel (en EUR)]]&[@Adresse])) 

Le résultat de celle-ci est exactement le même.

Ensuite, nous allons ajouter une colonne supplémentaire, afin de donner un numéro d’identification à chaque fois qu’une ligne correspond effectivement au critère saisi dans la cellule correspondante.

Comme je vous l’expliquais dans la vidéo précédente, pour ajouter rapidement une colonne dans un tableau structuré, il suffit de saisir le nom de cette nouvelle colonne juste à droite du tableau.

Ici, nous pouvons par exemple l’appeler « ID » :

Puis, nous sélectionnons l’une des cellules situées sur cette colonne, de préférence une cellule qui n’est pas directement la première (par exemple dans la cellule H13), afin d’y insérer la formule suivante :

 =NB.SI(G$9:G13;VRAI) 

Cette formule utilise la fonction NB.SI()qui permet de compter le nombre d'éléments dans une plage qui répondent à un certain critère.

Ses arguments sont les suivants :

• plage : est la plage de cellules dans laquelle vous souhaitez effectuer le comptage.
• critère : est la condition que les cellules de la plage doivent respecter pour être comptées.

Ici, nous souhaitons donc dénombrer le nombre de cellules ayant la valeur « VRAI » dans la colonne « Rechercher » pour les lignes situées à partir la première cellule de la colonne (aux coordonnées G9), jusqu’à la ligne dans laquelle nous insérons la formule.

Pour que l’analyse commence sur la première ligne, nous utilisons une référence mixte sur la référence de la cellule G9, afin de verrouiller le numéro de la ligne.

Puis, pour qu’Excel n’affiche des résultats que lorsque le résultat de la recherche est égal à VRAI, nous encapsulons ce résultat en tant qu’argument de la fonction SI() :

  =SI([@Recherche]=VRAI;NB.SI(G$9:G9;VRAI);"") 

De cette manière, si la valeur de la colonne Recherche n’est pas égale à VRAI, alors la formule retourne une cellule vide :

   4.2. Récupération des résultats

Maintenant pour récupérer ces résultats dans le tableau de recherche, nous allons commencer par sélectionner la première cellule de celui-ci (la cellule A5), afin de supprimer la formule insérée en appuyant sur la touche [Suppr.] du clavier.

Ensuite, nous allons effectuer une recherche sur la base qui va permettre de récupérer les informations correspondant aux lignes identifiées par la colonne « ID ».

Pour cela, plusieurs options s’offrent à nous.

Ici nous allons préférer effectuer une recherche avec les fonctions INDEX-EQUIV, qui fonctionnent sur toutes les versions d’Excel, ce qui est le but de ce nouveau tutoriel.

Nous allons également utiliser une petite astuce pour déterminer automatiquement le numéro d’identification à retrouver, sans qu’il ne soit nécessaire d’ajouter une nouvelle colonne dans la base.

Pour cela, nous allons utiliser la fonction Excel LIGNE() que nous avions découverte dans un tutoriel précédent, qui permet de retourner le numéro de la ligne d’une cellule que nous passons en argument.

Et si nous ne spécifions aucune cellule, alors le numéro de ligne retourné va correspondre au numéro de la ligne dans laquelle la formule est insérée.

Ainsi, si nous utilisons cette formule dans la cellule A5, la fonction LIGNE() nous retourne « 5 » :

Pour obtenir le premier résultat de recherche, il suffit donc de retirer « 4 » à ce résultat (c’est le numéro de la ligne de l’en-tête) :

 =LIGNE()-4 

Ensuite, nous allons souhaité obtenir le résultat de la colonne « Nom complet » de la base qui correspond à l’ID numéro 1 :

 =INDEX(Tableau1[Nom  complet];EQUIV(LIGNE()-4;Tableau1[ID];0)) 

N’oubliez pas d’utiliser l’argument facultatif « 0 » pour effectuer une recherche exacte, et retourner une erreur lorsqu’aucun résultat ne correspond à la ligne, nous aurons besoin de cette erreur juste après.

Maintenant que nous sommes capables d’identifier le nom du premier résultat de recherche, nous pouvons adapter celui-ci pour obtenir les autres informations le concernant.

Pour cela, nous allons modifier la formule pour effectuer une recherche en 3D dans notre base de données.

Il suffit d’effectuer les modifications suivantes :

• Pour effectuer la recherche sur tout le tableau, nous supprimons la référence à la colonne « Nom complet » pour ne garder que le nom du tableau (« Tableau1 ») en tant que premier argument de la fonction INDEX
• Pour sélectionner directement la colonne dans laquelle se trouve l’information, nous pouvons utiliser directement la fonction COLONNE() qui est l’équivalent de la fonction LIGNE() que nous avons découverte un peu plus tôt

La formule devient donc :

  =INDEX(Tableau1;EQUIV(LIGNE()-4;Tableau1[ID];0);COLONNE()) 

Et nous pouvons maintenant saisir la formule sur toutes les cellules de la table de recherche, en sélectionnant les cellules correspondantes, puis en validant la formule en appuyant sur les touches [Ctrl]+[Entrée] :

Ici, nous ne pouvons pas utiliser les poignées de recopie, car celles-ci adapterait la formule, et l’analyse ne se ferait plus sur la colonne « ID ».

Pour finir, comme je vous l’expliquais un peu plus tôt, la formule utilisée va retourner des erreurs lorsqu’il n’y a pas de résultat à afficher.

Pour éviter cela, nous allons tout simplement encapsuler la formule en tant qu’argument de la fonction SIERREUR() afin d’insérer un résultat vide ("") lorsqu’aucun résultat ne peut être retourné :

  =SIERREUR(INDEX(Tableau1;EQUIV(LIGNE()-4;Tableau1[ID];0);COLONNE());"")  

Comme nous l’avons vu juste avant pour saisir cette formule, sélectionnez au préalable toutes les cellules du tableau et validez en appuyant sur les touches [Ctrl]+[Entrée].

Et voilà, la recherche en temps réel est maintenant fonctionnelle, quelque soit votre version d’Excel :

   4.3. Bonus : Recherche sur la date

Maintenant, voyons un petit bonus.

Cette version que nous venons de mettre en place nous permet en effet plus de liberté dans les recherches.

En effet, si vous essayez d’effectuer une recherche sur l’année d’embauche, vous pourrez constater qu’Excel n’est pas en mesure de retourner le moindre résultat :

Pour comprendre la raison de ce dysfonctionnement, revenons sur la formule de recherche de résultat :

 =ESTNUM(CHERCHE(critere;[@[Nom  complet]]&[@Poste]&[@Département]&[@[Date  d''embauche]]&[@[Salaire mensuel (en EUR)]]&[@Adresse])) 

Ici, et dans la première version de ce tutoriel, nous récupérons la valeur de chacune des cellules de la ligne pour effectuer la recherche.

Or, dans Excel, une date est considérée comme une valeur numérique, sur laquelle il applique le format de date que nous lui connaissons.

Pour nous rendre compte, plaçons-nous dans la barre des formules afin de sélectionner la portion « [@[Date d''embauche]] », puis nous appuyons sur la touche [F9] du clavier pour qu’Excel affiche à la place le résultat correspondant :

Le numéro de série 40252 correspond donc au 15/03/2010.

Et c’est bien ce numéro de série qu’Excel va utiliser ensuite pour effectuer la recherche.

Pour corriger ce comportement, il suffit donc d’encapsuler la cellule en tant qu’argument de la fonction TEXTE() qui permet d’appliquer un format particulier à une cellule :

La formule devient donc :

 =ESTNUM(CHERCHE(critere;[@[Nom  complet]]&[@Poste]&[@Département]&TEXTE([@[Date  d''embauche]];"jj/mm/aaaa")&[@[Salaire mensuel (en  EUR)]]&[@Adresse])) 

Et maintenant, si nous revenons sur notre recherche, celle-ci aura bien quelques résultats à nous proposer :