原文标题:《哪位 Excel 高人琢磨出的这个数据整理技巧,太有用了!(下)》
小 E 之前在处理订单数据的时候,需要将相同的订单数据进行合并发货,以节约物流成本。
如下图所示,在此之前,我介绍了三种解决方法:函数方法,插件方法还有 PQ 方法。
小 E 在打包了上百份快递后,已经腰酸背痛的情景下。
巧好看到后台有小伙伴留言,想要让我们讲讲度量值。
本着满足于读者需求的原则,今天我们就来讲讲,如何利用度量值搞定上面这个问题 。
调用 PP 加载项
说到度量值,肯定有人会问,什么是度量值?
度量值:顾名思义,它就是一个值,也就是它的结果只有一个,一般是用聚合函数计算出来的值。
那么在 Excel 中,度量值在哪个地方呢?
就在 PowerPivot 选项卡下面。
PS. PowerPivot 是 Excel 的一个加载项,可用于管理数以百万计的数据行,并对这些数据执行强大的数据分析。
如果有些小伙伴没有看到 PowerPivot 选项卡,是因为没有将它调出来。
大家有开发工具选项卡的话,只需要在 COM 加载项把它调用出来就可以。
如果没有开发工具选项卡的话,我们可以在「文件」-「选项」-「加载项」,通过「转到」按钮,也可以调用出 COM 加载项对话框。
然后同样勾选「powerpivot」加载上去就可以了。
到这里,我就先默认大家都已经调出 PP 选项卡了。
那么下一步我们来看看具体做法。
具体步骤
由于存在同个信息有多笔订单数据的情况,为了简化问题的难度,方便大家理解,我跟之前一样,先制作一个汇总辅助表。
然后通过这个辅助表,用数据透视表的方式,来编写度量值。
我们先来看看具体操作步骤。
❶ 选中「辅助表」区域-在「插入」选项卡下-单击「数据透视表」-单击「表格与区域」。
放置在现有工作表中,同时勾选将此数据添加到数据模型中,单击【确定】按钮。
PS. 勾选数据模型后,数据会自动加载到 PowerPivot 中,所以我们就能使用里面的度量值啦~
这时,就可以看到刚刚的辅助表名称成为了透视表区域名称。
现在,我们来新建度量值~
❷ 在「PP」选项卡下单击「度量值」-新建度量值。
出现度量值对话框。
度量值名称:发货数量。
公式:
=CONCATENATEX'区域','区域'[产品名称]&"*"&'区域'[汇总个数],",")
单击【确定】按钮。
① 将姓名和手机号拖拽到行区域中。
② 将发货数量度量值拖拽到值区域中。
此时,我们想要的效果就实现啦!
度量值解释
在前面我们写了一个简单的度量值,就立马解决了我们的需求。
利用数据透视表的好处是,我们可以灵活的增加或者减少外部筛选字段条件。
比如说,我现在只想要合并同一手机号购买的产品以及对应数量,那么只需要把姓名字段从行区域中取消就可以了。
数据透视表中每一个汇总的值,里面都是一个数据集。
那我们来简单看看这个度量值。
=CONCATENATEX'区域','区域'[产品名称]&"*"&'区域'[汇总个数],",")
CONCATENATEX 函数的作用,就是将多个文本合并到一起,类似于 Excel 中的 TEXTJOIN 函数。
CONCATENATEX 函数的结构如下:
=CONCATENATEX表,表达式,分隔符)
我们先双击一下小爽发货数量的单元格。
此时,就会出现一个新的工作表,里面就有筛选小爽以及她的手机号码的数据集。
然后执行我们的度量值。
=CONCATENATEX'区域','区域'[产品名称]&"*"&'区域'[汇总个数],",")
先执行表达式: 产品名称与汇总个数合并:
'区域'[产品名称]&"*"&'区域'[汇总个数],
最后再执行 CONCATENATE 将数据利用分隔符合并。
合并后就是这个单元格的汇总结果了。
如果说不想用辅助表。
同理,我们也可以通过将数据源插入数据透视表的方式,将数据源添加到数据模型中,最后新建如下度量值,就可以搞定。
原理跟上面基本差不多,有兴趣的小伙伴可以自行去研究一下。
=CONCATENATEXVALUES'表1'[产品名称]),'表1'[产品名称]&"*"&calculatesum'表1'[商品数量])),";")
最后的话
本文讲解的是复杂合并同类项关于数据透视表的做法。
在插入数据透视表时勾选数据模型,数据会自动加载到 PowerPivot 简称 PP) 中,而度量值就是存在于 PP 中。
要想调用 PP 选项卡,只需要在 COM 加载项中勾选即可。
案例中的度量值,我们主要用到了 CONCATENATEX 函数,它是一个迭代函数,能够将数据集中的每一行进行表达式运算,最后再利用分隔符将其进行合并成一个值。
相比于其他的做法,使用数据透视表的好处是我们可以灵活的控制筛选环境,一旦筛选字段需要改变,我们只需要进行拖拖拽拽就可以。
而其他做法可能还需要重新更改,比较麻烦。
本文来自微信公众号:秋叶 Excel (ID:excel100),作者:小爽