Modern Excelで使う機能の一つPower Pivotを紹介。Modern Excelについてはこちら。
本記事ではPower Pivotを使う上で押さえておくべきキーポイントに絞って説明するため、読みながら手を動かせるような懇切丁寧な説明ではない（それなんて入門記事？）。
Power Pivotとは
概要
Power Pivotとは、Excel 2010から導入された機能（アドイン）。以下の機能を持ったデータ モデルというデータ構造を元に分析が行えるピボットテーブル/グラフ:
- 複数テーブルをデータ ソースにできる
- テーブル間の関係性（リレーションシップ）の設定ができる
- DAX(Data Analysis Expressions)を使った計算
Power Pivotを使えば、VLOOKUP関数等を使うことなくテーブル間を連携させ、DAXによる定量的で高度な分析ができる。
Power Pivotの有効化
Power Pivotはアドイン機能として提供されているので、使用するにはアドインを有効化する必要がある。手順は以下:
[ファイル] -> [その他] -> [オプション] -> [アドイン] -> [管理(A):]のプルダウン -> [COM アドイン] -> 右の[設定(G)...] -> [Microsot Power Pivot for Excel]にチェック
通常のピボットテーブルとの違い
| 通常のピボットテーブル | Power Pivot | |
|---|---|---|
| データ ソースの数 | 単独のテーブル | 複数のテーブル |
| データ ソースの場所・形式 | ブック内のデータ | 他のブックを含む様々なデータ ソース |
| 集計機能 | 単純 | 複雑 |
データ ソースの数
データ ソースの数について、通常のピボットテーブルが単独のテーブルのみに対して、Power Pivotは複数とすることが可能。これが意味するところは、通常のピボットテーブルの場合には、単独テーブルの各行で数字を分析の切り口を持たなければならないのに対して、Power Piovotの場合は、データを数字を持つテーブル（ファクト テーブル）と分析の切り口を持つ（複数の）テーブル（ディメンション テーブル）に分けて、それぞれを関連付けながら構造的データを持つことができるということ。
VLOOKUPでいいじゃんとかじゃなくて、今のExcelにはそれ用の機能があるので、大人しく使いましょうということ。
データ ソースの場所・形式
通常のピボットテーブルはデータ ソースの場所が同一ブック内に限られるのに対して、Power PivotはPower Queryと連携することで、アクセス可能な場所にあるあらゆるデータを利用することができる。ローカル、クラウド、Webサイト、システム等。場所だけではなく、形式もExcel、CSV、Access、SQL等々。
Power Queryについてはこちらの入門記事を参照。
VBAでがんばればいいじゃんとかじゃなくて、今のExcelには(ry
集計機能
通常のピボットテーブルでも計算式を定義して、簡単な集計（合計、平均値、加重平均等）を行うことが可能。しかし、Power PovitはDAXメジャーを使用することでかなり複雑な集計をすることも可能。前日比、年度累計、前年同月比、構成比等を、追加的な一時データを用意することなく計算可能。しかも、それら集計がデータの切り口ごとに動的に計算される。これは一時データを用意した計算が特定の切り口でしかできないのに対して、大きなアドバンテージを持つ。
一時データをたくさん用意すればいいじゃんとかじゃなくて、今の(ry
機能・使い方
データ取り込み
データ モデルにデータを取り込む。方法は2種類。Power Queryか、ブック内のテーブルを直接取り込むか。Power Queryで取り込むのが基本だが、簡易的なもの（規模が小さく、複雑ではないもの）ならブック内のテーブルからとってくるのもあり。Power PivotからPower BI（後述）へのステップアップを意識して、なるべくPower Qureyを使っておきたいところ。
Power Queryから
Power Queryを使えば、アクセス可能な場所にあるあらゆるデータを利用することができる。ローカル、クラウド、Webサイト、システム等。場所だけではなく、形式もExcel、CSV、Access、SQL等々。
Power Queryの使い方についてはこちらを参照。
ブック内のテーブルから
テーブル間のリレーション
テーブルを取り込んだら、テーブル間に関係性（リレーション）を設定する。Power Pivoはスター スキーマ（後述）と呼ばれるテーブル分けおよびそれらの関係性を前提としているためそのように作る。
ファクト テーブルとディメンション テーブル
Power Pivotでは複数テーブルを利用できると紹介したが、実際には「できる」ではなく「すべき」である。通常のピボットテーブルでは一つのテーブルの中に数字データとデータ分析の切り口（ピボットテーブルの行見出しなど）になる属性データをまとめて格納する（VLOOKUPU関数などを使ってそうしなければならない）が、Power Pivotでは数字を持つテーブルと（ファクト テーブル）と分析の切り口を持つ（複数の）テーブル（ディメンション テーブル）に分ける必要がある。
ファクト テーブルは行を観測、列を変数とするデータ。列の変数には行を特定するものと、その行の属性を表すもの、行ごとの定量的なものを表すものがある。売上データに例えると、行を特定するものは明細のid、属性を表すものは日付、商品、顧客、担当者など、定量的なものは、数量、売上額など。Power Pivotはデータウェアハウスなので、ファクト テーブルというが、業務システムとかのDB用語ではトランザクションテーブルに当たるもの。
ディメンション テーブルはファクト テーブルの属性を表す列を一意にしたもの。日付、商品、顧客、担当者の一覧。それらに付随する属性もディメンション テーブルで定義する。商品なら、個々の商品が属する大分類、中分類、小分類など。DB用語のマスターテーブルに相当。スター スキーマでは何とか正規化はそこまでやらない。
元データがファクト テーブルとディメンション テーブルに分かれていない場合は、Power Query等で事前に分けておく必要がある。
なお、Power Pivotにこれはファクト テーブル、ディメンション テーブルと色付けする機能があるわけではない。機能というより考え方である点に注意。
リレーション
データをファクト テーブルとディメンション テーブルに分けたら、それらの間の関係性=リレーションを定義する。リレーションは基本的に多対一で作る。ファクト側が多で、ディメンション側が一。
スター スキーマ
デーブルをファクト テーブルとディメンション テーブルに分け、ファクト テーブルを中心にいくつかのディメンション テーブルをリレーションで結ぶ構造をスター スキーマという。
スター スキーマでなくてもPower Pivotは動きはするが、DAXが書きにくくなったり、パフォーマンスが落ちたりする。作った人がスター スキーマを前提にしているので、大人しくそれに従おうよということ。特に初心者なら尚更。昔ながらのExcelの使い方（Modernじゃないやり方）は、神エクセルとかが普通に存在している無法地帯なので、こういうお作法的なものに馴染みが無い人も多いかもしれないが、Modern Excelではそうではないので慣れて欲しい（というかExcelだけが特殊で、他のアプリやプログラミング全般には普通はお作法的なものがある）。
スター スキーマにしたら、分析の切り口をディメンション テーブル側で確実に行うよう、ファクト テーブル側のキー項目を非表示にしておくこと。
なお、引用の引用であるが、スター スキーマにしない場合、以下のとおり意図しない集計となることになる可能性がある。
https://qiita.com/ns-horikoshi/items/a7a68f066f3f7b0bb0d5#2-1-%E5%95%8F%E9%A1%8C%E3%81%8C%E7%99%BA%E7%94%9F%E3%81%99%E3%82%8B%E5%85%B7%E4%BD%93%E4%BE%8B
以下を参考:
日付テーブル
日付のディメンション テーブル。ほとんどのデータ モデルで必要となる。時系列グラフの横軸にしたり、月別・年別集計をしたり、前日比、前年同期比、年度累計の計算で必要。
日付テーブルの要件は以下:
- "日付列" と呼ばれるデータ型 date (または date/time ) の列が必要です。
- 日付列には一意の値が含まれている必要があります。
- 日付列に空白を含めることはできません。
- 日付列に欠落している日付があってはなりません。
- 日付列は年間全体にわたっている必要があります。 1 年は必ずしも暦年 (1 月から 12 月) ではありません。
- 日付テーブルには日付テーブルとしてマークされている必要があります。
最後の二つを忘れがちだけど、よくわからなければそういうルールだと割り切ってちゃんとそうすること。日付テーブルとしてマークするには、作った日付テーブルの日付列を選択し、[デザイン]タブ -> [日付テーブルとしてマークする]で、Power Pivot側に日付テーブルとして認識させる。
以下を参考:
- Power BI Desktop で日付テーブルを作成する
- Power BIの日付テーブル作成メモ
- 日付テーブル は必要か、それが問題だ
- 日付テーブルには 1月1日 から 12月31日 までを含めるのですぞという話。Power BI 謎マナーではありません。
- 日付テーブルとしてマークする とは
階層化
ディメンション テーブルの各列の階層化が可能。よくあるのは日付テーブルに、年-四半期-月-日の日付階層列を作るケース。
[データ]タブ -> [データ モデルの管理] -(Power Pivot起動)-> [ホーム] -> [ダイアグラムビュー] -> 階層化したいディメンション テーブルの列を右クリック -> [階層の作成]
で作れる。日付以外にも、国-地域-都市や商品大分類-中分類-小分類などディメンションに階層がある場合にはあらかじめ作っておくと分析の際に便利。
DAX(Data Analysis Expressions)を使った計算
DAXとは、データ モデル内で使える関数式。DAXの関数式には様々なものが用意されており、それらを使えばデータ モデル内のデータを組み合わせて新しいデータを作ることが可能。スター スキーマにしたのはDAXを効率的に動かすため。
計算列とメジャー
DAXは計算列またはメジャーとして利用可能。
- 計算列: テーブルに新しい列を追加するもの。同じ行内の別の列同士での計算。データ モデル読み込み時に計算されるため、もともとある列と区別なく利用できる。Power Queryでも同じように計算式で列を追加できるが、DAXの方が数学関数等が用意されている点とリレーションを活用できる点でより複雑な計算が可能。
- メジャー: ピボットテーブルの値フィールドに配置したときのみ計算されるもの。ピボットテーブル作成時に計算される。フィルター、行見出し、列見出しなどの抽出条件（コンテキスト）ごとに動的に計算される。つまり、同じ行見出し、列見出しでも、フィルターやスライサーに合わせた計算結果を得ることができる。また、ピボットテーブルの値フィールド以外では利用できない。「集計するための定義」と理解しておく。
以下、メジャーについて説明する。
コンテキスト
コンテキストは、DAXでの集計対象となるデータ（ある一つの行とは限らない）を特定するための条件。行コンテキストとフィルター コンテキストの2つがある。
- 行コンテキスト: 行ごとに計算するDAX（計算列やイテレーター関数）でのひとつひとつの行のこと。
- フィルター コンテキスト: スライサー、フィルター、行見出し、列見出しなどで決まる、集計対象とする行（複数行となることが多い）の条件。
DAXの計算式に明示的に表れないが、DAXはコンテキストを受け取って計算する。
CALCULATE関数
ピボットテーブルから自動的に作成されるコンテキストを元に、新しいコンテキストを作成し、集計対象を変えることができるDAX関数。超重要。
コンテキストを変える=集計対象を変えるなので、CALCULATE関数とコンテキストの変換により、データ モデル内のデータを様々に組み合わせて、欲しい集計結果を得ることができる。これにより、前日比、年度累計、前年同月比、構成比等を、追加的な一時データを用意することなく計算可能。しかも、それら集計がデータの切り口ごとに動的に計算される。
メジャー、コンテキスト、CALCULATE関数についてのマニアックな解説はこちら。
変数
DAX式を書いていると、一つの式だけでは複雑になり過ぎたり、そもそも書けなくなることもある。そういう時は変数を利用する。シンタックスは以下:
メジャー名 =
VAR 変数1 = ...
VAR 変数2 = ...
RETURN 計算式
ピボットテーブルで集計
作成したデータ モデルを元にピボットテーブルを作成するには、[挿入]タブ -> [ピボットテーブル] -> [データ モデルから]の順に選択。後は普通のピボットテーブルのように表やグラフの作成が可能。
Power BIを使おう
ここまで書いてきた機能はPower BIと共通のものなので、Power Pivotを使いこなせれば楽にPower BIを使えるようになる。いきなりPower BIだとExcelとの違いが大きくてとっつきにくいので、まずは手軽に始められるPower PivotでExcelのBI的な使い方に慣れるというのもあり。
Power BIを使うことのメリットは以下:
- より多くのグラフ（=ビジュアル）が利用可能
- よりインタラクティブな分析が可能（あるビジュアルの凡例を選択すると他のビジュアルも連動など）
- クラウドでのレポート共有
より深いデータ分析や、チームとして情報共有を行っていくのならPower BIへステップアップがおすすめ。導入時点で情シスとのやりとりが必要かもしれないが、それだけの価値はある。