Guide complet pour combiner des classeurs Excel avec Power Query

La consolidation manuelle de données provenant de plusieurs feuilles de calcul peut s’avérer chronophage et propice aux erreurs de copie. Cette tâche devient encore plus fastidieuse lorsqu’il faut régulièrement mettre à jour l’ensemble de données. Heureusement, l’outil Power Query d’Excel permet d’automatiser entièrement ce processus.

Bien que ce guide explique comment extraire des informations de divers fichiers XLSX, on peut suivre un processus similaire avec les fichiers TXT, CSV et XML.

Étape 1 : Organiser les classeurs à combiner

Avant de commencer la combinaison des fichiers Microsoft Excel en une seule feuille de calcul, il convient de prendre certaines mesures importantes.

Premièrement, il faut s’assurer que tous les jeux de données à combiner sont structurés de manière identique. Plus précisément, ils doivent contenir le même nombre de colonnes, avoir les mêmes en-têtes de colonnes, et les onglets des feuilles de calcul doivent porter le même nom. Le processus sera également plus simple si les noms de fichiers suivent une syntaxe similaire.

Cela dit, les colonnes n’ont pas besoin d’être dans le même ordre, et le nombre de lignes de chaque feuille peut différer.

Deuxièmement, il faut s’assurer que les fichiers sont enregistrés dans le même dossier.

Si possible, le dossier ne devrait contenir que les fichiers à combiner, car cela rend le processus plus propre et plus facile dans les étapes ultérieures. Cependant, s’il faut absolument stocker d’autres fichiers dans ce dossier également, nous montrerons prochainement comment s’assurer que seuls les fichiers corrects sont sélectionnés pour la fusion des données.

Étape 2 : Ouvrir Excel et créer un lien vers le dossier pertinent

Une fois les fichiers correctement organisés dans le dossier selon les prérequis ci-dessus, on est prêt à commencer à rassembler les données.

D’abord, il faut ouvrir un nouveau classeur Microsoft Excel, commencer une feuille de calcul vierge, et appuyer sur F12 pour enregistrer le fichier dans un dossier différent de celui où se trouvent les fichiers individuels à combiner.

Ensuite, dans l’onglet Données du ruban, il faut cliquer sur “Obtenir des données”. Puis, survoler “À partir d’un fichier” et sélectionner “À partir d’un dossier”.

Maintenant, dans la boîte de dialogue Parcourir, il faut localiser et sélectionner le dossier où sont stockés les fichiers à combiner, puis cliquer sur “Ouvrir”.

Étape 3 : Choisir les fichiers à combiner

Ce qu’il faut faire dans cette étape dépend de ce qui est stocké dans le dossier sélectionné. S’il ne contient que les fichiers à fusionner, voir la Section 3A ci-dessous. D’autre part, si le dossier contient également des fichiers qu’on ne souhaite pas fusionner, passer à la Section 3B.

3A : Le dossier ne contient que les fichiers à fusionner

Plus tôt, nous avons dit que le processus de fusion de classeurs est plus propre et plus facile si le dossier sélectionné est dédié aux fichiers à combiner. C’est parce qu’il n’est pas nécessaire de filtrer les fichiers non désirés. Donc, puisqu’on est déjà prêt à combiner les fichiers, il faut cliquer sur “Combiner”, et dans le menu déroulant qui apparaît, cliquer sur “Combiner et transformer les données”.

Maintenant, après qu’Excel ait brièvement évalué les données, on verra la boîte de dialogue Combiner les fichiers, et on est prêt à passer à l’Étape 4.

3B : Le dossier contient des fichiers qu’on ne souhaite pas fusionner

Si le dossier sélectionné contient également des fichiers autres que ceux qu’on veut fusionner, après avoir localisé le dossier à l’Étape 2, on les verra listés dans les détails. Dans ce cas, il faut cliquer sur “Transformer les données”, car on veut apporter des modifications à la sélection de fichiers avant de combiner les données.

Cliquer sur cette option lance l’Éditeur Power Query, une fenêtre séparée dans Microsoft Excel où on peut filtrer les fichiers qu’on ne veut pas inclure dans le processus de combinaison.

Dans ce cas, le fichier nommé Détails de Contact doit être exclu, donc il faut cliquer sur la flèche déroulante de filtre dans l’en-tête de la colonne Nom, décocher ce fichier, et cliquer sur “OK”.

Maintenant, seuls les fichiers qu’on veut combiner sont listés, donc il faut cliquer sur la double flèche vers le bas dans la colonne Contenu pour lancer la boîte de dialogue Combiner les fichiers.

Étape 4 : Combiner les fichiers

Il y a quelques points à surveiller dans la boîte de dialogue Combiner les fichiers.

Premièrement, par défaut, le classeur désigné comme fichier d’exemple est le premier sélectionné dans la liste. Le fichier d’exemple dictera comment les données sont structurées lors de la combinaison des fichiers, donc, tant que tous les fichiers sont structurés de manière cohérente, il est acceptable de laisser cette option telle quelle.

Deuxièmement, si les classeurs ont plus d’un onglet de feuille de calcul, on les verra listés dans le volet Options d’affichage à gauche. C’est pourquoi il est important que tous les onglets qu’on veut combiner dans les classeurs désignés aient le même nom. De plus, si on a formaté les données comme des tableaux Excel et les a nommés, on verra la liste des noms ici.

Dans cet exemple, cependant, chaque classeur n’a qu’une seule feuille de calcul (nommée Scores), donc il faut sélectionner cette option. Après avoir examiné l’aperçu du fichier d’exemple sélectionné dans le volet de droite, il faut cliquer sur “OK” pour confirmer et lancer l’Éditeur Power Query.

Étape 5 : Transformer les données

