PowerPivotでVLOOKUPを実現するといろんな軸で分析できるようになる-30代後半で転職したSEのブログ

Excelでよく使われるVLOOKUP関数ですが、ExcelのPowerPivotを使用するとピボットテーブルで表示するデータに対しても関連する項目を表示することができます。

今回は下記のようなデータモデルの設定方法についてみていきます。

今回使用するデータは以下のものです。

【サーバー】テーブル

【管理者】テーブル

【OS】テーブル

下記のように各管理表をExcel内でテーブル化していきます。

テーブル化したら名前を変更しておきます。

同様に管理者の一覧表とOSの一覧表をテーブル化します。

各テーブル間の関連性を設定するために、データモデルを作成していきます。

初めに各テーブルをデータモデルの対象に設定していきます。

やり方はテーブルを選択した状態で、

PowerPivot→データモデルに追加をクリックします。

他テーブルもデータモデルに追加します。追加が完了すると以下のような

Windowが表示されます。続けて、ダイアグラムビューを選択します。

ダイアグラムビューで項目間のリレーションを設定します。

サーバーテーブルと管理者テーブルのリレーションを設定するには

サーバーテーブルの管理者IDをドラッグして管理者テーブルの管理者IDのところで離します。

同じく、サーバーテーブルのOSとOSテーブルのOSとのリレーションを設定します。

管理者テーブルやOSテーブルの項目をドラッグしてリレーションを設定すると

多対１ではなく、1対多になってしまうのでご注意ください。

ダイアグラムビューのピボットテーブルを選択するとこのデータモデルをベースに

ピボットテーブルを作成することができます。

まず、テーブル間のリレーションがピボットテーブル内で維持されるようにするため、

サーバーテーブルの【OS】と【管理者ID】の項目を下記のようにドラッグアンドドロップ

します。

この後は自分の分析し体軸を行のところに持ってくれば、管理者IDごとのサーバー状況や

OSのバージョンごとのサーバー台数など分析することができます。