MOS Excel Expertの出題範囲のなかで受験生を悩ませる、難しい関数の一つにキューブ関数（CUBESET関数、CUBEMEMBER関数、CUBEVALUE関数、CUBERANKEDMEMBER関数など）があります。
キューブは、あらかじめ集計されたものを効率よく検索するための特殊なデータベースで、通常は外部のデータベースサーバーに作ります。しかし、MOSではExcelファイルの内部にある「Excelデータモデル」を使う問題が出題されます。キューブとは何か？データモデルとは何か？ということを理解しないと解くことができません。
そこで、今回は、キューブ関数がさっぱりわからないというキューブ・アレルギーの皆さんのために、MOSで出題されるキューブ関数について解説します。
※CUBE関数は2010以降で使用可能ですが、2016で解説しています。また、MOS対策なのでMDX構文の説明はかなり大雑把です。
目次
- ０．MOS Excel Expertについて
- １．キューブとは何か
- ２．キューブ関数を知るための言葉を覚えよう
- ３．Excelデータモデルがあることを確認してみよう
- ４．CUBEMEMBER関数（項目名を表示する）
- ５．CUBEVALUE関数（合計などを表示する）
- ６．CUBESET関数（セットの取得）
- ７．CUBERANKEDMEMBER関数（最大のもの、最小のもの）
- ８．数式に変換
- ９．CUBERANKEDMEMBER関数とCUBESET関数の入れ子
- 10．試験対策として
０．MOS Excel Expertについて
MOSエキスパート試験の概要と、MOSの練習問題についてはFOM出版の対策テキストをご覧ください。
１．キューブとは何か
（１）キューブは集計結果を格納する
通常、集計表を作る時は、１つまたは複数の表（リスト形式）にデータを入力して、その中からデータを取り出したり、集計したりします。
ピボットテーブルを使うのが一般的です。Excelのように何千行、何万行の程度であればこのような分析方法で問題ありません。
しかし、企業が扱う何百万件、何億件といったデータを持つ巨大なデータベースに、多くの社員が直接アクセスして、直接集計するとサーバーがものすごく遅くなります。しかも、ピボットテーブルのようにいろいろと集計項目を変えるとさらに時間がかかります。
そこで、あらかじめ集計する方法を決めておいて、元のデータの一部と集計結果をあわせ持つデータベースを作っておけば、瞬時に集計結果を検索することができます。このような集計用のデータベースのことを、OLAP CUBE（オラップ・キューブ）略して「キューブ」といいます。
なお、キューブは閲覧するだけでなく、直接、データを入力したり、データを修正したりすることも可能です。
（２）キューブのメリットとデメリット
キューブは特殊な構造を持ち、データ入力（取得）や事前の集計処理に時間がかかるため、通常は夜間に自動的に集計を行うようにタイマーをセットしておく、いわゆる夜間バッチで集計することが多いです。キューブを作っておくと高速に集計結果が得られます。なぜなら事前に集計をしているからです。計算方法も事前に指定しているのでSUMなどの関数を使うこともありません。「合計された値」を呼び出すだけです。
しかし、キューブは集計結果を格納するものなので、基本的に保存できるのは数値データだけです。数値でないデータは分析ができません。
また、リアルタイムで入ってくるデータは反映されないので、数時間のタイムラグが発生します。
２．キューブ関数を知るための言葉を覚えよう
（１）接続名
Excelのキューブ関数を使うときには、あらかじめネットワークを通じてサーバーに接続しておかなければなりません。
データタブ、外部データ取り込みのなかのその他のデータソースで、データベースに接続します。
しかし、サーバーが無かったらこの画面で終了です（MOSの場合は接続できなくても問題ない）。
接続をするときには名前を付けます（フレンドリ名）。フレンドリ名の欄に入力したものが「接続名」です。
なお、接続の詳しい方法については下記のマイクロソフト公式サイトをご覧ください。
OLAP データベースに接続する (インポートする)
（２）ディメンションとメンバー
次の表は、縦を店舗とし、横を時系列にして売上金額を入力した二次元の表です。売上金額を店舗と月に分けて入力したものです。店舗と月のことをディメンション（Dimention, 次元軸）といいます。
それぞれのディメンションは階層構造になっていて、基本項目とそのグループである集計項目があります。例えば、4月～9月が基本項目で、第1四半期・第2四半期・半期は集計項目です。また、各支店名が基本項目で、東日本・西日本・全国は集計項目です。
それぞれの階層や項目のことを「メンバー」（Member）といいます。また、時系列や店舗とは異なる分類、例えば、商品ごとや担当者ごとに金額を入力すれば、3次元、4次元とディメンション（次元）が増えていきます。
（３）メジャー
キューブは、ルービックキューブのような立体で表すことがありますが、データを入力するかまたはほかのデータベースからデータが入ってくると、通常は、キューブ内の該当する「基本項目」のところにデータが格納されます。これを「セル」ということがあります。
キューブ内で集計を実行すると、集計項目のセルに集計結果が格納されます。これを事前集計処理ということがあります。基本項目のデータが更新されたら、その上の階層（集計項目）も変わります。前述のとおり、事前集計処理は夜間に自動的にあらかじめやっておく処理です。
このように定期的にキューブ内で集計をしておけば、ユーザーは集計結果にアクセスするだけで、集計値を見ることができます。このように個々のセルに格納された集計結果（集計値）のことをメジャー（Measure）といいます。
３．Excelデータモデルがあることを確認してみよう
問題
2つのテーブル「選手」「チーム」とピボットテーブルがある。
2つのテーブルがExcelデータモデルに登録され、それをもとにピボットテーブルが作られていることを確認しなさい。
解説
MOS Excel 2016エキスパートレベル（上級レベル）の出題範囲には「キューブ関数を使ってExcelデータモデルからデータを取り出す」と記載されています。Excelデータモデルとは、ExcelやAccessなどの複数の表をExcelに取り込み、統合して1つのデータベースにしたものです。これはExcelファイルに取り込んで使うものであり、外部のサーバに接続する必要はありません。
MOS試験ではすでにデータモデルが作られている状態で出題されます。MOSで出題されるデータモデルの作り方については別の記事で解説しています。
とりあえずExcelデータモデルがあることだけを確認してみましょう（※試験本番ではこの確認作業は不要です）。まず、2つのテーブルがあることを確認します。そして、テーブル名を確認します。テーブル名はテーブルツール、デザインタブにあります。
そして、これらのテーブルには同じ項目名があります。「チームID」は同じものです。データタブのリレーションシップを確認します。
2つのテーブルの「チームID」が接続されています。
2つの表に分けて入力してありますが、チームIDで接続することによって1つのデータベースとして扱うという意味です。この1つのデータベースのことを「Excelデータモデル」といいます。
次に、ピボットテーブルを確認します。ピボットテーブルのフィールドには2つのテーブルが表示されています。
2つのテーブルからチーム名、リーグ名、打席が集約されて、HR本数の合計が求められています。
４．CUBEMEMBER関数（項目名を表示する）
問題
Excelファイルに読み込まれているExcelデータモデルをもとに、チーム横浜を表すメンバーを取得し、項目名を表示しなさい。また、左打席を表すメンバーを取得し、項目名を表示しなさい。
解説
Excelデータモデルは厳密にいえばキューブではありませんが、キューブとして扱うことができ、キューブ関数を使うことができます。
CUBEMEMBER関数（キューブ・メンバー）は、キューブ内にメンバーが存在することを確認する関数です。メンバーが存在すればメンバー名を返し、存在しなければエラー値 #N/Aを返します。引数はセルの参照でない限り必ず文字列なので、ダブルクォーテーションで囲むことが必要です。
- =CUBEMEMBER("接続名", "メンバー式", "[キャプション]")
CUBEMEMBER関数の「MEMBER」とは項目名または階層のことで、特にピボットテーブルでは、縦横に並んでいる項目名を取得するときに使います。「項目名を表示しなさい」「メンバーを取得しなさい」といわれたらCUBEMEMBERです。
ここで、テーブルとそれに含まれる項目名を確認しておきます。
キューブ関数を入力するときはセルに直接入力して、数式入力補完機能（＝オートコンプリート）を最大限利用します。まず、「=cu」だけ入力します。これでキューブ関数が表示されます。
上下の矢印キーで「CUBEMEMBER」を選びます。
TABキーを押します（cubememberと直接入力しても構いません）。
第1引数は接続名です。ダブルクォーテーションを入力すると、「ThisWorkbookDataModel」と表示されます（※なお、「ThisWorkbookDataModel」と表示されるのに若干時間がかかることがあります）。Excelデータモデルの接続名は自動的に「ThisWorkbookDataModel」となっています。
TABキーを押します。
ダブルクォーテーションを入力します。ここで、ダブルクォーテーションで閉じるのを忘れてはいけません。
さらに、カンマを入力します。第2引数はメンバー式です。ダブルクォーテーションを入力します。テーブル名を選択します。いまはチーム名を取得しようとしているので「チーム」です。
ドットを入力します。項目名を選択します。セルに表示しようとしているのは「チーム名」です。チームIDではないことに注意します。
ドットを入力します。[All]と表示されることがあります。これを選びます。
ドットを入力します。[横浜]を選びます。
ちなみに、[All]というのは合計という意味ですが、[All]を省略することができます。したがって、[チーム].[チーム名].[横浜]となります。このように、メンバー式は"[テーブル名].[項目名].[項目]"の順番で、ドットでつないで表現します。
ダブルクォーテーションを入力します。ここで、ダブルクォーテーションで閉じるのを忘れてはいけません。
第3引数の入力は不要なので、このままかっこを閉じます。これで「横浜」と表示されます（※「横浜」と表示されるのに若干時間がかかることがあります）。
キューブ関数は次の3つを忘れなければどの関数も簡単に入力することができます。
- 最初に「=cu」と入力する
- 引数はダブルクォーテーションで囲む（特に最後を忘れないこと）
- メンバー式はドットで連結する
同じように、「左」打席も表示してみましょう。まず、「=cu」だけ入力します。上下の矢印キーで「CUBEMEMBER」を選びます。
TABキーを押します。第1引数は接続名です。ダブルクォーテーションを入力すると、「ThisWorkbookDataModel」と表示されます。MOSの試験では必ず「ThisWorkbookDataModel」です。
TABキーを押します。ダブルクォーテーションを入力します。
さらに、カンマを入力します。第2引数はメンバー式です。ダブルクォーテーションを入力します。テーブル名を選択します。いまは左右（打席）を取得しようとしているので「選手」です。
ドットを入力します。項目名を選択します。セルに表示しようとしているのは「打席」です。
ドットを入力します。[All]と表示される場合はこれを選びます（※ここで[左]と直接入力しても構いません）。
ドットを入力します。[左]を選びます。
[All]は消すことができます。したがって、[選手].[打席].[左]となります。ダブルクォーテーションを入力します。ここで、ダブルクォーテーションで閉じるのを忘れてはいけません。
第3引数の入力は不要なので、このままかっこを閉じます。これで「左」と表示されます。
５．CUBEVALUE関数（合計などを表示する）
問題
Excelファイルに読み込まれているExcelデータモデルをもとに、横浜チームの左打者のHR本数の合計を表示しなさい。
解説
CUBEVALUE関数（キューブ・バリュー）は、指定したメンバーの値を返します。
- CUBEVALUE(接続, [メンバー式 1], [メンバー式 2], …)
CUBEVALUE関数の「VALUE」とは、該当するセルの値のことで、特に、ピボットテーブルの場合、縦と横の項目名を指定することによって集計した値を求めるときに使います。「合計を表示しなさい」とあればCUBEVALUEです。
まず、「=cu」だけ入力します。上下の矢印キーで「CUBEVALUE」を選びます。
TABキーを押します。第1引数は接続名です。ダブルクォーテーションを入力すると、「ThisWorkbookDataModel」と表示されます。TABキーを押します。ダブルクォーテーションを入力するのを忘れてはいけません。
さらに、カンマを入力します。
第2引数はメンバー式です。本来なら、横浜チームの左打者を求めるにはさきほどと同じように「"[チーム].[チーム名].[横浜]","[選手].[打席].[左]"」と入力する必要がありますが、既にこのメンバー式を入力したセルがあればこれを参照することによって利用することができます。ダブルクォーテーションを入力せずに、セルB1をクリックします。
さらに、カンマを入力します。ダブルクォーテーションを入力せずに、セルB2をクリックします。
カンマを入力します。ダブルクォーテーションを入力して、[Measures]を選択します。これはメジャー＝集計値のことです。
ドットを入力します。ここで、「[合計 / HR]」と入力します。これはピボットテーブルの左上にある集計方法のことです。なお、スラッシュの前後には半角のスペースが必要です。
ダブルクォーテーションを入力するのを忘れてはいけません。かっこを閉じます。これで「26」と表示されます。
このようにピボットテーブル（Excelデータモデル）の場合、CUBEVALUE(ThisWorkbookDataModel, 項目, 項目, Measures)の形になります。
６．CUBESET関数（セットの取得）
問題
Excelファイルに読み込まれているExcelデータモデルをもとに、選手名をHR本数の多いほうから順に並べたデータのセットを取得し、「HR首位」と表示しなさい。
解説
CUBESET関数（キューブ・セット）は、1つ以上のメンバー、組み、セット、セル範囲を並べ替えたものを返す関数です。
- CUBESET(接続, セット式, [キャプション], [並べ替え順序], [並べ替えキー])
CUBESET関数のSETとは、メンバーを、指定した順序に並べ替えたものです。「並べ替えたもの」なので具体的な答えが返ってくるわけではありません。そこで、並べ替えがうまくいった場合は、第3引数でセルに表示する文字列を指定します。
まず、「=cu」だけ入力します。上下の矢印キーで「CUBESET」を選びます。TABキーを押します。
第1引数は接続名です。ダブルクォーテーションを入力すると、「ThisWorkbookDataModel」と表示されます。TABキーを押します。ダブルクォーテーションを入力するのを忘れてはいけません。
さらに、カンマを入力します。第2引数はセット式です。ダブルクォーテーションを入力します。テーブル名を選択します。いまは選手名を取得しようとしているので「選手」です。
ドットを入力します。項目名を選択します。「選手名」です。
ドットを入力します。[All]と表示される場合がありますが、これを無視して「Children」と入力します。[選手].[選手名].Childrenとなります。Childrenは選手名を「全部取得する」という意味です。ダブルクォーテーションを入力します。
さらに、カンマを入力します。第3引数は「"HR首位"」と入力します。さらに、カンマを入力します。第4引数の並べ替えの順序は１＝昇順、２＝降順、０＝並べ替えない、です。HR本数の多いほうから並べるので２です。
さらに、カンマを入力します。第5引数は並べ替えの基準となるキーなのでHRの本数です。さきほどの問題と同じように、ダブルクォーテーションを入力して、[Measure]を選択します。ドットを入力します。「[合計 / HR]」と入力します。
ダブルクォーテーションを入力するのを忘れてはいけません。このままかっこを閉じます。これで「HR首位」と表示されます。
HR首位と表示されたということは並べ替えが成功したということであり、セルには表示されていませんが、内部的にはHR本数の多いほうから順に「おちあい、ばあす、ぶうま、くろまて、かけふ、でひす、・・・」という選手名を並べたセット（配列のようなもの）が取得できたということです。このセットは次のCUBERANKEDMEMBER関数で利用します。
７．CUBERANKEDMEMBER関数（最大のもの、最小のもの）
問題
Excelファイルに読み込まれているExcelデータモデルをもとに、Excelファイルに読み込まれているExcelデータモデルをもとに、HR本数の最も多い選手の名前を表示しなさい。また、3番目に多い選手を表示しなさい。
解説
CUBERANKEDMEMBER関数（キューブ・ランクドメンバー）は、並べ替えられているセット内の指定した位置のメンバーを返します。
- CUBERANKEDMEMBER(接続, セット式, ランク, [キャプション])
CUBERANKEDMEMBER関数のRANKEDMEMBERとは、順番に並べたセットのうちの１つのメンバーのことで、さきほどのCUBESET関数で並べ替えた項目のうちの1つを取り出すときに使います。打率が最も高いということは1番目の選手（落合）ということになります。
まず、「=cu」だけ入力します。上下の矢印キーで「CUBERANKEDMEMBER」を選びます。TABキーを押します。
第1引数は接続名です。ダブルクォーテーションを入力すると、「ThisWorkbookDataModel」と表示されます。TABキーを押します。ダブルクォーテーションを入力するのを忘れてはいけません。
さらに、カンマを入力します。第2引数はセット式ですが、これは先ほどCUBESET関数を入力したセルです。ダブルクォーテーションを入力せずに、セルB4をクリックします。
さらに、カンマを入力します。第3引数のランクとは順番のことですが、1番目であれば１と入力します。ダブルクォーテーションは不要です。これで落合が表示されます。
第3引数を３にすると、3番目の選手（ブーマー）が表示されます。
＊補足＊CUBESETの段階で昇順にすれば最小のものを取得することができます。
８．数式に変換
問題
次のピボットテーブルを数式に変換しなさい。
解説
ピボットテーブルツール、分析タブのなかのOLAPツール、数式に変換をクリックします。
これでピボットテーブルを数式に変換されます。この数式はキューブ関数です。数式に変換してもExcelデータモデルは消えません。
９．CUBERANKEDMEMBER関数とCUBESET関数の入れ子
問題
数式に変換されている表をもとに、「Cリーグ」でHR本数が最も多いチームのチーム名を表示しなさい。
解説
さきほどの問題で、CUBERANKEDMEMBER関数の第2引数のセット式でCUBESET関数を入力したセルを利用しました。そこで、今度はCUBERANKEDMEMBER関数のなかにCUBESET関数を入れた式を作ってみましょう。
- CUBERANKEDMEMBER(接続, CUBESET(接続, セット式, [キャプション], [並べ替え順序], [並べ替えキー]), ランク, [キャプション])
まず、「=cu」だけ入力します。上下の矢印キーで「CUBERANKEDMEMBER」を選びます。TABキーを押します。
第1引数は接続名です。ダブルクォーテーションを入力すると、「ThisWorkbookDataModel」と表示されます。TABキーを押します。ダブルクォーテーションを入力するのを忘れてはいけません。さらに、カンマを入力します。
第2引数はセット式ですが、「cu」だけ入力します。上下の矢印キーで「CUBESET」を選びます。TABキーを押します。
第1引数は接続名です。ダブルクォーテーションを入力すると、「ThisWorkbookDataModel」と表示されます。TABキーを押します。ダブルクォーテーションを入力するのを忘れてはいけません。さらに、カンマを入力します。
第2引数はセット式です。本来ならCリーグの[チーム].[チーム名].Childrenですが、キューブ関数に変換したチーム名がありますので、それを参照すればよいです。Cリーグの3チームを範囲選択します。
さらに、カンマを入力します。第3引数は特に必要ないのでそのままもう一回カンマを入力します。カンマが2連続になります。
第4引数の並べ替えの順序は降順（２）です。さらに、カンマを入力します。
第5引数は並べ替えの基準となるキーは[Measure].[合計 / HR]ですが、これもキューブ関数に変換したものがありますのでこれを参照します。
括弧を閉じてカンマを入力します。HRの一番多いチームなので、ランクは１です。
- =CUBERANKEDMEMBER("ThisWorkbookDataModel",CUBESET("ThisWorkbookDataModel",A5:A7,,2,B3),1)
これで「阪神」と表示されます。
10．試験対策として
ここまでキューブ関数の概要を説明しましたが、実務上使うことはめったにないと思われます。
MOSでは多くても1問しか出題されませんから、1000点満点を狙わない限り、捨てても合格できます。FOM出版の練習問題がどうしてもわからなければ飛ばしてもかまいません。
解説は以上です。
