【Excel】CUBEKPIMEMBER関数の使い方｜KPI名・KPIプロパティを取得する

スポンサーリンク

Power Pivotで作ったKPIをExcelに表示したいけれど、ピボットテーブルではレイアウトが固定されて困っていませんか？

実はCUBEKPIMEMBER関数を使えば、KPI名・実績値・目標値・状態・傾向を関数だけで取り出せます。
CUBEVALUE関数と組み合わせれば、自分好みのレイアウトでKPIダッシュボードを組むことも可能です。

この記事ではCUBEKPIMEMBER関数の構文と4種類のKPIプロパティを解説します。
CUBEVALUEとの組み合わせ方や実務で使えるダッシュボード設計まで丁寧に紹介します。

CUBEKPIMEMBER関数とは？

CUBEKPIMEMBER関数は、KPI（重要業績指標）の名前やプロパティをセルに取り出す関数です。
OLAPキューブまたはExcelデータモデルに定義されたKPIを参照します。

KPIとは「目標に対してどれくらい達成しているか」を測るための指標です。
Power PivotやSQL Server Analysis Services（SSAS）で、あらかじめ定義しておきます。

CUBEKPIMEMBER関数を使うと、その定義済みKPIから次のような情報をセルに取り出せます。

  • KPIの名前（タイトル）
  • KPIの実績値
  • KPIの目標値
  • KPIの達成状態（良好・中立・悪化）
  • KPIの傾向（改善・横ばい・悪化）

NOTE

CUBEKPIMEMBER関数を使うには、ブックに「ExcelデータモデルでKPIが定義されている」または「OLAPキューブへの外部接続がある」のいずれかが必要です。何も接続していないブックでは使えません。

CUBEKPIMEMBER関数の構文と引数

CUBEKPIMEMBER関数の構文は次のとおりです。

=CUBEKPIMEMBER(connection, kpi_name, kpi_property, )

各引数の意味は次のとおりです。

引数必須内容
connection必須キューブまたはデータモデルへの接続名（文字列）。Excelデータモデルなら "ThisWorkbookDataModel"
kpi_name必須KPIの名前（文字列）。例: "[Sales].[KPI 1]"
kpi_property必須取り出すプロパティを表す1〜4の整数
caption省略可セルに表示する別名。省略するとKPI名+プロパティ名が表示される

kpi_propertyの値と意味

第3引数の kpi_property は1〜4の整数で、何を取り出すかを指定します。

値プロパティ名取り出す内容
1KPIValueKPIの実績値
2KPIGoalKPIの目標値
3KPIStatusKPIの状態（達成度）
4KPITrendKPIの傾向（前期比など）

kpi_property=3（KPIStatus）と kpi_property=4（KPITrend）の戻り値は少し特殊です。
通常 -1、0、1 の整数を返します。

戻り値状態傾向
1良好（目標達成）改善傾向
0中立（許容範囲内）横ばい
-1悪化（目標未達）悪化傾向

NOTE

状態と傾向の戻り値は、KPI定義時に設計者が設定したしきい値の式で決まります。基本は -1 / 0 / 1 ですが、カスタム定義によっては別の値を返す場合もあります。

CUBEKPIMEMBER関数の基本的な使い方

ここからは「Total Sales KPI」というKPIがExcelデータモデルに定義済みという前提で進めます。
基本的な使い方を順番に見ていきましょう。

基本例: KPIの名前を取得する

KPIの名前（タイトル）を取得するには、第3引数を 1（KPIValue）にして、別名を第4引数で指定します。

=CUBEKPIMEMBER("ThisWorkbookDataModel","[Measures].[Total Sales KPI]",1,"売上達成KPI")

セルには第4引数で指定した「売上達成KPI」が表示されます。
第4引数を省略した場合は「Total Sales KPI Value」のような自動生成のキャプションが入ります。

引数 kpi_property を変えて4つのプロパティを取り出す

同じKPIに対して、第3引数を1〜4まで変えると、それぞれ異なるプロパティを取り出せます。

=CUBEKPIMEMBER("ThisWorkbookDataModel","[Measures].[Total Sales KPI]",1,"値")
=CUBEKPIMEMBER("ThisWorkbookDataModel","[Measures].[Total Sales KPI]",2,"目標")
=CUBEKPIMEMBER("ThisWorkbookDataModel","[Measures].[Total Sales KPI]",3,"状態")
=CUBEKPIMEMBER("ThisWorkbookDataModel","[Measures].[Total Sales KPI]",4,"傾向")

ただし、この時点でセルに表示されるのは第4引数のキャプション文字列だけです。
実際の数値（実績値や状態の数値）を取り出すには、次の章で紹介するCUBEVALUE関数と組み合わせる必要があります。

NOTE

