Regrouper plusieurs feuilles Excel en une seule

Transformer chaque feuille en tableau nommé avec Ctrl+L

Pour que Power Query trouve tes feuilles, transforme les données de chacune en tableau avec Ctrl+L, puisque la requête lit la liste des tableaux du classeur. Donne ensuite à chaque tableau le nom de sa feuille, par exemple Paris, car c’est ce nom qui désignera l’onglet d’origine de chaque ligne dans le résultat.

1. 01

   Clique dans les données du premier onglet, puis appuie sur Ctrl+L, ou choisis Tableau dans l’onglet Insertion.

   

2. 02

   Laisse cochée la case « Mon tableau comporte des en-têtes », puis valide par OK.

3. 03

   Dans l’onglet Création de tableau, remplace le nom proposé dans la case Nom du tableau par le nom de l’onglet, par exemple Paris.

4. 04

   Refais ces trois gestes sur chacun des onglets à regrouper.

Empiler les tableaux du classeur avec Excel.CurrentWorkbook

Pour empiler les tableaux du classeur, une requête vide appelle Excel.CurrentWorkbook, qui renvoie une ligne par tableau avec deux colonnes. Content contient le tableau lui-même, et Name porte son nom, deux intitulés que Power Query garde en anglais, même dans un Excel en français. En dépliant Content, la requête met ensuite les tableaux bout à bout, et la colonne Name reste en face de chacune de leurs lignes.

1. 01

   Dans l’onglet Données, ouvre Obtenir des données, puis À partir d’autres sources, et choisis Requête vide. L’éditeur Power Query s’ouvre.

   

2. 02

   Dans la barre de formule de l’éditeur, remplace ce qui s’y trouve par = Excel.CurrentWorkbook() puis appuie sur Entrée pour afficher la liste des tableaux du classeur. Si la barre de formule manque, coche Barre de formule dans l’onglet Affichage de l’éditeur.

   

3. 03

   Ouvre le filtre de la colonne Name, choisis Filtres textuels puis N’est pas égal à, tape Regroupement et valide. C’est le nom que portera le résultat, et la sous-partie « Pourquoi le regroupement se remplit de doublons » explique ce qui arrive sans ce filtre.

4. 04

   Clique sur la double flèche à droite de l’en-tête Content, décoche « Utiliser le nom de la colonne d’origine comme préfixe », puis valide par OK. Les lignes de tous les tableaux s’empilent.

5. 05

   Dans le volet Paramètres d’une requête, à droite, renomme la requête Regroupement.

6. 06

   Renomme la colonne Name en Onglet, place-la en premier et donne le type Date à la colonne des dates, comme l’explique la sous-partie suivante.

7. 07

   Dans l’onglet Accueil, ouvre la flèche de Fermer et charger et choisis Fermer et charger dans. Dans la boîte Importation de données, choisis Tableau et Nouvelle feuille de calcul, puis valide. Le résultat arrive dans une nouvelle feuille.

À l’étape 7, c’est la commande Fermer et charger dans qui te laisse choisir où arrive le résultat. Un simple clic sur Fermer et charger suit en effet les options de chargement d’Excel, et si elles sont réglées pour ne créer qu’une connexion, aucune feuille n’apparaît.

Si tu préfères coller du code plutôt que cliquer, les mêmes étapes tiennent dans la requête ci-dessous. Ouvre une requête vide, clique sur Éditeur avancé dans l’onglet Accueil de l’éditeur, puis colle la requête entière avant d’adapter les noms des colonnes aux tiens. Donne-lui ensuite le nom Regroupement, comme à l’étape 5, sans quoi son filtre n’écarte pas le résultat, et charge-la comme à l’étape 7.

let
    Source = Excel.CurrentWorkbook(),
    #"Lignes filtrées" = Table.SelectRows(Source, each [Name] <> "Regroupement"),
    #"Content développé" = Table.ExpandTableColumn(#"Lignes filtrées", "Content", {"Date", "Client", "Montant"}, {"Date", "Client", "Montant"}),
    #"Colonnes renommées" = Table.RenameColumns(#"Content développé", {{"Name", "Onglet"}}),
    #"Colonnes permutées" = Table.ReorderColumns(#"Colonnes renommées", {"Onglet", "Date", "Client", "Montant"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonnes permutées", {{"Onglet", type text}, {"Date", type date}, {"Client", type text}, {"Montant", type number}})
in
    #"Type modifié"

Garder le nom de l’onglet d’origine de chaque ligne

Pour garder le nom de l’onglet d’origine de chaque ligne, il suffit de renommer la colonne Name, puisque chaque tableau a reçu le nom de son onglet à sa création. Name porte en effet le nom du tableau d’où vient la ligne, et non celui de la feuille. Un tableau resté sous le nom proposé par Excel, Tableau1 par exemple, apparaîtrait donc sous ce nom dans le résultat.

À l’étape 6, double-clique sur l’en-tête Name pour l’appeler Onglet, puis fais glisser la colonne en première position. Profites-en pour donner le type Date à la colonne des dates, avec l’icône placée à gauche de son en-tête, faute de quoi elles peuvent arriver en nombres, 46027 au lieu du 05/01/2026. Si le résultat est déjà chargé, rouvre d’abord la requête comme l’explique la sous-partie suivante, puis fais ces gestes.

Pourquoi le regroupement se remplit de doublons

Le regroupement se remplit de doublons dès que son propre tableau entre dans la requête, puisque le résultat chargé est lui aussi un tableau du classeur, qu’Excel.CurrentWorkbook trouve comme les autres. Sans le filtre posé sur son nom à l’étape 3, le résultat s’empile donc avec les onglets à chaque actualisation. Avec sept lignes à regrouper, par exemple, chaque actualisation ajoute au regroupement les lignes qu’il contient déjà, si bien que le tableau grossit de sept lignes à chaque fois.

