如果你经常使用Excel中分类汇总,那么你一定要学习一下PQ中分组依据(Table.Group),他可以说是对分类汇总进行了系统全面的增强和升级
直接上来就讲PQ中的Table.Group估计很多人都要懵,所以我们先来通过大家熟悉的功能引入!
引子 | Excel基础功能-分类汇总
这样的数据,我们先按照销售员做一个汇总,并且可以看到明细,这个时候,我们就可以使用分类汇总
动画操作演示:
1、对分组的销售员排序
2、点击【数据】-【分类汇总】
本质是分组+聚合!但是PQ中的分组功能,做了增强了,是那种真正的增强了很多!
类似的功能,我们看看PQ如何处理-分组依据(Table.Group)
关于如何把数据加入到PQ编辑器等基础问题,这里就不再啰嗦,因为前面已经写过基础专题,推荐0基础阅读一下,构建基本的知识框架
下面,我们直接来看功能演示及讲解!
PQ中的分组依据功能,使用界面操作,也是分两步
①:分组 - 根据那(几)个列把内容分成几组
②:聚合 - 对每一组中指定的列进行聚合操作(如求和、平均等)
PQ中不仅仅可以进行类似分类汇总的常规数组聚合操作,可以还是可以对一些文本进行聚合处理,比如文本使用指定分隔符连接到一起等!
下面是我们通过界面操作,自动生成的M函数公式,有点像录制宏!
下面我们会对Table.Group函数做一些基础讲解!
入门 | Table.Group函数基础语法
通过上面的引子,我们大概了解了Table.Group,下图就是Table.Group函数的基础语法拆解,说明都通过注释的方式在图中说明,先有一个大概的了解,我们再看两个案例巩固
第一参数比较好理解,就是一个加载到PQ中的表,部分同学不太理解第二、三参数
下面通过一个案例说明一下,第二、三参数
▎案例:理解第二、三参数
▼ 格式化一下代码,加上注释
对照着我们的手工操作,大家对Table.Group前三个参数应该有了进一步的认识、一般我们熟悉后,可以自己来书写,每个分组的第三参数是指定类型,也可以不写。
基础 | 文本聚合问题
上面我们说到PQ中分组可以对文本进行聚合,大大增强了分组汇总功能,不过我们看到默认的操作界面是没有关于文本的处理的,需要我们自己来写!
▼ 文本聚合
新手阶段,我们可以使用分组依据功能+修改来处理,等大家熟悉了之后就可以直接手工来写M函数,跟VBA学习中的从录制宏,修改宏,到手写VBA差不多!
我们把之前界面操作生成的一些List.Sum求和等按照自己的需求修改为文本拼接函数Text.Combine即可轻松按地区把姓名按照指定分隔符聚合到一列!当然我们单独的演示聚合
= Table.Group(源, {"地区"}, {{"销售员", each Text.Combine([销售员],"/")}})
我们对姓名去重-List.Distinct,实际可以去重合并
▼ 去重+合并
以上几个案例,想必大家对Table.Group已经有了一些基础的认知,完成一些常规的分组聚合应该完全OK了。铺垫了那么多,下面才是我们今天的硬核知识 - 局部分组问题!
我们都是对指定列的内容全部相同的分到一组,这其中知识把分类汇总的过程在内部完成了,但是有的需求,不做排序,我们只需要判断连续的内容。
这里就要说到Table.Group的第四和第五参数!
进阶 | Table.Group 局部分组-第三参数
为了大家更好的理解局部分组,我们先来看一个简单的案例
第三参数,决定了,我们是全局还是局部分组
第三参数:1或者不写,默认全局分组,所谓全局,就是扫描这个字段,把相同的全部分到一组,这个你直接案例分类汇总理解即可
第三参数:0,局部分组,局部相对全局,从上往下相同的分到一组,遇到不同的就停止,分为下一组
比如上面,我们看到李四有两组 811.4正好是最后三个数的合计
关于局部分组的规则,其实我们还可以自己来定义,这也是第五参数的作用,自由度越大,相对也就越难,这个道理大家应该能明白!
高阶 | Table.Group 局部分组-自定义规则
默认情况,局部分组就是从上往下,遇到不同,上面的内容就分为一组,这里的判断逻辑有两个,一个是基准点(x),一个是下一个不同(y)
具体运行规则,我们还是来看案例
案例:整理小组成员
这里我们的分组依据就是是否内容包含组,如果是我们就分成一组
= Table.Group( 源,"内容",{"组员",each _}, 0, (x,y)=>Byte.From(Text.EndsWith(y,"组")))
核心内容就是 (x,y)=>Byte.From(Text.EndsWith(y,"组")),下面我们来拆解一下运行逻辑
初始x就是首个内容,下面的每一个都是y,然后我们直接自己写的逻辑判断,是否已组结尾
判断的结果无法是True或者False,当是True的时候停止,True上面的内容分组一组,True的位置变成新的x,x下面的内容是新一次的y
联系客服