ExcelのCUBEVALUE関数の使い方｜MDX式でPower Pivotから集計値を取得する

スポンサーリンク

「ピボットテーブルでは表示位置や集計順が決め打ちになってしまう」「Power Pivot のメジャーをセル数式で自由に呼び出したい」――そんな高度な集計をピンポイントで実現するのが CUBEVALUE関数 です。

CUBEVALUE関数は、Power Pivot のデータモデルや OLAP キューブに接続し、MDX（多次元式）で指定した条件の集計値を 1 セルに返す関数です。ピボットテーブルの形式に縛られないダッシュボードやレポートを組み立てたいときに、中核となります。

この記事では、CUBEVALUE関数の構文、MDX 式の読み方、Power Pivot との連携手順、CUBE系 7 関数の使い分けを解説します。実務での活用例 5 選、よくあるエラーの直し方まで、現場で必要な情報を一通りまとめます。

CUBEVALUE関数とは？

読み方と語源

「キューブ バリュー」と読みます。関数名は CUBE（キューブ＝多次元データ）＋ VALUE（値）の組み合わせで、「多次元データから値を 1 つ取り出す」という意味です。

CUBE系関数は Excel 2007 で追加された 7 関数のひとつです（CUBEVALUE、CUBEMEMBER、CUBESET、CUBESETCOUNT、CUBERANKEDMEMBER、CUBEMEMBERPROPERTY、CUBEKPIMEMBER）。SQL Server Analysis Services（SSAS）や Power Pivot などの「多次元データソース」から数値・属性・メンバーを取り出すために設計されています。

できること

CUBEVALUE関数の役割は、ひとことで言えば「メジャー（集計値）の取得」です。たとえば次のような使い方ができます。

  • Power Pivot モデルで定義したメジャー（例：合計売上、平均単価、伸び率）を任意のセルに表示する
  • ピボットテーブルの形式に縛られない、見出しが自由なレポートを組み立てる
  • 「年 × 製品 × 地域」のように複数の次元を組み合わせた集計値を 1 セルで動的に取得する
  • スライサーやセルの値と連動させ、ユーザーが選んだ条件の集計値をリアルタイムに表示する

ピボットテーブルが「表全体を一気に表示する」道具だとすれば、CUBEVALUE関数は「1 セルずつ狙い撃ちで値を取り出す」道具です。レポートやダッシュボードの自由度がぐっと上がります。

対応バージョン

Excel 2007 以降で利用できます。データモデル（Power Pivot）と組み合わせる場合は Excel 2010 以降が必要です。Microsoft 365・Excel 2019・Excel 2021 はすべて対応しています。Excel for Mac でも Microsoft 365 版で利用できますが、Power Pivot の編集機能は Windows 版が中心です。

CUBEVALUE関数の書き方

基本構文

=CUBEVALUE( 接続, [メンバー式1], [メンバー式2], ... )

引数の説明

引数必須/任意説明
接続必須キューブやデータモデルへの接続名を文字列で指定します
メンバー式1 〜任意取得する値の条件を表す MDX 式またはセル参照を指定します（複数指定可）

メンバー式を複数指定すると、その「交点」にあたる集計値が返されます。たとえば「売上金額」「2024年」「飲料カテゴリ」の 3 つを指定すれば、2024年の飲料カテゴリの売上金額が返ります。メンバー式を 1 つも指定しない場合は、キューブ全体の集計値（メジャーの総合計）が返ります。

第 1 引数の「接続名」には、Power Pivot のデータモデルなら "ThisWorkbookDataModel" を指定します。外部の SSAS キューブなら、接続マネージャーで作成した接続名を使います。文字列なのでダブルクォーテーション（"）で囲むのを忘れないでください。

MDX式の基本

CUBEVALUE関数を使いこなす鍵は、第 2 引数以降の MDX 式 の書き方です。MDX（Multidimensional Expressions）は SQL の多次元データ版にあたる問い合わせ言語です。最初は構造に慣れる必要がありますが、ルールは数えるほどしかありません。

階層とメンバーの書き方

MDX 式の基本形は、角かっこ [] で要素を区切る次のパターンです。

[ディメンション名].[階層名].[メンバー名]

それぞれの要素は次のように対応します。

  • ディメンション：テーブルやデータの大きなくくり（例：時間、製品、地域）
  • 階層：ディメンションの中の切り口（例：年・月・日、カテゴリ・商品名）
  • メンバー：階層の具体的な値（例：2024、飲料、東京）

