わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

キューブ関数とは何か、CUBE関数の使い方をわかりやすく解説します【MOS Excel2016合格対策】

MOS Excel Expertの出題範囲のなかで受験生を悩ませる、難しい関数の一つにキューブ関数(CUBESET関数、CUBEMEMBER関数、CUBEVALUE関数、CUBERANKEDMEMBER関数など)があります。

キューブは、あらかじめ集計されたものを効率よく検索するための特殊なデータベースで、通常は外部のデータベースサーバーに作ります。しかし、MOSではExcelファイルの内部にある「Excelデータモデル」を使う問題が出題されます。キューブとは何か?データモデルとは何か?ということを理解しないと解くことができません。

そこで、今回は、キューブ関数がさっぱりわからないというキューブ・アレルギーの皆さんのために、MOSで出題されるキューブ関数について解説します。

※CUBE関数は2010以降で使用可能ですが、2016で解説しています。また、MOS対策なのでMDX構文の説明はかなり大雑把です。

 

 

目次

0.MOS Excel Expertについて

MOSエキスパート試験の概要と、MOSの練習問題についてはFOM出版の対策テキストをご覧ください。

 

1.キューブとは何か

(1)キューブは集計結果を格納する

通常、集計表を作る時は、1つまたは複数の表(リスト形式)にデータを入力して、その中からデータを取り出したり、集計したりします。

f:id:waenavi:20191017191543j:plain

 

ピボットテーブルを使うのが一般的です。Excelのように何千行、何万行の程度であればこのような分析方法で問題ありません。

f:id:waenavi:20191017191616j:plain

 

しかし、企業が扱う何百万件、何億件といったデータを持つ巨大なデータベースに、多くの社員が直接アクセスして、直接集計するとサーバーがものすごく遅くなります。しかも、ピボットテーブルのようにいろいろと集計項目を変えるとさらに時間がかかります。

f:id:waenavi:20191017192034j:plain

 

そこで、あらかじめ集計する方法を決めておいて、元のデータの一部と集計結果をあわせ持つデータベースを作っておけば、瞬時に集計結果を検索することができます。このような集計用のデータベースのことを、OLAP CUBE(オラップ・キューブ)略して「キューブ」といいます。

f:id:waenavi:20191017192540j:plain

 

なお、キューブは閲覧するだけでなく、直接、データを入力したり、データを修正したりすることも可能です。

f:id:waenavi:20191017192825j:plain

 

(2)キューブのメリットとデメリット

キューブは特殊な構造を持ち、データ入力(取得)や事前の集計処理に時間がかかるため、通常は夜間に自動的に集計を行うようにタイマーをセットしておく、いわゆる夜間バッチで集計することが多いです。キューブを作っておくと高速に集計結果が得られます。なぜなら事前に集計をしているからです。計算方法も事前に指定しているのでSUMなどの関数を使うこともありません。「合計された値」を呼び出すだけです。

f:id:waenavi:20191017194148j:plain

 

しかし、キューブは集計結果を格納するものなので、基本的に保存できるのは数値データだけです。数値でないデータは分析ができません。

f:id:waenavi:20191017194338j:plain

 

また、リアルタイムで入ってくるデータは反映されないので、数時間のタイムラグが発生します。

f:id:waenavi:20191017193515j:plain

 

2.キューブ関数を知るための言葉を覚えよう

(1)接続名

Excelのキューブ関数を使うときには、あらかじめネットワークを通じてサーバーに接続しておかなければなりません。

f:id:waenavi:20191017194541j:plain

 

データタブ、外部データ取り込みのなかのその他のデータソースで、データベースに接続します。

f:id:waenavi:20191017194623j:plain

 

しかし、サーバーが無かったらこの画面で終了です(MOSの場合は接続できなくても問題ない)。

f:id:waenavi:20191017194650j:plain

 

接続をするときには名前を付けます(フレンドリ名)。フレンドリ名の欄に入力したものが「接続名」です。

f:id:waenavi:20191017194736j:plain

 

なお、接続の詳しい方法については下記のマイクロソフト公式サイトをご覧ください。

OLAP データベースに接続する (インポートする)

