加入“Power BI和Excel Power系列”微信交流群，请查看文末进群方式。
一、CUBEVALUE函数与PowerPivot数据模型
二、CUBEMEMBER函数、CUBERANKEDMEMBER函数与PowerPivot数据模型
三、CUBESET函数与PowerPivot数据模型
四、切片器配合多维数据集函数实现动态筛选
Excel中有一类函数是多维数据集函数，也叫CUBE类函数，这一类函数属于工作表函数。CUBE，中文翻译成立方体，所以多维数据集也可以叫做一个数据立方体。数据立方体包含了维度，项目和值。我们可以根据这三个层次从数据立方体中获取相应的数据。
Excel中的多维数据集函数一共有7个，一般的工作表数据是不支持这几个函数，所以大多数使用Excel的人也没有应用这几个函数的场景。而Power Pivot的数据模型或者连接Power BI等其他外部多维数据集时，就可以使用这几个函数从维数据集中提取数据。应用过数据透视表的人都知道，可以使用GETPIVOTDATA函数可以获取来自数据透视表中的值。但是该函数只能在创建数据透视表之后才能使用，并且如果透视表的字段发生了变化，结果就会报错。所以这种方法在一些不能适合更多的场景。
而另一类用于从Power Pivot模型中提取数据的方法就是使用DAX作为查询工具来提取数据，同样这个方法只能产生固定的格式，结果有自动扩展的功能，但是也不能应用一些固定格式的报表。
所以，在这样的情况下，我们可以使用CUBE类函数来完成类似于这样的任务。这不仅可以创建更多的动态的图表，还可以创建多种场景下的各式报表。
首先在Excel中使用Power Pivot创建一个数据模型。如下图所示。
销售总金额 = SUM ( '明细表'[产品销售金额] )平均折扣 = AVERAGE ( '明细表'[折扣比例] )总毛利 = SUMX ( '明细表',
'明细表'[产品销售数量] * ( '明细表'[产品销售价格] - RELATED ( '产品表'[产品成本价格] ) ) )
当我们将度量值放置于数据透视表中，然后在数据透视表分析工具下，依次单击OLAP工具——转化为公式时，发现Excel会自动将行、列和值都转化成了对应的多维数据集函数的公式。那么下面我们具体地介绍一下，多维数据集中常用的几个函数，他们分别是：CUBEVALUE、CUBEMEMBER、CUBERANKEDMEMBER以及CUBESET函数。
一、CUBEVALUE函数与Power Pivot数据模型
CUBEVALUE函数的作用就是从数据模型中获取聚合值。其语法如下：
CUBEVALUE(模型连接,[表达式1],[表达式2],……)"ThisWorkbookDataModel"，并且这个连接是在英文状态下的双引号中使用，并且每个参数都是在英文双引号中间的。
CUBE类函数在输入的时候，Excel也会有自动智能语法提示功能。如图所示：当该函数只有第一个参数的时候，也就是说只有模型连接的时候，结果返回空文本。
=CUBEVALUE("ThisWorkbookDataModel")
再比如我们要提取销售总金额的汇总值时，公式就可以写成如下。结果返回3830959.23。
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[销售总金额]")
再进一步，我们添加一个条件，比们我们要提取A区的销售总金额时，我们就可以再添加一个表示A区的表达式。
选择完大区名称后，语法会提示有一个ALL，此时表示所有所有大区的销售总金额。
=CUBEVALUE("ThisWorkbookDataModel","[大区表].[大区名称].[All]","[Measures].[销售总金额]")
所以我们只要将上述的ALL的部分修改为“A区”就行，需要注意的是。即公式可以写为：
=CUBEVALUE("ThisWorkbookDataModel","[大区表].[大区名称].[A区]","[Measures].[销售总金额]")上面的例子中，我们一次只能获取一个大区的值，如果我们想要获取多个大区的值的时候，我们可以事先在单位格中输入我们要提取的大区的名称，然后再利用公式拼接起来即可。如：
=CUBEVALUE("ThisWorkbookDataModel","[大区表].[大区名称].["&B14&"]","[Measures].[销售总金额]")二、CUBEMEMBER函数、CUBERANKEDMEMBER函数与Power Pivot数据模型
在上一节的例子中，我们通过事先设定的条件用公式拼接的方法要可以获取多个大区的销售总金额。
除此之外，我们还可以通过CUBEMEMBER函数和CUBERANKEDMEMBER函数来获取维度的名称或者度量值的名称。
CUBEMEMBER函数用于获取维度的或者度量取的名称。语法如下：
CUBEMEMBER(模型连接,维度表达式,别名)
该函数同时也支持给我提取到的维度或者度量值起一个别名，即第三个函数。比如我们要提取A区的名称，公式就可以写成，结果返回“A区”.
=CUBEMEMBER("ThisWorkbookDataModel","[大区表].[大区名称].[A区]")
如果要想显示为“A大区”,那么我们就可以起一个别名，在上述公式的基础上写成：
=CUBEMEMBER("ThisWorkbookDataModel","[大区表].[大区名称].[A区]","A大区")
同样我们也可以提取度量值的名称，比如我们提取"A区"的“销售总金额”的名称，起别名为“总金额”.
=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[销售总金额]","总金额")
由于当维度名是由CUBE函数提取的时候，我们引用时，再不需要使用“&”来拼接，而是可以直接引用。
当我们再次想提取A大区的销售总金额的时候，我们就可以直接使用上述的两个公式提取的值。所以公式还可以为：
=CUBEVALUE("ThisWorkbookDataModel",B3,C2)
上面的例子我们可以通过CUBMEMBER函数可以提取维度和值的名称。但是一次只能提一个。而CUBRANKEDMEMBER函数可以一次性提取多个，但是遗憾的时候，我们必须要事先知道维度一共是几个。
CUBERANKEDMEMBER(模型连接,表达式,序号,[别名])
这里的序号我们可以直接写在单元格中，也可以直接使用ROW函数直接嵌套在函数中。需要说明的是序号是从1开始，1表示总汇，返回结果为“All”。比如我们想一次性将5个大区返回出来，那么我们就得有6个序号，1表示汇总，2开始才是大区的名称。所以公式如下。
=CUBERANKEDMEMBER("ThisWorkbookDataModel","[大区表].[大区名称].MEMBERS",B7)需要注意的是MEMBERS这个部分，表示每个成员，即维度。
该函数只能获取维度表，但是不能批量获取度量值的名称。
三、CUBESET函数与Power Pivot数据模型
使用CUBEVALUE函数可以获取单个维度的单个项目的值，如果我们要想获取一个维度里多个项目的值的时候，我们可以使用CUBSET函数先定义一个集合，然后再求值。
CUBESET函数创建由一个或者多个项目组合的计算集。语法如下：
CUBESET(数据连接,表达式,[别名],[排序依据],[排序类型])
比如我们想要创建一个A区和B区的集合，那么公式就可以写成：
=CUBESET("ThisWorkbookDataModel","{[大区表].[大区名称].[A区],[大区表].[大区名称].[B区]}","A区+B区")有了这个集合，就可以结合CUBEVALUE函数来计算A区和B区的销售总金额。公式如下：
=CUBEVALUE("ThisWorkbookDataModel",B15,"[Measures].[销售总金额]")
四、切片器配合多维数据集函数实现动态筛选
上面的几个例子我们讲了如何获取维度中的项目值以及为度量值的名称和值。但是有一个问题就是，这些需要都是固定的。如果要是能与切片器结果起来，那就更完美了。
=CUBERANKEDMEMBER("ThisWorkbookDataModel","[大区表].[大区名称].MEMBERS",B3)
=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[总毛利]")
=CUBEVALUE("ThisWorkbookDataModel",C3,D$2)此时我们想使用产品表中的产品分类列来筛选上述的表格。
事先们插入一个切片器，步骤为“插入”-“切片器”-“连接”-“数据模型”，选择本工作簿中的数据模型，单击打开。在打开的切片器的对话框中选择“产品表”中的“产品分类”列，插入切片器。如图所示：
但是此时这个切片器对于左侧的数据筛选，并不会起任何的作用。因为我们还没有对其设置。
切片器也是一个维度，在CUBEVALUE函数中，表达式就是维度。该函数支持将切片器作为维度写入公式中，所以我们只需要修改D3单元格中的公式，加入切片器即可。当我们在CUBEVALUE函数中加一个表达式，输入“切片器”三字的时候，该函数的智能语法提示我们有可用的切片器。需要注意的是“切片器”这三个字不加双引号，直接输入即可。
然后当我们从切片器中选择切片的项目时，表格中的数据就发生了变化。
上述的这些方法不受限是否是合并单元格，所以利用数据模型的这些特性，我们可以创建多种基于数据模型的表格、图表以及其他的报告。