Découvrez comment créer un menu de recherche multicritères et intelligent (comme Google) sur Excel (sans VBA)

Dans ce tutoriel, je vais vous montrer comment il est possible de créer un champ de recherche intelligent dans une cellule.

Dans celui-ci, nous allons pouvoir sélectionner directement des données ou alors utilisez la zone de recherche pour filtrer les données présentées, un peu à la manière de Google.

Téléchargement

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

Tutoriel Vidéo

1. Comment insérer rapidement une liste déroulante de sélection des données évoluée ?

Comme nous venons de le voir dans l'introduction de ce tutoriel, je vais maintenant vous présenter une méthode simple et rapide qui permet d'insérer des listes de sélection de données avec des fonctionnalités de recherche avancées directement dans des cellules Excel.

Pour cela, nous n'allons pas utiliser les méthodes classiques que nous avons déjà découvertes lors de vidéos précédentes, lesquelles consistent à créer une règle de validation des données. Mais ici, nous allons aller encore plus vite en exploitant les fonctionnalités de recherche que nous avons au sein des tableaux croisés dynamiques.

Pour cela, nous allons commencer par créer un tableau croisé dynamique. Pour cela, nous sélectionnons l'une des cellules de notre base, puis nous nous rendons dans le menu « Insertion », afin de cliquer sur « Tableau croisé dynamique » :

Excel nous affiche alors une boîte de dialogue qui va nous demander si nous souhaitons créer le tableau croisé dynamique sur la feuille de calcul en cours de travail ou alors sur une autre feuille de calcul. Ici, pour simplifier l'analyse des données, nous choisissons acérées ce nouveau tableau croisé dynamique directement sur la feuille de calcul active, au niveau de la cellule F6 :

Une fois le TCD ajouté sur la feuille, nous allons tout simplement pouvoir utiliser le champ de « Filtre » de celui-ci pour y intégrer le ou les champs que nous allons vouloir utiliser pour effectuer une recherche.

Pour cela, nous allons faire glisser le champ que nous voulons y intégrer directement à l'intérieur de celle-ci. Tout d'abord, nous allons vouloir effectuer des recherches en fonction du nom du commercial, puis en fonction du nom du produit vendu :

2. Personnaliser l'apparence des listes déroulantes

Comme vous pouvez le voir ici, nous voyons bien qu'il s'agit de champs de tableau croisé dynamique en raison de l'apparence donnée par Excel à ces cellules :

Mais pas de panique, nous allons personnaliser l'apparence de ces cellules en les sélectionnant puis en nous rendant dans le menu « Création » du menu ruban afin de choisir l'un des styles proposés :

Ensuite, nous pourrons utiliser les fonctionnalités de recherche de ces champs afin de choisir des éléments que nous souhaitons filtrer que ce soit au niveau du nom du commercial ou alors encore au niveau du nom du produit :

3. Comment exploiter les données ?

Maintenant que nous avons pu sélectionner des données extraites directement de notre base de données, nous allons mettre en place un certain nombre de calculs en utilisant ces valeurs.

Nous allons tout d'abord vouloir connaître le nombre de ventes qui correspondent aux valeurs que nous avons sélectionnées :

Pour cela, nous allons utiliser la fonction NB.SI.ENS() d’Excel :

 =NB.SI.ENS(Tableau1[Commercial];G6;Tableau1[Produit];G7)  

La fonction NB.SI.ENS() permet en effet de compter le nombre de cellules qui remplissent une condition spécifiée dans une plage de cellules donnée. Elle attend au minimum le couple de deux arguments suivants :

• La plage de cellules à analyser (obligatoire)
• La condition à vérifier (obligatoire)

Ces arguments pouvant se répéter pour tous les critères à analyser.

Puis, pour calculer le montant des ventes, la formule sera à peu près la même sauf que nous n'utilisons pas la fonction NB.SI.ENS(), mais SOMME.SI.ENS() qui fonctionne d’une manière relativement proche.

En effet, la fonction SOMME.SI.ENS() est également une fonction qui permet d’effectuer un calcul en fonction de critères. La différence ici c’est qu’elle attend trois arguments :

