Chercher et trouver dans une table Excel à deux entrées

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


Sujets que vous pourriez aussi aimer :


Chercher et extraire dans des tables Excel à 2 entrées

Dans cette formation Excel, nous proposons de construire une console fort utile en milieu professionnel, afin d'extraire une information située à l'intersection d'une table à deux entrées, selon des choix formulés à l'instant t, par le biais de deux listes déroulantes.



Dans l'exemple illustré par la capture ci-dessus, une société fabriquant des prépolymères doit savoir si l'association de deux produits, l'un en ligne, l'autre en colonne, est compatible ou nécessite des interventions intermédiaires. Ce renseignement se situe précisément à l'intersection de l'indice de ligne et de l'indice de colonne des produits désignés.

Source et présentation de l'outil
Pour pouvoir concentrer nos efforts sur la construction de la console dynamique, nous proposons de récupérer le classeur source offrant la base de données à manipuler.

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

Ce classeur est constitué de deux feuilles. La feuille Liaison illustrée par la capture ci-dessus propose la structure de la console à bâtir pour l'utilisateur. Deux listes déroulantes dynamiques doivent être construites, respectivement en B5 et C5. Elles consistent à formuler l'association des deux produits pour le processus de fabrication. La première, avant Batch, doit permettre de désigner un produit en ligne 3 de la base de données. La seconde, après batch, doit permettre de sélectionner un produit référencé en colonne B de la base de données. Ces deux produits représentent donc les entrées à recouper dans la table à deux entées. Et c'est l'information située à leur intersection qui doit être extraite et restituée en dessous, soit en cellule B8 de la console.

• Cliquer sur l'onglet CM en bas de la fenêtre Excel pour activer sa feuille,

Nous basculons ainsi sur la base de données qui confirme la structure à deux entrées de cette table, respectivement en ligne 3 et en colonne B.

Plages de cellules dynamiques
Pour coller à un cas professionnel, cette base est censée être connectée à une source externe afin de subir des mises à jour régulières, qui ont pour conséquence de faire évoluer ses données. Nous ne pouvons donc pas raisonner sur des plages de cellules aux bornes figées. Afin que la console soit capable d'extraire les informations, en considérant les potentielles nouvelles données intégrées, nous devons commencer par référencer la base de données dynamiquement. Pour cela, nous proposons de lui attribuer un nom et de rendre ses bornes évolutives par une formule qui lui est liée.

• Sélectionner la plage de cellules A1:DT123,

Cette plage représente l'intégralité des données en partant de la borne supérieure de la feuille. Une technique de sélection rapide et efficace consiste à sélectionner A1 et à réaliser le raccourci CTRL + MAJ + Fin afin d'étendre la sélection jusqu'à la dernière cellule.

• Dans la Zone Nom, en haut de la feuille et à gauche de la barre de formule, taper le texte tab_ref et valider ce nom à l'aide de la touche Entrée du clavier,

Désormais la base de données est reconnue sous ce nom et elle sera plus simple à désigner dans l'élaboration des formules d'extraction. Mais malgré ce nom, ses bornes demeurent figées. La hauteur et la largeur de la plage de cellules n'évolueront pas en fonction des nouvelles données intégrées.

Pour pallier ce problème, nous devons retravailler cette plage à l'aide d'une formule depuis le gestionnaire de noms. Le principe consiste à exploiter la fonction Excel Decaler. Comme nous l'avait appris la formation sur le placement dynamique de calculs notamment, cette fonction, après avoir désigné le point de départ de la plage, permet de définir une hauteur et une largeur dynamiques. Et c'est la fonction NbVal imbriquée, comptabilisant le nombre de cellules non vides à l'instant t, qui définira ces dimensions.

• Cliquer sur l'onglet Formules en haut de la fenêtre Excel pour activer son ruban,
• Dans le section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,
• Dans la boîte de dialogue qui suit, sélectionner le nom tab_ref,
• Puis dans la zone de saisie Fait référence à, supprimer la référence de la plage,
• A la place, taper le symbole = pour débuter la formule,
• Saisir le nom de la fonction pour des bornes dynamiques suivi d'une parenthèse, soit Decaler(,
• Cliquer le point de départ de la plage soit la cellule A1, ce qui donne : CM!$A$1,
• Taper un point-virgule suivi du chiffre 0, soit ;0,

Nous indiquons ainsi qu'aucun décalage en ligne ne doit être réalisé par rapport au point de départ défini.

• Taper de nouveau un point-virgule suivi du chiffre 0, soit ;0,

Nous indiquons ainsi qu'aucun décalage en colonne ne doit intervenir par rapport au point de départ de la plage de cellules.

