以前からMOS試験（ExcelのExpertレベル）ではキューブ関数が出題されていますが、全国にある試験会場からキューブのサーバに接続することはできないので、デモ画面（仮想の画面）で出題されていました。
しかし、Excel2016の試験では出題傾向が変わり「Excelデータモデル」からデータを取得する問題しか出題されなくなりました。そのため、キューブ関数が何の役に立つのか、何が便利なのかが全く分からない試験問題になってしまいました。しかも、問題に使われるExcelのファイルにはすでにデータモデルが設定されていて、そこからキューブ関数でデータを取り出すことだけが出題されるため、対策テキストの問題を解いても理解できない受験者が多いようです。
そこで、テーブルをデータモデルに追加して、キューブ関数を使うまでの一連の流れを解説します。この作業はMOSには出題されませんが、キューブ関数を理解する上で必須の知識ですから、ぜひ練習しておきましょう。
※なお、この記事はExcel2016で作成しており、Excel2010以前には対応していません。
目次
- ０．MOS Excel Expertについて
- １．テーブルを作りテーブル名をつける
- ２．リレーションシップ
- ３．Excelデータモデル
- ４．データモデルでピボットテーブルを作る
- ５．キューブ関数で取り出すことの意味
０．MOS Excel Expertについて
MOSエキスパート試験の概要と、MOSの練習問題についてはFOM出版の対策テキストをご覧ください。
また、「キューブ」とは何か、ということについてはこちらの記事をご覧ください。
１．テーブルを作りテーブル名をつける
問題
2つのシートにそれぞれ表を入力した。これらをテーブルにして「選手」「チーム」という名前をつけなさい。
解説
原則として1つのシートに1つの表を作ります。このとき、リスト形式の表を作らなければなりません（参考：【神Excel】８個の基本パターンで完全習得「リスト形式」の教科書）。1つ目の表を全部選択します。
挿入タブ、テーブルをクリックします。
「先頭行をテーブルの見出しとして使用する」のチェックを入れます。
テーブルをクリックすると、テーブルツール・デザインタブが表示されます。
その左端にテーブル名を入力する欄があります。ここに「選手」と入力します。これでテーブル名をつけることができました。
同様に、2つ目の表を全部選択して、挿入タブのテーブルをクリックします。
「先頭行をテーブルの見出しとして使用する」のチェックを入れます。
テーブル名を「チーム」にします。
名前ボックスにも表示されます。
２．リレーションシップ
問題
２つのテーブル間にリレーションシップを作成しなさい。なお、リレーションシップに使用する列は「チームID」とする。
解説
（１）リレーションシップの作成
２つのテーブルには「チームID」があります。
データタブ、リレーションシップをクリックします。
新規作成をします。
テーブルは「選手」、列（外部）は「チームID」です。関連テーブルは「チーム」、関連列（プライマリ）は「チームID」です。
これでリレーションシップを設定することができました。
（２）リレーショナルデータベース
これら2つの表はもともと1つの表として作成することも可能です。チーム名とリーグはVLOOKUP関数で検索すればよいです。
しかし、[チームID]－[チーム名]－[リーグ]のデータは重複しており、この関係が変わることはありません。関係が変わらないデータを重複して入力することは非効率であり、データの管理の仕方として間違っています。
そこで、この部分だけを分離して、別の表を作成します。
いっぱんにこの表のことをマスタといいますが、Excelでは関連テーブルと呼びます。関連テーブルの左端はコード番号（ID）になるようにします。この列のことを主キー（プライマリキー）といい、Excelでは関連列といいます。
分離すると、元の表はコードだけ入力すればよいことになります。この列のことを外部キーといいます。
作成した2つのテーブルはそのままでは1つのデータベースとはなりません。リレーションシップを作成することによって1つのデータベースとして扱われます。このようなデータベースのことをリレーショナルデータべース（RDB）といいます。
３．Excelデータモデル
問題
リレーションシップを設定した2つのテーブルが、Excelデータモデルに追加されたことを確認しなさい。
解説
（１）すでにデータモデルのテーブルになっている
もう一度、リレーションシップをクリックします。
作成したリレーションシップの編集をします。
「データモデルのテーブル」と表示されています。2つのテーブルがExcelデータモデルに追加されたことを表しています。
（２）データモデル
リレーションシップを設定するとExcelデータモデルに追加されます。
Excelデータモデル（PowerPivotデータモデルともいう）は、Excelで作成した複数のテーブルをまとめて1つのデータベースにしたものです。テーブルのほかに、Accessのデータや外部ファイル、外部サーバのデータを取り込むこともできます。
作業しているExcelブック（ファイル）に保存されるものなので、外部のサーバにアクセスすることはありません。また、他のExcelファイルに影響を与えるものでもありません。
ちなみに、外部のデータベースにアクセスするときには識別するために「接続名」をつけます。
この接続名は、接続の際に自由に名前を付けることができますが、Excelデータモデルには自動的に「ThisWorkbookDataModel」という接続名が付きます。したがって、キューブ関数でこのデータモデルにアクセスするときには必ず、接続名は「ThisWorkbookDataModel」としなければなりません。
４．データモデルでピボットテーブルを作る
問題
次のピボットテーブルを作成しなさい。
解説
挿入タブ、ピボットテーブルをクリックします。
「このブックのデータモデルを使用する」を選びます。また、新規ワークシートを選びます。
ピボットテーブルのフィールドにはチームと選手の2つのテーブルがあります。
テーブル「チーム」からリーグとチーム名、テーブル「選手」から打席、HRを取得します。
2つのテーブルからデータを取得して、1つのピボットテーブルを作ることができました（参考：【Excel】ピボットテーブルは集計の前の基礎トレーニングが大事！ピボットテーブル総まとめ）。
別解
ピボットテーブルを作成するときに「外部データソースを使用」を選び、接続の選択をクリックします。
テーブルの中から、ブックのデータモデルのテーブル（２テーブル）を選びます。
このとき、接続名には「ThisWorkbookDataModel」と表示されます。
５．キューブ関数で取り出すことの意味
問題
セルB1に「右」の打席を表すメンバーを取得して項目名を表示しなさい。また、A列のチーム名がキューブ関数で取得されたものであることを確認して、セルB2以降に各チームの右打者のHRの合計本数を表示しなさい。
解説
キューブ関数の使い方についての詳しい解説はこちらの記事をご覧ください。
セルに次のような数式を入力します。
- =CUBEMEMBER("ThisWorkbookDataModel","[選手].[打席].[右]")
「右」と表示されます。「右」と直接入力するのと何が違うのかと思うかもしれませんが、それはデータモデルから取り出しているからそのように思うだけであって、外部のサーバにアクセスしたときのことを考えましょう。外部のサーバーに「右」という項目があるのかどうかが分からないのでそれを探して表示しているのです。なければエラーが返ってくるのでIFERROR関数を使って別の処理をすることができます。
A列も同じようにCUBEMEMBER関数でチーム名を取得しています。
次の数式を入力します（参考：キューブ関数とは何か、CUBE関数の使い方をわかりやすく解説します【MOS Excel2016合格対策】）。
- =CUBEVALUE("ThisWorkbookDataModel",A2,$B$1,"[Measures].[合計 / HR]")
これでホームランの合計本数を求めることができます。
これはピボットテーブルで求めた値と同じです。ピボットテーブルで集計したものをわざわざキューブ関数で取り出して何が便利なのかと思うかもしれませんが、これも、外部のサーバにアクセスしたときのことを考えましょう。外部のサーバーにある集計値（メジャー、Measures）を直接Excelに表示できるというのが本来の使い方なのです。
解説は以上です。
