Extractions à partir de multiples feuilles Excel

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


Sujets que vous pourriez aussi aimer :


Extraction multi feuilles

Lorsque la référence cherchée se trouve dans l'un des tableaux de l'une des feuilles, c'est la plupart du temps un raisonnement matriciel qui dénoue le problème pour rapatrier les informations associées.



Dans l'exemple illustré par la capture, l'utilisateur choisit une référence avec une liste déroulante un peu comme si le code à barres de l'article avait été scanné. Et aussitôt, le prix de ce produit est rapatrié dans la cellule voisine. Pourtant, cette référence peut être placée dans n'importe quel tableau de n'importe quelle feuille Excel. L'enjeu consiste donc à produire des extractions précises même lorsque la feuille de recherche n'est pas connue à l'avance.

Classeur Excel à télécharger
Pour la mise en place de la solution, nous suggérons d'appuyer l'étude sur un classeur hébergeant plusieurs tableaux répartis dans plusieurs feuilles.

• Télécharger le classeur extraction-multi-feuilles.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ébouchons sur la première feuille du classeur. L'utilisateur choisit une référence avec une liste déroulante en cellule D4. Et aussitôt, c'est une formule matricielle qui doit importer le prix correspondant en cellule voisine E4.



En bas de la fenêtre Excel, vous remarquez que toutes les feuilles suivantes portent le même nom (Base) en préfixe. C'est un numéro incrémenté qui les différencie. Et c'est cette construction remarquable qui va grandement simplifier le raisonnement matriciel pour pouvoir les considérer toutes dans une seule et même formule.

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

Vous découvrez un tableau hébergeant des références avec leurs prix, de la ligne 2 à la ligne 10 et sur les colonnes A et B. Cette structure et ces bornes sont strictement respectées pour tous les autres tableaux des autres feuilles, bien que les références des articles diffèrent à chaque fois.

Technique matricielle multi feuilles
Pour résoudre le cas, nous allons engager en quelque sorte une recherche verticale multi feuilles. En imbriquant la fonction Somme.Si dans la fonction SommeProd, toutes les cellules de toutes les matrices désignées vont pouvoir être confrontées à la référence demandée. Ces matrices doivent représenter les plages aux bornes fixes certes, mais sur toutes les feuilles concernées à la fois.

• En bas de la fenêtre Excel, cliquer sur l'onglet extraction pour activer sa feuille,
• Cliquer alors sur la cellule E4 du prix pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• Inscrire la fonction de somme conditionnelle suivie d'une parenthèse, soit : Somme.Si(,

La feuille de recherche
En premier des trois arguments, nous devons lui spécifier la plage sur laquelle il est question de trouver la référence pour en extraire le prix correspondant. Et comme nous l'avons dit, nous ne savons pas quel est le tableau dans lequel se trouve ce code. En d'autres termes, nous ne connaissons pas la feuille dans laquelle il s'agit de réaliser l'analyse. Dans ce raisonnement matriciel, nous allons désigner chacune des feuilles grâce à une matrice virtuelle recomposant les numéros incrémentés. C'est ainsi qu'elles seront toutes concernées de façon récursive. Mais comme vous le savez, une matrice virtuelle doit être interprétée.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Entre guillemets, taper le préfixe commun à toutes les feuilles, soit : "Base",
• Puis, taper le symbole de concaténation (&) pour annoncer la matrice des numéros à suivre,
• Inscrire la fonction donnant l'indice de ligne, suivie d'une parenthèse, soit : Ligne(,

En guise de cellule, nous allons lui passer une matrice virtuelle représentant les quatre premières lignes d'une feuille. Dans ce raisonnement matriciel, elles vont toutes être passées en revue tour à tour, pour recomposer chaque suffixe de chaque feuille.

• Construire la matrice virtuelle suivante : 1:4,
• Puis, fermer la parenthèse de la fonction Ligne,

Maintenant que toutes les feuilles sont représentées, nous devons descendre jusqu'aux plages de cellules aux bornes communes. Ce sont elles qui abritent les références et les prix correspondants à extraire.

• Taper de nouveau le symbole de concaténation (&) pour poursuivre l'assemblage,
• Inscrire la plage entre guillemets avec le point d'exclamation en préfixe : "!A2:A10",
• Puis, fermer la parenthèse de la fonction Indirect,

Il s'agit bien sûr de la plage de recherche des références que l'utilisateur peut choisir avec la liste déroulante.

Le critère de recherche
En deuxième argument, il est question de définir le critère à utiliser pour la somme conditionnelle. En réalité, il s'agit du critère de recherche, donc de la référence choisie. Lorsqu'elle sera trouvée, la somme isolera l'unique prix qui sera restitué.

• Taper un point-virgule (;) pour passer dans l'argument du critère de la somme conditionnelle,
• Puis, désigner la référence cherchée par ses coordonnées de cellule, soit : D4,
• Taper un dernier point-virgule (;) pour passer dans l'argument de la plage pour la somme,

La plage d'extraction
Cette plage correspondant au critère pour la somme n'est autre que la plage d'extraction. Nous l'avons dit, seule une référence sera concordante, quel que soit le tableau et quelle que soit la feuille. En conséquence, la somme restituera simplement le prix ainsi isolé. La construction est identique à celle de la plage pour le critère de la fonction Somme.Si. Mais cette fois, nous devons pointer sur la colonne B, entre les lignes 2 et 10.

• Puis, reconstruire la plage d'extraction : Indirect("Base" & Ligne(1:4) & "!B2:B10"),
• Fermer la parenthèse de la fonction Somme.Si,
• Puis, fermer la parenthèse de la fonction SommeProd,
• Dès lors, valider la formule avec le raccourci clavier CTRL + MAJ + Entrée,

Depuis la version 2019 d'Office, ce raccourci n'est plus nécessaire car Excel comprend naturellement qu'il s'agit d'un raisonnement matriciel. Quoiqu'il en soit, vous voyez apparaître le prix correspondant. Et si vous vous rendez sur la feuille hébergeant cette référence, vous constatez que l'extraction est la bonne. Bien sûr, si vous changez de référence, le prix correspondant est automatiquement extrait et ce, quelle que soit la feuille hébergeant le code cherché. Mais vous l'avez compris, en raison de la nature de la fonction Somme.Si, cette recherche multifeuilles ne peut fonctionner que sur les données numériques à rapatrier.