自己动手制作excel成绩分析表（一劳永逸）

自己动手制作excel成绩分析表（一劳永逸）

(2011-10-11 00:58:26)

转载▼

标签： 杂谈

分类： ├→教学日记

前两天接到教务处主任通知，让我整理高三第二次月考成绩表，经过一晚上的努力，终于制作了一个一劳永逸的表，效果图如下：

 表一：文科成绩

 

表二：理科成绩

表三：文科成绩统计分析

表四：理科成绩统计分析

 

同一个文件中有四个工作表：“文科”“理科”“文科统计”“理科统计”

其中成绩表中包含有：总分，年级名次（相同的分数相同的名次），班级名次（在不用分班的情况下统计班级名次），单科年级名次，与上一次考试的对比（是进不了还是退步了）

统计表中包含有：每个班各个分数段的人数，年级各个分数段的人数，年级累计人数，每个班年级前多少名的人数

对于我们老师来说，这样的一张表足可以满足我们分析成绩的需要，如果每次考试都统计一次，工作量就会增加很多，并且是无效的重复劳动，为此鄙人用了四个小时的时间制作了这样的一张模板表，每次我们只需要把单科的成绩复制进来，就会自动求出总分，排出单科的名次，年级名次，班级名次以及与上一次的对比情况，还有各个分数段的人数，可以为我们省去很多不必要的麻烦，先将制作方法说明如下，有兴趣的朋友我们一起探讨，设计出更好的模板。

 

1、求总分

     这个大家都会，就不过多解释了，直接用SUM函数就行了

2、怎样排出年级名次，要求同一个分数有相同的名次，如效果图

  

方法：在G2单元格中输入 =RANK(F2,F:F)就可以了，RANK是排序函数，参数的意义是按照F列的数据排序

3、怎样在不分班的情况下，在不改变年级名次的情况下，排出班级名次？如上图

方法：在H2单元格个输入 =SUMPRODUCT(($E$2:$E$1000=E2)*(F$2:$F$1000>F2))+1

4、怎样统计各个班在每个分数段的人数？效果如表三、表四

方法：在B2中输入 =SUMPRODUCT((文科!E$2:文科!E$1000=A2)*(文科!F$2:文科!F$1000>=600))

          在B3中输入 =SUMPRODUCT((文科!E$2:文科!E$1000=A2)*(文科!F$2:文科!F$1000<600)*(文科!F$2:文科!F$1000>=590))

直接往后面复制，改变红色数字就可统计出各个分数段的人数

 

          理科统计表不用再逐个输入了，直接复制文科统计表，然后“编辑——替换”就行了

        

  

5、怎样统计各个班在年级前多少名的人数？

方法：在B19中输入 =SUMPRODUCT((文科!E$2:文科!E$1000=A19)*(文科!G$2:文科!G$1000<=50))

          在C19中输入 =SUMPRODUCT((文科!E$2:文科!E$1000=A19)*(文科!G$2:文科!G$1000<=100))

          后面的就不用说了吧

6、单元格里面都好多零好难看又影响看数据，怎样隐藏这些0？

 

 

方法：“工具 ——选项——视图——零值前面的钩去掉就行了”效果如下：

 好了，一张适用的成绩分析表就做好了，以后拿了直接用就OK了，凌晨一点了，睡觉去……

 

相关链接

自己动手制作excel成绩分析表（一劳永逸）

excel数据引用公式的使用方法

网络阅卷系统的使用---试卷的设计和答题卡的制作

SUMPRODUCT函数

用excel设计成绩分析表

excel合并多个工作表（提取工作表数据）