ファイル数は変動がなく、データ量だけが異なる可能性の高い CSV ファイルのデータを 1 つにまとめてピボットテーブルで集計を行うようなときには、「これとこれと、これを結合する。」というクエリを準備しておき、ワークシートにはテーブルを読み込まず、ピボットテーブルのデータソースとしてクエリを使用するとよいでしょう。
ここでは例として、[窓口集計] フォルダーに 3 つの CSV ファイル (窓口A.csv、窓口B.csv、窓口C.csv) があり、CSV ファイルを差し替えたときに更新で対応できるようにクエリを作成し、ピボットテーブルで集計を行います。
なお、ここで扱う CSV ファイルには、[No]、[日付]、[商品ID]、[個数] の 4 列があり、商品の出荷履歴がまとめられています。列名と順序はすべてのファイルで同じ、とします。列名が同じであれば順序は異なっていても処理できますが、ここでは定期的にシステムから自動的に出力されるデータであることをイメージして順序も同じにしてあります。 
データソースの指定とクエリの準備
1 つ目の CSV ファイルをデータソースとして指定したクエリを準備し、Power Query エディターで2 つ目と 3 つ目の CSV ファイルをデータソースとするクエリを追加します。
1.リボンの [データ] タブの [データの取得と変換] グループの [データの取得] をクリックし、[ファイルから] － [テキストまたは CSV から] をクリックします。
2.[データの取り込み] ダイアログ ボックスで 1 つ目の CSV ファイルを選択して [インポート] をクリックします。
3.表示されたウィンドウで [データの変換] をクリックします。
ここでは Power Query エディターでクエリの追加や加工作業を行うため [データの変換] を選択しています。
4.Power Query エディターが起動し、クエリが作成されます。
クエリの追加
2 つ目、3 つ目の CSV ファイルに接続するクエリを追加します。
1.Power Query エディターのリボンの [ホーム] タブの [新しいクエリ] グループの [新しいソース] をクリックして [ファイル] － [テキスト CSV] をクリックします。
2.[データの取り込み] ダイアログ ボックスで 2 つ目 (3 つ目) の CSV ファイルを選択して [インポート] をクリックします。
3.表示されるウィンドウで [OK] をクリックします。
4.クエリが追加されます。
5.手順 1. ～ 3. を繰り返して 3 つ目のクエリを追加します。
列の追加
現状では 3 つのテーブルを 1 つにまとめたときにどのファイル (窓口) のデータなのかがわからないため、列を追加して「A」「Ｂ」「Ｃ」の値を格納するように加工します。
1.列を追加するクエリを選択して、リボンの [列の追加] タブの [全般] グループの [カスタム列] をクリックします。
2.[カスタム列] ウィンドウの [新しい列名] に作成する列名を入力して、[カスタム列の式] に格納する値を数式で指定します。 ここでは、窓口Ａ のデータであることがわかるように「="Ａ"」という数式で「Ａ」を格納するようにしています。
3. 列が追加されます。すべてのレコードに「Ａ」の値が格納されています。
4. 残りの 2 つのクエリで手順1. ～ 2. を繰り返して列を追加します。「="Ｂ"」、「="Ｃ"」のようにそれぞれ格納する値を指定する数式を作成します。列名は 3 つのクエリで同じにします。
1 つのテーブルにまとめるクエリの追加
事前に準備した 3 つのクエリによって作成されるテーブルを 1 つのテーブルとなるようにまとめるため、新しいクエリを作成します。
1.リボンの [ホーム] タブの [結合] グループの [クエリの追加] の▼をクリックして [クエリを新規クエリとして追加] をクリックします。
2. [追加] ウィンドウで [3 つ以上のテーブル] を選択して、(右側の) [追加するテーブル] に結合するすべて (3 つ) のテーブルを追加して [OK] をクリックします。
3. 新しいクエリが作成され、3 つのクエリのテーブル データが 1 つにまとめられます。
4. クエリ名を変更するには、クエリを右クリックして [名前の変更] をクリック、編集できる状態になったら名前を入力して [Enter] キーを押します。
5. 前の手順で作成した ([担当窓口]) 列のフィルター ボタンをクリックすると、3 種類の値が表示されて結合できていることが確認できます。確認のみとするため [キャンセル] をクリックします。
列の削除と接続の作成
テーブルに不要な列を削除して、4 つのクエリのデータを利用できるように接続を作成します。
1.列名をクリックして選択して、リボンの [ホーム] タブの [列の管理] グループの [列の削除] をクリックします。複数の列は、[Ctrl] キーや [Shift] キーを使用して同時に選択できます。
2. Power Query エディターのリボンの [ホーム] タブの [閉じる] グループの [閉じて読み込む] － [閉じて次に読み込む] をクリックします。
[閉じて読み込む] を選択すると、新しいワークシートが追加されてテーブルが読み込まれます。ここでは 4 つのクエリのうち、1 つだけをテーブルとして読み込むため [閉じて次に読み込む] を実行して [データのインポート] ダイアログ ボックスで接続のみを作成できるようにしています。
3. [データのインポート] ダイアログ ボックスで [接続の作成のみ] を選択して [OK] をクリックします。
4 つのクエリへの接続が作成されます。この時点でテーブルは読み込まれていません。
ピボットテーブルによる集計
テーブルとして読み込んでもよいのですが、クエリのデータをソースにしてピボットテーブルで集計します。
1.[クエリと接続] ウィンドウでテーブルとして読み込むクエリ (3 つのテーブルを 1 つにまとめたテーブルのクエリ) を右クリックして [読み込み先] をクリックします。
2. [データのインポート] ダイアログ ボックスで [ピボットテーブル] を選択し、[既存のワークシート] の Sheet1 のセル A1 を指定して [OK] をクリックします。
3. ピボットテーブルが作成されます。
4. ピボットテーブルのフィールドを作成して、窓口ごと / 月ごとのクロス集計などを行います。
ここでは、あとで 1 月の データが追加された CSV ファイルに差し替えたときに変化がわかるように月をグループ化して表示しています。
5. 既存の CSV ファイルと同じ名前のファイルに、1 月のデータを追加してファイルを差し替えます。
6. ピボットテーブルを更新します。
7. 下図では、列のグループ化を解除して、任意の階層 (年 → 月) になるように再度グループ化しています。
今回は、テーブルをワークシートに読み込まずに接続だけを準備してワークシートには集計結果であるピボットテーブルだけを作成しました。テーブルとして必要ならワークシートにテーブルとして読み込んでください。
CSV ファイルに更新があったときにピボットテーブルを更新すれば最新の集計結果が確認できればよい、という場合は今回のように接続を作成してピボットテーブルを作成するとよいでしょう。
フォルダー内に集計対象とする CSV ファイルが増加していくようなケースについては、「フォルダー内の複数 CSV ファイルを結合する」を参照してください。
石田 かのこ
