Choix multiples dépendants et automatisés

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


Sujets que vous pourriez aussi aimer :


Choix multiples en cascade sur Bdd

Nous avons déjà avancé quelques techniques pour relier des listes déroulantes entre elles. L'une d'elles exploite des calculs de repérage pour produire les extractions affinées. Une autre fait intervenir les calculs matriciels. Une dernière met en jeu les plages nommées pour faire indirectement référence aux choix émis. Mais lorsque les informations offrent des liens évidents, il existe une autre méthode, encore plus simple.



Dans l'exemple illustré par la capture, nous travaillons sur une base de données des salariés d'une grande entreprise. Les services sont répartis sur plusieurs sites géographiquement différents. Pour décrire un salarié, au choix de son service, une liste déroulante dépendante s'ajuste pour proposer les sites liés. Et ce mécanisme est vrai sur toutes les lignes du tableau. Et nous allons le voir, une seule formule est nécessaire.

Source et présentation de la problématique
Pour les besoins des démonstrations, nous suggérons de tout d'abord récupérer cette source de données.

• Télécharger le classeur listes-reliees.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é,

Dans cette table des salariés, le service doit être indiqué en colonne D par le biais d'une première liste déroulante. C'est la raison de la présence des informations source en colonne K. L'information liée sur le site est attendue en colonne E. Elle doit être renseignée par le biais d'une seconde liste déroulante dépendant du choix émis avec la première. De fait, cette seconde liste déroulante est susceptible de proposer un contenu différent sur toutes les lignes du tableau. C'est la raison de la présence des informations de dépendance en colonnes H et I. Pour chaque service, sont énumérés les sites associés.

Première liste déroulante
La construction de la première liste est triviale. Son contenu est similaire pour tous les enregistrements de la base de données. Mais, les services sont susceptibles d'évoluer. Certains peuvent être ajoutés selon les choix stratégiques et la progression de l'entreprise. Nous devons donc construire une liste déroulante capable de détecter la borne variable de fin de la source d'information. Ainsi, son contenu s'ajustera dynamiquement. Pour cela, nous devons bien entendu exploiter la fonction Excel Decaler.

• Sélectionner tous les services de la colonne D, soit la plage de cellules D4:D21,
• 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 zone Autoriser de la boîte de dialogue qui suit, choisir l'option Liste,
• Puis, cliquer dans la zone Source du dessous pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe de la source de données,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Sur la feuille, cliquer en K4 pour désigner le premier service de la source de données,
• Taper alors trois points-virgules successifs, soit : ;;;,

En effet, le deuxième argument de la fonction Decaler concerne le décalage en ligne. Le troisième argument concerne le décalage en colonne. Or, par rapport à ce point de départ défini en K4, nous ne souhaitons observer aucun décalage. Nous souhaitons seulement ajuster la plage en hauteur de manière à intégrer toutes les informations à restituer. Et nous sommes désormais précisément positionnés dans cet argument de la hauteur variable à définir. Pour cela, nous pouvons exploiter la fonction Excel NbVal sur la colonne K. En comptant le nombre d'éléments qu'elle contient, elle renseignera sur la hauteur de la plage.

• Inscrire la fonction de décompte suivie d'une parenthèse, soit : NbVal(,
• Désigner la colonne K en cliquant sur son étiquette au-dessus de la feuille, soit : $K:$K,
• Fermer la parenthèse de la fonction NbVal,
• Puis, retrancher une unité à ce décompte, soit : -1,

En effet, cette colonne propose un titre qu'il faut exclure. Il ne figurera pas dans la liste. Ainsi, nous ajustons la hauteur de la liste déroulante au plus près.

• Fermer la parenthèse de la fonction Decaler,

La syntaxe que nous avons construite est donc la suivante :

=Decaler($K$4;;; NbVal($K:$K)-1)

Il est temps de la tester.

• Cliquer sur le bouton Ok pour valider la création de la liste déroulante ajustée,
• De retour sur la feuille, déployer la liste déroulante de l'un des services,

Comme vous pouvez le voir, chaque liste déroulante de cette colonne propose l'énumération de tous les services inscrits en colonne K.



Et si vous ajoutez une nouvelle information à la suite des données de cette colonne K, vous remarquez que la liste déroulante l'intègre aussitôt. Elle s'ajuste donc dynamiquement.

