用度量值搞定 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 将数据利用分隔符合并。

合并后就是这个单元格的汇总结果了。

如果说不想用辅助表。

同理，我们也可以通过将数据源插入数据透视表的方式，将数据源添加到数据模型中，最后新建如下度量值，就可以搞定。

原理跟上面基本差不多，有兴趣的小伙伴可以自行去研究一下。

=CONCATENATEX(VALUES('表1'[产品名称]),'表1'[产品名称]&"*"&calculate(sum('表1'[商品数量])),";")

最后的话

本文讲解的是复杂合并同类项关于数据透视表的做法。

在插入数据透视表时勾选数据模型，数据会自动加载到 PowerPivot (简称 PP) 中，而度量值就是存在于 PP 中。

要想调用 PP 选项卡，只需要在 COM 加载项中勾选即可。

案例中的度量值，我们主要用到了 CONCATENATEX 函数，它是一个迭代函数，能够将数据集中的每一行进行表达式运算，最后再利用分隔符将其进行合并成一个值。

相比于其他的做法，使用数据透视表的好处是我们可以灵活的控制筛选环境，一旦筛选字段需要改变，我们只需要进行拖拖拽拽就可以。

而其他做法可能还需要重新更改，比较麻烦。

本文来自微信公众号：秋叶 Excel （ID：excel100），作者：小爽

广告声明：文内含有的对外跳转链接（包括不限于超链接、二维码、口令等形式），用于传递更多信息，节省甄选时间，结果仅供参考，IT之家所有文章均包含本声明。