• Une plage nommée figure aussi dans la liste, au même titre que les tableaux, et elle peut y ajouter une ligne vide à son nom. Écarte ce qui n’est pas à regrouper par un second filtre sur Name.

• Un tableau posé sur un onglet masqué est regroupé lui aussi.

Si ta requête est déjà chargée sans ce filtre, rouvre-la en choisissant Lancer l’éditeur Power Query dans le menu Obtenir des données de l’onglet Données. Double-clique ensuite sur la requête dans le volet de gauche, puisqu’un simple clic ne fait que la sélectionner. Dans la liste Étapes appliquées, sélectionne l’étape Source, pose le filtre sur Name, puis accepte d’insérer l’étape quand l’éditeur te le propose.

Actualiser la requête après un ajout de lignes ou d’onglets

Après un ajout de lignes sur un onglet, actualise la requête avec le bouton Actualiser tout de l’onglet Données ou avec Ctrl+Alt+F5, et le regroupement les reprend. Encore faut-il écrire ces lignes juste sous un tableau, qui s’agrandit alors pour les prendre. Un nouvel onglet entre de la même façon dans le résultat, dès qu’il porte un tableau aux mêmes en-têtes que les autres.

Et si tu oublies souvent d’actualiser, demande à Excel de refaire le regroupement à chaque ouverture du classeur. Sélectionne une cellule du résultat, ouvre la flèche d’Actualiser tout et choisis Propriétés de connexion, puis coche « Actualiser les données lors de l’ouverture du fichier » dans l’onglet Utilisation de la boîte qui s’ouvre.

Regrouper tous les onglets d’un coup avec une référence 3D

Pour regrouper tous les onglets d’un coup, donne à ASSEMB.V une seule référence 3D, qui vise la même plage sur tous les onglets rangés entre deux onglets. Paris:Nantes!A2:C100 lit ainsi Paris, Lyon et Nantes jusqu’à la ligne 100, ce qui laisse de la place aux lignes à venir. Avec cette seule référence, pourtant, ASSEMB.V renvoie aussi les lignes vides sous forme de zéros, soit 297 lignes (99 par onglet) quand seules 7 sont remplies. FILTRE les écarte en ne gardant que les lignes dont la colonne A est remplie.

Pour garder les en-têtes au-dessus du résultat, place la ligne 1 d’un des onglets en premier argument d’ASSEMB.V. Les en-têtes de Paris coiffent alors les sept lignes empilées, comme dans la formule ci-dessous.

• Un onglet glissé entre Paris et Nantes entre dans le résultat, et un onglet placé après Nantes en sort.

• Range l’onglet du résultat après Nantes, puisqu’un onglet placé entre les deux entre lui-même dans la référence 3D. La formule relit alors ses propres cellules, et Excel signale une référence circulaire.

• Si tes onglets portent des tableaux du même nom, comme le demande la méthode Power Query, la référence 3D peut renvoyer l’erreur #VALEUR!. L’erreur vient dès qu’un tableau porte le nom du premier onglet de la référence. Passe alors directement les noms des tableaux à ASSEMB.V, comme dans la formule ci-dessous, qui suit aussi les lignes ajoutées aux tableaux.

Recopier tous les onglets dans une feuille Consolidation

• Elle crée la feuille Consolidation à la fin du classeur, ou vide celle qui existe déjà.

• Elle reprend les en-têtes du premier onglet rempli, précédés d’une colonne Feuille source.

• Elle écrit en face de chaque ligne le nom de l’onglet d’où elle vient.

• Elle recopie chaque onglet en un seul bloc, si bien qu’elle reste rapide sur plusieurs milliers de lignes et empile 6 000 lignes d’un coup.

• Elle saute les onglets vides et ne modifie pas les onglets de départ.

• Elle annonce à la fin le nombre d’onglets et de lignes regroupés.

Trois onglets d’agence avant et après la macro

Avant la macro, les agences de Lyon, de Paris et de Nantes ont chacune leur onglet, avec deux clients et leurs montants sous les en-têtes Client et Montant. Après, la feuille Consolidation empile ces six lignes sous les mêmes en-têtes, précédés d’une colonne Feuille source qui nomme l’onglet de chacune.

Installer la macro qui fusionne les feuilles

Connaître les limites de la macro avant de la lancer

• Elle empile tous les onglets du classeur, y compris les onglets masqués. Pour en laisser de côté, comme un onglet de synthèse fait avec ASSEMB.V ou Power Query, enregistre une copie du classeur, supprimes-y ces onglets, puis lance la macro sur cette copie.

• Elle prend les colonnes dans leur ordre, sans lire les en-têtes des autres onglets. Une colonne déplacée sur un onglet envoie ses valeurs sous le mauvais en-tête.

• Elle prend la première ligne remplie de chaque onglet pour ses en-têtes. Un titre posé au-dessus d’un tableau fait entrer la ligne vide et les en-têtes de cet onglet parmi les données.

• Une cellule mise en forme loin sous les données ajoute des lignes vides au nom de l’onglet.

• Les formules arrivent en valeurs, et les formats de nombre se perdent. Un montant en euros n’a plus son symbole et 25 % devient 0,25, alors que les dates restent affichées en dates.

• Un texte qui ressemble à un nombre ou à une date, comme le code postal 01000, reste un texte, puisque la macro lui rend l’apostrophe qui le garde tel quel.

• Une feuille Consolidation déjà présente est vidée puis réécrite, y compris ce que tu y as ajouté à la main, et Ctrl+Z ne la ramène pas.

• Le résultat ne suit pas les onglets, et une modification n’y entre qu’en relançant la macro.