补考清单 快速提取生成

张明昌2014-10-27 08:59应用 标签:技巧

全校那么多学生,如何快速确定谁需要补考、补考几门,提取补考清单是个费脑筋的问题。

如何快速、高效从班级成绩中提取补考清单,此前曾有人运用单元格设置结合公式来解决。我只用Excel函数与公式,不用人为设置单元格,几分钟就可搞定一个班级的补考数据,更加高效!

这里以我校11汽修班的成绩为例来说明。工作表命名为“汽修班”,共41名学生,单元格区域C4:i44为7门学科的分数区域,各学科满分为100分,小于60分为要补考的分数(图1)。

1

首先插入工作表,命名为“汽修班补考”,在单元格A2~E2分别输入“序号”、“姓名”、“科目”、“分数”、“补考门数”。

一、提取序号和姓名

在单元格A3中输入以下数组公式:

=INDEX(汽修班!A:A,SMALL(IF(汽修班!$C$4:$I$44<60,ROW(汽修班!$C$4:$I$44),65536),ROW(A1)))&""

按下Ctrl+Shift+Enter组合键后,公式将返回第一个需要补考的序号,将A3单元格公式向右、向下填充到B71单元格,B71没有任何数据了,即A3:B70单元格区域为所有需要补考的序号和姓名数据。

小提示:解释一下上面的这个公式,首先在C4:I44区域中,找出不及格的,如果不满足条件,就返回A列的第63356行,2003版Excel的最后一行,一般情况是一个空单元格;然后SAMLL(数组,ROW(A1)) 从小到大进行排序,返回这个数组中第几小的数;最后结合row函数下拉,通过INDEX做到依次引用A列的数据。

二、提取科目

在单元格C3中输入以下数组公式:

=INDEX(汽修班!$3:$3,MOD(SMALL(IF(汽修班!$C$4:$I$44<60,ROW(汽修班!$C$4:$I$44)*1000+COLUMN(汽修班!$C:$I),65536),ROW(A1)),1000))

按下Ctrl+Shift+Enter组合键后,公式将返回第一个需要补考的科目,将C3单元格公式向下填充到C71单元格,即可得到所有需要补考的科目数据。

以下数组公式也可以达到同样的结果:

=INDIRECT("汽修班!r3c"&RIGHT(SMALL(IF(汽修班!$C$3:$I$44<60,ROW(汽修班!$C$3:$I$44)*1000+COLUMN(汽修班!$C:$I),65536),ROW(A1)),3),0)

三、提取分数

在单元格D3中输入以下数组公式:

=INDIRECT("汽修班!"&TEXT(SMALL(IF(汽修班!$C$4:$I$44<60,ROW(汽修班!$C$4:$I$44)*1000+COLUMN(汽修班!$C:$I),65536),ROW(A1)),"r0c000"),0)

按下Ctrl+Shift+Enter组合键后,公式将返回第一个需要补考的科目的分数,将D3单元格公式向下填充到D71单元格,即可得到所有需要补考科目的分数信息。

四、汇总每人的补考门数

在单元格E3中输入以下公式:

=IF(COUNTIF($B$3:B3,B3)=1,COUNTIF($B$3:$B$70,B3),"")

按下Enter键后,公式将返回第一位同学的补考门数,将E3单元格公式向下填充到E71单元格即可。

对单元格区域A2:E71进行单元格格式设置,美化工作表,可以删除第71行的数据(图2)。

2

对于其余班级,只需替换原始成绩表数据,补考名单就会自动生成。