Power Pivot を使用して複雑なデータ モデルとテーブルの関係をマスターする

Power Pivot は、ユーザーが複雑なデータ モデルとテーブル間の関係を構築できるようにする強力な Excel アドインです。高度なデータ計算を実行するための堅牢なデータ モデルを作成します。外部ソフトウェアを必要とせずに動的な大規模なデータ モデルを作成できるようにすることで、Excel の機能を拡張します。この記事では、Power Pivot を使用して複雑なデータ モデルと関係を構築する方法を実際の例とともに示します。

Power Pivot とは何ですか?

Power Pivot は、ユーザーが次のことを可能にする Excel の強力なアドインです。

• 複数のソースから大規模なデータセットをインポートします。
• キー/プライマリ フィールドを使用してテーブル間のリレーションシップを作成します。
• データ分析式 (DAX) を使用して高度な計算を実行します。
• 効率的でインタラクティブなダッシュボードとピボットテーブルを作成します。

Power Pivot タブを有効にする:

• ファイルに移動します タブ>> オプションを選択します>> アドインを選択します Excel のオプションから .
• 管理内 ボックス>> COM アドイン を選択します>> [移動] をクリックします。 .
• COM アドインから ダイアログ ボックス>> Microsoft Power Pivot for Excel を選択します。>> [OK] をクリックします。 .

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

Power Pivot で複雑なデータ モデルとリレーションシップを構築する前に、 データセット内の各テーブルに一意の識別子があることを確認してください。 または主キー エンティティごとに。

以下が必要となる販売データセットを考えてみましょう。

• 販売 :SaleID (各セールの一意の識別子)
• 製品 :ProductID (各製品の一意の識別子)
• 顧客 :CustomerID (各顧客の一意の識別子)
• 地域 ：RegionID（各地域の一意の識別子）
• 日付 :日付 (毎日の一意の識別子)

これらの各フィールド、特に ProductID、CustomerID、RegionID などのリレーションシップの作成に使用されるフィールドがテーブル全体で一貫していることを確認してください。

2. Power Pivot へのデータのロード

データを Power Pivot にインポートするには、データの種類に基づいてさまざまなオプションを使用できます。

他のソースからデータをインポートするには、次の手順に従います。

• Power Pivot に移動します。 タブ>> [管理] をクリックします をクリックして、Power Pivot ウィンドウを開きます。
• Power Pivot ウィンドウで>> [外部データの取得] をクリックします。>> [他のソースから] を選択します。 データをインポートするには。

既存の Excel ワークブックからデータをインポートするには:

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

• 各テーブルに「Sales」などの名前を付けます。 、製品 、顧客 、地域 、 そして日付 .

• Power Pivot に移動します。 タブ>> [データ モデルに追加] を選択します。 Power Pivot エディタを開きます。 .

次の手順に従って、データをインポートできます。

3.関係を築く

データがロードされたので、これらのテーブル間の関係を確立する必要があります。

• Power Pivot ウィンドウで、デザインに移動します。 タブ>> 関係の作成を選択します .
• 関係を作成する ダイアログボックスが表示されます。
• 「相互に関連するテーブルと列を選択する」
• 次のマッピングを 1 つずつ使用して関係を定義します。
  • 販売[ProductID] -> 製品[ProductID] :各売上を対応する商品に結び付けます。
  • 販売[顧客ID] -> 顧客[顧客ID] :各販売を対応する顧客に結び付けます。
  • 売上[日付] -> 日付[日付] :各セールを対応する日付に結び付けます。
  • 顧客[地域ID] -> 地域[地域ID] :各顧客を対応する地域に接続します。

関係:

代替:ダイアグラム ビューから

• ダイアグラム ビューに切り替えます Power Pivot で。
• 製品 ID をドラッグします 売上から 製品の表 テーブル。
• 顧客 ID をドラッグします 売上から 顧客 へのテーブル テーブル。
• 日付 をドラッグします。 売上から 日付 の表 テーブル。
• 地域 ID をドラッグします 顧客から 地域の表 テーブル。

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

関係を構築すると、計算と分析の実行を開始できます。 Power Pivot を使用すると、計算列とメジャーを作成して、より深い洞察を得ることができます。

例:計算列

売上に計算列を作成しましょう。 利益を計算するための表 各セールから:

• Power Pivot 内 ウィンドウ>> 販売を選択します。 テーブル。
• [列の追加] をクリックします。 次の式を挿入して利益を計算します。

この計算列は売上に表示されます。 利益値を含むテーブル。列の名前を「利益」に変更できます。

例:メジャーの計算

対策 1:総収益

すべての売上の合計収益を計算するには、Power Pivot でメジャーを作成できます。

• セールス内 表の計算エリアに移動します。 .
• 次の DAX 式を入力して、総収益を作成します。 メジャー:

このメジャーは、データ モデルに適用されるフィルターまたはスライサーに基づいて調整され、総収益を動的に計算します。

対策 2:総利益

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

対策 3:顧客の平均収入

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

= AVERAGE(Customers[Income])

出力:

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

日付付き テーブルを使用すると、経時的な売上傾向の計算など、時間ベースの分析を実行できます。 Power Pivot はタイム インテリジェンスをサポートします TOTALYTD (年累計) や SAMEPERIODLASTYEAR などの関数を使用すると、さまざまな期間のパフォーマンスを比較できます。

年初から現在までの収益を計算するには 、次のようなメジャーを作成できます:

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

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

前年比収益を計算するには 成長する場合は、次の DAX 式を挿入します。

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

これにより、前年同期と比較した成長率が計算されます。

6.データを分析するためのピボット テーブルの作成

リレーションシップと計算を適切に設定したら、データを分析するためのピボットテーブルとピボットグラフの作成を開始できます。

• 挿入に移動します タブ>> ピボットテーブルを選択します .
• [ピボットテーブルの作成] で ダイアログ>> データモデルからを選択します .
• ピボットテーブル フィールド リストには、データ モデルに追加したすべてのテーブルとフィールドが表示されます。テーブルからフィールドを行にドラッグします。 、コラム 、 そして価値観 さまざまな分析を実行する領域

モデルからの高度な分析情報:

• 製品ごとの総収益を分析するには;
  • 製品名をドラッグします 製品から テーブルを行に追加 エリアと総収益 価値観に基づいて測定する
• 地域ごとの収益を分析するには;
  • 地域名をドラッグします 地域から テーブルを行に追加 エリアと総収益 価値観に基づいて測定する

インタラクションを改善するためにスライサーを追加することもできます。たとえば、月のスライサーを追加できます。 異なる月ごとにデータをフィルタリングします。

結論

実用的なデータセットを使用して、Power Pivot で複雑なデータ モデルと関係を構築するプロセスを示しました。これにより、従来の Excel 機能だけでは実現が困難であった高度な分析を実行できます。関連するテーブルをリンクし、計算列とメジャーを使用することで、製品、地域、顧客層ごとの販売実績の理解など、データについてより深い洞察を得ることができます。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!