具体例で見るとイメージしやすくなります。

[時間].[年].[2024]            → 2024年というメンバー
[製品].[カテゴリ].[飲料]       → 製品カテゴリ「飲料」というメンバー
[Measures].[売上金額]          → 「売上金額」というメジャー

メジャー（合計値や平均値などの集計列）は、ディメンション名が [Measures] 固定です。Power Pivot で作成したメジャー名はすべて [Measures] の下にぶら下がる、と覚えておくと迷いません。

& 付きと & なしの違い

Power Pivot のメンバー式では、メンバー名の前に & を付けるかどうかで挙動が変わります。

[製品].[カテゴリ].[電化製品]      → 「電化製品」という名前で検索
[製品].[カテゴリ].&[電化製品]     → 「電化製品」というキー値で直接参照

& 付き（キー参照）のほうが高速で、同じ名前のメンバーが複数あるときも誤参照しません。Power Pivot から自動生成された MDX 式は & 付きで書かれるのが普通です。手書きする場合も、原則として & 付きを使うのがおすすめです。

基本的な使い方

例1：売上金額の合計を取得する

Power Pivot モデルに「Sales」という接続があり、2024 年の飲料カテゴリの売上合計を取得する場合は次のように書きます。

=CUBEVALUE("Sales", "[Measures].[売上金額]", "[時間].[年].[2024]", "[製品].[カテゴリ].[飲料]")

3 つのメンバー式（売上金額 × 2024 年 × 飲料）の交点値が返ります。引数の順番に決まりはありませんが、「メジャーを最初、絞り込み条件を後ろ」と並べると読みやすくなります。

例2：Excel データモデル（Power Pivot）の場合

Excel ブック内のデータモデルへの接続名は "ThisWorkbookDataModel" 固定です。

=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[合計 / 売上]", "[製品テーブル].[カテゴリ].&[電化製品]")

Power Pivot で作成したメジャー名はそのまま [Measures].[メジャー名] で参照できます。スペースや日本語が入っていても、角かっこで囲まれていれば問題ありません。

例3：セル参照で動的に切り替える

メンバー式を直接文字列で書くのではなく、セルに入れた値で切り替えると、簡易ダッシュボードが作れます。

B1: [時間].[年].[2024]
B2: [製品].[カテゴリ].[飲料]
B3: =CUBEVALUE("ThisWorkbookDataModel", "[Measures].[売上金額]", B1, B2)

B1・B2 のセルをドロップダウンリストにしておけば、ユーザーが選んだ条件の集計値がリアルタイムに B3 に表示されます。Excel のリストデータと組み合わせると、コードを書かずにインタラクティブなレポートが作れます。

例4：メジャーの合計を一気に取り出す

メンバー式を 1 つも指定しないと、メジャーの総合計（フィルタなし）が返ります。

=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[売上金額]")

ダッシュボードの右上に「全社累計売上」を表示するときなど、シンプルですが使い勝手の良いパターンです。

例5：複数のメジャーを横並びで取得する

行に時期、列にメジャー（売上・利益・利益率）を並べた小さな KPI テーブルを CUBEVALUE関数だけで作れます。

B1: [時間].[年].[2024]
C2: =CUBEVALUE("ThisWorkbookDataModel", "[Measures].[売上金額]", $B$1)
D2: =CUBEVALUE("ThisWorkbookDataModel", "[Measures].[利益]",   $B$1)
E2: =CUBEVALUE("ThisWorkbookDataModel", "[Measures].[利益率]", $B$1)

B1 を変えるだけで C2:E2 の全数値が連動します。フィルタが効いたピボットテーブルを毎回作り直すよりも、ずっと軽快に運用できます。

CUBEMEMBER関数との組み合わせ

CUBEVALUE関数は CUBEMEMBER関数と組み合わせると、レポートの自由度がさらに上がります。

CUBEMEMBER関数はメンバーオブジェクトを取り出す関数です。セルに表示される文字列はメンバーの表示名で、内部的にはメンバー参照として CUBEVALUE関数に渡せる、という二段構えで動きます。

A1: =CUBEMEMBER("ThisWorkbookDataModel", "[製品テーブル].[カテゴリ].&[電化製品]")
       → セルには「電化製品」と表示される
B1: =CUBEVALUE("ThisWorkbookDataModel", "[Measures].[合計 / 売上]", A1)
       → 「電化製品」カテゴリの売上合計が返る

