也谈用excel进行成绩分析

《也谈用excel进行成绩分析》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、也谈用Excel进行成绩分析众所周知，Excel的数据处理能力非常强大，用它进行学生的成绩分析也只是冰山一角。而我研究的目的是：做一套成绩分析表，能让不太懂Excel的外行老师或领导轻松使用。要求：不写代码，不用宏，只要在工作表中写几个公式，让懂Excel的信息技术老师帮忙写进去，即可处理。本文中用到的函数有sum()、average()、large()、rank()、sumproduct()、if()、sumif()、countif()、roundup()等，若读者对其中某一个函数的用法不太清楚的话，请先搞清楚

2、再往下看，在这里我就不啰嗦了。一、基本情况介绍新建一个工作簿，取名为“成绩分析.xls”，包含3个工作表：第一个工作表，命名为“成绩名次”，存放原始成绩库，如图1中的ABCDE列数据，同时，可在右侧分别求出每个学生的各单科成绩、总成绩在年级中的名次，以及该生总成绩在班内的名次。在本文中，只以5个班次、2科成绩、399个学生为例说明方法，如有多班、多科，可变通使用。图1第二个工作表，命名为“成绩分析”，存放分析结果。按图2所示，先录入表头中相应的文字及A6：A10中的班次，并稍加美化。再手工输入相应必要的数字，比如

3、：本次考试各学科试卷的满分H2、K2，以及学校领导和年级主任等根据经验和需要确定的优秀生基数D2。至于本工作表中的其他数据，如B6:N11和第3行中的数值，均可输入公式，求得。其中包含：各班参评人数，各班及年级单科平均分、及格率、优秀生、优秀生数在年级中的排名，各单科及总成绩在年级中的最高分。如无特殊要求，各学校所要的“成绩分析”内容无出其右。图2第三个工作表，命名为“分数段”。1．设计理念：(1)在2-3行，除A列以外，用公式自动设置分数段的起、止分数。(2)在B4：L9中，用公式自动设置每个班在各个分数段的学

4、生分布数。(3)可以按领导意图的不同需要，任意设置“几分一档”和“底线参考分数”。2.概念说明：(1)几分一档。一般设为10，根据需要也可以把它设小一点，比如：5、2、1等或期间某个数。(2)底线参考分数。学校领导往往关心高分区，而对总成绩低于“某分数”的学生分布并不关心，我们就把“该分数”视为“底线参考分数”。在图3所示实例中，我们在单元格M10中设“底线参考分数”为100，通过公式计算后，“最后一档分数段”显示在L3和L2处的起、止分数就是0、100，就是说，在该分数段，会把各班总成绩小于或等于100的学生数

5、全部显示出来。图3看到这里，有人会提出问题来。比如：在图3实例中这两个参数分别是10和100，若把这两个数改为“5、100”，或“10、50”或其他任意两个数的组合，那“最后一档分数段”可不一定在L列呀。这是当然的，肯定会变化。这正是我认为非常得意的一点：若超过L列，只要您选中L2：L9，向右任意拖动填充柄进行填充，拖几次，没关系，直到拖出“最后一档分数段”来为止。若拖过了“最后一档分数段”，即拖得太多了，也不会出现任何问题，因为，我们在设计公式时，已考虑了“美化”和“保险”两个因素：如果出了点小问题或某档学生分

6、布数为0，则显示为空。另外，三个工作表所有公式中，凡涉及到原始数据（即“成绩名次”工作表的ABCDE列）的地方，全都是“名称定义”。这是我自认为非常得意的第二点：公式可移植性强。比如：即使再多加几个班（学科），我们只要正确进行“名称定义”，然后在图2和图3中再多加几行（列），再复制公式即可，且不必对公式作任何改动。二、制作过程您若有兴趣，不妨跟着我，开始进行“名称定义”和书写公式。步骤如下：1．进入工作表“成绩名次”(1)定义名称单击A2，按shift+ctrl+↓，可选中A2：A400，单击“插入”菜单，指向“

7、名称”，单击“定义”选项，随即弹出“定义名称”对话框，直接输入“bj”作为已选定区域的名称，单击“确定”按钮。即完成对字段“班次”下有效数据的名称定义。用同上面一样的方法，选中D2：D400，定义名称为“yw”;选中E2：E400，定义名称为“sx”(2)求总成绩，然后定义名称。在单元格F2，输入公式：=SUM(D2:E2)当输完公式，回车后，再选中F2单元格，双击填充柄（或拖动填充柄向下填充），可进行总成绩列的公式填充。再选中F2单元格，按shift+ctrl+↓（选中F2：F400），定义名称为“cj”。(3

8、)求学生在年级的语文名次。在单元格G2，输入公式：=RANK(yw,yw)求学生在年级的数学名次。在单元格H2，输入公式：=RANK(sx,sx)求学生在年级的总名次。在单元格I2，输入公式：=RANK(cj,cj)求学生总成绩在本班的名次。在单元格J2，输入公式：=SUMPRODUCT((bj=A2)*(cj>F2))+1(4)选中G2：J2，双击填充柄，即可自动填充G