Comment faire un tableau récapitulatif de plusieurs feuilles de calcul Excel

Supposons que vous ayez un classeur contenant plusieurs feuilles de calcul et que chaque feuille de calcul ait la même disposition. Et maintenant, vous devez fusionner les multiples feuilles et calculer les résultats finaux dans une feuille de calcul principale. Comment pouvez-vous consolider ou résumer les données de plusieurs feuilles de calcul ou classeurs dans une feuille de calcul principale? Ici, je vais vous présenter quelques astuces rapides pour résoudre ce problème sans copier ni coller.

Le tutoriel couvre deux scénarios les plus courants : la consolidation des données numériques (somme, nombre, moyenne, etc.) et la fusion des feuilles (c'est-à-dire la copie des données de plusieurs feuilles de calcul en une seule).

Table des matières

Consolider les données de plusieurs feuilles de calcul dans une seule feuille de calcul

Combiner les données de plusieurs feuilles de calcul à l'aide de Power Query

Consolider les données de plusieurs feuilles de calcul dans une seule feuille

La façon la plus rapide de consolider des données dans Excel (situées dans un ou plusieurs classeurs) consiste à utiliser la fonction intégrée Excel Consolider.

Prenons l'exemple suivant : supposons que vous ayez un certain nombre de rapports des bureaux régionaux de votre entreprise et que vous souhaitiez consolider ces chiffres dans une feuille de calcul principale afin d'avoir un seul rapport récapitulatif avec les totaux des ventes de tous les produits.

Comme vous le voyez dans la capture d'écran ci-dessous, les trois feuilles de calcul à consolider ont une structure de données similaire, mais un nombre différent de lignes et de colonnes:

Pour consolider les données dans une seule feuille de calcul, procédez comme suit :

1. Organisez correctement les données source : pour que la fonctionnalité Excel Consolider fonctionne correctement, assurez-vous que:

• Chaque plage (ensemble de données) que vous souhaitez consolider réside dans une feuille de calcul distincte. Ne placez aucune donnée sur la feuille sur laquelle vous prévoyez de sortir les données consolidées.
• Chaque feuille a la même disposition, et chaque colonne a un en-tête et contient des données similaires.
• Il n'y a aucune ligne ou colonne vide dans une liste.

2. Exécutez la fonctionnalité Consolider : dans la feuille de calcul principale, cliquez sur la cellule en haut à gauche où vous souhaitez que les données consolidées apparaissent, accédez à l'onglet « Données » et cliquez sur « Consolider ».

Il est conseillé de consolider les données dans une feuille vide. Si votre feuille de calcul principale contient déjà des données, assurez-vous qu'il y a suffisamment d'espace (lignes et colonnes vides) pour contenir les données fusionnées.

3. Configurez les paramètres de consolidation : La fenêtre de dialogue Consolider apparaît, ensuite procédez comme suit :

Dans la zone « Fonction », sélectionnez l'une des fonctions récapitulatives que vous souhaitez utiliser pour consolider vos données (Somme, Moyenne, Max, Min, etc.). Dans cet exemple, je sélectionne Somme.

Dans la zone « Référence », cliquez sur l'icône « Réduire la boîte de dialogue » et sélectionnez la plage dans la première feuille de calcul. Cliquez ensuite sur le bouton « Ajouter » pour ajouter cette plage à toutes les références :

Répétez cette étape pour toutes les plages que vous souhaitez consolider.

Si une ou certaines des feuilles résident dans un autre classeur, cliquez sur le bouton « Parcourir » pour localiser le classeur.

4. Configurez les paramètres de mise à jour : Dans la même fenêtre de dialogue « Consolider », sélectionnez l'une des options suivantes:

• Cochez les cases Ligne du haut et / ou Colonne gauche sous « étiquettes dans » si vous souhaitez que les étiquettes de ligne et / ou de colonne des plages source soient copiées dans la consolidation.
•  Cochez la case « Lier aux données source » si vous souhaitez que les données consolidées soient mises à jour automatiquement chaque fois que les données source changent. Dans ce cas, Excel créera des liens vers vos feuilles de calcul source ainsi qu'un plan comme dans la capture d'écran suivante.

Si vous développez un groupe (en cliquant sur le symbole de contour plus) puis cliquez sur la cellule avec une certaine valeur, un lien vers les données source s'affichera dans la barre de formule.

Comme vous le voyez, la fonctionnalité Excel Consolider est très utile pour rassembler les données de plusieurs feuilles de calcul. Cependant, elle a quelques limitations. En particulier, elle ne fonctionne que pour les valeurs numériques et elle résume toujours ces chiffres d'une manière ou d'une autre (somme, nombre, moyenne, etc.)

Si vous souhaitez fusionner des feuilles dans Excel en copiant leurs données, l'option de consolidation n'est pas la solution. Pour combiner seulement quelques feuilles, vous n'aurez peut-être besoin de rien d'autre que du bon vieux copier / coller. Mais si vous devez fusionner des dizaines de feuilles, les erreurs de copie / collage manuels sont inévitables. Dans ce cas, vous souhaiterez peut-être utiliser la technique suivante pour automatiser la fusion.

Combiner les données de plusieurs feuilles de calcul à l'aide de Power Query

Lorsque vous combinez des données de différentes feuilles à l'aide de Power Query, il est nécessaire d'avoir les données dans un tableau Excel (ou au moins dans des plages nommées). Si les données ne se trouvent pas dans un tableau Excel, la méthode présentée ici ne fonctionnerait pas.

Je vais travailler sur le même exemple :

Chacune de ces feuilles de calcul contient les données dans un tableau Excel et la structure du tableau est cohérente (c'est-à-dire que les en-têtes sont identiques).

Ce type de données est extrêmement facile à combiner à l'aide de Power Query (qui fonctionne très bien avec les données d'Excel Table).

Pour que cette technique fonctionne le mieux, il vaut mieux avoir des noms pour vos tableaux Excel.

J'ai donné aux tableaux les noms suivants: AGA, CAS, RAB.

Voici les étapes pour combiner plusieurs feuilles de calcul à l'aide de Power Query :

5. Accédez à l'onglet « Données »
6. Dans le groupe « Données externes », cliquez sur l'option « à partir d’autres sources » et sélectionnez « Requête vide ». Cela ouvrira l'éditeur Power Query.

7. Dans l'éditeur de requête, tapez la formule suivante dans la barre de formule: =Excel.CurrentWorkbook(). Notez que les formules Power Query sont sensibles à la casse. Vous devez donc utiliser la formule exacte comme mentionné (sinon vous obtiendrez une erreur).

8. Appuyez sur la touche Entrée. Cela vous montrera tous les noms de table dans le classeur entier (il vous montrera également les plages nommées et / ou les connexions au cas où elles existent dans le classeur).

9. Dans cet exemple, je souhaite combiner toutes les tables. Si vous souhaitez combiner uniquement des tableaux Excel spécifiques, vous pouvez cliquer sur l'icône déroulante dans l'en-tête du nom et sélectionner ceux que vous souhaitez combiner. De même, si vous avez des plages ou des connexions nommées et que vous souhaitez uniquement combiner des tables, vous pouvez également supprimer ces plages nommées.
10. Dans la cellule d'en-tête « Contenu », cliquez sur la double flèche pointue.
11. Sélectionnez les colonnes que vous souhaitez combiner. Si vous souhaitez combiner toutes les colonnes, assurez-vous que « (Sélectionner toutes les colonnes) » est coché.

12. Décochez l'option « Utiliser le nom de la colonne d'origine comme préfixe ».
13. Cliquez sur OK.

Les étapes ci-dessus combineront les données de toutes les feuilles de calcul dans une seule table.

Si vous regardez attentivement, vous trouverez que la dernière colonne (la plus à droite) a le nom des tableaux Excel (AGA, CAS et RAB). Il s'agit d'un identifiant qui nous indique quel enregistrement provient de quel tableau Excel. C’est aussi la raison pour laquelle j’ai dit qu’il valait mieux avoir des noms descriptifs pour les tableaux Excel.

Maintenant que vous disposez des données combinées de toutes les feuilles de calcul dans Power Query, vous pouvez les charger dans Excel en tant que nouveau tableau dans une nouvelle feuille de calcul.

Pour ce faire, suivez les étapes ci-dessous:

1. Cliquez sur le bouton « Fermer et charger ».
2. Sélectionnez l’option « Fermer et charger dans ».

3. Dans la boîte de dialogue « Charger dans », sélectionnez les options « Table » et « Nouvelle feuille de calcul ».

4. Cliquez sur OK.

Les étapes ci-dessus combineront les données de toutes les feuilles de calcul et vous afficheront ces données combinées dans une nouvelle feuille de calcul.

Télécharger le fichier Excel de ce tuto