Relier des listes déroulantes dynamiques par calculs Excel

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


Sujets que vous pourriez aussi aimer :


Lier des listes de données par Calcul Excel

Nous abordons le deuxième volet des formations Excel consistant à relier des listes déroulantes par le calcul, dans le but de fournir l'extraction des données, toujours grâce aux formules uniquement. Dans la formation précédente, nous avions réussi à reconstruire les sources de données purgées de leurs doublons.



Sur la base de ces données nettoyées de leurs redondances, nous avions construit des listes déroulantes dynamiques. Les fonctions Decaler et Nb nous avaient permis de définir des plages sources évolutives, pour qu'elles s'adaptent à leur contenu. En effet, nous souhaitons désormais que les données restituées sans doublons soient liées entre elles. Concrètement, si l'utilisateur choisit un département, seules les activités uniques de ce département doivent être proposées. Si l'utilisateur choisit ensuite une activité, seules les villes uniques disponibles pour l'activité dans le département, doivent être proposées. La longueur des données extraites varie donc en fonction de ces choix et nos listes s'adaptent.

Source et présentation de la problématique
Nous proposons de débuter les travaux sur la base des précédents, qui avaient permis d'extraire les données purgées de leurs doublons.

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

Nous retrouvons notre classeur constitué de deux feuilles : bd_sorties pour la base de données et listes pour l'extraction. Les informations des champs Département, Activité et Ville ont été extraites sans doublons, dans les colonnes I, J et K de la feuille listes. Cette extraction a été produite sur la base de deux calculs. Un calcul intermédiaire a été nécessaire dans la feuille bd_sorties pour repérer chaque donnée unique avec un numéro incrémenté. Un second calcul, dans la feuille listes, a effectué la recherche de ces repères numériques, pour restituer l'information unique du champ. Des listes déroulantes se nourrissent de ces informations, pour proposer les départements, activités et villes sans redondances, respectivement dans les cellules B5, C5 et D5. Ces listes ont été conçues sur des plages de cellules évolutives, grâce à l'imbrication des fonctions Excel Decaler et nb. Mais comme il n'existe pour l'instant aucun lien entre les sources extraites, les listes déroulantes sont complètement indépendantes à ce stade.



Comme l'illustre la capture ci-dessus en effet, rien n'empêche pour l'instant de choisir la ville de Cannes en même temps que le département de la Drôme. Or Cannes appartient au département des Alpes Maritimes (06). Elle ne doit donc pas être suggérée dans ce cas. Les listes déroulantes dépendantes doivent être remplies en fonction des choix réalisés dans les listes parentes.

Relier les extractions entre elles
Pour restituer les activités uniques en fonction du département choisi dans la première liste déroulante, nous allons devoir adapter le calcul intermédiaire dans la colonne E de la feuille bd_sorties. Pour l'instant il consiste au repérage numérique des activités uniques. Il doit désormais identifier les activités uniques, seulement pour le département désigné. Et nous devrons répliquer ce processus de calcul, pour la colonne G de la feuille bd_sorties afin d'identifier les villes sans doublons, pour le département et l'activité mentionnés en amont.

Pour ce faire, nous proposons de réaliser un nouveau calcul intermédiaire en colonne J de la feuille bd_sorties. Ce calcul consiste à repérer les activités correspondant au département choisi par leur numéro de ligne. Ces valeurs serviront alors à identifier de façon unique chaque activité pour le département, dans la plage qu'elles délimitent.

• Cliquer sur l'onglet bd_sorties en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule J2 et taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,
• Sélectionner la cellule du département en cours, soit la cellule D2,
• Taper le symbole = pour l'égalité du critère à vérifier,
• Cliquer alors sur l'onglet listes en bas de la fenêtre Excel pour activer sa feuille,
• Puis, cliquer sur la cellule du département choisi, soit B5,
• Enfoncer la touche F4 du clavier afin qu'elle soit toujours la référence dans le calcul répliqué,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir la formule suivante : LIGNE(D2), pour récupérer l'indice de ligne,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets ('') pour conserver la cellule vide lorsqu'il ne s'agit pas du bon département,
• Fermer la parenthèse de la fonction Si et valider le calcul par CTRL + Entrée,
• Double cliquer sur la poignée de la cellule pour répliquer son calcul sur tout le tableau,

