有人的地方就有江湖,有江湖的地方就有争鬥,争鬥就要以排名的方式展現結果。
“對前n名進行xxx的操作”在各行各業都有需求。
傳統的Excel處理方式:RANK或LARGE求取前n名,再排序或其他需要的操作。
Excel 2021版本有新玩法:SORT和SORTBY兩個排序函數雙管齊下,其樂無窮。
SORT排序
按總分由高到低排序:
=SORT(A2:D16,2,-1)
排序區域A2:D16,按其中的第2列(即總分列)排序,-1表示降序,如需升序可設定為1.
SORT按總分排序
SORTBY排序
按總分由高到低排序,如總分相同再按國文排序。
=SORTBY(A2:D16,B2:B16,-1,C2:C16,-1)
A2:D16:排序區域仍然是A2:D16
B2:B16,-1: 按B列(總分)由高到低
C2:C16,-1:按C列(國文)由高到低
注意兩次排序的差别,因為多了一個排序依據,塗顔色兩項發生了變化。
SORTBY排序
INDEX+SORT取前n名
例如提取總分前三名的案例,先用SORT按總分排序,再用INDEX提取前三。
=INDEX(SORT(A2:D16,2,-1),ROW(1:3),COLUMN(A:D))
INDEX+SORT
INDEX+SORTBY取前n名
如果隻需要提取姓名,可以改用SORTBY隻對姓名排序,總分作為排序依據。再用INDEX數組公式提取前三個。
=INDEX(SORTBY(A2:A16,B2:B16,-1),ROW(1:3))
INDEX+SORTBY
綜合應用
提取單筆訂單前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計數即可。
提取前五名