如何不排序提取排名前三的学生姓名?

编按:这是高手的交流区,小白免进!本期题目是不提前排序,直接采用函数提取成绩前三的学生姓名。难点在于怎么排除同分数的干扰。

统计前三名学生姓名是很多老师每次考试后必须要做的工作。统计成绩时最麻烦的就是碰到相同分数的,如果同样是100分到底算谁第一名呢?这不今天我们的学员LXP老师就遇到这样问题了,她希望返回班级所有学生中前三名学生名称,如果遇到相同的分数按照先后排列顺序返回。

如下表:A列是学生姓名,B列是学生成绩信息,现在需要在F列返回前三名的学生姓名。

如果没有相同分数,可以通过以下函数公式完成学生姓名提取:

=INDEX($A$2:$A$68,MATCH(LARGE($B$2:$B$68,ROW(A1)),$B$2:$B$68,0))

但正如下图所展示的那样,但存在相同分数的时候,这个公式就不管用了,出现了重名:

按照LXP老师的要求相同分数按排列顺序先后记名次,我有两种解决方法:

方法一:

函数公式:=INDEX($A$2:$A$67,MATCH(LARGE($B$2:$B$67,ROW(A1)),INDEX(((COUNTIFS($F$1:F1,$A$2:$A$67)>0)*-100+1)*($B$2:$B$67),),0),0)

函数思路:

INDEX(((COUNTIFS($F$1:F1,$A$2:$A$67)>0)*-100+1)*($B$2:$B$67),)这部分函数用于将B2:B67区域中的分数进行重新构建。譬如,成绩99的第一名学生姓名返回后,他的分数就会被乘以-99,就不会再以99分的分数去争抢第二名。

方法二:

首先我们先通过函数公式=LARGE($B$2:$B$68,ROW(A1))统计前三名分数。

然后通过以下函数公式完成前三名学生姓名查询:

=OFFSET(A$1,SMALL(IF($B$2:$B$68=F2,ROW($1:$67)),COUNTIF($F$2:F2,F2)),)(数组公式,按ctrl+shift+enter三键完成)

函数思路:

用IF函数把所有等于某个分数的行号找出,然后通过COUNTIF($F$2:F2,F2)统计F2:F4中该分数出现的次数,并由此确定small函数返回第几小的行号,这样就避免了分数相同,姓名相同。

不知对于这样不排序提取前三名姓名的需求,你还有哪些做法?欢迎留言分享。

(0)

相关推荐