首页 文章详情

99% 的人不知道的 Excel 中最强大的函数,没有之一

PowerBI战友联盟 | 1129 2022-07-09 23:48 0 0 0
UniSMS (合一短信)
预约直播讲解,错过也很正常

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% 的企业,以业务为导向,进行精华数据分析,实现个人价值和企业价值的统一精进,这是落地的。

但请主要,最重要的是:上述能力要配合业务智慧使用,才能真正彻底解锁。不然由于你的领先性将会被排挤和打压,导致反向结果。

请小心驾驶。

参考:

PowerBI建模,Excel作图,双剑合璧

2022-07-05

预约直播讲过也很正常

在订阅了BI佐罗讲授的《BI真经》之《BI进化论》课程区,将更新本文内容更多精华讲解。

Power BI 终极系列课程《BI真经》


BI真经 - 让数据真正成为你的力量

扫码与精英一起讨论 Power BI,验证码:data2022

点击“阅读原文”进入学习中心

good-icon 0
favorite-icon 0
收藏
回复数量: 0
    暂无评论~~
    Ctrl+Enter