Listes déroulantes Excel dépendantes et évolutives

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


Sujets que vous pourriez aussi aimer :


Relier des listes déroulantes au contenu évolutif

La problématique que nous proposons de résoudre au travers de cette formation Excel, ne consiste pas à proprement parler à établir la liaison entre deux listes déroulantes, pour les rendre dépendantes. Ces techniques nous les avons déjà apprises dans diverses formations avec différentes méthodes. Ici, l'enjeu consiste à conserver ces liaisons après mises à jour. Lorsque des listes déroulantes sont reliées entre elles par le jeu des plages nommées, si les sources évoluent, les nouvelles données ne sont pas intégrées. Et dans ce contexte précis, même l'emploi de la fonction Decaler, pour déplacer les bornes des plages sources en fonction du contenu, n'y change rien. Nous proposons donc d'apporter une solution toute simple, mais faut-il encore la connaître.



Dans l'exemple de l'application Excel finalisée ci-dessus, non seulement les listes déroulantes sont reliées entre elles, mais leur contenu continue d'évoluer si leurs sources évoluent elles-mêmes.

Sources et présentation de la problématique
Pour mettre en oeuvre ces précieuses techniques, nous proposons de débuter depuis un classeur source offrant des données à manipuler.

• Télécharger le classeur liaisons-listes-evolutives.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué des feuilles Sources et Lier_listes. La première énumère des villes rangées par département. La seconde propose justement de relier la liste déroulante des villes, en fonction du choix du département effectué dans la première liste déroulante. Vous l'avez compris, la seconde liste doit adapter son contenu en fonction de la première, mais pas seulement. Elle doit aussi évoluer si de nouvelles villes sont ajoutées après coup. Les plages sources pour la seconde liste déroulante sont situées à partir de la ligne 2 de la feuille Lier_listes, entre les colonnes F et J. Pour tester nos liaisons, nous piocherons de nouvelles villes dans la feuille Sources, afin de faire évoluer ces plages de données, et donc les listes qui en dépendent.

Listes déroulantes dépendantes
Nous devons donc construire deux listes déroulantes. La seconde, celle des villes, doit charger son contenu en fonction du département choisi dans la première. Nous proposons de nommer les plages source des villes avec les mêmes noms que ceux des départements. Ainsi, grâce à la fonction Excel Indirect, nous pourrons faire la correspondance entre ces listes, grâce à ces noms issus de la sélection réalisée pour le département. La formation Excel sur la recherche d'informations externes, nous avait appris à établir ce type de relation par les noms. Nous devons commencer par construire la liste déroulante des départements. Les données qui doivent la remplir sont situées en colonne B de la feuille Sources. Nous pourrions nous contenter de les sélectionner. Dans ce cas, la liste déroulante resterait bornée à une plage fixe. Pour rester cohérent à l'esprit de cette formation, nous proposons de bâtir une liste déroulante dynamique. Pour ce faire, nous devons exploiter la fonction Excel Decaler.

