Table des matières
Formulation du problème
Comme données d'entrée, nous avons un fichier Excel, où l'une des feuilles contient plusieurs tableaux avec des données de vente de la forme suivante :
Noter que:
- Des tableaux de différentes tailles et avec différents ensembles de produits et de régions en lignes et en colonnes sans aucun tri.
- Des lignes vides peuvent être insérées entre les tableaux.
- Le nombre de tables peut être quelconque.
Deux hypothèses importantes. Il est entendu que:
- Au-dessus de chaque tableau, dans la première colonne, il y a le nom du manager dont le tableau illustre les ventes (Ivanov, Petrov, Sidorov, etc.)
- Les noms des marchandises et des régions dans tous les tableaux sont écrits de la même manière - avec une précision de la casse.
Le but ultime est de collecter les données de tous les tableaux dans un seul tableau normalisé plat, pratique pour une analyse ultérieure et la construction d'un résumé, c'est-à-dire dans celui-ci :
Étape 1. Connectez-vous au fichier
Créons un nouveau fichier Excel vide et sélectionnons-le dans l'onglet Données Command Obtenir des données - À partir d'un fichier - À partir d'un livre (Données — Du fichier — Du classeur). Spécifiez l'emplacement du fichier source avec les données de vente, puis dans la fenêtre du navigateur, sélectionnez la feuille dont nous avons besoin et cliquez sur le bouton Convertir des données (Transformer les données):
Par conséquent, toutes les données qu'il contient doivent être chargées dans l'éditeur Power Query :
Étape 2. Nettoyer la corbeille
Supprimer les étapes générées automatiquement type modifié (type modifié) и En-têtes surélevés (En-têtes promus) et se débarrasser des lignes vides et des lignes avec des totaux à l'aide d'un filtre nul и TOTAL par la première colonne. En conséquence, nous obtenons l'image suivante:
Étape 3. Ajouter des gestionnaires
Afin de comprendre plus tard où sont les ventes, il est nécessaire d'ajouter une colonne à notre tableau, où dans chaque ligne il y aura un nom de famille correspondant. Pour ça:
1. Ajoutons une colonne auxiliaire avec des numéros de ligne à l'aide de la commande Ajouter une colonne - Colonne d'index - À partir de 0 (Ajouter une colonne — Colonne d'index — À partir de 0).
2. Ajouter une colonne avec une formule avec la commande Ajout d'une colonne - Colonne personnalisée (Ajouter une colonne — Colonne personnalisée) et y introduire la construction suivante :
La logique de cette formule est simple - si la valeur de la cellule suivante dans la première colonne est "Produit", cela signifie que nous sommes tombés sur le début d'un nouveau tableau, nous affichons donc la valeur de la cellule précédente avec le nom du gérant. Sinon, nous n'affichons rien, c'est-à-dire null.
Pour obtenir la cellule parent avec le nom de famille, nous nous référons d'abord au tableau de l'étape précédente #"Index ajouté", puis spécifiez le nom de la colonne dont nous avons besoin [Colonne1] entre crochets et le numéro de cellule dans cette colonne entre accolades. Le numéro de cellule sera un de moins que le numéro actuel, que nous prenons de la colonne Sommaire, Respectivement.
3. Il reste à remplir les cellules vides avec nul les noms des cellules supérieures avec la commande Transformer - Remplir - Bas (Transformer — Remplir — Bas) et supprimez la colonne dont vous n'avez plus besoin avec les indices et les lignes avec les noms de famille dans la première colonne. En conséquence, nous obtenons :
Étape 4. Regroupement dans des tableaux distincts par gestionnaires
L'étape suivante consiste à regrouper les lignes de chaque gestionnaire dans des tables distinctes. Pour cela, dans l'onglet Transformation, utilisez la commande Regrouper par (Transformer – Regrouper par) et dans la fenêtre qui s'ouvre, sélectionnez la colonne Responsable et l'opération Toutes les lignes (Toutes les lignes) pour simplement collecter des données sans appliquer de fonction d'agrégation à (somme, moyenne, etc.). P.):
En conséquence, nous obtenons des tables distinctes pour chaque gestionnaire :
Étape 5 : Transformer les tableaux imbriqués
Maintenant, nous donnons les tableaux qui se trouvent dans chaque cellule de la colonne résultante Toutes les données en bonne forme.
Tout d'abord, supprimez une colonne qui n'est plus nécessaire dans chaque table Gérante. Nous utilisons à nouveau Colonne personnalisée languette De La Carrosserie (Transformer - Colonne personnalisée) et la formule suivante:
Ensuite, avec une autre colonne calculée, nous élevons la première ligne de chaque tableau aux en-têtes :
Et enfin, nous effectuons la transformation principale - dépliage de chaque table à l'aide de la fonction M Table.UnpivotOtherColumnsTable.UnpivotOtherColumns:
Les noms des régions de l'en-tête iront dans une nouvelle colonne et nous obtiendrons un tableau normalisé plus étroit, mais en même temps plus long. Cellules vides avec nul sont ignorés.
En se débarrassant des colonnes intermédiaires inutiles, nous avons :
Étape 6 Développez les tables imbriquées
Il reste à développer toutes les tables imbriquées normalisées en une seule liste à l'aide du bouton à double flèche dans l'en-tête de colonne :
… et nous obtenons enfin ce que nous voulions :
Vous pouvez réexporter le tableau résultant vers Excel à l'aide de la commande Accueil — Fermer et charger — Fermer et charger dans… (Accueil — Fermer&Charger — Fermer&Charger vers…).
- Créer des tableaux avec différents en-têtes à partir de plusieurs livres
- 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