在工作中，我们很常遇到需要对销售业绩或学生成绩等进行排名。使用排序的方法可以很便捷的进行排名，但是运用函数公式可能更加方便和高大上。本文列举了关于排名的几种方法：
第一部分：美式排名公式
一、用RANK函数两参数用法做基础排名（默认降序排列）。
特点：如果有两个数字排名相同，如下图，有两个第5名，下一名就是第7名，跳过了6。也就是说最大的次序和总数据量一致，其中的第2个第5占据了“第6”这个名次。
单列成绩排名公式：=RANK(C2,C2:C11)
双列成绩排名公式:
=RANK(H2,($H$2:$H$6,$L$2:$L$6)) ---第一列
=RANK(M2,($H$2:$H$6,$L$2:$L$7)) ---第二列
参数重点：需要注意排名的成绩区域。
函数格式说明： RANK(number,ref,[order]) ;Number 必需。需要找到排位的数字。Ref 必需。数据区域，多个区域用括号如(A1:A8，C1:C8)。忽略非数字格式。Order 可选。一数字，指明数字排位的方式。 如果 order 为 0（零）或省略，从大到小降序排列。如果 order 不为零，从大到小升序排列。
二、用COUNTIF函数
单列成绩公式:=COUNTIF($C$2:$C$11,'>'&C2) 1
双列成绩公式:
=COUNTIF($H$2:$H$6,'>'&C2) COUNTIF($L$2:$L$6,'>'&C2) 1
=COUNTIF($H$2:$H$6,'>'&L2) COUNTIF($L$2:$L$6,'>'&L2) 1
和正序排名类似，用COUNTIF函数也可以替代RANK函数实现倒序排名，公式如下：=COUNTIF($C$2:$C$11,'<'&C2) 1
三、并列不重复排名
1、RANK和COUNTIF函数组合
公式：=RANK($C3,$C$3:$C$12) COUNTIF(C$3:$C3,$C3)-1
特点：用这个公式，根据Excel行的特点，相同分数时，行数越大排名越大。如下图D6和D7单元格。
2、用COUNTIF函数也可以实现上一步的不重复排名。
公式：=COUNTIF(H$3:$H3,H3) COUNTIF($H$3:$H$12,'>'&H3)
第二部分：中国式排名公式汇总
什么是“中国式排名”？以下图为例说明，C列是学生成绩，D列为学生成绩排位。比如学生江张六和张七的成绩并列排名为5，李五和李六的并列排名是6，张五排名是7。这种排名方式在并列排名之后仍保持连续的名次，中间不会间断。世界只有中国对学生成绩采用的是排名的方法，故称“中国式排名”。
先列举一下我搜集整理的中国式排名公式：
1. 普通公式=SUMPRODUCT(($C$2:$C$11>C2)/COUNTIF($C$2:$C$11,$C$2:$C$11)) 1
2. 数组公式: =SUM((C$2:C$11>=C2)/COUNTIF(C$2:C$11,C$2:C$11))
3. 数组公式: =SUM(IF($C$2:$C$11<=C2,'',1/(COUNTIF($C$2:$C$11,$C$2:$C$11)))) 1
4. 公式=SUMPRODUCT(N(IF(FREQUENCY($C$2:$C$11,$C$2:$C$11),$C$2:$C$11,0)>=C2)
5. 普通公式=SUM(--(FREQUENCY($C$2:$C$11,IF($C$2:$C$116>=C2,$C$2:$C$11))>0))
6. 数组公式: =SUM(--IF(FREQUENCY($C$2:$C$11,$C$2:$C$11)>0,$C$2:$C$11>C2)) 1
7. 数组公式: =SUM(--IF($C$2:$C$11>=C2,MATCH($C$2:$C$11,$C$2:$C$11,)=ROW($C$2:$C$11)-1))
8. 双列公式：
=COUNT(0/FREQUENCY(CHOOSE({1,2},$C$2:$C$6,$G$2:$G$6)-C2,ROW($1:$83)-1))
=COUNT(0/FREQUENCY(CHOOSE({1,2},$C$2:$C$6,$G$2:$G$6)-G2,ROW($1:$83)-1))
注意事项：
· 有的Excel运用不了这个公式，因为你的Excel中宏的级别设成是低。或者是在安装office没有完全安装。
第三部分：中国式排名公式具体分析
一、运用SUMPRODUCT函数
1. 公式=SUMPRODUCT(($C$2:$C$11>C2)/COUNTIF($C$2:$C$11,$C$2:$C$11)) 1
SUMPRODUCT是多条件求和函数。举例说明，上图两个数组数组1和数组2，公式“SUMPRODUCT(A2:B4, C2:D4)”的作用是：两个数组的所有元素对应相乘，然后把乘积相加，即 3*2 4*7 8*6 6*7 1*5 9*3。(156)你的，明白？
再来说公式：公式的前半部分“($E$3:$E$16>$E3)”是返回一个数组：在E3:E16区域内大于E3单元格数值的个数，这里为0；公式后半部分“/ COUNTIF($C$2:$C$11,$C$2:$C$11)”可表述为“*1/ COUNTIF($C$2:$C$11,$C$2:$C$11)”， COUNTIF($C$2:$C$11,$C$2:$C$11)的值永远为一个固定值1，大家可以测试一下。所以这个公式可简化为0*1 1=1。意思就是说在E3:E16区域内没有比E3大的数，排名为1。
再举例说明单元格D3的公式为：“= SUMPRODUCT(($C$2:$C$11>C2)/COUNTIF
($C$2:$C$11,$C$2:$C$11)) 1”。在E3:E16区域内比E10大的数字有6个，故其排名为7，所以函数公式后面要加1。
2. 公式简化：
=SUMPRODUCT((C$2:C$11>=C2)/COUNTIF(C$2:C$11,C$2:C$11))
公式解释：=SUMPRODUCT(($A$2:$A$11>=A2)/COUNTIF($A$2:$A$11,$A$2:$A$11))
$A$2:$A$11>=A2：运算结果得到{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}，
意思就是找到大于等于A2的分数，返回逻辑值。如果大于等于就是真，小于等于就是假（逻辑值参与运算的时候FALSE=0,TRUE=1)
COUNTIF($A$2:$A$11,$A$2:$A$11)：运算结果得到{1;1;1;2;1;1;1;1;1;2}。
意思是61在数据中有1个，81在数据中有1个，63在数据中有1个，83在数据中有2个，以此类推。就是求出A2:A11的每个数据在A2:A11的全部数据中有几个相同的。($A$2:$A$11>=A2)/COUNTIF($A$2:$A$11,$A$2:$A$11)：运算结果为：{1;1;1;0.5;1;1;1;1;1;0.5}，这样两个0.5加起来就是1，这样就做到相同分数只计算一次排名。
这也是大家所说的中国式排名，在中国人的习惯中，无论有几个并列第2 名，之后的排名仍应该是第3 名，即并列排名不占用名次。
3. 降序公式
=SUMPRODUCT((C$2:C$11<=C2)/COUNTIF(C$2:C$11,C$2:C$11))
4. 公式：=SUMPRODUCT(N(IF(FREQUENCY($C$2:$C$11,$C$2:$C$11),$C$2:$C$11,0)>=C2)
【思路分析】（1） 使用FREQUENCY将数组中不重复出现的元素标识出来，第一次出现的元素标识出现次数，重复出现的元素标识0，如FREQUENCY($C$2:$C$11,$C$2:$C$11)返回数组{2;1;2;0;1;1;0;1;1;0}
（2） 使用IF条件配合第1步的标识使原数组返回不重复元素与0组成的数组，即公式中的IF(FREQUENCY($C$2:$C$11,$C$2:$C$11), $C$2:$C$11,0)返回数组{8;9.5;9;0;8.5;6;0;7.5;7;0}
（3） 将第2步结果与数组中的每一个元素比较，判断是否大于等于其数值，返回一串逻辑值构成的数组即IF(FREQUENCY($C$2:$C$11,$C$2:$C$11), $C$2:$C$11,0)>=C2返回结果为{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}
（4） 使用N函数将逻辑值TRUE和FALSE分别转化为1和0，即{1;1;1;0;1;0;0;0;0;0}
（5） 最后使用SUMPRODUCT函数统计第4步中的求和结果，即中国式排名的名次。
这四个公式虽然也是数组函数，但因为是Sumproduct()所以不需要数组三键就能正确计算。
二、运用SUM IF函数组合
1. 数组公式:
=SUM(IF($C$2:$C$11<=C2,'',1/(COUNTIF($C$2:$C$11,$C$2:$C$11)))) 1
这是一个SUM(IF(Logical-test[value-if,[value-if-false），countif（rang，criteria））的数组公式，按ctrl shift enter三键结束。下拉复制公式即可得出其余的排名。
公式解释： COUNTIF($C$2:$C$11,$C$2:$C$11)部分：这是一个数组运算用法，它的运算过程是：
COUNTIF($C$2:$C$11,C2)
COUNTIF($C$2:$C$11,C3)
COUNTIF($C$2:$C$11,C4)
……
分别统计B2、B3、B4单元格在B2：B6区域中出现的次数。得到结果为：1,1,1,2,2。其中“1”代表此单元格中的内容在B2：B6区域中只出现一次，即没有重复；“2”代表此单元格中的内容在B2：B6区域里重复2次。这一步的操作，可以得到数据是否有重复和以及重复的次数。
1/ COUNTIF($C$2:$C$11,$C$2:$C$11)部分：在公式编辑栏选中这部分公式，按F9键查看运算结果为：{1;1;1;0.5;0.5}。
IF($C$2:$C$11>C2,……)部分：
IF第一参数：$C$2:$C$11>C2的结果是：{FALSE;TRUE;FALSE;TRUE;TRUE}，意思是B2单元格中的内容分别和C2：C11区域内的各个单元格内容进行大小比较。
“IF($C$2:$C$11>B2, 1/ COUNTIF($C$2:$C$11,$C$2:$C$11))”，这里IF省略了第三参数，因此当得到FALSE时，此时将返回结果“FALSE”，当得到TRUE时，此时将返回对应的结果，得到的结果是{FALSE;1;FALSE;0.5;0.5}。
接着SUM函数对IF函数内的结果进行加总，得到结果“3”。为什么还要再加上“1”呢？原因是IF函数内的测试条件是“>”，对于“B2：B6区域”里的最大值“72”而言，得到的结果是{FALSE;FALSE;FALSE;FALSE;FALSE}，那么SUM函数计算得到的值就是“0”，显然排名第0位，不符合常识，因此要额外加上“1”。
这个中国式排名公式的核心部分就是：“1/ COUNTIF($C$2:$C$11,$C$2:$C$11)”，目的是避免重复计算相同项。
2. 降序公式
{=SUM(IF($C$2:$C$11<=C2,1/(COUNTIF($C$2:$C$11,$C$2:$C$11)),''))}
三、运用SUM COUNTIF函数
1.数组公式=SUM((C2<=C$2:C$11)/COUNTIF(C$2:C$11,C$2:C$11)) }
函数关键部分是 1/COUNTIF(C$2:C$11,C$2:C$11)。countif（Rng,Cel）函数的作用是【统计】 Rng区域中符合Cel【相同条件】的【元素个数】，即每个元素的重复次数。
如果Cel参数是一个数组区域，那么将返回一个数组结果：即： COUNTIF(C$2:C$11,C$1:C$11)的含义，就是分别以第2参数C$2:C$11区域中的7个单元格为条件，每次查找第1参数C$2:C$11区域中=C$2 或C$3 或C$4…… 或C$11的元素数……返回一个数组结果如：{1,1,2,1,2,1,1}即，区域中每个元素的重复次数。
显然1代表重复次数=1，即这个元素在整个区域中只出现了1次。
2代表重复次数=2，即这个元素在整个区域中一共出现了2次。
…………
到这里就可以发现一个重要规律： 【区域中，重复n次的元素个数=n个】。即，
重复次数=1的 元素数只有1个，
重复次数=2的 元素数会有2个，
重复次数=3的 元素数会有3个，
……
重复次数=5的 元素数会有5个
因此，接下来引入一个重要的计算结果：1/COUNTIF(C$2:C$11,C$2:C$11)
例如： COUNTIF(C$2:C$11,C$2:C$11)= {1,1,2,1,2,1,1}时，
1/ COUNTIF(C$2:C$11,C$2:C$11) 计算就返回 = {1,1,0.5,1,0.5,1,1}
那么把这个数组结果相加，你就会惊奇地发现：总和=区域中不重复元素的个数
其实原理很简单，相当于对于每一个不重复元素来说，如果这个元素在区域中一共重复了n次，那就一定会有n个元素。
那么，对于每一个元素COUNTIF（）结果=n，而其1/COUNTIF()结果=1/n，因为一共有n个元素，那么它们的总和=n*（1/n）=1
…………
即，=Sum（1/Countif(rng,cel)) 得到的计算结果，就会是rng区域中不重复元素的个数。进一步，如果我们需要统计比这个数大的个数，当然使用(C$3:C$9>=C3)作为数组计算条件就可以了。
因此，最后的合成公式就是：SUM((C3<=C$2:C$11>=)/COUNTIF(C$2:C$11,C$2:C$11))
即，用sum（）统计以符合 (C$2:C$11>=C3)为条件，用/COUNTIF(C$2:C$11,C$2:C$11)方法统计区域中不重复元素的个数最后就得到了【中国式排名】的结果。
2. 降序公式：
{=SUM((C2>=$C$2:$C$11)/COUNTIF($C$2:$C$11,$C$2:$C$11))}
四、SUM FREQUENCY组合
1. 数组公式:
{=SUM(--IF(FREQUENCY($C$2:$C$11,$C$2:$C$11)>0,$C$2:$C$11>C2)) 1}
2. 普通公式:
=SUM(--(FREQUENCY($C$2:$C$11,IF($C$2:$C$116>=C2,$C$2:$C$11))>0))
五、SUM MATCH组合
{=SUM(--IF($C$2:$C$11>=C2,MATCH($C$2:$C$11,$C$2:$C$11,)=ROW($C$2:$C$11)-1))}
六、 统计双列成绩排名公式
=COUNT(0/FREQUENCY(CHOOSE({1,2},$H$2:$H$6,$L$2:$L$6)-H2,ROW($1:$100)-1)) =COUNT(0/FREQUENCY(CHOOSE({1,2},$H$2:$H$6,$L$2:$L$6)-L2,ROW($1:$100)-1))
七、Excel用SUMPRODUCT实现多条件排名
下图是汇总后的成绩表，三所学校各个专业的学生成绩都放到了一个工作表中。为了做好成绩分析，要求以总分为依据做好两个排名：一是排出每位学生在全市相同专业的学生中的名次；二是排出每位学生在本校本专业中的名次。
可以将数据按专业、按学校分别筛选出来复制到不同的工作表中，然后在不同的工作表中用RANK函数进行排序。如果使用SUMPRODUCT函数来完成这个有条件的排名工作，就方便多了。具体实现过程如下：
1、准备工作：
选定总分所在的H2:H1032单元格区域，点击功能区“公式”选项卡“定义的名称”功能组中“定义名称”按钮，在弹出的“新建名称”对话框“名称”输入框中输入为此区域定义的名称“zongfen”。此时，对话框下方的“引用位置”后的输入框中已经自动输入我们选定的单元格区域“=对口!$H$2:$H$1032”，如图2所示。
按同样的方法，选定学校所在单元格区域I2:I1032、专业所在单元格区域J2:J1032，分别为它们指定名称“xuexiao”和“zhuanye”。完成后，这准备工作就算是结束了。
2、排定名次：
在K1单元格输入标题“按专业排名”。点击K2单元格，输入公式
“=SUMPRODUCT((zhuanye=$J2)*($H2<zongfen)) 1”，按下回车键，结果出来了吧？向下拖动其填充句柄至最后一行，OK，按专业排名就算完成了。
在K1单元格输入标题“按专业排名”。点击K2单元格，输入公式“=SUMPRODUCT((zhuanye=$J2)*($H2<zongfen)) 1”，按下回车键，结果出来了吧？向下拖动其填充句柄至最后一行，OK，按专业排名就算完成了。
如果您也遇到类似的问题，比如平行班的成绩汇总在一张工作表中，而我们又需要学生的班内名次，那么不妨照此办理一回，呵呵，那效果，真的是谁用谁知道啊。
说明：这里我们使用的是SUMPRODUCT函数的多条件计数功能。
语法：＝SUMPRODUCT((条件1)*(条件2)*(条件3)* …(条件n))
作用：统计同时满足条件1、条件2到条件n的记录的个数。
联系客服
