Microsoft Excel には、複数のテーブル (異なるシートまたは異なるファイル) からデータを収集するための多くのツールがあります: 直接リンク、関数 間接 （間接）、Power Query、Power Pivot アドオンなど。バリケードのこちら側から見ると、すべてが良さそうに見えます。
しかし、逆の問題 (あるテーブルから別のシートにデータが広がる) に遭遇すると、すべてがさらに悲しくなります。 残念ながら、現時点では、Excel の兵器庫には、このようなデータ分離のための文明化された組み込みツールはありません。 そのため、Visual Basic でマクロを使用するか、マクロ レコーダー + Power Query の組み合わせを使用して、後で少し「ファイルを調整」する必要があります。
これをどのように実装できるかを詳しく見てみましょう。
問題の定式化
初期データとして、販売用に 5000 行を超えるサイズのテーブルがあります。
タスク: この表のデータを都市ごとに、この本の別のシートに配布します。 それらの。 出力では、販売が対応する都市で行われたテーブルの行のみを各シートに取得する必要があります。
準備
マクロ コードを複雑にせず、できるだけ理解しやすくするために、いくつかの準備手順を実行しましょう。
まず、 別のルックアップ テーブルを作成する、ここでは、個別のシートを作成するすべての都市が XNUMX つの列に一覧表示されます。 もちろん、このディレクトリには、ソース データに存在するすべての都市が含まれているわけではなく、レポートが必要な都市のみが含まれている場合があります。 このようなテーブルを作成する最も簡単な方法は、コマンドを使用することです データ – 重複を削除 (データ — 重複を削除) 列コピー用 市町村 または機能 ユニク （個性的） – 最新バージョンの Excel 365 を使用している場合。
Excel の新しいシートはデフォルトで現在の (前の) シートの前 (左側) に作成されるため、このディレクトリ内の都市を降順 (Z から A) に並べ替えるのも理にかなっています。シートはアルファベット順に並べられます。
第二に、 п両方のテーブルを動的に変換する （「スマート」）で作業しやすくします。 コマンドを使用します ホーム – 表としてフォーマットする (ホーム — テーブルとしてフォーマット) またはキーボードショートカット Ctrlキー+T. 表示されるタブで コンストラクタ （設計） それらを呼びましょう tableProdaji и テーブルシティ、それぞれ：
方法1.シートごとに分割するマクロ
[詳細設定]タブ 開発者 （デベロッパー） ボタンをクリック ビジュアルベーシック またはキーボードショートカットを使用 他の+F11. 開いたマクロ エディター ウィンドウで、メニューから新しい空のモジュールを挿入します。 インサート – モジュール そこに次のコードをコピーします。
Sub Splitter() For Each cell In Range("таблГорода") Range("таблПродажи").AutoFilter フィールド:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit 次のセル Worksheets("Данные").ShowAllData End Sub ここでループ For Each … 次へ ディレクトリのセルの通過を実装しました テーブルシティ、都市ごとにフィルタリングされます（メソッド オートフィルター) 元の販売テーブルで、結果を新しく作成されたシートにコピーします。 途中で、作成されたシートの名前が同じ都市名に変更され、ビューの列の幅の自動調整がオンになります。
タブのExcelで作成したマクロを実行できます 開発者 (Comma Separated Values) ボタンをクリックして、各々のジョブ実行の詳細（開始/停止時間、変数値など）のCSVファイルをダウンロードします。 マクロ (開発者 — マクロ) またはキーボードショートカット 他の+F8.
方法 2. Power Query で複数のクエリを作成する
前の方法はコンパクトで単純ですが、重大な欠点があります。元の販売テーブルに変更が加えられても、マクロによって作成されたシートは更新されません。 その場での更新が必要な場合は、VBA + Power Query バンドルを使用するか、静的データを含むシートだけでなく、更新された Power Query クエリをマクロを使用して作成する必要があります。
この場合のマクロは、前のものと部分的に似ています (サイクルもあります)。 For Each … 次へ ディレクトリ内の都市を反復処理します) が、ループ内ではフィルタリングとコピーが行われなくなりますが、Power Query クエリが作成され、その結果が新しいシートにアップロードされます。
Sub Splitter2() For Each cell In Range("City table") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " ソース= Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source 、{{""カテゴリー""、テキストを入力}、{""名前""、テキストを入力}、{""都市""、テキストを入力}、{""マネージャー""、テキストを入力}、{""ディールdate "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Rows with filter applied"" = Table.Se " & _ "lectRows(#""変更されたタイプ"", each ([都市] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""フィルターが適用された行""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provider =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 次のセル End Sub ローンチ後、都市ごとに同じシートが表示されますが、既に作成された Power Query クエリがそれらを形成します。
ソース データが変更された場合は、対応するテーブルをマウスの右ボタンで更新するだけで十分です。 更新して保存 （更新） ボタンを使用してすべての都市を一度に一括更新するか、 すべて更新 タブ 且つ (データ — すべて更新).
- マクロとは何か、マクロの作成方法と使用方法
- ワークブック シートを別のファイルとして保存する
- 本のすべてのシートから XNUMX つのテーブルにデータを収集する