Combiner les données de plusieurs classeurs dans Excel (à l'aide de Power Query)

Power Query peut être d'une grande aide lorsque vous souhaitez combiner plusieurs classeurs dans un seul classeur.

Par exemple, supposons que vous ayez les données de ventes pour différentes régions (Est, Ouest, Nord et Sud). Vous pouvez combiner ces données de différents classeurs dans une seule feuille de calcul à l'aide de Power Query.

Si vous avez ces classeurs dans différents emplacements/dossiers, c'est une bonne idée de les déplacer tous dans un seul dossier (ou de créer une copie et de placer cette copie de classeur dans le même dossier).

Donc, pour commencer, j'ai quatre classeurs dans un dossier (comme indiqué ci-dessous).

Maintenant, dans ce didacticiel, je couvre trois scénarios dans lesquels vous pouvez combiner les données de différents classeurs à l'aide de Power Query :

• Chaque classeur contient les données dans un tableau Excel et tous les noms de table sont identiques.
• Chaque classeur contient les données avec le même nom de feuille de calcul. Cela peut être le cas lorsqu'il y a une feuille nommée « résumé » ou « données » dans tous les classeurs et que vous souhaitez combiner tout cela.
• Chaque classeur contient de nombreuses feuilles et tableaux, et vous souhaitez combiner des tableaux/feuilles spécifiques. Cette méthode peut également être utile lorsque vous souhaitez combiner des tableaux/feuilles qui n'ont pas un nom cohérent.

Voyons comment combiner les données de ces classeurs dans chaque cas.

Chaque classeur a les données dans un tableau Excel avec la même structure

La technique ci-dessous fonctionnerait lorsque vos tableaux Excel ont été structurés de la même manière (mêmes noms de colonnes).

Le nombre de lignes de chaque table peut varier.

Ne vous inquiétez pas si certains des tableaux Excel ont des colonnes supplémentaires. Vous pouvez choisir l'un des tableaux comme modèle (ou comme « clé » comme l'appelle Power Query), et Power Query l'utiliserait pour combiner tous les autres tableaux Excel avec lui.

S'il y a des colonnes supplémentaires dans d'autres tables, celles-ci seront ignorées et seules celles spécifiées dans le modèle/la clé seront combinées. Par exemple, si la table modèle/clé que vous sélectionnez comporte 5 colonnes et que l'une des tables d'un autre classeur comporte 2 colonnes supplémentaires, ces colonnes supplémentaires seront ignorées.

J'ai maintenant quatre classeurs dans un dossier que je veux combiner.

Vous trouverez ci-dessous un aperçu du tableau que j'ai dans l'un des classeurs.

Voici les étapes pour combiner les données de ces classeurs dans un seul classeur (en tant que table unique).

1. Allez dans l'onglet Données.
2. Dans le groupe Obtenir et transformer, cliquez sur le menu déroulant Nouvelle requête.
3. Passez votre curseur sur « À partir du fichier » et cliquez sur « À partir du dossier ».
4. Dans la boîte de dialogue Dossier, entrez le chemin d'accès au dossier contenant les fichiers ou cliquez sur Parcourir et localisez le dossier.
5. Cliquez sur OK.
6. Dans la boîte de dialogue qui s'ouvre, cliquez sur le bouton Combiner.
7. Cliquez sur « Combiner et charger ».
8. Dans la boîte de dialogue « Combiner les fichiers » qui s'ouvre, sélectionnez le tableau dans le volet de gauche. Notez que Power Query vous montre la table du premier fichier. Ce fichier servirait de modèle (ou de clé) pour combiner d'autres fichiers. Power Query rechercherait maintenant « Table 1 » dans d'autres classeurs et le combinerait avec celui-ci.
9. Cliquez sur OK.

Cela chargera le résultat final (données combinées) dans votre feuille de calcul active.

Notez qu'avec les données, Power Query ajoute automatiquement le nom du classeur comme première colonne des données combinées. Cela aide à garder une trace des données provenant de quel classeur.

Si vous souhaitez d'abord modifier les données avant de les charger dans Excel, à l'étape 6, sélectionnez « Combiner et modifier ». Cela ouvrira le résultat final dans l'éditeur Power Query où vous pourrez modifier les données.

Quelques choses à savoir :

