使用 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 中，有没有分组函数？

当然！Office 365 现在已经有 Groupby 函数了，WPS 目前也已经更新。

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 (行字段，值，汇总方式)

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

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

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

到这里，大家应该懂了 Groupby 函数的作用了吧？

如果我们想要将姓名用顿号合并，可以直接改第三参数，使用匿名函数 Lambda。

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

最后，增加一个名次索引即可。

看到这里，可能有人会问，有没有不用函数的方法呢？

问得好！我们还可以用 PowerQuery，只需要动动鼠标改改参数，就可以搞定。

3、PowerQuery

先将数据导入到 PQ 编辑器中。

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

进入 PQ 编辑器中，选择业绩列，点击【主页】选项卡-【分组依据】，出现分组依据对话框。

新列名：姓名合并

操作：所有行

出现分组后的数据。

然后针对 each _进行更改。

如下图公式，改成对姓名列进行合并：

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

Text.Combine 函数能将姓名进行合并，类似于 Excel 中的 Textjoin 函数。

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

最后添加名次索引。

在【添加列】选项卡，单击【索引列】，【从 1】。

这时，就会出现一个索引列。

更改公式中的索引为「名次」。

到这里，PQ 做法就介绍完了。

4、最后的话

本文主要讲解的是根据业绩进行排名，且合并相同排名姓名的表格处理问题。

我一共分享了三种方法。

❶ 常规方法：

中国式排名中，重复数不占用名次，所以我们用 Unique 去重，再来判断大于等于值的个数，即可得到中国式排名。

合并同类项中，Filter 函数 + Textjoin 函数的组合搭配。

❷ 新函数 Groupby：

基于业绩字段进行分组，值数据为姓名，汇总方式将姓名合并。可以类比数据透视表进行理解。

其中有个参数可以指定排序依据，降序排序后，增加索引，就是对应的名次。

❸ PowerQuery：

思路也是分组，排序，加索引

• 分组依据

• 更改汇总方式 Text.Combine

• 将业绩进行降序

• 添加索引列，从 1 开始

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