学习Excel技术,关注微信公众号:
excelperfect
Q:我有一个学生成绩表,如何使用公式获取成绩排在最后5名的学生姓名,如下图1所示。
图1
A:可以对学生成绩从小到大进行排序,这样就可以看出排在最后的学生是哪几位了。将活动单元格置于列B中,单击功能区“开始”选项卡“编辑”组中的“排序和筛选——升序”,结果如下图2所示。
图2
也可以使用公式来实现,如下图3所示,可随指定的人数而变化。
图3
在单元格E2中输入数组公式:
=IFERROR(INDEX($A$1:$A$12,SMALL(IF($B$2:$B$12<=SMALL($B$2:$B$12,$D$2),ROW($A$2:$A$12),FALSE),ROW(A1))),"")
下拉至相应的单元格为止。
公式中:
SMALL($B$2:$B$12,$D$2)
得到指定倒数名次的分数,示例中为倒数第5名,即92。
然后,公式中的:
$B$2:$B$12<=SMALL($B$2:$B$12,$D$2)
求得哪些成绩小于指定的倒数分数,示例中得到数组:
{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE}
代入:
IF($B$2:$B$12<=SMALL($B$2:$B$12,$D$2),ROW($A$2:$A$12),FALSE)
得到排在倒数的分数所在的行数和FALSE组成的数组:
{FALSE;3;FALSE;5;FALSE;FALSE;FALSE;9;FALSE;11;12}
作为SMALL函数的参数,求出ROW(A1)即第1个最小值所在的行:
SMALL({FALSE;3;FALSE;5;FALSE;FALSE;FALSE;9;FALSE;11;12},1)
即
3
当将公式向下拖时,ROW(A1)会变成ROW(A2)、ROW(A3)、…,从而求出第2小、第3小、…的值所在的行。
再将结果代入INDEX函数获取对应的姓名:
INDEX($A$1:$A$12,3)
得到:
桑梓儿
注:本文配套示例工作簿在 知识星球App : 完美Excel社群 提供下载。欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。
欢迎到知识星球:完美Excel社群,进行技术交流和提问,获取更多电子资料。