Relier trois listes déroulantes Excel très facilement

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


Sujets que vous pourriez aussi aimer :


Relier facilement trois listes déroulantes

Dans le volet précédent, nous avons appris à créer des listes déroulantes chargées de valeurs uniques, organisées dans l'ordre alphabétique croissant. Pour cela, nous avions exploiter les fonctions matricielles Trier et Unique. Mais ces listes ne proposaient aucune dépendance. C'est le cas que nous souhaitons résoudre désormais.



Sur l'exemple illustré par la capture, nous travaillons sur une feuille indépendante de celle hébergeant la base de données. Elle est destinée à produire l'extraction des informations, selon les choix recoupés émis par l'utilisateur, par le biais de trois listes déroulantes reliées entre elles et placées sur la droite de la feuille.

En effet, lorsque l'utilisateur déploie la première liste déroulante, il peut choisir un département dans une liste de valeurs uniques et triées. C'est alors qu'il déploie la deuxième liste déroulante, celle des villes. Il peut maintenant choisir l'une des villes du département sélectionné en amont. Ces données, en plus d'avoir été purgées des doublons et triées, ont été filtrées sur le choix du département. A validation d'une ville, il déploie la troisième et dernière liste déroulante. Il ne lui reste plus qu'à désigner une activité de sortie parmi celles filtrées en fonction du département et de la ville des deux précédentes listes déroulantes.

Classeur Excel à télécharger
Pour développer le mécanisme, nous suggérons d'appuyer les travaux sur un classeur offrant une base de données plutôt riche en informations, donc propice aux recoupements.

• Télécharger le classeur listes-liaisons-formules.xlsm 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 débouchons sur la première feuille de ce classeur. Elle est nommée Liaisons. Elle offre une grille d'extraction à réaliser en fonction des choix émis par les trois listes déroulantes. Ces listes doivent être construites sur la droite de cette feuille, en cellules respectives G4, G7 et G10. La deuxième feuille est nommée BDD. C'est elle qui héberge la base de données des activités de sorties. C'est en fonction de ses informations que nous devons charger les listes déroulantes et les articuler entre elles. Enfin, la dernière feuille se nomme Inter.



Nous l'exploiterons pour créer les trois sources de données des listes déroulantes à relier entre elles.

Les plages de cellules évolutives
Pour que les données articulées puissent réagir au gré des nouvelles informations ajoutées dans la base de données, nous avons créé des plages de cellules dynamiques.

• 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,

Vous constatez la présence de quatre plages construites par calculs dynamiques. La plage table représente l'intégralité des informations de la base de données. Les trois autres font référence respectivement aux colonnes des activités, des départements et des villes. Toutes les quatre sont construites sur l'imbrication des fonctions Excel Decaler et NbVal. C'est ainsi qu'elles sont capables d'adapter leurs hauteurs si de nouvelles données venaient à être ajoutées à la suite. Naturellement, nous exploiterons ces noms dans la construction des sources de données des listes déroulantes.

Liste des départements uniques et triés
La source de la première liste déroulante est la plus simple à construire. Elle doit énumérer tous les départements de la base de données, sans doublons et triés dans l'ordre croissant. Pour cela, nous n'avons qu'à répliquer la technique du volet précédent, en imbriquant les fonctions Trier et Unique.

• Cliquer sur le bouton Fermer du gestionnaire de noms,
• En bas de la fenêtre Excel, cliquer sur l'onglet Inter pour activer sa feuille,
• Sélectionner alors le premier département à extraire en cliquant sur sa cellule C4,
• Taper le symbole égal (=) pourinitier la syntaxe de la formule matricielle,
• Appeler la fonction de tri par son nom, suivi d'une parenthèse ouvrante, 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,
• Fermer la parenthèse de la fonction Trier,
• Puis, valider la formule matricielle par la touche Entrée du clavier,

Comme vous pouvez l'apprécier, les résultats se propagent automatiquement.



Nous obtenons bien la liste des départements uniques triés dans l'ordre alphabétique croissant.

Villes triées et uniques du département
Maintenant, nous devons travailler la source de données de la deuxième liste déroulante. Elle doit certes offrir les villes uniques triées, mais seulement pour le département choisi avec la première liste déroulante. Une condition doit donc être émise dans l'imbrication des fonctions Trier et Unique. La fonction qui permet d'émettre une condition sur l'ensemble d'une plage ou d'un tableau est forcément une fonction matricielle. Elle se nomme Filtre.

• Sélectionner la première ville filtrée à extraire en cliquant sur sa cellule D4,
• Taper le symbole égal (=) pour amorcer la syntaxe de la formule matricielle,
• Inscrire la fonction de tri suivie d'une parenthèse, soit : Trier(,
• Inscrire la fonction pour purger les doublons, suivie d'une parenthèse, soit : Unique(,

Jusque là rien ne change. Nous souhaitons extraire les valeurs uniques et triées. Mais cette double contrainte doit s'exercer sur les villes restantes, en fonction du choix du département. C'est là qu'entre en jeu la fonction Filtre.

