問題の定式化
4 つのフォルダーに複数のファイルがあります (この例では – XNUMX 個、一般的なケースでは – 好きなだけ) レポート:
内部では、これらのファイルは次のようになります。
ここで：
- 必要なデータシートは常に呼び出されます 写真、ただし、ブック内のどこにでも配置できます。
- シートを超えて 写真 各ブックには他のシートが含まれる場合があります。
- データを含むテーブルの行数は異なり、ワークシートの別の行から始まる場合があります。
- 異なるテーブルの同じ列の名前が異なる場合があります (たとえば、 数量 = 数量 = 数量).
- テーブル内の列は、異なる順序で配置できます。
タスク: シートのすべてのファイルから売上データを収集する 写真 後で要約またはその他の分析を構築するために、XNUMX つの共通テーブルに変換します。
ステップ 1. 列名のディレクトリーの準備
最初に行うことは、列名のすべての可能なオプションとその正しい解釈を含む参考書を準備することです。
タブの [テーブルとしてフォーマット] ボタンを使用して、このリストを動的な「スマート」テーブルに変換します。 ホーム (ホーム — テーブルとしてフォーマット) またはキーボードショートカット Ctrlキー+T コマンドを使用してPower Queryにロードします データ – テーブル/範囲から (データ — テーブル/範囲から). 最近のバージョンの Excel では、名前が次のように変更されました。 葉付き （シートより）.
Power Query クエリ エディター ウィンドウでは、従来、ステップを削除します。 変更されたタイプ ボタンをクリックして、代わりに新しいステップを追加します fx数式バーで（表示されていない場合は、タブで有効にすることができます レビュー) に組み込みの Power Query 言語 M で式を入力します。
=Table.ToRows(ソース)
このコマンドは、前のステップでロードされたものを変換します ソース ネストされたリスト (リスト) で構成されるリストへの参照テーブル。各リストは値のペアです。 なりました XNUMX行から：
このタイプのデータは、ロードされたすべてのテーブルのヘッダーの名前を一括で変更するときに、少し後で必要になります。
変換が完了したら、コマンドを選択します ホーム — 閉じてロード — 閉じてロード… と輸入の種類 接続を作成するだけ (ホーム — Close&Load — Close&Load to… — 接続のみ作成) そしてエクセルに戻ります。
ステップ 2. すべてのファイルからすべてをそのままロードします
フォルダからすべてのファイルの内容をロードしましょう。 チームの選択 データ – データの取得 – ファイルから – フォルダーから (データ — データの取得 — ファイルから — フォルダーから) 次に、ソースブックがあるフォルダー。
プレビュー ウィンドウで、 変換 （変身） or 変更する （編集）:
そして、ダウンロードしたすべてのファイルの内容を展開します （バイナリ） 列見出しに二重矢印があるボタン コンテンツ:
最初のファイルの例に対する Power Query (ボストーク.xlsx) は、各ワークブックから取得するシートの名前を尋ねます – 選択します 写真 OK を押します。
その後 (実際には)、ユーザーには明らかではないいくつかのイベントが発生し、その結果が左側のパネルにはっきりと表示されます。
- Power Query は、フォルダーから最初のファイルを取得します (これを取得します)。 ボストーク.xlsx — ファイル例) を例として、クエリを作成してそのコンテンツをインポートします。 サンプルファイルの変換. このクエリには、次のようないくつかの簡単な手順があります ソース (ファイルアクセス) ナビゲーション (シートの選択) と、場合によってはタイトルを上げます。 このリクエストは、XNUMX つの特定のファイルからのみデータをロードできます ボストーク.xlsx.
- このリクエストに基づいて、それに関連付けられた関数が作成されます ファイルを変換する （特徴的なアイコンで示されます fx)、ソース ファイルは定数ではなく、変数値 (パラメーター) になります。 したがって、この関数は、引数として挿入した任意の本からデータを抽出できます。
- 関数は、列から各ファイル (バイナリ) に順番に適用されます コンテンツ – ステップがこれを担当 カスタム関数を呼び出す ファイルのリストに列を追加するクエリで ファイルを変換する 各ワークブックからのインポート結果:
- 余分な列は削除されます。
- ネストした表の内容が展開されます(ステップ 拡張された表の列) – すべての書籍からのデータ収集の最終結果が表示されます。
ステップ 3. サンディング
前のスクリーンショットは、「現状のまま」の直接アセンブリが低品質であることが判明したことを明確に示しています。
- 列が逆になっています。
- 多くの余分な行 (空であるだけでなく)。
- テーブル ヘッダーはヘッダーとして認識されず、データと混合されます。
これらの問題はすべて非常に簡単に修正できます。Convert Sample File クエリを微調整するだけです。 それに加えたすべての調整は、関連するファイルの変換機能に自動的に分類されます。つまり、後で各ファイルからデータをインポートするときに使用されます。
リクエストを開くことで サンプルファイルの変換、不要な行をフィルタリングするステップを追加します（たとえば、列ごとに） Column2) ボタンで見出しを上げる 最初の行をヘッダーとして使用する (最初の行をヘッダーとして使用). テーブルの見栄えが良くなります。
異なるファイルの列が後で自動的に相互に収まるようにするには、同じ名前を付ける必要があります。 このような大量の名前変更は、XNUMX 行の M コードで以前に作成されたディレクトリに従って実行できます。 もう一度ボタンを押しましょう fx 数式バーで、変更する関数を追加します。
= Table.RenameColumns(#"Elevated Headers", Headers, MissingField.Ignore)
この関数は、前のステップからテーブルを取得します 昇格したヘッダー ネストされたルックアップリストに従って、その中のすべての列の名前を変更します ヘッドライン. XNUMX 番目の引数 MissingField.Ignore ディレクトリにあるがテーブルにない見出しでエラーが発生しないようにするために必要です。
実際、それだけです。
リクエストに戻る レポート まったく異なる画像が表示されます – 前のものよりもはるかに優れています:
- Power Query、Power Pivot、Power BI とは何か、Excel ユーザーがそれらを必要とする理由
- 特定のフォルダー内のすべてのファイルからデータを収集する
- 本のすべてのシートから XNUMX つのテーブルにデータを収集する