Vos données sont réparties dans plusieurs feuilles de calcul Excel ? Regroupez-les dans un unique fichier : ce sera beaucoup plus simple pour les analyser, les comparer, les traiter, les combiner, les additionner et les consolider !

Parfois, les données d'Excel dont vous avez besoin sont réparties dans plusieurs feuilles ou même plusieurs fichiers. Bien entendu, il est toujours possible de copier et coller les cellules dont vous avez besoin, en les plaçant toutes dans une même feuille. Cependant, selon la quantité de données, l'opération peut vite s'avérer fastidieux. Si vous avez besoin de retrouver ces informations au même endroit, le mieux est encore de rassembler plusieurs feuilles ou classeurs Excel dans un unique document.

Excel facilite grandement la combinaison de plusieurs feuilles dans un nouveau classeur. Une fois vos feuilles rassemblées, son outil Consolider vous aidera même à réaliser des cumuls automatiques de plusieurs tableaux de chiffres qui ont la même structure. Vous trouverez cet outil dans les versions récentes d'Excel pour Windows et Mac, mais pas dans la version gratuite Excel pour le Web.

Regrouper plusieurs feuilles Excel dans un seul fichier

Pour rassembler dans un même classeur Excel des feuilles Excel provenant de différents fichiers, vous aurez le choix : soit recopier ces feuilles de calcul en gardant les fichiers originaux intacts, soit déplacer définitivement ces feuilles dans le classeur de destination. Rappelons que pour Excel, un fichier s'appelle un classeur. Il comporte une ou plusieurs feuilles de calcul, chacune étant représentée par un onglet (Feuil1, Feuil2…) visible au bas de la fenêtre, et que l'on peut renommer, déplacer, colorer, etc.

• Ouvrez le ou les fichiers Excel contenant les feuilles de calcul à déplacer. Si le fichier de destination existe déjà, ouvrez également ce classeur.
• Lorsqu'un classeur comporte plusieurs feuilles à copier ou à déplacer vers un autre classeur, cliquez sur l'onglet de la première feuille, maintenez enfoncée la touche Ctrl sous Windows ou Commande sou macOS puis cliquez sur chaque onglet souhaité. Les onglets sélectionnés passent en blanc et sont soulignés en vert.

• Si les onglets à sélectionner se suivent, il y a plus simple : cliquez sur le premier onglet, pressez la touche Maj et cliquez sur le dernier onglet. Vous voulez retirer un onglet de la sélection ? Pressez la touche Ctrl ou Commande et cliquez dessus : l'onglet redevient gris.

• Une fois la sélection de feuilles effectuée dans un classeur, cliquez avec le bouton droit de la souris sur l'un des onglets sélectionnés et choisissez Déplacer ou copier.

• Dans la fenêtre Déplacer ou copier, si le fichier Excel de destination n'existe pas encore, sélectionnez (nouveau classeur) pour en créer un. Si le classeur existe, il doit être ouvert pour pouvoir le sélectionner dans la liste.

• Si le classeur de destination existe, Excel vous présente la liste de ses onglets afin que vous précisiez où les feuilles en cours de copie/déplacement y seront insérées : cliquez sur (en dernier) ou sur le nom d'une feuille pour insérer les feuilles juste avant.

• N'oubliez pas aussi de cocher la case Créer une copie, à moins de vouloir déplacer les feuilles sélectionnées, donc les supprimer de leur classeur d'origine. Pressez le bouton OK.
• Répétez cette opération pour tous les autres classeurs où sont stockées des feuilles à regrouper dans le classeur de destination.
• Une fois le regroupement des feuilles terminé, dans le classeur de destination, renommez éventuellement vos feuilles d'un double clic sur leur onglet.

• Pour déplacer un onglet dans un même classeur, attrapez son onglet à la souris et faites-le glisser latéralement vers sa destination : durant la glissade, un petit triangle noir vous indique où l'onglet va s'intercaler quand vous relâcherez le bouton de la souris.

