ExcelのCUBESET関数の使い方｜データモデルからセットを取得する

スポンサーリンク

ExcelのCUBESET関数、聞き慣れない名前ですよね。CUBE関数シリーズの中でも少し上級者向けの関数ですが、使い方を覚えると「複数のメンバーをまとめて扱う」ことができるようになります。

たとえば「東京・大阪・名古屋の売上をまとめて集計したい」「売上Top5の商品をリストにしたい」といった場面で活躍します。CUBESET関数でメンバーの集合を作り、他のCUBE関数と組み合わせるのが基本パターンです。

この記事では、CUBESET関数の構文から実践的な使い方まで、順を追って解説していきます。

ExcelのCUBESET関数とは？

CUBESET関数は、ExcelのCUBE関数シリーズのひとつです。Power Pivot（パワーピボット）のデータモデルやOLAP（オーラップ：多次元分析用のデータベース）キューブから、メンバーのセット（集合）を取得します。

CUBEMEMBER関数が「1つのメンバー」を取り出す関数なのに対して、CUBESET関数は「複数のメンバーをまとめたグループ」を作る関数です。

CUBESET関数の読み方

読み方は 「キューブセット」 です。

  • CUBE = 立方体（多次元データの構造を指す用語）
  • SET = セット（集合・グループ）

「多次元データからメンバーの集合を取り出す関数」という意味ですね。

CUBESET関数でできること

CUBESET関数を使うと、次のようなことができます。

  • 複数のメンバーをひとつの集合にまとめる
  • MDX式でフィルタ条件を指定してメンバーを絞り込む
  • 取得したセットを並べ替える（昇順・降順）
  • CUBERANKEDMEMBER関数と組み合わせて、セット内のN番目を取り出す
  • CUBESETCOUNT関数と組み合わせて、セット内の件数を数える

単体で使うと、セルにはキャプション（表示名）だけが表示されます。CUBESET関数の真価は、他のCUBE関数と組み合わせたときに発揮されますよ。

使う前に確認！Power Pivotのデータモデル接続

CUBESET関数を使うには、Power Pivotのデータモデルが必要です。まだデータモデルを作成していない方は、先に準備しておきましょう。

データモデルとは、Excel内部に作られるデータベースのようなものです。CUBE関数はこのデータモデルに対して問い合わせを行います。

CUBESET関数の第1引数には接続名を指定しますが、Power Pivotのデータモデルを使う場合は 「ThisWorkbookDataModel」 と書きます。これは「このブックのデータモデル」という意味で、Excelが自動的に付ける固定の名前です。

データモデルの作り方や接続名の詳細は、CUBEMEMBER関数の解説記事で紹介しています。まだ馴染みがない方は先にそちらを確認してみてください。

なお、Power Pivotが使えるExcelのエディションは以下のとおりです。

  • Microsoft 365（デスクトップ版すべて）
  • Excel 2024 / 2021 / 2019（Professional / Home & Business / Home & Student）
  • Excel 2016（Professional / Professional Plus）

CUBESET関数の書き方（構文と引数）

CUBESET関数の構文を見てみましょう。引数は5つありますが、必須は2つだけです。

=CUBESET(接続, セット式, [キャプション], [並べ替え順序], [並べ替えキー])

各引数の役割を表で確認しましょう。

引数必須/省略可説明
接続必須データモデルやキューブの接続名
セット式必須取得するセットを定義するMDX式
キャプション省略可セルに表示する文字列
並べ替え順序省略可セットの並べ替え方法（0〜6の整数）
並べ替えキー省略可並べ替えの基準となる値のMDX式

引数1 接続

「接続」には、接続先のデータモデルやキューブの名前を文字列で指定します。

Power Pivotのデータモデルを使う場合は次のように書きます。

"ThisWorkbookDataModel"

接続名を間違えると #NAME? エラーになるので、コピーして貼り付けるのが確実です。

引数2 セット式（MDX式）

「セット式」には、取得したいメンバーの集合をMDX式（エムディーエックス式：多次元データを問い合わせるための記述方法）で指定します。

よく使うパターンを紹介します。

パターン1：特定の階層のすべてのメンバーを取得

"[売上データ].[商品カテゴリ].Children"

.Children を付けると、その階層に属するすべてのメンバー（飲料、食品、日用品など）をセットとして取得できます。

パターン2：特定のメンバーを列挙してセットを作る

"{[売上データ].[地域].[東京], [売上データ].[地域].[大阪]}"

