|
全校那么多学生，如何快速确定谁需要补考、补考几门，提取补考清单是个费脑筋的问题。 如何快速、高效从班级成绩中提取补考清单，此前曾有人运用单元格设置结合公式来解决。我只用Excel函数与公式，不用人为设置单元格，几分钟就可搞定一个班级的补考数据，更加高效！ 这里以我校11汽修班的成绩为例来说明。工作表命名为“汽修班”，共41名学生，单元格区域C4:i44为7门学科的分数区域，各学科满分为100分，小于60分为要补考的分数（图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）。
对于其余班级，只需替换原始成绩表数据，补考名单就会自动生成。 |
