抛弃数组公式Excel新函数加持轻松提取前N名

200 次浏览

提问加Q群341401932 加微信EXCEL880B领取免费教程

有人的地方就有江湖,有江湖的地方就有争斗,争斗就要以排名的方式体现结果。

“对前n名进行xxx的操作”在各行各业都有需求。

传统的Excel处理方式:RANK或LARGE求取前n名,再排序或其他需要的操作。

Excel 2021版本有新玩法:SORT和SORTBY两个排序函数双管齐下,其乐无穷。

SORT排序

按总分由高到低排序:

=SORT(A2:D16,2,-1)

排序区域A2:D16,按其中的第2列(即总分列)排序,-1表示降序,如需升序可设置为1.

抛弃数组公式Excel新函数加持轻松提取前N名插图

SORTBY排序

 

按总分由高到低排序,如总分相同再按语文排序。

=SORTBY(A2:D16,B2:B16,-1,C2:C16,-1)

A2:D16:排序区域仍然是A2:D16

B2:B16,-1: 按B列(总分)由高到低

C2:C16,-1:按C列(语文)由高到低

注意两次排序的差别,因为多了一个排序依据,涂颜色两项发生了变化。

抛弃数组公式Excel新函数加持轻松提取前N名插图1

INDEX+SORT取前n名

完成排序后再来取前n名,只需取其前n行数据即可。

例如提取总分全三名的案例,先用SORT按总分排序,再用INDEX提取前三。

=INDEX(SORT(A2:D16,2,-1),ROW(1:3),COLUMN(A:D))

抛弃数组公式Excel新函数加持轻松提取前N名插图2

INDEX+SORTBY取前n名

如果只需要提取姓名,可以改用SORTBY只对姓名排序,注意,排序依据还是总分。再用INDEX数组公式提取前三个。

=INDEX(SORTBY(A2:A16,B2:B16,-1),ROW(1:3))

抛弃数组公式Excel新函数加持轻松提取前N名插图3

综合应用

提取单笔订单前5名的销售员并口头表扬。注意,前5名中存在销售员重复的情况,要考虑去除重复的问题。

=TEXTJOIN(“,”,1,UNIQUE(INDEX(SORTBY(A2:A19,D2:D19,-1),ROW(1:5))))

公式解析

SORTBY排序。针对名字区域排序,排序依据是销售额,-1降序。

INDEX结合ROW提取前5,ROW函数的作用是产生一个数组,也可以直接用数组{1,2,3,4,5}代替。

关于ROW的用法,此前有专门的文章介绍,点击查看

UNIQUE去除重复。

TEXTJOIN合并。这一步视需求而定,不合并也是可以的。

如需统计人数,TEXTJOIN改为COUNTA计数即可。

抛弃数组公式Excel新函数加持轻松提取前N名插图4

好了,今天的Excel小课堂就到此结束,大家赶紧实际操作联系一下吧,有不懂的问题可以留言问我!感谢关注Excel880,还请多多转发,持续关注我们呀!

郑广学Excel实战教程