Fractionner un tableau en feuilles

Microsoft Excel dispose de nombreux outils pour collecter les données de plusieurs tables (de différentes feuilles ou de différents fichiers) : liens directs, fonction INDIRECT (INDIRECT), modules complémentaires Power Query et Power Pivot, etc. De ce côté de la barricade, tout semble bon.

Mais si vous rencontrez un problème inverse - répartir les données d'une table sur différentes feuilles - alors tout sera beaucoup plus triste. À l'heure actuelle, il n'existe malheureusement pas d'outils intégrés civilisés pour une telle séparation de données dans l'arsenal d'Excel. Il vous faudra donc utiliser une macro en Visual Basic, ou utiliser la combinaison enregistreur de macros + Power Query avec un petit « raffinement de fichier » après.

Examinons de plus près comment cela peut être mis en œuvre.

Formulation du problème

Nous avons comme données initiales un tel tableau d'une taille de plus de 5000 lignes pour les ventes :

Tâche : répartir les données de ce tableau par ville sur des feuilles séparées de ce livre. Ceux. à la sortie, vous devez obtenir sur chaque feuille uniquement les lignes du tableau où la vente a eu lieu dans la ville correspondante :

Préparer

Afin de ne pas compliquer le code de la macro et de le rendre aussi facile à comprendre que possible, effectuons quelques étapes préparatoires.

La première, la créer une table de recherche distincte, où une seule colonne listera toutes les villes pour lesquelles vous souhaitez créer des feuilles séparées. Bien entendu, cet annuaire peut ne pas contenir toutes les villes présentes dans les données sources, mais uniquement celles pour lesquelles nous avons besoin de rapports. La façon la plus simple de créer une telle table est d'utiliser la commande Données - Supprimer les doublons (Données - Supprimer les doublons) pour la copie de colonne Ville ou fonction UNIK (UNIQUE) – si vous disposez de la dernière version d'Excel 365.

Etant donné que les nouvelles feuilles dans Excel sont créées par défaut avant (à gauche de) la feuille actuelle (précédente), il est également logique de trier les villes de ce répertoire par ordre décroissant (de Z à A) – puis après la création, la ville les fiches seront classées par ordre alphabétique.

Deuxièmement, le пconvertir les deux tables en dynamique ("intelligent") pour faciliter le travail avec eux. Nous utilisons la commande Accueil – Format sous forme de tableau (Accueil — Format sous forme de tableau) ou raccourci clavier Ctrl+T. Dans l'onglet qui s'affiche Constructeur (Conception) appelons-les tablProdaji и TableVille, respectivement:

Méthode 1. Macro pour la division par feuilles

Dans l'onglet Avancé promoteur (Développeur) cliquez sur le bouton Visual Basic ou utilisez le raccourci clavier autre+F11. Dans la fenêtre de l'éditeur de macros qui s'ouvre, insérez un nouveau module vide via le menu Insertion – Module et copiez-y le code suivant :

Sub Splitter() For Each cell In Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Cellule suivante Worksheets("Данные").ShowAllData End Sub	  

Ici avec une boucle Pour chaque … Suivant implémenté le passage par les cellules du répertoire TableVille, où pour chaque ville il est filtré (méthode Filtre automatique) dans la table des ventes d'origine, puis en copiant les résultats dans la feuille nouvellement créée. En cours de route, la feuille créée est renommée du même nom que la ville et l'ajustement automatique de la largeur des colonnes pour la beauté est activé.

Vous pouvez exécuter la macro créée dans Excel sur l'onglet promoteur bouton (dans la fenêtre de contrôle qui apparaît maintenant) Macros (Développeur — Macros) ou raccourci clavier autre+F8.

Méthode 2. Créer plusieurs requêtes dans Power Query

La méthode précédente, malgré toute sa compacité et sa simplicité, présente un inconvénient important : les feuilles créées par la macro ne sont pas mises à jour lorsque des modifications sont apportées à la table des ventes d'origine. Si une mise à jour à la volée est nécessaire, vous devrez utiliser le bundle VBA + Power Query, ou plutôt créer à l'aide d'une macro non seulement des feuilles avec des données statiques, mais des requêtes Power Query mises à jour.

La macro dans ce cas est partiellement similaire à la précédente (elle a également un cycle Pour chaque … Suivant pour parcourir les villes du répertoire), mais à l'intérieur de la boucle, il n'y aura plus de filtrage et de copie, mais la création d'une requête Power Query et le téléchargement de ses résultats dans une nouvelle feuille :

Sub Splitter2() For Each cell In Range("City table") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Type modifié"" = Table.TransformColumnTypes(Source , {{""Catégorie"", saisissez du texte}, {""Nom"", saisissez du texte}, {""Ville"", saisissez du texte}, {""Gestionnaire"", saisissez du texte}, {""Offre date "", tapez datetime}, {""Cost"", tapez number}})," & Chr(13) & "" & Chr(10) & " #""Lignes avec filtre appliqué"" = Table.Se " & _ "lectRows(#""Changed type"", each ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Lignes avec filtre appliqué""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Fournisseur =Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False . SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery :=False End With ActiveSheet.Name = cell.Value Cellule suivante End Sub  

Après son lancement, nous verrons les mêmes feuilles par ville, mais les requêtes Power Query déjà créées les formeront :

Avec tout changement dans les données source, il suffira de mettre à jour le tableau correspondant avec le bouton droit de la souris - la commande Mettre à jour et enregistrer (Rafraîchir) ou mettre à jour toutes les villes à la fois en masse à l'aide du bouton Tout mettre à jour languette Données (Données — Actualiser tout).

• Que sont les macros, comment les créer et les utiliser
• Enregistrement des feuilles de classeur dans des fichiers séparés
• Collecter les données de toutes les feuilles du livre dans un seul tableau