J'ai récemment reçu une question d'un lecteur sur la combinaison de plusieurs feuilles de calcul dans le même classeur en une seule feuille de calcul.
Je lui ai demandé d'utiliser Power Query pour combiner différentes feuilles, mais j'ai réalisé que pour quelqu'un qui débute avec Power Query, cela peut être difficile.
J'ai donc décidé d'écrire ce didacticiel et de montrer les étapes exactes pour combiner plusieurs feuilles en une seule table à l'aide de Power Query.
Ci-dessous une vidéo où je montre comment combiner les données de plusieurs feuilles/tables à l'aide de Power Query :
Vous trouverez ci-dessous des instructions écrites sur la façon de combiner plusieurs feuilles (au cas où vous préféreriez un texte écrit à une vidéo).
Remarque : Power Query peut être utilisé comme complément dans Excel 2010 et 2013 et est une fonctionnalité intégrée à partir d'Excel 2016. En fonction de votre version, certaines images peuvent sembler différentes (les captures d'images utilisées dans ce didacticiel proviennent d'Excel 2016).
Combiner les données de plusieurs feuilles de calcul à l'aide de Power Query
Lors de la combinaison de 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 sont pas dans un tableau Excel, la méthode indiquée ici ne fonctionnerait pas.
Supposons que vous ayez quatre feuilles différentes - Est, Ouest, Nord et Sud.
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 les mêmes).
Cliquez ici pour télécharger les données et suivre.
Ce type de données est extrêmement facile à combiner à l'aide de Power Query (qui fonctionne très bien avec les données du tableau Excel).
Pour que cette technique fonctionne au mieux, il est préférable d'avoir des noms pour vos tableaux Excel (fonctionnez sans cela aussi, mais c'est plus facile à utiliser lorsque les tableaux sont nommés).
J'ai donné aux tables les noms suivants : East_Data, West_Data, North_Data et South_Data.
Voici les étapes pour combiner plusieurs feuilles de calcul avec des tableaux Excel à l'aide de Power Query :
- Allez dans l'onglet Données.
- Dans le groupe Obtenir et transformer des données, cliquez sur l'option « Obtenir des données ».
- Allez dans l'option « À partir d'autres sources ».
- Cliquez sur l'option « Requête vierge ». Cela ouvrira l'éditeur Power Query.
- Dans l'éditeur de requête, saisissez la formule suivante dans la barre de formule : =Excel.Cahier de travail actuel(). Notez que les formules Power Query sont sensibles à la casse, vous devez donc utiliser la formule exacte comme mentionné (sinon vous obtiendrez une erreur).
- Appuyez sur la touche Entrée. Cela vous montrera tous les noms de table dans l'ensemble du classeur (il vous montrera également les plages nommées et/ou les connexions au cas où il existerait dans le classeur).
- [Étape facultative] Dans cet exemple, je veux 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 nommé des plages ou des connexions et que vous souhaitez uniquement combiner des tables, vous pouvez également supprimer ces plages nommées.
- Dans la cellule d'en-tête Contenu, cliquez sur la flèche à double pointe.
- 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é.
- Décochez l'option "Utiliser le nom de colonne d'origine comme préfixe".
- Cliquez sur OK.
Les étapes ci-dessus combineraient les données de toutes les feuilles de calcul dans un seul tableau.
Si vous regardez attentivement, vous constaterez que la dernière colonne (la plus à droite) porte le nom des tableaux Excel (East_Data, West_Data, North_Data et South_Data). 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 était préférable d'avoir des noms descriptifs pour les tableaux Excel.
Voici quelques modifications que vous pouvez apporter aux données combinées dans Power Query lui-même :
- Faites glisser et placez la colonne Nom au début.
- Supprimez le "_Data" de la colonne du nom (il vous reste donc Est, Ouest, Nord et Sud dans la colonne du nom). Pour ce faire, cliquez avec le bouton droit sur l'en-tête Nom et cliquez sur Remplacer les valeurs. Dans la boîte de dialogue Remplacer les valeurs, remplacez _Data par un espace.
- Modifiez la colonne Données pour afficher uniquement les dates (et non l'heure). Pour ce faire, cliquez sur l'en-tête de la colonne Date, accédez à l'onglet « Transformer » et modifiez le type de données en date.
- Renommez la requête en ConsolidatedData.
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 faire ça. suivez les étapes ci-dessous :
- Cliquez sur l'onglet "Fichier".
- Cliquez sur Fermer et charger vers.
- Dans la boîte de dialogue Importer des données, sélectionnez les options Table et Nouvelle feuille de calcul.
- Cliquez sur OK.
Les étapes ci-dessus combineraient les données de toutes les feuilles de calcul et vous donneraient ces données combinées dans une nouvelle feuille de calcul.
Un problème que vous devez résoudre lorsque vous utilisez cette méthode
Si vous avez utilisé la méthode ci-dessus pour combiner tous les tableaux du classeur, vous risquez de rencontrer un problème.
Voir le nombre de lignes des données combinées - 1304 (ce qui est juste).
Maintenant, si j'actualise la requête, le nombre de lignes passe à 2607. Actualisez à nouveau et il passera à 3910.
Voici le problème.
Chaque fois que vous actualisez la requête, elle ajoute tous les enregistrements des données d'origine aux données combinées.
Remarque : vous ne rencontrerez ce problème que si vous avez utilisé Power Query pour combiner TOUS LES TABLEAUX EXCEL dans le classeur. Si vous avez sélectionné des tables spécifiques à combiner, vous ne rencontrerez pas ce problème.Comprenons la cause de ce problème et comment y remédier.
Lorsque vous actualisez une requête, elle revient en arrière et suit toutes les étapes que nous avons suivies pour combiner les données.
Dans l'étape où nous avons utilisé la formule =Excel.CurrentWorkbook(), il nous a donné une liste de toutes les tables. Cela a bien fonctionné la première fois car il n'y avait que quatre tables.
Mais lorsque vous actualisez, il y a cinq tables dans le classeur - y compris la nouvelle table que Power Query a insérée où nous avons les données combinées.
Ainsi, chaque fois que vous actualisez la requête, en plus des quatre tableaux Excel que nous souhaitons combiner, cela ajoute également la table de requête existante aux données résultantes.
C'est ce qu'on appelle la récursivité.
Voici comment résoudre ce problème.
Une fois que vous avez inséré =Excel.CurrentWorkbook() dans la barre de formule de Power Query et appuyé sur Entrée, vous obtenez une liste de tableaux Excel. Pour vous assurer de ne combiner que les tables de la feuille de calcul, vous devez en quelque sorte filtrer uniquement les tables que vous souhaitez combiner et supprimer tout le reste.
Voici les étapes à suivre pour vous assurer que vous n'avez que les tables requises :
- Cliquez sur la liste déroulante et placez le curseur sur Filtres de texte.
- Cliquez sur l'option Contient.
- Dans la boîte de dialogue Filtrer les lignes, saisissez _Data dans le champ à côté de l'option « contient ».
- Cliquez sur OK.
Vous ne verrez peut-être aucun changement dans les données, mais cela empêchera la table résultante d'être à nouveau ajoutée lorsque la requête est actualisée.
Notez que dans les étapes ci-dessus, nous avons utilisé "_Données” pour filtrer car nous avons nommé les tables de cette façon. Mais que se passe-t-il si vos tables ne sont pas nommées de manière cohérente. Que faire si tous les noms de table sont aléatoires et n'ont rien en commun.
Voici la façon de résoudre ce problème : utilisez le filtre « n'est pas égal » et entrez le nom de la requête (qui serait ConsolidatedData dans notre exemple). Cela garantira que tout reste le même et que la table de requête résultante qui est créée est filtrée.
Outre le fait que Power Query rend tout ce processus de combinaison de données de différentes feuilles (ou même de la même feuille) assez facile, un autre avantage de son utilisation est qu'il le rend dynamique. Si vous ajoutez d'autres enregistrements à l'une des tables et actualisez la Power Query, les données combinées vous seront automatiquement fournies.Remarque importante : dans l'exemple utilisé dans ce didacticiel, les en-têtes étaient les mêmes. Si les en-têtes sont différents, Power Query combinera et créera toutes les colonnes de la nouvelle table. Si les données sont disponibles pour cette colonne, elles seront affichées, sinon elles afficheront null.
Vous aimerez peut-être également les didacticiels Power Query suivants :
- Combinez les données de plusieurs classeurs dans Excel (à l'aide de Power Query).
- Comment annuler le pivotement des données dans Excel à l'aide de Power Query (aka Get & Transform)
- Obtenir une liste des noms de fichiers à partir des dossiers et sous-dossiers (à l'aide de Power Query)
- Fusionner des tableaux dans Excel à l'aide de Power Query.
- Comment comparer deux feuilles/fichiers Excel