A1 のメンバーを別のカテゴリに切り替えれば、B1 の集計値も自動的に追従します。ラベルと数値を別々に管理せず、片方を変えればもう片方も追従する、という保守しやすい構造になるのがメリットです。

CUBEMEMBER関数の詳細は CUBEMEMBER関数の使い方｜OLAPキューブからメンバーを取得する もあわせて参照してください。

CUBE系7関数の役割と使い分け

CUBEVALUE関数は CUBE系 7 関数の中でも最もよく使う関数ですが、他の 6 関数を組み合わせると表現力が一気に広がります。役割を整理しておきます。

関数返すもの主な用途
CUBEVALUE集計値（数値）メジャーの値を取得する中心的な関数
CUBEMEMBERメンバーオブジェクト次元メンバーを参照し、CUBEVALUE と組み合わせる
CUBESETセット（メンバーの集合）条件を満たすメンバーの集合を定義する
CUBESETCOUNTセットの件数CUBESET で定義したセットの要素数を返す
CUBERANKEDMEMBERランク順のメンバーセット内の N 番目のメンバーを取得する（売上 Top10 など）
CUBEMEMBERPROPERTYメンバーのプロパティメンバーの属性情報（住所・カテゴリなど）を取得する
CUBEKPIMEMBERKPI のプロパティKPI 名・目標値・ステータスなどを取得する

使い分けの基本は、次のように整理できます。

  • 集計値（数値）が欲しい → CUBEVALUE
  • どのメンバーを参照するか宣言する → CUBEMEMBER
  • 条件に合うメンバーを集合として扱う → CUBESET + CUBESETCOUNT
  • 売上 Top10 のような順位付きリストが欲しい → CUBESET + CUBERANKEDMEMBER
  • メンバーの属性情報を一緒に表示したい → CUBEMEMBERPROPERTY
  • KPI の目標値や達成ステータスを取り出したい → CUBEKPIMEMBER

各関数の詳細は次の記事を参照してください。

Power Pivotとの連携手順

CUBEVALUE関数を使う前提として、Power Pivot のデータモデルが必要です。連携の流れを順番にまとめます。

手順1：データをデータモデルに読み込む

データタブの「データの取得」または「テーブル/範囲から」でクエリを作成します。「読み込み先」→「接続のみ作成」「データモデルに追加」にチェックを入れると、テーブルがデータモデルに登録されます。

手順2：Power Pivotでメジャーを定義する

データタブの「データモデルの管理」から Power Pivot ウィンドウを開き、計算領域で DAX 式を使ってメジャーを定義します。

合計売上 := SUM('売上'[金額])
平均単価 := AVERAGE('売上'[単価])
前年比   := DIVIDE([合計売上], CALCULATE([合計売上], DATEADD('カレンダー'[日付], -1, YEAR)))

メジャー名は CUBEVALUE関数の [Measures].[メジャー名] でそのまま参照できます。

手順3：CUBEVALUE関数で参照する

定義したメジャーを CUBEVALUE関数で取り出します。

=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[合計売上]")

絞り込みが必要なら、メンバー式を追加するだけです。

=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[合計売上]", "[製品].[カテゴリ].&[飲料]")

ピボットテーブルからの変換も便利

メンバー式を 1 から手書きするのが大変な場合は、いったんピボットテーブルを作ってから「OLAP ツール」→「数式に変換」を実行します。すると、ピボットテーブルが CUBEVALUE関数と CUBEMEMBER関数の組み合わせに展開されます。

複雑な MDX 式を覚える前に、まずはこの「数式に変換」で生成されたコードを読み解くと、MDX 式の書き方が自然に身につきます。

実務での活用例

活用例1：経営ダッシュボードの主要 KPI 表示

経営会議用の KPI ダッシュボード（売上・粗利・在庫回転率など）を 1 枚のシートにまとめるとき、各 KPI は CUBEVALUE関数で取得します。

売上    : =CUBEVALUE("ThisWorkbookDataModel", "[Measures].[当月売上]")
粗利    : =CUBEVALUE("ThisWorkbookDataModel", "[Measures].[当月粗利]")
粗利率  : =CUBEVALUE("ThisWorkbookDataModel", "[Measures].[当月粗利率]")
在庫回転: =CUBEVALUE("ThisWorkbookDataModel", "[Measures].[在庫回転率]")

ピボットテーブルだと「表の中の値」になりがちですが、CUBEVALUE関数で個別セルに展開すれば、フォント・色・配置を自由に設計できます。

活用例2：部門別・地域別のクロス集計レポート

