excel排名 | 您所在的位置:网站首页 › excel函数公式排名次并列往后 › excel排名 |
今天给大家讲利用SUMPRODUCT函数实现两种常用的排名:名次连续的中国式排名和组内排名。 第一种排名:名次不间断的中国式排名RANK和RANK.EQ排名次,但这两个排名函数的结果是:如果有相同的数值会出现相同的排名,再继续排下去,会出现名次“间断”的情况,如下图,两个第三名后,会直接出现第五名,而没有第四名,这种“不连续”名次,成为美式排名,在我们国家不受欢迎。 美式排名名次间断 如何实现名次不间断的“中式排名”? 【方法实现】 可以借助SUMPRODUCT 函数。 在D2中输入公式: =SUMPRODUCT(($B$2:$B$7>=B2)/COUNTIF($B$2:$B$7,$B$2:$B$7)),确定,公式向下填充,即可实现。 如下图所示: 中式不间断排名 【函数解释】 ($B$2:$B$7>=B2):将B2:B7单元格数值依次与B2对比,如果大于等于B2,返回TRUE,否则返回FALSE,所以此部分返回数值:{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE},即:{1;0;0;0;0;0} COUNTIF($B$2:$B$7,$B$2:$B$7):在B2:B7单元格区域依次计算B2:B7每个单元格数值出现的个数,返回数组:{1;1;2;2;1;1}; SUMPRODUCT(($B$2:$B$7>=B2)/COUNTIF($B$2:$B$7,$B$2:$B$7)):把上述两个数组对应数值相乘,再求和,即得名次1。 再对该公式进行进一步解释: 当向下填充到D5单元格,公式变化为:=SUMPRODUCT(($B$2:$B$7>=B5)/COUNTIF($B$2:$B$7,$B$2:$B$7)) ($B$2:$B$7>=B4),返回值是:{TRUE;TRUE;TRUE;TRUE;FALSE;FALSE},即:{1;1;1;1;0;0} COUNTIF($B$2:$B$7,$B$2:$B$7),返回值是:{1;1;2;2;1;1} 总公式SUMPRODUCT(($B$2:$B$7>=B5)/COUNTIF($B$2:$B$7,$B$2:$B$7)),即是:SUMPRODUCT({1;1;0.5;0.5;0;0}),即得名次3。 第二种排名:分组排名如下图所示样表,员工分A、B、C要求不改变现有排序的情况下,计算出每位员工在自己组内的排名: 计算组内排名 【方法实现】 在E2单元格输入公式: =SUMPRODUCT(($C$2:$C$14=C2)*($D$2:$D$14>=D2)/COUNTIFS($C$2:$C$14,$C$2:$C$14,$D$2:$D$14,$D$2:$D$14)),确定,然后公式向下填充,即可得组内排名。 实现组内排名 【公式解析】 $C$2:$C$14=C2:在C2:C14区域的 每一个单元格与C2相比较,如果相等返回TRUE,否则返回FALSE。本部分返回数组: {TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}(数组一) $D$2:$D$14>=D2:在D2:D14区域的 每一个单元格与D2相比较,如果大于或等于D2返回TRUE,否则返回FALSE。本部分返回数组: {TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE ;FALSE }(数组二) COUNTIFS($C$2:$C$14,$C$2:$C$14,$D$2:$D$14,$D$2:$D$14):查找C列D列从第2行到第14行每一行出现的次数。本部分得数组: {2;1;1;2;1;1;1;1;1;1;1;1;1}(数组三) =SUMPRODUCT(($C$2:$C$14=C2)*($D$2:$D$14>=D2)/COUNTIFS($C$2:$C$14,$C$2:$C$14,$D$2:$D$14,$D$2:$D$14)):数组一*数组二/数组三,得到的数组{0.5;0;0;0.5;0;0;0;0;0;0;0;0;0},数组内数据加和,即得第一位的排名。 |
CopyRight 2018-2019 实验室设备网 版权所有 |