Excel 中最强大的函数到底是哪个呢?
这看起来是标题党。其实,经过大量培训中的调查,对于这个函数,的确 99% 的人都不知道。至于强大性,有可能超越 VLOOKUP 的强大吗?没有错。
我是 BI 佐罗,今天来和大家介绍这个强大的函数:CubeValue。
CubeValue 是干嘛的
CubeValue,是用来从 Cube(立方体)中提取数据的。准确讲,这里的立方体是一个 “超立方体”,该 “超立方体” 中包括了所有的数据以及业务逻辑。因此,构建 CUBE,是磨刀不误砍柴工;而直接写公式计算则有点显得 “着急”。后面会详细讲解什么是 CUBE。
为什么几乎没人知道
之所以几乎没有人知道这个函数,是因为几乎没人用这个函数;
之所以几乎没有人用这个函数,是因为几乎不存在用这个函数的条件。
要使用这个函数,就必须得有 CUBE,在过去,想要有 CUBE,就必须有人搭建 CUBE。这在办公领域,几乎是不存在的。
因为,搭建 CUBE 和用 Excel 是泾渭分明的两套绝对无交叉的技能体系,需要企业至少有一个部门叫:商业智能部门。
由于企业中,中小企业占据了 99% 的数量,而中小企业并没有专业的 BI 部门,因此,在 Excel 的用户中,99% 的人根本没有使用这个函数的机会和前提。
为什么现在可以了
使用 CUBE 函数,必须具备一个条件,那就是:必须有一个 CUBE。
怎么才可以有一个 CUBE 呢?有三种方法:
由专业 IT 团队利用 SSAS 搭建 CUBE,提供给 Excel 用户。(推荐度:不推荐)
使用 Excel Power Pivot 构建一个内置小 CUBE。(推荐度:看情况)
使用 Power BI 构建一个内置小 CUBE。(推荐度:必然之选)
使用 Power BI 以满足的一个重要需求就是:业务人员需要将各种表格连成一片,并附加一些业务逻辑在上面。由于 Excel 中数据模型能力受限,使用 Power BI 变成了必然的技术选项。
另一方面,Power BI 作为微软提供的统一自助商业智能分析平台,已经在与 Excel 的接通方面提供了战略级的原生支持以及持续优化。这句话很重要,这里想强调的是:用 Excel 连接 Power BI 或其中的数据模型 CUBE,并不是一个黑科技或技巧,而是微软就在战略层面希望用户这么做的设计。
要支持这一观点,有两个事实。
第一,Excel 的更新中,已经在显著的位置,提供了对 Power BI 数据模型(也就是 CUBE)的连接。如下:
可以看出,在插入透视表的选项中,已经明确指出,透视表可以有四个来源:
表格和区域,这是 99% 的办公人员用的内容;
来自外部数据源,这是一种通用的基础;
来自数据模型,就是 Excel 内置的数据模型;
来自 Power BI
很明显,巨量用户在这里入口都会好奇地问正在学习的教程 / 老师,后面三个是干嘛的?大部分回答,可能是:用不到,不用管。这句话会耽误所有被这句话耽误的人;也同时给了没有被耽误的人全新的世界。
第二,在最近的 Power BI 官方博客中,明确指出,Excel 连接 Power BI 的体验在持续优化。如下:
另外,Excel 连接云端 Power BI 的体验如下:
微软 Power BI 团队以及 Excel 团队已经将形如左侧的用户体验优化成了右侧状态。
根据以上两大事实,可见:
Excel 连接 Power BI 的数据模型,不仅仅是技巧,更是战略级的康庄大道。但 99% 的人不曾走过,这个大道上,几乎没有车辆,因此,在这个大道上行驶,不仅不卷,还可以高速驾驶,畅通无阻。
到底 CUBE 是什么
作为超立方体的 CUBE,其真正精确称呼应该是:数据模型超级立方体。
CUBE = Data + Model + Logic
该公式非常重要,其内涵也非常深刻。
数据。
模型。
逻辑。
数据,哪里都有数据,但大部分数据都是脏的,就像不能喝的水一样。但数据是一切的基础。但有数据也没用。
模型,只有让数据形成模型,才可以表示商业的结构化。模型是商业数据的结构化抽象,如下:
Model = Table + Relation
要想有模型,必须是基于标准表构建关系,连成一片。
逻辑,用来表达如何基于模型,从中提取信息的过程。
Logic = Formula( Model )
之所以称 CUBE 为数据模型超级立方体,在于:
它不仅仅是三维的,而是 N 维的。
它不仅仅有数据,而且有结构化且富有关系的数据。
它不仅仅是静态的,而且包括动态的计算逻辑。
CubeValue
既然 CUBE 是数据模型超级立方体,那么,需要一个机制来从中获益,这个机制被封装成一个函数,在 Excel 中,就是:CubeValue。
不难想象,要使用 CubeValue,要给它三个信息:
CUBE 在哪里?
哪些维度下?
计算逻辑是啥?
不论是 Excel 或者其他任何工具,如果希望从数据模型超级立方体中获取信息,则需要且仅需要以上信息参数即可,稍后举例。
关于 CUBE 误解的纠正
这里并不讲解构建 CUBE 的细节,创建的任何一个 Power BI 文件,都是 CUBE。
但值得注意的是,对于初学者可能会陷入一些误区,值得注意的正确概念如下:
CUBE 不是 DAX。
CUBE 是看不见的,但其结构是可以被理解的。
用维度或表去理解 CUBE 是等价的。
最大最重要的一点是:
CUBE 是自然的,简单的,理所应当的。
💡脑洞
睡觉前,我在想如何更精确的定义和更通俗地理解 CUBE,我突然想到维度之间是彼此独立的,在数学上叫:垂直正交基。通过选择一个基,可以定位到唯一的位置(Cell),CUBE 有很好的数学逻辑基础,关于这方面的论述,我们将在相对专业的文章和课程中再展示给大家。
使用 CUBE 的方法一:透视表
使用 CUBE 有两种方式,
【第一种】正则方式:透视表
之所以叫正则方式,在于强调该方式的通用性和简单性,如下:
两种图标分别直观地展示了:
CUBE 中的逻辑
CUBE 中的维度
从这个意义上来看,用户基于 CUBE 使用透视表的本质在于:
按照某些维度来筛选出一个子 CUBE;
将子 CUBE 的维度放在二维表的行;
将子 CUBE 的维度放在二维表的列;
行列交叉处的点(Cell)放入 CUBE 中的逻辑进行计算以填充值。
这才是透视表本源的,真正的奥妙所在。
透视表(Pivot Table),其透视一词的中文翻译极为精妙,它准确地诠释了:
如何用一个二维表面来观察多维数据宇宙的方法。
有的小伙伴会问:
那么我们平时基于 Excel 区域 / 表格做的透视表呢?
没有错,平时基于 Excel 区域 / 表格做的透视表,究其本质,用户仅仅构建了一个二维数据宇宙而已。
透视表的广义精妙之处在于:
数据宇宙可以是二维的,三维的,…,N 维的,透视表,就像是一个窗口,将 N 维宇宙坍缩在一个二维平面,来观测它。
使用 CUBE 的方法二:CubeValue
使用 CUBE 有两种方式,
【第二种】自由方式:CubeValue。
既然用户基于 CUBE 使用透视表的本质在于:
按照某些维度来筛选出一个子 CUBE;
将子 CUBE 的维度放在二维表的行;
将子 CUBE 的维度放在二维表的列;
行列交叉处的点(Cell)放入 CUBE 中的逻辑进行计算以填充值。
那么,是否可以将这种批量式的方法,精细化到具体的每个格子(Cell)呢?
这个方法需要一个工具,该工具就是 CubeValue。
CUBE 在哪里?
哪些维度下?
计算逻辑是啥?
例如,对于如下的透视表:
如果不想要透视表,而仅仅想知道技术的销售额是多少,怎么做呢?可以这样:
其中的 Excel 函数是:
= CUBEVALUE( "PBIFileModel", "[Dim 产品].[产品类别].&[技术]", "[Measures].[Model.Sales]" )
这里的确包括了三部分:
CUBE 在哪里?答案:"PBIFileModel"。
哪些维度下?答案:"[Dim 产品].[产品类别].&[技术]"。
计算逻辑是啥?答案:"[Measures].[Model.Sales]"。
当有了这几个参数,CubeValue 函数就会从叫 "PBIFileModel" 的 CUBE 中,在 "[Dim 产品].[产品类别].&[技术]" 的维度下,按 "[Measures].[Model.Sales]" 的逻辑来计算出值并返回。
CubeValue 的特点
CubeValue 除了在参数中具备特点:
CUBE 在哪里?答案:"PBIFileModel"。
哪些维度下?答案:"[Dim 产品].[产品类别].&[技术]"。
计算逻辑是啥?答案:"[Measures].[Model.Sales]"。
以外,还有三大重要的特点。
【特点一】文本式导航
为了在 CUBE 中导航,这里使用了文本的方法,这样的方法有一个好处:
可以进行字符串拼接,为形成构建复杂应用提供了灵活的基础。
【特点二】智能提示
在导航时,可以直接使用智能提示,例如:
很有可能在使用 CubeValue 时,并不太清楚 CUBE 中有哪些可用的资源,而该函数自带智能提示,可以很方便的导航。
度量值,也可以完全通过导航找到。
【特点三】叶子节点表达
如果想选择产品类别为 “技术” 的维度值,也就是该维度下的叶子节点,如下:
只需要在中括号前加一个 & 符号即可。该模式为固定规律,这样写 CubeValue 时就很容易了。
根据以上三大重大特点,实际可以看出,CubeValue 的使用非常简单。
透视表转为 CubeValue
如果嫌弃透视表比较死板或者手写 CubeValue 的效率不高,Excel 还提供了一个方法,将透视表转为 Cube 函数方法,如下:
点击【数据透视表分析】【OLAP 工具】【转换为公式】即可将透视表转为全部 CUBE 函数。如下:
可以看出,整个透视表平面化了,内部的计算全部用 CUBE 函数给出。
Cube 运算的组装
观察下,还可以发现,CubeValue 的计算可以由其他的单元格给出,来动态拼装一个 Cube 计算,这点又非常强大了。
CubeMember 函数
观察 “技术” 二字,可以发现:
这里的 “技术” 二字并非文本,而是利用了 CubeMember 函数。
CubeMember 函数是 CubeValue 函数的伴随函数,它实现了可复用。
如果需要复用一个维度对象,可以先用 CubeMember 暂存,然后再去 CubeValue 拼装。
这种方式彻底实现了:
可复用性
可动态拼装
最强大的函数
之所以说 CubeValue 是 Excel 中最强大的函数,且没有之一,是因为它站在数据模型超级立方体这座顶峰上。
使用 CubeValue 以及联合使用其伴随函数 CubeMember 相当于在利用最强大的商业智能资源,这当然是最强大的。
VLOOKUP 在这里,仅仅是构建 CUBE 中的数据模型中的表之间关系的等价机制。
可见理解和准确使用 CUBE 函数系列,和办公用 VLOOKUP 等级,已经差开了 N 个数量级的能量。
历史革新
Cube 函数是一套新的机制吗?不是。
它甚至和经典款的 Excel 同时出现,已经存在数十年。能真正发挥其威力的时代却在现在,其本质原因在于:
业务人员可以在完全不需要 IT 的协助或干预下,完全自助构建 CUBE,这将彻底解锁尘封在 Excel 体内的超级能力。
总结
本文并非标题党,反而描述了一个事实,至此,你已经超越了 99% 的 Excel 用户,进入了成为 1% 的人的通道。
Cube 函数,具备这样的重大特点:
Excel 中最强大的函数,没有之一,因为有 CUBE 为它撑腰。
使用起来极其简单,且非常好记。
配合伴随函数以及文本拼装可以制作出动态而强大的任意计算。
现在,业务人员可以利用 Power BI 和 Excel 彻底解锁自助商业智能的全部威力。
我们根本不 Care 企业数字化转型是什么,我们现在只是知道:
一个人,一天时间,一台电脑,一套工具(几乎免费的),一套方法,驾驭一个亿的数据之能力已然在这里。
不难测算,该能力足以进入全世界 99% 的企业,以业务为导向,进行精华数据分析,实现个人价值和企业价值的统一精进,这是落地的。
但请主要,最重要的是:上述能力要配合业务智慧使用,才能真正彻底解锁。不然由于你的领先性将会被排挤和打压,导致反向结果。
请小心驾驶。
参考:
预约直播讲解,错过也很正常
在订阅了BI佐罗讲授的《BI真经》之《BI进化论》课程区,将更新本文内容更多精华讲解。
Power BI 终极系列课程《BI真经》
BI真经 - 让数据真正成为你的力量
扫码与精英一起讨论 Power BI,验证码:data2022
点击“阅读原文”进入学习中心
↙