PowerPivot【02】PowerPivot(パワーピボット)の作成方法【設定編】

PowerPivot【01】PowerPivot(パワーピボット)で出来ること
上記で、「出来ること」「普通のピボットテーブルとの差」を書いたので、次のステップとして、PowerPivotの設定方法をまとめました。

PowerPivotの処理ステップ一覧

↓クリックすると拡大します。(ごちゃごちゃしていますが、注意すべきことと流れを書いたつもりです)

 

一つずつ話していくとかなり長くなるのですが、まずは、処理の順に最低限知っておきたいことをお話していきます。

※前準備:PowerPivotタブの表示

(無くても対応できるのですが、表示しておくと便利です。)
ファイル/オプション/アドイン/下の管理からCOMアドインを選択
/設定をクリック/Microsoft Power Pivot for Excelを選択

Ⅰ.テーブル構成検討

ファイル構成等をよく考えてから、PowerPivot集計に取りかかって下さい。
PowerPivotでは、複数のファイルをキー結合して集計しますが、その際には以下「1~3」を踏まえて設計する必要があるのです。

1.結合のカタチ

・結合は多対1
・PowerPivotのデータモデル(DataModel)は、以下を組み合わせた「Star Schema (Star構造)」です。
・データテーブル(fact table)
・マスターテーブル(dimension table)
データテーブル:マスターテーブルは 多対1(Many-to-One)の組み合わせです。
上記を踏まえてテーブル(ファイル)を用意する必要があります。
※1対1はつながるものの、「多対1」としてつなぐことになるので、PivotTableで正しく処理が出来ているかどうか都度、確認が必要です。

(以下引用)——————————————-
Microsoft リレーションシップについての注意事項

データ モデルのテーブル リレーションシップでは、一対一 (1 搭乗者につき 1 枚の搭乗券など) または一対多 (1 フライトにつき多数の搭乗者など) が可能ですが、多対多はできません。
多対多のリレーションシップにすると、”循環する依存関係が検出されました” などの循環依存のエラーが発生します。
このエラーは、多対多の 2 つのテーブルを直接関連付けた場合、
または間接的に関連付けた場合 (各リレーションシップ内では一対多でも、エンド ツー エンドでは多対多に見える、テーブル リレーションシップの連鎖) に発生します。
リレーションシップについては、「データ モデルのテーブル間のリレーションシップ」を参照してください。
(引用終わり)——————————————-

2.結合キーは一つ

PowerQueryとは違い、キーは一つだけです
テーブルの結合で複数のキーが必要なときは、以下いずれかの対応が必要です。
・マスタテーブルを「2つ以上」用意して、「迂回形」でつなぐ。
・PowerQuery上で、複数のキーを結合して一つのキーにする。

3.クエリ名称は「マスタテーブル」と「データテーブル」がわかるようにする。

PowerPivot上、キー項目を選択する際は、「1対多」の「1」の方を選ぶ必要があるので、「クエリ名称で区別できる」ようにしておきます。
例えば、「dt_ 、DT_、 mt_、 MT_ PQ参照_」等、わかるようにしておくと良いです。
※Qでは無く、PQ_としているのは、私の環境ではAccessからエクスポートする頻度が多く、その場合、クエリからの出力が「Q_」になっているからです。
クエリ名は、後から変更しずらいので、事前にメモ書き等しておくと良いです。

 

Ⅱ.PowerQuery取り込み

1.テーブル変換

テーブルになっていないリストは、そのままクエリに変換できますが、
PowerPivot等で活用するなら、いったんテーブルにした方が後でみた方がわかりやすいです。
◆テーブル変換
①テーブル変換する範囲を選択して、②挿入タブ/テーブル ③先頭行をテーブルの見出しと使用するに「v」
◆テーブル名を設定
⑤テーブル上にカーソルを合わせて/テーブルデザインタブ/テーブル名を変更

 

2.PowerQuery取り込み・クエリ名称変更

データタブ/データの取得 or  テーブルまたは範囲/PoweQueryを起動する。
詳しくは、PowerQuery【02】PowerQuery(パワークエリー)の動かし方 を見て下さい。

ここで、クエリ名称を変更します。特に「データモデル」に変換した後は、名前を変えるのは大変なことが多いです
(クエリ名を変えても、上手く変更できないケースあり。いったんデータモデルを削除しないと、消しきれないケースもある)

3.PowerQueryエディタ

PowerQueryエディタの使い方は、PowerQuery【02】PowerQuery(パワークエリー)の動かし方 をを見て下さい。

ここでお伝えしたいのは、閉じ方です。
いままでPowerQueryでの閉じ方は「閉じて読み込む」でしたが、この後「データのインポート設定」表示をしたいので
閉じて次に読み込む」にした方が、より便利です。
ただ、「閉じて読み込む」にしても大丈夫です。「クエリ」を右クリックして「読み込み先」をクリックすれば、「データのインポート設定」が表示されます。

