週ごとや月ごとなど、定期的に増加する CSV ファイルを 1 つにまとめて集計を行うようなときには、決められた場所 (フォルダー) に CSV ファイルを追加して、更新作業をするだけでワークシート上のテーブルにデータが追加されるようにしておくとよいでしょう。
ここでは例として、[期間集計] フォルダーに 2 つの CSV ファイル (10月.csv、11月.csv) が含まれている状態でスタートし、12月.csv などがあとから追加されていくときにも更新で対応できるようにクエリを作成します。
なお、ここで扱う CSV ファイルには、[No]、[日付]、[商品ID]、[個数] の 4 列があり、商品の出荷履歴がまとめられています。列名と順序はすべてのファイルで同じ、とします。列名が同じであれば順序は異なっていても処理できますが、ここでは定期的にシステムから自動的に出力されるデータであることをイメージして順序も同じにしてあります。
データソースの指定とクエリの準備
CSV ファイルが格納されているフォルダー (デスクトップの [期間集計] フォルダー) を指定してファイルを結合するクエリを作成します。
1.リボンの [データ] タブの [データの取得と変換] グループの [データの取得] をクリックし、[ファイルから] － [フォルダーから] をクリックします。
2.[参照] ダイアログ ボックスで CSV ファイルを格納しているフォルダーを選択して [開く] をクリックします。
3.表示されたウィンドウで [結合] － [データの結合と変換] をクリックします。
ここでは CSV ファイルを結合して 1 つのテーブルにして、Power Query エディターで加工作業を行うため、 [データの結合と変換] を選択しています。[結合と読み込み] を選択すると結合したテーブルがワークシートに読み込まれます。結合した表の読み込み先ワークシートを指定したり、テーブル以外を使用したりしたい場合などは [結合及び読み込み先] を選択します。
4.[ファイルの結合] ウィンドウでプレビューを確認して [OK] をクリックします。
5.Power Query エディターが起動し、クエリが作成されます。
6.[Source.Name] 列のフィルター ボタンをクリックすると、ソースとなっている 2 つのファイル名が表示され、結合できていることが確認できます。確認のみとするため [キャンセル] をクリックします。
テーブルの加工
テーブルには不要な列を削除したり、足りない列を追加したりといった加工作業を行います。
インデックス列を使って「1」からの連番を振る
データ ソースとなっている CSV ファイルの [No] フィールドには、それぞれのファイルで「1」から連番が格納されているため、全体を通しての番号ではなく重複があります。「1」を開始値とするインデックス列を追加して全体を通した連続番号が表示される列を作成します。
1.リボンの [列の追加] タブの [インデックス列] － [1 から] をクリックします。
2.[インデックス] 列が作成されます。
[No] 列は途中から (11月.csv のデータから) 「1」が再開していますが、[インデックス] 列では通した番号になっていることが確認できます。
列の削除
テーブルに不要な列を削除します。
1.列名をクリックして選択して、リボンの [ホーム] タブの [列の管理] グループの [列の削除] をクリックします。複数の列は、[Ctrl] キーや [Shift] キーを使用して同時に選択できます。
2.選択した列が削除されます。
この処理に限った話ではないですが、行ったステップを取り消す場合は、[適用したステップ] で削除できます。
列名の変更
列名を変更する見出しをダブル クリックして編集できる状態になったら変更後の名前を入力して [Enter] キーを実行します。
列の移動
列の順序を変更するには、見出しにマウス ポインターを合わせて移動先へドラッグ アンド ドロップします。
ワークシートに読み込む
加工して整形したテーブルを既存のワークシートに読み込みます。
1.Power Query エディターのリボンの [ホーム] タブの [閉じる] グループの [閉じて読み込む] － [閉じて次に読み込む] をクリックします。
[閉じて読み込む] を選択すると、新しいワークシートが追加されてテーブルが読み込まれます。ここでは既存の Sheet 1 に読み込むために [閉じて次に読み込む] を実行して [データのインポート] ダイアログ ボックスでワークシートのセル番地を指定できるようにしています。
2.[データのインポート] ダイアログ ボックスで [テーブル] を選択し、[既存のワークシート] の Sheet1 のセル A1 を指定して [OK] をクリックします。
3.指定したワークシートに結合されたデータがテーブルとして読み込まれます。
テーブル名はクエリ名と一致しています。テーブルの配色などのデザインは変更できます。
今回使用しているデータでは、現時点で 132 レコード含まれています。
フォルダーへの CSV ファイルの追加
フォルダーに列の構成が一致している CSV ファイル (12月.csv) を追加して、更新を実行するとテーブルにレコードが追加されることを確認します。
1.リボンの [データ] タブの [クエリと接続] の [すべて更新] をクリックします。
2.フォルダーに追加した 12月.csv のデータがテーブルに追加されます。
クエリのマージをしてマスターテーブルの情報を結合する
前の手順で作成したテーブルには [商品ID] 列のみが含まれており、商品名や単価は含まれていません。履歴を記録するテーブルではよくある構造です。
ここでは、商品 ID や商品名、商品の単価がまとめられている Excel ブックのテーブルをマージして、テーブルに商品名と単価も表示します。
[List] フォルダーの商品リスト.xlsx にある「Product」テーブルのデータを使用できるようにクエリを編集します。
1.リボンの [クエリ] タブの [編集] グループの [編集] をクリックします。
2.Power Query エディターが起動します。リボンの [ホーム] タブの [新しいクエリ] グループの [新しいソース] をクリックし、[ファイル] － [Excel ブック] をクリックします。
3.[データの取り込み] ダイアログ ボックスでファイルを選択して [インポート] をクリックします。
4.[ナビゲーター] ウィンドウでデータ ソースとするテーブルを選択して [OK] をクリックします。
5.クエリが追加されます。
6.結合する (列としてデータを追加する) クエリ (期間集計) を選択している状態で、リボンの [ホーム] タブの [結合] グループの [クエリのマージ] をクリックします。
7.[マージ] ウィンドウの下のドロップダウン リストで追加したクエリを選択します。
8.テーブル間でデータを参照するために、共通のデータが格納されている列をクリックします。 今回はたまたまどちらのテーブルも [商品ID] 列ですが、列名は異なっていても同種のデータが格納されていればよいです。リレーションシップを設定するときのキーとなる列だと考えればよいでしょう。
9.[OK] をクリックします。
10.マージしたテーブルのデータが含まれる列が追加されます。
11.追加された列の展開ボタンをクリックして、列として追加するフィールド名 ([商品名] と [単価]) のチェックのみをオンにして、[元の列名をプレフィックスとして使用します] をオフにして [OK] をクリックします。
12.チェック ボックスをオンにしていた列が追加されます。
13.列の配置を変更する場合は、列名をドラッグ アンド ドロップして移動します。
14.Power Query エディターのリボンの [ホーム] タブの [閉じる] グループの [閉じて読み込む] － [閉じて次に読み込む] をクリックします。
15.[データのインポート] ダイアログ ボックスで [接続の作成のみ] を選択して [OK] をクリックします。
「Product」テーブル自体はワークシートに読み込む必要がないため、接続のみを作成しています。
16.テーブルに追加した列が表示されます。
ワークシートのテーブルの加工
Power Query エディターでクエリを編集して数式による処理結果を列として含めることもできますが、ワークシートに読み込んだテーブルに計算列を追加することもできます。
ここでは、テーブルに [金額] 列を作成し、[単価] 列と [数量] 列を乗算する数式を作成しています。
フォルダーにファイルを追加してテーブルを更新したときに、レコードが追加されるだけでなく、数式も維持されていることが確認できます。
クエリの準備など、最初は少し作業に時間を取られますが、一度作ってしまえば更新作業だけで適切な加工と結合が行われるため、ミスの内容に手作業&チェックに時間をかけているという方は選択肢の 1 つとして検討してみるとよいでしょう。
石田 かのこ