• Si la fenêtre d'Excel n'est pas assez large pour afficher tous les onglets du classeur, le coin inférieur gauche de la fenêtre affiche des triangles de navigation (visibles en vert ci-dessus). Un clic du bouton droit dans cette zone fait également apparaître la liste des onglets dans une mini fenêtre de sélection.

Consolider les données de plusieurs feuilles Excel

Regrouper, fusionner, additionner, combiner des données… Quand les données à cumuler sont comparables d'un tableau à l'autre, les comptables, eux, préfèrent le terme "consolider". Une maison mère consolide, par exemple, les données de ses filiales pour estimer le résultat total du groupe.

Nous allons utiliser ici la commande Consolider d'Excel, très simple à prendre en main pour calculer, par exemple, la somme de plusieurs tableaux dans un tableau récapitulatif. Précisons qu'Excel propose d'autres méthodes (plus puissantes, mais moins accessibles) pour effectuer ces regroupements, notamment les tableaux croisés dynamiques et, avec les versions récentes du tableur, l'éditeur Power Query.

Avec la commande Consolider, les données de chaque liste à combiner doivent respecter une certaine logique. Si vous consolidez régulièrement des tableaux, vous vous éviterez des problèmes en créant un modèle de feuille de calcul à remplir.

Avant toute chose, donc, jetez un coup d'œil à vos tableaux à consolider. Si le même type de données apparaît toujours à la même position dans chaque liste – la première colonne de valeurs concerne par exemple toujours les ventes de smartphones, la deuxième colonne les ventes de téléviseurs, etc. –, Excel peut effectuer une consolidation par position. Peu importe si les étiquettes (Smartphones, Téléviseurs…) ne sont pas écrites de manière strictement identique puisqu'il n'en tiendra pas compte.

Si les étiquettes des lignes et colonnes sont identiques d'une liste à l'autre, cela permet à Excel de savoir quelles données doivent être cumulées. Microsoft parle de consolidation par catégorie. Les étiquettes de lignes et colonnes n'ont pas nécessairement besoin de figurer à la même place.

Sur l'exemple ci-dessous, dans les feuilles Paris et Lyon, les colonnes Téléviseurs et Ordinateurs sont inversées, ce qui n'empêche pas la consolidation par catégorie (tableau vert au premier plan) d'effectuer les bons cumuls.

En revanche, si les étiquettes des tableaux ne sont pas strictement identiques dans une consolidation par catégorie – ci-dessous, la troisième colonne s'appelle Téléviseurs dans le tableau de Paris et TV dans le tableau de Marseille –, alors les deux colonnes seront bien reportées dans la liste consolidée (tableau vert au premier plan) mais aucun cumul ne sera réalisé pour ces colonnes.

Voici comment consolider vos tableaux de données avec Excel pour Windows et pour Mac.

• Il est préférable d'ouvrir les fichiers Excel contenant les tableaux à consolider. Les tableaux de données à consolider ne doivent comporter aucune ligne ou colonne vide.
• Dans la feuille de destination, cliquez sur une cellule vide. Ce sera la première cellule (coin supérieur gauche) du tableau que va générer la consolidation. Laissez suffisamment de cellules vides à droite et en dessous.
• Dans l'onglet Données, groupe Outils de données, cliquez sur Consolider.

• La fenêtre Consolider vous permet d'indiquer les références des tableaux à combiner. Si l'un des tableaux se trouve dans un classeur non ouvert, cliquez sur le bouton Parcourir. Excel inscrit le nom du fichier dans la zone Référence, mais vous devrez taper manuellement la référence de la plage de cellules. Mieux vaut donc ouvrir au préalable ce classeur !

• Si la feuille se trouve dans le classeur en cours d'utilisation ou dans un classeur actuellement ouvert dans Excel, cliquez dans la zone Référence puis sur l'onglet de la feuille à retenir. Sélectionnez à la souris ou au clavier la plage de cellules à consolider et pressez le bouton Ajouter.