波括弧 {} の中にメンバーをカンマ区切りで並べると、指定したメンバーだけのセットが作れます。

パターン3：セル参照で動的にセットを作る

=CUBESET("ThisWorkbookDataModel", {A1, A2, A3})

CUBEMEMBER関数が入ったセルを参照して、動的なセットを作ることもできます。

引数3 キャプション（省略可）

「キャプション」には、セルに表示する文字列を指定できます。

CUBESET関数はセット（集合）を返す関数なので、キャプションを省略するとセルには #N/A と表示されることがあります。レポートの見栄えを整えるために、わかりやすいキャプションを付けておくのがおすすめです。

=CUBESET("ThisWorkbookDataModel", "[売上データ].[商品カテゴリ].Children", "全商品カテゴリ")

この場合、セルには「全商品カテゴリ」と表示されます。

引数4 並べ替え順序（省略可）

「並べ替え順序」には、セット内のメンバーをどう並べ替えるかを0〜6の整数で指定します。

整数値説明並べ替えキー
0既存の順序を維持（既定値）不要
1並べ替えキーで昇順必須
2並べ替えキーで降順必須
3アルファベット昇順不要
4アルファベット降順不要
5元の昇順不要
6元の降順不要

並べ替え順序を1または2に指定した場合は、次の「並べ替えキー」の指定が必須です。忘れるとエラーになるので注意しましょう。

引数5 並べ替えキー（省略可）

「並べ替えキー」には、並べ替えの基準となる値をMDX式で指定します。

たとえば「売上金額の合計」で降順に並べ替えたい場合は次のように書きます。

=CUBESET("ThisWorkbookDataModel", "[売上データ].[商品カテゴリ].Children", "売上順", 2, "[Measures].[売上金額の合計]")

これで商品カテゴリが売上金額の大きい順に並んだセットが作れます。ランキングレポートを作るときに便利ですね。

CUBESET関数の使い方（実用例）

ここからは、実際の使い方を見ていきましょう。

基本的な使用例

まずはシンプルな例です。「売上データ」テーブルの「地域」列に属するすべてのメンバーのセットを取得します。

=CUBESET("ThisWorkbookDataModel", "[売上データ].[地域].Children", "全地域")

セルには「全地域」と表示されます。このセットには東京、大阪、名古屋などすべての地域メンバーが含まれています。

ただし、セルの表示はキャプションだけなので「本当にセットが作れているの？」と不安になりますよね。確認するには、CUBESETCOUNT関数を使います。

=CUBESETCOUNT(A1)

A1にCUBESET関数が入っていれば、セット内のメンバー数が表示されます。

CUBERANKEDMEMBERと組み合わせてリストを作る

CUBESET関数の最も実用的な使い方は、CUBERANKEDMEMBER関数との組み合わせです。

たとえば「売上金額が多い順に商品カテゴリをリスト表示する」場合、次のように書きます。

ステップ1：CUBESET関数でソート済みのセットを作る

セルA1に売上順のセットを作成します。

=CUBESET("ThisWorkbookDataModel", "[売上データ].[商品カテゴリ].Children", "売上順カテゴリ", 2, "[Measures].[売上金額の合計]")

ステップ2：CUBERANKEDMEMBER関数でN番目を取り出す

セルA2〜A4で、1位・2位・3位のメンバーを取り出します。

=CUBERANKEDMEMBER("ThisWorkbookDataModel", A1, 1)  → 売上1位の商品カテゴリ
=CUBERANKEDMEMBER("ThisWorkbookDataModel", A1, 2)  → 売上2位の商品カテゴリ
=CUBERANKEDMEMBER("ThisWorkbookDataModel", A1, 3)  → 売上3位の商品カテゴリ

ステップ3：CUBEVALUE関数で各カテゴリの売上金額を取得する

セルB2〜B4で、各メンバーの売上金額を集計します。

=CUBEVALUE("ThisWorkbookDataModel", A2, "[Measures].[売上金額の合計]")

この3つの関数を組み合わせるだけで、売上ランキングレポートが完成します。ピボットテーブルを使わなくても、数式だけで動的なランキング表が作れるのがCUBESET関数の強みですよ。

特定のメンバーだけを選んでセットを作る

「全メンバーではなく、特定のメンバーだけをまとめたい」という場面もあります。

=CUBESET("ThisWorkbookDataModel", "{[売上データ].[地域].[東京], [売上データ].[地域].[大阪], [売上データ].[地域].[名古屋]}", "主要3都市")

