キューブ関数の世界一簡単な説明

2019/2/11 11:23 追記

MOS Excel Expertの試験範囲にもなっているキューブ関数ですが。
これ、MOS Expert受験した人、勉強した人で理解できる方、いらっしゃいますでしょうか。
なんだか、日本ではそんなに使うケースを想定できないし、正直、MOS Expertの受験層には合っていないのではないかなと思ったのですが。
とは言うものの、やっぱり知っていれば知ってるだけ使い方があるので、今回はキューブ関数のうち、一番使うであろうCUBEVALUE関数の使い方をそんなに難しくないレベルで紹介してみたいと。
データをいじりながら読んでみた方が面白いので、データをOneDriveに置きました。
ダウンロードして使ってください。
https://1drv.ms/x/s!AmF9El5QuPUYgeMcvTCfgKPTO53Cgw

いっぱい項目のある表の処理

世の中には次のようなデータがあります。今回は架空のデータですが、絶対こんな風に項目数がめっちゃ多い表があります。

で、この表、数字を集計するとしたらどんな集計しますかね。
年月ごとに金額を集計できますね。それで金額の動向つかめるし、前年同月比だって出ますよね。
天気によって契約金額が変わるとかあるかもしれないですね。ないかもしれないですけど分析することはできますね。
納入先の地域ごとに担当者の年齢性別ごとに、成績がいい層ってあるかもしれないですね。だとしたら契約担当者は契約の取りやすい層の人にさせたほうが実績出ますよね。
とか、いろいろ分析ができます。
その分析をする時に使うのは、おそらく一番優れているツールはピボットテーブルだと思うんですよ。
でも、この表で次のような分析をしたくなったらどうします？
曜日ごとに天気ごとに平均気温を5度おきに契約担当年齢を10歳おきに契約担当性別ごとに顧客都道府県ごとの商品ごとの契約金額の平均。
そんなのピボットテーブルでできませんよね。
というのもピボットテーブルでは、縦横の2つにしか表を作れないからです。工夫すればフィルタエリアを使ってもう一つできるかもしれないですけど。
そこで使っていきたいのがキューブ関数です。
でも、キューブ関数を使っても、結局Excelって縦横でしかセルがないので表現するにも2要素が限界、これは大事なので抑えておいてください！

キューブ関数の準備

さて、実際にキューブ関数の使い方ですが、本当に間違いを恐れずに言うのですが、キューブ関数は、ピボットテーブルを作っていないとできません。
はじめにExcelのブックにピボットテーブルを用意すること、ここから始めます。
ただ、ピボットテーブルも普通に作るのではなく、もうワンステップ必要です。
まず、元データの表をクリックして、挿入タブのピボットテーブル、ここまでは普通に操作します。

ここです！ここで、「このデータをデータモデルに追加する」このチェックを入れてください。

どうやら裏っかわで、ブックにデータモデルという形でピボットテーブルが埋め込まれるようです（この辺は正直ブラックボックスすぎて触れたくない）。

ここで、集計する数値の契約金額を値にセットして合計を求めます。準備はこれだけでいいです。

ここで、性別ごとに支店ごとに顧客の都道府県ごとに商品ごとの契約金額を求めてみたいのですが、要素が4つあるので見やすい表にするためにどうしたらいいでしょうね。
本当は一番何ごとに見たいかで分けるんですけど、今回は一番分けてみたいのが支店だとして考えます。
まずは、それぞれの項目でどれだけのデータの種類があるか、重複の削除機能などで重複データを削除します。

で、ここからこんな表にします。これを支店ごとに作ります。

これならまあまあExcelで見やすいかなと思います。
当然このレベルではピボットテーブルで配置してもできますが、今回はキューブ関数の紹介なのでキューブ関数で作ります。

CUBEVALUE関数を入力してみよう！

セルB2に「=CUBE」と入力すると、「CUBEVALUE」関数が出てきます。選んでください。

次に「”」を入力します。すると自動的に「ThisWorkbookDataModel」が出てきます。これを選んで「”」で閉じてください。

あとは、ここに入る「女」「宮城」「商品1」「関東」の項目名を入れていけばそのデータが指定されます。これをメンバと呼ぶらしいです。
なのですが、だたそのセルを選択してだけだとうまく認識してくれなくて、例えば「女」であれば「[女]」にしないといけないようなのですね。
だから例えばセルB1の「女」であれば、「”[“&B1&”]”」としなければならないのです。
そのうえ、それぞれのセルを複合参照にしないとコピーできないのです。セルB1、B2は行だけ絶対、セルA3は列だけ絶対、セルA2は行列とも絶対参照です。
これを「,」で繋いでいきます。
とすると次のとこまでできますね。