行に部門、列に地域を並べた集計表を CUBEVALUE関数で作成します。

B2: =CUBEVALUE("ThisWorkbookDataModel", "[Measures].[売上]", "[部門].[名称].&[" & $A2 & "]", "[地域].[名称].&[" & B$1 & "]")

A 列に部門名、1 行目に地域名を入れておけば、& 演算子で文字列を結合してメンバー式を組み立てられます。フィルタを掛け替えても見出しがズレない、堅牢なレポートになります。

活用例3：前年同月比・伸び率の表示

Power Pivot のメジャーで「前年同月比」を定義しておけば、CUBEVALUE関数で 1 セルに取り出せます。

=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[前年同月比]", "[時間].[年月].&[2024-04]")

メジャー側で DAX 式を組んでおくので、各セルの数式はシンプルなまま、複雑な計算が再利用できます。

活用例4：スライサーと連動した動的レポート

ピボットテーブルにスライサーを設定し、同じデータモデルを参照する CUBEVALUE関数を別エリアに置きます。するとスライサーの選択が、CUBEVALUE関数の集計値にも反映されます。

スライサーで「2024年・関東・飲料」を選ぶと、別エリアの CUBEVALUE関数の値も自動的に同じ条件の数値に切り替わります。コードを書かずに対話的なレポートが作れます。

活用例5：エラーを IFERROR で吸収して整える

CUBEVALUE関数はメンバーが存在しないと #N/A を返します。ユーザー向けレポートでは IFERROR でラップして「-」や 0 に置き換えると、見栄えが整います。

=IFERROR(CUBEVALUE("ThisWorkbookDataModel", "[Measures].[売上]", "[時間].[年月].&[2024-04]"), "-")

特に時系列レポートで、まだデータがない月に #N/A が並ぶと読みにくくなるため、IFERROR で吸収するのは定石です。

ピボットテーブルとの違い・使い分け

CUBEVALUE関数とピボットテーブルは、同じデータモデルを参照しても用途が異なります。

観点ピボットテーブルCUBEVALUE関数
レイアウト表形式に固定セル単位で自由配置
数式隠蔽されて見えないセルに数式として残る
動的更新フィルタ・スライサーセル参照・スライサー
学習コスト低いMDX 式の知識が必要
向く用途探索的なデータ分析完成形のダッシュボード

「データを眺めて気付きを得たい」段階ではピボットテーブルが向いています。「レイアウトを固定して KPI を提示したい」段階では CUBEVALUE関数、という使い分けが現場では一般的です。

よくあるエラーと対処法

エラー原因対処法
#NAME?接続名が存在しない、関数名のスペルミス接続マネージャーで接続名を確認する。ThisWorkbookDataModel のスペルにも注意
#N/A指定したメンバーがキューブ内に存在しないMDX 式のメンバー名を Power Pivot のテーブルと照合する
#VALUE!MDX 式の構文が正しくない[ディメンション].[階層].[メンバー] の形式を確認する
#GETTING_DATAデータ取得中のまま固まる接続が応答しているか、キューブが大きすぎないかを確認する
空白セル該当する集計値が存在しないフィルタ条件が厳しすぎないか、メジャーが空でないかを確認する

特に多いのは、メンバー名のスペルミスや & の付け忘れによる #N/A です。Power Pivot 側で右クリック→「列のコピー」をして、メンバー名を貼り付けると入力ミスを減らせます。

セルが #GETTING_DATA のまま固まる場合は、データソースへの接続が応答していない可能性があります。接続のテストと、キューブのサイズを見直してみてください。

まとめ

CUBEVALUE関数のポイントをまとめます。

  • Power Pivot や OLAP キューブから、MDX 式で指定した条件の集計値を 1 セルに取得できる
  • 引数は「接続名」と「メンバー式（複数指定可）」の 2 種類で、複数指定すれば交点の値が返る
  • CUBEMEMBER関数と組み合わせると、ラベルと数値を連動させた保守しやすいレポートが作れる
  • CUBE系 7 関数の中で最も使用頻度が高く、ピボットテーブルでは作れない自由なダッシュボードの中核を担う
  • 複雑な MDX 式は、ピボットテーブルから「数式に変換」で生成すると入門しやすい

まずは「ThisWorkbookDataModel」への接続で、メジャー 1 つを CUBEVALUE関数で取り出すところから始めてみてください。Power Pivot のメジャーがそのままセル数式から呼べる感覚がつかめれば、自由なレポート設計の世界が一気に広がります。

関連記事

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