https://support.office.com/ja-jp/article/olap-データベースに接続する-インポートする-e26384cb-30be-4324-adee-066cd62b1ae9

 

(2)ディメンションとメンバー

次の表は、縦を店舗とし、横を時系列にして売上金額を入力した二次元の表です。売上金額を店舗と月に分けて入力したものです。店舗と月のことをディメンション(Dimention, 次元軸)といいます。

f:id:waenavi:20191017195251j:plain

 

それぞれのディメンションは階層構造になっていて、基本項目とそのグループである集計項目があります。例えば、4月~9月が基本項目で、第1四半期・第2四半期・半期は集計項目です。また、各支店名が基本項目で、東日本・西日本・全国は集計項目です。

それぞれの階層や項目のことを「メンバー」(Member)といいます。また、時系列や店舗とは異なる分類、例えば、商品ごとや担当者ごとに金額を入力すれば、3次元、4次元とディメンション(次元)が増えていきます。

f:id:waenavi:20191017200737j:plain

 

(3)メジャー

キューブは、ルービックキューブのような立体で表すことがありますが、データを入力するかまたはほかのデータベースからデータが入ってくると、通常は、キューブ内の該当する「基本項目」のところにデータが格納されます。これを「セル」ということがあります。

f:id:waenavi:20191017220156j:plain

 

キューブ内で集計を実行すると、集計項目のセルに集計結果が格納されます。これを事前集計処理ということがあります。基本項目のデータが更新されたら、その上の階層(集計項目)も変わります。前述のとおり、事前集計処理は夜間に自動的にあらかじめやっておく処理です。

f:id:waenavi:20191017215954j:plain

 

