Récupérer toutes les informations associées à une référence

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


Sujets que vous pourriez aussi aimer :


Récolter toutes les données liées

Au cours de diverses formations, nous avons déjà démontré maintes techniques pour importer toutes les données attachées à une référence choisie par l'utilisateur. Et pour cela, nous avions notamment imbriqué les fonctions Excel Index et Equiv. Ici, nous amenons une technique matricielle judicieuse qui permet de simplifier largement la syntaxe finale de la formule d'extraction.



Sur l'exemple illustré par la capture, nous travaillons sur une base de données d'articles vestimentaires. Sur la droite de ce tableau, l'utilisateur choisit l'une des références disponibles avec une liste déroulante. Et aussitôt dans trois cellules situées un peu plus bas, ce sont les informations attachées sur le nom, le prix et le stock qui sont dynamiquement rapatriées. Pour cela, c'est une unique formule à la syntaxe triviale qui réalise le tour de force. A la fin de ce sujet, nous proposerons de comparer avec la technique consistant à imbriquer deux fonctions Equiv dans une fonction Index.

Classeur Excel à télécharger
Pour la démonstration de cette technique matricielle, nous suggérons d'appuyer l'étude sur un classeur Excel offrant cette base de données.

• Télécharger le classeur extraire-tout.xlsx 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 découvrons la base de données entre les colonnes B et G et étendue sur plus de 200 lignes. En cellule I6, une liste déroulante se suggère lorsqu'elle est sélectionnée. L'utilisateur peut choisir l'une des références disponibles à la vente pour consulter ses informations associées. Ces données doivent être rapatriées en cellules I8, I9 et I10 à l'aide d'une seule formule.

• Déployer la zone Nom en haut à gauche de la feuille Excel,

Deux plages nommées existent. En les sélectionnant tour à tour, vous constatez que la plage bdd désigne toutes les cellules de la base de données, tandis que la plage ref désigne seulement la colonne des références. Nous engagerons ces noms dans la construction de la formule pour en simplifier la syntaxe.

Démarrer l'extraction
Pour débuter l'extraction des données à partir de la cellule I8, nous allons exploiter la fonction Index à appliquer sur la base de données reconnue sous l'intitulé bdd. Comme une petite matrice virtuelle interviendra pour désigner les multiples colonnes d'extraction, nous devons commencer par sélectionner toutes les cellules qui attendent l'importation des informations liées.

• Sélectionner la plage de cellules I8:I10,
• Taper le symbole égal (=) pour initier la syntaxe de la formule d'extraction,
• Inscrire le nom de la fonction d'extraction, suivi d'une parenthèse, soit : Index(,
• Désigner la base de données par son nom, soit : bdd,
• Puis, taper un point-virgule (;) pour passer dans l'argument de la position en ligne,

Trouver la position en ligne
Pour extraire le nom, nous devons maintenant trouver la position en ligne de la référence dans le tableau. Pour cela, nous devons engager la recherche de cette clé dans la colonne des références, grâce à la fonction Equiv.

• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• Désigner la référence cherchée en cliquant sur sa cellule I6,

Malgré la réplication attendue sur les deux lignes du dessous, il n'est pas nécessaire de figer cette cellule. En effet, nous engageons une technique matricielle qui fige elle-même cette référence désignée.

• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Désigner la colonne des références par son nom, soit : ref,

Remarque : Si cette colonne n'avait pas été nommée, il existerait une technique efficace pour éviter de devoir désigner à la souris les centaines de cellules dans la rangée. Elle consisterait à exploiter la fonction Index comme suit : INDEX(bdd;;3). Avec le chiffre 3 en troisième paramètre, nous la ferions naturellement pointer sur l'intégralité de la colonne des références.

• Taper un point-virgule suivi du chiffre zéro : ;0, pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, taper un point-virgule (;) pour passer dans l'argument de la position en colonne,

Définir les positions en colonnes
C'est maintenant qu'intervient l'astuce matricielle essentielle. Pour une formule efficace, nous ne souhaitons pas désigner une seule colonne, mais toutes celles qui concernent le nom, le prix et le stock à rapatrier. Pour cela, nous allons construire une petite matrice de chiffres. Chaque chiffre doit repérer la position de la colonne souhaitée dans la base de données.

• Construire la matrice virtuelle suivante : {1;2;6},

Nous désignons donc les positions respectives pour le nom, pour le prix et pour le stock. Avec des points-virgules dans l'énumération, nous fabriquons une matrice verticale avec ces chiffres. En effet, la réplication est attendue à la verticale, sur les lignes du dessous. C'est ainsi que chaque numéro sera respectivement considéré pour extraire l'information attendue.

• Fermer la parenthèse de la fonction Index,
• Puis, valider la formule par le raccourci clavier CTRL + MAJ + Entrée,

Comme vous pouvez l'apprécier, toutes les données attachées à la référence qui avait été choisie, sont aussitôt extraites.



Bien sûr, si vous changez de référence avec la liste déroulante, les importations s'actualisent instantanément. La syntaxe très simple de la formule matricielle que nous avons bâtie, est la suivante :

=INDEX(bdd;EQUIV(I6; ref; 0); {1;2;6})

Sachez enfin et néanmoins qu'il aurait été possible de bâtir une formule unique évitant la technique matricielle, pour aboutir au même résultat :

=INDEX(bdd;EQUIV($I$6; ref; 0); EQUIV(J8; $B$3:$G$3; 0))

Elle consiste à imbriquer deux fonctions Equiv dans la fonction Index. Elle impose aussi l'entrée en lice des références absolues. Et vous en conviendrez, la syntaxe s'alourdit assez significativement.