• Répétez l'opération pour chaque feuille comportant un tableau participant à la consolidation : un clic dans la zone Référence > sélection d'une plage de cellules dans une feuille du classeur > bouton Ajouter.
• Dans la liste déroulante Fonction, sélectionnez ensuite le type d'opération à réaliser : Somme, Moyenne, etc.

• Si les données se trouvent toutes exactement au même endroit dans chaque tableau et que vous ne cochez pas au moins l'une des deux cases Ligne du haut et Colonne de gauche, la consolidation produira un tableau de valeurs numériques cumulées sans aucune étiquette pour les lignes et les colonnes. Ce qui ne facilite pas la lecture !

• Plus probable : si vous souhaitez une consolidation par catégorie (les étiquettes de lignes et colonnes à cumuler doivent être écrites exactement de la même façon dans chaque tableau), cochez les cases Ligne du haut et/ou Colonne de gauche.

• Si vous ne cochez pas la case Lier aux données source, seuls les résultats s'inscriront dans le tableau de synthèse. Vous devrez donc relancer la consolidation si les données des tableaux sources changent.
• Si vous cochez la case Lier aux données source, Excel conserve un lien vers les données d'origine (grâce à des formules) et met à jour le résultat consolidé à chaque changement de valeur dans les références sources.
• Pressez le bouton OK pour lancer la consolidation.
• Ci-dessous, nous avons coché Ligne du haut et Colonne de gauche, et décoché Lier aux données source. En cellule B4, par exemple, Excel a inscrit le total des valeurs Smartphones pour le mois de Janvier des trois feuilles Lyon, Marseille et Paris (410=173+150+87). Aucune modification dans les feuilles d'origine ne sera répercutée, mais on relance facilement une consolidation : Excel conserve en mémoire les plages de références et permet d'en ajouter/supprimer.

• Ci-dessous, voici le résultat de la même consolidation par catégorie, mais en cochant la case Lier aux données source. Excel crée un mode Plan et masque les lignes de détail servant au calcul des données consolidées.

• Si vous cliquez par exemple sur le signe [+] à gauche du mois de Janvier, le [+] se transforme en [-] et vous affichez au-dessus (lignes 4 à 6) le détail des ventes de Smartphones pour le mois de Janvier, pour les feuilles Lyon, Marseille et Paris ; la somme de ces ventes figure en cellule C7. La cellule C4, elle, contient par exemple la formule =Lyon!$B$4 et se mettra donc à jour si cette valeur change. La colonne B a été ajoutée par Excel, elle indique le nom du fichier (classeur) où se trouve cette ligne de données (Ventes Totales France.xlsx sur notre exemple).

• Si vous cliquez en haut sur le bouton [2] du mode Plan, vous affichez le détail de tous les mois, comme on le voit sur l'illustration ci-dessous. Vous masquerez à nouveau ces lignes de détails en cliquant sur [1], c'est-à-dire pour n'afficher que le niveau 1 du mode Plan.

• Si cette présentation vous déplaît, le mode Plan se désactive dans l'onglet Données > bouton Dissocier > Effacer le plan. L'opération ne peut pas être annulée (Ctrl+Z est inactif). Au besoin, pour recréer ce même plan, sélectionnez l'une de ses cellules et cliquez sur le bouton Grouper > Plan automatique.

Utiliser une formule pour consolider des données

Si vous préférez effectuer vous-même la consolidation en écrivant des formules, voici quelques pistes.

• Une formule peut faire référence à des cellules situées dans d'autres feuilles ou classeurs. S'il s'agit d'un autre classeur, le nom du classeur s'écrit entre crochets. Le nom de la feuille s'inscrit juste après, suivi d'un point d'exclamation puis de la référence aux cellules. Des apostrophes encadrent les noms de classeur et de feuille si ces noms comportent des espaces.
• Par exemple ='[Ventes France.xlsx]Marseille'!B4+'[Ventes Espagne.xlsx]Madrid'!C15 fait référence à la cellule B4 de la feuille Marseille du classeur Ventes France, ainsi qu'à la cellule C15 de la feuille Madrid située dans le classeur Ventes Espagne.
• Cette formule pourrait également s'écrire
  =SOMME('[Ventes France.xlsx]Marseille'!B4;'[Ventes Espagne.xlsx]Madrid'!C15)
