Consolider les données de plusieurs 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 :


Réunir les données de plusieurs feuilles

A l'occasion de cette nouvelle astuce Excel, nous allons découvrir comment consolider les informations de plusieurs feuilles dans un seul tableau. Et pour cela, nous allons construire une unique formule à répliquer.



Dans l'exemple illustré par la capture, toutes les données numériques de différents commerciaux sont réunies dans un tableau de synthèse. Ces données sont les chiffres d'affaires réalisés par chacun au cours des mois du premier semestre. Ces mois sont représentés par les six premiers ongletsdu classeur. Ils portent effectivement ces intitulés. Ces intitulés sont précisément des éléments de recherche. Ils sont listés sur la première ligne du tableau de synthèse. Et nous allons donc les exploiter pour pointer sur la bonne feuille, sur la base d'une formule unique à répliquer, pour réunir instantanément et automatiquement toutes les informations dispersées.

Classeur source et présentation
Pour la démonstration de cette nouvelle astuce, nous suggérons d'appuyer les travaux sur ce classeur hébergeant ces différentes feuilles.

• Télécharger le classeur consolider-plusieurs-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 feuille de synthèse. Elle est nommée Semestre. Naturellement, son tableau est vide pour l'instant. En bas de la fenêtre Excel, vous notez la présence de six autres onglets pour les six mois du premier semestre.

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

Nous découvrons un tableau des ventes réalisées par les commerciaux pour ce mois désigné. Contrairement au tableau de synthèse, les noms des commerciaux sont énumérés sur une ligne, la ligne 3 ici en l'occurrence. Dans le tableau de synthèse, ils sont énumérés sur une colonne. Il s'agit de la colonne B. Mais nous le verrons, cette différence de structure n'est pas un problème. Les données numériques des quantités vendues par article sont retranscrites dans les cases de chaque tableau. Et ces articles sont listés en première colonne du tableau. La ligne Total (Ligne 11) est importante. Ce sont ces résultats consolidés par mois que nous devons rapatrier pour chaque commercial dans le tableau de synthèse.

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

Nous trouvons un tableau offrant la même structure que celui de la feuille précédente. Mais deux faits notables sont à souligner. Le nombre des commerciaux en activité est plus important. De plus, ils ne sont pas listés dans le même ordre. Mais une fois encore nous le verrons, cette différence d'organisation ne sera pas une embûche pour l'astuce que nous allons apporter afin de réunir toutes les données des différentes feuilles.

Consolider les totaux par mois
Pour réunir ces données éparpillées dans le tableau de synthèse, l'astuce consiste à réaliser l'extraction indirectement sur le tableau désigné et reconnu par son nom de feuille, le même que le nom du mois pour lequel il s'agit de rapatrier chaque donnée. Il convient donc d'imbriquer judicieusement les fonctions Index pour l'extraction, Indirect pour pointer sur la bonne feuille et Equiv pour déceler la position du commercial.

• En bas de la fenêtre Excel, cliquer sur l'onglet Semestre pour revenir sur sa feuille,
• Sélectionner la première cellule vide du tableau en cliquant sur C4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,

En premier paramètre, nous devons fournir à cette fonction le tableau de recherche. Celui-ci change de feuille en fonction de l'indication textuelle sur le mois fourni en ligne 3 de ce tableau de synthèse. Pour être considérée comme une feuille par Excel, cette indication textuelle doit être interprétée.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Désigner le nom de la feuille cible en cliquant sur la cellule C3 du premier mois,
• Puis, enfoncer deux fois la touche F4 du clavier, ce qui donne : C$3,

De cette manière, nous figeons cette référence en ligne et la gardons libre de se déplacer en colonne. Nous l'avons dit, l'enjeu est de produire une formule unique à répliquer sur tout le tableau. Au gré de la réplication sur les cases de droite, ce sont bien les autres mois pour les autres feuilles qui doivent être considérés. C'est la raison pour laquelle nous avons supprimé le dollar devant l'indice C de colonne. En revanche, au gré de la réplication sur les cases du dessous, la formule doit toujours pointer sur la feuille reconnue par le nom du mois, immuablement placé sur la ligne 3. C'est la raison pour laquelle nous conservons le dollar devant l'indice 3 de ligne.

