Excel中国式排名公式

其他回答

摘自：http://club.excelhome.net/thread-500420-1-1.html

所谓中国式排名，是指排名时如果出现并列的情况，并列者不占用名次。

例如有两个并列第2名，则下一个名次还是为第3 名，而不是排到第4 名，这似乎更符合中国人的习惯。而在Excel中用RANK函数排序时，并列者将占用名次，因而会导致后面有的名次会出现空缺。在许多情况下，我们可能要用到中国式排名。下面是几种进行中国式排名的方法：

   一、用辅助列

   在能够改变表格结构的情况下，可以先对表格按降序的方式排序，然后在辅助列中输入公式，获得中国式排名。    如下图A1:D19区域中是某公司年度考核成绩表，总成绩在D列中，现在要对D列进行中国式排名。

        1.选择D2:D19列中的任一单元格，单击工具栏中的“Z→A”按钮，使总成绩按降序排序。

   2.在E2单元格中输入公式：

   =RANK(D2,$D$2:$D$19)    然后向下拖，将公式填充到E3:E19区域。

   3.在F2单元格中输入数值“1”。然后在F3单元格中输入公式：    =IF(E3=E2,F2,F2+1)    然后向下拖，将公式填充到F3:F19区域，即可得到中国式排名。    这种方法操作简单，无需复杂的公式，缺点是要使用辅助列。

   二、使用公式

   用下面的几个公式都可以获得中国式排名，还是以上图为例，在单元格中输入公式并向下拖：

   =SUMPRODUCT((D$2:D$19>D2)*(1/(COUNTIF(D$2:D$19,D$2:D$19))))+1

   或下面的几个数组公式，公式输入完毕后按Ctrl+Shift+Enter结束：

   =SUM(IF(D$2:D$19>D2,1/COUNTIF(D$2:D$19,D$2:D$19)))+1

   =SUM(--(FREQUENCY(D$2:D$19,IF(D$2:D$19>=D2,D$2:D$19))>0))

   =SUM(--IF(D$2:D$19>=D2,MATCH(D$2:D$19,D$2:D$19,)=ROW($2:$19)-1))

摘自：http://www.toberp./html/support/1113926598.html

　　Excel中排名，大家习惯分为美式排名和中国式排名。

　　美式排名通常使用RANK函数，用法比较简单，可以参考文章：rank函数实例讲解。　　什么是excel中国式排名呢？咱们中国人的习惯，比如无论有几个并列第3名，之后的排名仍应该是第4名，即并列排名不占用名次，这就是中国式排名。

　　下面这个案例，也是IT部落窝论坛的excel中国式排名练习题，语文成绩有两个72分，并列第一名，随后的66分为第二名……在C2单元格写公式，完成中国式排名。

　　中国式排名，其实就是对一个数在一组数据中排名第几的统计，重复数排名相同，比它小的最大数的排名只低一级。通常可以使用countif，frequency和SUMPRODUCT等函数结合实现。

　　下面提供几种相关的中国式排名解法：

　　中国式排名解法一：

　　在C2单元格输入公式，

=SUM(IF($B$2:$B$6>B2,1/COUNTIF($B$2:$B$6,$B$2:$B$6)))+1，

然后按ctrl+shift+enter三键结束。下拉复制公式即可得出其余的排名。

　　公式解释：

　　COUNTIF($B$2:$B$6,$B$2:$B$6)部分：这是一个数组运算用法，它的运算过程是：　　COUNTIF($B$2:$B$6,B2)　　COUNTIF($B$2:$B$6,B3)　　COUNTIF($B$2:$B$6,B4)　　……　　分别统计B2、B3、B4单元格在B2：B6区域中出现的次数。得到结果为：1,1,1,2,2。其中“1”代表此单元格中的内容在B2：B6区域中只出现一次，即没有重复；“2”代表此单元格中的内容在B2：B6区域里重复2次。这一步的操作，可以得到数据是否有重复和以及重复的次数。

　　1/COUNTIF($B$2:$B$6,$B$2:$B$6)部分：　　在公式编辑栏选中这部分公式，按F9键查看运算结果为：{1;1;1;0.5;0.5}。　　IF($B$2:$B$6>B2,……)部分：　　IF第一参数：$B$2:$B$6>B2的结果是：{FALSE;TRUE;FALSE;TRUE;TRUE}，意思是B2单元格中的内容分别和B2：B6区域内的各个单元格内容进行大小比较。

　　“IF($B$2:$B$6>B2,1/COUNTIF($B$2:$B$6,$B$2:$B$6))”，这里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($B$2:$B$6,$B$2:$B$6)”，目的是避免重复计算相同项。

　　中国式排名解法二：

　　C2单元格输入公式：=SUMPRODUCT(($B$2:$B$6>=B2)/COUNTIF($B$2:$B$6,$B$2:$B$6))，下拉复制即可。

　　这个公式的思路也是不重复计数。

　　中国式排名解法三：　　使用FREQUENCY 函数的数组解法完成。B2公式为：=SUM(--(FREQUENCY(B$2:B$6,IF(B$2:B$6>=B2,B$2:B$6))>0))，三键结束。

　　中国式排名解法四：　　=SUMPRODUCT((B$2:B$6>B2)*(1/(COUNTIF(B$2:B$6,B$2:B$6))))+1

　　中国式排名解法五：　　=SUM(--IF($B$2:$B$6>=B2,MATCH($B$2:$B$6,$B$2:$B$6,)=ROW($2:$6)-1))    

用公式：=SUM((B$2:B$9>=B2)*(1/COUNTIF(B$2:B$9,B$2:B$9)))，获得中国式排名结果。

中国式排名特点：会出现并列名次。比如说：4,2,2,1。中国式排名次序是：1,2,2,3。也就是并列第二名后是第三名，不会漏掉名次的数字。