• Si vous sélectionnez un tableau Excel comme modèle (à l'étape 7), Power Query utilisera les noms de colonnes de ce tableau pour combiner les données d'autres tableaux. Si d'autres tables ont des colonnes supplémentaires, celles-ci seront ignorées. Au cas où ces autres tables n'auraient pas de colonne, qui se trouve dans votre table de modèle, Power Query mettra simplement « null » pour cela.
• Les colonnes n'ont pas besoin d'être dans le même ordre car Power Query utilise des en-têtes de colonne pour mapper les colonnes.
• Puisque vous avez sélectionné Table1 comme clé, Power Query recherchera Table1 dans tous les classeurs et combinera tous ceux-ci. S'il ne trouve pas de tableau Excel du même nom (Table1 dans cet exemple), Power Query vous renverra une erreur.

Ajout de nouveaux fichiers au dossier

Maintenant, prenons une minute et comprenons ce que nous avons fait avec les étapes ci-dessus (qui ne nous ont pris que quelques secondes).

Nous avons combiné les données de quatre classeurs différents dans une seule table en quelques secondes sans même ouvrir aucun des classeurs.

Mais ce n'est pas tout.

Le vrai POUVOIR de Power Query est que maintenant, lorsque vous ajoutez plus de fichiers au dossier, vous n'avez plus besoin de répéter aucune de ces étapes.

Il vous suffit de déplacer le nouveau classeur dans le dossier, d'actualiser la requête et il combinera automatiquement les données de tous les classeurs de ce dossier.

Par exemple, dans l'exemple ci-dessus, si j'ajoute un nouveau classeur - 'Mid-West.xlsx' dans le dossier et actualisez la requête, cela me donnera instantanément le nouvel ensemble de données combiné.

Voici comment actualiser une requête :

• Cliquez avec le bouton droit sur le tableau Excel que vous avez chargé dans la feuille de calcul et cliquez sur Actualiser.
• Cliquez avec le bouton droit sur la requête dans le volet « Requête de classeur » et cliquez sur Actualiser
• Allez dans l'onglet Données et cliquez sur Actualiser.

Chaque classeur a les données avec le même nom de feuille de calcul

Si vous n'avez pas les données dans un tableau Excel, mais que tous les noms de feuille (à partir desquels vous souhaitez combiner les données) sont les mêmes, vous pouvez utiliser la méthode indiquée dans cette section.

Il y a certaines choses dont vous devez être prudent lorsqu'il s'agit uniquement de données tabulaires et non d'un tableau Excel.

• Les noms des feuilles de calcul doivent être les mêmes. Cela aidera Power Query à parcourir vos classeurs et à combiner les données des feuilles de calcul portant le même nom dans chaque classeur.
• Power Query est sensible à la casse. Cela signifie qu'une feuille de calcul nommée « données » et « données » sont considérées comme différentes. De même, une colonne avec l'en-tête « Store » et une autre avec « magasin » sont considérées comme différentes.
• S'il est important d'avoir les mêmes en-têtes de colonne, il n'est pas important d'avoir le même ordre. Si la colonne 2 de « East.xlsx » est la colonne 4 de « West.xlsx », Power Query la fera correspondre correctement en mappant les en-têtes.

Voyons maintenant comment combiner rapidement les données de différents classeurs où le nom de la feuille de calcul est le même.

Dans cet exemple, j'ai un dossier avec quatre fichiers.

Dans chaque classeur, j'ai une feuille de calcul avec le nom « Données » qui contient les données au format suivant (notez qu'il ne s'agit pas d'un tableau Excel).

Voici les étapes pour combiner les données de plusieurs classeurs dans une seule feuille de calcul :

1. Allez dans l'onglet Données.
2. Dans le groupe Obtenir et transformer, cliquez sur le menu déroulant Nouvelle requête.
3. Passez votre curseur sur « À partir du fichier » et cliquez sur « À partir du dossier ».
4. Dans la boîte de dialogue Dossier, entrez le chemin d'accès au dossier contenant les fichiers ou cliquez sur Parcourir et localisez le dossier.
5. Cliquez sur OK.
6. Dans la boîte de dialogue qui s'ouvre, cliquez sur le bouton Combiner.
7. Cliquez sur « Combiner et charger ».
8. Dans la boîte de dialogue "Combiner les fichiers" qui s'ouvre, sélectionnez "Données" dans le volet de gauche. Notez que Power Query vous montre le nom de la feuille de calcul du premier fichier. Ce fichier servirait de clé/modèle pour combiner d'autres fichiers. Power Query parcourra chaque classeur, trouvera la feuille nommée « Données et combinera tout cela.
9. Cliquez sur OK. Maintenant, Power Query parcourra chaque classeur, recherchera la feuille de calcul nommée « Données », puis combinera tous ces ensembles de données.

Cela chargera le résultat final (données combinées) dans votre feuille de calcul active.

Si vous souhaitez d'abord modifier les données avant de les charger dans Excel, à l'étape 6, sélectionnez « Combiner et modifier ». Cela ouvrira le résultat final dans l'éditeur Power Query où vous pourrez modifier les données.

Chaque classeur contient les données avec des noms de table ou des noms de feuille différents

Parfois, vous pouvez ne pas obtenir de données structurées et cohérentes (telles que des tableaux portant le même nom ou une feuille de calcul portant le même nom).

Par exemple, supposons que vous obteniez les données d'une personne qui a créé ces ensembles de données mais a nommé les feuilles de calcul Données Est, Données Ouest, Données Nord et Données Sud.

