「テーブルのリレーションシップ」とピボットテーブル

下図の [売上] テーブルには、「No」「日付」「社員番号」「顧客ID」「金額」のフィールドがあり、このテーブルを元にピボットテーブルを作成して「社員番号」ごとの「金額」の合計を算出すると、こんな感じ↓になります。

わかりやすいかどうかは別として、社員ごとの金額の合計は算出できています。

このままではちょっとわかりにくいので、「社員番号じゃなくて、社員名でピボットテーブルに集計結果を表示したい」としましょう。

こんなとき、社員番号の隣に 1 つずつ社員名を入力していくのはたいへんなので、
[社員リスト] のような別テーブルに「社員番号」と「氏名」をまとめ、VLOOKUP 関数を使って社員名を表示したりしませんか？

集計元データに「社員名」フィールドを追加することができれば、ピボットテーブルでそのフィールドの値を利用できるため、こんな風に↓社員の名前で集計結果を表示できます。

逆に言うと、集計元データを加工しなければならない、ということです。

Step1　テーブルのリレーションシップ

Excel 2013 以降のバージョンでは、「リレーションシップの設定」という機能を使って、2 つのテーブルを (ここ大事、普通の表ではなくテーブル) を結合して、1 つのピボットテーブルで集計できるため、VLOOKUP 関数などを使用して集計元データをまとめるなどといった事前の作業が不要になります。

テーブルにリレーションシップを設定して利用するには、2 つのテーブルの特徴と、目的に合わせてどのように関連付けを行うべきかを検討する必要があります。

■　Excel のテーブルをリレーショナルデータベースとして利用するには
「リレーショナルデータベース」という言葉を聞いたことがありますか？
リレーショナルデータベースは、目的別に複数のテーブルに分けて保存されているデータを必要に応じて結合して利用できるデータベース (データの集まり) です。
たとえば、Access はリレーショナルデータベース管理ソフトの 1 つです。

行と列で構成されている表のことを「テーブル」と表現し、この考え方を機能化しているのが Excel のテーブルです。
行は「レコード」といい、1 行に 1 件の情報を保存します。列は「フィールド」（または「カラム」）といい、各項目を表します。
たとえば、社員の情報を保存する「社員リスト」テーブルでは、1 行に 1 名分の社員の情報が保存され、各項目に「社員番号」や「氏名」といった決められた情報を保存します。

売上が発生したときに、「売上」を入力して管理するテーブルがあるとします。
このテーブルには、[顧客名] や [金額] などの売上にかかわるデータを入力します。

このとき、1 つのテーブルで管理すると、同じ顧客の複数の売上を入力するときに、毎回、担当者の名前を入力したり、顧客名を入力したりしなければなりません。同じ担当者であっても、「織田」「織田信長」というような入力ミスが発生する可能性もあります。

そのため、「社員番号」「氏名」などが入力された [社員リスト] テーブルと、売上を入力する [売上] テーブルの 2 つに分けて管理し、2 つのテーブルを [社員番号] という共通のフィールドで関連付けると、2 つのテーブルを 1 つのテーブルのように利用してピボットテーブルを作成することができます。

この関連付けの設定のことを「リレーションシップの設定」といいます。

■　テーブルの種類を確認するには
Excel で集計をしたいデータを目的別に複数のテーブルに分けているとします。
このテーブルは「比較的変動の少ない情報」 ([得意先]、[商品] など) を管理している「主テーブル」と、「日々の活動の記録となる情報」 ([売上]、[仕入] など) を管理している「リレーションテーブル」に分けられます。

たとえば、下図では、[社員リスト] テーブルが「主テーブル」、[売上] テーブルが「リレーションテーブル」です。

2 つのテーブルを関連付けるには共通のフィールドが必要です。
共通のフィールドは、主テーブルの「主キー」とリレーションテーブルの「外部キー」です。

「主キー」とは、各レコードを一意に識別するフィールドで、「外部キー」は、主テーブルと関連付けるフィールドです。
外部キーを主キーと関連付けることで、リレーションテーブルから主テーブルの情報を参照できるようになります。

