Regrouper plusieurs feuilles Excel en une seule

Transformer chaque onglet en tableau

La requête lit les tableaux du classeur, si bien que chaque onglet à regrouper doit d’abord devenir un tableau. Le nom de ce tableau devient celui de l’onglet d’origine dans le résultat, d’où l’intérêt de lui donner le nom de sa feuille.

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.

Combiner les tableaux avec Excel.CurrentWorkbook

Excel.CurrentWorkbook renvoie une ligne par tableau du classeur, avec une colonne Content, qui contient le tableau, et une colonne Name, qui porte son nom (Power Query garde ces noms en anglais). En dépliant Content, la requête met ces 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 « Éviter que le tableau regroupé se compte lui-même » dit pourquoi il faut l’écarter.

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, la commande Fermer et charger dans te laisse choisir où arrive le résultat. Un simple clic sur Fermer et charger suit, lui, les options de chargement d’Excel, qui peuvent être réglées pour ne créer qu’une connexion, sans aucune feuille.

Les mêmes étapes tiennent dans la requête ci-dessous, que tu peux coller entière dans l’Éditeur avancé d’une requête vide (onglet Accueil de l’éditeur) avant d’adapter les noms des colonnes aux tiens. Donne-lui ensuite le nom Regroupement (étape 5), sans quoi son filtre n’écarte pas le résultat, puis 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

La colonne Name porte le nom du tableau d’où vient chaque ligne, et non celui de la feuille. Puisque chaque tableau a reçu le nom de son onglet à sa création, il suffit de la renommer pour qu’elle se lise comme l’onglet d’origine.

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

Éviter que le tableau regroupé se compte lui-même

Le résultat chargé est lui aussi un tableau du classeur, qu’Excel.CurrentWorkbook trouve comme les autres, d’où le filtre sur son nom posé à l’étape 3. Sans ce filtre, le résultat s’empile avec les onglets à chaque actualisation. Sur sept lignes, par exemple, chaque actualisation ajoute au regroupement les lignes qu’il contient déjà, et 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 une plage nommée 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.

Pour corriger une requête déjà chargée, ouvre le menu Obtenir des données de l’onglet Données et choisis Lancer l’éditeur Power Query. 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 le propose.

Mettre à jour après un ajout de lignes

Le regroupement reprend les lignes ajoutées à la prochaine actualisation, que tu lances avec le bouton Actualiser tout de l’onglet Données ou avec Ctrl+Alt+F5. Encore faut-il les écrire juste sous un tableau, qui s’agrandit alors pour les prendre. Un nouvel onglet entre de la même façon, dès qu’il porte un tableau aux mêmes en-têtes.

Pour que le regroupement se refasse à 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. Dans l’onglet Utilisation de la boîte qui s’ouvre, coche « Actualiser les données lors de l’ouverture du fichier ».

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

Une référence 3D 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. Avec cette seule référence, ASSEMB.V renvoie alors 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.

• 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. Placé entre les deux, il entre lui-même dans la référence 3D, et la formule relit ses propres cellules. Excel signale alors 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.

Ce que fait la macro

• 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, ce qui reste rapide sur plusieurs milliers de lignes. Elle empile ainsi 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.

Avant et après

La macro réunit ici trois onglets d’agence, Lyon, Paris et Nantes, dans la feuille Consolidation.

Installer la macro

Ses limites

• 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 nombre saisi en texte redevient un nombre, si bien qu’un code postal comme 01000 perd son zéro.

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