Récolter les données de plusieurs feuilles avec une formule

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


Sujets que vous pourriez aussi aimer :


Synthèse multi-feuille

Avec cette nouvelle astuce Excel, nous allons voir comment réunir sur une seule et même feuille tous les chiffres d'affaires réalisés chaque mois et consolidés indépendamment dans des feuilles explicitement différentes.



C'est ce qu'illustre le résultat présenté par la capture. En première colonne, les mois rappellent les noms de chaque feuille. Et c'est cette astuce qui va nous permettre de pointer sur chacune d'elles sur la base d'une seule formule à répliquer. Dans chaque feuille, chaque synthèse est effectivement judicieusement placée en entête dans une cellule possédant toujours les mêmes coordonnées.

Classeur source
Pour la mise en place de cette astuce, nous suggérons d'appuyer l'étude sur un classeur livrant déjà ces synthèses dans différentes feuilles.

• Télécharger le classeur consolidation-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 l'une des feuilles de ce classeur. Il s'agit de la feuille Avril pour le mois du même nom. La synthèse y est implantée en cellule C2. C'est le cas pour chaque feuille. Vous pouvez le constater en basculant de l'une à l'autre. Ainsi, malgré les variations des nombres de jours dans le mois, nous saurons que l'adresse de la cellule à prélever ne bouge pas.

Consolider les feuilles avec une formule
Dans un contexte où le nombre de ces feuilles se multiplie, il est opportun d'envisager la consolidation des données avec une seule formule dynamique, capable donc de s'actualiser au gré des évolutions de valeurs.

• En bas de la fenêtre Excel, cliquer sur l'onglet Synthese pour afficher sa feuille,

Nous trouvons un tableau qui énumère les mois en colonne D. Et comme nous l'avons évoqué, il s'agit aussi des noms respectifs des feuilles. Les synthèses doivent être rapatriées en regard, en colonne voisine E. A partir de la cellule E4, la technique usuelle consisterait à taper le symbole égal (=) puis à cliquer sur la cellule C2 de la feuille Janvier. Nous obtiendrions alors la syntaxe suivante : =Janvier!C2. Mais cette formule est statique en raison du nom de la feuille en préfixe. Elle ne peut donc être répliquée automatiquement pour prélever dans les autres feuilles au même emplacement. L'astuce consiste donc à utiliser ces noms de feuilles comme variables du calcul grâce aux informations données en première colonne de ce tableau de consolidation.

• Sélectionner la cellule de la première synthèse à récupérer en cliquant sur E4,
• Taper le symbole égal (=) pour initier le calcul,
• Désigner la première feuille en cliquant sur la cellule D4 du premier mois,
• Enfoncer la touche 1 en haut à gauche du clavier pour le symbole de concaténation (&),
• Ouvrir les guillemets et inscrire un point d'exclamation, soit : "!,

Comme vous le savez, le point d'exclamation est le suffixe du nom de la feuille pour pouvoir atteindre l'une de ses cellules. Cette syntaxe s'apparente à celle de la programmation orientée objets pour atteindre des propriétés, méthodes ou objets enfants dans la hiérarchie.

• Taper alors les coordonnées de la cellule à atteindre, soit C2,
• Fermer les guillemets, puis valider le calcul par le raccourci clavier CTRL + Entrée,

Cette technique permet de garder active la cellule du résultat. Comme vous pouvez le voir, nous avons bien recomposé l'adresse de la cellule externe dont il s'agit de récupérer le contenu.



Et si vous double cliquez sur la poignée du résultat, ce sont bien toutes les adresses à atteindre qui sont dynamiquement reconstruites pour chaque feuille.

Bien sûr, l'objectif n'est pas d'afficher ces adresses mais de les exploiter pour prélever l'information située dans la destination. Et comme vous le savez sans doute, il existe une fonction Excel permettant d'interpréter ces informations textuelles, lorsqu'elles désignent des éléments d'un classeur. Il s'agit de la fonction Indirect.

• Sélectionner de nouveau la cellule E4,
• Dans la barre de formule, cliquer après le symbole égal pour y placer le point d'insertion,
• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Puis, cliquer à la fin de la syntaxe, après les guillemets, pour y placer le point d'insertion,
• Fermer la parenthèse de la fonction Indirect,
• Dès lors, valider la formule par le raccourci clavier CTRL + Entrée,

Cette fois, c'est bien un résultat numérique qui apparaît. Il s'agit de la synthèse des chiffres d'affaires consolidés dans la feuille Janvier.

• Double cliquer sur la poignée du résultat pour répliquer la formule sur tout le tableau,

Tous les chiffres sont rapatriés depuis les feuilles respectives et ce, avec une seule formule, simplissime au demeurant : =INDIRECT(D4&"!C2"). Vous pouvez facilement vérifier la cohérence des résultats en basculant d'une feuille à une autre.

Remarque : Les jeux de couleurs qui se déclenchent sont dues à la présence de règles de mise en forme conditionnelle prédéfinies sur la plage du calcul.

Certes, il existe une contrainte dans cette étude. Les entêtes de ligne doivent porter le même nom que les feuilles à pointer. Mais dans les volets suivants, nous verrons comment nous dédouaner de ce carcan.