Assemblage de tableaux à partir de différents fichiers Excel avec Power Query

Formulation du problème

Examinons une belle solution pour l'une des situations très standard auxquelles la plupart des utilisateurs d'Excel sont confrontés tôt ou tard : vous devez collecter rapidement et automatiquement des données à partir d'un grand nombre de fichiers dans un tableau final. 

Supposons que nous ayons le dossier suivant, qui contient plusieurs fichiers avec des données de villes filiales :

Le nombre de fichiers n'a pas d'importance et peut changer à l'avenir. Chaque fichier a une feuille nommée Ventesoù se trouve la table de données :

Le nombre de lignes (commandes) dans les tableaux est bien sûr différent, mais l'ensemble des colonnes est standard partout.

Tâche : collecter les données de tous les fichiers dans un seul livre avec une mise à jour automatique ultérieure lors de l'ajout ou de la suppression de fichiers de ville ou de lignes dans les tableaux. Selon le tableau consolidé final, il sera alors possible de créer des rapports, des tableaux croisés dynamiques, des données de tri par filtre, etc. L'essentiel est de pouvoir collecter.

Nous sélectionnons des armes

Pour la solution, nous avons besoin de la dernière version d'Excel 2016 (la fonctionnalité nécessaire y est déjà intégrée par défaut) ou des versions précédentes d'Excel 2010-2013 avec le complément gratuit installé Requête d'alimentation de Microsoft (téléchargez-le ici). Power Query est un outil super flexible et super puissant pour charger des données dans Excel depuis le monde extérieur, puis les supprimer et les traiter. Power Query prend en charge presque toutes les sources de données existantes - des fichiers texte à SQL et même Facebook 🙂

Si vous n'avez pas Excel 2013 ou 2016, vous ne pouvez pas lire plus loin (je plaisante). Dans les anciennes versions d'Excel, une telle tâche ne peut être accomplie qu'en programmant une macro en Visual Basic (ce qui est très difficile pour les débutants) ou par une copie manuelle monotone (ce qui prend beaucoup de temps et génère des erreurs).

Étape 1. Importer un fichier en tant qu'échantillon

Tout d'abord, importons les données d'un classeur à titre d'exemple, afin qu'Excel "reprenne l'idée". Pour ce faire, créez un nouveau classeur vierge et…

