Rechercher dans plusieurs feuilles avec Excel

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


Sujets que vous pourriez aussi aimer :


Recherches dans plusieurs feuilles

Sur un tableau de synthèse placé sur une feuille d'accueil, l'objectif de cette nouvelle astuce Excel est de réussir à importer les informations liées à des références, à l'aide d'une seule formule et sachant que la feuille d'origine n'est pas connue à l'avance.



Sur l'exemple illustré par la capture, ce sont les ventes réalisées par des légumes sur une période précise qui sont réunies sur une seule et même feuille. Pourtant ces ventes sont archivées par mois de l'année, dans des feuilles respectives et donc différentes.

Classeur Excel à télécharger
Pour la mise en place de cette solution, nous proposons d'appuyer l'étude sur un classeur offrant ces données réparties dans différentes feuilles.

• Télécharger le classeur recherches-differentes-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 de ce classeur. Elle livre un tableau de synthèse qu'il est question de compléter. Des fruits sont énumérés en première colonne. Les dates des ventes consolidées sont inscrites en deuxième colonne. C'est en fonction de cette information que nous devons réussir à piocher le total des ventes dans la feuille correspondante. En effet, ces chiffres sont regroupés en fonction du mois durant lequel ils ont été réalisés.



Vous pouvez le constater en consultant les noms des onglets en bas de la feuille Excel. Si vous affichez l'une ou l'autre feuille, vous remarquez que ces tableaux ont la même structure. Ils hébergent les informations sur une même plage bornée : C4:E6.



En revanche, les fruits ne sont pas nécessairement classés dans le même ordre.

Reconstruire les noms des feuilles cibles
Sur la page d'accueil, vous avez sans doute remarqué la présence de l'intitulé Aide en colonne F. Pour la compréhension de la formule finale, nous souhaitons en effet évoluer par étapes. Le premier objectif consiste à reconstruire l'adresse permettant de désigner une feuille en fonction de l'information sur la date, pour atteindre son tableau d'extraction. La fonction Excel Texte permet de convertir une donnée en texte avec un format de sortie spécifique à définir. Sur la date inscrite en colonne D, ce format ne doit conserver que l'abréviation du mois en texte avec les quatre chiffres de l'année. C'est ainsi que sont nommées les feuilles. Ce nom doit être encadré de côtes et suivi d'un point d'exclamation pour atteindre hiérarchiquement la plage cible.

• Cliquer sur la cellule F4 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Puis, inscrire une simple côte entre guillemets, soit : "'",

La première côte est donc placée. Une seconde devra surgir après le nom de la feuille.

• Après un espace, inscrire le caractère de concaténation (&) pour débuter l'assemblage,
• Puis, ajouter un nouvel espace,

Ces espaces servent à améliorer la lisibilité de la formule.

• Inscrire la fonction de conversion, soit : Texte(,
• Désigner la première date à transformer en cliquant sur la cellule D4,
• Puis taper un point-virgule (;) pour passer dans l'argument du format de sortie,
• Puis, entre guillemets, inscrire le code suivant : "mmm aaaa",

Grâce aux quatre lettres a, nous conservons la précision totale sur l'année, soit les quatre chiffres. Avec les trois lettres m, nous demandons l'abréviation du mois de la date ainsi passée en premier argument. Naturellement, l'espace permet de séparer le mois abrégé de l'année qui suit.

• Dès lors, fermer la parenthèse de la fonction Texte,
• Ajouter un caractère de concaténation (&) après un espace pour poursuivre l'assemblage,
• Puis, ajouter un nouvel espace,
• Dès lors, ouvrir les guillemets suivis d'une apostrophe et d'un point d'exclamation, soit : "'!,

Avec cette nouvelle simple côte, nous réalisons l'encadrement du nom de la feuille, comme expliqué précédemment. Le point d'exclamation annonce la plage de cellules cible à suivre et à atteindre.

• Inscrire les coordonnées de cette plage et fermer les guillemets, soit : C4:E6",
• Valider enfin la formule par le raccourci clavier CTRL + Entrée,

De cette manière et comme vous le savez, nous conservons active la cellule du résultat. Et précisément, cette première adresse ainsi reconstituée est parfaitement correcte. Pour le 15/01/2021, elle pointe bien sur le tableau de la feuille du mois de Janvier : 'janv2021'!C4:E6.

• Cliquer et glisser la poignée du résultat vers le bas jusqu'en cellule F13,

Comme vous pouvez le voir, les sources d'extraction respectives sont désormais connues.

Extraire à partir de la bonne feuille
Nous proposons de les exploiter avec la fonction RechercheV pour trouver chaque fruit dans sa feuille. Mais attention, l'adresse que nous avons recomposée est considérée comme un texte complètement inerte à ce stade. Nous l'avons démontré à de multiples occasions au travers de diverses formations, cette adresse doit être interprétée avec la fonction Excel Indirect.

• Sélectionner de nouveau la cellule F4 du premier résultat,
• Dans la barre de formule, sélectionner toute la syntaxe sans le symbole égal,
• La copier avec le raccourci (CTRL + C) puis sortir de la barre de formule avec la touche Echap,
• Cliquer alors sur la cellule E4 de la première vente à importer,
• Taper le symbole égal (=) pour initier la syntaxe de la formule d'extraction,
• Inscrire la fonction de recherche suivie d'une parenthèse, soit : RechercheV(,
• Désigner le premier fruit à trouver en cliquant sur sa cellule C4,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,

C'est lui qui est dynamique dans la mesure où jusqu'au calcul précédent, sa feuille n'était pas connue à l'avance. Mais désormais ce problème est résolu.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Dès lors, coller (CTRL + V) la syntaxe précédemment copiée,
• Puis, fermer la parenthèse de la fonction Indirect,

Nous venons donc de définir la plage de cellules sur la feuille variable à partir de laquelle il est question d'importer la vente correspondante.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne,

Dans chacun de ces tableaux, la vente à extraire est située en troisième colonne.

• Inscrire le chiffre 3 pour désigner cette colonne,
• Taper un point-virgule suivi du booléen Faux, soit : ;Faux, pour une recherche exacte,
• Fermer la parenthèse de la fonction RechercheV,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,
• Enfin, double cliquer sur la poignée du résultat pour répandre la logique sur le tableau,

Comme vous pouvez l'apprécier, toutes les extractions sont parfaitement réalisées bien que les sources de données n'étaient pas connues à l'avance. Et vous pouvez très facilement vérifier la cohérence de ces importations en basculant d'une feuille à une autre.

La syntaxe complète de la formule d'extraction multi feuille que nous avons construite est la suivante :

=RECHERCHEV(C4; INDIRECT("'" & TEXTE(D4; "mmm aaaa") & "'!C4:E6"); 3; FAUX)