2 つのテーブルをもとにピボットテーブルを作成する

たとえば、売上情報を管理している [売上] テーブルがあるとします。

このテーブルでは、顧客や売上⾦額などの売上に関わるデータを管理しているのですが、効率よく入力を行うために「顧客名」ではなく「顧客ID」が、社員の「氏名」ではなく「社員番号が入力されているとします。
(Access などのデータベースでは、このように管理されていることが多いです。)

このテーブルを集計元データとしてピボットテーブルで集計をすると、A のように社員番号ごとの金額の集計はできますが、テーブルに社員の氏名情報はないので、B のように社員の氏名を表示することはできません。

2-00.png


B のピボットテーブルを作りたいとき、売上とは別に用意されている担当者情報のテーブルがあれば、[売上] テーブルに新しい列を追加して VLOOKUP 関数などを使った数式によって、[担当者] テーブルから社員の氏名を表示できます。

この加工ができれば集計元データに氏名を含められるため、B のピボットテーブルを作成できます。

2-01.png

 

これも 1 つの方法ですが、Excel 2013 / 2016 では、集計に使用するリスト (一覧表) をテーブルに変換すると、「リレーションシップの設定」という機能を使って 2 つのテーブルを関連付けることができ、1 つのテーブルからもう⼀⽅のテーブルのデータを参照できるようになるため、VLOOKUP 関数などを使⽤して 2 つのテーブルを 1 つにまとめるといった事前の作業をしなくても、B のピボットテーブルを作成できます。

Excel のテーブルをリレーショナル データベースとして
利用するには

テーブルを使う


⾏と列で構成されている表のことを「テーブル」と表現し、この考え⽅を機能化しているのが Excel のテーブルです。

⾏は「レコード」といい、1 ⾏に 1 件の情報を保存します。列は「フィールド」といい、各項⽬を表します。
たとえば、社員の情報を保存する [担当者] テーブルでは、1 ⾏に 1 名分の社員の情報が保存され、各項⽬に「社員番号」や「氏名」といった決められた情報を保存します。


2-12.png
 

テーブルについては ちょこテクでも何度かご紹介しているので、よろしければ 過去の記事 などを参照してください。

 


テーブルの役割 (種類) を確認する / 2 つのテーブルを関連付ける


複数のテーブルを関連付けして 1 つのデータベースとして扱うとき、テーブルは、「⽐較的変動の少ない情報」を管理している「主テーブル」と、「頻繁に変動のある記録となる情報」を管理している「リレーション テーブル」に分けられます。

たとえば、下図では、[担当者] テーブルが「主テーブル」、[売上] テーブルが「リレーション テーブル」です。
2 つのテーブルは、共通のデータを持つ  [社員番号]  フィールドで関連付けることができます。(フィールド名は一致していなくてもよし)

専門的な言い方をすると、共通のフィールドはそれぞれ、主テーブルの「主キー」とリレーション テーブルの「外部キー」です。
「主キー」は、レコードを⼀意に識別するフィールドなので、1 つのテーブルに 1 回しか登場しません。(重複しません)
「外部キー」は日々増えていく明細データの一部なので、何度も出てくる可能性があります。

外部キーを主キーと関連付けることで、リレーション テーブルから主テーブルの情報を参照できるようになります。
リレーション テーブルから、「S001 さーん。お名前を教えてくださーい。」っていったら、主テーブルから「はーい。徳川です」っていう返事が返ってくる、そのやり取りをするためのつながり、という感じでしょうか。

04.png
 

このあとの手順で出てきますが、リレーションシップの設定は、[リレーションシップの作成] ダイアログ ボックスの [テーブル] に「リレーション テーブル」を、[関連テーブル] に「主テーブル」を指定します。
どっちがどっちなの?ということが理解できていないと、この設定が思うようにできません。


2-02.png
 

「リレーショナル データベース」とは、担当者の情報を管理するテーブルと、売上情報を管理するテーブルのように、⽬的別に複数のテーブルに分けているときに、必要に応じて 2 つのテーブルを結合して利⽤できるデータベースです。

[担当者] テーブルと、売上を⼊⼒する [売上] テーブルの 2 つを [社員番号] という共通のフィールドで関連付けて、リレーショナル データベースとすることで、2 つのテーブルを 1 つのテーブルのように利⽤して、ピボットテーブルを作成することができます。


2-03.png
 


リレーションシップの設定 (操作)


テーブルにリレーションシップを設定するには、次のように操作します。
しつこいようですが、リレーションシップを設定するリスト (一覧表) は、事前にテーブルに変換しておいてください。

