Créer des tableaux avec différents en-têtes à partir de plusieurs livres

Formulation du problème

Nous avons plusieurs fichiers (dans notre exemple - 4 pièces, dans le cas général - autant que vous le souhaitez) dans un dossier Rapports:

À l'intérieur, ces fichiers ressemblent à ceci :

Où:

• La fiche technique dont nous avons besoin s'appelle toujours Photos, mais peut se trouver n'importe où dans le classeur.
  
• Au-delà de la feuille Photos Chaque livre peut avoir d'autres feuilles.
• Les tableaux contenant des données ont un nombre de lignes différent et peuvent commencer par une ligne différente sur la feuille de calcul.
• Les noms des mêmes colonnes dans différentes tables peuvent différer (par exemple, Quantité = Quantité = Qté).
• Les colonnes des tableaux peuvent être organisées dans un ordre différent.

Tâche : collecter les données de vente de tous les fichiers de la feuille Photos dans un tableau commun afin de construire ensuite un résumé ou toute autre analyse dessus.

Étape 1. Préparation d'un répertoire de noms de colonnes

La première chose à faire est de préparer un livre de référence avec toutes les options possibles pour les noms de colonnes et leur interprétation correcte :

Nous convertissons cette liste en un tableau "intelligent" dynamique à l'aide du bouton Formater comme tableau de l'onglet Accueil (Accueil — Format sous forme de tableau) ou raccourci clavier Ctrl+T et chargez-le dans Power Query avec la commande Données – À partir du tableau/de la plage (Données – À partir du tableau/de la plage). Dans les versions récentes d'Excel, il a été renommé en Avec des feuilles (De la feuille).

Dans la fenêtre de l'éditeur de requête Power Query, nous supprimons traditionnellement l'étape Type modifié et ajoutez une nouvelle étape à sa place en cliquant sur le bouton f_xdans la barre de formule (si elle n'est pas visible, vous pouvez l'activer dans l'onglet Avis) et saisissez-y la formule dans le langage M intégré de Power Query :

=Table.VersRows(Source)

Cette commande convertira celle chargée à l'étape précédente Identifier table de référence en une liste composée de listes imbriquées (Liste), dont chacune, à son tour, est une paire de valeurs C'était devenu d'une ligne :

Nous aurons besoin de ce type de données un peu plus tard, lors du renommage en masse des en-têtes de toutes les tables chargées.

Une fois la conversion terminée, sélectionnez les commandes Accueil — Fermer et charger — Fermer et charger dans… et le type d'importation Créez simplement une connexion (Accueil — Fermer&Charger — Fermer&Charger vers… — Créer uniquement une connexion) et retournez dans Excel.

Étape 2. Nous chargeons tout de tous les fichiers tels quels

Chargeons maintenant le contenu de tous nos fichiers à partir du dossier - pour l'instant, tel quel. Choisir les équipes Données – Obtenir des données – À partir d'un fichier – À partir d'un dossier (Données — Obtenir des données — Du fichier — Du dossier) puis le dossier où se trouvent nos livres sources.

Dans la fenêtre d'aperçu, cliquez sur Convertir (Transformer) or Modifier (Edit):

Et puis développez le contenu de tous les fichiers téléchargés (Binaire) bouton avec des doubles flèches dans l'en-tête de colonne Contenu:

Power Query sur l'exemple du premier fichier (Vostok.xlsx) nous demandera le nom de la feuille que nous voulons prendre de chaque classeur - choisissez Photos et appuyez sur OK :

Après cela (en fait), plusieurs événements qui ne sont pas évidents pour l'utilisateur se produiront, dont les conséquences sont clairement visibles dans le panneau de gauche :

1. Power Query prendra le premier fichier du dossier (nous l'aurons Vostok.xlsx — sur le lien Exemple de fichier) à titre d'exemple et importe son contenu en créant une requête Convertir un exemple de fichier. Cette requête aura quelques étapes simples comme Identifier (accès aux fichiers) Navigation (sélection de feuilles) et éventuellement rehausser les titres. Cette requête ne peut charger que les données d'un fichier spécifique Vostok.xlsx.
   
2. Sur la base de cette demande, la fonction qui lui est associée sera créée Convertir un fichier (indiqué par une icône caractéristique f_x), où le fichier source ne sera plus une constante, mais une valeur variable - un paramètre. Ainsi, cette fonction peut extraire des données de n'importe quel livre que l'on y glisse comme argument.
   
3. La fonction sera appliquée tour à tour à chaque fichier (Binary) de la colonne Contenu - step est responsable de cela Appeler la fonction personnalisée dans notre requête qui ajoute une colonne à la liste des fichiers Convertir un fichier avec les résultats d'importation de chaque classeur :

   

4. Les colonnes supplémentaires sont supprimées.
5. Le contenu des tables imbriquées est développé (étape Colonne de tableau étendue) – et nous voyons les résultats finaux de la collecte de données de tous les livres :

   
   

Étape 3. Ponçage

La capture d'écran précédente montre clairement que l'assemblage direct « tel quel » s'est avéré de mauvaise qualité :

• Les colonnes sont inversées.
• Beaucoup de lignes supplémentaires (vides et pas seulement).
• Les en-têtes de tableau ne sont pas perçus comme des en-têtes et sont mélangés avec des données.
  

Vous pouvez résoudre tous ces problèmes très facilement - modifiez simplement la requête Convertir un fichier d'exemple. Tous les ajustements que nous y apportons tomberont automatiquement dans la fonction de conversion de fichier associée, ce qui signifie qu'ils seront utilisés ultérieurement lors de l'importation de données à partir de chaque fichier.

En ouvrant une demande Convertir un exemple de fichier, ajoutez des étapes pour filtrer les lignes inutiles (par exemple, par colonne Column2) et remonter les rubriques avec le bouton Utiliser la première ligne comme en-tête (Utilisez la première ligne comme en-têtes). La table sera bien meilleure.

Pour que les colonnes de différents fichiers s'emboîtent automatiquement les unes sous les autres ultérieurement, elles doivent porter le même nom. Vous pouvez effectuer un tel renommage en masse selon un répertoire préalablement créé avec une ligne de M-code. Appuyons à nouveau sur le bouton f_x dans la barre de formule et ajoutez une fonction à modifier :

= Table.RenameColumns(#"En-têtes élevés", En-têtes, MissingField.Ignore)

Cette fonction reprend le tableau de l'étape précédente En-têtes surélevés et renomme toutes les colonnes en fonction de la liste de recherche imbriquée Titres. Troisième argument MissingField.Ignore est nécessaire pour que sur les en-têtes qui sont dans le répertoire, mais pas dans la table, une erreur ne se produise pas.

En fait, c'est tout.

Revenir à la demande Rapports nous verrons une image complètement différente – beaucoup plus belle que la précédente :

• Qu'est-ce que Power Query, Power Pivot, Power BI et pourquoi un utilisateur Excel en a besoin
  
• Collecte des données de tous les fichiers d'un dossier donné
• Collecter les données de toutes les feuilles du livre dans un seul tableau