リレーションシップの設定は、[リレーションシップの作成] ダイアログボックスの [テーブル] ボックスに「リレーションテーブル」の名前を、[関連テーブル] ボックスに「主テーブル」の名前を指定します。
そのため、それぞれのテーブルの種類を把握しておく必要があります。

Step2　リレーションシップの設定

テーブルにリレーションシップを設定するには、次のように操作します。

なお、リレーションシップの設定をする表は、テーブルでなければなりませんので、事前にテーブルに変換してください。

ここでは、[売上] テーブルの [社員番号] 列と、[社員リスト] テーブルの [社員番号] 列にリレーションシップを設定することを例にします。

リボンの [データ] タブの [データツール] グループの [リレーションシップ] をクリックします。
[リレーションシップの管理] ダイアログボックスの [新規作成] をクリックします。
[テーブル] で「リレーションテーブル」(売上などの明細データのテーブル) を選択し、[列 (外部)] で、[関連テーブル] と関連付けるフィールドを選択し、[関連テーブル] で「主テーブル」(社員や得意先などのリスト) を選択し、[関連列 (プライマリ)] で [テーブル] と関連付けるフィールドを選択して [OK] をクリックします。
リレーションシップが作成され、[リレーションシップの管理] ダイアログボックスに表示されます。
[閉じる] をクリックします。
リレーションシップが設定されます。
なお、ワークシート上でこのリレーションシップの状況を確認することはできません。
バージョンやアドインをインストールしているかどうかで確認方法は異なりますが、少なくとも、リボンの [データ] タブの [接続] グループの [接続] をクリックして、[ブックの接続] ダイアログボックスを表示すると、リレーションシップを設定したテーブルがデータモデルとして定義されていることを確認できます。

データモデルって？というところがむずかしく感じますが、ここでは、ワークシート以外の別の場所にあるデータ構造だと考えてください。リレーションシップを設定したことで、ピボットテーブルで集計するための 2 つのテーブルをもとにしたデータ構造ができた、とイメージしてもらえればよいと思います。

Step3　リレーションシップを利用した
　　　ピボットテーブルの作成

リレーションシップを設定すると、2 つのテーブルを結合された 1 つのまとまり (データモデル) としてピボットテーブルを作成できます。

前の手順で設定したリレーションシップを利用して、ピボットテーブルで集計するには、いろんな手順があるのですが、その一例を下記に記載します。

ピボットテーブルを作成するセルをアクティブにし、リボンの [挿入] タブの [テーブル] グループの [ピボットテーブル] をクリックします。
[ピボットテーブルの作成] ダイアログボックスの [外部データソースを使用] をオンにし、[接続の選択] をクリックします。
(ワークシート上ではない、別の場所にあるデータ構造をもとに作る、というイメージをしてください)
[既存の接続] ダイアログボックスの [テーブル] タブを選択すると、リレーションシップの設定によって作成された、データモデル (結合された 2 つのテーブル) を、集計元データとして選択できることがわかります。

このデータモデルを選択して、[開く] をクリックします。
集計元データが選択されます。
[ピボットテーブルの作成] ダイアログボックスの [OK] をクリックします。
ピボットテーブルが作成され、フィールドリストにリレーションシップを設定した 2 つのテーブルが表示されます。

テーブルを展開し、集計に必要なフィールドを配置します。
VLOOKUP 関数などを使用して、集計元データを加工しなくても、2 つのテーブルを関連付けて集計が行えます。

Access でのデータベース作成経験がある方は、リレーションシップについてもすんなりイメージできると思うのですが、そうではない方はなかなかこの記事だけではイメージできないかもしれません。

ただ、知っていただきたいのは、今まで工夫をすることで対応していた集計が、機能を使って対応できるようになっている、ということです。そしてそれらをきちんと使っていくためには、「テーブル」や「リレーションシップ」、「データべース」なんていう言葉についても学習する必要がある、そういった時代になってきていますよ、ということでしょうか。
そのためには、その作業や目的にあった製品の使い分けも大事だし、製品の機能をきちんと使うことも大事ですね。

ちなみに、このようなリレーションシップや Access の基本的な使い方などは、こちらのセミナーでご案内しています。
お試しになりたい方はぜひ。たまには宣伝です♪

■ ExcelとOffice連携セミナー
http://officetanaka.net/seminar/seminar7.htm