• Sélectionner la cellule B5 de la feuille Lier_listes,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Outils de données sur la droite du ruban, cliquer sur le bouton Validation de données,
• Dans la zone Autoriser de l'onglet Options de la boîte de dialogue qui s'affiche, choisir Liste,
• Cliquer dans la zone Source située en-dessous, pour activer sa saisie,
• Taper le symbole = pour débuter la formule de la plage dynamique,
• Saisir la fonction pour déplacer les bornes d'une plage suivie d'une parenthèse, soit : Decaler(,
• Cliquer sur l'onglet Sources en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner le premier département, soit la cellule B3, ce qui donne : Sources!$B$3,
• Taper un point-virgule (;) suivi du chiffre 0 pour n'effectuer aucun décalage en ligne,
• Taper un nouveau point-virgule (;) suivi du chiffre 0 pour n'effectuer aucun décalage en colonne,
• Taper un point-virgule (;) pour passer dans l'argument de la hauteur qui doit être variable,
• Saisir la fonction comptant les cellules non vides sur une plage, suivie d'une parenthèse, soit : NbVal(,
• Cliquer sur l'étiquette de la colonne B pour la désigner intégralement, ce qui donne : Sources!$B:$B,
• Fermer la parenthèse de la fonction NbVal,
• Puis, retrancher une unité (-1) pour exclure la cellule du titre dans le décompte,
• Fermer la parenthèse de la fonction Decaler,
• Valider cette source dynamique en cliquant sur le bouton Ok,

De retour sur la feuille Lier_listes, vous remarquez que la liste déroulante apparaît en lieu et place de la cellule désignée en amont. Elle est effectivement remplie de tous les départements situés en colonne B de la feuille Sources. Nous souhaitons vérifier que cette liste est bien dynamique, capable de considérer les nouvelles données ajoutées.

• Cliquer sur l'onglet Sources en bas de la fenêtre Excel pour activer sa feuille,
• A la suite des départements, en cellule B8, saisir Jura et valider la saisie,
• Revenir sur la feuille Lier_listes,
• Puis, cliquer sur la flèche de la liste déroulante en B5 pour visualiser son contenu,

Comme vous le remarquez, le nouveau département que nous venons d'ajouter, a été automatiquement intégré dans la liste déroulante. La formule que nous avons tapée a donc bien permis de faire évoluer sa source dynamiquement, au gré des mises à jour. Sa syntaxe est la suivante :

=DECALER(Sources!$B$3; 0; 0; NBVAL(Sources!$B:$B)-1)

En premier argument de la fonction Decaler, nous avons indiqué le point de départ de la liste déroulante, soit la cellule B3 de la feuille Sources. Avec les deux arguments suivants réglés à zéro, nous avons indiqué que nous ne souhaitions aucun décalage, ni en ligne, ni en colonne par rapport à ce point de départ. Le troisième argument est sans doute le plus important. Il définit la hauteur de la liste déroulante, par rapport à ce point de départ. Et grâce à la fonction NbVal, nous avons rendu cette hauteur dynamique. NbVal retourne le nombre de cellules non vides dans la colonne B. En lui retranchant une unité, elle renvoie le nombre de départements, soit la hauteur dynamique de la liste déroulante.



Comme nous n'avons pas prévu de villes pour le département du Jura, nous proposons de supprimer cette information.

• Dans la feuille Sources, supprimer le contenu de la cellule B8,

De retour sur la feuille Lier_listes, si vous déployer l'affichage de la liste déroulante, vous constatez qu'elle s'est de nouveau strictement adaptée à sa source.

Relier des listes par des plages nommées
Le contenu de la seconde liste déroulante, celle des villes, n'est donc pas défini à l'avance. Sa plage source dépend du choix réalisé depuis la première liste déroulante. Pour établir un lien naturel de dépendance, nous proposons de nommer chaque plage source, avec le nom du département correspondant. Ainsi, l'utilisateur en choisissant un département, fournit à Excel le nom de la plage à charger dans la seconde liste déroulante. Ces noms sont rappelés en entête de chaque colonne située entre F et J. Pour gagner du temps, il est conseiller de les copier tour à tour.

• Copier le nom du département des Alpes Maritimes,
• Sélectionner les villes de ce département, soit la plage de cellules F3:F8,
• Dans la zone Nom, en haut de la fenêtre et à gauche de la barre de formule, coller le nom du département,
• Valider ce nom en enfonçant la touche Entrée du clavier,

Nous devons procéder de la même façon pour attribuer un nom de liaison à chaque plage de cellules.

• Attribuer le nom Ardèche à la plage de cellules G3:G8,
• Attribuer le nom Bouches_du_Rhone à la plage de cellules H3:H8,
• Attribuer le nom Drome à la plage de cellules I3:I8,
• Attribuer le nom Isère à la plage de cellules J3:J8,

Comme en programmation, ces noms ne doivent pas proposer d'espace, sinon Excel les réfute. C'est pourquoi nous avons utilisé un Underscore (_) pour les matérialiser.



Maintenant que chaque plage de cellules est nommée respectivement avec le nom du département correspondant, il est temps d'établir la relation entre les deux listes déroulantes. Il suffit d'exploiter la fonction Excel Indirect et de la faire pointer sur la cellule de la première liste déroulante. Elle indiquera ainsi de récupérer le nom du département choisi, pour charger le contenu de la plage nommée indirectement de la même façon.

• Sélectionner la cellule de la seconde liste déroulante, soit la cellule D5,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Sur la droite du ruban, cliquer sur le bouton Validation de données,
• Dans la zone Autoriser de la boîte de dialogue qui suit, choisir Liste,
• Puis, cliquer dans la zone Source pour activer sa saisie,
• Taper le symbole = pour débuter la formule de liaison,
• Saisir la fonction Indirect suivie d'une parenthèse, soit Indirect(,
• Sélectionner la cellule de la première liste déroulante, soit B5,
• Fermer la parenthèse de la fonction Indirect,
• Valider la formule en cliquant sur le bouton Ok,
• Si un message d'erreur intervient, cliquer sur le bouton Oui pour forcer le passage,

En effet, lorsqu'aucune donnée n'est sélectionnée par le biais de la première liste déroulante, Excel ne peut trouver aucune correspondance de plage nommée.

• Dérouler la première liste déroulante et choisir le département des Bouches du Rhône,
• Déployer alors la seconde liste déroulante,

Comme vous le constatez, cette dernière propose en effet les villes du département choisi en amont. Et si vous changez de département, les villes s'adaptent de nouveau automatiquement. La liaison dynamique entre les deux listes déroulantes a parfaitement fonctionné. C'est bien la fonction Excel Indirect qui redéfinit la source de données, en fonction du nom de plage de cellules fourni. En revanche à ce stade et fort logiquement, la liste déroulante des villes n'évolue pas dynamiquement. Si vous ajoutez par exemple la ville de Vence en cellule F9 pour le département des Alpes Maritimes, elle ne figurera pas dans la seconde liste déroulante.



Les plages nommées telles que nous les avons construites raisonnent sur des bornes fixes.

Listes déroulantes dépendantes et évolutives
Nous connaissons l'intérêt de la fonction Excel Decaler. Nous l'avons exploitée pour faire varier dynamiquement la hauteur de la source de données de la première liste déroulante. Donc, nous pourrions être tenté de l'exploiter pour adapter chacune des plages nommées, afin d'intégrer les nouvelles valeurs ajoutées.

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

La boîte de dialogue Gestionnaire de noms apparaît. Elle énumère tous les noms que nous avons attribués à des plages de cellules pour ce classeur.

• Sélectionner le nom Alpes_Maritimes et cliquer sur le bouton Modifier,
• Dans la nouvelle boîte de dialogue qui apparaît, modifier sa source dans la zone Fait référence à, grâce à la formule suivante :

=Decaler(Lier_listes!$F$3; 0; 0; NbVal(Lier_listes!$F:$F)-1)

• Puis cliquer sur le bouton Ok pour la valider et revenir sur le Gestionnaire de noms,
• Cliquer sur le bouton Fermer pour revenir sur la feuille Lier_Listes,
• Choisir le département des Alpes Maritimes avec la première liste déroulante,
• Puis déployer l'affichage de la seconde liste pour visualiser ses villes,

Comme vous le remarquez, plus rien ne se produit. En guise d'amélioration, nous avons brisé le lien de dépendance entre les deux listes déroulantes, pour ce département. Pourtant la formule est bonne. Nous avons exploité l'imbrication des fonctions NbVal et Decaler à maintes reprises dans nos formations, pour faire évoluer les plages des sources de données. Et d'ailleurs cette formule est équivalente à celle que nous avons mise en oeuvre pour la liste des départements. Cela signifie que dans le cas de la dépendance par le jeu de la fonction Indirect, nous ne pouvons pas dynamiser les plages de cellules nommées.

Fort heureusement, cette formation a pour objectif de proposer la solution. Il s'agit d'une astuce dont la mise en oeuvre est finalement plus simple. La formation Excel sur la liaison dynamique aux sources externes, nous avait démontré qu'Excel créait lui-même des plages de cellules dynamiques. Et nous avions créé des noms calqués sur ces plages pour qu'ils évoluent en même temps que les sources. C'est exactement ce que nous proposons de reproduire ici. Mais pour ce faire, nous avons besoin dans un premier temps, de supprimer les noms que nous avons créés.

• Cliquer de nouveau sur le bouton Gestionnaire de noms du ruban Formules,
• Dans la boîte de dialogue qui apparaît, sélectionner le premier nom, soit : Alpes_Maritimes,
• Puis, cliquer sur le bouton Supprimer en haut de la boîte de dialogue,
• Et confirmer le message par Ok,
• De la même manière, supprimer tous les autres noms jusqu'à ce que la liste soit vide,
• Puis, cliquer sur le bouton Fermer du Gestionnaire de noms,

Nous sommes de retour sur la feuille. Toutes les relations de dépendance ont été rompues. Dans la mesure où les noms n'existent plus, la fonction Excel Indirect ne peut plus établir la correspondance. Nous rebâtirons ces mêmes noms sur la base de plages dynamiques. Et pour qu'Excel crée automatiquement ces plages, nous devons leur attribuer des mises en forme automatiques.

• Sélectionner la première plage des villes avec son entête, soit la plage de cellules F2:F8,
• Puis, cliquer sur le bouton Mettre sous forme de tableau dans le ruban Accueil,
• Dans la liste des visuels, cliquer sur l'un de la première rangée, par exemple le bleu clair,

Une petite boîte de dialogue surgit. Elle confirme la plage de cellules désignée pour la mise en forme automatique.

• Dans cette boîte de dialogue, cocher la case Mon tableau comporte des entêtes,
• Puis, valider par Ok,

Deux phénomènes visuels se produisent. Tout d'abord, vous constatez l'apparition d'une petite flèche dans l'entête de la plage de cellules concernée. Il s'agit d'un bouton de filtre dont nous n'avons pas besoin d'ailleurs. Et puis, en haut de la fenêtre Excel, un ruban contextuel nommé Création se rend disponible. Il permet de personnaliser certains paramètres pour cette mise en forme automatique.

• Dans ce ruban Création, décocher la case Bouton de filtre,

La petite flèche en entête disparaît aussitôt.

• Sélectionner uniquement les villes du département, soit la plage de cellules F3:F8,
• Consulter l'indication donnée dans la zone Nom en haut à gauche de la fenêtre Excel,

Excel a attribué un nom par défaut (Tableau1) à cette plage de cellules qu'il considère désormais comme un petit tableau dynamique. La première cellule de la plage n'est pas incluse, puisque nous l'avons explicitement définie comme l'entête.

Nous devons reproduire exactement les mêmes actions pour transformer les autres listes de villes, en plages de cellules dynamiques.

• Attribuer une mise en forme automatique à la plage G2:G8,
• Définir la première cellule comme l'entête et décocher la case Bouton de filtre,
• Puis, reproduire ces actions de manière à transformer les plages de cellules H2:H8, I2:I8 et J2:J8,

Toutes les plages de cellules ont été transformées en tableaux dynamiques. Mais les noms attribués par Excel ne peuvent pas être exploités directement dans les formules, pour les relations. En revanche, nous pouvons nous en inspirer. Il suffit d'attribuer un nom à chaque plage de cellules ainsi encapsulée par Excel. Ce nom sera alors calqué sur la plage dynamique. Si elle évolue, les bornes définies par le nom, évoluent aussi. Il s'agit de redéfinir les plages nommées que nous avons précédemment supprimées.

• Sélectionner la plage de cellules F3:F8,
• Dans la zone Nom, inscrire ou coller le nom du département, soit : Alpes_Maritimes,
• Puis, valider par la touche Entrée,

A validation, vous constatez que ce nouveau nom ne semble pas conservé au profit de celui attribué automatiquement par Excel. Mais il a bien été pris en compte. Nos proposons de le vérifier plus tard.

• Attribuer le nom Ardèche à la plage de cellules G3:G8,
• Attribuer le nom Bouches_du_Rhone à la plage de cellules H3:H8,
• Attribuer le nom Drome à la plage de cellules I3:I8,
• Enfin, attribuer le nom Isère à la plage de cellules J3:J8,
• Puis, cliquer sur le bouton Gestionnaire de noms dans le ruban Formules,

Comme vous le remarquez dans la boîte de dialogue qui apparaît, tous les noms sont listés, aussi bien ce que nous avons créés que ceux définis automatiquement par Excel. Et d'ailleurs, dans la colonne Fait référence à, le lien entre les deux plages nommées est établi. Comme les plages définies par Excel sont conçues pour évoluer dynamiquement, celles que nous avons créées, devraient s'en inspirer.

• Cliquer sur le bouton Fermer du Gestionnaire de noms pour revenir sur la feuille,

Nous devons maintenant tester le bon fonctionnement des relations de dépendance entre les listes déroulantes.

• A l'aide de la première liste déroulante, choisir le département de l'Isère par exemple,
• Puis, déployer l'affichage de la seconde liste déroulante,

Les villes de l'Isère apparaissent. Nous avons donc rétabli la liaison qui existait entre les deux. Si vous changez de département, le contenu de la seconde liste s'adapte pour proposer les villes correspondantes. Nous souhaitons maintenant faire évoluer ces sources de données.

• Sélectionner la cellule G9 pour ajouter une nouvelle ville au département de l'Ardèche,
• Saisir la ville suivante : Grospierres et valider,
• Avec la première liste déroulante, choisir le département de l'Ardèche,
• Puis, déployer l'affichage de la liste des villes,

Comme vous le constatez, cette nouvelle ville ajoutée après coup, a bien été intégrée par la liste déroulante. Nous avons donc réussi à construire des listes déroulantes dépendantes, grâce aux noms attribués aux plages de cellules sources. Et nous les avons rendues dynamiques et évolutives en calquant ces noms, sur ceux créés par Excel, par le biais de la mise en forme automatique.