应用Excel轻松应对特殊的学生成绩分析统计.docVIP

应用Excel轻松应对特殊的学生成绩分析统计 2004.11.22??来自：新浪科技　???? 共有评论()条 发表评论 ?? 收藏 2．初步准备--考试号里提班级　　 如图1，从B列的考试号中取出前三位(班级编号)放在S列，即在单元格S2输入公式“=LEFT(B2,3)”，然后双击(或拖动)S2单元格右下角的填充柄即可。如果是，则在D2单元格中填入“201a” 　　文/郭大春 ??? 1．考试混合编，成绩统一理--老方法遇到新问题??? 关于使用Excel进行学生成绩处理，已经是老话题了。但在实际工作中还是会有很多新问题，例如，现在很多学校都是全年级各班混在一起考试，以防考试改卷中的不正当竞争。而统计成绩时，则是将已判分但未拆封的考卷统一交到教务处，先按座位号顺序(每本考卷的自然顺序)录入各科分数，再分析统计出全年级各科成绩。举例说明，如图1(记录11至830隐藏了)，要统计二(1)班优秀人数，传统做法就是先按考试号排序，再通过公式“=COUNTIF(分数!D2:D69,=96)”求出。它的弊端是要手工逐个修改 “D2:D69”这个参数中的两个行号(2和69)，这可是一项工作量很大的工作。当然，简单的方法还是有的，往下看吧。 　　图1 原始成绩表 ??? 2．初步准备--考试号里提班级??? 如图1，从B列的考试号中取出前三位(班级编号)放在S列，即在单元格S2输入公式“=LEFT(B2,3)”，然后双击(或拖动)S2单元格右下角的填充柄即可。 ??? 3．再做辅表--班级等级二合一??? 在图1所示的工作簿中再新建一工作表，并将其命名为“等级”，在单元格A1中输入公式“=分数!A1”，回车，选定A1，按住A1右下角的填充柄向右下拖至C840单元格，将“分数”工作表中的姓名、考号、座位号引用到“等级”工作表中(注意，千万不能复制粘贴过来，这样不能保持两表数据的一致性)。再选定C1，按住C1右下角的填充柄向右拖至L1单元格，将语文、数学等9个学科科目引用过来。接着，在D2单元格中输入IF嵌套公式“=IF(分数!D2=96,分数!$S2a,IF(分数!D2=72,分数!$S2b,IF(分数!D248,分数!$S2d,分数!$S2c)))”。D2单元格中公式的含义是：看“分数!D2”单元格中的分数(即“分数”工作表中李悦的语文分数)是否大于等于96。如果是，则在D2单元格中填入“201a”——“分数”工作表中S2单元格中的字符“201”加上“a”(“201”表示二(1)班，“a”表示成绩等级为“优秀”)；如果不是(即小于96)，再看是否大于等于72。如果是，则在D2单元格中填入“201b”；如果不是(即小于72)，再看是否小于48。如果是，则在D2单元格中填入“201d”；如果不是(即小于72大于48)，则在D2单元格中填入“201c”。最后按住D2单元格右下角的填充柄向右下拖至L840单元格，就可以将每个学生各科成绩的等级及所属班级都填好了 ??? 4．最终统计--所需数据瞬间齐??? 辅表制好之后，言归正传回到“统计”工作表(如图2)中，在A17到E28单元格区域中利用自动填充功能再制作一小块辅助数据(如图2)。 　　图2 “统计”工作表 ??? 万事俱备，下面开始班级总人数及优秀率、及格率等的统计了。仍以二(1)班优秀率为例，现在就改用这样的公式了“COUNTIF(等级!$D:$D,$B17)”，即对“等级”工作表中D列所有单元格进行统计(等级!$D:$D)，找出值为“201a”(本工作表即“统计”工作表的$B17的值，代表二(1)班优秀率)的单元格数目。 具体做法如下： ??? (1)班级总人数(在B4单元格中输入)：“=COUNTIF(分数!$S:$S,A17)”； ??? (2)优秀人数（在C4单元格中输入)：“=COUNTIF(等级!$D:$D,$B17)”； ??? (3)优秀率（在D4单元格中输入)：“=C4/$B4100”； ??? (4)及格人数（在E4单元格中输入)：“=COUNTIF(等级!$D:$D,$B17)+COUNTIF(等级!$D:$D,$C17)”； ??? (5)及格率(在F4单元格中输入)：“=E4/$B4100”； ??? (6)低分人数(在G4单元格中输入)：”=COUNTIF(等级!$D:$D,$E17)”； ??? (7)低分率(在H4单元格中输入)：“=G4/$B4100”； 到此为止，其余数据通过自动填充功能，瞬间即可完成。 ??? 5．方法点评--一表成，终年用，一劳而永逸??? (1)不同年级成绩统计的简单套用：比如，首先制作好了一年级的统计表，通过复制粘贴将第一个工作表(“分数”工作表)的内容更改为二年级的数据表，则二年级