美式排名

特点：如果有两个数字排名相同，如下图，有两个第5名，下一名就是第7名，跳过了6。也就是说最大的次序和总数据量一致，其中的第2个第5占据了“第6”这个名次。

单列成绩排名公式：=RANK(C2,C2:C11)   

图片

双列成绩排名公式:    

=RANK(J2,($J$2:$J$6,$N$2:$N$6))    ---第一列

=RANK(N2,($J$2:$J$6,$N$2:$N$6))   ---第二列

图片

中式排名

图片

什么是“中国式排名”？

以上图为例说明，C列是学生成绩，D列为学生成绩排位。比如学生江张六和张七的成绩并列排名为5，李五和李六的并列排名是6，张五排名是7。这种排名方式在并列排名之后仍保持连续的名次，中间不会间断。

世界只有中国对学生成绩采用的是排名的方法，故称“中国式排名”。

图片

注意事项：

· 有的Excel运用不了这个公式，因为你的Excel中宏的级别设成是低。或者是在安装office没有完全安装。

图片

一、运用SUMPRODUCT函数 

1.公式一：=SUMPRODUCT(($C$2:$C$11>C2)/COUNTIF($C$2:$C$11,$C$2:$C$11)) 1

公式的前半部分$C$2:$C$11>C2是返回一个数组：在C2:C11区域内大于C2的 单元格数值的个数，这里为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。

※公式简化：

=SUMPRODUCT((C$2:C$11>=C2)/COUNTIF(C$2:C$11,C$2:C$11))

这也是大家所说的中国式排名，在中国人的习惯中，无论有几个并列第2 名，之后的排名仍应该是第3 名，即并列排名不占用名次。

※ 降序公式

=SUMPRODUCT((C$2:C$11<=C2)/COUNTIF(C$2:C$11,C$2:C$11))

2. 公式二：=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步中的求和结果，即中国式排名的名次。

二、运用SUM IF函数组合

=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)”，目的是避免重复计算相同项。

※ 降序公式

{=SUM(IF($C$2:$C$11<=C2,1/(COUNTIF($C$2:$C$11,$C$2:$C$11)),''))}

三、运用SUM COUNTIF函数

=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)方法统计区域中不重复元素的个数最后就得到了【中国式排名】的结果。

※ 降序公式：

{=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))  

图片

图片