![9398db3998b75fdce4c549b97f7e62c0.png](https://i-blog.csdnimg.cn/blog_migrate/205709b6799f7dee52c8f76fee7c4534.jpeg)
通过我的第 289 篇Excel原创文章
你好,我是小必老师,感谢与你在这里相遇,以Excel会友。
在Excel中,相信使用过数据透视表与分类汇总的朋友们都知道。分组聚合(求和、平均值、最大值、最小值、计数等)是Excel中最常见的操作。在工作表中这些功能只是一些最常见的功能。
在Excel Power Query中的【分组依据】功能同样是十分地很强大。但是众所周知,要实现高级功能往往离不开函数。那么今天的主角就是——Table.Group函数。(关于图形操作的分组依据小伙伴们可以自行练习,这里限于篇幅再不做过多的表述)
功能:就是指定关键字对表进行分组并对列值进行聚合运算。
语法:function(table as table,key as any,aggratedColumns as list,optional groupKind as nullbale GroupKind.Type,optional comparer as nullable function) as table
看完上面的语法其实是一头雾水,那么简单的解释就是:
第1参数为要对那个表进行分组;第2个参数是分组的关键列,为一个list,但是当只有一个关键字的时候可以直接写成文本型;第3个参数是分组聚合的列,该参数必须是一个list,并且如果对多个列进行聚合,那第每个list都需要一对大括号,即构成每一个list.第4个参数是一个可选参数,是分组的类型,共有两个参数。一个是GroupKind.Local与GroupKind.Global,也可以使用逻辑值代替,即0与1.第5个参数是同样一个可选参数,具有比较功能,是对关键字进行逻辑判断后再分组的一个参数。此参数是一个fuction,那么可扩展性非常强。看了上面的参数的介绍,更觉得深的没边了,其实不然。通过下面几个实例,你将会有更加深刻地理解,也就不会觉得这上函数有多高深了。
案例-1
如图所示,将数据源按“使用方”进行分组,对“员工姓名”进行不重复计数,对“订单数量”与“订单总金额”进行求和。
![4197bdf50e416efb65c2e1c12bad29ea.png](https://i-blog.csdnimg.cn/blog_migrate/1926633743f7492a39df818661aed37c.jpeg)
此时M公式可以写成:
= Table.Group(源, "使用方" ,
{
{"员工数量", each List.Count(List.Distinct(_[员工姓名]))},
{"订单量", each List.Sum([订单数量])},
{"总金额", each List.Sum([订单总金额])}
}
)
![5768b474d1f17111b2987e3d616a8f4c.png](https://i-blog.csdnimg.cn/blog_migrate/e7646771995ed193566c760105dca97c.png)
案例-2
上面的例子主要讲述了Table.Group函数 常规用法。那么第三参数到底有什么用途呢?关于第三个参数1表示全局分组,0表示局部分组。举一个简单的例子给大家看看,将下面的数据对“使用方”为关键字进行分组,对“订单数量”进行求和。
![8e53aa9fc80812b7b8ea35be7a880934.png](https://i-blog.csdnimg.cn/blog_migrate/63d1deba2c2a29e6abe3beb442d6722d.jpeg)
A.全局分组(GroupKind.Global)全局分组了正常的分组功能。
= Table.Group(源,"使用方",
{
{"订单量",each List.Sum([订单数量])}
},1)
或者即上面的1还可以省略。结果下图所示。
![6b1e48fbd97443df7241099a7febcf74.png](https://i-blog.csdnimg.cn/blog_migrate/71fbe1933d22c5743b3edd60b11aacc6.png)
B.局部分组(GroupKind.Local)
局部分组的第4个参数写成0就是局部分组。
= Table.Group(源,"使用方",
{
{"订单量",each List.Sum([订单数量])}
},0)
结果如下图所示。
![0078b66e885fa4cd44b42f6c8c863931.png](https://i-blog.csdnimg.cn/blog_migrate/647ca8c3cf2b3b7bc8983e0107bf97fe.png)
通过上面的全局分组与局部分组的案例可以看出,全局分组是将表中所有相同的类别进行归类;而局部分组是只对连续相同的类别进行归类。
这个功能有什么用呢,可以用来判断连续性的问题,比如判断员工的连续上班迟到的天数,商品是否断码以及球队的最大连胜场次的问题。
下面就举一个员工连续迟到的次数的问题。如图所示。
![9c416a9eee02049dc8254d1277fde046.png](https://i-blog.csdnimg.cn/blog_migrate/cf4b345ef3eea31a74473dfda22edbed.jpeg)
对于这个问题,分步来看每个步骤的结果。
首先对数据进行局部分组,即对“考勤类型”进行局部分组,即将连续相同的进行分组。如果如下图所示。
![980669c658a0e7c4c886e43427eb3ed4.png](https://i-blog.csdnimg.cn/blog_migrate/2ccc8c49df0c2674a4852186aaaa310e.jpeg)
分组完成后对“考勤类型”进行筛选,筛选出“迟到”的记录。如图所示。
![3da8675ac97454bb0d55dcb127b976e9.png](https://i-blog.csdnimg.cn/blog_migrate/6d60300c802df918130fee5ffd564df9.jpeg)
最后再进行一次分组即可完成。分组的条件为“次数里面的最大值即可”。
![a22c862c3c0b56dca0d49258c7f8a193.png](https://i-blog.csdnimg.cn/blog_migrate/1b0c0c1c8127bc6d53fb14a21de588ad.jpeg)
let
源 = Excel.CurrentWorkbook(){[Name="表5"]}[Content],
局部分组 = Table.Group(源,{"员工姓名","考勤类型"},
{
{"次数",each List.Count([考勤类型])}
},0),
筛选 = Table.SelectRows(局部分组,each [考勤类型]="迟到"),
连续最大 = Table.Group(筛选,"员工姓名",
{
{"连续迟到最大次数",each List.Max([次数])}
}
)
in
连续最大
当然对于上面的案例,还有其他更中简单的方法。这里只对Table.Group函数进行讲解与说明。
案例-3
讲了前面的两个例子后,对于分组的基本的用法有一个更加深刻地理解。那第紧接着再讲第5个参数,第5个参数是对第二个参数,即关键字的一个判断,判断值是一个逻辑值。
通常用(x,y)=>Number.From()固定格式来处理所判断后的条件值。x代表每一个分组的第一行,y为x当前行及下面的每一行。
如图所示,还是上面的例子,只是做了一个更改,就是每个姓名下面的都是空白,即null值。如何进行分组时,可向下填充。但这里为了讲解Table.Group函数 第5个参数的用法。如何对姓名及以下记录中的null值归纳在一起进行分组。
![983a7f46c6da7f837bd12cd802ecc5c9.png](https://i-blog.csdnimg.cn/blog_migrate/ea2a8ff7d370c9095631a71702c21125.jpeg)
此时可以利用该第5个参数为function的属性去扩展。
= Table.Group(源,"员工姓名",
{"次数",each List.Count([考勤类型])},0,
(x,y)=>Number.From((y is text)))
![27a72384997f1e3a48c0d5c27338abfd.png](https://i-blog.csdnimg.cn/blog_migrate/0449d9b01a42c881005ad68935e5ff4b.jpeg)
对于这个用法相比前面的用法来说就比较地复杂了。第5个参数的属性是对第2个参数,即关键字进行判断。对于上面的题目中的原理可以理解成遍历判断。
过程可以理解为:
第1次分组:
x="安原"时,y为"安原"以下的值,即y={null,null,null,null,null,null,null,null,安丁红,null,null,null,null,null,null,null,杨玉梅,null,null,null,null,null,null,null,null}.所以判断y里的每个元素是否为文本。即:第1个null值判断,y=null,结果为FALSE,不满足条件,为第1组,即"安原组";
第2个null值判断,y=null,结果为FALSE,不满足条件,为第1组,即"安原组";……
第8个null值判断,y=null,结果为FALSE,不满足条件,为第1组,即"安原组";第9个null值判断,y="安丁红",结果为TRUE,此时满足条件,完成第一次分组,再以出现的文本值的位置为第2个分组的开始,继续进行第二次分组。
第2次分组:
x="安丁红"时,分组的逻辑与上面的是一样的,此时的y为"安丁红"以下的值,即y={安丁红,null,null,null,null,null,null,null,杨玉梅,null,null,null,null,null,null,null,null}。
依据以上的逻辑进行分组,直到所有的数据被判断完成。如果用图可以表示为:
![bac492b0ec067a75665462c597360e1e.png](https://i-blog.csdnimg.cn/blog_migrate/51b0d9fbc4fc7defa26b48597afbcc7f.jpeg)
(x,y)=>的形式还可以写成其他的,不拘泥这一种。但是目前Table.Group函数的第5个参数还不是特别地明朗。期待大家更多地去挖掘与研究。