La formule que nous avons tapée est la suivante :

=SI(D2=listes!$B$5; LIGNE(D2); '')



Comme l'illustre la capture ci-dessus, pour le département de la Drôme (26) sélectionné depuis la feuille listes, l'énumération des enregistrements commence à partir de la ligne 241. Il s'agit de la ligne à partir de laquelle nous devrons adapter le calcul consistant à repérer de façon unique les activités pour le département sélectionné.

De la même façon en colonne K, nous pouvons préparer le terrain. Il s'agit de faire ressortir les indices de lignes des enregistrements correspondant à la fois au département et à l'activité choisis depuis la feuille listes. Deux conditions doivent donc être vérifiées ensemble dans la zone de critère de la fonction Excel Si. Pour cela, nous devons exploiter la fonction exclusive ET qui impose de vérifier ensemble les critères énumérés.

• En cellule K2, saisir la formule suivante :

=si(et(D2=listes!$B$5; F2=listes!$C$5); ligne(H2);'')

• Double cliquer sur la poignée de la cellule pour répliquer le calcul sur tout le tableau,

Nous cherchons à identifier les enregistrements pour lesquels le département en cours (D2) est équivalent au département choisi (listes!$B$5) et pour lesquels, l'activité en cours (F2) est équivalente à l'activité choisie (listes!$C$5). Lorsque ces deux critères sont vérifiés ensemble, nous prélevons l'indice de ligne (ligne(H2)), comme point de référence du calcul d'identification à suivre. Dans le cas contraire, comme toujours, nous conservons la cellule vide ('').

En faisant défiler le tableau vers le bas, vous voyez l'énumération des lignes apparaître effectivement, à partir de la première activité correspondante, pour le département choisi depuis la liste déroulante.

Pour commencer par les activités à extraire de façon unique, toujours selon le département choisi, en colonne E, nous devons réaliser le décompte de ces dernières (Nb.si), sur une plage de cellules qui démarre à partir du premier indice de ligne précédemment référencé, et progressant avec le calcul. Mais pour spécifier une telle plage dans le calcul, nous devons explicitement désigner la cellule de départ. C'est pourquoi nous devons exploiter la fonction Excel Adresse, selon la syntaxe suivante :

=Adresse(num_ligne ; num_colonne)

Le numéro de ligne est renvoyé par le calcul précédent en colonne J. La colonne est connue, il s'agit de la F, soit d'indice 6. La référence retournée par la fonction Adresse, par exemple F241 est considérée comme un texte par Excel. Pour être exploitée en tant que telle, elle doit être convertie à l'aide de la fonction Indirect, comme suit :

=Indirect( Adresse(num_ligne ; num_colonne))

Par exemple, en cellules L241 et M241, si nous tapions les formules respectives :

=ADRESSE(J241;6)
=INDIRECT(ADRESSE(J241;6))

Nous obtiendrions les résultats suivants :

$F$241 et Camping/Location



Enfin, n'oublions pas qu'il faut faire varier la hauteur de la plage de recherche à l'aide de la fonction Excel Decaler.