• Taper un nouveau point-virgule (;) pour accéder à la hauteur dynamique de la plage à définir,
• Saisir la fonction comptant les cellules non vides suivie d'une parenthèse, soit NbVal(,
• Cliquer sur l'étiquette de la colonne B, ce qui donne : CM!$B:$B,

Nous demandons ainsi à la fonction NbVal de comptabiliser les cellules non vides dans cette colonne. La valeur renvoyée fera office de hauteur dynamique pour le tableau de la base de données.

• Fermer la parenthèse de la fonction NbVal,
• Puis, ajouter une unité à ce résultat, soit +1,

En effet, la toute première ligne dans cette colonne est vide. Elle ne sera donc pas comptabilisée par la fonction NbVal. Et pour ne pas tronquer la dernière référence de produit en bas du tableau, nous ajoutons cette unité pour l'intégrer dans le décompte.

• Taper un point-virgule (;) pour passer dans l'argument de la largeur dynamique à définir,
• Saisir la fonction comptant les cellules non vides suivie d'une parenthèse, soit NbVal(,
• Désigner la troisième ligne de la feuille en cliquant sur son étiquette, ce qui donne : CM!$3:$3,
• Fermer la parenthèse de la fonction NbVal,
• Puis, fermer la parenthèse de la fonction Decaler,
• Cliquer sur le bouton à la coche verte pour vérifier la formule,
• Enfin, cliquer sur le bouton Fermer pour valider les modifications,

La formule que nous avons bâtie pour rendre cette plage dynamique et évolutive est la suivante :

=DECALER(CM!$A$1; 0; 0; NBVAL(CM!$B:$B) + 1; NBVAL(CM!$3:$3))



Désormais, la base de données référencée par le nom table_ref est dynamique. Elle évoluera en même temps que les données sont mises à jour par le jeu des connexions. Et donc en bâtissant nos calculs sur ce nom, nous produirons des extractions elles-aussi dynamiques, tenant compte des mêmes mises à jour.

Listes déroulantes dynamiques
Pour définir les deux entrées dynamiquement par le biais des deux listes déroulantes de la feuille Liaison, nous devons nous assurer là aussi, que leur contenu évolue au gré des mises à jour. Ce sont encore une fois les fonctions Excel Decaler et NbVal qui vont nous permettre de rendre dynamiques ces sources de données.

• Cliquer sur l'onglet Liaison en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule B5,

Cette liste déroulante concerne les produits de la première entrée de la table, soit de la ligne 3 en commençant par la colonne G.

• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Puis, dans la section Outils de données, cliquer sur le bouton Validation de données,
• Dans la zone Autoriser de la boîte de dialogue qui suit, choisir Liste,
• Cliquer alors dans la zone Source située juste en dessous afin d'activer la saisie,

C'est ici que nous devons bâtir la formule permettant à la liste déroulante de considérer tous les produits de la ligne 3, y compris les nouveaux, si des mises à jour survenaient.

• Taper le symbole = pour initialiser la formule,
• Saisir la fonction pour définir des bornes variables suivie d'une parenthèse, soit Decaler(,
• Cliquer sur l'onglet CM en bas de la fenêtre Excel pour activer sa feuille,
• Désigner alors le point de départ de la plage, soit la cellule G3, ce qui donne : CM!$G$3,
• Taper deux fois un point-virgule suivi du chiffre 0, soit : ;0;0,

En effet comme précédemment, nous ne souhaitons opérer aucun décalage, ni en ligne ni en colonne, par rapport à ce point de référence.

• Taper de nouveau un point-virgule suivi du chiffre 1,

Il s'agit en effet de l'argument de la hauteur. Celui-ci est fixe, nous travaillons sur la largeur de la ligne 3. Le chiffre 1 précise donc bien qu'il s'agit d'une plage de cellules sur une hauteur de 1 ligne et sur une largeur variable que nous allons désormais définir.

• Taper un nouveau point-virgule (;) pour passer dans l'argument de la largeur variable à définir,
• Saisir la fonction comptant les cellules non vides suivie d'une parenthèse, soit NbVal(,
• Puis, cliquer sur l'étiquette de la ligne 3, ce qui donne : CM!$3:$3,
• Fermer la parenthèse de la fonction NbVal,
• Puis retrancher six unités, soit : -6,

Les produits sont en effet référencés à partir de la colonne G, soit la septième. Or nous comptons toutes les cellules non vides sur la ligne 3. Les six premières doivent donc être exclues pour construire une source de données, bornée au plus juste pour la liste déroulante.

