以前の投稿でも Excel 2013 から新機能として追加されて「リレーションシップ」を紹介した。
テーブルのすすめ ピボットテーブルとリレーションシップ
このリレーションシップはテーブル間のリレーションを設定した上で、ピボットテーブル作成時に「複数のテーブルを分析するかどうかを選択」の「このデータをデータ モデルに追加する」のチェックを入れることで、ピボットテーブルのフィールド リストで利用可能になる。
またリレーションシップと同様の機能として Power Pivot の「計算列」の紹介で RELATED 関数を使った列の追加と複数テーブルの分析を可能にするピボットテーブルの作成も紹介した。
Power Pivot で計算列を作る
さらに、Power Query でも「マージ」という機能でリレーションシップ同様の結果（マージされたテーブル）を得る方法も紹介している。
Excel ユーザーのための Power Query
Excel 単体のみで利用する環境であれば、実際のところリレーショナル データベースのようなテーブルの「正規化」をすることはあまりないが、マスターデータがサーバーやクラウドといった他のシステムにある、もしくは日々のトランザクション データを他のシステムからインポートする、となると、VLOOKUP 関数や MATCH/INDEX 関数を使って複数のテーブルや表を参照しなければならないケースが出てくる。このようなケースが多くなると「リレーションシップ」の機能の利用を検討したほうが良い場合がある。
Power Pivot や Power Query という Power BI のアドインによってさまざまな可能性が提示されているが、上記のように「似たような機能」が複数あり、その選択に悩んだり、特徴を理解するのに時間がかかるのも事実である。
さらにデータ モデルを使ったピボットテーブルでは「集計フィールド」、「集計アイテム」、「日付のグループ化」ができなくなる（もちろん、それを実現するための代案もある）ということも無視できない。
[追記] 日付のグループ化はデータ モデルに追加しても可能になりました。
そこでその特徴をまとめてみた。上述のような「リレーションシップ」や「マージ」を行うと、その後工程でピボットテーブルを利用することが多いため、ピボットテーブルの利用という観点からまとめてみる。ただし、あくまで実践ワークシート協会の業務の中で実際に利用した経験上からの示唆である。
データ モデル利用の有無がポイント
上述のように似たような結果が得られる様々な機能が存在している。リレーションシップという複数テーブル間の関連を設定する方法では Excel 2013 リレーションシップとデータ モデルを使ったピボットテーブル、Power Pivot、そして Power Query のマージ機能がある。
最終的に Excel のピボットテーブルを使いたい場合、この3つの機能の関係は以下のような図で表すことができる。
Power Pivot はデータ モデルを前提とする。扱うデータは必ずデータ モデルでなければいけない。
Excel 2013 リレーションシップを設定した複数テーブル分析可能なピボットテーブルもデータ モデルを作成しなければならない。
データ モデルを使って、複数テーブルを関連づけたり、列の加工を行ったりして、そのデータを Excel のピボットテーブルに渡している、と考えられる。
データ モデルはさまざまなサーバーやクラウドを前提としているため、Excel のためだけのデータではない。そのため、本来 Excel のデータ（テーブルまたは表）を前提として用意された機能・オプションが使えなくなっていると考えると妥当だろう。
データ モデルを使ったピボットテーブルでは制限がでる、ということだ。（何度も書くが、その代替策はきちんと用意されている）
日付データのグループ化も可能な「リレーションシップ」環境
さまざまなデータ ソースを扱うことを目的としたデータ モデルは今後も拡張、発展すると考えられる。しかし、製品・サービスとしては必要だが、実務ではそれほど多様なデータ ソースを扱ってはいないのも事実だ。（今後はわからないが、少なくとも現状は多くても２～３種類だろう）
これまで同様のピボットテーブルの操作感で、リレーションシップを使って複数テーブル、複数のデータ ソースの分析をしたい、となれば、上図の構成から Excel テーブルからピボットテーブルを作るしかない。そこで出てくるのは Power Query のマージ機能だ。
Power Query のマージ機能により、複数のテーブルやデータ ソースを1つの Excel のテーブルにし、そこからピボットテーブルを作れば、これまで同様の操作性を維持することができる。
以下のような Excel のテーブルを例にとって検証してみよう。
Excel 2013 のリレーションシップを使ったピボットテーブルや、Power Pivot から作成したピボットテーブルでは、日付のグループ化、集計フィールド、集計アイテムの利用はできない。
ここでサンプルの3つのテーブルを「マージ」したテーブルを Power Query でまず作成する。
Excel のテーブルを参照するクエリを作成するとき、同じテーブルを作成する必要はないので、「接続の作成のみ」を選ぶ。もちろん、ここではデータ モデルは作成しない。
それを3つにテーブルで繰り返し、３回のマージにより1つのテーブルにする。
この最後のマージ(Merge3)によって出来上がったテーブルからピボットテーブルを作る。このピボットテーブルは Excel のテーブルを参照している通常のピボットテーブルなので、日付によるグループ化などが可能だ。
Power Pivot の意義
それでは Power Pivot の意義はないのかというと、Power Query のブック クエリのウィンドゥを見てわかるように、複雑なことをやろうとすると、クエリ、マージ、追加といった操作の繰り返しになる。Power Pivot であれば Power Pivot ウィンドゥのダイアグラム ビューを使ってビジュアルに操作が可能だ。
テーブルの数が多くなり、リレーションシップの項目も多くなれば、Power Pivot ウィンドウによる設定の容易さは計り知れない。基幹系アプリケーションが利用しているデータベースなどは多くのテーブルが存在するため、このようなビジュアルツールでなければ設定や管理が煩雑になるだろう。
データ モデルはいつ必要になるのか
よって、Excel を中心としているユーザーにとってはデータ モデルが必須となるシーンはあまりないのが現実だ。Excel のテーブルであったり、SharePoint Online の SharePoint リストからそれほど多くないリストのインポートを Excel にして利用するのであれば、データ モデルを前提とする Power Pivot や、データ モデルを作成する Power Query のモデル OLE DB 接続にする意味はあまりない。SharePoint リスト接続や Power Query の OLE DB 接続を使い、Excel 上に展開されたテーブルを扱うことでおおよそのことができるだろう。
実践ワークシート協会の業務で唯一データ モデルが必要になるのが、Excel Online / Excel Web Access といった、Excel ブックを SharePoint Online 上で活用するときである。まだ、活用しきれていないが、Power BI もデータ モデルの Excel ブックを前提としている。
いますぐは必要ないかもしれないが、データ モデルについては今後のキーとなるので引き続きウォッチが必要だろう。
[2016/5 追記]
今後、Excelブック内でのデータ モデルは必要になります。それは Power BI service でレポートやダッシュボードを他のメンバーと共有する際に、データ モデルをベースとした処理が前提となることからです。Excelの中だけで閉じるのであればデータ モデルを作成する必要はありませんが、Power BI service などのExcel以外のサービスを利用することを考えると、データ モデルが前提となるからです。これが1年以上前と 2016年の上旬との大きな違いです。
テーブルのすすめ ピボットテーブルとリレーションシップ
このリレーションシップはテーブル間のリレーションを設定した上で、ピボットテーブル作成時に「複数のテーブルを分析するかどうかを選択」の「このデータをデータ モデルに追加する」のチェックを入れることで、ピボットテーブルのフィールド リストで利用可能になる。
またリレーションシップと同様の機能として Power Pivot の「計算列」の紹介で RELATED 関数を使った列の追加と複数テーブルの分析を可能にするピボットテーブルの作成も紹介した。
Power Pivot で計算列を作る
さらに、Power Query でも「マージ」という機能でリレーションシップ同様の結果（マージされたテーブル）を得る方法も紹介している。
Excel ユーザーのための Power Query
Excel 単体のみで利用する環境であれば、実際のところリレーショナル データベースのようなテーブルの「正規化」をすることはあまりないが、マスターデータがサーバーやクラウドといった他のシステムにある、もしくは日々のトランザクション データを他のシステムからインポートする、となると、VLOOKUP 関数や MATCH/INDEX 関数を使って複数のテーブルや表を参照しなければならないケースが出てくる。このようなケースが多くなると「リレーションシップ」の機能の利用を検討したほうが良い場合がある。
Power Pivot や Power Query という Power BI のアドインによってさまざまな可能性が提示されているが、上記のように「似たような機能」が複数あり、その選択に悩んだり、特徴を理解するのに時間がかかるのも事実である。
さらにデータ モデルを使ったピボットテーブルでは「集計フィールド」、「集計アイテム」、「日付のグループ化」ができなくなる（もちろん、それを実現するための代案もある）ということも無視できない。
[追記] 日付のグループ化はデータ モデルに追加しても可能になりました。
そこでその特徴をまとめてみた。上述のような「リレーションシップ」や「マージ」を行うと、その後工程でピボットテーブルを利用することが多いため、ピボットテーブルの利用という観点からまとめてみる。ただし、あくまで実践ワークシート協会の業務の中で実際に利用した経験上からの示唆である。
データ モデル利用の有無がポイント
上述のように似たような結果が得られる様々な機能が存在している。リレーションシップという複数テーブル間の関連を設定する方法では Excel 2013 リレーションシップとデータ モデルを使ったピボットテーブル、Power Pivot、そして Power Query のマージ機能がある。
最終的に Excel のピボットテーブルを使いたい場合、この3つの機能の関係は以下のような図で表すことができる。
Power Pivot はデータ モデルを前提とする。扱うデータは必ずデータ モデルでなければいけない。
Excel 2013 リレーションシップを設定した複数テーブル分析可能なピボットテーブルもデータ モデルを作成しなければならない。
データ モデルを使って、複数テーブルを関連づけたり、列の加工を行ったりして、そのデータを Excel のピボットテーブルに渡している、と考えられる。
データ モデルはさまざまなサーバーやクラウドを前提としているため、Excel のためだけのデータではない。そのため、本来 Excel のデータ（テーブルまたは表）を前提として用意された機能・オプションが使えなくなっていると考えると妥当だろう。
データ モデルを使ったピボットテーブルでは制限がでる、ということだ。（何度も書くが、その代替策はきちんと用意されている）
日付データのグループ化も可能な「リレーションシップ」環境
さまざまなデータ ソースを扱うことを目的としたデータ モデルは今後も拡張、発展すると考えられる。しかし、製品・サービスとしては必要だが、実務ではそれほど多様なデータ ソースを扱ってはいないのも事実だ。（今後はわからないが、少なくとも現状は多くても２～３種類だろう）
これまで同様のピボットテーブルの操作感で、リレーションシップを使って複数テーブル、複数のデータ ソースの分析をしたい、となれば、上図の構成から Excel テーブルからピボットテーブルを作るしかない。そこで出てくるのは Power Query のマージ機能だ。
Power Query のマージ機能により、複数のテーブルやデータ ソースを1つの Excel のテーブルにし、そこからピボットテーブルを作れば、これまで同様の操作性を維持することができる。
以下のような Excel のテーブルを例にとって検証してみよう。
Excel 2013 のリレーションシップを使ったピボットテーブルや、Power Pivot から作成したピボットテーブルでは、日付のグループ化、集計フィールド、集計アイテムの利用はできない。
ここでサンプルの3つのテーブルを「マージ」したテーブルを Power Query でまず作成する。
Excel のテーブルを参照するクエリを作成するとき、同じテーブルを作成する必要はないので、「接続の作成のみ」を選ぶ。もちろん、ここではデータ モデルは作成しない。
それを3つにテーブルで繰り返し、３回のマージにより1つのテーブルにする。
この最後のマージ(Merge3)によって出来上がったテーブルからピボットテーブルを作る。このピボットテーブルは Excel のテーブルを参照している通常のピボットテーブルなので、日付によるグループ化などが可能だ。
Power Pivot の意義
それでは Power Pivot の意義はないのかというと、Power Query のブック クエリのウィンドゥを見てわかるように、複雑なことをやろうとすると、クエリ、マージ、追加といった操作の繰り返しになる。Power Pivot であれば Power Pivot ウィンドゥのダイアグラム ビューを使ってビジュアルに操作が可能だ。
テーブルの数が多くなり、リレーションシップの項目も多くなれば、Power Pivot ウィンドウによる設定の容易さは計り知れない。基幹系アプリケーションが利用しているデータベースなどは多くのテーブルが存在するため、このようなビジュアルツールでなければ設定や管理が煩雑になるだろう。
データ モデルはいつ必要になるのか
よって、Excel を中心としているユーザーにとってはデータ モデルが必須となるシーンはあまりないのが現実だ。Excel のテーブルであったり、SharePoint Online の SharePoint リストからそれほど多くないリストのインポートを Excel にして利用するのであれば、データ モデルを前提とする Power Pivot や、データ モデルを作成する Power Query のモデル OLE DB 接続にする意味はあまりない。SharePoint リスト接続や Power Query の OLE DB 接続を使い、Excel 上に展開されたテーブルを扱うことでおおよそのことができるだろう。
実践ワークシート協会の業務で唯一データ モデルが必要になるのが、Excel Online / Excel Web Access といった、Excel ブックを SharePoint Online 上で活用するときである。まだ、活用しきれていないが、Power BI もデータ モデルの Excel ブックを前提としている。
いますぐは必要ないかもしれないが、データ モデルについては今後のキーとなるので引き続きウォッチが必要だろう。
[2016/5 追記]
今後、Excelブック内でのデータ モデルは必要になります。それは Power BI service でレポートやダッシュボードを他のメンバーと共有する際に、データ モデルをベースとした処理が前提となることからです。Excelの中だけで閉じるのであればデータ モデルを作成する必要はありませんが、Power BI service などのExcel以外のサービスを利用することを考えると、データ モデルが前提となるからです。これが1年以上前と 2016年の上旬との大きな違いです。