• Sélectionner la cellule E2 de la feuille bd_sorties,
• Enfoncer la touche Suppr pour supprimer l'ancien calcul,
• Taper le symbole = pour débuter la nouvelle formule,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse soit, Si(,
• Puis taper le critère suivant en désignant la colonne du calcul précédent : J2<>'',

Si l'indice de ligne n'est pas repéré en colonne J, selon le calcul que nous avons bâti, nous en déduisons que le département en cours n'est pas le département sélectionné. En conséquence, le dénombrement (Nb.Si) pour le repérage des activités uniques, n'est pas à faire. Dans le cas contraire (répondant à ce critère), il doit être réalisé.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir de nouveau le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,
• Taper le nom de la fonction de dénombrement suivi d'une parenthèse, soit NB.SI(,
• Puis, ajouter la fonction Decaler comme suit : DECALER(,
• Ajouter de même la fonction Indirect comme suit : INDIRECT(,
• Puis, saisir le nom de la fonction retournant la référence d'une cellule, suivi d'une parenthèse, soit : Adresse(,
• Pour la référence de ligne, inscrire le calcul suivant : MIN(J:J),

La fonction Min appliquée sur la colonne J permet ainsi de retourner la première référence de ligne inscrite par le calcul précédent. Elle indique donc à partir de quel point commencer le dénombrement, soit le point de départ de la plage de cellules évolutive, désignée par la fonction Decaler.

• Taper un point-virgule pour passer à l'argument de colonne de la fonction Adresse,
• Saisir le chiffre 6 pour désigner la colonne F des activités,
• Puis, fermer la parenthèse de la fonction Adresse,
• Taper alors un point-virgule suivi du chiffre 1 pour indiquer à la fonction Indirect de retourner la référence exploitable de la cellule pour le calcul,
• Fermer la parenthèse de la fonction Indirect,

Nous sommes ainsi de retour dans la fonction Decaler pour laquelle, comme lors de la formation précédente, nous ne souhaitons réaliser aucun décalage, ni en ligne ni en colonne, par rapport à la cellule de référence.

• En conséquence, taper un point-virgule suivi du chiffre 0 suivi d'un autre point-virgule, suivi de nouveau du chiffre 0, ce qui donne : ;0;0,
• Taper un nouveau point-virgule pour passer dans l'argument de la hauteur à définir pour cette plage évolutive,

Ici, il s'agit de définir la hauteur de la plage de cellules à analyser. Il faut donc désigner les cellules précédentes, à partir de la borne marquée comme référence, soit LIGNE(F2)-MIN(J:J) + 1.

• Dans l'argument de la hauteur, saisir le calcul suivant : LIGNE(F2)-MIN(J:J) + 1,
• Fermer la parenthèse de la fonction Decaler,
• Puis taper un point-virgule pour passer dans l'argument du critère de la fonction Nb.Si,

Nous avons ajouté une unité à la hauteur de la plage de cellules que nous avons définie par le calcul. En effet, comme cette plage inclut la ligne en cours d'étude, pour la première par exemple, la hauteur conduirait au résultat 0, ce qui est impossible. Du coup, comme la cellule en cours d'analyse est elle-même incluse dans le dénombrement de la fonction Nb.Si, le critère à vérifier devient : >1 à la place de >0.

• Saisir le critère : >1,
• Puis taper un point-virgule pour passer dans la branche Alors de la seconde fonction Si,
• Saisir deux guillemets ('') pour indiquer de ne pas référencer l'activité lorsqu'elle l'a déjà été,
• Taper un point-virgule pour passer dans la branche Sinon de la seconde fonction Si,

Il s'agit désormais d'incrémenter les numéros de références, pour les valeurs uniques détectées, en fonction des précédents déjà calculés. Pour ce faire, nous exploitons une fois encore la fonction Excel Max.

• Saisir le nom de la fonction suivi d'une parenthèse, soit Max(,
• Cliquer sur la première cellule de la colonne, soit E1,
• Taper le symbole deux points (:), pour produire la plage E1:E1,
• Cliquer sur la première référence de cette plage,
• Puis, enfoncer deux fois la touche F4 du clavier pour la figer seulement en ligne, ce qui donne : E$1:E1,
• Fermer la parenthèse de la fonction Max,
• Puis ajouter une unité : +1, pour l'incrémentation,
• Fermer la parenthèse de la seconde fonction Si,
• Puis, taper un point-virgule pour passer dans la branche Sinon de la première fonction Si,
• Taper deux guillemets pour conserver la cellule vide lorsqu'il ne s'agit pas du bon département,
• Fermer la parenthèse de la première fonction Si,
• Valider la formule par le raccourci CTRL + Entrée,
• Puis double cliquer sur la poignée de la cellule pour répercuter le calcul sur la hauteur du tableau,

La formule que nous avons créée est la suivante :

=SI(J2<> ''; SI(NB.SI(DECALER(INDIRECT(ADRESSE(MIN(J:J); 6); 1); 0; 0; LIGNE(F2) - MIN(J:J) + 1); F2) > 1; ''; MAX(E$1:E1) + 1); '')



Dans l'exemple illustré par la capture ci-dessus, le département des Bouches du Rhône est sélectionné dans la feuille listes. En conséquence, c'est seulement à partir de ce point que les activités sont recensées pour référencer les valeurs uniques qui lui sont directement liées, soient les activités du département. La formule est parfaitement fonctionnelle mais relativement complexe. C'est à cette limite qu'il convient de se poser la question de l'emploi du code Visual Basic. Bien sûr, il requiert des notions de programmation, mais la formation VBA pour relier des listes entre elles, nous avait proposé une mise en oeuvre finalement plus simple.

Dans la colonne J de la feuille listes, le calcul permettant de reconstruire les activités uniques en fonction de ces repérages numériques, avait été préconçu lors de la formation précédente. Et comme la deuxième liste déroulante a été fabriquée sur cette plage de cellules restituées, les données se sont automatiquement mises à jour. Il en ira de même pour les villes lorsque la formule aura été adaptée en colonne G de la feuille bd_sorties.

• Pour ce faire, en G2, adapter la formule comme suit :

=SI(K2<> ''; SI(NB.SI(DECALER(INDIRECT(ADRESSE(MIN(K:K); 8); 1); 0;0; LIGNE(H2) - MIN(K:K) + 1); H2) > 1; ''; MAX(G$1:G1) + 1);'')

Le principe est strictement le même grâce au calcul intermédiaire réalisé en colonne K, avec la double condition. Donc, nous remplaçons les références à la colonne J par la colonne K. De même le repérage n'est plus effectué sur les activités, mais sur les villes. Donc nous remplaçons le chiffre 6 par le chiffre 8 pour désigner la huitième colonne, soit la colonne H. Et nous remplaçons toutes les références à la colonne F par la colonne H.



Dans l'exemple illustré par la capture ci-dessus, l'activité parc d'attraction et le département des Bouches du Rhône sont sélectionnés par le biais des deux premières listes déroulantes de la feuille listes. En conséquence, la formule repère parfaitement les villes concernées par ce double critère recoupé. Nous avons bien produit des listes de données uniques réagissant les unes en fonction des autres.

Listes déroulantes reliées en cascade
Comme nous l'avons évoqué précédemment, le travail consistant à créer des listes déroulantes sur des plages de cellules variantes, a déjà été fait en amont. De fait, nos listes déroulantes se sont automatiquement articulées les unes autour des autres, par l'intégration de ces deux nouvelles formules, pour les activités et les villes liées. C'est ce que nous proposons de constater.

• Cliquer sur l'onglet listes en bas de la fenêtre Excel pour activer sa feuille,
• Avec la première liste déroulante en B5, sélectionner le Var par exemple,

Instantanément vous remarquez que la liste des activités du département se recompose en colonne J.

• Avec la deuxième liste déroulante en C5, choisir l'activité Restaurant par exemple,

Comme précédemment, au choix de l'activité, vous constatez que la liste des villes associées se reconstitue instantanément en colonne K cette fois. Vous avez de même pu remarquer que la taille de la liste déroulante des activités était parfaitement réajustée à son contenu, grâce à l'emploi de la fonction Decaler dans la formation précédente.

• Pour terminer la simulation, sélectionner la ville Hyeres les palmiers à l'aide de la dernière liste déroulante,

Contrairement à ce que nous avions pu remarquer en début de formation, les villes proposées sont bien celles du département choisi en amont et plus spécifiquement, pour l'activité définie en deuxième critère. Nous avons donc réussi le tour de force de remplir des listes déroulantes en fonction de choix réalisés dans d'autres listes, uniquement par les calculs Excel. L'application s'articule dynamiquement et évolue automatiquement.



A ce stade en revanche, la zone d'extraction reste muette. Il s'agit de l'objectif du troisième volet de ces formations. Nous devrons être en mesure de proposer tous les résultats correspondant aux choix recoupés par les listes déroulantes et ce, de façon dynamique par les calculs Excel et donc, sans code Visual Basic.