CUBEKPIMEMBER関数は単独で使うと「メンバーオブジェクト」を返します。セルにはキャプションのテキストが表示されますが、内部的にはCUBEVALUEで数値化するためのIDのようなものを保持しています。

CUBEKPIMEMBERとCUBEVALUEを組み合わせてKPIを表示する

CUBEKPIMEMBER関数の本領は、CUBEVALUE関数の引数として渡したときに発揮されます。
CUBEVALUEは「メンバーが指す実際の数値」を返す関数です。
CUBEKPIMEMBERで指定したプロパティの数値を、これで取り出せます。

値（実績）をCUBEVALUEで取得する

実績値を数値として取り出す数式は次のとおりです。

=CUBEVALUE("ThisWorkbookDataModel",CUBEKPIMEMBER("ThisWorkbookDataModel","[Measures].[Total Sales KPI]",1))

これでセルに「12,345,678」のような実績値の数値が表示されます。

目標値をCUBEVALUEで取得する

第3引数を 2（KPIGoal）に変えれば、目標値を取り出せます。

=CUBEVALUE("ThisWorkbookDataModel",CUBEKPIMEMBER("ThisWorkbookDataModel","[Measures].[Total Sales KPI]",2))

状態を判定アイコンに変換する

状態（kpi_property=3）はそのままでは -1 / 0 / 1 の整数です。
IF関数やCHOOSE関数で記号に変換すると見やすくなります。

=CUBEVALUE("ThisWorkbookDataModel",CUBEKPIMEMBER("ThisWorkbookDataModel","[Measures].[Total Sales KPI]",3))

この戻り値（仮に E2 セル）を判定マークに変換するなら次のとおりです。

=CHOOSE(E2+2,"×","△","○")

-1 のときは ×、0 のときは △、1 のときは ○ が返ります。
+2 を足しているのは、CHOOSE関数のインデックスが1始まりだからです。

傾向を矢印に変換する

傾向（kpi_property=4）も同じ方法で矢印に変換できます。

=CUBEVALUE("ThisWorkbookDataModel",CUBEKPIMEMBER("ThisWorkbookDataModel","[Measures].[Total Sales KPI]",4))

戻り値（仮に F2 セル）を矢印に変換するなら次のとおりです。

=CHOOSE(F2+2,"↓","→","↑")

-1 で下向き矢印、0 で横向き矢印、1 で上向き矢印が表示されます。

CUBEKPIMEMBERでKPIダッシュボードを作る

ここまでの組み合わせを使うと、関数だけでKPIダッシュボードを作れます。
ピボットテーブルに頼らないので、レイアウトの自由度が高いのが利点です。

KPI名・実績・目標・達成率・状態・傾向の6列レイアウト

A列〜F列に次のような数式を並べると、KPIダッシュボードの1行ぶんが完成します。

列ヘッダー数式
AKPI名=CUBEKPIMEMBER("ThisWorkbookDataModel","[Measures].[Total Sales KPI]",1,"売上KPI")
B実績=CUBEVALUE("ThisWorkbookDataModel",CUBEKPIMEMBER("ThisWorkbookDataModel","[Measures].[Total Sales KPI]",1))
C目標=CUBEVALUE("ThisWorkbookDataModel",CUBEKPIMEMBER("ThisWorkbookDataModel","[Measures].[Total Sales KPI]",2))
D達成率=B2/C2
E状態=CHOOSE(CUBEVALUE("ThisWorkbookDataModel",CUBEKPIMEMBER("ThisWorkbookDataModel","[Measures].[Total Sales KPI]",3))+2,"×","△","○")
F傾向=CHOOSE(CUBEVALUE("ThisWorkbookDataModel",CUBEKPIMEMBER("ThisWorkbookDataModel","[Measures].[Total Sales KPI]",4))+2,"↓","→","↑")

条件付き書式と組み合わせる

E列の状態セルに条件付き書式を設定すれば、○ を緑、△ を黄、× を赤に塗り分けられます。
「ホーム」タブ →「条件付き書式」→「セルの強調表示ルール」→「指定の値に等しい」で設定します。

複数KPIを縦に並べる

KPIが複数ある場合は、第2引数のKPI名を変えて縦に並べていきます。
たとえば2行目に「Total Sales KPI」、3行目に「Profit Margin KPI」のように記述します。
経営ダッシュボードのような一覧表が、関数だけで作れます。

NOTE

同じKPI名を何度も書くのが面倒な場合は、KPI名を別セル（例: G2）に書いておき、セル参照で渡す形にすると保守が楽になります。=CUBEKPIMEMBER("ThisWorkbookDataModel",G2,1) のように書けます。

CUBEKPIMEMBER関数でよくあるエラーと対処法

CUBEKPIMEMBER関数を使っているとよく出るエラーと対処法を整理しておきます。

