在工作中，我们很常遇到需要对销售业绩或学生成绩等进行排名。使用排序的方法可以很便捷的进行排名，但是运用函数公式可能更加方便和高大上。本文列举了关于排名的几种方法：

第一部分：美式排名公式

一、用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,'

三、并列不重复排名

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

在K1单元格输入标题“按专业排名”。点击K2单元格，输入公式“=SUMPRODUCT((zhuanye=$J2)*($H2

如果您也遇到类似的问题，比如平行班的成绩汇总在一张工作表中，而我们又需要学生的班内名次，那么不妨照此办理一回，呵呵，那效果，真的是谁用谁知道啊。

说明：这里我们使用的是SUMPRODUCT函数的多条件计数功能。

语法：＝SUMPRODUCT((条件1)*(条件2)*(条件3)* …(条件n))

作用：统计同时满足条件1、条件2到条件n的记录的个数。