キューブ関数を使ってデータ分析を効果的に行う方法 回答する


#Excel

キューブ関数の使い方がいまいち理解できません。色々なウェブサイトで調べた結果、ルービックキューブのクルクルとブロックを回すかのようにディメンションを変えることにより、違った角度からのデータ分析が可能なのだろうと推測していますが、どうやれば使えるのか分かりません。

キューブ関数の種類は下記の通り7種類存在することは分かりました。


(マイクロソフトから参照)

関数名 引数 説明
CUBEKPIMEMBER (接続, KPI 名, KPI のプロパティ, [キャプション]) 主要業績評価指標 (KPI) のプロパティを返し、KPI 名をセルに表示します。KPI は、月間粗利益や四半期従業員退職率など、定量化が可能な測定値であり、組織の業績をモニタリングするために使用されます。
CUBEMEMBER (接続, メンバー式, [キャプション]) キューブのメンバーまたは組を返します。キューブ内にメンバーまたは組が存在することを確認するために使用します。
CUBEMEMBERPROPERTY (接続, メンバー式, プロパティ) キューブ内のメンバー プロパティの値を返します。メンバー名がキューブ内に存在することを確認し、このメンバーの特定のプロパティを取得するために使用します。
CUBERANKEDMEMBER (接続, セット式, ランク, [キャプション]) セット内の n 番目の (ランクされている) メンバーを返します。売り上げトップの販売員、成績上位 10 位までの生徒など、セット内の 1 つ以上の要素を取得するために使用します。
CUBESET (接続, セット式, [キャプション], [並べ替え順序], [並べ替えキー]) セット式をサーバー上のキューブに送信して、計算されたメンバーまたは組のセットを定義します。サーバー上のキューブによってセットが作成され、Microsoft Excel に返されます。
CUBESETCOUNT (セット) セット内のアイテムの数を返します。
CUBEVALUE (接続, [メンバー式 1], [メンバー式 2], …) キューブの集計値を返します。


問題は、これをどのように扱えば、データ分析が効果的に行えるかが分かりません。SQL Serverに接続が必要とのことですが、その辺から私の知識レベルでは理解が難しいです。

エクセルビールのコミュニティの皆さんでご存知の方はいますか?よろしくお願いいたします!

bitcoin 2019.02.10 12:30:33 (2019.02.10 12:34:33 更新) 7353

コメントする

コメントするには ログイン していただく必要があります。

回答 2

はい、こんにちは。

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

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

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

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

キューブ関数の目的

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

以下のような表ですね。

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

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

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

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

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

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

でも、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

佐藤嘉浩(Excelの魔法使い・スピラー) 2019.02.11 11:14:00

実際に試してみたら、支店アイテム「北海道」は[北海道]って指定だけじゃなくて[支店].[北海道]って指定できるようです。
bitcoin 2019.03.25 20:16:03
めちゃくちゃ丁寧にありがとうございます。やっと理解できた気がします!

コメントする

コメントするには ログイン していただく必要があります。

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

ビジネスインテリジェンスとは何か
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

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


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

丸の内QUEEN 2019.02.11 09:29:37 (2019.02.11 09:30:31 更新)

コメントする

コメントするには ログイン していただく必要があります。

回答する
質問に回答するには ログイン していただく必要があります。
一覧に戻る