Extraire selon des critères recoupés 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 :


Extraire les données de choix multiples par calculs Excel

Dans ce troisième volet, nous souhaitons finaliser l'application permettant d'extraire les données de bases Excel, selon des choix recoupés en cascade. Dans les deux précédentes étapes, nous avions premièrement extrait les données sources purgées de leurs doublons, et deuxièmement, nous avions relié ces sources entre elles. De fait, nous avions conçu des listes déroulantes dont les sélections dépendaient les unes des autres.



Comme l'illustre la capture ci-dessus de l'application Excel finalisée, l'utilisateur réalise trois choix en cascade par le biais de listes déroulantes. Il définit tout d'abord un département proposé dans la première des listes. La source de la deuxième liste déroulante se génère alors en fonction de ce premier choix. Il définit donc une activité de sortie et les propositions de villes se génèrent automatiquement en fonction de ce deuxième choix. A la sélection d'une ville, les enregistrements correspondant aux trois critères recoupés, sont instantanément extraits dans la zone d'extraction, située juste en dessous. Et à l'instar des deux précédentes étapes, nous souhaitons réaliser cette sélection de données par calculs Excel, sans l'intervention du code Visual Basic.

Source et problématique
Nous devons récupérer les travaux précédents qui ont permis d'articuler ces listes déroulantes, sur la source de données. Ainsi, nous pourrons concentrer nos efforts sur les formules permettant de réaliser les requêtes d'extraction.

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

Nous arrivons sur un classeur constitué de deux feuilles. La feuille bd_sorties est celle de la base de données listant les idées de sorties par département, activité et ville. C'est sur ces trois champs que des calculs ont été bâtis lors des formations précédentes afin de générer des listes déroulantes dynamiquement liées. Ces listes se situent dans la feuille de l'application nommée listes.

• En bas de la fenêtre Excel, cliquer sur l'onglet listes pour activer sa feuille,

Ces listes déroulantes sont proposées en ligne 5 des colonnes B, C et D. Elles ont été conçues sur des plages de cellules dynamiques pour s'adapter aux variations des sources de données I, J et K, grâce à la fonction Decaler. Ces sources sont issues de calculs permettant l'extraction des données à la fois reliées et purgées de leurs doublons.

• A l'aide de la première liste déroulante en B5, sélectionner le département 83-Var,
• A l'aide de la deuxième liste déroulante en C5, sélectionner l'activité Hôtel/Restaurant,
• Enfin, avec la dernière liste en D5, sélectionner la ville de Toulon,

Pas d'extraction pour l'instant puisqu'il s'agit précisément de la solution à mettre en place dans ce troisième volet. En revanche, à chaque choix dans une liste déroulante, vous avez pu remarquer les sources de données se reconstituer automatiquement, sur la partie droite de la feuille. Par le jeu des calculs, les informations sont extraites dynamiquement pour restreindre les propositions selon les sélections réalisées en amont. C'est ainsi que les listes remplies de ces données s'articulent les unes par rapport aux autres.

Repérer les enregistrements vérifiant les conditions
Pour simplifier la formule d'extraction des données, nous proposons de bâtir un calcul intermédiaire dans la colonne L de la feuille bd_sorties. Ce calcul doit marquer l'enregistrement en cours d'une valeur numérique incrémentée, s'il satisfait aux trois conditions recoupées des listes déroulantes. Pour l'incrémentation automatique dans la colonne du calcul, nous exploiterons l'astuce de la fonction Max sur une plage de cellules, dont la borne inférieure se déplace en même temps que la formule est répliquée. En somme, nous reproduisons des techniques identiques à celles des deux volets précédents. Pour vérifier plusieurs conditions à la fois, nous devons exploiter la fonction Excel ET dans la zone de critère de la fonction conditionnelle Si.

