Extractions Excel sur trois listes déroulantes liées

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


Sujets que vous pourriez aussi aimer :


Extraire sur 3 listes déroulantes recoupées

Dans la formation précédente, nous avons appris à articuler trois listes déroulantes entre elles, par formules Excel, relativement simples au demeurant. Sur ce socle, nous souhaitons maintenant produire l'extraction des informations de la base de données, en corrélation avec les choix entonnoirs émis par le biais de ces listes déroulantes articulées en cascade.



C'est ce qu'illustre la solution finalisée présentée par la capture. Sur la droite de la grille d'extraction, l'utilisateur actionne une première liste déroulante pour choisir un département. Aussitôt, toutes les activités de sorties de ce département sont extraites dans la grille au centre de l'écran. Dès lors, il affine son choix en déployant la deuxième liste déroulante pour désigner une ville dans ce département. Instantanément, les propositions se restreignent pour n'offrir plus que les activités de sorties dans cette ville et pour ce département. S'il le souhaite, il peut encore affiner les résultats en actionnant la troisième liste déroulante pour choisir une activité recensée dans la ville de ce département. Et comme précédemment, les extractions se mettent aussitôt à jour pour faciliter les choix.

Classeur Excel à télécharger
Pour aboutir cette solution, nous devons axer le développement sur la base des travaux précédents ayant permis d'articuler ces trois listes déroulantes entre elles.

• Télécharger le fichier extraire-sur-trois-listes-liees.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 trouvons bien la grille d'extraction entre les colonnes B et E. Les listes déroulantes articulées entre elles, grâce aux travaux précédents, sont respectivement placées en cellules G4, G7 et G10.



Si vous affichez le gestionnaire de noms en cliquant sur le bouton du même nom dans le ruban Formules, vous constatez que quelques plages stratégiques ont été nommées, de manière à les rendre dynamiques.

La plage nommée table représente l'intégralité des données de la base de données placée dans la feuille intitulée BDD. C'est à partir de cette source d'informations que nous allons produire les extractions croisées. Les trois autres plages représentent respectivement la colonne des activités, celle des départements et enfin celle des villes. Toutes les quatre exploitent les fonctions Excel Decaler et NbVal pour adapter dynamiquement leurs hauteurs au potentiel nouveau contenu ajouté à la suite de la base de données.

Filtrer sur le département
Pour bien comprendre la formule matricielle finale à bâtir, nous proposons de décomposer la construction de sa syntaxe en trois étapes. Dans un premier temps, nous souhaitons extraire uniquement les activités de sorties répondant favorablement au choix du département par l'utilisateur, par le biais de la première liste déroulante en G4. Et comme nous l'avons appris à l'occasion du volet précédent, la fonction matricielle qui permet d'extraire toutes les données d'une plage honorant un critère, est la fonction Filtre.

