La semaine dernière, nous abordions ensemble les listes déroulantes les plus basiques et les plus répandues. Aujourd’hui, nous débloquons ensemble toute la puissance de cet outil, et les possibilités de restriction qu’il offre, souvent insoupçonnées par le grand public. Pour plus d’informations sur les bases des listes déroulantes, voici le lien de l’article de la semaine dernière : ici
Si vous souhaitez en savoir plus sur les validations des données de manière générale, voici le lien de l’article qui présente toutes ses composantes une par une : ici
Niveau 2 : La liste déroulante à partir d’une plage nommée (colonne de tableau ou simplement une plage nommée)
Ici, nous commençons à rentrer dans des utilisations plus avancées de la liste déroulante, et donc plus dynamiques.
Chez Solpedinn, nous prônons toujours l’utilisation de tableaux, dans la mesure du possible. En voici une application intéressante. Jusqu’ici, nous avions des listes déroulantes relativement figées, qui nécessitent une retouche à chaque modification des paramètres. L’étape suivante est donc de les rendre dynamiques, donc sensibles aux modifications dans la source, que ce soit en termes de valeurs inscrites ou en termes de nombre de valeurs.
Ici, l’idée est de passer par une fonction INDIRECT.
Reprenons notre plage en B3:B9 et renommons cette plage en « prenoms » par exemple.
A partir de maintenant, si on écrit dans la source de notre liste déroulante =INDIRECT(‘’prenoms’’), nous obtenons le même résultat que dans la technique niveau 1. En cas d’ajout, cette façon de procéder exigera tout de même la modification de la taille de la plage nommée dans le gestionnaire de noms, mais pas dans la validation des données, (ce qui est encore moins usuel).
La question se pose alors : est-ce juste une manière plus compliquée de créer une liste déroulante de niveau 1 ?
La réponse est oui, si l’on en reste là. Ici, ce qui nous intéresse, c’est le principe qu’on peut décliner pour créer des listes déroulantes dépendantes, c’est-à-dire de pouvoir accéder à plusieurs listes selon une autre valeur sélectionnée dans une autre cellule.
Cela étant, il y a un type de plage nommée qui se crée et se modifie sans passer par le gestionnaire de noms et ce sont … les colonnes de tableau. Et dans ce cas-là, même plus besoin d’aller dans le gestionnaire des noms pour répercuter les modifications. Seul petit détail qui change par rapport à la simple plage nommée, maintenant, pour un tableau qu’on va nommer Tableau8 (par exemple) et dont la colonne qui nous intéresse s’appelle « Valeurs », on écrira =INDIRECT(‘’Tableau8[Valeurs]’’).
Avantages :
- Principe applicable pour des utilisations encore plus avancées (listes déroulantes dépendantes)
- Dynamique, le contenu est changeable sans devoir passer par l’outil validation des données de nouveau,
- Synergise particulièrement bien avec les colonnes de tableau
Inconvénients :
- Nécessite une formule dans la source avec INDIRECT, sans outil d’assistance à l’écriture
- Si le nom est supprimé ou modifié, ne fonctionnera plus
- Pas de tri ou de filtrage pour aider à la sélection dans la liste (même défaut que le niveau 1).
Niveau 2.5 : la liste déroulante dépendante
Ce type de liste déroulante est un dérivé de la liste déroulante avec la fonction INDIRECT (donc le niveau 2) mais avec une possibilité de taille en plus : il est possible de stocker dedans plusieurs listes simultanément, que l’on pourrait sélectionner en changeant la valeur dans une autre cellule. C’est pourquoi on l’appelle « dépendante », son contenu dépend du contenu d’une cellule tierce.
Comment ça fonctionne ? Plutôt que d’écrire directement dans la source le nom de la plage nommée ou la colonne de tableau, nous allons l’écrire dans une cellule, puis remplacer le nom par l’adresse de cette cellule. Ainsi, la liste déroulante sera extraite depuis la plage qui porte le nom de cette dernière, et donc, de cette manière, on peut théoriquement créer un grand nombre de listes déroulantes différentes dans une seule cellule.
En voici un exemple : nous avons un tableau nommé Tableau8 avec 2 colonnes : Equipe 1 et Equipe 2. Nous voudrions pouvoir sélectionner quelqu’un de l’équipe désirée, sans prendre le risque de prendre la mauvaise personne. Je saisis l’équipe qui m’intéresse en E3. Maintenant, ma formule de validation des données sera la suivante : =INDIRECT(‘’Tableau8[‘’ & E3 & ‘’]’’)
Maintenant, je peux contrôler quelle équipe apparaîtra dans la liste déroulante en changeant la cellule E3 (qui elle-même peut être une liste déroulante !).
Les avantages et les inconvénients de ce procédé sont les mêmes que le cas précédent.
Niveau 3 : La liste déroulante filtrable
C’est la toute dernière-née de la famille des listes, et elle offre des possibilités jusqu’ici disponibles uniquement sur Google Sheets (c’est d’ailleurs un des cas où Google Sheets écrase Excel) : la liste déroulante qui dépend d’une fonction auto-propagée telle que FILTRE, TRIER ou encore TRIERPAR.
Cette technique offre tous les avantages de tous les autres niveaux, et y ajoute la possibilité de filtrer les listes. C’est particulièrement utile pour les listes longues ou très longues, puisqu’on peut décider d’entrer une partie de ce qu’on recherche, puis de le sélectionner parmi les valeurs qui contiennent cette partie.
En revanche, cette version n’est pas disponible (à ma connaissance) sur les versions antérieures à Excel 365. Il existe bien des manières de contourner cela, notamment à l’aide de la fonction DECALER, qui sera encore plus complexe. Je choisis de ne pas présenter ces solutions de contournement, puisqu’elles sont n’apportent pas d’amélioration (que ce soit en termes d’utilité ou en termes de simplicité) sur la version que j’utilise.
D’abord, regardons comment créer cette liste déroulante, puis regardons comment la rendre dépendante.
Pour la version de base, nous procédons en 3 étapes :
- Créer une colonne extraite de la colonne source avec une fonction FILTRE, couplée à une fonction GAUCHE (plus de détails dans l’exemple qui arrive juste après). Optionnellement, on peut y ajouter une fonction TRIER, pour avoir les résultats dans l’ordre, même après le filtre.
- Dans la fenêtre de validation de données, paramétrer l’alerte d’erreur pour qu’elle accepte tout de même les erreurs (puisque saisir une partie et également une erreur de saisie).
- Dans la source de la liste déroulante, sélectionner la cellule où la fonction FILTRE a été utilisée, puis y ajouter un #. Le # après une adresse signifie que dans le cadre d’une formule auto-propagée, la liste ira chercher toutes les cellules où elle s’est propagée automatiquement.
Ainsi, nous obtenons une liste déroulante qui fonctionne extrêmement bien avec les longues listes, puisqu’on peut effectuer une recherche dedans, ce qui évite de se prendre une avalanche de valeurs.
Comme promis, voici l’exemple (avec les même données et adresses que lors des autres niveaux) :
- Dans ma cellule E3 j’utilise la fonction FILTRE couplée à une fonction GAUCHE et une fonction NBCAR. L’idée ici est de créer une liste de valeurs qui commencent de la même manière que caractères que j’écris dans la cellule D3, cellule où la liste déroulante sera située. En bonus, j’ajoute une fonction TRIER autour, pour avoir les prénoms dans l’ordre alphabétique.
- Maintenant je retourne en D3, et je peux commencer à créer ma liste déroulante. La première étape est de retirer la restriction qui m’empêche de saisir une valeur non conforme en allant dans Alerte d’erreur, puis choisir Avertissement ou Information. Cette étape n’est pas obligatoire, mais facilite grandement l’utilisation. Bien entendu, cela vient avec un problème : Excel n’empêchera plus la saisie de données non conformes et se contentera d’avertir l’utilisateur. C’est donc une option à considérer.
- Dans la source de ma liste déroulante, je tape =E3#. Le tour est joué !
-
Malheureusement, il est, à ce jour, pas possible de mettre la fonction FILTRE directement dans la source de la liste déroulante, c’est pourquoi nous sommes obligés d’utiliser une colonne supplémentaire, et de procéder en 3 étapes.
Avantages :
- Le meilleur type de liste quand on dispose d’un nombre important de valeurs possibles
- La liste déroulante simple la plus dynamique
Inconvénients :
- Complexe, demande d’utiliser une colonne supplémentaire, et plusieurs étapes,
- Requiert Excel 365 pour être mis en place
- N’empêche plus les saisies erronées, même si un avertissement est adressé à l’utilisateur.
Niveau 3.5 : La liste déroulante filtrable et dépendante
C’est la réponse à la question suivante : comment avoir accès à plusieurs listes déroulantes filtrables dans une seule liste déroulantes filtrable ? Il « suffit » de combiner notre fonction FILTRE avec la fonction INDIRECT que nous avions utilisée sur la liste déroulante dépendante !
Donc, pour combiner les 2, il va falloir remplacer les plages ciblées par la fonction FILTRE, par une fonction INDIRECT, de la même manière que lors de la création de la liste déroulante dépendante classique.
Procédons par le même exemple :
Ici, en G3, j’ai utilisé la même fonction FILTRE que lors de l’exemple précédent à une différence près : j’ai remplacé B3:B9 (l’adresse de la première colonne) par « INDIRECT(‘’Tableau8[‘’ & E3 & ‘’]’’). Bien entendu, en E3, j’ai inscrit le nom de la colonne qui m’intéresse, et c’est donc de E3 que le contenu va dépendre.
Pour ce qui est du reste des manipulations, la procédure est exactement la même que pour la liste déroulante filtrable, je choisis donc de ne pas la détailler.
Avantages :
- Le meilleur de tous les mondes en termes de dynamisme avec des listes dépendantes, filtrables et sensibles au changement de taille des listes,
- Tous les avantages de la liste déroulante dépendante, ainsi que tous ceux de la liste déroulante filtrable
Inconvénients :
- C’est également la liste déroulante la plus technique à mettre en place, et elle demande de bien maîtriser les noms dans le classeur (plages nommées et/ou colonnes de tableau)
- Dans le cas du tableau, si la liste doit dépendre de plusieurs tableaux (et plusieurs colonnes), cela complexifie encore plus la fonction INDIRECT,
- Problème de protection contre les saisies erronées,
- Nécessite une colonne supplémentaire pour stocker les données filtrées,
- Nécessite également l’accès à la version Office 365.
Quelle liste déroulante choisir dans toutes ces options ?
Excel, et en l’occurrence les listes déroulantes, sont un peu des allégories de la vie : plus cela demande de l’effort, plus le résultat est bluffant, mais avoir un résultat bluffant n’est pas forcément utile dans tous les cas. Donc, avant d’aller sur les listes déroulantes filtrables et dépendantes, il faudrait se poser la question « quel type de liste est le plus utile ? ».
Dans chaque cas, ce n’est ni plus ni moins qu’une question de rapport qualité/prix, ou encore, quel type de liste a les atouts recherchés et les inconvénients les moins gênants, et, dans cette optique, la liste déroulante filtrable et dépendante n’est pas mieux ou moins bien que la simple liste déroulante avec les valeurs saisies directement dedans.
Bien entendu, il existe également d’autres types de liste déroulante, dont ceux à base d’un mix entre la fonction DECALER et la fonction NBVAL, (avec parfois EQUIV pour la version dépendante, mais comme précisé précédemment, en l’absence de réel avantage par rapport aux versions (ce point est très ouvert au débat) abordées ici, nous nous contentons d’une mention honorable.
Nous n’avons pas non plus abordé les sous-variantes (par exemple, l’intégration de fonctions de texte dans les formules pour mieux coller aux noms) de toutes les formules abordées, dans le souci de rester le plus simple possible, puisque les possibilités offertes par ces sous-variantes impacteraient la clarté de la description, sans apporter réellement sur le plan du principe général et de l’utilité.
