はい、こんにちは。

Excelの魔法使い、佐藤嘉浩です。

EXCEL BEERさんでは2回目、久々の回答です。

キューブ関数は、MOS Excel のExpertでも出題範囲なので、試験対策した人はおそらく「なんだこれ」となっている機能の一つかもしれません。

そこで、キューブ関数は何に使うのか、どんな風に使うのかを整理してみましょう。

キューブ関数の目的

キューブ関数は多くの項目がある集計をするのに使うと考えています。

以下のような表ですね。

もっと世の中には多くの項目を使った表、例えばアンケートとかなら３０項目当たり前だったりもします。

そういった表で集計する時、例えば、天気ごとに商品ごとの契約金額の平均を求めるならばピボットテーブルで十分集計できます。関数でならSUMIFS関数などが使えるでしょう。

しかし、曜日ごとに天気ごとに平均気温を5度おきに契約担当年齢を10歳おきに契約担当性別ごとに顧客都道府県ごとの商品ごとの契約金額の平均を求めたいなんていうときに、Excelでは集計しにくくなります。

理由は、Excelは縦と横でしか項目を割り振れないからです。これはExcelが持っている一番使いにくい部分といえるかもしれません。

そこで、縦と横の２項目だけじゃなくて、全体的にどのアイテムでも何個でも集計できるようにしたのがキューブという考え方です。

キューブって説明文だとルービックキューブみたいな考え方で図示されてますけど、私は「多面体ルービックキューブ」みたいなイメージでいます。ググると画像が出てくるのでこんなイメージと捉えていただければ。

でも、Excelである以上、表現したものは縦横の呪縛からは離れられないんですけどね。

キューブ関数の準備

まず、SQLServerやPowerQueryが必要という情報もありますが、実際に操作するうえで必要ありません。衝撃的な事実ですが。

何が必要かというと、元データをピボットテーブルで処理することです。

上記の表なら契約金額を集計するのですが、契約金額を値に入れて総合計だけ求めてください。

その時に一つやらなければならないことがあります。「ピボットテーブルをデータモデルに追加する」という作業です。

難しいことはありません。このように、ピボットテーブルを作成する状態で、「このデータをデータモデルに追加する」のチェックを入れて、あとは普通にピボットテーブルを作るだけです。

すると、キューブ関数で使える「ThisWorkbookDataModel」という形でピボットテーブルが登録されます。

ピボットテーブルを元にしないキューブ関数の使い方もあるのでしょうけど、我々が使うのはこのやり方で十分なのではないかなと思っています。

キューブ関数の作成

実はキューブ関数も値を集計するCUBEVALUE関数しか把握はしていないので、CUBEVALUE関数で説明します。

CUBEVALUE関数の書式はマニュアルに従わないでものすごくわかりやすく書くと次の通りです。

=CUBEVALUE(データモデル,[集計アイテム1],[集計アイテム2],[集計アイテム3],.....,[Measures].[ピボットテーブル上の集計項目名])

です。実際の計算式は次のようになります。男で宮城で商品1の契約金額の合計を求めます。

=CUBEVALUE("ThisWorkbookDataModel",[男],[宮城],[商品1],[Measures].[合計 / 契約金額])

「"ThisWorkbookDataModel"」はお決まりで、ピボットテーブルを登録した裏テーブルみたいなものを指しているのでしょう。

性別が男で都道府県が宮城で商品名が商品1という指定をしています。が、どこに「性別」だの指定しているかってことなんですが、指定しなくてもアイテム名のどこかが男のところを見つけてきて集計します。でもそれじゃ困りますよね。例えば上の表でも支店名と都道府県に同じ北海道が入っているので混乱しているとExcelも思います。
あ、今思いついたので検証はしていないですが、もしかしたら、[北海道]だけではなくて[支店].[北海道]と指定すればいいのかもしれないです。

で、このCUBEVALUE関数ですが、入力する時に結構楽な入力ができるみたいなんです。それに従うと面白いように簡単に計算式が作れます。

「=CUBEVALUE("」ここまで入力すると、「ThisWorkbookDataModel」が表示されるのでそのまま選択します。

そして「,」で区切りながら、集計したいアイテム名を[]で囲んで記述していきます。

最後のアイテム名を入力したら、「,」を入れると、「Measures」が出てくるのでそれを選択、「.[]」と入力して、ここでいったん確定。

作成したピボットテーブルに行って、集計項目名を名前ボックス上でコピーしてきます。

そして、CUBEVALUE関数の計算式を開いて[]の間に貼り付ければ完成です。

キューブ関数って結局・・・

さて、ここまでできたところでキューブ関数ってなんだったんだろうと考えてみたんですけど。

とてつもない大きな一覧表から1つの要素についての集計値を取り出す関数。

ということなんでしょうか。

本来、先述の「曜日ごとに天気ごとに平均気温を5度おきに契約担当年齢を10歳おきに契約担当性別ごとに顧客都道府県ごとの商品ごとの契約金額の平均を求めたい」という集計一覧表を作りたいということではなくて、あくまでターゲットの1項目について集計したいときに使う、そんな感じがしています。

せんでん

このご質問をうけまして、以下URLにキューブ関数のめちゃくちゃですけどわかりやすい記事を書きました。

よければご覧ください。

https://www.yosato.net/?p=1610

少し古いですが、分析システムの全体像を使うために良い記事見つけましたよ！

ビジネスインテリジェンスとは何か
http://www.atmarkit.co.jp/ait/articles/0612/09/news010.html

多次元分析の概要とデータ分析のポイント
http://www.atmarkit.co.jp/ait/articles/0701/13/news024.html

Analysis Servicesを使って多次元分析に挑戦しよう
http://www.atmarkit.co.jp/ait/articles/0702/02/news110.html

Reporting Servicesを使ったレポート作成の技
http://www.atmarkit.co.jp/ait/articles/0703/01/news138.html

分析ポータル作成とETLツールの実践活用法
http://www.atmarkit.co.jp/ait/articles/0704/05/news120.html

参考になればと思います！


質問投稿とは関係ないですが、このサイトデザインが可愛いし、サイトコンセプトが共感できますね！運営者様お疲れ様です！