问与答88： 如何获取成绩排在最后5名的学生姓名？

学习Excel技术，关注微信公众号：

excelperfect

Q：我有一个学生成绩表，如何使用公式获取成绩排在最后5名的学生姓名，如下图1所示。

图1

A：可以对学生成绩从小到大进行排序，这样就可以看出排在最后的学生是哪几位了。将活动单元格置于列B中，单击功能区“开始”选项卡“编辑”组中的“排序和筛选——升序”，结果如下图2所示。

图2

也可以使用公式来实现，如下图3所示，可随指定的人数而变化。

图3

在单元格E2中输入数组公式：

=IFERROR(INDEX(A1:A12,SMALL(IF(B2:B12<=SMALL(B2:B12,D2),ROW(A2:A12),FALSE),ROW(A1))),"")

下拉至相应的单元格为止。

公式中：

SMALL(B2:B12,D2)

得到指定倒数名次的分数，示例中为倒数第5名，即92。

然后，公式中的：

B2:B12<=SMALL(B2:B12,D2)

求得哪些成绩小于指定的倒数分数，示例中得到数组：

{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE}

代入：

IF(B2:B12<=SMALL(B2:B12,D2),ROW(A2:A12),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(A1:A12,3)

得到：

桑梓儿