• Inscrire le nom de cette fonction suivie d'une parenthèse, soit : Filtre(,
• Désigner la plage à filtrer par son nom, soit : Villes,
• Taper un point-virgule (;) pour passer dans l'argument du critère pour le filtre,

C'est une correspondance qui doit être observée sur la plage des départements, en fonction de celui choisi par l'utilisateur avec la première liste déroulante.

• Désigner la plage des départements par son nom, soit : Dep,
• Taper le symbole égal (=) pour annoncer la condition à observer,
• En bas de la fenêtre Excel, cliquer sur l'onglet Liaisons pour afficher sa feuille,
• Cliquer alors sur la cellule G4 du département choisi, ce qui donne : Liaisons!G4,
• Fermer la parenthèse de la fonction Filtre,
• Fermer la parenthèse de la fonction Unique,
• Puis, fermer la parenthèse de la fonction Trier,
• Enfin, valider la formule matricielle avec la touche Entrée du clavier,

A notre grande surprise, c'est le message d'erreur #CALC! qui surgit. Mais cette surprise n'en est pas une. Elle s'explique par le fait qu'aucun département et qu'aucune ville ne sont désignés dans la feuille Liaisons. Les listes déroulantes n'existent pas encore. En conséquence, l'extraction ne peut se faire. Mais nous pouvons la simuler.

• Revenir sur la feuille Liaisons,
• Puis, en cellule G4, inscrire par exemple le département suivant : 26-Drome,
• Après validation, revenir sur la feuille Inter,

Comme vous pouvez le voir, les extractions se sont aussitôt actualisées. Et ce sont bien toutes les villes de la Drôme, sans doublons et triées, qui sont proposées. Notre deuxième source de données est donc prête et ces fonctions matricielles sont décidément très puissantes.

Activités de la ville du département
Désormais, pour créer la source de données entonnoir des activités de la ville choisie dans le département mentionné, le principe est le même. Mais c'est une double condition qui doit être observée en deuxième argument de la fonction Filtre. Il s'agit précisément de recouper deux matrices conditionnelles.

