[Excel] テーブルのすすめ ピボットテーブルとリレーションシップ

今回も Excel 2007 以降の重要な機能追加である「テーブル機能」を使った話です。

ピボットテーブルの参照先としてテーブルを使う

すでにテーブルを参照先範囲に指定することでデータの増減に自動的に対応する恩恵を受けることは入力規則や関数で紹介してきました。
同様にピボットテーブルの参照先範囲としてテーブルを指定すると、動的にデータの増減（参照先範囲の変化）に対応できます。ただしピボットテーブルの「更新」はこれまで同様押す必要はあります。

元データの行の追加だけでなく、列の追加にもピボットテーブルは対応します。

ピボットテーブルを使うユーザーはすでに参照先データがきれいな「表」になっているはずです。その表をテーブルに変換して、ピボットテーブルの参照先としてテーブルを指定するだけでこの恩恵を受けられるのです。これを使わない手はありません。

ただ、残念なことに、この「ピボットテーブル」の結果の表は「テーブル」機能で実装されていません。テーブル機能が出る前からピボットテーブルは存在し、その実装は同じ「テーブル」という用語を使っていても違うのです。ピボットテーブルの参照が構造化参照のようになれば、、、と思いがちですが注意してください。

そのかわりと言ってはなんですが、Excel 2013 以降でこのピボットテーブルとテーブルの組み合わせでさらに強力に業務を支援する機能が追加されています。それが「リレーションシップ」です。

VLOOKUPはもう使わない？Excel 2013 から実装されたリレーションシップ

上記で使ったテーブルには商品コードが記入されていて、商品名はありません。このようなデータの持ち方の場合は、商品テーブルが別に存在して、そこから商品名を VLOOKUP でこれまでは検索してきました。
（注釈ですが、この図のようなデータの持ち方、ピボットテーブルの配置はお勧めではないどころかやってはいけません。テーブルを使う場合は混乱を避けるために1ワークシート1テーブルとすべきだと個人的に考えます。すべてのデータを見せるためにこのような配置をしています。）

2つ程度であれば、列を追加して VLOOKUP を使ってデータを参照してもいいですが、基幹業務システムからデータをインポートしたり、何等かの形でそのデータを利用しようとしたりすると複数のテーブルに分けられた「正規化」されたテーブルであることが多いです。

これに対応するために Excel 2013 では「リレーションシップ」という機能が追加されました。

それぞれの参照データ範囲は「テーブル」に変換されている必要があります。
テーブルに変換されていると [データ] タブの [データ ツール] セクションにある [リレーションシップ] の [リレーションシップの管理] ダイアログで参照・テーブル間の関連付けが可能になります。

以下の3つのテーブルの関連付けを行ってみます。

個人売上テーブル

商品テーブル

地域テーブル

欲しいデータは個人売上テーブルの商品コードである A や B が商品名になったものや、その商品の地域名です。そのため、まず、個人売上テーブルと商品コードを関連づけます。ブック内のテーブルは「リレーションシップの管理」ダイアログのプルダウン リストに表示されるのでシートを移動して範囲指定するようなことはありません。個人売上テーブルで A, B, C... がある [商品] 列と、商品テーブルで A, B, C... がある [商品コード] を関連付けます。
続けて、商品テーブルと地域テーブルを関連付けます。今度は 001, 002, 003... がある列をそれぞれ関連付けます。

今は以下のような関連付けになっています。

ここまで設定したらピボットテーブルでこの関連付けが利用可能になります。

個人売上テーブルを参照先として指定したピボットテーブルを作ってみます。この時重要なのは、このリレーションシップを利用したい場合は必ず [複数のテーブルを分析するかどうかを選択] の [データモデルに追加する] をチェックすることです。

これで作成したピボットテーブルのフィールド リストにはこれまでなかったタブが表示されます。その中の [すべてのフィールド] をクリックすると、個人売上テーブルに関連付けられたテーブルが表示され、それを展開すると列名が表示されます。

この各テーブルの列名を使ってピボットテーブルを作成することが可能です。

このフィールド リストを使って、商品名別と地域名別の売上のピボットテーブルを作成してみます。

複数テーブルを使ったシンプルなピボットテーブルであれば、このリレーションの機能をどんどん活用すべきですが、その反面、実はこれまで Excel のピボットテーブルでできていたこと（それも重要なこと）がいくつかできなくなっていることに注意しなければなりません。（もちろん、その対応方法は存在します）それを以下に紹介します。