• La plage de cellules à analyser (obligatoire)
• La condition à vérifier (obligatoire)
• La plage de cellules de critères (optionnelle)

Le coupe d’arguments « condition » et plage_critere » pouvant ici aussi se répéter pour tous les critères à analyser.

 =SOMME.SI.ENS(Tableau1[Montant];Tableau1[Commercial];G6;Tableau1[Produit];G7)  

Enfin, pour calculer le prix moyen que cela représente, il suffit de diviser le montant par le nombre :

4. Calcul si aucun commercial n'est sélectionné

Alors maintenant, nous allons faire évoluer un petit peu les formules que nous venons de créer afin de pouvoir répondre au cas dans lequel nous n'aurions sélectionné aucun commercial. C’est-à-dire que nous voudrions connaître uniquement le nombre d'iPhone 13 Pro vendu par l'ensemble des commerciaux.

Pour cela, nous allons devoir adapter très légèrement les fonctions que nous avons utilisées, afin que si la cellule G6 que nous utilisons ici a pour valeur « (Tous) », nous puissions ignorer le critère de filtre.

Pour cela, la solution va consister à remplacer la valeur « (Tous) » par un astérisque (« * »), laquelle permet de spécifier à Excel que nous voulons récupérer toutes les données.

Pour cela, nous allons par exemple pouvoir utiliser la fonction SUBSTITUE() qui va permettre d'analyser la cellule G6 et de remplacer « (Tous) » par « * ».

Les formules deviennent alors :

=NB.SI.ENS(Tableau1[Commercial];SUBSTITUE(G6;"(Tous)";"*");Tableau1[Produit];SUBSTITUE(G7;"(Tous)";"*"))  
=SOMME.SI.ENS(Tableau1[Montant];Tableau1[Commercial];SUBSTITUE(G6;"(Tous)";"*");Tableau1[Produit];SUBSTITUE(G7;"(Tous)";"*"))  

5. Ajout d'un troisième critère et solution alternative qui fonctionne à tous les coups

La solution que nous venons de découvrir fonctionne très bien, à condition que le nombre de critères de sélection soit limité à deux. À partir de trois, celle-ci ne sera plus en mesure de faire fonctionner les fonction NB.SI.ENS() et SOMME.SI.ENS().

Heureusement, il existe encore une autre solution, laquelle va consister à insérer une nouvelle colonne dans le tableau afin de déterminer si tous les critères sont effectivement remplis.

Pour illustrer ce cas, nous allons commencer par ajouter une colonne avec les mois correspondants aux dates que nous allons calculer avec la fonction MOIS() d’Excel :

Puis nous ajoutons cette colonne mois dans la liste des champs servant de filtre dans le TCD, en actualisant ce dernier, puis en faisant glisser le champ « Mois » dans la zone « Filtre » :

Enfin, pour prendre en considérant ce nouveau champ, nous allons encore devoir ajouter une nouvelle colonne au tableau permettant de déterminer si oui ou non la ligne doit être prise en compte dans les calculs.

Voici la formule à inclure dans cette colonne, si vous souhaitez en savoir plus sur le détail de sa construction, je vous invite à consulter la vidéo d’illustration de ce tutoriel dans laquelle nous avons tout loisir de détailler son élaboration pas-à-pas :

 =ET(OU([@Commercial]=$I$6;$I$6="(Tous)");OU([@Produit]=$I$7;$I$7="(Tous)");OU([@Mois]=$I$8;$I$8="(Tous)"))  

Il ne reste plus qu’à modifier la fonction utilisée pour calculer le nombre de ventes :

 =NB.SI(Tableau1[Est sélectionnée];VRAI) 

Et celle pour le montant total des ventes : 

 =SOMME.SI(Tableau1[Est  sélectionnée];VRAI;Tableau1[Montant]) 

Ce qui nous permet maintenant de jouer avec les filtres ainsi mis en place pour modifier les résultats affichés par les calculs :

Edmond a ainsi vendu un seul iPhone 13 Pro au cours du mois d’avril, pour un montant de 1097€.