Listes dépendantes de base de données Excel

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


Sujets que vous pourriez aussi aimer :


Choix dépendants sur base de données

Savoir créer des listes dépendantes est un enjeu important dans bien des cas. Leur mécanisme apporte ergonomie et efficacité dans les applications. Mais leur construction est bien souvent complexe. Ici, nous apportons une solution redoutable d'efficacité et de simplicité.



Sur l'exemple illustré par la capture, nous travaillons sur un fichier clients. Cette source de données est renseignée au fur et à mesure de la prospection. L'opérateur inscrit la civilité, le nom et le prénom. Puis, il indique le code postal. En fonction de cette information, une liste déroulante doit proposer toutes les villes attachées. Forcément donc, sur chaque ligne de la base de données, ses suggestions évoluent pour s'adapter au code postal renseigné en amont. Cette dépendance doit se matérialiser à l'aide d'une unique formule.

Source et présentation du concept
Pour réaliser ces travaux, nous proposons de récupérer cette source d'informations.

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

Le classeur ainsi récolté est muni de deux feuilles nommées respectivement Prospection et Villes. La feuille Prospection offre le tableau de saisie sur lequel il est question de créer le mécanisme de dépendance entre le choix de la ville et la saisie du code postal. La feuille Villes est la base de données de ces informations dépendantes. Pour les départements de la région PACA, elle recense toutes les villes associées à chaque code postal. Elle est pertinemment triée dans l'ordre croissant premièrement sur le code postal puis sur la ville. Toutes les villes d'un même code postal sont ainsi placées les unes en dessous des autres. Cette astuce est fondamentale pour repérer aisément la plage de cellules devant servir à charger la liste déroulante en fonction du code postal saisi, dans le tableau de la feuille Prospection.

Ajuster la plage de la liste déroulante
La fonction Excel Decaler est la solution avancée pour créer ces liens dynamiques et variables sur chaque ligne du tableau de saisie. En fonction du code postal inscrit, elle doit observer un décalage en ligne sur la source de données afin de pointer sur la première ville correspondante. Dès lors, elle doit adapter la hauteur variable de la plage en fonction du nombre de villes associées à ce même code postal. Pour calculer cette hauteur, nous exploiterons la fonction de dénombrement conditionnel. Il s'agit bien sûr de la fonction Nb.Si.

• Dans la zone Nom, en haut à gauche de la feuille Excel, saisir les coordonnées : G6:G100,
• Puis, valider par la touche Entrée du clavier,

De cette manière, nous sélectionnons une plage suffisamment haute sur la colonne des villes. Par définition, ce tableau de saisie est amené à évoluer. Nous allons ainsi construire des listes déroulantes qui seront prêtes à réagir aux nouveaux codes postaux renseignés.

• 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 plage de cellules variable,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• En bas de la fenêtre Excel, cliquer sur l'onglet Villes pour activer sa feuille,
• Désigner alors la première ville de la base de données en cliquant en C3, soit : Villes!$C$3,

Nous définissons donc le point de départ de la plage variable sur la première des villes. Nous allons devoir déplacer ce point de référence sur la première ville correspondant au code postal saisi. Pour cela, nous devons le rechercher dans sa colonne.

• Taper un point-virgule (;) pour passer dans l'argument du décalage en ligne,
• Inscrire la fonction de recherche de position, suivie d'une parenthèse, soit : Equiv(,
• En bas de la fenêtre Excel, cliquer sur l'onglet Prospection pour revenir sur sa feuille,
• Puis, désigner le premier code postal à chercher en cliquant sur sa cellule, soit : $F$6,
• Enfoncer trois fois la touche F4 du clavier pour la libérer complètement, soit : F6,

En effet, chaque liste déroulante doit se nourrir en fonction de l'information du code postal renseigné en regard.

• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• En bas de la fenêtre Excel, cliquer sur l'onglet Villes pour activer sa feuille,
• Désigner toute la colonne des codes postaux en cliquant sur son étiquette D : Villes!$D:$D,
• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, retrancher trois unités à ce résultat,

Les deux lignes au-dessus des codes postaux doivent bien évidemment être exclues du décompte. De plus, le point de départ est déjà placé sur le premier de ces codes postaux. Sa position ne doit donc pas être incluse dans le décompte.

• Taper deux points-virgules : ;; afin d'ignorer le décalage en colonne,

De fait, nous nous retrouvons dans l'argument de la hauteur variable de cette plage. Elle doit être calculée en fonction du nombre de villes attachées au code postal renseigné. Pour cela, nous allons exploiter la fonction de dénombrement conditionnel Nb.Si.

• Inscrire la fonction de dénombrement suivie d'une parenthèse, soit : Nb.Si(,
• Sur la feuille Villes, cliquer de nouveau sur l'étiquette de colonne des CP, soit : Villes!$D:$D,
• Taper un point-virgule (;) pour passer dans l'argument du critère à dénombrer,
• En bas de la fenêtre Excel, cliquer sur l'onglet Prospection pour revenir sur sa feuille,
• Désigner le premier code postal du tableau en cliquant sur sa cellule F6, soit : $F$6,
• Enfoncer trois fois la touche F4 du clavier pour la libérer complètement, soit : F6,
• 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 valider la création de la liste,

En déployant tour à tour les listes déroulantes de la colonne des villes, vous constatez qu'elles sont précisément adaptées et remplies en fonction du code postal inscrit dans la colonne voisine. Bien entendu, si vous ajoutez un nouveau prospect avec un code postal, une nouvelle liste déroulante auto-ajustée se génère automatiquement sur sa ligne. Attention néanmoins à bien formater l'intégralité des codes postaux en texte. C'est ainsi qu'ils sont retranscrits dans la source de données et que les recherches et comparaisons peuvent se faire.