去年年底帮媳妇儿处理了一个她们学校里统计学生成绩的EXCEL表格问题。
表格中涉及到一些简单的总分统计、均分计算和优秀率及格率等计算等。如图(学生姓名已修改):
拿到表格后,发现她们领导给的表格中总分算法公式错误了=。=!
因为他们用于参评的总分统计是要排除最后5名学生成绩的。而她们领导在敲表格的时候,统计总分用的是在这个表格基础上,【分别对每一列成绩排除最差一名之后求和,再把5列求和结果相加...】,这显然是错的——因为最后无名成绩并不一定分别分布在不同的5列中啊=。=!
这个错误,导致表格后面平均分的数据也出错了。
于是,在不改变表格结构的情况下,动手帮媳妇儿改了一下。
考虑到她使用这个表格的时候,会有对成绩排序的需求和傻瓜式操作的需求,修改思路是【开个新sheet计算结果,然后将结果导入原表格。】
操作步骤
1,开个新表,将所有成绩引用到新表中来。
先按shift+f11 新建一个sheet,命名为“计算表”,把表格原来的sheet命名为“结果表”。之后做好表头,如下图。
2,把原表格中的所有成绩引用到【所有成绩】下方(D列),然后做好“序号”方便之后排序。 如图
注意,上方表格一共可容纳114个成绩数据,引用成绩的时候,注意把114个成绩都引用过来,表格中没有成绩数据的,引用后会显示为“0”。
3,接下来用公式进行排序,并计算总成绩和除了最后5名之外的总成绩。
排序公式是:
【从小到大】
=IFERROR(SMALL($D$3:$D$116,COUNTIF($D$3:$D$116,MIN($D$3:$D$116))+A3),0)
【从大到小】
=LARGE(D:D,A3)
分别把上述公式复制到B3和C3中,然后直接下拉,在D列中的成绩就会分别按从小到大和从大到小排列出来。如图。
4,公式算出总成绩和 除5名之外的总成绩,并将最终数据引用回到原来的表格中,就得到文章最开始的那个表格中的数据了。
最终的结果就是,把学生的成绩录入进去,就会出下面的所有计算结果,完全傻瓜式的表格。
整个操作和算法都很简单,并不高深,稍微动动手的事而已,建议自己动手操作。
如果,没学会,我已经把这个文件上传到百度文库了,你可以去下载,不过我的劳动是要收费哦,不贵,自己开个百度文库VIP而已:)
此外,对这个表格,还有其他功能需求的,可以联系我,我帮你做相应功能——只是实现一般简单的计算的话,免费帮搞,关注一下我,然后开口就行,比较难搞的功能的话,那个…适当收费:)
联系客服