formateur informatique

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

Accueil  >  Bureautique  >  Excel  >  Excel Astuces  >  Récolter les données de plusieurs feuilles avec une formule
Livres à télécharger


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


Inscription Newsletter    Abonner à Youtube    Vidéos astuces Instagram
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.

Tableau de synthèse Excel pour consolider les résultats de plusieurs feuilles

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. Synthèse des chiffres d-affaires par mois sur des feuilles Excel différentes

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. 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.

Reconstruire les adresses des autres feuilles Excel par calcul

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,
Consolider les données de plusieurs feuilles Excel dans un même tableau avec la fonction Indirect

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.

 
Sur Facebook
Sur Youtube
Les livres
Contact
Mentions légales



Abonnement à la chaîne Youtube
Partager la formation
Partager sur Facebook
Partager sur Twitter
Partager sur LinkedIn