前往BianBan.Net首页

BianBan.Net完美定义智能分班软件标杆

关于编班的搜索

Excel快速计算、统计混合编班考试成绩表 首 页 阅览室 馆友 我的图书馆 Excel快速计算、统计混合编班考试成绩表 收藏人:秦楼楚月 2011-05-26 | 阅: 转: | 分享 | 大 中 小 Excel快速计算、统计混合编班考试成绩表2007-6-20 为了保证学生成绩录入和处理的公平性,学校采用混合编班统一录入的方式。也就是说,把同年级多个班的学生成绩统一录入到一个Excel工作表中,而且使同班的学生不相邻。这种情况下,想在不破坏原始工作表的情况下统计各班学生各科的总分、平均分及各科的分数段分布,咱们还得好好动一下脑子。   先看一下原始成绩表,如图1所示。   第一列为学生的考号,其中考号的第5位和第6位数字是班级代码。学生各科的成绩分别在C、D、E、F列。而我们需要统计的是各学科各班的总分、平均分及各分数段人数等项指标,如图2所示。   一、总分的统计   在不破坏原工作表顺序的情况下,总分的统计我们可以使用SUM函数及数组公式来完成。   首先点击L3单元格计算01班语文科的总成绩。   输入&#8220;=SUM(IF(MID($A$2:$A$150,5,2)= $K3,$C$2:$C$150))&#8221;,然后按下&#8220;Ctrl+Shift+Enter&#8221;快捷键,可以看到公式的最外层添加了数组公式标记(一对大括号),公式变身成为&#8220;{=SUM(IF(MID($A$2:$A$150,5,2)= $K3,$C$2:$C$150))}&#8221;。切记这对大括号是不可以手工输入的。   按下快捷键后,L3单元格立刻显示出了01班语文科的总分。快吧?还有更快的呢。单击此单元格,向下拖动其填充句柄至L10单元格复制此公式,立刻各班的语文科总分就都有了。   方便吧?简单介绍一下公式中用到的相关函数,那咱们就可以举一反三了。   MID($A$2:$A$150,5,2)= $K3:K3单元格是班级代码。而MID函数可以截取字符串中的指定字符,如MID(A2,5,2)的含义是从A2单元格字符串的第5个字符开始截取2个字符组成新的字符串。至于公式中所写的的MID($A$2:$A$150,5,2)就可以利用数组公式对A2至A150单元格中的字符串逐一截取了。   而IF函数当然是用作判断了。IF(MID($A$2:$A$150,5,2)= $K3是判断截取的字符串是否与在K3单元格中的字符串相同。   整个公式的意思是:逐一判断A2至A150单元格中第5、6字符串是否与K3单元格的字符串相同,如果相同,那么就把同一行中C列的单元格数据进行累加。 二、平均分的统计   明白了各班总分是怎么计算出来的,那么平均分的计算就易如反掌了。仍然点击L3单元格,拖动其填充句柄向右至M3单元格,松开鼠标后在M3单元格同样可以得到01班语文科的总分。不要紧,在公式编辑栏中把公式中的&#8220;SUM&#8221;函数更改为&#8220;AVERAGE&#8221;函数,再按下&#8220;Ctrl+Shift+Enter&#8221;快捷键。好了,现在看到结果了吧?   选中M3单元格,向下拖动填充句柄至M10单元格复制此公式。行了,各班的语文平均分就都有了。   三、分数段统计   在混合编班而又不破坏原表顺序的情况下,我们就不能使用常用的COUNTIF函数或FREQUENCY函数来统计各分数段人数。我们可以使用SUMPRODUCT函数来完成这个任务。   点击N3单元格统计01班语文成绩在90分以上的人数。在此单元格输入公式&#8220;=SUMPRODUCT((MID($A$2:$A$150,5,2)=$K3)*($C$2:$C$150>=90))&#8221;,然后按下回车键。对,是回车键,而不是前面用的&#8220;Ctrl+Shift+Enter&#8221;快捷键。按下回车键后自然可以看到统计结果了。   这个公式的意思是:统计考号的5、6两位字符串与K3单元格字符串相同且C2:C150单元格数据大于90的单元格个数。&#8220;*&#8221;符号前后的括号内就是我们设置的条件。如果要设置的条件更多,那就再添加&#8220;*&#8221;,并在其后添加括号,在括号内添加条件就是了。   如法炮制,在O3单元格输入如下公式&#8220;=SUMPRODUCT((MID($A$2:$A$150,5,2)=$K3)*($C$2:$C$150>=80)*($C$2:$C$150<90))&#8221;,回车后就可以得到80-89分这一分数段的人数了。   选中O3单元格,向右拖动其填充句柄复制公式至S3单元格。并根据要统计的不同的分数段,修改各单元格的公式。如S3单元格的公式应修改为&#8220;=SUMPRODUCT((MID($A$2:$A$150,5,2)=$K3)*($C$2:$C$150<50))&#8221;。   好了,现在01班的各分数段已经统计出来了。选中N3:S3单元格区域,向下拖动填充句柄至S10单元格复制相应公式。OK,各班分数段统计完毕。最终效果如图3所示。   四、其它各科的成绩统计   以数学科统计为例。细细想来,数学科的统计与语文科成绩唯一的不同就是语文成绩在C列而数学成绩在D列。所以,我们只要复制语文统计表然后把其中的&#8220;$C$2:$C$150&#8221;替换为&#8220;$D$2:$D$150&#8221;就可以了。   先选中K1:S10单元格区域,按下&#8220;Ctrl+C&#8221;快捷键复制,再单击K12单元格,然后按下&#8220;Ctrl+V&#8221;快捷键进行粘贴。把K12单元格的&#8220;语文&#8221;修改为&#8220;数学&#8221;。现在选中新复制过来的K12:S21单元格区域。然后按下&#8220;Ctrl+F&#8221;快捷键,打开&#8220;查找和替换&#8221;对话框。然后点击&#8220;替换&#8221;选项卡,在&#8220;查找内容&#8221;输入框中输入要查找的内容&#8220;$C$2:$C$150&#8221;,再在&#8220;替换为&#8221;输入框中输入要替换的内容&#8220;$D$2:$D$150&#8221;。点击下方的&#8220;全部替换&#8221;按钮。OK,数学成绩的统计就算是大功告成了。如图4所示。   其它各科的依此类推。 来自:秦楼楚月 > 献花(0) +1 类似文章 更多 什么是绝对引用和相对引用 Excel中多个单元格相同公式的输入方法 关于EXCEL的时间相加 【Excel技巧】输入数字自动在指定的单元... 快速查看Excel使用的公式 Excel中多个单元格求和 “excel 判断单元格是否为空,如果不为空... 用Excel表格排名次 热门推广 猜你喜欢十种领带打法图解大全懂你的人,会用你需要的方式爱你:爱是...男人哄女人之10妙招十则黄金定律,助你摆脱"穷忙"人生看古代床的演变 发表评论: 最新文章学英语:Excel里轻轻松松背单词...EXCEL函数实例活用100谈》读书...在Excel工作表单元格中引用当前...Excel函数应用实例:自动录入性...Excel快速计算、统计混合编班考...excel计算公式大全 更多 热门文章 拆别人的台,就是为难自己 你有所不知的城市名称由来溯源 清朝皇族们的姓氏的深层意义 习近平同志系列讲话的思想精髓 四十种饼的做法 屌丝你真的该反思了 【美味酱制作】 做面酱和豆瓣酱... 香、麻、辣、开胃的酸菜鱼 染尽一季的绿意,放生自己【情... 企业管理方法:横向控制法 中国式人情隐规则:生活的关系学 好美的风景人物摄影(300P) 更多>> 关闭 关闭