• Cliquer sur la cellule D4 pour la sélectionner de nouveau,
• Dans sa barre de formule, copier la syntaxe jusqu'à la parenthèse de la fonction Filtre,
• Sortir de la barre de formule avec la touche Echap du clavier,
• Cliquer alors sur la cellule E4 de la première activité à extraire,
• Dans sa barre de formule, coller la syntaxe prélevée, soit : =TRIER(UNIQUE(FILTRE(,
• Désigner la plage des activités à filtrer par son nom, soit : act,
• Taper un point-virgule (;) pourpasser dans l'argument du critère,
• Ouvrir une parenthèse pour accueillir la première matrice conditionnelle,
• Désigner la plage des départements par son nom, soit : Dep,
• Taper le symbole égal (=) pour annoncer le premier critère à honorer,
• Sur la feuille Liaisons, cliquer sur la cellule G4, ce qui donne : Liaisons!G4,
• Fermer alors la parenthèse de la première matrice conditionnelle,

Nous venons d'émettre la première condition visant à isoler les activités du département choisi par l'utilisateur. Mais ces activités doivent aussi correspondre à la ville désignée par la suite. Nous devons donc recouper une seconde matrice conditionnelle.

• Taper le symbole de l'étoile (*) pour annoncer la seconde matrice conditionnelle à recouper,
• Ouvrir une nouvelle parenthèse pour accueillir cette dernière,
• Désigner la plage des villes par son nom, soit : Villes,
• Taper le symbole égal (=) pour annoncer la seconde condition à recouper avec la première,
• Sur la feuille Liaisons, cliquer sur la cellule G7 de la ville choisie, soit : Liaisons!G7,
• Fermer la parenthèse de la seconde matrice conditionnelle,
• Fermer la parenthèse de la fonction Filtre,
• Fermer la parenthèse de la fonction Unique,
• Fermer la parenthèse de la fonction Trier,
• Enfin, valider la formule matricielle avec la touche Entrée du clavier,

La sentence est la même que précédemment. Une erreur est générée. La raison est aussi la même. Aucune ville n'est encore mentionnée dans le département choisi. Donc, aucune extraction ne peut aboutir.

• En cellule G7 de la feuille Liaisons, taper par exemple la ville suivante : Valence,

Nous obtenons bien la source de données des activités uniques et triées, pour la ville choisie dans le département sélectionné.

Sources des listes déroulantes recoupées
Le plus dur est fait. Les sources existent désormais. Mais ne l'oublions pas, elles sont dynamiques. Leurs contenus et donc leurs hauteurs peuvent varier en fonction des choix réalisés en amont. C'est la raison pour laquelle, pour construire ces sources de données, nous devons exploiter une fois encore, les fonctions Excel Decaler et NbVal. Ce sont elles qui vont permettre d'adapter les contenus des listes déroulantes en fonction des hauteurs de ces plages.

• Revenir sur la feuille Liaisons et cliquer sur la cellule du département à choisir, soit 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,
• Déployer alors la liste déroulante de la zone Autoriser,
• Dans les propositions, choisir l'option Liste,
• Dès lors, cliquer dans la zone Source pour l'activer,
• Taper le symbole égal (=) pour amorcer la construction de la source de données,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Désigner le point de départ de la plage en cliquant sur la cellule C4 de la feuille Inter,

Nous obtenons la syntaxe suivante : Inter!$C$4.

• Taper trois points-virgules (;;;) pour sauter dans l'argument de la hauteur,
• Inscrire la fonction pour compter les cellules non vides, suivie d'une parenthèse, soit : NbVal(,
• Désigner l'intégralité potentielle des départements, en cliquant sur l'étiquette de la colonne C,

Nous obtenons la syntaxe suivante : Inter!$C:$C.

• Fermer la parenthèse de la fonction NbVal,
• Retrancher une unité à ce résultat, soit : -1,

En effet, dans le décompte, nous devons exclure la cellule du titre dans cette colonne. C'est ainsi que la source de données va s'ajuster strictement sur la hauteur des départements extraits.

• Fermer la parenthèse de la fonction Decaler,
• Valider la création de la source de données par le bouton Ok,

De retour sur la feuille Excel, si vous déployez la liste déroulante en cellule G4, vous constatez qu'elle propose en effet strictement tous les départements uniques triés dans l'ordre croissant. Si d'autres venaient à être ajoutés dans la base de données, grâce aux calculs intermédiaires impliquant les fonctions matricielles, ils seraient automatiquement intégrés.



Le principe est désormais le même en cellules G7 et G10 pour construire les sources dynamiques pour la liste déroulante dépendante des villes et pour celle, doublement dépendante des activités. Selon le même protocole, les sources de données suivantes doivent être construites :
En cellule G7 : =Decaler(Inter!$D$4;;; NbVal(Inter!$D:$D)-1),
En cellule G10 : =DECALER(Inter!$E$4;;; NBVAL(Inter!$E:$E)-1).

Désormais, si vous choisissez un département, la deuxième liste déroulante s'ajuste précisément pour ne proposer que les villes de ce département. Et si vous choisissez une ville, c'est la troisième liste déroulante qui s'adapte précisément pour ne proposer que les activités recensées dans la ville de ce département. Tout cela est rendu possible par nos calculs intermédiaires d'extraction dans la feuille Inter, exploitant les fonctions Trier, Unique et Filtrer.

De même, si vous créez une nouvelle activité en bas de la base de données, dans un nouveau département et donc dans une nouvelle ville, vous constatez que ces données sont dynamiquement intégrées dans les listes déroulantes.

Réinitialiser les listes dépendantes
Cependant et cela ne vous a sans doute pas échappé, une petite coquille subsiste. Au changement de département ou de ville, la liste dépendante garde la valeur précédemment choisie dans sa case. Pourtant, il ne s'agit plus d'une ville du nouveau département ou pas forcément d'une activité de la nouvelle ville.



Pour corriger ce défaut tout à fait logique, nous proposons d'enclencher un léger code VBA réagissant automatiquement aux changements de valeurs. Comme nous l'avons démontré dans une précédente astuce, nous proposons de le livrer ici afin de parachever proprement l'application pour articuler des listes déroulantes entre elles.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur sur la gauche, double cliquer sur l'élément Feuil2(Liaisons),
• En haut de la feuille de code, déployer la liste déroulante de gauche,
• Dans les propositions, choisir l'objet Worksheet,

Cette action a pour effet de créer la procédure événementielle Worksheet_SelectionChange. Ce n'est pas celle qui nous intéresse. Nous souhaitons intervenir au changement de valeur et non au changement de sélection.

• Dans la liste de droite, choisir l'évènement Change,

Cette action a pour effet de créer la procédure événementielle Worksheet_Change.

• Dans les bornes de cette procédure, inscrire le code VBA suivant :

Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Address = "$G$4" And Target.Count = 1) Then
Range("G7").Value = ""
Range("G10").Value = ""
ElseIf (Target.Address = "$G$7" And Target.Count = 1) Then
Range("G10").Value = ""
End If
End Sub

L'objet Target passé en paramètre de la procédure représente la cellule modifiée. Nous n'agissons que dans le cas des cellules G4 et G7. Dans le premier cas, nous effaçons les cases des deux autres listes déroulantes. Dans le second, nous effaçons la case de la dernière liste déroulante.

Maintenant, si vous revenez sur la feuille après avoir enregistré les modifications et que vous changez de département et/ou de ville, vous constatez que les cases dépendantes sont parfaitement réinitialisées.

Dans le prochain volet, nous exploiterons ces travaux pour produire l'extraction des idées de sorties correspondant à l'activité choisie dans la ville du département sélectionné.