excel排名公式 |
您所在的位置:网站首页 › 总分排名怎么算excel公式 › excel排名公式 |
在工作中,我们很常遇到需要对销售业绩或学生成绩等进行排名。使用排序的方法可以很便捷的进行排名,但是运用函数公式可能更加方便和高大上。本文列举了关于排名的几种方法: 第一部分:美式排名公式 一、用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) 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) 【思路分析】(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$11C2,……)部分: 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=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的记录的个数。 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |