Le filtre Excel est l'une des fonctionnalités les plus utilisées lorsque vous travaillez avec des données. Dans cet article de blog, je vais vous montrer comment créer un champ de recherche de filtre Excel dynamique, de sorte qu'il filtre les données en fonction de ce que vous tapez dans le champ de recherche.
Quelque chose comme indiqué ci-dessous :
Il y a une double fonctionnalité à cela - vous pouvez sélectionner le nom d'un pays dans la liste déroulante, ou vous pouvez saisir manuellement les données dans la zone de recherche, et cela vous montrera tous les enregistrements correspondants. Par exemple, lorsque vous tapez "I", cela vous donne tous les noms de pays avec l'alphabet I dedans.
Regarder la vidéo - Création d'un champ de recherche de filtre Excel dynamique
Création d'une zone de recherche de filtre Excel dynamique
Ce filtre Excel dynamique peut être créé en 3 étapes :
- Obtenir une liste unique d'articles (pays dans ce cas). Cela serait utilisé dans la création de la liste déroulante.
- Création du champ de recherche. Ici, j'ai utilisé une Combo Box (contrôle ActiveX).
- Paramétrage des données. Ici, j'utiliserais trois colonnes d'aide avec des formules pour extraire les données correspondantes.
Voici à quoi ressemblent les données brutes :
CONSEIL UTILE : C'est presque toujours une bonne idée de convertir vos données dans un tableau Excel. Vous pouvez le faire en sélectionnant n'importe quelle cellule de l'ensemble de données et en utilisant le raccourci clavier Ctrl + T.
Étape 1 - Obtenir une liste unique d'articles
- Sélectionnez tous les pays et collez-le dans une nouvelle feuille de calcul.
- Sélectionnez la liste des pays -> Aller aux données -> Supprimer les doublons.
- Dans la boîte de dialogue Supprimer les doublons, sélectionnez la colonne dans laquelle vous avez la liste et cliquez sur OK. Cela supprimera les doublons et vous donnera une liste unique, comme indiqué ci-dessous :
- Une étape supplémentaire consiste à créer une plage nommée pour cette liste unique. Pour faire ça:
- Allez dans l'onglet Formule -> Définir le nom
- Dans la boîte de dialogue Définir un nom :
- Nom : Liste des pays
- Portée : Cahier d'exercices
- Fait référence à : =UniqueList!$A$2:$A$9 (j'ai la liste dans un onglet séparé nommé UniqueList dans A2:A9. Vous pouvez vous référer à l'endroit où se trouve votre liste unique)
REMARQUE : Si vous utilisez la méthode « Supprimer les doublons » et que vous développez vos données pour ajouter plus d'enregistrements et de nouveaux pays, vous devrez répéter cette étape. Alternativement, vous pouvez également vous une formule pour rendre ce processus dynamique.
Étape 2 - Création de la zone de recherche du filtre Excel dynamique
Pour que cette technique fonctionne, nous aurions besoin de créer une « boîte de recherche » et de la lier à une cellule.
Nous pouvons utiliser la zone de liste déroulante dans Excel pour créer ce filtre de zone de recherche. De cette façon, chaque fois que vous entrez quelque chose dans la zone de liste déroulante, cela sera également reflété dans une cellule en temps réel (comme indiqué ci-dessous).
Voici les étapes à suivre :
- Accédez à l'onglet Développeur -> Contrôles -> Insérer -> Contrôles ActiveX -> Zone de liste déroulante (contrôles ActiveX).
- Si vous n'avez pas l'onglet Développeur visible, voici les étapes pour l'activer.
- Si vous n'avez pas l'onglet Développeur visible, voici les étapes pour l'activer.
- Cliquez n'importe où sur la feuille de calcul. Il insèrera la Combo Box.
- Cliquez avec le bouton droit sur la zone de liste déroulante et sélectionnez Propriétés.
- Dans la fenêtre Propriétés, apportez les modifications suivantes :
- Cellule liée : K2 (vous pouvez choisir n'importe quelle cellule dans laquelle vous souhaitez qu'elle affiche les valeurs d'entrée. Nous utiliserons cette cellule pour définir les données).
- ListFillRange : CountryList (il s'agit de la plage nommée que nous avons créée à l'étape 1. Cela afficherait tous les pays dans la liste déroulante).
- MatchEntry : 2-fmMatchEntryNone (cela garantit qu'un mot n'est pas automatiquement complété lorsque vous tapez)
- Avec la zone de liste déroulante sélectionnée, accédez à l'onglet Développeur -> Contrôles -> Cliquez sur Mode de conception (cela vous fait sortir du mode de conception et vous pouvez maintenant taper n'importe quoi dans la zone de liste déroulante. Maintenant, tout ce que vous tapez sera reflété dans la cellule K2 en temps réel)
Étape 3 - Définition des données
Enfin, nous relions tout par des colonnes d'aide. J'utilise ici trois colonnes d'aide pour filtrer les données.
Colonne d'assistance 1 : Saisissez le numéro de série de tous les enregistrements (20 dans ce cas). Vous pouvez utiliser la formule ROWS() pour ce faire.
Colonne d'assistance 2 : Dans la colonne d'aide 2, nous vérifions si le texte saisi dans la zone de recherche correspond au texte dans les cellules de la colonne pays.
Cela peut être fait en utilisant une combinaison de fonctions IF, ISNUMBER et SEARCH.
Voici la formule :
=SI(ESTNUM(RECHERCHE($K$2,D4)),E4,"")
Cette formule recherchera le contenu dans la zone de recherche (qui est liée à la cellule K2) dans la cellule qui a le nom du pays.
S'il y a une correspondance, cette formule renvoie le numéro de ligne, sinon elle renvoie un blanc. Par exemple, si la zone de liste déroulante a la valeur 'US', tous les enregistrements avec le pays comme 'US' auront le numéro de ligne, et le reste sera vide ("")
Colonne d'aide 3 : Dans la colonne d'assistance 3, nous devons empiler tous les numéros de ligne de la colonne d'assistance 2. Pour ce faire, nous pouvons utiliser une combinaison si les formules IFERREUR et SMALL. Voici la formule :
=SIERREUR(PETIT($F$4:$F$23,E4),"")
Cette formule empile tous les numéros de ligne correspondants. Par exemple, si la zone de liste déroulante a la valeur US, tous les numéros de ligne contenant « US » sont empilés ensemble.
Maintenant, lorsque nous avons empilé les numéros de ligne, il nous suffit d'extraire les données de ces numéros de ligne. Cela peut être fait facilement en utilisant la formule d'index (insérez cette formule à l'endroit où vous souhaitez extraire les données. Copiez-la dans la cellule en haut à gauche où vous souhaitez extraire les données, puis faites-la glisser vers le bas et vers la droite).
=SIERREUR(INDICE($B$4:$D$23,$G4,COLONNES($I$3:I3)),"")
Cette formule comporte 2 parties :
INDICE - Cela extrait les données en fonction du numéro de ligne.
SIERREUR - Cela retourne vide lorsqu'il n'y a pas de données.
Voici un aperçu de ce que vous obtenez enfin :
La zone de liste déroulante est une liste déroulante ainsi qu'une zone de recherche. Vous pouvez masquer les données d'origine et les colonnes d'assistance pour afficher uniquement les enregistrements filtrés. Vous pouvez également avoir les données brutes et les colonnes d'aide dans une autre feuille et créer ce filtre Excel dynamique dans une autre feuille de calcul.
Faire preuve de créativité! Essayez quelques variantes
Vous pouvez essayer de le personnaliser selon vos besoins. Vous voudrez peut-être créer plusieurs filtres Excel au lieu d'un. Par exemple, vous souhaiterez peut-être filtrer les enregistrements dans lesquels Sales Rep est Mike et Country est Japan. Cela peut être fait en suivant exactement les mêmes étapes avec quelques modifications dans la formule dans les colonnes d'aide.
Une autre variante pourrait consister à filtrer les données commençant par les caractères que vous entrez dans la zone de liste déroulante. Par exemple, lorsque vous entrez « I », vous pouvez extraire les pays commençant par I (par rapport à la construction actuelle où cela vous donnerait également Singapour et les Philippines car il contient l'alphabet I).
Comme toujours, la plupart de mes articles sont inspirés des questions/réponses de mes lecteurs. J'aimerais avoir vos commentaires et apprendre de vous. Laissez vos impressions dans la section commentaires.
Remarque : si vous utilisez Office 365, vous pouvez utiliser la fonction FILTRE pour filtrer rapidement les données au fur et à mesure que vous tapez. C'est plus facile que la méthode montrée dans ce tutoriel.
