也谈用Excel进行成绩分析.doc

也谈用Excel进行成绩分析众所周知，Excel的数据处理能力非常强大，用它进行学生的成绩分析也只是冰山一角。而我研究的目的是：做一套成绩分析表，能让不太懂Excel的外行老师或领导轻松使用。要求：不写代码，不用宏，只要在工作表中写几个公式，让懂Excel的信息技术老师帮忙写进去，即可处理。本文中用到的函数有sum()、average()、large()、rank()、sumproduct()、if()、sumif()、countif()、roundup()等，若读者对其中某一个函数的用法不太清楚的话，请先搞清楚再往下看，在这里我就不啰嗦了。一、基本情况介绍新建一个工作簿，取名为“成绩分析.xls”，包含3个工作表：第一个工作表，命名为“成绩名次”，存放原始成绩库，如图1中的ABCDE列数据，同时，可在右侧分别求出每个学生的各单科成绩、总成绩在年级中的名次，以及该生总成绩在班内的名次。在本文中，只以5个班次、2科成绩、399个学生为例说明方法，如有多班、多科，可变通使用。图1第二个工作表，命名为“成绩分析”，存放分析结果。按图2所示，先录入表头中相应的文字及A6：A10中的班次，并稍加美化。再手工输入相应必要的数字，比如：本次考试各学科试卷的满分H2、K2，以及学校领导和年级主任等根据经验和需要确定的优秀生基数D2。至于本工作表中的其他数据，如B6:N11和第3行中的数值，均可输入公式，求得。其中包含：各班参评人数，各班及年级单科平均分、及格率、优秀生、优秀生数在年级中的排名，各单科及总成绩在年级中的最高分。如无特殊要求，各学校所要的“成绩分析”内容无出其右。图2第三个工作表，命名为“分数段”。1设计理念：(1)在2-3行，除A列以外，用公式自动设置分数段的起、止分数。(2)在B4：L9中，用公式自动设置每个班在各个分数段的学生分布数。(3)可以按领导意图的不同需要，任意设置“几分一档”和“底线参考分数”。2.概念说明：(1)几分一档。一般设为10，根据需要也可以把它设小一点，比如：5、2、1等或期间某个数。(2)底线参考分数。学校领导往往关心高分区，而对总成绩低于“某分数”的学生分布并不关心，我们就把“该分数”视为“底线参考分数”。在图3所示实例中，我们在单元格M10中设“底线参考分数”为100，通过公式计算后，“最后一档分数段”显示在L3和L2处的起、止分数就是0、100，就是说，在该分数段，会把各班总成绩小于或等于100的学生数全部显示出来。图3看到这里，有人会提出问题来。比如：在图3实例中这两个参数分别是10和100，若把这两个数改为“5、100”，或“10、50”或其他任意两个数的组合，那“最后一档分数段”可不一定在L列呀。这是当然的，肯定会变化。这正是我认为非常得意的一点：若超过L列，只要您选中L2：L9，向右任意拖动填充柄进行填充，拖几次，没关系，直到拖出“最后一档分数段”来为止。若拖过了“最后一档分数段”，即拖得太多了，也不会出现任何问题，因为，我们在设计公式时，已考虑了“美化”和“保险”两个因素：如果出了点小问题或某档学生分布数为0，则显示为空。另外，三个工作表所有公式中，凡涉及到原始数据（即“成绩名次”工作表的ABCDE列）的地方，全都是“名称定义”。这是我自认为非常得意的第二点：公式可移植性强。比如：即使再多加几个班（学科），我们只要正确进行“名称定义”，然后在图2和图3中再多加几行（列），再复制公式即可，且不必对公式作任何改动。二、制作过程您若有兴趣，不妨跟着我，开始进行“名称定义”和书写公式。步骤如下：1进入工作表“成绩名次”(1)定义名称单击A2，按shift+ctrl+，可选中A2：A400，单击“插入”菜单，指向“名称”，单击“定义”选项，随即弹出“定义名称”对话框，直接输入“bj”作为已选定区域的名称，单击“确定”按钮。即完成对字段“班次”下有效数据的名称定义。用同上面一样的方法，选中D2：D400，定义名称为“yw”; 选中E2：E400，定义名称为“sx”(2)求总成绩，然后定义名称。在单元格F2，输入公式： =SUM(D2:E2) 当输完公式，回车后，再选中F2单元格，双击填充柄（或拖动填充柄向下填充），可进行总成绩列的公式填充。再选中F2单元格，按shift+ctrl+（选中F2：F400），定义名称为“cj”。(3)求学生在年级的语文名次。在单元格G2，输入公式：=RANK(yw,yw)求学生在年级的数学名次。在单元格H2，输入公式： =RANK(sx,sx)求学生在年级的总名次。 在单元格I2，输入公式：=RANK(cj,cj)求学生总成绩在本班的名次。在单元格J2，输入公式：=SUMPRODUCT(bj=A2)*(cjF2)+1(4)选中G2：J2，双击填充柄，即可自动填充G3：J400中每一单元格的公式。(5)用上面“定义名称”的方法，分别定义“G2：G400”、“H2：H400”、“I2：I400”和“J2：J400”为ywmc、sxmc、jmc、bmc。2进入工作表“成绩分析”(1)在H2、K2内，分别输入语文、数学实际考试用试卷的满分，本表中两数字分别是100、120。(2)在单元格D2内，输入“优秀生基数”，该数字由有关领导根据以往经验和实际情况而定。在本实例中，我们定的“优秀生基数”是100，这样就可以计算前“100”名优秀生在各班的分布了。(注:年级总成绩降序排列第100个人,不一定是第100名，可能是并列第100、99、98、97或更小等名次。)(3)书写B6：N10区域的公式： 先求B6：N6中每单元格的公式，再统一填充。 B6中，=COUNTIF(成绩分析.xls!bj,=&A6) C6中，=SUMIF(成绩分析.xls!bj,=&A6,成绩分析.xls!yw)/B6 D6中，=ROUNDUP(SUMPRODUCT(成绩分析.xls!bj=$A6)*(成绩分析.xls!yw=$H$2*0.6)/COUNTIF(成绩分析.xls!bj,=&$A6)*100,1) E6中，=SUMPRODUCT(成绩分析.xls!bj=A6)*(成绩分析.xls!ywmc=$K$2*0.6)/COUNTIF(成绩分析.xls!bj,=&$A6)*100,1) I6中，=SUMPRODUCT(成绩分析.xls!bj=A6)*(成绩分析.xls!sxmc=$D$2) J6中，=RANK(I6,I$6:I$10) K6中，=C6+G6 L6中，=RANK(K6,K$6:K$10) M6中，=SUMPRODUCT(成绩分析.xls!bj=A6)*(成绩分析.xls!jmc=&$H$2*0.6)/COUNT(成绩分析.xls!yw)*100,1) =SUM(E6:E10) G11：I11中，从左至右公式分别为：=AVERAGE(成绩分析.xls!sx) =ROUNDUP(COUNTIF(成绩分析.xls!sx,=&$K$2*0.6)/COUNT(成绩分析.xls!sx)*100,1) =SUM(I6:I10)K11中，公式为：=AVERAGE(成绩分析.xls!cj)M11中，公式为：=SUM(M6:M10)（注：F11、J11和N11三个单元格中，没有实际意义，因此，不必再写什么公式）(5)书写各科最高分公式： 语文最高分，在单元格E3，公式为：=LARGE(yw,1) 数学最高分，在单元格G3，公式为：=LARGE(sx,1) 总成绩最高分，在单元格I3，公式为：=LARGE(成绩分析.xls!cj,1)至此，第二个工作表“成绩分析”的公式全部书写完毕。3进入工作表“分数段”(1)由学校领导根据需要定出“几分一档”和“底线参考分数”，分别填写在K10、M10单元格中，并分别定义名称为：“fd”、“dxf”。在本表中，我们以“10、100”为例，来说明如何写出各单元格公式。(2)B3、B2作为第一个分数段的起止分数，需要单独输入公式： B2中，我们可以定义为年级总成绩中的最高分数，而这个结果已经在第二个工作表“成绩分析”I3单元格中求出。因此，其公式就是：=成绩分析!I3 B3中，公式为：=B2-MOD(B2,fd)+1(3)书写其他分数段的起止分数所在单元格中的公式： C2中，=IF(OR(B3=0,B3=),B3-1) C3中，=IF(C2=,IF(C2-fd+1=dxf,C2-fd+1,0) 选中C2：C3，拖动填充柄，向右填充，填到L列，恰好是“最后一档分数段”。如果向右拖动填充时，拖过了L列，甚至到了M、N或更远，也不会有影响，只不过这些单元格显示为空罢了。(4)书写B4：L8中的公式：在B4中，计算01班满足条件“总成绩大于等于B3中的值、并且小于等于B2中的值”的学生数，其他，依此类推。因此，需把如下公式书写到B4中：=IF(SUMPRODUCT(成绩分析.xls!bj=$A4)*(成绩分析.xls!cj=B$3)=0,SUMPRODUCT(成绩分析.xls!bj=$A4)*(成绩分析.xls!cj=B$3) 选中B4，向右填充，填到L列；此时，B4：L4区域正好被选中，紧接着双击填充柄，即可填充其他班次的B5：L8区域。(5)书写B9：L9中的公式： B9中的公式如下：=IF(COUNTIF(成绩分析.xls!cj,=&B3)=0,COUNTIF(成绩分析.xls!cj,=&B3)输完后，再选中B9，向右填充，填到L列。至此，第三个工作表“分数段”的公式已经填写完毕。如果领导有意改变“几分一档”和“底线参考分数”的值，读者可以按图3后