=CUBEVALUE(“ThisWorkbookDataModel”,”[“&B$1&”]”,”[“&B$2&”]”,”[“&$A3&”]”,”[“&$A$2&”]”,

ああ、疲れた。
では、最後の集計方法のところを作ります。
「”」を続けて入力すると、「[Measures]」というのが出てきます。これがメジャーと呼ばれるもので、まあ、計算方法だと思って差し支えないでしょう。これを選択します。

最後に何をメジャーするかというのを書いていきます。
これはピボットテーブルの集計値の項目名でいいのではないかと思います。

「合計 / 契約金額」ですね。これは数式バーとかで正確にコピーした方が安全です。
[]で囲んで、最終的に次のような計算式になります。

=CUBEVALUE(“ThisWorkbookDataModel”,”[“&B$1&”]”,”[“&B$2&”]”,”[“&$A3&”]”,”[“&$A$2&”]”,”[Measures].[合計 / 契約金額]”)

ここまで、実は入力すると何か表示されてくるのでそれをガイドに入力すれば簡単なのかなと思います。あと、アイテム名は[]で囲むことを忘れなければ。

で、これを表全体にコピーすれば求まります。

すばらしいですね。求まっています。

あれ？北海道がエラー。

キューブ関数の元データで注意しなきゃいけないこと

今回、北海道のセル参照って、何が北海道って指定してないじゃないですか。
ここ、落とし穴なんです。
実は北海道って、支店名と顧客都道府名の両方にあるんです。
だからExcelはどっちの北海道を指しているかわからないので混乱しちゃったみたいなんです。
うまくどっちか選ぶ時もあるんですけど、その時もそっちじゃないほうを選んでくれちゃったりしています。
ということで、支店名には～支店という風に全部変換します。
フラッシュフィルで一発変換して切り取って貼り付けました。

集計表の方も同じく支店名に支店をつけます。これでうまくいくぞう！！

うまくいきませんでした。
これ、もう一つのキューブ関数の嫌なところなんですけど、元データ替えたらピボットテーブルから一式更新しなければならないのです。
データタブの中のすべて更新で更新しちゃいます。

こんどこそうまくいきました。おおむね成功です☆

あとは支店名を入れ替えてデータを作っていく感じになると思います。
ってここまで苦労したものって、実はピボットテーブルでも無理すれば作れるんじゃない？元データ変えたら更新しなきゃいけないのだからピボットテーブルと同じじゃん。
SUMIFS関数でもできちゃうし。
全くもってその通りです。

キューブ関数の存在意義

じゃ、キューブ関数って使い道ないんじゃないの？
と思ってしまいますが、実はキューブ関数でしかできないこともあるのです。
SUMIFS関数とかCOUNTIFS関数って基本関数をIFで多数の条件分けで使えるじゃないですか。
今のところできるのは、合計、個数、平均、最大、最小ですよね。
他の集計はできないです。
よくアンケートを取る時には、統計処理をします。そこで使う関数として、標準偏差や分散がありますが、それらは条件で振り分ける関数はありません。
そこで、登場するのがピボットテーブルの集計方法。
ピボットテーブルでは、集計方法を右クリックすることで変更することができるのです。この、その他のオプションの中では標準偏差や分散を求めることができます。

ならこの中の分散はCUBEVALUE関数でも使えてほしいわけです。

ということで、計算式を「分散」に変更してみましょう。

=CUBEVALUE(“ThisWorkbookDataModel”,”[“&B$1&”]”,”[“&B$2&”]”,”[“&$A3&”]”,”[“&$A$2&”]”,”[Measures].[分散 / 契約金額]”)

エラーになってしまいました。

実は、ピボットテーブルで分散を実際に求めないと反応しません。
ということでピボットテーブルの値の集計方法を分散にしてみます。

求まりましたね。

ということで、全部にコピーします。

うまくいきました。
でもここで、ピボットテーブルの集計を合計に戻したらどうなっちゃうのでしょう。
実は戻しても大丈夫で、更新してないから大丈夫なんじゃないのと思って更新してみても大丈夫でした。
どうやら一回でもピボットテーブルで集計した方法であれば、あとは変更しても大丈夫みたいです。
ということで、はじめに考えられるだけの総集計をピボットテーブルで求めて、それをベースにキューブ関数でいろいろな集計表を作るとかしてもいいのかなと思います。
そして、結局は更新とかの手間はあるけども、ピボットテーブルでそう集計さえ求めていれば、ピボットテーブルの答えを使って別に集計表を作ることもできるし、それを元にIF関数で分岐もできたりします。
そういう使い方はキューブ関数じゃないとできないのです。

PowerQuery？クエリデータ？SQLサーバー？

ここからは全くの虚言なのですが、そう考えた方が理解しやすいかなと思って言うのですが。
ここまででキューブ関数を使う上で、必須だと言われている、PowerQueryだとか、データベースサーバーだとか、SQLだとかって話、出てないですよね。
実際になんですが、キューブ関数はピボットテーブルをブックにデータモデルとして追加するだけで使えちゃうんです。
本当はサーバーやらSQLサーバーやらを用意して、データウェアハウス的なものを元に使えばまた違った使い方ができるのかもしれませんが。
一つだけ思ったのは、ピボットテーブルの元データ範囲って行数増やしたり減ったりした時って、元データを絶対に設定しなおししなきゃいけなくて、それをしないために元データをテーブルとして設定して、それをPowerQueryで取り込めば、いくらデータの増減があっても、更新すれば一発で反映できるじゃないですか。
だからキューブ関数の元データがPowerQueryって言ってるのかなとか思っています。

追記

支店の北海道を確実に指定するには、[北海道]だけではなくて、[支店].[北海道]と指定すればいいようです。