Power Query / Power Pivot の極め

Excel小技part3

Power QueryとPower Pivotで売上データを自動集計＆分析する完全ガイド【実習ファイル付き】

ExcelのPower QueryとPower Pivotを組み合わせると、複数ファイルをまとめる「データ加工」と、高速で柔軟な「分析」が一気に実現できます。
今回は、実際のサンプルファイルを使って、フォルダ内の複数Excelを自動結合し、その結果をPower Pivotで分析する流れを体験してみましょう。

1. サンプルファイルの準備

  1. 以下のリンクからZIPファイルをダウンロードして解凍してください。
  2. 解凍後のフォルダには次の4つのExcelファイルが入っています。

サンプルデータ（ZIP）をダウンロード

sales_202506.xlsx
sales_202507.xlsx
sales_202508.xlsx
sales_summary.xlsx  ← 集計用（このファイルを開く）

この中の sales_summary.xlsx を開いて作業を始めます。

2. Power Queryで複数ファイルを自動結合する

  1. sales_summary.xlsx を開き、[データ]タブ → [データの取得] → [フォルダーから] をクリック
  2. 解凍したフォルダを選択して [OK]
  3. フォルダ内のファイル一覧が表示されたら [データの結合] → [結合して変換]
  4. Power Query エディターが開いたら、不要な列を削除し、列名を「日付」「担当者」「商品」「売上金額」に変更
  5. 「売上金額」は数値型、「日付」は日付型に変換
  6. [閉じて読み込む]をクリック → sales_summary.xlsx のシートに統合データが表示されます

この時点で、フォルダに新しい売上ファイルを追加すれば、[更新]ボタンを押すだけで結合データが自動更新されます。

3. Power Pivotを有効化する

  1. [ファイル] → [オプション] → [アドイン] を開く
  2. 下部の「管理」ドロップダウンから「COM アドイン」を選択 → [設定]
  3. Microsoft Power Pivot for Excel にチェックを入れてOK
  4. リボンに「Power Pivot」タブが表示されます

4. Power Pivotにデータを読み込む

  1. sales_summary.xlsx の結合済みデータをアクティブにした状態で、[Power Pivot]タブ → [データモデルに追加]をクリック
  2. Power Pivot ウィンドウが開き、売上データが読み込まれます

5. 追加マスタを作って関係を設定する

部署別の集計を試すため、担当者と部署の対応表（マスタ）を作ります。

  1. 新しいシートに「担当者」「部署」列を作成し、担当者ごとの部署名を入力
  2. [Power Pivot]タブ → [データモデルに追加] でマスタも追加
  3. Power Pivot ウィンドウで [ダイアグラムビュー] を開き、売上データの「担当者」とマスタの「担当者」をドラッグして接続

6. ピボットテーブルで分析する

  1. Power Pivotウィンドウ → [ピボットテーブル] → [新しいワークシート]
  2. 「部署」を行、「商品」を列、「売上金額」を値に配置
  3. 部署別 × 商品別の売上表が作成されます

7. DAXでメジャーを作成する

  1. Power Pivotウィンドウで新しいメジャーを作成
  2. 次の式を入力： Total Sales := SUM([売上金額])
  3. このメジャーをピボットテーブルに追加すると、任意の条件下で合計売上が集計できます

まとめ

今回の流れは、Power Queryでデータを整形 → Power Pivotで分析 という、Excelの最新・実務的なデータ分析手順です。
これを覚えると、毎月の集計やレポート作成が大幅に効率化されます。
さらにDAX関数を活用すれば、前年比、累計、比率などの高度な指標も簡単に追加できます。

コメント