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输入原始数据 ①单击工作表标签“统计表”,选中单元格区域A49:G150,按组合键复制。 ②单击工作表标签“名次”,按组合键粘贴。
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名名次表。
|