使用 Excel 新函数 Groupby 快速分组

优秀的Excel函数，颠覆传统，成功之路已经开启！

大家好，我是数据处理领域的小爽~

每个季度，公司都需要对员工的业绩进行排名，如果业绩相同，则需要将姓名合并在一起，具体如下所示：

从左侧表格中的数据，如何合并成右侧的表格呢？

1、传统方法

看看这个案例，这不就是典型的中国式排名 + 合并相同项吗？

PS：中国式排名是指重复数据不占用名次，排在其之前的最大值名次减一。

首先，我们需要创建一个辅助列，用于计算中国式排名：

=SUM(--(UNIQUE($B$2$B$20)=B2))

公式很容易理解。首先对业绩数据进行去重（Unique函数），然后统计大于等于当前业绩的数量，即得到中国式排名。

然后，合并相同项，也就是汇总姓名：

=TEXTJOIN(",",,FILTER($A$2:$A$20,$C$2:$C$20=E2))

最后，使用Index+Match函数匹配业绩即可：

=INDEX($B$2:$B$20,MATCH(E2,$C$2:$C$20,0))

以上就是传统的思路。

我们也可以换个角度思考：将业绩分组，合并分组后的姓名，对分组后的业绩进行降序排序，添加名次索引。

不理解？没关系，看下面的案例 ↓

2、Groupby函数

Excel中是否有用于分组的函数？

当然！Office365和WPS都已经更新了Groupby函数。

Groupby函数的作用就是根据某些字段对数据进行分组。

第一种方法用了多个函数是吧？

使用Groupby函数，只需要一个公式：

=GROUPBY(B2:B20,A2:A20,ARRAYTOTEXT,00,-1)

先了解Groupby函数的基本语法：

=GROUPBY (row_fields，values，function，[field_headers]，[total_depth]，[sort_order]，[filter_array]) =GROUPBY(行字段，值，汇总方式，[是否包含标题]，[是否显示总计小计]，[排序依据]，[筛选依据])

看一下公式：

=GROUPBY(B2:B20,A2:A20,ARRAYTOTEXT,0,0,-1)

行字段：B2:B20，即业绩列。

值：A2:A20，即姓名列。

汇总方式：Arraytotext，将姓名列合并为一个字符串。Arraytotext函数将数组转换为字符串，默认使用逗号分隔。

[是否包含标题]：0，不包含标题。

[是否显示总计小计]：0，无总计。

[排序依据]：按值字段列排序，数字表示在结果表中的列号，正数表示升序，负数表示降序。这里设定为-1，表示降序排序。

也可以通过数据透视表来理解Groupby函数。

Groupby函数有三个必选参数。

=GROUPBY (行字段，值，汇总方式)

行字段：业绩列，对应数据透视表的行字段。

值：姓名列，对应数据透视表的值。

汇总方式：Counta函数，对应数据透视表的值计数。

希望通过上面的内容能更好地理解Groupby函数的作用。

想要用顿号合并姓名，可以直接修改第三个参数，使用Lambda匿名函数。

=GROUPBY(B2:B20,A2:A20,LAMBDA(s,TEXTJOIN("、"s))0,0-1)

最后，添加名次索引即可。

也许有人会问，有没有不使用函数的方法呢？

很好的问题！我们还可以使用PowerQuery，只需简单操作，即可完成。

3、PowerQuery操作

将数据导入PQ编辑器。

全选数据，点击【数据】选项卡，选择【来自表格 / 区域】-【确定】。

在编辑器中，选择业绩列，点击【主页】选项卡-【分组依据】，打开分组依据对话框。

新列名：姓名合并

操作：所有行

现在已经进行了分组。

然后对 each _ 进行修改。

修改如下图所示，将姓名列合并：

each Text.Combine([姓名],"、")

Text.Combine函数用于合并姓名，类似于Excel中的Textjoin函数。

选择业绩列下三角-【降序排序】。

最后，添加名次索引。

在【添加列】选项卡，点击【索引列】，选择【从1开始】。

这样就会看到一个索引列。

修改公式中的索引为“名次”。

以上就是PowerQuery的操作方法。

4、总结

本文重点介绍了根据业绩排名，合并相同排名姓名的表格处理技巧。

总共分享了三种方法。

❶ 传统方法：

在中国式排名中，重复数据不占用名次，因此使用Unique去重，再计算大于等于该值的数量，即可得到中国式排名。

在合并相同项时，采用Filter函数 + Textjoin函数的组合。

❷ 新函数Groupby：

基于业绩字段进行分组，值数据为姓名，汇总方式为姓名合并。可以视为数据透视表的一种操作。

其中一个参数指定排序方式，进行降序排序后，再添加索引，即得到相应的名次。

❸ PowerQuery：

思路与分组、排序、添加索引类似

• 分组依据

• 修改合并方式 Text.Combine

• 业绩降序排列

• 添加索引列，从1开始

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

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