分享

Excel在学校中的应用29-学生成绩综合统计表(二)

 甘苦人生2010 2013-01-12

        4.7 学生成绩综合统计表(二)

案例背景
    4.6节介绍了统计年级和各班的及格人数与及格率,优秀人数与优秀率,以及如何动态的统计各班里前、后若干名(如年级前20名和后20名)的人数和比例。可能您还想在不破坏原成绩表结构的情况下,查看各门课程年级前若干名的成绩以及是哪些学生获得该名次。
    本案例仍以某中学高一年级4个教学班100名学生的期末考试的3科成绩和总分为样本,动态的查看各门课程年级前20名的成绩和学生。
    要实现本案例中的功能,学员应该掌握以下EXCEL技术点
    ●基础知识 删除条件格式 选择性粘贴的转置功能
    ●函数应用 SUM函数,IF函数,COUNTIF函数,LARGE函数,SMALL函数,INDEX函数,ROW函数
    ●综合应用 Excel窗体的应用 函数嵌套数组公式。
最终效果展示

4.7.1创建原始数据表
Step 1打开工作簿、插入工作表、重名工作表
打开4.6节中的工作簿综合统计表.xls”,然后插入一张工作表,将其重命名为名次

Step 2输入原始数据
单击工作表标签统计表,选中单元格区域A49G150,按组合键复制。
单击工作表标签名次,按组合键粘贴。

Step 3删除条件格式
选中单元格区域A1:G102,单击菜单格式”→“条件格式,弹出条件格式对话框。
单击删除按钮,弹出删除条件格式对话框。

删除条件格式对话框中勾选条件1“条件2“命令按钮,然后单击确定按钮返回条件格式对话框。

然后单击确定按钮即可删除条件格式

4.7.2创建动态名次表
Step 1创建Excel“窗体数据源
选中单元格区域D1:G1,按组合键复制。
单击单元格J2,单击菜单编辑”→“选择性粘贴,弹出选择性粘贴对话框,在选择性粘贴对话框中勾选转置复选框。
然后单击确定按钮。

Step 2插入Excel窗体组合框
单击菜单视图”→“工具栏”→“窗体,在桌面上调出窗体组合工具栏命令按钮。

单击选中窗体组合工具栏中的组合框按钮,选中单元格I1,按照单元格I1大小初步设置组合框按钮的大小。
右键单击单元格J1 中的组合框按钮,在弹出的下拉列表中选择设置控件格式命令,弹出设置控件格式对话框。
切换到控制选项卡,在数据源区域复选框中输入:“$J$2:$I$5”,在单元格链接复选框中输入:“$J$1”,在下拉显示项数复选框中输入4”

然后单击确定按钮。然后从单元格I1窗体的下拉选框中选择语文
Step3为动态名次表设置数据源
在单元格I2输入如下公式,然后按键确认。
=INDEX($D$2:$G$102,ROW(),$J$1)
选中单元格I2,向下拖曳单元格I2右下角的填充柄至单元格I101即可完成公式填充。

Step4创建动态名次表
在单元格K1输入如下公式,按键确认,然后选中单元格区域K1:O1,单击合并及居中按钮。
=CHOOSE(J1,J2,J3,J4,J5)&"成绩前20"
按图10样式在单元格区域K2:O22输入表格框架信息并设置边框。

在单元格K3输入如下公式,然后按组合键确认。
=SMALL(RANK($I$2:$I$101,$I$2:$I$101),ROW()-2)
在单元格O3中输入如下公式,然后按键确认得到第一名的分数。
=LARGE($I$2:$I$101,ROW()-2)
在单元格N3中输入如下公式,然后按组合键确认得到获得第一名的学生姓名。=INDEX($C:$C,SMALL(IF($I$2:$I$101=O3,ROW($C$3:$C$102)),COUNTIF($O$3:$O3,$O3)))

在单元格M3中输入如下公式,然后按组合键确认得到获得第一名的学生的学号。
=INDEX($B:$B,SMALL(IF($I$2:$I$101=O3,ROW($B$3:$B$102)),COUNTIF($O$3:$O3,$O3)))

在单元格L3中输入如下公式,然后按组合键确认得到获得第一名的学生所在班级。
=INDEX($A:$A,SMALL(IF($I$2:$I$101=O3,ROW($A$3:$A$102)),COUNTIF($O$3:$O3,$O3)))

选中单元格区域K3:O3,向下拖曳单元格O3右下角的填充柄至单元格O22即可完成公式填充。
选中单元格区域K3:O22,重新设置边框。

单击单元格I1中窗体的下箭头,从下拉选框中选择数学,此时名次表显示的是数学20名对应内容。

    至此,在不破坏原成绩表结构的前提下,直接从成绩表中成功提取出前20名的成绩以及对应的学生姓名、学号和班级,并且通过窗体下拉选框可以动态的选择不同课程从而产生动态的前20名名次表。

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多