4.データのインポート設定

後から変更するときは、上記3の通り、「クエリ」を右クリック/「読み込み先」をクリックしてください。

「データ損失の可能性」のメッセージが出ますが、通常はOKで進みます。

 

5.複数のテーブルやファイルで「2~4」を繰り返す

PowerPivotの対象とするテーブルやファイルについて、以下をくりかえす。
2.PowerQuery取り込み・クエリ名称変更
3.PowerQueryエディタ(閉じて次に読み込む)
4.データのインポート設定

6.クエリグループの作成

必須では無いのですが、後から見たときにわかりやすいと思います。

Ⅲ.リレーションシップの設定

◆リレーションシップの設定方法

リレーションシップの設定方法は、大きく分けて二つあります。(他にもあるかもしれません)

私は、以下の理由から、方法1を推すので、方法1で案内します。
「方法2:データタブ/リレーションシップ」は、分かりやすいが、「データモデル」と「データモデルで無いもの」どちらも選択肢に出るので、区別が付きにくい。
また、Excel2016だと、画面上では、そもそも「データモデル」「ただのテーブル」の区別が付かない。

・方法1 PowerPivotウィンドウ/ダイアグラムビュー
(ダイアグラムビューへの展開方法は以下いずれかです)
・データタブ/データモデルの管理をクリックして、PowerPivotウィンドウへ移動
・PowerPivotタブ/データモデルの管理
※PowerPivotウィンドウのデザインタブからでもリレーションシップは設定できますが、それは後述します。

・方法2 リレーションシップ画面での設定
・データ/リレーションシップ

1.リレーションシップの設定

①以下いずれかで、「データモデルの管理」アイコンをクリック

・データタブ/データモデルの管理をクリックして、PowerPivotウィンドウへ移動
・PowerPivotタブ/データモデルの管理

 

②ダイアグラムビューをクリックして、ダイアグラム画面を表示する

③二つのデータモデルのキーをドラッグしてつなぐ

◆注意事項:接続方法

データテーブル同士を直接接続せず、共有のマスタテーブル(参照テーブル)を介して間接的に接続する。

2.階層の設定

階層を設定しておくとPivotTableで処理するときに便利です。
イメージ出来ないときは、後からでも設定できるので、ここはスキップしても大丈夫です。
(次回以降事例でご案内しておきます)
「項目名を右クリックして/階層の作成」 して設定します。

3.選択すべきで無い項目を隠す

詳細は「8.PowerPivot作成」で書きますが、PowerPivotの項目選択時は、基本的に「1対多」の「1」側を選択する必要があります。
となると、間違えて選択しないように(特に共有していると、他の人は間違う可能性が高いです)、選択すべきで無い「多」側のキー項目を隠します。
必須ではないですが、何度も使うPowerPivotの時は、やっておいた方が良いです。
項目名を右クリックして/クライアントツールに非表示(戻すときは「表示」)

4.確認方法

デザインタブからでもリレーションシップの設定が出来ます。
デザインタブ/リレーションシップの管理で、設定を確認するとわかりやすいので便利です。

5.ご参考

※ちなみに方法2(データ/リレーションシップ)は、こんな感じです。ご参考まで。

Ⅳ.PowerPivot作成

作成方法

1.PowerPivotの作成方法は、今までのPivtoTableと同じ「挿入タブ/ピボットテーブル」です。
違いは、以下2点です。

①表題や「スライサーの活用」を勘案して、B2セル位から、PivotTableを設定する。
②「このブックのデータモデルを使用するに「●」を選択する(PowerQuery上でデータモデルに「v」をつけていれば自動的に「●」がつきます。)

※データモデルウィンドウの「ピボットテーブル」でも同様な処理ができます。

2.ピボットテーブル名を変えて、データモデルの中の項目を選択する。

あとは、通常のピボットテーブルと同じです。ピボットテーブルより、出来ることは色々ありますが、まずは通常のピボットテーブルのように触ってみて下さい。

3.設定しておいた方が良いこと

ピボットテーブル上で、右クリック/ピボットテーブルオプション で、以下①②をすると便利です。

①更新時の列幅の自動調整の「v」を外し、
②データのないアイテムを列に表示する(または行に表示する)に「v」

4.更新方法

PowerQueryの項目を一つずつ更新する方法もありますが、PowerPivotウィンドウで「最新の情報に更新/すべて更新」で一気に更新すると便利です。
ただし、件数が正しいかは、都度確認してください。

 

Ⅴ.PowerPivot次のステップ→ダッシュボード化

スライサー(通常のPivotTableでもあります)
ピボットグラフ
条件付き書式
DAX関数
等で、色々なことができますが、説明が長くなるのと、書くのに時間がかかりそうなので、次回以降にご案内します。
(知らなくても、それなりのものは出来ます)

 

(参考にPowerQuery作成手順も掲載します。)