CUBESET関数・CUBESETCOUNT関数について解説いたします。
CUBE関数はあらかじめ集計されたデータを検索しやすくするためにExcelファイル内部にある「Excelデータモデル」や「PowerPivotデータモデル」を使いますが、
外部のデータベースに集計結果を集約し作成しておけばスタッフ数名でも瞬時にデータを取り出すことも出来ます。
このような集計のデータベースの事をキューブと言います。
また、キューブは閲覧するだけではなく、データを入力したり修正したりすることも出来ちゃいます。キューブは集計結果を格納するものですので基本的に数値データだけ保存ができます。
CUBESET関数
セット式をサーバー上のキューブに送信します。計算されたメンバーまたは組のセットを取得出来る関数になります。
キューブは特殊な構造を持っており、3次元以上で集計分析することが出来ます。Excelデータモデルは「商品」「期間」「支店」などの分析支店になる「ディメンション(軸)」と
「数量」「金額」などの分析対象となる「メジャー(集計値)」によって構成されます。
ディメンションの1つ1つの階層を「メンバー」といい、ディメンションと1つのメンバーの組み合わせを「組」と言います。
メンバーや組を合わせて指定する事で、「Excelデータモデル」内の特定の範囲の数量や金額等のメジャー(集計値)を取り出すことが出来ます。
「Excelデータモデル」から特定の範囲を絞り込む為に、メンバーや組を組み合わせて指定した式を「メンバー式」と言います。又、複数の「組」をまとめたものを「セット」と言います。
外部のサーバーなどの場合には、集計計算に時間がかかるんですが、膨大なデータだった場合、通常は夜間に自動的に集計を行うようにタイマーなどをかけておくんですね！
計算結果のキューブが作成されていると高速に集計結果が得られるという事になります。
書式は、=CUBESET(接続,セット式,表示名,並べ替え順序,並べ替えキー)になります。
()内の引数は
接続は、キューブへの接続名を表す文字列を表示します。
セット式は、メンバーまたは組のセットを表すセット式の文字列です。 セット内の1つ以上のメンバー、組、またはセットを含むExcel範囲へのセル参照を選択することもできます。
表示名は、省略可能ですが省略すると何も表示しません。名前の定義がされている場合は、キューブのキャプションではなくセルに表示される文字列が表示されます。
並べ替え順は、省略可能です。実行する並べ替えの種類になります (存在する場合)。1は昇順で2が降順になります。その他には下記のいずれかを選択できます。
並べ替えキーは、並べ替えの基準となる項目を選択します。
下のようなデータがあります。
どちらも、テーブルになっているのとテーブル名も付いていることが条件です。「Excelデータモデル」にする事とそのデータ利用してピボットテーブルの作成が必要になります。
新しいExcelを新規で立ち上げてデータのタブからデータの取得と変換のデータの取得からファイルで2個のテーブルをインポートします。
インポートする事によって「Excelデータモデル」と新規のExcelが認識してくれます。2つのテーブルをリレーションシップを使ってピボットテーブルの作成をします。
「Excelデータモデル」として作成したピボットテーブルがこちらになります。
これから、CUBESET関数を利用してセットの作成に入ります。上記の選手名のデータの中からHR数を降順に並べ替えるセットの作成を致します。
新しいシートを出して、セットと選手の人数と入力してください。
B1に、=CUBESET(“ThisWorkbookDataModel”,”[選手].[選手名].Children”,”HR首位”,2,”[Measures].[合計 / HR]”)と入力します。
Childrenと[合計 / HR]は手入力になります/の前後には半角のスペースが入ります。その他はデータモデルになっているので出てきます。
結果、「HR首位」と表示されセットが完成したことになります。
CUBESETCOUNT関数
カウント関数になるのですが、先ほど作成したCUBESET関数のセットを利用して、選手の人数をカウントします。
CUBESET内の項目の数を数えてくれる関数になるので、CUBESET関数が必要になります。
先程のデータを確認してください！
先程はB1のセルのCUBESET関数を使って選手名でのHR数を降順で並べ替えるセットを作成しましたね！
今度はB2のセルにB1のセルの項目数を数えます
書式は、=CUBESETCOUNT（セット）になります。
()内の引数は、先ほどCUBESET関数を使って導き出したセットのセルを使用します。
B2のセルに、=CUBESETCOUNT(B1)と入力します。結果は「25」と出ました。25人いるという事ですね。
CUBESET関数、CUBESETCOUNT関数のまとめ
CUBESET関数はExcelに接続した「データモデル」を指定した順序を並べ方物を返してくれて、
CUBESET関数を利用して、その項目の個数を出してくれるのがCUBESETCOUNT関数になります。
アンケートとかなら100個ものデータがあることが多いと思います。。
アンケートのデータを集計する時には、商品毎の天気別に売上金額の平均を求めたい時にはピボットテーブルで集計できちゃいます。
関数を使うのであればAVERAGEIFS関数等で集計は出来ると思います。
でも、曜日のに天気や平均気温を2度おきに担当者年齢を5歳おきに契約担当性別毎に顧客都道府県毎の商品毎の契約金額の平均を求めたいときに、AVERAGEIFS関数では集計できなくなっちゃいます。
Excelで出来なくなるのには、Excelは列と行でしか項目が見れないからなんです。
そこで、列と行の2項目だけじゃなくて、立体的にどの項目でもいくつでも計算出来る様にしたのがCUBE関数という事になります。
2次元の状態で考えるのではなく3次元の状態で考えてくれるという事になります。
難しい考え方の関数ですが、MicrosoftのExpert試験でも出題されていますので実際に触って見て下さいね！