Sur la feuille de destination, nous devons pointer sur la ligne des totaux consolidés par mois. Et comme vous le savez dans ces adresses, le nom d'une feuille doit toujours être suivi d'un point d'exclamation avant d'atteindre ses cellules. Cet assemblage doit se faire par concaténation.

• 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 : "!,
• Désigner la ligne figée du total correspondant à toutes les feuilles, soit : $C$11:$I$11,

Il convient de taper ces coordonnées sans oublier les dollars devant chaque indice. Vous pouvez aussi sélectionner la plage sans changer de feuille, donc sur la feuille active et la figer.

• Fermer les guillemets puis fermer la parenthèse de la fonction Indirect,

De fait, nous sommes de retour dans les arguments de la fonction Index. Nous venons de définir la ligne variable de recherche pour l'extraction. Mais selon le commercial, l'information à prélever n'est pas située dans la même colonne. C'est la fonction Equiv qui permet de déceler cette position.

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

Comme la matrice de recherche n'est constituée que d'une seule rangée, aussi étonnant que cela puisse paraître, pour pointer sur le bon emplacement, nous pouvons indifféremment exploiter le deuxième argument de la fonction Index (Position en ligne) comme son troisième (Position en colonne). La fonction Index comprend et s'adapte.

• Inscrire la fonction de recherche de position suivie d'une parenthèse, soit : Equiv(,
• Désigner le commercial cherché en tapant ses coordonnées, soit : B4,

A défaut de pouvoir cliquer sur la cellule étant donné que la formule déborde sur la case, l'option de la saisie des coordonnées est tout à fait judicieuse.

• Enfoncer trois fois la touche F4 du clavier pour la figer seulement en colonne, soit : $B4,

La situation est similaire à la précédente mais les degrés de liberté s'inversent. Au cours de la réplication de la formule sur les lignes du dessous, ce sont bien les autres commerciaux qui doivent être cherchés. Donc, nous libérons la ligne de la cellule. Mais au cours de la réplication de la formule sur les colonnes de droite, c'est toujours le même commercial qui doit être cherché pour les autres mois. Donc, nous figeons sa colonne.

• Taper un point-virgule (;) pour passer dans l'argument de la ligne de recherche,

Nous devons pointer sur la plage de cellules C3:I3 de la bonne feuille. Donc là aussi comme précédemment, nous devons reconstruire cette adresse dynamique et l'interpréter.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Désigner le nom de la feuille en cliquant sur le premier mois en cellule C3,
• Enfoncer deux fois la touche F4 du clavier pour la libérer en colonne, ce qui donne : C$3,

Les raisons sont les mêmes que précédemment.

• Enfoncer la touche 1 en haut à gauche du clavier pour le symbole de concaténation (&),
• Taper un guillemet suivi d'un point d'exclamation, soit : "!,
• Inscrire les coordonnées figées de la ligne commune des commerciaux, soit : $C$3:$I$3,
• Fermer les guillemets puis fermer la parenthèse de la fonction Indirect,

Nous sommes donc de retour dans les arguments de la fonction Equiv.

• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour réaliser une recherche exacte,
• Dès lors, fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Indirect,
• Enfin, valider la formule avec le raccourci clavier CTRL + Entrée,

De cette manière et comme vous le savez, nous conservons la cellule du résultat active pour l'exploiter dans l'enchaînement. La première donnée rapatriée tombe. Et si vous basculez sur la feuille Janvier, vous constatez qu'il s'agit bien des ventes consolidées pour le Vendeur Breye, qui d'ailleurs n'est absolument pas positionné dans la même colonne que celle du tableau de synthèse.

• Tirer la poignée du résultat sur la droite jusqu'en cellule H4,
• Puis, tirer la poignée de la sélection vers le bas jusqu'en cellule H10,

Il n'est effectivement pas possible de tirer une poignée sur la diagonale.



Comme vous pouvez le voir, toutes les données éparpillées à l'origine sont parfaitement réunies. Nous pourrions très bien les consolider en ajoutant une ligne de total, sous le tableau, comme c'est le cas pour chacune des feuilles des mois. L'unique formule de consolidation que nous avons bâtie pour obtenir ce résultat est la suivante :

=INDEX(INDIRECT(C$3 & "!$C$11:$I$11"); EQUIV($B4; INDIRECT(C$3 & "!$C$3:$I$3"); 0))

Bien entendu, si vous modifiez des valeurs dans les tableaux sources, elles sont automatiquement ramenées et consolidées dans le tableau de synthèse.