ここでは、[売上] テーブルの [社員番号] フィールドと [担当者] テーブルの [社員番号] フィールドを関連付けて、リレーションシップを設定します。
 

  1. リボンの [データ] タブの [データ ツール] グループの [リレーションシップ] を
    クリックします。
    2-04.png
     
  2. [リレーションシップの管理] ダイアログ ボックスの [新規作成] をクリックします。
    08.png
     
  3. [テーブル] と [関連テーブル] はどちらを先に選択してもいいのですが、[テーブル] でリレーション テーブル ([売上] テーブル) を選択して、[列 (外部)] で関連付けるフィールド ([社員番号] フィールド) を選択し、 [関連テーブル] で主テーブル ([担当者] テーブル) を選択して、[関連列 (プライマリ)] で関連付けるフィールド ([社員番号] フィールド) を選択して、[OK] をクリックします。
    2-10.png
     
  4. リレーションシップが作成され、[リレーションシップの管理] ダイアログ ボックスに表⽰されます。
    [閉じる] をクリックします。
    11.png

    2 つのテーブルにリレーションシップを設定したということは、2 つのテーブルのデータが、分析用データとして「データ モデル」というワークシートとは別の場所に準備された、とイメージしてください。

 

(おまけ) データ モデルの確認

Office Professional Plus 2016 (または Office 365 ProPlus) の Excel 2016 の場合は、リボンの [データ] タブの [データ ツール] グループに [データ モデルの管理] コマンドがあり、クリックすると [Power Pivot for Excel] が起動します。

[Power Pivot for Excel] ではデータ モデルの中身を確認できる、と思えばよいです。

2-05.png

[Power Pivot for Excel] ウィンドウの [ホーム] タブの [ダイアグラム ビュー] をクリックし、リレーションシップを表している線にマウス ポインターを合わせると、各テーブルのどのフィールドが関連付けられているのかがわかります。


確認が終わったら [Power Pivot for Excel] ウィンドウを閉じます。

13.png

 

 

他方のテーブルのデータを参照するピボットテーブルを作成するには

リレーションシップの設定されている 2 つのテーブルのデータ (=データ モデル) を使ってピボットテーブルで集計します。

ここでは、[売上] テーブルと [担当者] テーブルにリレーションシップが設定されていることを前提に、[売上] テーブルの [⾦額] を [担当者] テーブルの [⽒名] ごとに集計した結果を表⽰するピボットテーブルを作成します。

これを作る↓
14.png
 

  1. ピボットテーブルを作成する場所 (シートとセル) を選択し、リボンの [挿⼊] タブの [テーブル] グループの [ピボットテーブル] をクリックします。
    2-06.png
     
  2. [ピボットテーブルの作成] ダイアログ ボックスの [外部データ ソースを使用] をオンにして、[接続の選択] をクリックします。

    データ モデルを集計元データとするため、「ワークシート上のデータではない = 外部データ ソース」を選択する、と考えてください。
    2-07.png
     
  3. [既存の接続] ダイアログ ボックスの [テーブル] タブを選択し、[このブックのデータ モデル] で使用するデータ モデルを選択して、[開く] をクリックします。
    2-08.png

     
  4. [ピボットテーブルの作成] ダイアログ ボックスで、集計元データとしてデータ モデルが選択され、事前に指定した作成場所が表示されています。

    [OK] をクリックします。
    2-09.png

     
  5. ピボットテーブルが作成されます。

    [ピボットテーブルのフィールド リスト] で [すべて] (Excel 2013 の場合は [すべてのフィールド]) が選択されているとき、フィールド セクションには、ブック内のテーブルやリレーションシップが設定されているテーブルが表示されています。

    [売上] テーブルの [金額] フィールドを [値] エリアに配置し、[担当者] テーブルの [氏名] フィールドを [行] エリアに配置すると、[売上] テーブルの [⾦額] を [担当者] テーブルの [⽒名] ごとに集計した結果がピボットテーブルに表示されます。

    VLOOKUP などを使って加工しなくても、2 つのテーブルを関連付けてピボットテーブルによる集計ができました。
    2-11.png

 


事前にリレーションシップを設定していなくても、ピボットテーブルを作成しているときに、「リレーションシップがありませんよ、作りますか?」と聞いてくれて、そのときに設定することができるし、Excel 2016 であれば、[自動検出] という機能を使って、データの特徴から自動的にリレーションシップを設定することができたりします。

20.png


それでもやっぱり、ちゃんと事前に、どちらのテーブルがどういったデータを管理していて、どの列とどの列が関連付けられているのか、などを理解したうえで使うべきだと思います。なんとなくやったらできちゃったでは、やりたいことができないときがきます。

たとえば、いつもすべてのデータが 1 つの表にまとまっているわけではないでしょうし、データ分析を行うときに、別の場所で集められたデータと組み合わせる必要が出てきたりします。

Access などをお使いでない方は、リレーショナル データベースとか、リレーションシップなどという言葉や仕組みになじみがないかもしれませんが、今後は Excel でもこんなことができるのが当たり前になっていきますので、どこかでお勉強しておくとよいかもしれません。
 

石田かのこ