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