自己动手制作excel成绩分析表(一劳永逸)转.docx

原文地址：自己动手制作excel成绩分析表（一劳永逸）作者：李国磊
效果图如下: 表一:文科成绩

 表二：理科成绩
表三：文科成绩统计分析
表四：理科成绩统计分析

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

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<<font color="#FF0000"〉600）*（文科！F＄2：文科！F$1000>=590））
直接往后面复制，改变红色数字就可统计出各个分数段的人数

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

5、怎样在统计表中出现文科（理科）成绩表中的最高分，及对应的名字和班级？
统计文科（理科）成绩表中的最高分
A2=MAX（INDIRECT(”文科!”＆CHAR（64+ROW(C5)）＆2):INDIRECT("文科!"＆CHAR（64+ROW(C5))&1000)）
 CHAR（  ）返回单元格中的字符
ROW（     ）   返回单元格所在的行
“64+row（)”的含义是,65对应A,66对应B，……比如上面表一中总分在D列，所以就是64+ROW(C5)
调出姓名
=LOOKUP(2，1/((文科!D$2：D$1000=MAX(（文科！D＄2:D＄1000=A2）*文科！D＄2：D＄1000））），文科!B$2:B$1000）
A2是刚返回的最高分
D列是分数 B列是姓名
 调出班级
=LOOKUP（2，1/(（文科！D＄2：D$1000=MAX（(文科！D$2:D＄1000=A2）＊文科！D$2:D$1000）））,文科！C＄2：C$1000)
C列是班级  
6、怎样统计各个班在年级前多少名的人数？
方法：在B19中输入 =SUMPRODUCT（（文科!E$2:文科！E$1000=A19)＊（文科!G$2:文科！G$1000<=50）)
          在C19中输入 =SUMPRODUCT(（文科!E＄2：文科!E＄1000=A19）*(文科！G$2：文科!G$1000〈=100))
          后面的就不用说了吧
7、单元格里面都好多零好难看又影响看数据，怎样隐藏这些0？

方法:“工具 ——选项—-视图——零值前面的钩去掉就行了"效果如下：
 好了，一张适用的成绩分析表就做好了，以后拿了直接用就OK了，凌晨一点了,睡觉去……