データモデルとは

ところで、ピボットデーブルを作成するときに [複数のテーブルを分析するかどうかを選択] で [このデータをデータモデルに追加する] にチェックを入れました。一体データモデルとは何なんでしょうか。

このブログで Excel 2007 以降のテーブル機能を紹介する発端となっているのは Office 365 との親和性でした。　http://road2cloudoffice.blogspot.jp/2014/10/excel-office-365.html

Office 365 の SharePoint Online や Access アプリ、クラウドや社内にあるサーバーのデータと、Excel との橋渡しをするのがテーブルです、と紹介しましたが、さらに正確にいうとテーブルとデータモデルによって連携できる、と言えます。

そもそも、このデータモデルという実装方法は Excel から発生したものではなく、マイクロソフト社のデータベースサーバーである SQL Server で Excel との連携のために開発された Excel アドインから来ています。

2007 Office リリース用 SQL Server 2005 データ マイニング アドイン
http://office.microsoft.com/ja-jp/excel-help/HA010225754.aspx

そのため、データモデルとなった「データの集まり」は Excel の機能を使うことができない場合もあります。

このリレーションシップ機能を使ったピボットテーブルとして実際に見ているデータはデータモデルから取り出しているものです。それは [データ] タブの [接続] の [ブックの接続] ダイアログから確認できます。ThisWorkbookDataModel はピボットテーブルの範囲を示しています。

そして、このデータモデルのリレーションシップを使ったピボットテーブルでの代表的な制限は以下です。
[追記] Excel 2016 からはこの制限はなくなっている。詳しくはこちらを参照のこと。

・ グループ化ができない
・ 集計フィールド、集計アイテムを追加できない

グループ化はシリアル値である日付を月や年にまとめるためにピボットテーブルではよく使うでしょう。集計フィールドもデータに単価と数量があり、そこから金額を計算するときに使うことが多いです。

これらが使えないとピボットテーブルによる分析はできないも同然でしょう。しかし、そもそも「SQL Serer 2005 データ マイニング アドイン」から発展してきた実装方法です。このアドインはデータの分析のための機能です。

Excel が本来持っていた機能はデータモデルによりデータの持ち方が変わってしまったため利用できませんが、その代替は当然用意されています。

それがデータモデルに追加する「セット」であり、そこで利用する MDX 言語です。
(MDX : the MultiDimensional eXpression)

上図「ブックの接続ダイアログ」で ThisWorkbookDataModel を選択した状態で [セットの管理] ボタンを押せば、新しいセットの追加や MDX 編集画面がでます。

ただ、公開されている日本語の情報が非常に少ないのと、Excel 側からの観点よりも、SQL Server のアドイン（Analysis Services）側からの情報に偏ることが多い、さらに SQL 構文などのデータベースの知識を必要とするため、Excel ユーザーにとっては習得は厳しいかもしれない、というのが現状でしょう。

このような状況を打破し、Excel ユーザーにも直感的にわかりやすく、Excel の操作性に近い設定を提供するのが PowerPivot です。 PowerPivot の対象はクラウドやサーバーのデータだけでなく、Excel ブック内のデータモデル化したテーブルも対象にできます。つまり「テーブル」であれば、PowerPivot からの利用が可能なのです。

PowerPivot で日付グループ化、集計列追加でピボットテーブルを作った例

ただし、PowerPivot を使うには「エディション」に注意しなければなりません。
Personal エディションや Home & Business エディションでは PowerPivot を利用することができません。利用するには Office 2010 以上の Professional や Solo 、Office 365 Pro Plus が必要となります。

[追記] Power Pivot で日付のグループ化
http://road2cloudoffice.blogspot.jp/2014/11/powerpivot.html

テーブル関連の投稿
[テーブルとVLOOKUP] https://road2cloudoffice.blogspot.jp/2014/10/vlookup.html
[テーブルと入力規則] https://road2cloudoffice.blogspot.jp/2014/10/blog-post.html
[テーブルと集計行] https://road2cloudoffice.blogspot.jp/2014/10/blog-post_31.html
[テーブルと構造化参照] https://road2cloudoffice.blogspot.jp/2014/11/blog-post.html
[テーブルとピボット] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
[テーブルとVBA] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html