Power Pivotで複雑なデータモデルとテーブルのリレーションシップをマスターする完全ガイド

Power Pivotは、Excelに搭載された強力なアドインであり、複雑なデータモデルやテーブル間のリレーションシップを構築できるツールです。堅牢なデータモデルを作成することで、高度なデータ計算を実行できます。外部ソフトウェアを必要とせず、動的かつ大規模なデータモデルを構築できる点で、Excelの機能を大きく拡張します。本記事では、実例を交えながら、Power Pivotを使った複雑なデータモデルとリレーションシップの構築方法を詳しく解説します。

Power Pivotとは?

Power PivotはExcelの強力なアドインで、以下のようなことが可能になります。

• 複数のデータソースから大量のデータセットを取り込む
• キー項目(主キー)を使用してテーブル間のリレーションシップを作成する
• DAX(Data Analysis Expressions)による高度な計算を実行する
• 効率的でインタラクティブなダッシュボードやピボットテーブルを作成する

Power Pivotタブを有効化する手順:

• ファイルタブ >> オプションを選択し、Excelのオプションからアドインを選択します。
• 管理ボックスでCOM アドインを選択し、設定をクリックします。
• COM アドインダイアログボックスでMicrosoft Power Pivot for Excelにチェックを入れ、OKをクリックします。

1. Power Pivot用にデータを準備する

Power Pivotで複雑なデータモデルやリレーションシップを構築する前に、データセット内の各テーブルに、各エンティティを一意に識別する一意識別子(主キー)が含まれていることを確認しましょう。

ここでは、以下のテーブルを含む売上データセットを例に考えます。

• Sales(売上):SaleID(各売上の一意識別子)
• Products(製品):ProductID(各製品の一意識別子)
• Customers(顧客):CustomerID(各顧客の一意識別子)
• Regions(地域):RegionID(各地域の一意識別子)
• Dates(日付):Date(各日付の一意識別子)

特にProductID、CustomerID、RegionIDなど、リレーションシップの作成に使用するフィールドについては、テーブル間で値の一貫性が保たれていることが重要です。

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

Power Pivotへのデータ取り込みは、データの種類に応じて複数の方法から選択できます。

他のソースからデータを取り込む場合:

• Power Pivotタブ >> 管理をクリックして、Power Pivotウィンドウを開きます。
• Power Pivotウィンドウ内で外部データの取得 >> その他のソースを選択して、データをインポートします。

既存のExcelブックからデータを取り込む場合:

• データ範囲を選択します。
• 挿入タブ >> テーブルを選択します。

• 各テーブルに「Sales」「Products」「Customers」「Regions」「Dates」などのわかりやすい名前を付けます。

• Power Pivotタブ >> データモデルに追加を選択すると、Power Pivotエディターが開きます。

これらの手順に従うことで、データをインポートできます。

3. リレーションシップを作成する

データの読み込みが完了したら、次にテーブル間のリレーションシップを確立します。

• Power Pivotウィンドウでデザインタブ >> リレーションシップの作成を選択します。
• リレーションシップの作成ダイアログボックスが表示されます。
• 「関連するテーブルと列を選択してください」という指示に従って設定します。
• 以下のマッピングに従って、リレーションシップを1つずつ定義します。
  • Sales[ProductID] → Products[ProductID]:各売上を対応する製品に接続
  • Sales[CustomerID] → Customers[CustomerID]:各売上を対応する顧客に接続
  • Sales[Date] → Dates[Date]:各売上を対応する日付に接続
  • Customers[RegionID] → Regions[RegionID]:各顧客を対応する地域に接続

作成されたリレーションシップ:

代替方法：ダイアグラムビューから作成する

• Power Pivotでダイアグラムビューに切り替えます。
• SalesテーブルのProductIDをProductsテーブルへドラッグします。
• SalesテーブルのCustomerIDをCustomersテーブルへドラッグします。
• SalesテーブルのDateをDatesテーブルへドラッグします。
• CustomersテーブルのRegionIDをRegionsテーブルへドラッグします。

4. 集計列とメジャーの作成

リレーションシップを構築したら、計算や分析を始められます。Power Pivotでは、集計列(計算列)やメジャーを作成することで、より深い洞察を得ることができます。

例：集計列の作成

まず、Salesテーブルに集計列を作成し、各売上の利益(Profit)を計算してみましょう。

• Power PivotウィンドウでSalesテーブルを選択します。
• 列の追加をクリックし、利益を計算するための以下の数式を入力します。

この集計列は、利益の値とともにSalesテーブルに表示されます。列名は「Profit」などに変更できます。

例：メジャーの作成

メジャー1:総売上(Total Revenue)

すべての売上の総売上高を計算するには、Power Pivotでメジャーを作成します。

• Salesテーブルの計算領域に移動します。
• Total Revenueメジャーを作成するために、次のDAX数式を入力します。

このメジャーは総売上を動的に計算し、データモデルに適用されるフィルターやスライサーに応じて自動的に調整されます。

メジャー2:総利益(Total Profit)

総利益を計算するには、計算領域に次のDAX数式を挿入します。

メジャー3:顧客の平均収入(Average Customer Income)

顧客の平均収入を計算するには、計算領域に次のDAX数式を挿入します。

= AVERAGE(Customers[Income])

出力結果:

5. 高度な分析：タイムインテリジェンス

Dateテーブルを用意しておくと、時間軸に基づく分析が可能になります。たとえば、売上トレンドの推移を計算できます。Power Pivotはタイムインテリジェンス関数(TOTALYTD(年初来累計)やSAMEPERIODLASTYEAR(前年同期)など)をサポートしており、異なる期間のパフォーマンス比較が簡単に行えます。

年初来売上(YTD Revenue)を計算するには、次のようなメジャーを作成します。

=TOTALYTD(SUM(Sales[Revenue]),Dates[Date])

このメジャーは、年の初めから選択した日付までの累積売上を計算します。

前年比売上成長率(YoY Revenue Growth)を計算するには、次のDAX数式を挿入します。

=DIVIDE(
SUM(Sales[Revenue]) -
CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Dates[Date])),
CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Dates[Date])),
0)

これにより、前年の同じ期間と比較した伸び率(%)が計算されます。

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

リレーションシップと計算の準備が整ったら、ピボットテーブルやピボットグラフを作成してデータ分析を始めましょう。

• 挿入タブ >> ピボットテーブルを選択します。
• ピボットテーブルの作成ダイアログでデータモデルを選択します。
• ピボットテーブルのフィールドリストに、データモデルに追加したすべてのテーブルとフィールドが表示されます。各テーブルからフィールドを行・列・値エリアにドラッグして、さまざまな分析を行いましょう。

データモデルからの高度な分析例:

• 製品別の総売上を分析する場合:
  • ProductsテーブルのProductNameを行エリアへ、Total Revenueメジャーを値エリアへドラッグします。
• 地域別の売上を分析する場合:
  • RegionsテーブルのRegionNameを行エリアへ、Total Revenueメジャーを値エリアへドラッグします。

さらに、スライサーを追加すると操作性が向上します。たとえば月(Month)のスライサーを追加すれば、月ごとにデータを絞り込んで表示できます。

まとめ

本記事では、実際のデータセットを用いて、Power Pivotで複雑なデータモデルとリレーションシップを構築するプロセスを解説しました。関連するテーブル同士を連携させ、集計列やメジャーを活用することで、製品別・地域別・顧客属性別の売上パフォーマンス把握など、従来のExcel関数だけでは難しい高度な分析が実現できます。ぜひ実際の業務データにも応用してみてください。