• si vous avez Excel 2016, ouvrez l'onglet Données et alors Créer une requête – À partir d'un fichier – À partir d'un livre (Données - Nouvelle requête - A partir du fichier - A partir d'Excel)
• si vous avez Excel 2010-2013 avec le complément Power Query installé, ouvrez l'onglet Requête d'alimentation et sélectionnez dessus A partir du fichier – A partir du livre (À partir du fichier — À partir d'Excel)

Ensuite, dans la fenêtre qui s'ouvre, accédez à notre dossier avec des rapports et sélectionnez l'un des fichiers de la ville (peu importe lequel, car ils sont tous typiques). Après quelques secondes, la fenêtre Navigateur devrait apparaître, où vous devez sélectionner la feuille dont nous avons besoin (Ventes) sur le côté gauche, et son contenu sera affiché sur le côté droit :

Si vous cliquez sur le bouton dans le coin inférieur droit de cette fenêtre Télécharger (Charge), le tableau sera immédiatement importé dans la feuille dans sa forme d'origine. Pour un seul fichier, c'est bien, mais nous devons charger de nombreux fichiers de ce type, nous allons donc procéder un peu différemment et cliquer sur le bouton Correction (Edit). Après cela, l'éditeur de requête Power Query devrait être affiché dans une fenêtre séparée avec nos données du livre :

C'est un outil très puissant qui vous permet de "finir" le tableau à la vue dont nous avons besoin. Même une description superficielle de toutes ses fonctions prendrait une centaine de pages, mais, si très brièvement, en utilisant cette fenêtre, vous pouvez :

• filtrer les données inutiles, les lignes vides, les lignes avec des erreurs
• trier les données sur une ou plusieurs colonnes
• se débarrasser de la répétition
• diviser le texte collant par colonnes (par délimiteurs, nombre de caractères, etc.)
• mettre le texte en ordre (supprimer les espaces supplémentaires, corriger la casse, etc.)
• convertir les types de données de toutes les manières possibles (transformer des nombres comme du texte en nombres normaux et vice versa)
• transposer (faire pivoter) les tableaux et étendre les tableaux croisés bidimensionnels en tableaux plats
• ajouter des colonnes supplémentaires au tableau et y utiliser des formules et des fonctions à l'aide du langage M intégré à Power Query.
• ...

Par exemple, ajoutons une colonne avec le nom textuel du mois à notre tableau, afin que plus tard, il soit plus facile de créer des rapports de tableau croisé dynamique. Pour cela, faites un clic droit sur l'en-tête de la colonne donnéeset sélectionnez la commande Dupliquer la colonne (Colonne en double), puis cliquez avec le bouton droit sur l'en-tête de la colonne en double qui apparaît et sélectionnez Commandes Transformer – Mois – Nom du mois:

Une nouvelle colonne doit être formée avec les noms de texte du mois pour chaque ligne. En double-cliquant sur un en-tête de colonne, vous pouvez le renommer de Copier la date à un plus confortable Mois, par exemple.

Si, dans certaines colonnes, le programme n'a pas correctement reconnu le type de données, vous pouvez l'aider en cliquant sur l'icône de format à gauche de chaque colonne :

Vous pouvez exclure les lignes avec des erreurs ou des lignes vides, ainsi que les responsables ou clients inutiles, à l'aide d'un simple filtre :

De plus, toutes les transformations effectuées sont fixées dans le panneau de droite, où elles peuvent toujours être annulées (croix) ou modifier leurs paramètres (engrenage) :

Léger et élégant, n'est-ce pas ?

Étape 2. Transformons notre requête en fonction

Afin de répéter ensuite toutes les transformations de données effectuées pour chaque livre importé, nous devons convertir notre requête créée en une fonction, qui sera ensuite appliquée, à son tour, à tous nos fichiers. Pour ce faire, c'est en fait très simple.

Dans l'éditeur de requête, allez dans l'onglet Affichage et cliquez sur le bouton Éditeur avancé (Affichage — Éditeur avancé). Une fenêtre devrait s'ouvrir où toutes nos actions précédentes seront écrites sous forme de code en langage M. Veuillez noter que le chemin d'accès au fichier que nous avons importé pour l'exemple est codé en dur dans le code :

Faisons maintenant quelques ajustements :

Leur signification est simple : la première ligne (chemin du fichier)=> transforme notre procédure en une fonction avec un argument chemin du fichier, et ci-dessous, nous modifions le chemin fixe vers la valeur de cette variable. 

Tout. Cliquer sur Finition et devrait voir ceci:

N'ayez pas peur que les données aient disparu - en fait, tout va bien, tout devrait ressembler à ceci 🙂 Nous avons créé avec succès notre fonction personnalisée, où l'intégralité de l'algorithme d'importation et de traitement des données est mémorisée sans être liée à un fichier spécifique . Il reste à lui donner un nom plus compréhensible (par exemple obtenirDonnées) dans le panneau à droite dans le champ Prénom et tu peux récolter Accueil — Fermer et télécharger (Accueil - Fermer et Charger). Veuillez noter que le chemin d'accès au fichier que nous avons importé pour l'exemple est codé en dur dans le code. Vous reviendrez à la fenêtre principale de Microsoft Excel, mais un panneau avec la connexion créée à notre fonction devrait apparaître à droite :

Étape 3. Collecte de tous les fichiers

Tout le plus dur est derrière, reste la partie agréable et facile. Allez dans l'onglet Données - Créer une requête - À partir d'un fichier - À partir d'un dossier (Données — Nouvelle requête — A partir du fichier — A partir du dossier) ou, si vous avez Excel 2010-2013, similaire à l'onglet Requête d'alimentation. Dans la fenêtre qui apparaît, spécifiez le dossier où se trouvent tous nos fichiers de ville source et cliquez sur OK. L'étape suivante devrait ouvrir une fenêtre où tous les fichiers Excel trouvés dans ce dossier (et ses sous-dossiers) et les détails de chacun d'eux seront répertoriés :

Cliquez Modifier (Edit) et encore une fois, nous entrons dans la fenêtre familière de l'éditeur de requêtes.

Nous devons maintenant ajouter une autre colonne à notre table avec notre fonction créée, qui « extraira » les données de chaque fichier. Pour cela, rendez-vous dans l'onglet Ajouter une colonne - Colonne personnalisée (Ajouter une colonne - Ajouter une colonne personnalisée) et dans la fenêtre qui apparaît, entrez notre fonction obtenirDonnées, en lui spécifiant en argument le chemin complet de chaque fichier :

Après avoir cliqué sur OK la colonne créée doit être ajoutée à notre table sur la droite.

Supprimons maintenant toutes les colonnes inutiles (comme dans Excel, en utilisant le bouton droit de la souris - Effacer), ne laissant que la colonne ajoutée et la colonne avec le nom du fichier, car ce nom (plus précisément, la ville) sera utile d'avoir dans les données totales pour chaque ligne.

Et maintenant le "moment wow" - cliquez sur l'icône avec ses propres flèches dans le coin supérieur droit de la colonne ajoutée avec notre fonction :

… décocher Utiliser le nom de la colonne d'origine comme préfixe (Utilisez le nom de la colonne d'origine comme préfixe)et cliquez sur OK. Et notre fonction chargera et traitera les données de chaque fichier, en suivant l'algorithme enregistré et en collectant le tout dans une table commune :

