当前位置:首页 > 办公 > Excel教程 > 正文内容

成绩排名,原来有4种方法!你是不是只知道Rank函数?

访客2个月前 (09-02)Excel教程58

源文件如下,需要对表中的成绩进行排名。

 

中国式排名(例如,两个人分数一样,并列第1,两个第1,后面就是第2名)

非中国式排名(例如,两个人分数一样,并列第1,两个第1,后面就是第3名)

-01- 中国式排名

方法一:SUMPRODUCT+ COUNTIF 排名

 

=SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7))+1。 即在G2单元格输入公式后,同时按ctrl+shift+enter,然后下拉即可完成。

函数解析

sumproduct有两个英文单词组成,sum是和,product是积,所以是乘积之和的意思。COUNTIF是计数函数。

函数解释

= COUNTIF(B$2:B$7,B$2:B$7) 表示的是分数出现的次数,和=COUNTIF($B$1:$B$7,B1)表达意思是一样的。例如,92分出现的是2次,因此结果是2;其他分数出现的是1次,因此返回的结果是1

 

在SUMPRODUCT中,数组公式 B$2:B$7>B2 表示的是由TRUE和FALSE组成的逻辑数组{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}转化为1和0的数组{0;0;0;0;0;0}。

因此G2单元格中的公式,=SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7))+1 是对{0;0;0;0;0;0}/{2;1;1;1;1;2}+1求和, 即 0/2+0/1+0/1+0/1+0/1+0/2+1=1

因此G3单元格中的公式,=SUMPRODUCT((B$2:B$7>B3)/COUNTIF(B$2:B$7,B$2:B$7))+1 是对{1;0;0;0;0;1}/{2;1;1;1;1;2}+1求和, 即 1/2+0/1+0/1+0/1+0/1+1/2+1=2

因此类推即可。

方法二:COUNTIF+ IF 排名

 

=SUM(–IF(B$2:B$7>B2,1/COUNTIF(B$2:B$7,B$2:B$7)))+1。同时按ctrl+shift+enter,然后下拉即可完成。

函数解释

= COUNTIF(B$2:B$7,B$2:B$7) 表示的是分数出现的次数,和=COUNTIF($B$1:$B$7,B1)表达意思是一样的。例如,92分出现的是2次,因此结果是2;其他分数出现的是1次,因此,返回的结果是1。

 

在if中,数组公式 B$2:B$7>B2 表示的是由TRUE和FALSE组成的逻辑数组{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}转化为1和0的数组{0;0;0;0;0;0}。

因此G2单元格中的公式,=SUM(–IF(B$2:B$7>B2,1/COUNTIF(B$2:B$7,B$2:B$7)))+1是对0+0+0+0+0+0+1=1

因此G3单元格中的公式,=SUM(–IF(B$2:B$7>B3,1/COUNTIF(B$2:B$7,B$2:B$7)))+1 即 1/2+0+0+0+0+1/2+1=2

类推即可。

方法三:MATCH + ROW + IF 函数

 

=SUM(–IF(B$2:B$7>=B2,MATCH(B$2:B$7,B$2:B$7,)=ROW($2:$7)-1))。同时按ctrl+shift+enter,然后下拉即可完成。

公式解读

B$2:B$7>=B2,表示的是数组{92; 91; 89;80;82;92}>=92, 表示的是由TRUE和FALSE组成的逻辑数组{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}

MATCH(B$2:B$7,B$2:B$7,),表示的是数字第一次出现的行数,因此表示的数组为{1;2;3;4;5;1}

ROW($2:$7)-1 表示的是{2;3;4;5;6;7}-1= {1;2;3;4;5;6}

因此, IF(B$2:B$7>=B2,MATCH(B$2:B$7,B$2:B$7,)=ROW($2:$7)-1) 表示为

IF({TRUE;FALSE;FALSE;FALSE;FALSE;TRUE},{1;2;3;4;5;1}={1;2;3;4;5;6}) =IF({TRUE;FALSE;FALSE;FALSE;FALSE;TRUE},{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE})

注意:true表示数值1,false表示数值为0

因此=SUM(–IF(B$2:B$7>=B2,MATCH(B$2:B$7,B$2:B$7,)=ROW($2:$7)-1)) = 1

-02- 非中国式排名——rank函数

可以发现这个函数最简单,雷哥就不多解释。

=RANK (B2, B:B)

 

各位朋友,你看懂了吗?

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/1332.html

标签: excel
分享给朋友:

相关文章

打开Excel 2016里面都是灰色没有数据怎么办?

打开Excel 2016里面都是灰色没有数据怎么办?

  office是许多用户在日常生活中经常使用到的软件,而Excel作为office中的重要组件,许多用户也是经常使用 到。但是最近有不少同学问我,自己的电脑在使用的时候打开Excel...

Excel表格怎么快速的对数据进行排列组合?方方格子工具教你快速掌握排列组合技巧

Excel表格怎么快速的对数据进行排列组合?方方格子工具教你快速掌握排列组合技巧

  Excel表格怎么快速的对数据进行排列组合?许多同学在Excel表格中录制好数据之后经常都会为自己的数据进行一个排列操作,这样的操作会让用户的Excel表格看起来更加的清晰,那么我...

ROW与ROWS函数,生成连续的整数

ROW与ROWS函数,生成连续的整数

有时候,我们希望公式中有一部分能够在该公式向下复制到连续的行时,生成一系列连续的整数。 一个经典的示例是使用INDEX/SMALL组合的公式,例如下面的公式1: =INDEX($B$1:$B$10,SMALL(IF($A...

Excel 2019中使用条件格式分析数据的7类方法教学

Excel 2019中使用条件格式分析数据的7类方法教学

  我们在使用Excel的时候有些时候会使用到条件格式功能,条件格式功能是基于条件更改单元格的外观,使用条件格式可以直观地查看和分析数据,发现关键问题及数据的变化,趋势等。那么我们怎么...

average函数怎么用?Excel下average函数的使用方法

average函数怎么用?Excel下average函数的使用方法

  AVERAGE函数,意思是EXCEL表格中的计算平均值函数。关于sum函数的用法有很多,有不少朋友问到AVERAGE函数的用法。那么,在sum函数里的AVERAGE函数应该怎么运用...

怎么用Excel 2007计算平均值和标准差

怎么用Excel 2007计算平均值和标准差

在Excel 2007中计算平均值和标准差非常简单,只用几分钟就能完成。 步骤 方法 1计算平均值 在Excel中使用“AVERAGE”函数来计算一组数据的平均值。在Excel电子表格中,输入一些数据。选择你想要用来显...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。