• Enfin, fermer la parenthèse de la fonction Decaler et valider,

La formule que nous avons construite pour définir une source dynamique, est la suivante :

=DECALER(CM!$G$3;0;0; 1; NBVAL(CM!$3:$3) - 6)

De retour sur la feuille Liaison, vous notez la présence de la liste déroulante. Si vous la déployez, vous constatez que toutes les références de produits, jusqu'à la dernière, ont parfaitement été intégrées au plus juste. Et pour vérifier qu'elle est bien dynamique, il suffit de saisir une information supplémentaire à l'extrémité de la rangée, soit en cellule DU3 de la feuille CM. Instantanément, grâce aux fonctions Decaler et NbVal, cette nouvelle référence est prise en compte dans liste déroulante, qui a déplacé l'extrémité de la plage source sur la droite.

De la même façon, nous devons créer la liste déroulante dynamique pour la seconde entrée, celle des produits Aps Batch. Elle fait référence à la colonne B cette fois et doit donc varier en hauteur et non plus en largeur. L'argument de la largeur étant facultatif dans la fonction Decaler, il n'est pas nécessaire de le définir. Comme l'énumération des produits commence à partir de la ligne 8, les 7 premières cellules ne doivent pas être comptabilisées. Mais comme la première ligne est vide, elle ne sera pas comptée par la fonction NbVal. Il s'agit donc de retrancher 7-1, soit 6 unités pour le décompte de la hauteur variable.

• Sélectionner cette fois la cellule C5 de la feuille Liaison,
• Dans le ruban Données, cliquer sur le bouton Validation de données,
• Dans la zone Autoriser de la boîtede dialogue, choisir Liste,
• Dans la zone Source, construire la formule suivante :

=DECALER(CM!$B$8; 0; 0; NBVAL(CM!$B:$B) - 6)

Après validation, vous obtenez là aussi une liste déroulante dynamique parfaitement bornée sur une source de données évolutive, comme en atteste la capture ci-dessous. Nous avons ajouté une nouvelle saisie à la suite des références, plus précisément en cellule B124 de la feuille CM. Et comme vous le constatez, elle est parfaitement intégrée en bas de la liste déroulante.



Extraire les données recoupées par les deux entrées
Maintenant que nous sommes assurés d'une part que la base de données sur laquelle nous exercerons les calculs est dynamique et d'autre part, que les listes déroulantes fournissant les critères à recouper, sont elles mêmes dynamiques, nous pouvons bâtir la formule en cellule B8. C'est elle qui offrira la puissance et le confort à l'utilisateur afin de déterminer instantanément, si l'association des deux produits sélectionnés est compatible.

Pour ce faire, nous devons exploiter les fonctions de recherche Index et Equiv. La première permet de restituer une information d'une base de données en fonction de son indice de ligne et de son indice de colonne, selon la syntaxe suivante :
=Index(Base_de_données ; indice_ligne ; indice_colonne)

La seconde permet de fournir ces indices dynamiques, selon un élément recherché, soit les produits sélectionnés par le biais des listes déroulantes. Sa syntaxe est la suivante :

=Equiv(Valeur_cherchée ; Rangée_de_recherche ; Type_de_recherche)

Le dernier argument Type_de_recherche est un booléen que nous fixerons à 0 pour réaliser une recherche selon une correspondance exacte. Enfin, nous devons imbriquer ce calcul dans une fonction de gestion d'exception (SiErreur) afin d'empêcher les retours disgracieux si les éléments cherchés ne sont pas trouvés.