Si c’est la première fois qu’on ouvre l’Éditeur Power Query dans Excel, cela peut initialement être déroutant. Cependant, une fois qu’on prend le temps de comprendre ce que font les différentes zones de l’éditeur, on réalise que c’est en fait un outil intuitif.

Le volet de gauche est l’endroit où toutes les requêtes sont situées. Lors de la combinaison de données de divers classeurs Excel, il faut seulement se concentrer sur deux d’entre elles : la requête Transformer le fichier d’exemple, qui est utilisée comme modèle pour tous les changements qu’on veut apporter aux jeux de données, et la requête ajoutée en bas de la liste, qui consolide les fichiers. Cependant, selon ce qu’on veut faire, elles sont toutes deux utiles pour transformer les données.

D’abord, dans la requête Transformer le fichier d’exemple, il faut cliquer sur les icônes de format de nombre dans l’en-tête de chaque colonne pour indiquer à Excel quels types de données elles contiennent. Dans cet exemple, les première, troisième et quatrième colonnes contiennent des nombres entiers, et la deuxième colonne contient une date.

Il faut regarder dans le volet Paramètres de requête sur le côté droit de l’Éditeur Power Query pour voir (et supprimer si nécessaire) chaque étape prise dans la transformation des données.

Ensuite, dans la requête ajoutée, nous n’avons pas besoin de la colonne Nom de la source, mais il serait pratique d’avoir l’année pour chaque score d’équipe.

On peut transformer cette colonne pour ne conserver que les années tout en supprimant le reste des noms de fichiers. Pour ce faire, il faut cliquer sur l’en-tête de colonne pour que toutes les valeurs de la colonne soient sélectionnées, et dans l’onglet Transformer du ruban, cliquer sur “Extraire”, puis “Texte avant le délimiteur”.

Dans ce cas, nous voulons conserver l’année et supprimer tout ce qui suit le premier espace. Donc, dans le champ de texte Délimiteur, il faut taper un seul espace. Puis, cliquer sur “Options avancées” et sélectionner “Depuis le début de l’entrée”. Maintenant, cliquer sur “OK”.

Finalement, il faut double-cliquer sur l’en-tête de colonne pour la renommer “Année”, et changer le type de données en “Nombre entier”.

Il faut prendre un moment pour faire défiler la liste des données combinées pour vérifier qu’il n’y a pas d’erreurs ou d’autres transformations qu’on souhaite effectuer.

Étape 6 : Charger les données combinées sur une nouvelle feuille de calcul

Maintenant que les données des classeurs ont été combinées et transformées, il est temps de voir à quoi elles ressemblent dans une feuille de calcul Excel normale. Dans l’onglet Accueil de la fenêtre de l’Éditeur Power Query, il faut cliquer sur la partie supérieure du bouton divisé “Fermer et charger”.

Il faut cliquer sur la partie inférieure du bouton divisé “Fermer et charger” pour plus d’options de chargement. Par exemple, on peut charger les données sur une feuille de calcul existante ou sous forme de tableau croisé dynamique.

Maintenant, les données se chargent sur une nouvelle feuille de calcul sous forme de tableau Excel formaté, ce qui signifie qu’on peut formater son apparence et ses propriétés dans l’onglet Création de tableau du ruban.

Pour apporter d’autres modifications à la requête dans l’Éditeur Power Query, après avoir activé le volet Requêtes et connexions dans l’onglet Données, il faut cliquer avec le bouton droit sur la requête et sélectionner “Modifier”.

Une fois les modifications nécessaires apportées dans l’Éditeur Power Query, il faut cliquer à nouveau sur “Fermer et charger” pour mettre à jour le jeu de données résultant.

Étape 7 : Ajouter plus de données à partir de classeurs supplémentaires

Finalement, imaginons que les données pour 2025 sont maintenant arrivées, et qu’on veut ajouter ces nouvelles informations à la requête existante. Étonnamment, parce qu’on a déjà pris les mesures nécessaires dans l’Éditeur Power Query, il suffit de s’assurer que le classeur répond à tous les critères décrits à l’Étape 1, et de cliquer sur un seul bouton dans Excel pour actualiser le tableau.

D’abord, il faut déplacer le classeur contenant les données vers le dossier où se trouvent les données des années précédentes, en s’assurant que le nom du classeur, la structure de la feuille de calcul et le nom de l’onglet sont cohérents avec les autres fichiers.

Puis, il faut ouvrir le classeur contenant les données précédemment ajoutées, et dans le volet Requêtes et connexions (qu’on peut activer en cliquant sur “Requêtes et connexions” dans l’onglet Données du ruban), cliquer sur l’icône “Actualiser” à côté de la requête principale.

Après avoir donné à Excel quelques secondes pour terminer cette mise à jour, il faut faire défiler vers le bas pour voir les données supplémentaires ajoutées au bas du tableau.

Pour conserver tout formatage appliqué au tableau avant d’actualiser la Power Query, il faut cliquer sur “Propriétés” dans l’onglet Création de tableau du ruban, et s’assurer que “Conserver le formatage des cellules” est coché. Si on change les largeurs de colonnes et qu’on ne veut pas qu’elles s’ajustent automatiquement aux données qu’elles contiennent à chaque actualisation, il faut décocher “Ajuster la largeur des colonnes”.

En plus d’ajouter des données de classeurs séparés enregistrés dans un dossier, on peut empiler des données de plusieurs feuilles Excel dans un seul classeur en créant des connexions de données entre elles et en combinant les requêtes. De plus, on peut utiliser l’Éditeur Power Query pour importer des tableaux depuis le web.

Sandrine Marcos

Rédactrice spécialisée en édition de site. Formation de journaliste et passionnée par les nouvelles technologies, l’intelligence artificielle et la rédaction web.