• Cliquer sur l'onglet bd_sorties en bas de la fenêtre Excel pour activer sa feuille,
• Puis, sélectionner la cellule L2 sur la droite du tableau,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,
• Saisir la fonction pour énumérer les conditions suivi d'une parenthèse, soit ET(,
• Sélectionner le département de la ligne en cours pour le premier critère à vérifier, soit D2,
• Taper le symbole = pour enclencher l'égalité à vérifier,
• Cliquer sur l'onglet listes en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule du département, soit B5,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : listes!$B$5,
• Taper un point-virgule (;) pour passer au deuxième critère à recouper,
• Sur la feuille bd_sorties, sélectionner la cellule F2 de l'activité,
• Taper le symbole = puis sélectionner la cellule C5 de la feuille listes pour l'activité,
• Enfoncer la touche F4 du clavier pour figer cette dernière dans le calcul,
• Taper un point-virgule (;) pour passer à la troisième condition à croiser,
• Sélectionner alors la cellule H2 de la feuille bd_sorties pour la ville en cours,
• Taper le symbole = et désigner la cellule D5 de la feuille listes pour la ville correspondante,
• Enfoncer la touche F4 pour la figer et fermer la parenthèse de la fonction Et,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir la fonction retournant le nombre le plus grand suivi d'une parenthèse, soit Max(,
• Sélectionner la toute première cellule de la colonne L, soit L1,
• Taper le symbole deux points (:) pour générer la plage de départ, soit L1:L1,
• Dans la formule, cliquer sur la première des deux références,
• Puis enfoncer la touche F4 du clavier pout la figer, ce qui donne : $L$1:L1,
• Fermer la parenthèse de la fonction Max,
• Taper le symbole + suivi du chiffre 1 pour l'incrément,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets ('') pour conserver la cellule vide lorsque les conditions recoupées par la fonction ET, ne sont pas satisfaites,
• Fermer la parenthèse de la fonction Si,
• Valider le calcul par le raccourci clavier CTRL + Entrée pour conserver la cellule active ,
• Double cliquer sur la poignée de la cellule pour répliquer la formule sur la hauteur du tableau,

Selon les choix que nous avons réalisés précédemment, nous sommes obligés d'atteindre la ligne 889 de la feuille bd_sorties, pour trouver les premiers enregistrements marqués par la formule. Ils correspondent en effet aux trois Hôtel/Restaurant pour la ville de Toulon située dans le Var. Ce sont ces numéros que nous devons exploiter pour produire l'extraction multicritère correspondante, dans la feuille listes. La formule que nous avons construite est la suivante :

=SI(ET(D2=listes!$B$5; F2=listes!$C$5; H2=listes!$D$5); MAX($L$1:L1) + 1; '')

Notez que nous avons supprimé le nom de la feuille bd_sorties en préfixe des cellules D2, F2 et H2. Excel nous l'impose en effet à chaque fois que nous basculons d'une feuille à une autre pendant la construction de la formule. Comme cette dernière est située dans la feuille bd_sorties, il n'est pas nécessaire de conserver sa référence, pour plus de clarté.

Extraction des données selon les conditions recoupées
Nous devons donc réaliser la recherche de ces numéros incrémentés pour trouver la ligne des enregistrements concordants. L'astuce, comme dans les formations précédentes, consiste à exploiter la fonction Excel Ligne qui permet de retourner l'indice de ligne d'une cellule désignée. En désignant la cellule A1 pour le premier calcul (Ligne(A1)), nous réaliserons ainsi la recherche du chiffre 1 qui s'auto-incrémentera au fur et à mesure que le calcul est répliqué sur les lignes du dessous. Nous ne pouvons pas exploiter la fonction Excel RechercheV. Cette dernière impose en effet que l'élément à chercher se situe en première colonne du tableau de référence. Or, la colonne L est justement la dernière. Nous proposons de palier le problème avec l'imbrication des fonctions Index et Equiv, telles que nous les avions présentées dans la formation Excel pour extraire des données. Leurs syntaxes sont les suivantes :

=INDEX(Base_de_données ; numero_de_ligne ; numero_de_colonne)
=EQUIV(Valeur_cherchée ; Colonne_de_recherche ; 0)

La fonction Excel Index permet donc d'extraire une information de base de données selon son indice de ligne et selon son indice de colonne. La colonne est fixe. Pour l'extraction de la raison sociale par exemple, il s'agit de la deuxième colonne, soit le chiffre 2 pour définir son argument. Le numéro de ligne en revanche est variable. Il peut être trouvé grâce au calcul intermédiaire que nous avons produit en colonne L de la feuille bd_sorties. Et c'est la fonction Equiv qui permet justement de réaliser la recherche de ce numéro dans la colonne désignée, afin de retourner l'indice de ligne en question.

Pour simplifier le calcul, avant de le débuter, nous proposons d'attribuer un nom à la plage de cellules de la base de données. Elle sera ainsi plus simple à désigner dans la formule d'extraction.

• Cliquer sur l'onglet bd_sorties en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule A1,
• Réaliser la combinaison de touches CTRL + MAJ + FIN pour étendre la sélection jusqu'à la dernière cellule active de la feuille,
• Dans la zone Nom, en haut à gauche de la feuille, saisir bds et valider par la touche Entrée,

Dans les calculs à venir, la base de données, donc le tableau de recherche, pourra être désigné par simple saisie de son nom. La combinaison de touches que nous avons exploitée pour étendre la sélection est démontrée dans la formation sur les raccourcis claviers Excel. Si elle ne vous convient pas, rien ne vous empêche de sélectionner à la souris.

