「Power Pivot」ならマスターデータが別ファイルでもピボットテーブルを使える！

Power Pivotでリレーションシップを設定してみよう

　販売管理システムから出力した売上データを、商品カテゴリ別や部署別に集計するような業務がありますよね。ところが、売上データには商品コードや担当者コードしかなく、商品名やカテゴリ、所属部署などは別のマスターファイルに保存されていることもあります。

売上明細データは販売管理システムから出力される

商品マスター（左）や社員マスター（右）は別のファイルとして保存されている

　例えば、商品カテゴリ別の売上を集計するには、商品コードを手掛かりに、VLOOKUP / XLOOKUP関数で商品カテゴリを取り出し、売上明細データに列を追加する方法があります。しかし、集計する項目が増えるたびに列や数式を追加するのは面倒です。

　そこで活用したいのが、「Power Pivot」です。複数の表を関連付けることで、元の表に列を追加せずにピボットテーブルで集計できます。複数のマスターを参照して集計する作業では、特に効果的な方法です。

3つの表をデータモデルに追加して関連付ける

　ここでの課題は、集計に必要な情報が3つの表に分かれていることです。VLOOKUP / XLOOKUP関数を使って必要な情報を取り出す方法もありますが、集計する項目ごとに列を追加することになります。

　また、通常のピボットテーブルでは、基本的に1つの表を集計元とするため、3つの表をそのまま組み合わせることはできません。Power Pivotなら、表を結合せずに集計できます。

　Power Pivotでは、複数の表を「データモデル」に追加して関連付けることができます。今回のように、売上明細と複数のマスターを組み合わせて集計する場合に適しています。

　なお、データモデルに追加する順番に決まりはありませんが、元の表に見出し行が設定されている場合は、［先頭の行を列見出しとして使用する］にチェックを付けてください。表を関連付ける際の目安になります。また、Power Pivotを初めて利用する場合は、データ分析アドインを有効化するように促すメッセージが表示されます。

新しいExcelファイルを開いて操作する。［データ］タブの［データモデル］－［データモデルの管理］をクリックする

データ分析アドインが無効になっている場合は、このメッセージが表示される。［有効化］をクリックする

［Power Pivot for Excel］の画面が表示される。データソースを追加する前なので、何も表示されない。［その他のソース］をクリックする

［Excelファイル］を選択して、［次へ］をクリックする

接続名は特に変更する必要はない。［参照］をクリックして、接続するファイルを指定する。［先頭の行を列見出しとして使用する］にチェックを付ける。［次へ］をクリックする

［基になるテーブル］にチェックを付けて、［完了］をクリックする

［閉じる］をクリックする

接続されたデータ（ここでは売上明細）が［Power Pivot for Excel］の画面に表示される

同様の手順で、商品マスターと社員マスターを追加しておく

　注意したいのは、商品マスターの商品コードや、社員マスターの社員番号が重複していないことです。売上明細データには同じ商品コードや担当者コードが何度も登場しますが、マスター側にはコードごとに1件のデータがある、という関係を前提とします。関連付けるフィールドのデータ型も揃えておきます。

　ダイアグラムビューに切り替えて、3つの表を関連付けましょう。この設定を「リレーションシップ」と呼びます。表同士に共通するコードを指定します。今回のデータでは、次の2つを設定します。

• 売上明細データの「商品コード」と、商品マスターの「商品コード」
• 売上明細データの「担当者コード」と、社員マスターの「社員番号」

［ダイアグラムビュー］をクリックする

表のサイズや位置を調整しておく。商品マスターの商品コードを売上明細データの商品コードにドラッグする。同様に、社員マスターの社員番号を売上明細データの担当者コードにドラッグする

リレーションシップを設定した状態。商品マスターや社員マスターの横に表示されている「1」はキーが重複しない側、売上明細データの「*」は同じキーが複数行に登場できる側を表す

元の表にない項目を使って売上を集計する

　リレーションシップの設定後にピボットテーブルを作成します。フィールドリストに3つの表が表示される点が通常のピボットテーブルとの違いです。

［ピボットテーブル］をクリックする

ピボットテーブルの作成場所を指定する。新しいファイルで操作しているので、［既存のワークシート］を選択して［OK］をクリックする

ピボットテーブルが作成された。フィールドリストに、売上明細データと商品マスター、社員マスターが表示されている。［Power Pivot for Excel］の画面は閉じておく

　これで、3つの表に含まれるフィールドを組み合わせて集計できます。例えば、商品カテゴリ×所属部署の売上金額や、営業エリア・担当者×商品カテゴリの販売数量などを集計できます。

　商品カテゴリや所属部署、営業エリアは売上明細データにはない項目ですが、リレーションシップを設定しているため、そのまま集計に利用できます。

商品カテゴリ×所属部署の売上金額を集計した結果

営業エリア・担当者×商品カテゴリの販売数量を集計した結果

　元の売上明細やマスターの内容が変更された場合も、データモデルのデータを更新すれば、設定済みのリレーションシップを使って集計し直せます。新しいデータに合わせて、VLOOKUP / XLOOKUP関数を入力し直す必要はありません。

リレーションシップを設定し直す場合や、表を追加する場合は［Power Pivot］タブから操作する。［管理］をクリックする

［最新の情報に更新］をクリックする

　ただし、少数の項目を参照するだけなら、VLOOKUP / XLOOKUP関数を使うほうが手軽です。Power Pivotが向いているのは、複数のマスターを参照し、集計する項目を何度も切り替えるような場面です。

　売上以外にも、受注情報と顧客マスター、在庫情報と商品マスターなどを組み合わせる場合に応用できます。集計のたびにマスターを参照して列を追加しているなら、Power Pivotを使う方法も検討してみてください。