excel中利用powerquery实现数据整理

小E做了一场「直播惊喜活动」，结束后有许多快递需要发。

为了节约物流成本，他信心满满的跟老板说：可以将相同订单数据汇总合并后，一起发货。

老板一听，决定全程交由他搞。这下好了，可怜的小E不仅要搞数据，还要打包一堆快递，非常的头疼。

在之前的文章，我们给大家分享了函数和方方格子插件法。

今天，小爽就来给大家介绍一下其他的方法，看看利用 PQ，我们是如何解决这个问题的。

PS：PowerQuery(简称PQ)，是Excel2016及以上版本自带的插件，M函数是PQ中的函数叫法。

由于存在同个信息有多笔订单数据的情况，为了简化问题的难度，方便大家理解，我们先制作一个汇总辅助表。

温馨提示：PQ的做法，需要涉及到几个基础的M函数，不过也不难~

分组依据

使用PQ，自然是需要先将数据源导入到PQ编辑器里面啦！

选中辅助表的表格区域，在【数据】选项卡下，单击【来自表格/区域】，单击【确定】按钮。

进入到PQ编辑器后，下一步，就是要进行分组啦。

PQ中的分组问题，我们用到的是分组依据功能~?何为分组依据？

分组依据的功能，有点像数据透视表，对指定字段数据进行筛选统计。它可以指定多个字段作为条件，也可以同时统计多个结果。

分组依据可以支持的统计方式包括：求和，平均值、最小值，非重复行计数和所有行。

知识点补充完毕，大家跟着我来看看操作~

选中需要分组的列，也就是我们的姓名和手机列，在【转换】选项卡下单击【分组依据】。

出现分组依据对话框。

新列名：发货数量

操作：所有行

这个时候，我们可以看到表格按照姓名和手机号分组后，多了一个发货数量列。

发货数量列中每一行的Table里面就包含了当前行筛选后的表格数据。

按行合并

分组后的类型type后面的内容对全局不影响，为了代码简洁性可以删掉。

删掉后，公式更简洁~=Table.Group源,{"姓名","手机号"},{{"发货数量",each_}})

each_中的下划线就代表每一行中的筛选后的表的数据。

紧接着，如下图所示，我们需要把分组后发货数量这一列里面，筛选后的数据表中的产品名称和汇总个数用星号(＊)一一进行合并。

如下图：=Table.Group源,{"姓名","手机号"},{{"发货数量",eachTable.ToList(_,(x)=x{2}&"*"&Text.From(x{3}))}})

Table.ToList(_,(x)=x{2}&"*"&Text.From(x{3}))

Table.ToList函数主要是将表中每一行的产品名称和汇总个数进行处理。

其中，

产品名称在每一行索引中的第2个；

汇总个数在每一行索引的的第3个。

?Table.ToList函数是？

Table，是表的意思，ToList，是转换列表的意思。这个函数就是用来处理每一行数据的。

Table.ToList有两个参数：=Table.ToList表,(x)=x)x 代表每一行形成的列表

小Tip：

上面我加大了一点点难度，其实我们也可以事先在辅助表中先把产品名称和汇总个数进行合并了。这样大家应该更容易理解。

将辅助表导入分组后，直接取合并的列就可以了。

列表合并

最后一步，就是将合并后的产品名称和汇总个数，整个列表用分隔符合并起来。

在原先的基础上，需要利用Text.Combine函数将list用逗号进行合并。?Text.Combine函数是？

Text，是文本的意思，Combine，是结合的意思。这个函数就是用来文本连接的！

Text.Combie有两个参数：=Text,Combie文本序列分隔符

案例中，加一个Text.Combie：=Table.Group源,{"姓名","手机号"},{{"发货数量",eachText.Combine(Table.ToList(_,(x)=x{2}&"*"&Text.From(x{3})),",")}})

如果有做合并列的辅助表。

=Table.Group源,{"姓名","手机号"},{{"发货数量",eachText.Combine([合并],",")}})

到这里就搞定啦~

如果不想要有辅助表的做法，就需要两层分组，思路跟前面是一样的，就是多加了个Table.Group分组，学有余力的小伙伴可以试试。let源=Excel.CurrentWorkbook(){【Name="表 1"】}【Content】,分组的行=Table.Group源,{"姓名","手机号"},{{"发货数量",eachText.Combine(Table.ToList(Table.Group_,{"产品名称"},{{"a",(x)=Text.From(List.Sum(x【商品数量】)}}),(y)=y{0}&"*"&y{1}),";")}})in分组的行

最后的话

本文主要讲解了「如何用PQ解决复杂合并同类项」的问题，为了简化问题，文中制作了一个辅助表。

其中涉及以下知识点：

❶ 分组依据，能够将多字段进行汇总合并统计，如果想要返回所有行，在操作中选所有行就可以了。

❷ Table.ToLIst 可以对表中每一行数据进行进一步操作。

❸ Text.Combine 主要就是通过指定分隔符合并列表。