Il est temps de construire la formule d'extraction des données.

• Cliquer sur l'onglet listes en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la première cellule de l'extraction à produire, soit B11,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction de gestion d'erreur suivi d'une parenthèse, soit SiErreur(,
• Saisir la fonction d'extraction par ligne et colonne suivi d'une parenthèse, soit Index(,
• Saisir le texte bds pour désigner la base de données précédemment nommée,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Saisir la fonction de recherche retournant la ligne suivi d'une parenthèse, soit Equiv(,
• Saisir le nom de la fonction retournant l'indice de ligne suivi d'une parenthèse, soit Ligne(,
• Cliquer sur la cellule A1 pour restituer mécaniquement les numéros à retrouver,
• Fermer la parenthèse de la fonction Ligne,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Cliquer sur l'onglet bd_sorties en bas de la fenêtre Excel pour activer sa feuille,
• Cliquer alors sur l'étiquette de la colonne L, ce qui donne : bd_sorties!L:L,
• Taper un point-virgule (;) pour passer dans le dernier argument de la fonction Equiv,
• Saisir le chiffre 0 pour commander une recherche selon une correspondance exacte,
• Fermer la parenthèse de la fonction Equiv,
• Taper un point-virgule pour passer dans l'argument de l'indice de colonne de la fonction Index,
• Saisir le chiffre 2 pour désigner la colonne de la raison sociale,
• Fermer la parenthèse de la fonction Index,
• Taper un point-virgule pour passer dans le second argument de la fonction SiErreur,
• Saisir deux guillemets ('') pour conserver la cellule vide en cas d'erreur,
• Fermer la parenthèse de la fonction SiErreur,
• Valider la formule par le raccourci CTRL + Entrée pour conserver la cellule active,
• Tirer la poignée du calcul jusqu'à la ligne 24 par exemple,

Comme nous l'avions constaté lors de la vérification du calcul intermédiaire, trois enregistrements sont effectivement extraits. Il s'agit bien des données vérifiant les trois conditions recoupées par les listes déroulantes en cascade, à savoir les Hôtel/Restaurant pour la ville de Toulon située dans le Var. Les données sont extraites instantanément, sans doublons, listées les unes à la suite des autres, grâce à la recherche sur un numéro incrémenté. Une fois encore, nous parvenons à réaliser cette prouesse sans faire appel au code VBA Excel. Et vous en conviendrez, la formule est relativement simple :

=SIERREUR(INDEX(bds; EQUIV(LIGNE(A1); bd_sorties!L:L; 0); 2); '')

• Répliquer ce calcul sur les colonnes D, E, F et G de la zone d'extraction en adaptant la valeur du paramètre de l'indice de ligne, pour la fonction Index,
• Puis tirer la poignée pour chaque colonne jusqu'à la ligne 24,

Le chiffre 2 du troisième argument de la fonction Index devient respectivement :

• Le chiffre 4 pour le département,
• Le chiffre 6 pour l'activité,
• Le chiffre 8 pour la ville,
• Et le chiffre 1 pour l'identifiant (Id),

Chaque calcul ainsi répliqué fournit le détail de champ, extrait de la base de données, pour l'enregistrement correspondant. Vous notez la construction automatique de bordures d'encadrement, dès lors qu'un contenu est détecté.

• Cliquer dans l'une des cellules de la zone d'extraction, par exemple B11,
• Dans le ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Puis, dans la liste, choisir Gérer les règles,
• Dans la boîte de dialogue qui suit, cliquer sur le bouton Modifier la règle,

Comme l'indique la formule illustrée par la capture ci-dessus, lorsque la cellule en cours est détectée comme non vide, une bordure lui est attribuée. Il s'agit donc d'un format qui réagit au contenu de la cellule. C'est la raison pour laquelle les attributs de mise en valeur restent vierges lorsqu'aucune information n'est détectée.

• Cliquer deux fois sur Ok pour fermer les deux boîtes de dialogue,

Réalisons une dernière simulation à titre de vérification mais aussi de satisfaction quant à la puissance et à l'automatisme du processus.

• A l'aide de la première liste déroulante, choisir le département 07-Ardèche,
• Avec la deuxième liste, choisir l'activité liée Restaurant,
• Enfin, sélectionner la ville dépendante Aubenas dans la troisième liste,

A peine le dernier choix sur la ville est-il émis que la zone d'extraction se reconstruit, pour offrir tous les enregistrements concordant avec les trois critères recoupés par les listes déroulantes.