强大的CUBE函数（2）：CUBEMEMBER，SUBEVALUE

分类：多维数据集

上文见强大的CUBE函数（1）：概述

CUBEMEMBER

---

我们先来看一下CUBEMEMBER函数，这个函数的作用是用来返回Cube中的成员。

语法如下：

CUMEMEMBER(连接,成员表达式,[文本])

其中，

• 连接 => 简单理解为当前Excel中的数据模型。因为一个Excel文件只能有一个数据模型，所以这个名字是固定的：ThisWorkbookDataModel。注意，这是一个文本，必须写在引号里。

• 成员表达式 => 要返回哪一个成员。写法是这样的：[表].[字段].&[项目]。其中，表指的是数据模型中的表的名称；字段是该表中的列名称；项目是该列中的一个值。例如：[SalesData].[产品].&[芬达]。注意，这个参数也是文本，必须写在引号里。

• 文本 => 是一个可选的参数。如果不使用该参数，公式返回的是成员在表中的值，如果使用该参数，公式返回的是这个参数指定的值。

实际上，我们在将透视表转换为公式后，那些用来分类的维度也转换为CUBEMEMBER函数了：

提醒关注：

• “以下项目的综合:数量”这个成员，是[Measures]组中的一项。
  

• 行总计和列总计对应参数的写法。

• 引用具体字段中的某一项，要写&，写全部的项目[All]时，不用&。
  

CUBEVALUE

---

CUBEVALUE函数的作用是根据给定的MEMBER，返回对应的度量值。

语法如下：

CUBEVALUE(连接，[成员表达式1]，......)

其中，

• 连接 => 简单理解为当前Excel中的数据模型。因为一个Excel文件只能有一个数据模型，所以这个名字是固定的：ThisWorkbookDataModel。注意，这是一个文本，必须写在引号里。

• 成员表达式 => 需要返回的值所属的成员。写法是这样的：[表].[字段].&[项目]。可以省略。

下面举几个例子帮助我们理解CUBEVALUE函数。

例1

=CUBEVALUE("ThisWorkbookDataModel")

CUBEVALUE函数可以除了连接外，没有任何参数。但是，这个公式返回空值。（实际上是返回空文本）

这就好像在透视表中没有设置任何字段，只有一个空的透视表一样。

例2

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[以下项目的总和:数量]")

如果公式中没有指示任何维度，只有一个度量成员的话，显示的是总计。

这就好像在透视表中只有值字段，没有其他行字段和列字段一样。

例3

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[以下项目的总和:数量]","[SalesData].[月份].&[一月]")

这就是CUBEVALUE函数的标准用法。相当于透视表中既有值字段，也有行字段，或者列字段。

注：在CUBEVALUE函数中，这些成员之间的先后顺序没有任何关系，可以将度量写在前面，也可以将其他成员写在前面。

例4

=CUBEVALUE("ThisWorkbookDataModel","[SalesData].[月份].&[一月]")

CUBEVALUE函数中可以不写任何度量值成员，只写其他维度成员。但是，返回的结果永远为1。

例5

=CUBEVALUE("ThisWorkbookDataModel",$B$3,$B6,D$4)

在CUBEVALUE函数中，成员可以通过单元格引用指定。但是，你不能简单的在单元格中输入“一月”，“芬达”等文本，然后就在CUBEVALUE公式中引用，那样会返回#N/A的错误值。只有单元格中是用CUBEMEMBER函数返回的结果时，才能在CUBEVALUE中引用该单元格。

实际上，在这个例子的公式中，CUBEVALUE并不是使用那些单元格的显示值，而是使用该单元格的公式中所指明的成员表达式。

小插曲

这里有一个"bug"。如果你在一个单元格（比如B1）中使用CUBEMEMBER函数返回了一个成员，然后在CUBEVALUE函数中引用B1单元格，这当然会返回正确结果。现在，你将B1单元格复制，然后粘贴成数值，你会发现CUBEVALUE依然返回正确的结果。只有当你手动输入该值的时候才会出错！

度量值

在上面的例子中，我们查询的度量值是：

以下项目的总和:数量

这是中文版Excel中固定的写法。类似的还有计数，下图展示了一些常见的写法：

类似于“以下项目的计数”这样的度量值称为隐式度量值，实际上是对表格中字段的直接汇总。

如果不知道怎么写，也可以创建一个透视表，看看名字是什么

。

你也可以自己创建度量值。像上图中的占比就是我自己创建的一个度量。关于如何创建度量值，请参见Power BI相关文章：

不可能的透视表之如何在透视表中显示文本

不可能的透视表之项目完成率——一个曾经困扰我非常久的问题

【不可能的数据透视表】如何用数据透视表计算中位数

不可能的数据透视报表

或学习我们相关的视频课程。

切片器结合CUBEVALUE函数

---

我们可以添加针对数据模型的切片器。

在Excel中，点击插入选项卡，点击切片器：

然后选择“数据模型”，

选择需要添加的字段：

添加一个切片器，

在切片器上点击右键，在菜单中点击切片器设置，打开切片器设置对话框，

记住红色加亮的切片器名称：切片器_月份，

在CUBEVALUE公式中将该名称添加为一个参数：

现在，CUBEVALUE已经可以通过切片器改变不同月份的汇总结果了。

实际上，这里切片器被当作CUBE的成员使用了。

注：当使用切片器作为成员时，注意不要放在引号里。

未完待续

Excel+Power Query+Power Pivot+Power BI

---

Power Excel 知识库    按照以下方式进入知识库学习

Excel函数   底部菜单：知识库->Excel函数

自定义函数  底部菜单：知识库->自定义函数

Excel如何做  底部菜单：知识库->Excel如何做

面授培训  底部菜单：培训学习->面授培训

也可以在历史文章中学习Excel，Power Query，Power Pivot，Power BI，Power Automate各种技巧。