接上文
强大的CUBE函数(2):CUBEMEMBER,SUBEVALUE
CUBSET函数的作用是将一组选定的成员组合为一个集合。
语法如下:
CUBESET(连接,集合表达式,[显示文本],[排序方式],[排序依据])
其中,
连接 => 简单理解为当前Excel中的数据模型。因为一个Excel文件只能有一个数据模型,所以这个名字是固定的:ThisWorkbookDataModel。注意,这是一个文本,必须写在引号里。
集合表达式 => 指要作为一个集合的一些项目。
[显示文本] => 指组合后的集合的显示值。
[排序方式] => 组合集合时用的排序方式。
[排序依据] => 组合集合时用的排序依据。
还是用具体例子来帮助理解吧。
例1
=CUBESET("ThisWorkbookDataModel","[SalesData].[月份].[一月]","报告月份")
这个公式组合了一个set,显示为“报告月份”,其中只组合了月份中的“一月”:
这里的第三个参数是这个set的显示值,如果不指定该参数,那么该单元格将显示为空。
使用的时候跟member一样:
这个公式实际上没什么实际意义,因为它的成员只有一个月份,跟直接使用CUBEMEMBER一样。
例2
=CUBESET("ThisWorkbookDataModel","[SalesData].[月份].[一月]:[SalesData].[月份].[三月]","报告月份")
在这个例子中,我们做了一个新的集合set,将月份中从一月到三月的数据组合起来,并将该集合显示为“报告月份”,
在CUBEVALUE中可以直接使用,
但是,很遗憾,这个统计并没有如我们所愿,汇总了1,2,3月的数据:
其实,这个集合汇总了列表中从“三月”到“一月”的数据:
这个例子提示我们,如果要汇总数据,请注意顺序。在我们的数据中,月份是文本,所以按照文本排序。在一月和三月之间就包含4,5,10,11等月,返回没有我们想要的2月份。
如果月份列是真正的日期数据,这个组合就可以得到我们真正想要的结果了。
当然,推荐的做法是在模型中使用日期维度表(如果是其他维度,就引入相应的维度表),然后在维度表中进行自己想要的排序)。
后面的两个排序参数基本上可以先不用管他了。
例3
如果希望将不连续的成员组合成一个集合,使用下面的公式:
=CUBESET("ThisWorkbookDataModel",{"[SalesData].[月份].[一月]","[SalesData].[月份].[二月]","[SalesData].[月份].[三月]"},"一季度")
这个公式将一月,二月,三月组成了“一季度”结合,即使它们并不连续。
不过这个公式的正规写法应该是这样的:
=CUBESET("ThisWorkbookDataModel",{"[SalesData].[月份].[一月]","[SalesData].[月份].[二月]","[SalesData].[月份].[三月]"},"a")
请仔细区别这两种写法。
例4
=CUBESET("ThisWorkbookDataModel",切片器_月份,"报告月份")
我们可以将切片器作为CUBESET的参数。这样,我们就创建了一个动态集合,该集合的元素由切片器的选择决定。
利用这个方法,我们可以做出非常灵活的统计报表。
注:CUBESET函数的第二个参数:集合表达式最长只能有255个字符。如果超过了255个字符,请将该表达式放在其他单元格中,用CUBESET函数引用该单元格。
CUBSETCOUNT函数返回一个集合(CUBESET函数的返回值)中的元素个数。
语法如下:
CUBESETCOUNT(集合)
其中,
集合 => 指需要计算元素个数的集合set。
假设,集合定义如下:
切片器选择如下:
公式:
=CUBESETCOUNT(F6)
返回结果:4。
这个公式也可以写成:
=CUBESETCOUNT(CUBESET("ThisWorkbookDataModel",切片器_月份,"报告月份"))
CUBERANKEDMEMBER函数按照排名返回一个集合中的对应成员。
语法如下:
CUBERANKEDMEMBER(连接,集合表达式,名次,[显示值])
其中,
简单理解为当前Excel中的数据模型。因为一个Excel文件只能有一个数据模型,所以这个名字是固定的:ThisWorkbookDataModel。注意,这是一个文本,必须写在引号里。
集合表达式 => 跟CUBESET中的集合表达式一样,也可以是CUBESET函数的返回值。
名次 => 需要返回的成员的名次。
[显示值] => 将返回的成员显示为该显示值,如果不使用此参数,将显示成员在模型中的值。
例如:
=CUBERANKEDMEMBER("ThisWorkbookDataModel",F6,3)
该公式返回F6集合中的第3个值,结果是:一月。
其中,F6中是CUBESET公式:
=CUBESET("ThisWorkbookDataModel",切片器_月份,"报告月份")
该函数不能用于数据模型,只能用于多维数据集。就不多加介绍了,大家使用的机会并不多。
未完待续
Excel+Power Query+Power Pivot+Power BI
自定义函数 底部菜单:知识库->自定义函数
面授培训 底部菜单:培训学习->面授培训
也可以在历史文章中学习Excel,Power Query,Power Pivot,Power BI,Power Automate各种技巧。
联系客服