エラー主な原因対処法
#N/A指定したKPI名がデータモデル/キューブに存在しない、スペルミス、大文字小文字違いピボットテーブルのフィールドリストでKPI名を確認しコピペで貼り付ける
#NAME?関数名のタイプミス、connectionが引用符で囲まれていない関数名の綴りを確認、第1引数を "ThisWorkbookDataModel" のように引用符で囲む
#GETTING_DATAエラーではなく、バックグラウンドで値を取得中の一時表示数秒待つ。長時間続く場合は接続不良を疑う
空欄・想定外の値kpi_propertyに1〜4以外の数値を指定、KPIが定義されていないメジャーを指定kpi_propertyは1〜4の整数のみ使用し、データモデル側でKPIが定義されているか確認

NOTE

KPI名は "[Measures].[KPI 1]" のような角カッコ付きの形式で書く必要があります。これは「キューブ表記」と呼ばれる書式で、ピボットテーブルからフィールドをコピーすると正しい形式で取得できます。

似たCUBE関数との使い分け

CUBE関数シリーズには7つの関数があり、それぞれ役割が異なります。
CUBEKPIMEMBERは「KPI専用」のメンバー取得関数なので、用途に応じて他のCUBE関数と使い分けます。

関数役割主な用途
CUBEMEMBER一般のメンバーを取得次元・階層・メジャーの参照
CUBEVALUEメジャー値（数値）を取得売上合計・件数などの集計値
CUBESETメンバーのセット（集合）を定義「上位10商品」などの動的なグループ
CUBESETCOUNTセット内のメンバー数を取得セットの件数カウント
CUBERANKEDMEMBERセット内のN番目のメンバーを取得TOP5表示
CUBEMEMBERPROPERTYメンバーのプロパティを取得メンバーの属性値（地域コードなど）
CUBEKPIMEMBERKPIとそのプロパティを取得KPIダッシュボード

CUBEMEMBERとの違い

CUBEMEMBER関数はキューブ内の一般のメンバー（次元・階層・メジャー）を返します。
一方、CUBEKPIMEMBERはKPI定義に特化しています。
KPIプロパティ（値・目標・状態・傾向）を引数で指定して取り出せる点が異なります。

CUBEMEMBERの詳しい使い方は【Excel】CUBEMEMBER関数の使い方をご覧ください。

CUBEVALUEとの違い

CUBEVALUE関数は「メジャーの数値そのもの」を返す関数です。
一方CUBEKPIMEMBERは「メンバー（参照情報）」を返すだけです。
実際の数値を取りたいときはCUBEVALUEと組み合わせる必要があります。
役割が補完的な2関数です。

CUBEVALUEの詳しい使い方は【Excel】CUBEVALUE関数の使い方をご覧ください。

CUBEMEMBERPROPERTYとの違い

CUBEMEMBERPROPERTY関数はメンバーの「属性プロパティ」（地域名や商品カテゴリなど）を返します。
一方、CUBEKPIMEMBERは「KPIプロパティ」（値・目標・状態・傾向）を返すという点で別物です。
プロパティの種類が違います。

CUBEMEMBERPROPERTYの詳しい使い方は【Excel】CUBEMEMBERPROPERTY関数の使い方をご覧ください。

CUBE関数シリーズの全体マップ

CUBE関数を学習する順序としては、まずCUBEMEMBER・CUBEVALUEで基本を押さえます。
次にCUBESET・CUBESETCOUNT・CUBERANKEDMEMBERでセット操作に進みます。
最後にCUBEMEMBERPROPERTY・CUBEKPIMEMBERで属性・KPI取得に広げるのがおすすめです。

関連記事のリンクは次のとおりです。

まとめ

ExcelのCUBEKPIMEMBER関数は、Power PivotやOLAPキューブで定義したKPIを取り出す関数です。
関数だけでセルに値・目標・状態・傾向を表示できます。

要点をまとめると次のとおりです。

  • 構文は =CUBEKPIMEMBER(接続名, KPI名, プロパティ番号, [別名])
  • 第3引数は 1=値 / 2=目標 / 3=状態 / 4=傾向 の4種類
  • 状態と傾向は通常 -1 / 0 / 1 の整数を返す
  • 数値を取り出すにはCUBEVALUE関数と組み合わせる
  • IF関数やCHOOSE関数で ○△× や矢印に変換すると見やすい
  • CUBE関数シリーズ7関数の中で、KPI専用のメンバー取得がCUBEKPIMEMBERの役割

ピボットテーブルでは難しい自由レイアウトのKPIダッシュボードを作りたいときに、CUBEKPIMEMBERとCUBEVALUEの組み合わせは強力な武器になります。
Power Pivotで作ったKPIを最大限に活かすために、ぜひ活用してみてください。

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