このように定期的にキューブ内で集計をしておけば、ユーザーは集計結果にアクセスするだけで、集計値を見ることができます。このように個々のセルに格納された集計結果(集計値)のことをメジャー(Measureといいます。

f:id:waenavi:20191017215724j:plain

 

3.Excelデータモデルがあることを確認してみよう

問題

2つのテーブル「選手」「チーム」とピボットテーブルがある。

f:id:waenavi:20191017202338j:plain

 

2つのテーブルがExcelデータモデルに登録され、それをもとにピボットテーブルが作られていることを確認しなさい。

f:id:waenavi:20191017202403j:plain

f:id:waenavi:20191017202424j:plain

 

解説

MOS Excel 2016エキスパートレベル(上級レベル)の出題範囲には「キューブ関数を使ってExcelデータモデルからデータを取り出す」と記載されています。Excelデータモデルとは、ExcelやAccessなどの複数の表をExcelに取り込み、統合して1つのデータベースにしたものです。これはExcelファイルに取り込んで使うものであり、外部のサーバに接続する必要はありません。

f:id:waenavi:20191017203529j:plain

 

MOS試験ではすでにデータモデルが作られている状態で出題されます。MOSで出題されるデータモデルの作り方については別の記事で解説しています。

 

とりあえずExcelデータモデルがあることだけを確認してみましょう(※試験本番ではこの確認作業は不要です)。まず、2つのテーブルがあることを確認します。そして、テーブル名を確認します。テーブル名はテーブルツール、デザインタブにあります。

f:id:waenavi:20191017203638j:plain

f:id:waenavi:20191017203658j:plain

 

そして、これらのテーブルには同じ項目名があります。「チームID」は同じものです。データタブのリレーションシップを確認します。

f:id:waenavi:20191017203808j:plain

 

2つのテーブルの「チームID」が接続されています。

f:id:waenavi:20191017204010j:plain

 

2つの表に分けて入力してありますが、チームIDで接続することによって1つのデータベースとして扱うという意味です。この1つのデータベースのことを「Excelデータモデル」といいます。

f:id:waenavi:20191017204328j:plain

 

次に、ピボットテーブルを確認します。ピボットテーブルのフィールドには2つのテーブルが表示されています。

f:id:waenavi:20191017204459j:plain

 

2つのテーブルからチーム名、リーグ名、打席が集約されて、HR本数の合計が求められています。

f:id:waenavi:20191017204417j:plain

 

4.CUBEMEMBER関数(項目名を表示する)

問題

Excelファイルに読み込まれているExcelデータモデルをもとに、チーム横浜を表すメンバーを取得し、項目名を表示しなさい。また、左打席を表すメンバーを取得し、項目名を表示しなさい。

f:id:waenavi:20191017204928j:plain

 

解説

Excelデータモデルは厳密にいえばキューブではありませんが、キューブとして扱うことができ、キューブ関数を使うことができます。

CUBEMEMBER関数(キューブ・メンバー)は、キューブ内にメンバーが存在することを確認する関数です。メンバーが存在すればメンバー名を返し、存在しなければエラー値 #N/Aを返します。引数はセルの参照でない限り必ず文字列なので、ダブルクォーテーションで囲むことが必要です。

  • =CUBEMEMBER("接続名", "メンバー式", "[キャプション]")

CUBEMEMBER関数の「MEMBER」とは項目名または階層のことで、特にピボットテーブルでは、縦横に並んでいる項目名を取得するときに使います。「項目名を表示しなさい」「メンバーを取得しなさい」といわれたらCUBEMEMBERです。

f:id:waenavi:20191017205250j:plain

 

ここで、テーブルとそれに含まれる項目名を確認しておきます。

f:id:waenavi:20191017204459j:plain

 

キューブ関数を入力するときはセルに直接入力して、数式入力補完機能(=オートコンプリート)を最大限利用します。まず、「=cu」だけ入力します。これでキューブ関数が表示されます。

f:id:waenavi:20191017205359j:plain

 

上下の矢印キーで「CUBEMEMBER」を選びます。

f:id:waenavi:20191017205445j:plain

 

TABキーを押します(cubememberと直接入力しても構いません)。

f:id:waenavi:20191017205504j:plain

 

第1引数は接続名です。ダブルクォーテーションを入力すると、「ThisWorkbookDataModel」と表示されます(※なお、「ThisWorkbookDataModel」と表示されるのに若干時間がかかることがあります)。Excelデータモデルの接続名は自動的に「ThisWorkbookDataModel」となっています。

f:id:waenavi:20191017205604j:plain

 

TABキーを押します。

f:id:waenavi:20191017205651j:plain

 

ダブルクォーテーションを入力します。ここで、ダブルクォーテーションで閉じるのを忘れてはいけません。

f:id:waenavi:20191017205716j:plain

 

さらに、カンマを入力します。第2引数はメンバー式です。ダブルクォーテーションを入力します。テーブル名を選択します。いまはチーム名を取得しようとしているので「チーム」です。

f:id:waenavi:20191017205757j:plain

 

ドットを入力します。項目名を選択します。セルに表示しようとしているのは「チーム名」です。チームIDではないことに注意します。

f:id:waenavi:20191017205833j:plain

ドットを入力します。[All]と表示されることがあります。これを選びます。

f:id:waenavi:20191017205908j:plain

 

ドットを入力します。[横浜]を選びます。

f:id:waenavi:20191017205928j:plain

 

ちなみに、[All]というのは合計という意味ですが、[All]を省略することができます。したがって、[チーム].[チーム名].[横浜]となります。このように、メンバー式は"[テーブル名].[項目名].[項目]"の順番で、ドットでつないで表現します。

f:id:waenavi:20191017205958j:plain

 

ダブルクォーテーションを入力します。ここで、ダブルクォーテーションで閉じるのを忘れてはいけません。

f:id:waenavi:20191017210034j:plain

 

第3引数の入力は不要なので、このままかっこを閉じます。これで「横浜」と表示されます(※「横浜」と表示されるのに若干時間がかかることがあります)。

f:id:waenavi:20191017210057j:plain

 

キューブ関数は次の3つを忘れなければどの関数も簡単に入力することができます。

  • 最初に「=cu」と入力する
  • 引数はダブルクォーテーションで囲む(特に最後を忘れないこと)
  • メンバー式はドットで連結する

同じように、「左」打席も表示してみましょう。まず、「=cu」だけ入力します。上下の矢印キーで「CUBEMEMBER」を選びます。

f:id:waenavi:20191017210212j:plain

 

TABキーを押します。第1引数は接続名です。ダブルクォーテーションを入力すると、「ThisWorkbookDataModel」と表示されます。MOSの試験では必ず「ThisWorkbookDataModel」です。

f:id:waenavi:20191017210257j:plain

 

TABキーを押します。ダブルクォーテーションを入力します。

f:id:waenavi:20191017210330j:plain

 

さらに、カンマを入力します。第2引数はメンバー式です。ダブルクォーテーションを入力します。テーブル名を選択します。いまは左右(打席)を取得しようとしているので「選手」です。

f:id:waenavi:20191017210353j:plain

 

ドットを入力します。項目名を選択します。セルに表示しようとしているのは「打席」です。

f:id:waenavi:20191017210412j:plain

 

ドットを入力します。[All]と表示される場合はこれを選びます(※ここで[左]と直接入力しても構いません)。

f:id:waenavi:20191017210431j:plain

 

ドットを入力します。[左]を選びます。

f:id:waenavi:20191017210457j:plain

 

[All]は消すことができます。したがって、[選手].[打席].[左]となります。ダブルクォーテーションを入力します。ここで、ダブルクォーテーションで閉じるのを忘れてはいけません。

f:id:waenavi:20191017210521j:plain

 

第3引数の入力は不要なので、このままかっこを閉じます。これで「左」と表示されます。

f:id:waenavi:20191017210541j:plain

 

5.CUBEVALUE関数(合計などを表示する)

問題

Excelファイルに読み込まれているExcelデータモデルをもとに、横浜チームの左打者のHR本数の合計を表示しなさい。

f:id:waenavi:20191017210608j:plain

 

解説

CUBEVALUE関数(キューブ・バリュー)は、指定したメンバーの値を返します。

  • CUBEVALUE(接続, [メンバー式 1], [メンバー式 2], …)

CUBEVALUE関数の「VALUE」とは、該当するセルの値のことで、特に、ピボットテーブルの場合、縦と横の項目名を指定することによって集計した値を求めるときに使います。「合計を表示しなさい」とあればCUBEVALUEです。

f:id:waenavi:20191017210822j:plain

 

まず、「=cu」だけ入力します。上下の矢印キーで「CUBEVALUE」を選びます。

f:id:waenavi:20191017210857j:plain

 

TABキーを押します。第1引数は接続名です。ダブルクォーテーションを入力すると、「ThisWorkbookDataModel」と表示されます。TABキーを押します。ダブルクォーテーションを入力するのを忘れてはいけません。

f:id:waenavi:20191017210920j:plain

 

さらに、カンマを入力します。

f:id:waenavi:20191017210946j:plain

 

第2引数はメンバー式です。本来なら、横浜チームの左打者を求めるにはさきほどと同じように「"[チーム].[チーム名].[横浜]","[選手].[打席].[左]"」と入力する必要がありますが、既にこのメンバー式を入力したセルがあればこれを参照することによって利用することができます。ダブルクォーテーションを入力せずに、セルB1をクリックします。

f:id:waenavi:20191017211017j:plain

 

さらに、カンマを入力します。ダブルクォーテーションを入力せずに、セルB2をクリックします。

f:id:waenavi:20191017211038j:plain

 

カンマを入力します。ダブルクォーテーションを入力して、[Measures]を選択します。これはメジャー=集計値のことです。

f:id:waenavi:20191017211109j:plain

 

ドットを入力します。ここで、「[合計 / HR]」と入力します。これはピボットテーブルの左上にある集計方法のことです。なお、スラッシュの前後には半角のスペースが必要です。

f:id:waenavi:20191017211210j:plain

f:id:waenavi:20191017211511j:plain

 

ダブルクォーテーションを入力するのを忘れてはいけません。かっこを閉じます。これで「26」と表示されます。

f:id:waenavi:20191017211358j:plain

 

このようにピボットテーブル(Excelデータモデル)の場合、CUBEVALUE(ThisWorkbookDataModel, 項目, 項目, Measures)の形になります。

 

6.CUBESET関数(セットの取得)

問題

Excelファイルに読み込まれているExcelデータモデルをもとに、選手名をHR本数の多いほうから順に並べたデータのセットを取得し、「HR首位」と表示しなさい。

f:id:waenavi:20191017211753j:plain

 

解説

CUBESET関数(キューブ・セット)は、1つ以上のメンバー、組み、セット、セル範囲を並べ替えたものを返す関数です。

  • CUBESET(接続, セット式, [キャプション], [並べ替え順序], [並べ替えキー])

CUBESET関数のSETとは、メンバーを、指定した順序に並べ替えたものです。「並べ替えたもの」なので具体的な答えが返ってくるわけではありません。そこで、並べ替えがうまくいった場合は、第3引数でセルに表示する文字列を指定します。

まず、「=cu」だけ入力します。上下の矢印キーで「CUBESET」を選びます。TABキーを押します。

f:id:waenavi:20191017211845j:plain

 

第1引数は接続名です。ダブルクォーテーションを入力すると、「ThisWorkbookDataModel」と表示されます。TABキーを押します。ダブルクォーテーションを入力するのを忘れてはいけません。

f:id:waenavi:20191017211920j:plain

 

さらに、カンマを入力します。第2引数はセット式です。ダブルクォーテーションを入力します。テーブル名を選択します。いまは選手名を取得しようとしているので「選手」です。

f:id:waenavi:20191017211950j:plain

 

ドットを入力します。項目名を選択します。「選手名」です。

f:id:waenavi:20191017212039j:plain

 

ドットを入力します。[All]と表示される場合がありますが、これを無視して「Children」と入力します。[選手].[選手名].Childrenとなります。Childrenは選手名を「全部取得する」という意味です。ダブルクォーテーションを入力します。

f:id:waenavi:20191017212124j:plain

 

さらに、カンマを入力します。第3引数は「"HR首位"」と入力します。さらに、カンマを入力します。第4引数の並べ替えの順序は1=昇順、2=降順、0=並べ替えない、です。HR本数の多いほうから並べるので2です。

f:id:waenavi:20191017212228j:plain

 

さらに、カンマを入力します。第5引数は並べ替えの基準となるキーなのでHRの本数です。さきほどの問題と同じように、ダブルクォーテーションを入力して、[Measure]を選択します。ドットを入力します。「[合計 / HR]」と入力します。

f:id:waenavi:20191017212327j:plain

 

ダブルクォーテーションを入力するのを忘れてはいけません。このままかっこを閉じます。これで「HR首位」と表示されます。

f:id:waenavi:20191017212424j:plain


HR首位と表示されたということは並べ替えが成功したということであり、セルには表示されていませんが、内部的にはHR本数の多いほうから順に「おちあい、ばあす、ぶうま、くろまて、かけふ、でひす、・・・」という選手名を並べたセット(配列のようなもの)が取得できたということです。このセットは次のCUBERANKEDMEMBER関数で利用します。

 

7.CUBERANKEDMEMBER関数(最大のもの、最小のもの)

問題

Excelファイルに読み込まれているExcelデータモデルをもとに、Excelファイルに読み込まれているExcelデータモデルをもとに、HR本数の最も多い選手の名前を表示しなさい。また、3番目に多い選手を表示しなさい。

f:id:waenavi:20191017212603j:plain

 

解説

CUBERANKEDMEMBER関数(キューブ・ランクドメンバー)は、並べ替えられているセット内の指定した位置のメンバーを返します。

  • CUBERANKEDMEMBER(接続, セット式, ランク, [キャプション])

CUBERANKEDMEMBER関数のRANKEDMEMBERとは、順番に並べたセットのうちの1つのメンバーのことで、さきほどのCUBESET関数で並べ替えた項目のうちの1つを取り出すときに使います。打率が最も高いということは1番目の選手(落合)ということになります。

f:id:waenavi:20191017213223j:plain

 

まず、「=cu」だけ入力します。上下の矢印キーで「CUBERANKEDMEMBER」を選びます。TABキーを押します。

f:id:waenavi:20191017213305j:plain

 

第1引数は接続名です。ダブルクォーテーションを入力すると、「ThisWorkbookDataModel」と表示されます。TABキーを押します。ダブルクォーテーションを入力するのを忘れてはいけません。

f:id:waenavi:20191017213335j:plain

 

さらに、カンマを入力します。第2引数はセット式ですが、これは先ほどCUBESET関数を入力したセルです。ダブルクォーテーションを入力せずに、セルB4をクリックします。

f:id:waenavi:20191017213412j:plain

 

さらに、カンマを入力します。第3引数のランクとは順番のことですが、1番目であれば1と入力します。ダブルクォーテーションは不要です。これで落合が表示されます。

f:id:waenavi:20191017213455j:plain

 

第3引数を3にすると、3番目の選手(ブーマー)が表示されます。

f:id:waenavi:20191017213458j:plain

 

*補足*CUBESETの段階で昇順にすれば最小のものを取得することができます。

 

8.数式に変換

問題

次のピボットテーブルを数式に変換しなさい。

f:id:waenavi:20191017213532j:plain

 

解説

ピボットテーブルツール、分析タブのなかのOLAPツール、数式に変換をクリックします。

f:id:waenavi:20191017213555j:plain

 

これでピボットテーブルを数式に変換されます。この数式はキューブ関数です。数式に変換してもExcelデータモデルは消えません。

f:id:waenavi:20191017213625j:plain

 

9.CUBERANKEDMEMBER関数とCUBESET関数の入れ子

問題

数式に変換されている表をもとに、「Cリーグ」でHR本数が最も多いチームのチーム名を表示しなさい。

f:id:waenavi:20191017213730j:plain

 

解説

さきほどの問題で、CUBERANKEDMEMBER関数の第2引数のセット式でCUBESET関数を入力したセルを利用しました。そこで、今度はCUBERANKEDMEMBER関数のなかにCUBESET関数を入れた式を作ってみましょう。

  • CUBERANKEDMEMBER(接続, CUBESET(接続, セット式, [キャプション], [並べ替え順序], [並べ替えキー]), ランク, [キャプション])

まず、「=cu」だけ入力します。上下の矢印キーで「CUBERANKEDMEMBER」を選びます。TABキーを押します。

f:id:waenavi:20191017213913j:plain

 

第1引数は接続名です。ダブルクォーテーションを入力すると、「ThisWorkbookDataModel」と表示されます。TABキーを押します。ダブルクォーテーションを入力するのを忘れてはいけません。さらに、カンマを入力します。

f:id:waenavi:20191017213950j:plain

 

第2引数はセット式ですが、「cu」だけ入力します。上下の矢印キーで「CUBESET」を選びます。TABキーを押します。

f:id:waenavi:20191017214014j:plain

 

第1引数は接続名です。ダブルクォーテーションを入力すると、「ThisWorkbookDataModel」と表示されます。TABキーを押します。ダブルクォーテーションを入力するのを忘れてはいけません。さらに、カンマを入力します。

f:id:waenavi:20191017214049j:plain

 

第2引数はセット式です。本来ならCリーグの[チーム].[チーム名].Childrenですが、キューブ関数に変換したチーム名がありますので、それを参照すればよいです。Cリーグの3チームを範囲選択します。

f:id:waenavi:20191017214121j:plain

 

さらに、カンマを入力します。第3引数は特に必要ないのでそのままもう一回カンマを入力します。カンマが2連続になります。

f:id:waenavi:20191017214200j:plain

 

第4引数の並べ替えの順序は降順(2)です。さらに、カンマを入力します。

f:id:waenavi:20191017214225j:plain

 

第5引数は並べ替えの基準となるキーは[Measure].[合計 / HR]ですが、これもキューブ関数に変換したものがありますのでこれを参照します。

f:id:waenavi:20191017214251j:plain

 

括弧を閉じてカンマを入力します。HRの一番多いチームなので、ランクは1です。

  • =CUBERANKEDMEMBER("ThisWorkbookDataModel",CUBESET("ThisWorkbookDataModel",A5:A7,,2,B3),1)

f:id:waenavi:20191017214324j:plain

 

これで「阪神」と表示されます。

f:id:waenavi:20191017214410j:plain

 

10.試験対策として

ここまでキューブ関数の概要を説明しましたが、実務上使うことはめったにないと思われます。
MOSでは多くても1問しか出題されませんから、1000点満点を狙わない限り、捨てても合格できます。FOM出版の練習問題がどうしてもわからなければ飛ばしてもかまいません。

 


解説は以上です。


 

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]