Je vous propose une amélioration de notre liste déroulante dynamique sur les anciennes versions d’Excel en effectuant un tri automatique pour celles qui passaient par un tableau structuré.
Le défaut de la première version est que la liste déroulante n’était pas triée alphabétiquement. Cela fait suite à une demande de plusieurs abonnés de la chaîne YouTube.
Solutions possibles
Il existe différentes manières de procéder :
- Une formule unique qui est très complexe qui nécessite 5 fonctions. Elle ne vient pas de moi mais de ce qu’on peut trouver sur Internet. J’ai pu la teste et elle fonctionne mal voire pas du tout (mon avis)
- Ma manière de faire que j’utilise depuis longtemps et vu pour d’autres chose (recherchev notamment). C’est ce que je vous propose.
Rendre la liste déroulante dynamique
Repartons sur notre exemple utilisé sur l’ancien article. J’ai extrait d’un tableau les services où sont rattachées des personnes.
J’ai au préalable supprimer les doublons.
Elle va se dérouler en 3 étapes :
- Déterminer le rang de l’élément dans la liste.
- Afficher la liste triée
- Créer la liste déroulante
1. Rang de l'élément
Il existe plusieurs fonctions dédiée à cela comme RANG ou PETITE.VALEUR mais cela fonctionne mal quand on range des valeur au format texte. Je vais vous montrer quelque chose de plus simple.
C’est un équivalent de ce que j’utilisais pour avoir plusieurs résultats avec la RECHERCHEV avant que RECHERCHEX et FILTRE arrivent.
On va utiliser la fonction NB.SI pour déterminer à chaque ligne combien d’éléments du tableau sont en dessous ou égaux à l’élément sur la ligne où je me trouve.
=NB.SI($A$2:$A$7;"<="&$A2)
Explication :
En B2, je calcule le nombre de valeurs sur A2 à A7 sont inférieures ou égales à la valeur de A2.
J’obtiens 3. Cela veut dire que des éléments sont avant A2 et donc que A2 est le troisième élément de la liste.
2. Afficher la liste triée
Maintenant qu’on a le rang de chaque élément, on peut lister dans l’ordre.
En tenant compte de la vieille version d’Excel que nous utilisons, j’opte pour la combinaison INDEX et EQUIV.
En C2, J’obtiens la formule :
=INDEX($A$2:$A$8;EQUIV(LIGNE(A1);$B$2:$B$8;0))
Explications :
- INDEX me permet d’afficher la liste des éléments qui se trouvent de A2 à A8
- EQUIV cherche la ligne de la liste en fonction de son rang (ci-dessous)
- LIGNE(A1) renvoie 1 car il détermine le numéro de ligne dans la cellule testée. J’utilise ce subterfuge pour que à la première ligne, il cherche le 1er rang.
Lors de la recopie de la formule, la deuxième ligne mette LIGNE(A2) donc 2, le 2ème élément de la liste. Ça m’évite de saisir manuellement 1,2,3,4,5, etc… dans la formule de chaque ligne et rend dynamique la formule.
3. Créer la liste déroulante
Vous constatez que la liste est désormais triée alphabétiquement. Dernière étape : créer la liste déroulante.
- Placez vous dans une cellule hors du tableau structuré (ex : E1)
- Allez dans Données > Validation de données
- Autorisez Liste
Saisissez =INDIRECT(« Tabl_bases2[tri] ») dans le champ Source
Cliquez sur OK
Notre liste déroulante est désormais triée dynamiquement.
SI vous le souhaitez, vous pouvez masquer les données inutiles (colonne A et B).
