- 如何制作wps表格教程:wps表格怎么拉宽?
- wps表格新手教程:双控件Excel图表制作
- wps教程全集自学:wps表格教程如何绘制三线表格
- wps自动生成目录:WPS2016怎么设计漂亮的课程表?
excel的rank函数怎么用?源文件如下,需要对表中的成绩进行排名。
中国式排名(例如,两个人分数一样,并列第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)
各位朋友,你看懂了吗?
上一篇:wps教程零基础教程:表格rank函数怎么用 下一篇:wps怎么自动生成目录:rank函数怎么用?
郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。