Rechercher dans toutes les feuilles du classeur Excel

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


Sujets que vous pourriez aussi aimer :


Rechercher dans toutes les feuilles du classeur

L'astuce Excel précédente a démontré comment réaliser des extractions numériques à partir de plusieurs feuilles dans un même classeur. Ici, l'objectif est assez similaire.



Cependant, comme le démontre le cas illustré par la capture, une différence de taille se dresse. Il n'existe pas de cohérence dans les noms des feuilles. Dans le cas précédent, elles possédaient toutes le même préfixe. C'est un numéro incrémenté qui les différenciait. Et grâce à lui, nous avions pu les traiter de façon récursive dans un raisonnement matriciel. Au choix d'une référence dans une liste déroulante, nous avions rapatrié le prix correspondant, sans connaître la feuille l'hébergeant. Ici, selon le même principe, il est donc question d'exercer une recherche multi feuilles, sans connaître les noms des feuilles et tout en imaginant que celles-ci pourraient progresser en nombre.

Classeur Excel à télécharger
Pour développer cette nouvelle astuce, nous suggérons d'appuyer l'étude sur un classeur abritant différentes sources de données réparties dans des feuilles différentes aux noms complètement indépendants.

• Télécharger le classeur chercher-toutes-feuilles.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 découvrons tout d'abord la première feuille de ce classeur. En cellule D4, l'utilisateur doit choisir une référence à l'aide d'une liste déroulante. Cette référence peut être hébergée par l'une des quatre feuilles suivantes. Une fois le choix validé, le prix de cet article doit être rapatrié en cellule E4 voisine, par une formule matricielle capable de scruter n'importe quelle feuille du classeur.



Si vous cliquez sur les onglets des feuilles suivantes pour afficher leurs contenus, vous remarquez qu'elles hébergent des tableaux de même structure, aux bornes identiques. En revanche, elles ne référencent pas les mêmes codes articles, ceux précisément qu'il s'agit de dénicher depuis la feuille d'accueil.

Connaître les noms des feuilles
Un procédé existe avec Excel pour connaître tous les noms des feuilles et les exploiter naturellement dans une formule. Il consiste à dégainer la fonction de Macro XL4 qui se nomme Lire.Classeur. Nous l'avions décortiquée dans une formation qui démontre comment lister les noms des feuilles.

• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaires de noms,

Dans la boîte de dialogue qui apparaît, un nom existe déjà en effet (nomF). Il exploite la syntaxe suivante : =STXT(LIRE.CLASSEUR(1); TROUVE("]"; LIRE.CLASSEUR(1))+1; 99).

Avec la valeur 1 passée en argument de la fonction de macro Lire.Classeur, nous obtenons un tableau de tous les noms des feuilles du classeur. Mais ceux-ci sont préfixés du nom du classeur entre crochets. C'est la raison pour laquelle nous exploitons les fonctions Stxt et Trouve pour ne conserver que ce qui se trouve derrière le crochet fermant (]), soit les noms des feuilles strictement.

• Cliquer sur le bouton Ok de la boîte de dialogue pour revenir sur la feuille Excel,

En revanche, selon la version d'Excel, il n'est pas impossible que vous soyez contraint de débloquer la sécurité concernant les macros XL4. Pour cela :

• En haut de la fenêtre Excel, cliquer sur l'onglet Développeur pour activer son ruban,
• Tout à fait à gauche, dans la section Code, cliquer sur le bouton Sécurité des macros,
• Dans la boîte de dialogue, cocher l'option Activer les macros VBA,
• Puis, cocher la case Activer les macros Excel 4,0,

Dès lors, vous pouvez valider ces nouveaux réglages en cliquant sur le bouton Ok.

Le raisonnement multi feuilles
Pour débuter et comme nous l'avons fait dans le volet précédent, l'idée consiste à imbriquer la fonction Somme.Si dans la fonction SommeProd. Ainsi, toutes les cellules de toutes les matrices désignées pourront être confrontées à la référence demandée. Ces matrices doivent désigner les plages aux bornes fixes certes, mais sur toutes les feuilles concernées à la fois dont les noms sont détenus dans le tableau nomF.