• Cliquer sur le bouton Fermer du gestionnaire de noms,
• En G4, choisir un département avec la liste déroulante, par exemple : 26-Drome,
• Cliquer alors en B4 pour désigner la première cellule de l'extraction,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Puis, inscrire la fonction de gestion des anomalies, suivie d'une parenthèse, soit : SiErreur(,

En effet, nous souhaitons éviter les messages d'erreur lorsque la fonction d'extraction échoue. Ce cas se produit inévitablement quand l'utilisateur n'a par exemple émis aucun choix avec les listes déroulantes.

• Dès lors, inscrire la fonction matricielle d'extraction, suivie d'une parenthèse, soit : Filtre(,
• Désigner la base de données à analyser par son nom, soit : table,
• Taper un point-virgule (;) pour passer dans l'argument du critère à observer sur cette source,

Nous le savons, la correspondance des lignes à extraire doit s'établir sur le département dans un premier temps. Mais comme nous recouperons d'autres critères avec celui-ci par la suite, nous avons besoin d'encapsuler cette première matrice conditionnelle dans des parenthèses.

• Ouvrir une parenthèse pour accueillir la matrice conditionnelle,
• Désigner la colonne des départements par son nom, soit : dep,
• Taper le symbole égal (=) pour annoncer le critère à honorer,
• Dès lors, désigner le département choisi par l'utilisateur en cliquant sur sa cellule G4,
• Fermer la parenthèse de la matrice conditionnelle,
• Puis, fermer la parenthèse de la fonction Filtre,
• Dès lors, taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire deux guillemets ("") pour conserver les cellules vides en cas d'anomalie,
• Fermer la parenthèse de la fonction SiErreur,
• Enfin, valider simplement la formule matricielle avec la touche Entrée du clavier,

Comme vous pouvez l'apprécier, grâce à la nature matricielle de cette fonction Filtre, les résultats de l'extraction se répandent naturellement en lignes et en colonnes. De plus et pour la même raison, il n'a pas été nécessaire de dégainer les références absolues sur la cellule G4. Une fonction matricielle confronte naturellement toutes les cellules des matrices impliquées dans le raisonnement. Bien sûr et enfin, ces données extraites sont parfaitement filtrées sur les activités de la Drôme, comme l'avait demandé l'utilisateur.

Filtrer sur le département et la ville
Pour permettre à l'utilisateur des sélections plus précises, nous devons maintenant recouper la matrice conditionnelle des départements avec celle des villes. Vous l'avez compris, l'adaptation s'annonce triviale.

• Cliquer de nouveau sur la première cellule d'extraction, soit : B4,
• Dans la barre de formule, cliquer juste avant la parenthèse fermante de la fonction Filtre,

Nous sommes ainsi placés juste après la parenthèse fermante de la matrice conditionnelle.

• Taper le symbole étoile (*) pour annoncer la matrice conditionnelle à recouper,
• Ouvrir une parenthèse pour accueillir cette nouvelle matrice,
• Désigner la colonne des villes par son nom, soit : villes,
• Taper le symbole égal (=) pour annoncer la condition à observer sur cette plage,
• Désigner alors la ville choisie par l'utilisateur en cliquant sur sa cellule G7,
• Dès lors, fermer la parenthèse de cette deuxième matrice conditionnelle,
• Puis, comme précédemment, valider la formule matricielle avec la touche Entrée du clavier,

Cette fois, plus aucune extraction n'est produite. La raison est simple. Nous n'avons encore formulé aucun choix sur la ville.

• Avec la liste déroulante en G7, choisir par exemple la ville de Valence,

L'extraction chirurgicale s'opère comme par magie. Seules subsistent les activités de la ville de Valence dans le département de la Drôme. En conséquence, la liste est moins volumineuse que précédemment. Elle est plus précise.

Filtrer sur le département, la ville et l'activité
C'est maintenant une troisième matrice conditionnelle que nous devons recouper avec les précédentes, de manière à inclure le filtre sur le choix de l'activité. Le principe est strictement identique.

• Cliquer de nouveau sur la première cellule d'extraction en B4,
• Dans la barre de formule, cliquer juste avant la parenthèse fermante de la fonction Filtre,

Nous sommes ainsi placés juste après la parenthèse fermante de la deuxième matrice conditionnelle.

• Taper le symbole de l'étoile (*) pour annoncer la troisième condition à recouper,
• Ouvrir une nouvelle parenthèse pour accueillir la dernière matrice conditionnelle,
• Désigner la colonne des activités par son nom, soit : act,
• Taper le symbole égal (=) pour annoncer le critère à respecter,
• Puis, cliquer sur la cellule G10 du choix de l'activité,
• Dès lors, fermer la parenthèse de la troisième matrice conditionnelle,
• Enfin, valider la formule matricielle par la touche Entrée du clavier,

Les symptômes sont les mêmes que précédemment. Aucune activité n'est extraite. Par contre, si vous choisissez une activité avec la liste déroulante en cellule G10, comme Hôtel/Restaurant, aussitôt l'extraction se réalise.



Par ces recoupements de conditions en cascade, nous obtenons une liste de choix encore plus affinée que la précédente.

Amorcer ou désamorcer les critères
Cependant, il manque un petit quelque chose pour que cette application soit totalement ergonomique. Rien n'empêche l'utilisateur de ne pas vouloir restreindre ses choix, ou de ne les restreindre que partiellement. Pour une application plus souple, des résultats doivent être livrés sur le département seul, sur le recoupement du département et de la ville et encore sur le recoupement des trois listes. Pour cela, il s'agit d'engager ces autres matrices conditionnelles sur critère, grâce à la fonction Excel Si.

• En cellule B4, adapter la précédente formule matricielle comme suit :

=SIERREUR(FILTRE(table;(dep=G4)*(SI(G7<>""; villes=G7; 1))*(SI(G10<>""; act=G10; 1))); "")

Très simplement, si la ville est désignée (G7<>""), nous engageons la matrice conditionnelle (villes=G7), sinon nous inscrivons le booléen 1. En effet, la première matrice conditionnelle répond elle-même par une matrice de booléens repérant les positions des résultats concordants. Pour ne pas les annihiler, nous confirmons le marquage de ces positions. Le principe est alors strictement identique pour le critère combiné sur les activités.

Désormais, si vous choisissez seulement un département, vous obtenez bien toute la liste des activités dans ce département. Si vous combinez avec une ville, l'extraction s'affine pour n'offrir que les idées dans la ville du département. Enfin, si vous affinez avec l'activité, vous obtenez bien une extraction chirurgicale sur les trois critères recoupés.