複数のテーブルを元に集計したいならパワーピボットがお勧め

もしあなたが複数のデータを元に１つの集計結果を求めなければならないとしたら、どうしたら良いでしょうか。

先日もそんなご相談をいただき、パーソナルレッスンを提供させていただきましたが、その際にお勧めしたのはExcelパワーピボットです。

大量のデータを簡単に集計できるピボットテーブルの進化版として最近注目度の高いExcelパワーピボットですが、「複数のテーブルを元に集計ができる」という点が最大の特徴であり、またパワーピボットの基礎でもあります。

リレーションシップを設定し基本となるデータモデルを構築する

通常のピボットテーブルでは、集計元となるのは１つの表です。それに対し、パワーピボットでは「データモデル」と呼ばれるデータの集まりを集計元として使用します。

このデータモデルには、複数の表を含むことができますが、一元的に扱うために「リレーションシップ」を設定する必要があります。

リレーションシップというのは、例えば「売上データ」という表（テーブル）にある「商品コード」をキーとして、「商品マスタ」にある商品の情報を呼び出せるように結びつけることです。

上の図では、「売上データ」にさらに「顧客マスタ」や「担当者マスタ」といった表とのリレーションシップを設定しています。こうすることで、集計のベースとなる「売上データ」の表には本来「商品分類」や「商品名」、「売価」、「原価」といった商品に関する情報が無かったとしても、あたかも同じ表の中にその情報があるかのように集計することができるようになります。

データモデルを構築するには

データモデルを構築するには、元になる表（テーブル）をデータモデルに追加する必要があります。

いくつかの方法がありますが、初めての場合には１つのBookのシートごとに、それぞれのテーブルを追加して作業するとわかりやすいでしょう。

その後、アクティブセルを追加したい表の中に置いた状態で、［データモデルに追加］ボタンはExcelの［Power Poivot］タブにある［データモデルに追加］ボタンをクリックします。

パワーピボットを初めて使う場合には［Power Pivot］タブがありません。一度［データ］タブの［データツール］グループにある［管理］ボタンをクリックし、［Power Pivot for Excel］が起動するのを確認してから、Excel画面に戻って［Power Pivot］タブが追加されるのを確認してから操作してたください。

データモデルに取り込みたい表を全て上記の手順で「データモデルに追加」してみましょう。

続いてリレーションシップを設定します。

［Power Pivot］タブの［管理］ボタンをクリックするなどして、［Power Pivot for Excel］の画面に切り替えます。

画面下部からシートを切り替えて、取り込んだテーブルが表示されていることを確認しましょう。

その後、［ホーム］タブの［ダイアグラムビュー］ボタンをクリックすると表示が切り替わります。それぞれのテーブルが四角いオブジェクトとして表示されます。囲みの中のフィールド名（列名）部分は掴んでドラッグできるようになっているので、それぞれリレーションシップを設定したいフィールド同士をドラッグして繋ぎましょう。

その際「売上データ」のように、日々積み重なるデータを扱うテーブルを真ん中に、マスタテーブルをその周囲に移動しておくと操作がしやすくなります。

データモデルを元にピボットテーブルを作成する

必要なフィールドをすべて繋ぎ終わったら、基本となるデータモデルが出来上がりです。これを元にピボットテーブルを作成できますので、Book内にできれば新しいシートを作成し、［挿入］タブ-［テーブル］グループから、［ピボットテーブル］のボタンの中の［データモデルから］を選択すると、今作成したデータモデルを元に、ピボットテーブルを作成できるようになります。

もし通常のピボットテーブルで集計しようとするとどうなるか

上記のようにデータが複数の表にわかれている場合、通常のピボットテーブルで集計するためには、事前に１つの表に情報をまとめてやる必要があります。

一般的には「VLOOKUP関数」などの関数を使って１つの表を作成する方法か、パワークエリを使って「クエリのマージ」機能で２つの表をさせておく方法があります。

VLOOKUP関数を使う

元となるテーブルの数が少なく、また参照すべき値も少ない場合には、VLOOKUP関数のようなLOOKUP系関数を使って１つのテーブルを作成する方法もあります。

ただし参照するデータが増えると、その都度関数列を作成しなければいけないこと、レコード（各データに含まれる行）が多い場合、データが重くなりExcel自体の挙動が不安定になってくるなどがデメリットです。

また、一度だけの集計であれば良いのですが、繰り返し集計結果を求めなければならない場合には、都度作表するのは効率が悪いです。

クエリのマージを使う

パワークエリを使ってそれぞれの表を取得し、「クエリのマージ」を使って１つの表を作っておくの方法もあります。

クエリのマージであれば、関数を使う方法よりも簡単ですし、一度クエリを作成してしまえば、次の機会からは「更新」ボタンをワンクリックで１つの表を出力することができます。集計結果であるピボットテーブルまで連携させることも簡単にできます。

ただしこちらも、レコードが多い場合には動作が重くなってくる可能性があります。

まとめ

パワーピボットなら、事前に１つの表を作成することなく、一気にピボットテーブルを作成できます。さらにデータ量が多くても、比較的軽い挙動で安定的に集計結果を求めることができます。元の表はクエリで取得しますので、集計元が更新された場合にももちろんワンクリックで集計結果を更新できます。

ピボットテーブルを使ったことのある人であれば、パワーピボットの利用は決して難しいものではありません。まずはシンプルな「データモデル」を作成して、その便利さを実感してみてください。

パワーピボットの詳細を学びたい方は、ぜひ拙著「Excelパワーピボットで極める一歩先のデータ集計・分析（インプレス社）」をご活用くださいね！