波括弧 {} の中にメンバーをカンマ区切りで列挙します。この数式で「東京・大阪・名古屋」だけを含むセットが作れます。

このセットをCUBEVALUE関数の引数に渡せば、3都市の合計売上を一発で取得できます。

=CUBEVALUE("ThisWorkbookDataModel", A1, "[Measures].[売上金額の合計]")

地域別レポートで「主要都市だけ」を集計したいときにとても便利です。

よくあるエラーと対処法

CUBESET関数で発生しやすいエラーと、その対処法をまとめました。

#NAME?エラー

#NAME? エラーは、接続名が正しく認識されない場合に発生します。

主な原因は次のとおりです。

  • 接続名のスペルが間違っている
  • 接続名をダブルクォーテーションで囲んでいない
  • データモデルがブック内に存在しない

接続名は「ThisWorkbookDataModel」をそのままコピーして貼り付けるのが確実です。

#N/Aエラー

#N/A エラーは、セット式が無効な場合に発生します。

主な原因は次のとおりです。

  • MDX式のテーブル名・列名のスペルが間違っている
  • .Children などのMDX関数の書き方が正しくない
  • 波括弧 {} やカンマの位置がずれている

MDX式は角括弧 [] やドット . の抜けが原因になることが多いので、ひとつずつ確認してみてくださいね。

#VALUE!エラー

#VALUE! エラーは、引数の型が正しくない場合に発生します。

  • 並べ替え順序に0〜6以外の数値を指定している
  • 数式バーの文字数が255文字を超えている

255文字制限に引っかかる場合は、セット式を短くするか、セル参照を使って分割しましょう。

#GETTING_DATAの表示

#GETTING_DATA はエラーではありません。データモデルからデータを取得している最中のステータス表示です。

通常は数秒で結果に切り替わります。長時間表示が続く場合は、「データ」タブの「すべて更新」を試してみてください。

他のCUBE関数との使い分け

CUBESET関数は、CUBE関数シリーズ7種類のうちのひとつです。それぞれの役割を確認しておきましょう。

関数名役割CUBESETとの関係
CUBEMEMBER個別メンバーを取得CUBESETはメンバーの「集合」版
CUBEVALUE集計値を取得CUBESETを条件にして複数メンバーの合計を取得
CUBESETメンバーの集合を取得本記事で解説
CUBERANKEDMEMBERセット内のN番目を取得CUBESETで作ったセットから順位指定で取り出す
CUBESETCOUNTセットの要素数を取得CUBESETのメンバー数を数える
CUBEMEMBERPROPERTYメンバーのプロパティを取得CUBEMEMBERのプロパティ情報を参照
CUBEKPIMEMBERKPI情報を取得目標値やステータスの表示に使用

CUBESET関数をよく使う組み合わせパターンは次の3つです。

  • CUBESET + CUBERANKEDMEMBER: セット内のN番目のメンバーを取り出す（ランキング表）
  • CUBESET + CUBESETCOUNT: セット内のメンバー数を確認する
  • CUBESET + CUBEVALUE: セット全体の集計値を取得する（複数メンバーの合計）

CUBEMEMBERが「1つ」を取り出す関数なら、CUBESETは「グループ」を扱う関数と覚えておきましょう。

まとめ

CUBESET関数の使い方を振り返っておきましょう。

  • CUBESET関数はデータモデルからメンバーのセット（集合）を取得する関数
  • 構文は =CUBESET(接続, セット式, [キャプション], [並べ替え順序], [並べ替えキー]) の5引数
  • 接続名はPower Pivotなら「ThisWorkbookDataModel」を指定
  • セット式は .Children や波括弧 {} でメンバーの集合を定義する
  • 並べ替え順序を1または2にする場合は、並べ替えキーの指定が必須
  • CUBERANKEDMEMBER関数と組み合わせてランキング表を作るのが実践的な使い方
  • #NAME? は接続名の誤字、#N/A はMDX式の不備、#VALUE! は引数の型エラーが主な原因
  • #GETTING_DATA はエラーではなくデータ取得中の表示
  • 対応バージョン: Excel 365 / Excel 2024 / Excel 2021 / Excel 2019 / Excel 2016

CUBE関数シリーズの中でも、CUBESET関数は他の関数と組み合わせて使うことで本領を発揮します。まずは .Children で全メンバーのセットを作り、CUBERANKEDMEMBERで1つずつ取り出すパターンから試してみてください。

スポンサーリンク
タイトルとURLをコピーしました