Excel排名怎么做?我用8年经验总结出这几个实用方法
Excel排名怎么做?我用8年经验总结出这几个实用方法|z35资讯网
很多人一提到Excel排名,脑子里第一反应就是点一下排序按钮。但我见过太多人排完序之后,原始数据的顺序全乱了,想恢复都恢复不了。还有人排名完发现名次不对,因为没考虑并列的情况。这些问题我当年都踩过,今天就把我这些年用Excel做排名的经验整理一下。
先说一下最常见的场景。你手上有一张销售表,A列是姓名,B列是销售额,现在要在C列显示出每个人的名次。这种情况我一般用RANK函数,公式是=RANK(B2,$B$2:$B$20),往下拉就行。注意那个美元符号,不加的话下拉之后引用区域会跟着跑,结果全错。这个坑我当年踩过,排查了半小时才发现问题。

RANK函数默认是从大到小排,也就是数值越大名次越靠前。如果你想反过来,数值越小名次越高,比如统计用时或者误差,那就加第三个参数,写成=RANK(B2,$B$2:$B$20,1)。这个参数写0或者省略就是从大到小,写1就是从小到大。

并列排名怎么处理
实际工作中经常遇到分数一样的情况。比如两个人都是95分,RANK默认会给他们同样的名次,比如都是第2名,然后下一个分数直接跳到第4名,中间的第3名就空着了。这种叫"美式排名",大多数考核场景够用。但有些公司要求不能跳号,第2名并列之后下一个人算第3名,这种叫"中式排名",就得用别的写法。

中式排名的公式稍微复杂点,可以用=SUMPRODUCT((B$2:B$20>B2)/COUNTIF(B$2:B$20,B$2:B$20))+1。这个公式我第一次看也是一脸懵,后来理解了逻辑就通了:它统计的是比当前值大的不同数值有几个,重复的只算一次,然后加1就是名次。现在新版本的Excel还有RANK.EQ和RANK.AVG,EQ跟老RANK一样,AVG是并列时取平均名次,比如并列第2的两个人都算2.5名。用哪个看你自己的需求。
多条件排名和动态排名
有时候排名不是看单一指标。我做过一个项目,要先按部门分组,组内再按业绩排名。这时候可以用COUNTIFS配合,公式写成=COUNTIFS(部门列,当前部门,业绩列,">"&当前业绩)+1。这样每个部门内部单独排名,互不干扰。
还有一种情况是数据会不断更新,每加一行排名就要重算。这时候建议把区域做成表格(Ctrl+T),或者用整列引用,比如$B:$B,这样新增的数据会自动纳入计算范围。不过整列引用数据量大的时候会卡,几十万行的话还是老老实实指定区域。
如果只是想快速看个排名,不想写公式,那排序功能也够用。选中数据区域,数据选项卡里点排序,选好主要关键字就行。但记得先复制一份原始数据,排序是不可逆操作,排完想还原顺序很麻烦。我一般会先在最左边插一列序号,从1拉到末尾,这样排乱了还能按序号排回来。
- RANK:最常用,默认降序,加参数1可升序,并列会跳号
- RANK.EQ / RANK.AVG:新版本函数,功能更细,AVG取平均名次
- SUMPRODUCT+COUNTIF:实现中式排名,并列不跳号
- COUNTIFS:多条件分组排名,按部门、班级等维度分开算
- 排序功能:临时看排名用,操作前务必备份数据
最后提一个新手常犯的错。RANK函数的引用区域一定要用绝对引用,或者用表格结构化引用。我见过有人写=RANK(B2,B2:B20),下拉之后变成=RANK(B3,B3:B21),排名结果看着好像对,其实完全乱套。这种错误很隐蔽,因为数字还在合理范围内,不仔细核对根本发现不了。养成加$的习惯,能省很多返工的时间。
总的来说,Excel排名这件事不难,难的是根据具体场景选对方法。日常考核用RANK够了,要处理并列跳号就上SUMPRODUCT,分组排名用COUNTIFS。把这几招练熟,基本能应付工作中九成以上的排名需求。