PowerPivotを使って複数の表を集計する
「PowerPivot」を使えば、複数の表の関連付け(リレーション)の操作を簡単・確実にすることができます。
- PowerPivotを使って複数の表を集計する
Excel2013で追加された機能「PowerPibot」
Excel2013以降にはビッグデータを扱うことができるPowerPivotという機能が標準で搭載されています。
「PowerPivot(パワーピボット)」という機能を使えば、「複数の表の関連付け」をより簡単に設定することが簡単にできます。
※ 複数の表を集計する方法はこちらも参考にしてください
ピボットテーブルで複数の表を集計する(リレーションシップ)その1 - Excelの機能を活用して、事務作業の省力化や経営分析をする
PowerPivotタブを表示させます
Power Pivotの操作はリボンのPower Pivotタブで行います。
通常「Power Pivotタブ」はリボンに表示されていないので、設定で表示させます。
PowerPibotの設定は「開発タブ」から行う
Power Pivotタブの表示設定は「開発タブ」から行います、リボンに「開発タブ」が無い場合は、リボンのユーザー設定で表示させます。
- リボン部分の「空白部分」で「右クリック」します
- 「リボンのユーザー設定」をクリック
- Excelのオプションボックスで、「リボンのユーザー設定」を選択
- メインタブの「開発」にチェックを入れます
- 「OK」をクリック
※ 「リボン」の表示方法
「リボン」が表示されていない場合。(※ リボンの機能はExcel2007から)
- 「リボンの表示オプション」をクリック
- 「タブとコマンドの表示」を選択
開発タブで、「COMアドイン」を設定しPower Pivotタブを表示させます
PowerPivotタブが表示されました
PowerPivotを使えば、面倒な関連付け(リレーション)が簡単にできます
PowerPivotを使えば、操作が面倒で分かりにくい関連付け(リレーション)が、手書きのチャート図のような画面で「ドラッグ」操作するだけで簡単にできるようになります。
関連付け(リレーション)の様子が「図」で確認できます
「リレーションシップの作成」は、通常下の図のように操作します
リレーションシップの作成する時はあらかじめ「どんなテーブルがあるのか」、「テーブル内にどんなフィールド(項目)があるのか」を把握する必要があります。
「リレーションシップの作成」の操作では、直接入力する
「リレーションシップの作成」画面では、直接入力していくので全てのテーブルの内容を把握しておく必要がある。
ピボットテーブルで複数の表を集計する(リレーションシップ)その1 - Excelの機能を活用して、事務作業の省力化や経営分析をする
「どこと、どこを、どのように関連付けるか」をまとめたチャート図を作る
「リレーションシップの作成」で関連付けする方法では、それぞれの表の「関連する項目」を把握する必要があり、複雑なリレーションを作成する場合は「チャート図」を作成する必要があります。
フィールド(項目)の関連性を図式化した「チャート図」
Power Pivotの「ダイアグラムビュー」は、ドラッグ操作で関連付けができます
PowerPivotを使えば下図のように「ダイアグラムビュー」という機能で、関連付け(リレーション)の様子が「チャート図」のように表示されます。
関連付け(リレーション)の操作もドラッグ操作でできます。
Power Pivotの操作方法
「PowerPibot」はExcelとは別のプログラムとして立ち上がります。
Excelで作成している「テーブル」を1つ1つ「データモデル」としてPowerPibotに登録していきます。
テーブルをデータモデルに1つ1つ登録する
- データモデルとして登録する「テーブル内のどこか1カ所」を選択
- 「PowerPivot」タブを選択
- 「データモデルに追加」をクリック
- 関連付けたいそれぞれのテーブルで同じ操作を行う
PowerPivot(プログラム)が立ち上がります
PowerPibot はExcelとは別プログラムとして、別ウィンドウで立ち上がります。
最初は「データビュー」画面で立ち上がるので、「ダイアグラムビュー」に切り替ゎります。
「ダイアグラムビュー」ではドラッグ操作ができます
各テーブルはドラッグ操作で移動させることができます
関連付け(リレーション)を行います
関連付けしたい「主」となるフィールドを左クリックで選択し、そのまま関連付け(リレーション)をするフィールドにドラッグするだけで、関連付け(リレーション)ができます。
- 関連付けをする「主」になるフィールドを左クリック
- 左クリックしたまま、関連付けたい相手先までカーソルを動かす(ドラッグ)(※ 黒ラインが出現します)
- 関連付けたい相手にカーソルを重ねてクリックを解除
関連付け(リレーション)が設定された部分に「ライン」が表示されます
ピボットテーブルを作成します
PowerPivotによる関連付け(リレーションシップ)の作業が終わったので、後はピボットテーブルで「リレーションシップ」の操作を行います。
- テーブル内の「どこか1カ所」を選択
- 「挿入タブ」を選択
- 「ピボットテーブル」をクリック
- 「ピボットテーブルの作成ボックス」で、「このデータをデータモデルに追加する」にチェックを入れる
- 「OK」ボタンをクリック
4つのテーブルが「フィールド」に追加されました
PowerPibotを使って関連付け(リレーション)の設定をすることで、簡単に分かりやすく操作することができます。
PowerPivotの本来の機能は非常に大きなデータ(ビッグデータ)を扱うのが不得意なExcelの弱点を補うためのものです。
大きなデータや複数の「表」を関連付け(リレーション)するのは「リレーショナルデータベース」と呼ばれるAccessの得意分野です。
しかし、「クロス集計」などの複雑な集計作業は、ピボットテーブルが使えるExcelの方が得意です。
通常の業務で「ビッグデータ」を使う機会はありませんが、規模の小さなデータでも「リレーションシップ」を作成する場合は、PowerPivotの「ダイアグラムビュー」を使えば、ドラッグ操作だけで簡単にリレーションシップを設定することができます。
- PowerPivotを使って複数の表を集計する