Liste déroulante dépendante
La seconde liste déroulante doit donc se nourrir en fonction du choix émis par le biais de la première liste. Comme l'évoquent les indications fournies en colonne I, les sites varient selon le service. L'idée consiste bien sûr à utiliser la fonction Excel Decaler en définissant tout d'abord le point de départ de la plage sur le premier site. Ensuite, un décalage doit être opéré vers le bas, donc en ligne, pour déplacer ce point de référence sur le premier site concordant. Pour cela, il suffit d'exercer une recherche du service dans la colonne H voisine, grâce à la fonction Equiv. Elle retournera la position à ajuster. Enfin, nous devons adapter la hauteur de la liste en fonction du nombre de sites référencés pour le service. Nous pouvons les compter grâce à la fonction Nb.Si.

• Sélectionner tous les sites du tableau, soit la plage de cellules E4:E21,
• Dans le ruban Données, cliquer sur le bouton Validation des données,
• Dans la section Autoriser de la boîte de dialogue, choisir l'option Liste,
• Puis, cliquer dans la zone Source du dessous pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Désigner le premier site de la source de données en cliquant sur sa cellule I4,
• Taper un point-virgule (;) pour passer dans l'argument du décalage en ligne,
• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• Désigner le service à chercher en cliquant sur la cellule D4, ce qui donne : $D$4,
• Enfoncer trois fois la touche F4 du clavier pour la libérer complètement, soit : D4,

De cette manière, chaque liste déroulante dépendante fera référence à son service inscrit en regard.

• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Désigner la colonne des services en cliquant sur son étiquette H, ce qui donne : $F:$H,

En raison de la fusion de cellules en première ligne, c'est la plage F:H qui est retranscrite.

• Remplacer la lettre F par la lettre H,
• Puis, cliquer à la fin de la syntaxe pour y replacer le point d'insertion,
• Taper un point-virgule suivi du chiffre 0, soit : ;0, pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Retrancher quatre unités à ce résultat, soit : -4,

Nous devons en effet ignorer les trois premières lignes de la colonne dans le décompte. Elles ne font pas partie des renseignements recherchés. La quatrième est due au fait que le point d'insertion est déjà placé sur l'une des données de la source. Elle est déjà comptabilisée. Donc nous la retranchons pour obtenir un décalage parfaitement ajusté.

• Taper deux points-virgules successifs, soit : ;;,

Ainsi, nous ignorons le paramètre du décalage en colonne et nous nous retrouvons propulsés dans l'argument de la hauteur variable. Celle-ci dépend du nombre de sites associés à un service.

• Inscrire la fonction de dénombrement conditionnel, suivie d'une parenthèse, soit : Nb.Si(,
• Cliquer de nouveau sur l'étiquette de la colonne H, ce qui donne : $F:$H,
• Comme précédemment, remplacer la lettre F par la lettre H,
• Puis, cliquer à la fin de la syntaxe pour y replacer le point d'insertion,

Nous désignons ainsi la colonne dans laquelle nous souhaitons dénombrer un critère. Plus précisément il s'agit d'une donnée, celle du service.

• Taper un point-virgule (;) pour passer dans l'argument du critère de la fonction Nb.Si,
• Désigner le premier service du tableau de données en cliquant sur D4, ce qui donne : $D$4,
• Pour la libérer complètement comme précédemment, enfoncer trois fois la touche F4,
• Fermer la parenthèse de la fonction Nb.Si,
• Puis, fermer la parenthèse de la fonction Decaler,
• Enfin, cliquer sur le bouton Ok de la boîte de dialogue pour créer la liste déroulante,

En déployant quelques listes déroulantes des sites, vous constatez que les propositions sont parfaitement ajustées au service mentionné dans la colonne voisine. Chacune de ces listes est également parfaitement calibrée en hauteur. Maintenant, nous allons démontrer que ces liens de dépendance sont évolutifs.

• En cellule K9, ajouter le service suivant,
• Sur la droite de la feuille, sélectionner la plage de cellules O5:P8,

Elle énumère les sites associés à ce nouveau service.

• Copier cette plage à l'aide du raccourci clavier CTRL + C,
• Sélectionner alors la cellule H22 en dessous de la source de données,
• Coller cette plage avec le raccourci clavier CTRL + V,
• Dans le tableau de données, sélectionner par exemple la cellule D8,
• Avec la liste déroulante, choisir le nouveau service : Ingénierie,
• Puis, déployer la liste déroulante associée en cellule E8,

Comme vous pouvez le voir, la liste déroulante dépendante a parfaitement ajusté ses propositions au choix du nouveau service, fraîchement ajouté dans la source d'information.



Nous avons donc réussi à créer des listes déroulantes reliées entre elles et ce, pour toutes les lignes du tableau, grâce à la fonction Excel Decaler.