Excel。氏名の総計をキューブ関数で算出するのがさっぱりわかりません。
＜CUBEMEMBER関数＞
MOS（マイクロソフトオフィススペシャリスト）という試験があります。
MOS ExcelのExpertというレベルの試験の試験範囲に「CUBE関数シリーズ」が含まれています。
「CUBE関数」は日頃、ほぼ使わない関数ということもあり「さっぱりわかりません」と最初から捨ててしまう人も多くいるようです。
正直なところ、出題されないかもしれないし、出題されても多くはないだろうから、捨てて、それ以外の範囲の精度を上げたほうが、合格にはいいのかもしれません。
ただ、出題範囲のCUBE関数は、「カラクリ」や疑問が解消されてくると、それほど難しい数式ではないので、解答できるようになります。
今回は、簡単に解き方などをご紹介します。
FOM出版さんや日経BPさんの模擬問題で確認していただければと思いますが、FOM出版さんの模擬問題に、「氏名ごとの総計」を算出するのにキューブ関数をつかって算出してほしいというのがあります。
まずは、「カラクリ」というか、算出するシートは、すでに、数式の作り方の前に確認しておきたいことが「2つ」あります。
１つ目は、すでに「データモデル」が設定されている。
データモデルの話をするとここで終わってしまうので、ざっくりいうと、分析用のデータベースが用意されていますということです。
2つ目は、必要なところに、CUBEMEMBER関数をつかっている。
キューブ関数一つで、数式をつくろうとすると、引数が長くなりすぎるので、準備したセルがあります。
それが、C3の「総計」とB4の安川さん から B23の立川さん までです。
該当のセルをクリックすると数式を見ることができます。
例えば、模擬問題のシート：会員別のC3には、
=CUBEMEMBER("ThisWorkbookDataModel","[売上データ].[注文日 (月)].[All]","総計")
B4の安川さんには、
=CUBEMEMBER("ThisWorkbookDataModel","[会員].[氏名].&[安川 博美]")
というように事前に準備されてある状態のシートなんだということです。
要するにキューブ関数の問題は、算出するための数式を作るだけの状態になっているというわけです。
総計という集計値を算出したいので、キューブ関数の「CUBEVALUE関数」をつかいます。
模擬問題のC4の安川さんのセルには、
=CUBEVALUE("ThisWorkbookDataModel",B4,$C$3,"[Measures].[合計 / 売上価格]")
という数式を設定して、あとは、立川さんまで、オートフィルで数式をコピーします。
これで、完成という流れです。
模擬問題を丸々使うわけにはいきませんので、データが同じではありませんが、数式の作り方を説明していきます。
自分で、データをつくって、算出してみたい方は、下記の方法を参考にしてください。
あと、キューブ関数は、基本的に「手入力」でつくります。
ダイアログボックスだと、単語を覚えておかないといけないので、ちょっと、面倒です。
ブックの説明をしていきます。
担当データシートに、元になるデータがあります。
このデータから、ピボットテーブルをつくります。
その時に、「テーブルまたは範囲からのピボットテーブル」ダイアログボックスにある、「このデータをデータモデルに追加する」にチェックマークをいれると、データモデルをつくることができます。
これが、キューブ関数をつくるときに表示される「ThisWorkbookDataModel」というわけです。
模擬試験は、外部データなので、ピボットテーブルというわけではありませんので、ご注意ください。
氏名の総計を算出していきます。
総計を算出するためには、先ほど模擬問題で説明したように、C3とB4:B7に、CUBEMEMBER関数を設定しておく必要があります。
その後、C4をクリックします。
総計という値を算出したいので、キューブ関数の、「CUBEVALUE関数」をつかいます。
CUBEVALUE関数を選択したら、最初の引数を設定しますので、躊躇なく「”（ダブルコーテーション）」を入力します。
入力候補に、「ThisWorkbookDataModel」が表示されますので、選択します。
ThisWorkbookDataModelのあとに、「”（ダブルコーテーション）」を入力します。
2つ目の引数の「メンバー式1」を設定しますので、「,（カンマ）」を入力します。
メンバー式1とは何なのか。これは、B4に設定したCUBEMEMBER関数の数式のことです。
事前に準備してあるといったのは、本来B4に設定しなければ、2つ目の引数に、B4の数式を入力する必要があったわけです。
そうなると、数式が長くなりすぎて可読性が悪化することもあり、B4にCUBEMEMBER関数をつかって算出しておいたというわけです。
なので、メンバー式1には、「B4」と設定します。
3つ目の引数はメンバー式2なのですが、これも、メンバー式1と同じで、C3にCUBEMEMBER関数を設定してあります。
「C3」をつかいますが、オートフィルで数式をコピーするので、絶対参照が必要になりますので、「$C$3」と設定します。
最後4つ目の引数のメンバー式3をつくりますので、「,（カンマ）」を入力したら「”（ダブルコーテーション）」を入力します。
[Measures]を選択します。この[Measures]は、集計しろという意味です。
「.（ドット）」で区切って「[合計 / 金額]」と入力します。
「/（スラッシュ）」の前後を半角空白で空けるのかというと、ピボットテーブルの値のフィールド見出しを見れば、同じようにするんだなとわかるかと。
あとは、「”（ダブルコーテーション）」を入力して、メンバー式3を完成させたら、数式自体が完成なので、「)」で閉じます。
C4の数式は、
=CUBEVALUE("ThisWorkbookDataModel",B4,$C$3,"[Measures].[合計 / 金額]")
あとは、オートフィルで数式をコピーして完成です。
これで、該当するデータの集計を算出することができました。
ざっくりでしたが、キューブ関数の説明でした。