• Sélectionner la cellule B8 de la feuille Liaison,
• Taper le symbole = pour débuter le calcul,
• Saisir la fonction de gestion d'erreur suivie d'une parenthèse, soit SiErreur(,
• Saisir le nom de la fonction de recherche suivi d'une parenthèse, soit Index(,
• Taper le nom désignant la base de données dynamique, soit tab_ref,
• Puis, taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Saisir la fonction de recherche dans une rangée suivie d'une parenthèse, soit Equiv(,
• Sélectionner la donnée à chercher, soit la cellule C5,
• Taper un point-virgule (;) pour passer dans l'argument de la rangée de recherche,
• Cliquer sur l'onglet CM en bas de la fenêtre Excel pour activer sa feuille,
• Désigner intégralement la colonne B en cliquant sur son étiquette, soit CM!B:B,
• Taper un point-virgule suivi du chiffre 0, soit : ;0, pour 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 la fonction de recherche dans une rangée suivie d'une parenthèse, soit Equiv(,
• Cliquer sur l'onglet Liaison en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la seconde donnée à chercher, soit la cellule B5,
• Taper un point-virgule (;) pour passer dans l'argument de la rangée de recherche,
• Cliquer sur l'onglet CM en bas de la fenêtre Excel pour activer sa feuille,
• Désigner intégralement la ligne 3 en cliquant sur son étiquette, ce qui donne : CM!3:3,
• Taper un point-virgule suivi du chiffre 0, soit : ;0, pour une correspondance exacte,
• Fermer la parenthèse de la fonction Equiv,
• Fermer la parenthèse de la fonction Index,
• Taper un point-virgule (;) pour passer dans l'argument de la gestion d'erreur,
• Saisir deux guillemets ('') pour garder la cellule vide en cas de souci,
• Fermer la parenthèse de la fonction SiErreur,
• Enfin, valider la formule avec la touche Entrée du clavier,

Le calcul que nous avons bâti est le suivant :

=SIERREUR(INDEX(tab_ref; EQUIV(C5;CM!B:B;0); EQUIV(B5;CM!3:3;0)); '')

Pour simplifier la syntaxe, nous avons supprimé le préfixe de la feuille Liaison (Liaison!) qui est inscrit par Excel de retour sur la feuille du calcul. Le préfixe de la feuille CM quant à lui est indispensable, puisqu'il désigne une autre feuille que celle dans laquelle la formule s'exécute.

Si vous sélectionnez des valeurs différentes par le biais des listes déroulantes, vous remarquez que l'information située à l'intersection des deux rangées ainsi désignées, est parfaitement récoltée et extraite.



Ainsi donc, à l'instant t, l'utilisateur obtient une réponse rapide selon une question formulée sur la base de données, par le biais de deux choix. L'application est donc à la fois simple et efficace pour tirer des enseignements rapides. Dans le cas des sélections illustrées par la capture ci-dessus, nous apprenons que l'association des deux produits impose des complications et nécessite une action importante.

Mise en valeur dynamique des résultats
Pour parfaire l'ergonomie de l'application et faciliter l'interprétation des résultats d'extraction, nous souhaitons déclencher des codes couleurs automatiques. Ils doivent représenter l'importance de l'action à réaliser. C'est bien sûr la mise en forme conditionnelle qui permet de faire réagir des cellules en fonction de critères. Nous l'avions largement mise en application dans la formation sur le jeu du MasterMind avec Excel. Il y a plusieurs cas de figure à envisager, selon la valeur retournée :

• Ok : Indicateur vert,
• Ok selon... : Indicateur bleu,
• Avec purge : Indicateur jaune,
• Rinçage : Indicateur rouge,

Le cas le plus spécifique est la valeur Ok avec une indication, comme sauf pour EDILON. Il s'agit de vérifier la présence du texte Ok dans l'expression. Ce critère de format dynamique doit être posé en premier de sorte que l'égalité sur le texte Ok seul, puisse prendre le pas lorsqu'elle survient.

• Sélectionner la cellule B8 du résultat,
• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, pointer sur Règles de mise en surbrillance des cellules,
• Puis, dans le sous menu qui apparaît, cliquer sur Texte qui contient,
• Dans la zone de saisie de la boîte de dialogue qui suit, saisir le texte ok,
• Dérouler la liste déroulante associée sur la droite,
• Tout en bas, choisir Format personnalisé,
• Dans l'onglet Remplissage de la boîte de dialogue qui suit, choisir un fond bleu pâle,
• Valider par Ok une première fois puis une seconde fois pour la première boîte de dialogue,

Les autres critères sont plus simples, ils consistent à vérifier l'égalité stricte. La cellule B8 étant toujours sélectionnée :

• Cliquer de nouveau sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, pointer sur Règles de mise en surbrillance des cellules,
• Dans le sous menu qui apparaît, choisir Egal à,
• Dans la boîte de dialogue qui suit, taper de nouveau le texte ok,
• Dans la liste déroulante, choisir le format prédéfini : Remplissage vert avec texte vert foncé,
• Valider ce format dynamique par Ok,

• Réitérer exactement le même processus pour que l'égalité avec le texte avec purge soit associée à un remplissage jaune avec texte jaune foncé et que l'égalité avec le texte rinçage soit associée à un remplissage rouge clair avec un texte rouge foncé,

La capture ci-dessus synthétise tous ces réglages de mise en forme conditionnelle. Ils se déclenchent parfaitement, selon la valeur extraite. Le bleu remplit en effet la cellule lorsque l'association est ok selon une condition et le vert prend le pas, grâce à la chronologie de la mise en place, lorsque l'association des deux produits est parfaitement compatible. L'application finalisée s'en trouve plus efficace et ergonomique.