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

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 formation - liste de recherche - 01

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 :

Excel formation - liste de recherche - 02

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 :

Excel formation - liste de recherche - 03

 

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 :

Excel formation - liste de recherche - 04

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 :

Excel formation - liste de recherche - 05

 

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 :

Excel formation - liste de recherche - 06

 

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 :

Excel formation - liste de recherche - 07

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

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

Excel formation - liste de recherche - 08

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)  

Excel formation - liste de recherche - 09

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

Excel formation - liste de recherche - 10

 

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

Excel formation - liste de recherche - 11

 

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 :

Excel formation - liste de recherche - 12

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

Excel formation - liste de recherche - 13

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

Excel formation - liste de recherche - 14

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 :

Excel formation - liste de recherche - 15

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

 



Articles qui pourraient vous intéresser

Comment convertir une adresse postale en coordonnées GPS sur Excel (Latitude et longitude) ?
Découvrez comment créer un menu de recherche multicritères et intelligent (comme Google) sur Excel (sans VBA)
Comment créer des graphiques imbriqués dans Excel sans utiliser Power BI ?
Comment gérer ses comptes sur Excel avec le « Suivi du budget familial »
Comment modifier la mise en forme d’un tableau à la volée en 1 clic sur Excel (sans VBA) ?
Comment purger les anciennes valeurs qui restent dans un champ d’un tableau croisé dynamique ?
Comment répartir une base sur plusieurs feuilles Excel avec les tableaux croisés dynamiques sur Excel ?
Comment obtenir le détail d’une valeur d’un tableau croisé dynamique en 1 clic sur Excel
Comment lancer automatiquement une macro à l’ouverture d’un fichier Excel ?
Comment protéger efficacement un classeur Excel en utilisant une clé USB comme protection physique ?
Comment lire un fichier externe (txt, csv,…) avec une fonction Excel VBA ?
Comment créer un arbre généalogique en quelques minutes sur Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2023 - 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.