admin 管理员组文章数量: 887021
2024年2月23日发(作者：office怎么求平均分)
Excel PowerPivot DAX表达式related和relatedtable函数详解
大家都直到，PowerPivot是Excel最新加入的数据库分析工具，它和PowerQuery，PowerMap，Powerview构成了Excel Power BI的核心功能模块。而这些组件都是基于数据库的，它属于一种列式存储，效率比Excel工作表的单元格存储更高。对于PowerPivot而言，大多数Excel工作表函数仍能在“添加列”中正常使用，但是有些函数是不能直接使用的，尤其在关系型数据库中很重要的vlookup函数，在PowerPivot中是无法直接使用的，但是数据建模中的本质就是基于公共列的关系的建立，所以PowerPivot中必定存在专门解决这类问题的函数，今天给大家介绍其中的两个：related和relatedtable函数。它们的功能比vlookup更强，是PowerPivot的专属函数。
【软件版本】Excel 2019 家庭学生版
一、问题背景介绍
大家都知道，说到数据建模，常常离不开“一”端和“多”端两个概念，下面通过两张表来解释这两个概念（这两张表我已经事先从工作表导入到了PowerPivot中，导入方法不再赘述）。
现有两张表：
①销售明细表
②商品分类对照表
观察以上两张表（销售明细表；商品分类对照表），我们不难发现几个特点：
①两个表有一个公共列：“商品名称”列；
②在“销售明细表”中的“商品名称”，由于有很多笔销售记录，所以同一个商品名称可能反复出现多次，这也符合事实情况；
③在“商品分类对照表”中的“商品名称”，没有重复值，每个商品名称都是唯一不重复的；
我们通常诸如“销售明细表”的这类表格称为“事实表”，又可以称为“多”端表（有重复项），
而把“商品分类对照表”这类表成为“维度表”，又可以称为“一”端表（无重复项）。
二、related和relatedtable函数用法介绍
函数的用法（类似于vlookup函数的功能）
那么，按照常规的Excel操作中的数据透视表，如果这两个表不在PowerPivot而在Excel中，如果我要分析每一个“商品分类”的总销售金额是多少：通常的做法是，通过vlookup在“一”端表中查到对应的“商品分类”，然后返回给“多”端表中对应的行。然后把匹配好了商品分类的“多”端表（宽表），导入数据透视表进行分析即可。
但是在PowerPivot中不能使用vlookup，所以我们就需要使用related函数来替代它的功能，但是要使用related函数有个前提条件：就是先要对发生关系的两个表格的公共列进行数据关系建模。
听着高端，其实我们打开“关系图视图”中（单击按钮即可打开视图），
把一个表的公共列（即“商品名称”）鼠标拖动到另一个表的公共列上即可：
松开鼠标，看到以下的标记：
其中“1”标记，表示“一”段表（或称“维度表”），
其中“*”标记，表示“多”端表（或称“事实表”）。
表示数据建模成功！
那么，既然related函数和vlookup函数类似，肯定就是用于把“商品分类对照表”中的“商品分类”通过建模使用的公共列“商品名称”匹配到“销售明细”表中了，这就是vlookup的功能，和related函数是一样的。
那么怎么使用related函数呢？
我们把鼠标定位到“销售明细”表最右侧的“添加列”中的任意单元格，
然后再顶部的公式编辑栏输入：=related(‘商品分类对照表’[商品分类])
然后回车，即可把“商品对照分类”表中的“商品分类”字段匹配到“销售明细”表中来。
效果如下图所示：
怎么样？是不是和vlookup完全一致？
而且由于之前进行了数据建模，related函数写起来也比vlookup更加简单，
related只有一个参数：‘表名’[字段名]
这就是related函数的基本用法：即利用建模的公共列，用于把“维度表”表中其他字段信息匹配到“事实表”中，它的功能和vlookup完全一致!
table函数的用法
那么既然related函数和vlookup一样，relatedtable函数是干嘛用的呢？其实，relatedtable的用处和related刚好相反：是把“事实表”（或称“多”端表）的信息，利用数据建模关系，查询匹配到“维度表”（或称“一”端表）中。
那么大家肯定有疑问了，“一”端表中每个“商品名称”只有一行记录，而“多”端表中“商品名称”有很多重复的记录，通过“商品名称”匹配到“一”端表中，那“一”段表总共就有10行，岂不要炸掉？一行格子怎么放下这么多行“多”端表中的记录呢？
哈哈，这个问题其实很简单，你想想我们做数据透视表的时候，几万行的数据，我们的值字段怎么最后就就变成了一个几十个格子的二维表呢？答案很很简单，就是有“聚合”，我们数据透视表中的“值汇总方式”就是一种“聚合”，同样的道理，如果“一”端表中放不下这么多行“多”端表所匹配的数据，我们就把这能够对的上的N行数据做一个“聚合”(或者称为“汇总”)，称为一个单一的数值，不就可以放到一行中了吗？
所以，我们在“一”端表中使用relatedtable函数时，如果外面不嵌套任何一种类型的“聚合”函数，它就会报错，比如下面这样：
假设这里我们须要在“一”端表中，通过“商品名称”获取它在“多端”表中的所有行，由于有很多行匹配的“商品名称”记录存在，对应的“销售额”自然无法放在一行中显示，于是会报错：
那么，我们只有在外部嵌套一个聚合函数，才能把这重复的N行数据聚合成一个数值，也才能放进一行中进行显示，比如：我们可以使用countrows等聚合函数：
这里我们就以countrows为例，可以理解为求每个“商品名称”的有多少笔销售记录。
值得注意的是：relatedtable函数针对是整个表，而非表中的某一个字段，比如我们无法通过relatedtable来查询每个“商品名称”的总的销售金额是多少。
正式因为relatedtable引用的是整个表格，如其名“table”，所以它也只有一个参数：即须要引用过来的表的表名，所以如果统计每个“商品名称”有多少行交易记录，正确的写法应该是：
=countrows(relatedtable(‘销售明细’))
如此一来，我们就在“一”端表中，得到了每个“商品名称”的销售记录的行数。
【拓展延申】
能否把“多”端表和“一”端表中通过“商品名称”匹配的某一个字段（例如“销售额”等），汇总到“一”端表中呢？
其实也可以，但是这里牵扯到一些其他的知识，这里只提供方法，不展开讲原理。
=calculate(sumx(‘销售明细’, ‘销售明细’[销售额], ‘销售明细’[商品明细]= ‘销售明细’[商品明细])
三、本篇总结
今天主要给大家讲了PowerPivot中的两个函数：related和relatedtable。其中related的功能和Excel工作表函数vlookup是相同的，而relatedtable的功能，相当于是把建模列匹配的表格行整行从“多”端抓取到“一”端，所以须要使用聚合函数countrows匹配。其实今天讲的都是最基础的内容，relatedtable构建的表格行也可以和其他的DAX函数进行嵌套使用，但不属于本节介绍的内容。
须要注意的是，无论是related还是relatedtable函数，都必须先在PowerPivot中完成数据建模才能使用！此外，relate和relatedtable的参数都只有一个，related的参数是：表名+字段名，而relatedtable的参数是：表名。前者返回匹配的某一个字段中的行，而后者返回的是若干个匹配的整行（包含有多列）构成的一个微型表格。
版权声明：本文标题：Excel PowerPivot DAX表达式之related和relatedtable函数详解 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.freenas.com.cn/free/1708639842h528493.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论