事務作業の省力化や資料作成に役立つ、Excelの使い方を紹介

Excelの機能を最大限に生かして、業務の省力化や経営状況を分析できる資料の作成方法を解説します。

ピボットテーブルで複数の表を集計する(リレーションシップ)(その2)

PowerPivotを使って複数の表を集計する

 「PowerPivot」を使えば、複数の表の関連付け(リレーション)の操作を簡単・確実にすることができます。

 

 

Excel2013で追加された機能「PowerPibot」

 Excel2013以降にはビッグデータを扱うことができるPowerPivotという機能が標準で搭載されています。  

PowerPivot(パワーピボット)」という機能を使えば、「複数の表の関連付け」をより簡単に設定することが簡単にできます。

 

※ 複数の表を集計する方法はこちらも参考にしてください

ピボットテーブルで複数の表を集計する(リレーションシップ)その1 - Excelの機能を活用して、事務作業の省力化や経営分析をする

 

PowerPivotタブを表示させます

 Power Pivotの操作はリボンPower Pivotタブで行います。

 通常「Power Pivotタブ」はリボンに表示されていないので、設定で表示させます。

 

PowerPibotの設定は「開発タブ」から行う

 Power Pivotタブの表示設定は「開発タブ」から行います、リボン「開発タブ」が無い場合は、リボンのユーザー設定で表示させます。

  1. リボン部分の「空白部分」「右クリック」します
  2. 「リボンのユーザー設定」をクリック
  3. Excelのオプションボックスで、「リボンのユーザー設定」を選択
  4. メインタブ「開発」にチェックを入れます
  5. 「OK」をクリック

f:id:k-ohmori9616:20200220101301p:plain 

※ 「リボン」の表示方法

 「リボン」が表示されていない場合。(※ リボンの機能はExcel2007から)

  1. 「リボンの表示オプション」をクリック
  2. 「タブとコマンドの表示」を選択

f:id:k-ohmori9616:20200220102329p:plain

 

 

開発タブで、「COMアドイン」を設定しPower Pivotタブを表示させます

  1. 「開発タブ」を選択
  2. 「COMアドイン」をクリック
  3. COMアドインのボックスでMicrosoft Power Pivot for Excelにチェック
  4. 「OK」を押す

f:id:k-ohmori9616:20200220101745p:plain

 

PowerPivotタブが表示されました

f:id:k-ohmori9616:20200220102630p:plain

 

PowerPivotを使えば、面倒な関連付け(リレーション)が簡単にできます

 PowerPivotを使えば、操作が面倒で分かりにくい関連付け(リレーション)が、手書きのチャート図のような画面で「ドラッグ」操作するだけで簡単にできるようになります。 

関連付け(リレーション)の様子「図」で確認できます

f:id:k-ohmori9616:20200220103742p:plain

 

リレーションシップの作成」は、通常下の図のように操作します

 リレーションシップの作成する時はあらかじめ「どんなテーブルがあるのか」「テーブル内にどんなフィールド(項目)があるのか」を把握する必要があります。

 

「リレーションシップの作成」の操作では、直接入力する

 「リレーションシップの作成」画面では、直接入力していくので全てのテーブルの内容を把握しておく必要がある。

   f:id:k-ohmori9616:20200220130038p:plain

ピボットテーブルで複数の表を集計する(リレーションシップ)その1 - Excelの機能を活用して、事務作業の省力化や経営分析をする

   

「どこと、どこを、どのように関連付けるか」をまとめたチャート図を作る

 「リレーションシップの作成」で関連付けする方法では、それぞれの表の「関連する項目」を把握する必要があり、複雑なリレーションを作成する場合は「チャート図」を作成する必要があります。

 

フィールド(項目)の関連性を図式化した「チャート図」

 f:id:k-ohmori9616:20190611095142p:plain

 

Power Pivotの「ダイアグラムビュー」は、ドラッグ操作で関連付けができます

 PowerPivotを使えば下図のように「ダイアグラムビュー」という機能で、関連付け(リレーション)の様子が「チャート図」のように表示されます。

 関連付け(リレーション)の操作もドラッグ操作でできます。

f:id:k-ohmori9616:20200220121840p:plain


Power Pivotの操作方法

 「PowerPibot」はExcelとは別のプログラムとして立ち上がります。

 Excelで作成している「テーブル」1つ1つ「データモデル」としてPowerPibotに登録していきます。

 

テーブルデータモデル1つ1つ登録する
  1. データモデルとして登録する「テーブル内のどこか1カ所」を選択
  2. 「PowerPivot」タブを選択
  3. 「データモデルに追加」をクリック
  4. 関連付けたいれぞれのテーブルで同じ操作を行う

f:id:k-ohmori9616:20200220124013p:plain

 

 PowerPivot(プログラム)が立ち上がります

 PowerPibotExcelとは別プログラムとして、別ウィンドウで立ち上がります。

 最初は「データビュー」画面で立ち上がるので、「ダイアグラムビュー」に切り替ゎります。

f:id:k-ohmori9616:20200220124756p:plain

 

「ダイアグラムビュー」ではドラッグ操作ができます

 各テーブルはドラッグ操作で移動させることができます

f:id:k-ohmori9616:20200220121840p:plain


関連付け(リレーション)を行います

 関連付けしたい「主」となるフィールド左クリックで選択し、そのまま関連付け(リレーション)をするフィールドにドラッグするだけで、関連付け(リレーション)ができます。

  1. 関連付けをする「主」になるフィールド左クリック
  2. 左クリックしたまま関連付けたい相手先までカーソルを動かす(ドラッグ) 黒ラインが出現します
  3. 関連付けたい相手カーソルを重ねてクリックを解除

   f:id:k-ohmori9616:20200220125450p:plain

 

関連付け(リレーション)が設定された部分「ライン」が表示されます

f:id:k-ohmori9616:20200220125209p:plain

 

ピボットテーブルを作成します

 PowerPivotによる関連付け(リレーションシップ)の作業が終わったので、後はピボットテーブルで「リレーションシップ」の操作を行います。

  1. テーブル内の「どこか1カ所」を選択
  2. 「挿入タブ」を選択
  3. 「ピボットテーブル」をクリック
  4. ピボットテーブルの作成ボックス」で、「このデータをデータモデルに追加する」にチェックを入れる
  5. 「OK」ボタンをクリック

  f:id:k-ohmori9616:20200220130816p:plain

 

4つのテーブル「フィールド」に追加されました

 PowerPibotを使って関連付け(リレーション)の設定をすることで、簡単に分かりやすく操作することができます。

f:id:k-ohmori9616:20200220131142p:plain

  

 PowerPivotの本来の機能は非常に大きなデータビッグデータを扱うのが不得意なExcelの弱点を補うためのものです。

 大きなデータや複数の「表」を関連付け(リレーション)するのは「リレーショナルデータベース」と呼ばれるAccessの得意分野です。

 しかし、「クロス集計」などの複雑な集計作業は、ピボットテーブルが使えるExcelの方が得意です

 通常の業務でビッグデータを使う機会はありませんが、規模の小さなデータでも「リレーションシップ」を作成する場合は、PowerPivotの「ダイアグラムビュー」を使えば、ドラッグ操作だけで簡単にリレーションシップを設定することができます。

 

 

k-ohmori9616.hatenablog.com