Introduction
Excel est un outil couramment utilisé pour l’analyse de données et la présentation de résultats. Il permet de créer des tableaux et des graphiques facilement. Cependant, il y a des fonctionnalités moins connues mais qui peuvent s’avérer très pratiques, comme la création d’une liste déroulante auto-filtrante. Une liste déroulante permet d’afficher un choix prédéfini d’éléments dans une cellule. Lorsque la liste déroulante est auto-filtrante, elle permet de filtrer les données du tableau en fonction du choix sélectionné dans la liste. Dans cet article, nous allons voir comment créer une liste déroulante auto-filtrante sur Excel.
Créer une liste déroulante simple
La première étape est de créer une liste déroulante simple. Pour cela, il faut suivre les étapes suivantes :
- Dans une cellule, tapez les éléments de la liste, séparés par des virgules. Par exemple : "Pommes, Poires, Bananes".
- Sélectionnez la cellule où vous voulez mettre la liste déroulante.
- Cliquez sur l’onglet "Données" dans le ruban Excel.
- Cliquez sur "Validation des données" dans le groupe "Outils de données".
- Dans la boîte de dialogue "Validation des données", sélectionnez "Liste" dans le menu déroulant "Autoriser".
- Dans la zone "Source", tapez la référence aux cellules qui contiennent les éléments de la liste, en commençant par le signe égal. Par exemple : "=A1:A3".
- Cliquez sur "OK".
La liste déroulante est maintenant visible dans la cellule sélectionnée. Si vous cliquez sur la flèche de la liste déroulante, les éléments de la liste apparaissent.
Créer une liste déroulante auto-filtrante
Maintenant que nous avons créé une liste déroulante simple, nous allons voir comment la rendre auto-filtrante. Pour cela, il faut ajouter une formule dans la zone "Source" de la validation des données.
- Dans une cellule, tapez les éléments de la liste, séparés par des virgules. Par exemple : "Pommes, Poires, Bananes".
- Sélectionnez la cellule où vous voulez mettre la liste déroulante.
- Cliquez sur l’onglet "Données" dans le ruban Excel.
- Cliquez sur "Validation des données" dans le groupe "Outils de données".
- Dans la boîte de dialogue "Validation des données", sélectionnez "Liste" dans le menu déroulant "Autoriser".
- Dans la zone "Source", tapez la formule suivante :
=INDIRECT("A"&MATCH(D1,$A$1:$A$10,0)+1&":A"&MATCH("*",$A$1:$A$10,MATCH(D1,$A$1:$A$10,0))-1+MATCH(D1,$A$1:$A$10,0))
- Cliquez sur "OK".
La liste déroulante est maintenant auto-filtrante. Si vous cliquez sur la flèche de la liste déroulante, les éléments de la liste apparaissent. Si vous sélectionnez un élément, la liste déroulante se met à jour pour afficher uniquement les éléments qui correspondent à ce choix.
Explication de la formule
La formule dans la zone "Source" de la validation des données est un peu complexe. Voici une explication détaillée de chaque partie :
=INDIRECT("A"&MATCH(D1,$A$1:$A$10,0)+1&":A"&MATCH("*",$A$1:$A$10,MATCH(D1,$A$1:$A$10,0))-1+MATCH(D1,$A$1:$A$10,0))
- La fonction MATCH recherche la position de l’élément sélectionné dans la colonne qui contient la liste. Par exemple, si nous avons sélectionné "Poires", MATCH renvoie le numéro de ligne 2.
- La fonction INDIRECT crée une référence de plage à partir de deux références de cellule (début et fin) sous forme de texte. Elle prend en entrée une chaîne de caractères qui représente une référence de cellule. Par exemple, si nous avons sélectionné "Poires", la référence de départ est "A3" et la référence de fin est "A4". La fonction INDIRECT crée la chaîne de caractères "=A3:A4" qui est ensuite interprétée comme une référence de plage par Excel.
- La fonction MATCH est utilisée une deuxième fois pour trouver la position de la première cellule vide dans la colonne qui contient la liste, en partant de la ligne où se trouve l’élément sélectionné. Cette position moins un est ajoutée à la première position trouvée par la première fonction MATCH. Cela donne la référence de fin de la plage. Par exemple, si nous avons sélectionné "Poires" et que la première cellule vide se trouve à la ligne 5, la référence de fin est "A4:A5".
- La plage de référence créée par la fonction INDIRECT est passée à la validation des données. Cette plage est auto-filtrante car elle ne contient que les éléments qui correspondent à l’élément sélectionné dans la liste déroulante.
Utilisation de la liste déroulante auto-filtrante
La liste déroulante auto-filtrante est très utile pour filtrer les données d’un tableau en fonction de critères prédéfinis. Par exemple, si nous avons une liste de clients avec leur nom, leur ville et leur pays, nous pouvons créer une liste déroulante pour chaque critère et filtrer les données en temps réel. Pour cela, il suffit de suivre les étapes suivantes :
- Créez une liste déroulante auto-filtrante pour chaque critère (nom, ville, pays).
- Sélectionnez les données à filtrer.
- Cliquez sur l’onglet "Données" dans le ruban Excel.
- Cliquez sur "Filtrer" dans le groupe "Trier et filtrer".
- Cliquez sur la flèche du filtre pour le critère que vous souhaitez filtrer.
- Sélectionnez les éléments que vous voulez afficher dans le tableau.
- Cliquez sur "OK".
Le tableau est maintenant filtré en fonction des critères sélectionnés dans les listes déroulantes. Si vous changez un critère, le tableau se met à jour automatiquement.
Astuces et conseils
Voici quelques astuces et conseils pour travailler plus efficacement avec les listes déroulantes auto-filtrantes :
- Pour éviter les erreurs, il est recommandé d’utiliser des références absolues dans la formule de la zone "Source". Par exemple, "=A$1:A$10" plutôt que "=A1:A10".
- Si la liste contient beaucoup d’éléments, il peut être difficile de trouver l’élément que vous cherchez dans la liste déroulante. Pour faciliter la recherche, ajoutez un champ de recherche en haut de la liste déroulante. Pour cela, cliquez sur la flèche de la liste déroulante, puis sur "Recherche".
- Si vous avez besoin de créer plusieurs listes déroulantes auto-filtrantes pour différents critères, il peut être fastidieux de recopier la formule dans chaque zone "Source". Pour éviter cela, créez la formule dans une cellule cachée, puis référencez cette cellule dans la zone "Source" de chaque liste déroulante.
- Si vous voulez créer une liste déroulante qui affiche des éléments en fonction d’un critère qui n’est pas dans la liste, utilisez la fonction INDEX. Cette fonction renvoie la valeur d’une cellule située à un certain numéro de ligne et de colonne dans une plage. Par exemple, si vous avez une liste de fruits avec leur couleur, leur variété et leur prix, vous pouvez créer une liste déroulante pour afficher les variétés de pommes rouges. Pour cela, tapez "=INDEX($B$2:$C$10,MATCH("Pommes Rouges",$A$2:$A$10,0),2)" dans la zone "Source" de la validation des données. Cette formule renvoie le prix des pommes rouges, qui est situé dans la deuxième colonne de la plage $B$2:$C$10.
Conclusion
La création d’une liste déroulante auto-filtrante sur Excel peut sembler un peu complexe, mais elle est très utile pour filtrer les données d’un tableau en fonction de critères prédéfinis. En utilisant la formule adéquate dans la zone "Source" de la validation des données, vous pouvez créer des listes déroulantes qui se mettent à jour automatiquement en fonction des choix de l’utilisateur. N’hésitez pas à utiliser les astuces et conseils présentés dans cet article pour travailler plus efficacement avec les listes déroulantes auto-filtrantes.
Note : Cet article n'est pas mis à jour régulièrement et peut contenir des informations obsolètes ainsi que des erreurs.