• 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(,

Parcourir toutes les feuilles
Comme vous le savez, un raisonnement matriciel engage un traitement récursif. En passant la plage nomF en premier argument de la fonction Somme.Si, ce sont toutes les feuilles qu'elle représente qui vont être passées en revue tour à tour. Et sur chaque feuille, nous devons descendre jusqu'à la plage de recherche de la référence demandée, soit la plage A2:A10. De plus, ces noms doivent être interprétés par Excel pour ne pas être considérés comme des textes.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Dans l'enchaînement, taper une apostrophe entre guillemets, soit : "'",

En effet, le nom d'une feuille doit être désigné entre côtes. Une simple côte fait très bien l'affaire.

• Inscrire le caractère de concaténation (&) pour débuter l'assemblage avec la partie dynamique,
• Taper le nom de la matrice des feuilles, soit : nomF,
• Taper de nouveau le symbole de concaténation pour poursuivre avec la partie statique,
• Taper un guillemet suivi d'une apostrophe, soit : "',
• Puis, inscrire la plage de recherche préfixée d'un point d'exclamation, soit : !A2:A10,
• Dès lors, fermer les guillemets ainsi que la parenthèse de la fonction Indirect,

Une fois encore dans ce raisonnement matriciel, grâce à la plage nomF et à la fonction Indirect, nous venons de désigner toutes les feuilles du classeur à considérer pour la recherche de la référence.

Le critère de recherche
En deuxième argument, la fonction Somme.Si attend le critère à observer sur l'une des plages que nous lui avons fournies en premier paramètre. Ce critère n'est autre que la référence choisie en cellule D4.

• Taper un point-virgule (;) pour passer dans l'argument du critère,
• Puis, taper les coordonnées de la cellule de recherche, soit : D4,

La plage d'extraction
Le troisième argument de la fonction Somme.Si correspond conventionnellement à la plage concordante sur laquelle doit être effectuée la somme selon le critère honoré sur la plage de recherche. Vous l'avez compris, comme cette référence ne peut être trouvée que dans l'un des tableaux de l'une des feuilles, la somme va simplement restituer la valeur numérique recherchée (Le prix). Il s'agit donc de la plage d'extraction dans ce raisonnement matriciel. Mais comme pour les plages de recherche, les plages d'extraction doivent toutes être désignées. Leur construction matricielle est identique à la précédente. Mais cette fois, il faut pointer sur la rangée B2:B10.

• Taper un point-virgule (;) pour passer dans le troisième argument de la fonction Somme.Si,
• Puis, construire la matrice suivante : Indirect("'" & nomF & "'!B2:B10") ,
• Dès lors, fermer la parenthèse de la fonction Somme.Si,
• Puis, fermer la parenthèse de la fonction SommeProd,
• Enfin, valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

Nous le répétons à chaque occasion, depuis la version 2019, ce raccourci n'est plus nécessaire. Excel comprend naturellement qu'il s'agit d'un raisonnement matriciel à engager.

Le prix associé est directement rapatrié. Si vous vérifiez sur la bonne feuille, vous constatez que l'importation est parfaitement cohérente. Si vous changez de référence, le nouveau prix est aussitôt extrait et ce, sans connaître à l'avance la feuille dans laquelle doit être exercée la recherche.

La syntaxe complète de la formule matricielle que nous avons construite est la suivante :

{=SOMMEPROD(SOMME.SI(INDIRECT("'" & nomF & "'!A2:A10"); D4; INDIRECT("'" & nomF & "'!B2:B10")))}

Enfin et vous l'avez compris, si vous ajoutiez une nouvelle feuille portant un tableau avec les mêmes bornes, que vous créiez de nouvelles références associées à des prix et que vous tapiez l'un de ces codes en cellule D4 de la feuille d'accueil, la recherche serait toujours fructueuse, intégrant automatiquement le nouvel onglet.

Cependant et dans notre cas, la liste déroulante que nous avons construite n'accepte pas la saisie des valeurs qui n'étaient pas prévues. Mais une formation nous avait appris à débrider une liste déroulante sous Excel.