• Pour éviter d'avoir à taper ces références, ouvrez d'abord les classeurs "sources" en question. Dans le classeur où doit figurer la consolidation, tapez le signe égal (=) pour débuter votre formule, inscrivez éventuellement un nom de fonction (SOMME( ou autre) puis cliquez dans un classeur source sur la bonne feuille et la bonne cellule : Excel inscrit pour vous la référence. Poursuivez en tapant un point-virgule et en sélectionnant la valeur suivante qui doit figurer dans la formule.
• Si toutes les feuilles à consolider se trouvent dans le même classeur, la formule s'écrira par exemple plus simplement :
  =SOMME(Paris!B4;Marseille!B4;Lyon!B4;'La Baule'!B4)
• Si toutes les feuilles à consolider respectent exactement le même modèle, vous pouvez utiliser une "référence 3D" pour faire référence à la même cellule des différentes feuilles. Ci-dessus, notre formule fait toujours référence à la cellule B4, quelle que soit la feuille, car nos tableaux sont identiques. Donc si les onglets des feuilles se suivent, cette formule pourrait s'écrire :
  =SOMME('Paris:La Baule'!B4)
• Inutile de la taper in extenso : commencez par taper =SOMME( puis cliquez sur le premier onglet (Paris), pressez la touche Maj, cliquez sur le dernier onglet (La Baule), relâchez la touche Maj et cliquez enfin sur la bonne cellule (B4).

• Si la formule donne le résultat escompté, copiez-collez-la dans les cellules adjacentes pour étendre la consolidation à tout le tableau.
• Les résultats du copier-coller sont incohérents ? Dans la formule que vous copiez, les références aux cellules doivent être "relatives", et non "absolues". Autrement dit, retirez le signe $ s'il apparaît dans une référence à une formule.
• Écrivez par exemple =SOMME('Paris:La Baule'!B4) au lieu de =SOMME('Paris:La Baule'!$B$4) avant de copier-coller cette formule.
• Vous avez transformé vos listes de données sources en "tableaux" Excel (onglet Accueil > Mettre sous forme de tableau) ? Bonne idée ! D'abord parce qu'Excel se sert de vos étiquettes pour nommer vos colonnes (Smartphones, Téléviseurs, etc.), ce qui facilite la rédaction et la compréhension des formules.

•  C'est encore mieux si vous renommez chaque tableau avec un nom plus évocateur que celui donné par Excel (par défaut Tableau1, Tableau2, etc.). Cliquez sur une cellule de ce tableau, puis sur l'onglet Création de tableau dans Windows (onglet Tableau sur Mac), et tapez un nom de tableau en haut à gauche, TabParis sur l'exemple ci-dessous.

• Après avoir créé et renommé les trois tableaux (TabLyon, TabMarseille, TabParis) : dans la feuille de consolidation, si on tape =SOMME puis que l'on va cliquer sur la première valeur numérique en haut à gauche de chaque tableau, c'est-à-dire ici la cellule B4 (en séparant comme d'habitude chaque référence par un point-virgule), Excel inscrit la formule suivante :
  =SOMME(TabParis[@Smartphones];TabMarseille[@Smartphones];TabLyon[@Smartphones])

• En recopiant cette formule dans les cellules à gauche et en dessous, Excel adapte tout seul la formule ! Recopiée en C4, par exemple, la formule ne fait plus référence aux Smartphones mais aux Téléviseurs et devient :
  =SOMME(TabParis[@Téléviseurs];TabMarseille[@Téléviseurs];TabLyon[@Téléviseurs])

• Idem pour la colonne D qui fait référence aux Ordinateurs, et la colonne E aux Accessoires. Avec la commande Mettre sous forme de tableau, Excel vous offre vraiment un outil très puissant.