用度量值搞定 Excel 数据整理

原文标题:《哪位 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),作者:小爽

Published by

风君子

独自遨游何稽首 揭天掀地慰生平

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注