ブログ

HOME
ブログ
ブログ
PowerPivot【02】PowerPivot（パワーピボット）の作成方法【設定編】

2021年3月22日 / 最終更新日時 : 2021年3月23日鹿子木真理子ブログ

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等で活用するなら、いったんテーブルにした方が後でみた方がわかりやすいです。
◆テーブル変換
①テーブル変換する範囲を選択して、②挿入タブ／テーブル　③先頭行をテーブルの見出しと使用するに「ｖ」
◆テーブル名を設定
⑤テーブル上にカーソルを合わせて／テーブルデザインタブ／テーブル名を変更

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の時は、やっておいた方が良いです。
項目名を右クリックして／クライアントツールに非表示（戻すときは「表示」）