Pour une beauté complète, vous pouvez également supprimer les extensions .xlsx de la première colonne avec les noms de fichiers - par remplacement standard par "rien" (clic droit sur l'en-tête de colonne - Représentant(e) suppléant(e)) et renommez cette colonne en Ville. Et corrigez également le format des données dans la colonne avec la date.

Tout! Cliquer sur Accueil – Fermer et charger (Accueil — Fermer et charger). Toutes les données collectées par la requête pour toutes les villes seront téléchargées dans la feuille Excel actuelle au format « tableau intelligent » :

La connexion créée et notre fonction d'assemblage n'ont en aucun cas besoin d'être enregistrées séparément - elles sont enregistrées avec le fichier actuel de la manière habituelle.

À l'avenir, pour toute modification dans le dossier (ajout ou suppression de villes) ou dans les fichiers (modification du nombre de lignes), il suffira de faire un clic droit directement sur le tableau ou sur la requête dans le panneau de droite et de sélectionner le commande Mettre à jour et enregistrer (Rafraîchir) – Power Query « reconstruira » à nouveau toutes les données en quelques secondes.

PS

Amendement. Après les mises à jour de janvier 2017, Power Query a appris à collecter des classeurs Excel par lui-même, c'est-à-dire qu'il n'est plus nécessaire de créer une fonction distincte - cela se fait automatiquement. Ainsi, la deuxième étape de cet article n'est plus nécessaire et l'ensemble du processus devient sensiblement plus simple :

1. Selectionnez Créer une demande – À partir du fichier – À partir du dossier – Sélectionner le dossier – OK
2. Lorsque la liste des fichiers apparaît, appuyez sur Modifier
3. Dans la fenêtre de l'éditeur de requête, développez la colonne Binaire avec une double flèche et sélectionnez le nom de la feuille à extraire de chaque fichier

Et c'est tout! Chanson!

• Refonte du tableau croisé en un tableau plat adapté à la création de tableaux croisés dynamiques
• Création d'un graphique à bulles animé dans Power View
• Macro pour assembler des feuilles de différents fichiers Excel en un seul