Ou, la personne peut avoir créé des tableaux Excel, mais avec des noms différents.

Dans de tels cas, vous pouvez toujours utiliser Power Query, mais vous devez le faire en quelques étapes supplémentaires.

1. Allez dans l'onglet Données.
2. Dans le groupe Obtenir et transformer, cliquez sur le menu déroulant Nouvelle requête.
3. Passez votre curseur sur « À partir du fichier » et cliquez sur « À partir du dossier ».
4. Dans la boîte de dialogue Dossier, entrez le chemin d'accès au dossier contenant les fichiers ou cliquez sur Parcourir et localisez le dossier.
5. Cliquez sur OK.
6. Dans la boîte de dialogue qui s'ouvre, cliquez sur le bouton Modifier. Cela ouvrira l'éditeur Power Query où vous verrez les détails de tous les fichiers du dossier.
7. Maintenez la touche Contrôle enfoncée et sélectionnez les colonnes « Contenu » et « Nom », faites un clic droit et sélectionnez « Supprimer les autres colonnes ». Cela supprimera toutes les autres colonnes à l'exception des colonnes sélectionnées.
8. Dans le ruban de l'éditeur de requête, cliquez sur « Ajouter une colonne », puis sur « Colonne personnalisée ».
9. Dans la boîte de dialogue Ajouter une colonne personnalisée, nommez la nouvelle colonne « Importation de données » et utilisez la formule suivante =Excel.Classeur([CONTENU]). Notez que cette formule est sensible à la casse et que vous devez la saisir exactement comme je l'ai montré ici.
10. Maintenant, vous verrez une nouvelle colonne dans laquelle est écrit Table. Maintenant, laissez-moi vous expliquer ce qui s'est passé ici. Vous avez fourni à Power Query les noms des classeurs, et Power Query a récupéré les objets tels que les feuilles de calcul, les tables et les plages nommées de chaque classeur (qui réside dans la cellule Table à partir de maintenant). Vous pouvez cliquer sur l'espace blanc à côté du tableau de texte et vous verrez les informations en bas. Dans ce cas, comme nous n'avons qu'un seul tableau et une seule feuille de calcul dans chaque classeur, vous ne pouvez voir que deux lignes.
11. Cliquez sur l'icône à double flèche en haut de la colonne « Importation de données ».
12. Dans la zone de données de la colonne qui s'ouvre, décochez la case "Utiliser la colonne d'origine comme préfixe", puis cliquez sur OK.
13. Vous verrez maintenant un tableau développé dans lequel vous voyez une ligne pour chaque objet du tableau. Dans ce cas, pour chaque classeur, l'objet feuille et l'objet table sont répertoriés séparément.
14. Dans la colonne Genre, filtrez la liste pour n'afficher que la table.
15. Maintenez la touche Ctrl enfoncée et sélectionnez la colonne Nom et données. Maintenant, faites un clic droit et supprimez toutes les autres colonnes.
16. Dans la colonne Données, cliquez sur l'icône en forme de double flèche en haut à droite de l'en-tête de données.
17. Dans la zone de données de colonne qui s'ouvre, cliquez sur OK. Cela combinera les données de toutes les tables et s'affichera dans Power Query.
18. Vous pouvez maintenant effectuer la transformation dont vous avez besoin, puis accéder à l'onglet Accueil et cliquer sur Fermer et charger.

Maintenant, permettez-moi d'essayer d'expliquer rapidement ce que nous avons fait ici. Comme il n'y avait aucune cohérence dans les noms de feuilles ou de tables, nous avons utilisé la formule =Excel.Workbook pour récupérer tous les objets des classeurs dans Power Query. Ces objets peuvent inclure des feuilles, des tables et des plages nommées. Une fois que nous avons eu tous les objets de tous les fichiers, nous les avons filtrés pour ne considérer que les tableaux Excel. Ensuite, nous avons étendu les données dans les tableaux et combiné tout cela.

Dans cet exemple, nous avons filtré les données pour n'utiliser que des tableaux Excel (à l'étape 13). Si vous souhaitez combiner des feuilles et non des tableaux, vous pouvez filtrer les feuilles.

Remarque - cette technique vous donnera les données combinées même en cas de non-concordance dans les noms de colonnes. Par exemple, si dans East.xlsx, vous avez une colonne mal orthographiée, vous vous retrouverez avec 5 colonnes. Power Query remplira les données dans les colonnes s'il les trouve, et s'il ne trouve pas de colonne, il signalera la valeur comme « nulle ».

De même, si vous avez des colonnes supplémentaires dans l'une des feuilles de calcul des tableaux, elles seront incluses dans le résultat final.

Maintenant, si vous obtenez plus de classeurs à partir desquels vous devez combiner des données, copiez-les simplement dans le dossier et actualisez la Power Query.