Liste déroulante sans doublons et triée avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Listes déroulantes uniques et triées

Nous allons travailler sur une base de données assez dense des idées de sorties. Forcément, les départements, les villes et même les activités sont répétées à de nombreuses reprises. L'objectif est de créer des listes déroulantes sur ces clés. Il doit s'agir de listes de valeurs uniques triées dans l'ordre alphabétique croissant. Autrefois, et nous l'avions démontré dans de nombreuses formations, il fallait utiliser des calculs intermédiaires assez longs ou des formules matricielles plutôt complexes pour réaliser le tour de force. Depuis 2019, Excel propose des fonctions raisonnant de façon matricielle mais ne sollicitant pas l'esprit de l'utilisateur sur ce point. Elles se chargent de tout. Il s'agit des fonctions Unique et Trier. Elles portent bien leurs noms.



Sur l'exemple illustré par la capture, sur une base de données des idées de sorties, l'utilisateur peut actionner deux listes déroulantes sur la droite du tableau de données. La première ne propose que les départements, purgés de leurs doublons et triés dans l'ordre croissant. La seconde, dans le même registre, ne propose que les villes uniques. Certes ces deux listes ne sont pas encore liées à ce stade. En d'autres termes, la seconde ne propose pas que les villes uniques et triées, correspondant au département choisi avec la première liste déroulante. Mais ce volet est une étape du processus que nous souhaitons accomplir.

Classeur Excel à télécharger
Pour la démonstration de cette astuce Excel, nous suggérons d'appuyer les travaux sur un classeur offrant cette base de données.

• Télécharger le classeur listes-triees-sans-doublons.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous trouvons le tableau des idées de sorties entre les colonnes B et E, sur plusieurs centaines de lignes. Les plages des départements et des villes sont référencées dynamiquement de manière à accepter de nouvelles entrées à tout moment. Nous proposons de le constater.

• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,

Comme vous le constatez, deux plages nommées ont été travaillées par formules.



La première se nomme Dep. Elle représente tous les départements, ceux existants et ceux à venir. La seconde se nomme Villes. Elle représente toutes les villes, celles existantes et celles à venir. Toutes deux exploitent les fonctions Decaler et NbVal pour les faire progresser en hauteur en fonction du potentiel nouveau contenu ajouté à la volée.

Trier les valeurs uniques
Depuis la version 2019, Excel propose notamment deux fonctions raisonnant de façon matricielle. Il s'agit des fonctions Trier et Unique. En les combinant sur les plages de cellules constatées précédemment, nous devons être en mesure de créer deux listes, une pour les départements, l'autre pour les villes, sans doublons et triées dans l'ordre alphabétique croissant. Nous proposons d'agir sur des colonnes arbitraires, à droite de la feuille, pour produire ces extractions organisées. Nous agirons ensuite sur ces colonnes pour créer des plages nourrissant les listes déroulantes, en cellules G4 et G7.

• Fermer le gestionnaire de noms pour revenir sur la feuille Excel,
• Cliquer par exemple sur la cellule K1 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la première formule matricielle,
• Inscrire la fonction de tri suivie d'une parenthèse,soit : Trier(,
• Inscrire la fonction pour éliminer les doublons, suivie d'une parenthèse, soit : Unique(,
• Désigner la plage dynamique des départements par son nom, soit : Dep,
• Fermer la parenthèse de la fonction Unique,
• Puis, fermer la parenthèse de la fonction Trier,
• Dès lors, valider la formule par la touche Entrée du clavier,

Comme vous pouvez l'apprécier, tous ses résultats se répandent automatiquement sur les cellules du dessous, tant qu'il y a des départements non identiques aux précédents, à extraire. Et ceux-ci sont dans le même temps, triés dans l'ordre alphabétique croissant. C'est toute la magie de ces fonctions matricielles qui simplifient considérablement la tâche.

• De la même façon, en cellule L1, construire et valider la formule suivante :

=Trier(Unique(Villes))

Cette fois, ce sont bien toutes les villes qui sont restituées dans l'ordre croissant et sans doublons.

Plages uniques triées et évolutives
Comme les villes et les départements sont susceptibles d'être enrichis, ces plages d'extractions sont censées elles aussi évoluer dynamiquement. Pour pouvoir les exploiter comme sources de données évolutives pour les listes déroulantes, nous devons commencer par les représenter par des noms de plages capables de considérer les nouvelles potentielles données. Là encore, les fonctions Excel Decaler et NbVal doivent intervenir dans des noms de plages à créer.

• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,
• Dans la boîte de dialogue, cliquer sur le bouton Nouveau,
• Dans la zone Nom, taper l'intitulé suivant : listeDep,
• Dans la zone Fait référence à, supprimer le contenu,
• Puis, taper le symbole égal (=) pour initier la syntaxe de la nouvelle plage dynamique,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Puis, désigner le premier département extrait, soit K1 : valeursUniquesTriees!$K$1,

Dans ce contexte, la cellule est toujours figée et apparaît toujours préfixée du nom de la feuille qui l'héberge.

• Taper trois points-virgules, soit: ;;;, pour passer directement dans l'argument de la hauteur,

En effet, nous ne souhaitons observer aucun décalage ni en ligne, ni en colonne. Tout ce que nous souhaitons est de faire grandir cette plage en hauteur, en fonction des nouveaux potentiels départements ajoutés et donc extraits naturellement par les fonctions matricielles.

• Inscrire la fonction pour compter les cellules non vides, soit : NbVal(,
• Désigner tous les départements en cliquant sur son étiquette de colonne K, ce qui donne :

valeursUniquesTriees!$K:$K

Ainsi, nous comptons le nombre de départements extraits pour déterminer dynamiquement la hauteur de la plage devant servir de source de données à la première liste déroulante.

• Fermer la parenthèse de la fonction NbVal,
• Puis, fermer la parenthèse de la fonction Decaler,
• Valider la syntaxe de cette plage dynamique en cliquant sur le bouton Ok,

Ainsi, nous sommes de retour sur la première boîte de dialogue. La plage que nous venons de créer y apparaît listée.

• Cliquer encore sur le bouton Nouveau,
• Dans la zone Nom, taper l'intitulé listeVilles,
• Dans la zone Fait référence à, adapter la précédente syntaxe comme suit :

=DECALER(valeursUniquesTriees!$L$1;;;NBVAL(valeursUniquesTriees!$L:$L))

En effet, nous agissons cette fois sur la colonne L des villes. Nous scrutons la hauteur variable de cette plage grâce à la fonction NbVal, comme précédemment.

• Cliquer sur le bouton Ok pour valider la création de cette nouvelle plage dynamique,

Les deux plages apparaissent désormais dans le gestionnaire de noms avec leurs syntaxes respectives. Nous allons pouvoir les exploiter comme sources de données des listes déroulantes à construire en cellules G4 et G7.

• Cliquer sur le bouton Fermer du gestionnaire pour revenir sur la feuille Excel,

Listes déroulantes triées et sans doublons
Il ne nous reste plus qu'à utiliser ces noms dynamiques construits sur ces formules matricielles pour produire des contenus de listes déroulantes, organisés dans l'ordre croissant et dépourvus de valeurs redondantes.

• Sélectionner la cellule de la première liste déroulante à construire en cliquant sur sa case G4,
• En haut de la fenêtre Excel, cliquer sur l'onglet Données pour activer son ruban,
• Dans la section Outils de données du ruban, cliquer sur le bouton Validation des données,
• Dans la boîte de dialogue qui suit, déployer la liste déroulante intitulée Autoriser,
• Dans les propositions, choisir l'option Liste,
• Puis, cliquer dans la zone du dessous intitulée Source pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe,
• Puis, appeler la plage des départements par son nom, soit : ListeDep,
• Cliquer sur le bouton Ok pour valider la liaison,

De retour sur la feuille, vous constatez avec plaisir que la cellule G4 offre effectivement la liste des départements uniques triés dans l'ordre croissant.

• Cliquer sur la cellule G7 pour la sélectionner,
• Dans le ruban Données, cliquer sur le bouton Validation des données,
• Dans la boîte de dialogue, déployer la liste déroulante de la zone Autoriser,
• Dans les propositions, choisir l'option Liste,
• Dans la zone Source, construire la liaison suivante : =listeVilles,
• Valider cette source de données en cliquant sur le bouton Ok,

De retour sur la feuille et comme précédemment, vous constatez que cette seconde liste déroulante est chargée des villes uniques triées dans l'ordre croissant. Certes et nous l'avions annoncé, ces listes déroulantes ne sont pas reliées entre elles, mais nous avions démontré le processus à l'occasion de plusieurs formations. D'ailleurs l'astuce suivante proposera d'établir ces liaisons avec une simplicité déconcertante. Dès lors, la voie sera ouverte pour produire des extractions chirurgicales sur ces multiples choix recoupés en cascade.

Quoiqu'il en soit, si vous ajoutez de nouveaux départements et de nouvelles villes en queue de liste de la base de données, vous constatez que ceux-ci sont immédiatement intégrés dans les listes déroulantes, aux emplacements qui les attendent, selon les tris organisés par les formules matricielles.