Excel中CUBE函数与数据模型的激情碰撞之一：初识

CUBE类函数非常强大，可以帮助我们充分利用数据模型超强的汇总分析能力。

之前介绍过CUBE类函数。这次重新整理，并且将CUBE类函数与动态数组和LAMBDA等新函数结合，从而发挥出更加强大的数据处理能力。算是一个升级系列吧。

从一个应用技巧说起这是一个不太常用但是非常好使的技巧。我们知道，透视表很方便，计算速度非常快。比大部分Excel函数都快。但是，透视表与最终报告之间很可能在格式和布局上不兼容。简单的说，我们需要在报告中引用一个汇总数据，而这个数据我们又必须（或者说最好的做法是）使用透视表计算。这时，我们先创建一个透视表，完成计算。然后使用GETPIVOTDATA函数从透视表中取值。

然后，使用GETPIVOTDATA公式：=GETPIVOTDATA(

"销售额", $A$3,

"Year", 2011,

"月", 4,

"大类", "Deluxe“)这个公式帮助我们从透视表中取出2011年4月产品大类为Deluxe的销售额。具体用法见这里。这是一个非常有用的技巧，我们用它做过很多非常专业的报告。但是，存在两个缺点：首先，要使用这个函数，必须存在透视表。其次，透视表结构的更改会导致函数计算结果发生变化。这两个缺点导致了这个函数使用时不是非常方便。因为有可能需要保留非常多的各种结构的透视表。

CUBE函数

我们今天介绍的CUBE类函数就可以看作GETPIVOTDATA的升级版。这里的升级有三个角度：数据源由一个表升级到了多个表，变成了数据模型。从原来从透视表中取数变成了从数据模型中取数。使用的函数从一个GETPIVOTDATA变成了一组CUBE类函数。现在，我们可以将多个表组织为一个数据模型，从而避免了非得将它们合并为一张表。数据模型中的表被分为两类，维度表（dimension）和事实表（fact），这种将维度表和事实表分开的方式使得我们可以针对维度的不同级别进行非常灵活和精细的分析。

这个分析可以通过透视表来展示，也可以通过各种视觉对象（Power BI）或图表展示。仔细分析这些展示，都可以理解为某个维度上的某个值的汇总。按照这个理解，我们就可以设计函数来实现同样的结果。这就是CUBE类函数的作用。这些函数可以帮助我们非常灵活的利用数据模型强大的计算能力，同时，它们还可以与动态数组和各种新函数，比如LAMBDA函数等结合，从而发挥更加强大的作用。

几个概念

这里需要介绍几个概念。

借用之前做过的3D数据报告理解CUBE概念CUBE，实际上指的是数据模型。如果只有三个维度的话，数据模型就可以类别为一个立方体。借用以前我做过的一个3维数据报告，MEMBER指维度成员。比如，时间维度的MEMBER=Q3。区域维度的MEBER=东区。VALUE指事实（fact）的汇总，或者叫做度量（measure）

用CUBE函数解析超级透视表

实际上，任何视觉对象 （透视表，图表）都可以用CUBE函数实现。下面我们以一个超级透视表为例，解析一下CUBE函数的应用：

这个超级透视表中，我们将其分为8个不同的元素，其中，除了7，8外，其余的都是MEMBER，只有7，8是VALUE。它们分别由下面的公式生成：

//1 度量值成员，需要使用哪一个计算结果？=CUBEMEMBER(

"ThisWorkbookDataModel",“

[Measures].[以下项目的总和:SalesQuantity]“)

//2 产品维度成员=CUBEMEMBER(

"ThisWorkbookDataModel",

"[Product].[ClassName].&[Deluxe]“)

//3 产品维度成员=CUBEMEMBER(

"ThisWorkbookDataModel",

{

"[Product].[ClassName].&[Deluxe]",

"[Product].[ProductName].&[A. Datum Consumer Digital Camera E100 Azure]“})

//4 产品维度成员：总计=CUBEMEMBER(

"ThisWorkbookDataModel",

"[Product].[ClassName].[All]",

"总计“)

//5 时间维度成员=CUBEMEMBER(

"ThisWorkbookDataModel",

"[Calendar].[Year].&[2011]“)

//6 时间维度成员：总计=CUBEMEMBER(

"ThisWorkbookDataModel",

"[Calendar].[Year].[All]",

"总计")

//7 汇总值=CUBEVALUE(

"ThisWorkbookDataModel",

$B$3,$C545,D$4)

//8 汇总值=CUBEVALUE(

"ThisWorkbookDataModel",

$B$3,$B550,D$4)这里只使用了两个CUBE函数：

CUBEMEMBERCUBEVALUE实际上，从CUBE（立方体）的角度理解，就是在一个 三维（多维）空间中，通过坐标定位某个点，然后返回该点（value），在这个透视表中，假设要得到箭头所示的值，就是要通过产品维度和时间维度，以及一个特殊的维度：Measure，来定位：

产品维度坐标为 ：Dulex类中的A. Datum Consumer Digital Camera E100 Grey，时间维度坐标为：2012年，

度量值Measure维度为：以下项目的总和:SalesQuantity根据这三个坐标在CUBE中定位，可以得到值：520。这就是第7个公式的作用。

而1~6分别介绍了从CUBE(数据模型)中取出给定成员（MEMBER）的公式，注意公式公式4和6中关于总计的写法与其他成员值的不同）。

有了这个两个函数，我们就可以将超级透视表函数化，而且不再依赖于超级透视表了。

超级透视表转为公式

除了这两个函数，Excel中还有其他的CUBE函数。关于这些CUBE函数的使用，我们下次再详细介绍。

目前，大家可以先自己琢磨一下这两个函数的使用。大家可以在Excel中先创建一个超级透视表，然后在数据透视表分析选项卡中，将透视表转换为公式：

这样，超级透视表就被转换为CUBE函数生成的结果了。详细介绍请看视频

加入E学会，学习更多Excel应用技巧

/portal/learn/class_list

详情咨询客服（底部菜单-知识库-客服）

Excel Power Query Power Pivot Power BI

Power Excel 知识库

按照以下方式进入知识库学习Excel函数 底部菜单：知识库->Excel函数

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

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

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

Excel企业应用 底部菜单：企业应用

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