4.6 学生成绩综合统计表(一)
案例背景 期末考试后,除了要统计各个分数段人数分布情况外(前面章节已经介绍了多种统计分数段的方法)可能还要统计年级和各班的及格人数与及格率,优秀人数与优秀率,以及各班里前、后若干名(如年级前20名和后20名)的人数和比例等 。 本案例以某中学高一年级4个教学班100名学生的期末考试的3科成绩和总分为样本,统计全年级4个班级学生的期末考试及格人数与及格率,优秀人数与优秀率,以及各班里年级前20名和后20名的人数和比例,以便进行综合分析。同时将前20名和后20名学生的成绩在统计表中标示出来,以使得前、后20名的成绩能够在成绩表突出显示。 要实现本案例中的功能,学员应该掌握以下EXCEL技术点 ●基础知识 条件格式 ●函数应用 ISNUMBER函数 SUM函数,LARGE函数,SMALL函数 ●综合应用 函数嵌套数组公式。 最总效果展示
4.6.1创建原始数据表 Step 1创建工作簿、重名工作表 创建工作簿“综合统计表.xls”,然后将工作表重命名为“统计表”,删除多余工作表。
Step2输入原始成绩 ①在单元格A49输入表格标题:“高一年级期末考试成绩统计表”,选择你好单元格区域A49:G49,单击“合并及居中”按钮,字体、字型、字号设置为黑体加粗16号。 ②在单元格区域A50:G50输入字段名:“班级”,“学号”,“姓名”,“语文”,“数学“,“英语”和“总分“。 ③在单元格区域A51:F150输入全年级4个班100名学生的“语文”,“数学“,“英语”成绩 ④在单元格G51输入公式:“=SUM(D51:F51) ”,然后按键确认,计算出第一个学生的“总分”。 ⑤选中单元格G51,双击单元格G51右下角的填充柄即可完成其余学生“总分”的统计工作。 ⑥选中单元格区域A50:G150,按图2样式设置边框,添加底纹,文本居中显示。
4.6.2统计及格人数与及格率 Step 1创建统计表框架 在单元格区域A1:J9按图3样式输入“高一年级各班及格人数与及格率统计表”框架信息。
Step2输入统计分数线 ①选中单元格区域B3:D3,输入“60”, 按组合键确认。 ②在单元格E3中输入“180”。 Step3统计及格人数 ①在单元格B4输入如下公式,然后按组合键确认。 “=SUM(ISNUMBER(D$51:D$150)*(D$51:D$150>=B3))”
②在单元格B5输入如下公式,按组合键确认。 “=SUM((($A$51:$A$150=1))*(D$51:D$150>=B$3))”
③在单元格区域B6:B8分别输入如下公式,按组合键确认。 “=SUM((($A$51:$A$150=2))*(D$51:D$150>=B$3))” “=SUM((($A$51:$A$150=3))*(D$51:D$150>=B$3))” “=SUM((($A$51:$A$150=4))*(D$51:D$150>=B$3))”
④在单元格B9中输入公式:“=SUM(B5:B8)“,然后按键确认,即可验证上述各班统计数之和是否等于年级统计数。 ⑤选中单元格区域B4:B9,向右拖曳单元格B9右下角的填充柄至E列即可完成“数学“、”英语“和”总分“的相应统计工作。
Step4统计及格率 ①在单元格G4输入公式“=B4/100 “,然后按键确认。 ②选中单元格G4,向下拖曳单元格G4右下角的填充柄至单元格G8即可完成各班:“语文“学科及格率的统计。 ③在单元格G9输入公式:“=SUM(G5:G8) “,然后按键确认。 ④选中单元格区域G4:G9,向右拖曳单元格G9右下角的填充柄至J列即可完成“数学“、”英语“和”总分“及格率的统计和验证工作。 ⑤选中单元格区域G4:J9,单击菜单“格式“→”单元格“,弹出”单元格格式“对话框,切换到数字选项卡,在”分类“选项框中选择”百分比“,然后单击”确定“按钮,即可将该区域数值设置成百分比形式显示。
4.6.3统计优秀人数与优秀率 Step 1创建统计表框架 在单元格区域A12:J20按图3样式输入“高一年级各班优秀人数与优秀率统计表”框架信息。
Step2输入统计分数线 ①选中单元格区域B14:D14,输入“90”, 按组合键确认。 ②在单元格E3中输入“270”。 Step3统计优秀人数 ①在单元格B15输入如下公式,然后按组合键确认。 “=SUM(ISNUMBER(D$51:D$150)*(D$51:D$150>=B14))” ②在单元格B16输入如下公式,然后按组合键确认。 “=SUM((($A$51:$A$150=1))*(D$51:D$150>=B$14))” ③在单元格区域B17:B19分别输入如下公式,然后按组合键确认。 “=SUM((($A$51:$A$150=2))*(D$51:D$150>=B$14))” “=SUM((($A$51:$A$150=3))*(D$51:D$150>=B$14))” “=SUM((($A$51:$A$150=4))*(D$51:D$150>=B$14))” ④在单元格B20中输入公式:“=SUM(B16:B19) “,然后按键确认,即可验证上述各班统计数之和是否等于年级统计数。 ⑤选中单元格区域B15:B20,向右拖曳单元格B20右下角的填充柄至E列即可完成“数学“、”英语“和”总分“的相应统计工作。
Step 4统计优秀率 ①在单元格G15输入公式“=B15/100 “,然后按键确认。 ②选中单元格G15,向下拖曳单元格G15右下角的填充柄至单元格G19即可完成各班:“语文“学科及格率的统计。 ③在单元格G20输入公式:“=SUM(G16:G19) “,然后按键确认。 ④选中单元格区域G15:G20,向右拖曳单元格G20右下角的填充柄至J列即可完成“数学“、”英语“和”总分“优秀率的统计和验证工作。 ⑤选中单元格区域G15:J20,单击菜单“格式“→”单元格“,弹出”单元格格式“对话框,切换到数字选项卡,在”分类“选项框中选择”百分比“,然后单击”确定“按钮,即可将该区域数值设置成百分比形式显示。
4.6.4统计年级前20名中各班所占人数和比例 Step 1创建统计表框架 在单元格区域A23:J31按图12样式输入统计表框架信息。
Step2计算”前20名”分数线 ①在单元格B25输入公式“=LARGE(D$51:D$150,$B$23) ”,然后按键确认。 ②选中单元格B25,向右拖曳单元格B25右下角的填充柄至E列完成公式填充,即可得到“数学“、”英语“和”总分“第20名的分数。
Step 3统计年级和各班所占前20名人数 ①在单元格B26输入如下公式,然后按组合键确认。 “=SUM(ISNUMBER(D$51:D$150)*(D$51:D$150>=B25)) ” ②在单元格B27输入如下公式,然后按组合键确认。 “=SUM((($A$51:$A$150=1))*(D$51:D$150>=B$25)) ” ③在单元格区域B28:B30分别输入如下公式,按组合键确认。 “=SUM((($A$51:$A$150=2))*(D$51:D$150>=B$25))” “=SUM((($A$51:$A$150=3))*(D$51:D$150>=B$25))” “=SUM((($A$51:$A$150=4))*(D$51:D$150>=B$25))” ④在单元格B31输入公式:“=SUM(B27:B30)”,然后按键确认。即可验证上述各班统计数之和是否等于年级统计数。
⑤选中单元格区域B26:B31,向右拖曳单元格B31右下角的填充柄至E列完成公式填充,即可得到“数学“、”英语“和”总分“前20名中年级和各班所占人数。
Step4统计中各班所占年级前20名的比例 ①选中单元格区域G26:J26,输入“100%”,然后按组合键确认。 ②在单元格G27中输入公式:“=B27/B$26”然后按键确认。 ③选中单元格G27,向下拖曳单元格G27右下角的填充柄至G30完成公式填充。 ④在单元格G31中输入公式:“=SUM(G27:G30)”,然后按键确认。 ⑤选中单元格区域G27: G31,向右拖曳单元格G31右下角的填充柄至J列完成公式填充。
4.6.5统计年级后20名中各班所占人数和比例 Step 1创建统计表框架 在单元格区域A34:J42按图17样式输入统计表框架信息。
Step2计算”后20名”分数线 ①在单元格B36输入公式“=SMALL(D$51:D$150,$B$34) ”,然后按键确认。 ②选中单元格B36,向右拖曳单元格B36右下角的填充柄至E列完成公式填充,即可得到“数学“、”英语“和”总分“倒数第20名的分数。
Step3统计年级和各班所占后20名人数 ①在单元格B37输入如下公式,按组合键确认。 “=SUM(ISNUMBER(D$51:D$150)*(D$51:D$150<=B36)) ” ②在单元格B38输入如下公式,按组合键确认。 “=SUM((($A$51:$A$150=1))*(D$51:D$150<=B$36)) ” ③在单元格区域B39:B41分别输入如下公式,按组合键确认。 “=SUM((($A$51:$A$150=2))*(D$51:D$150<=B$36))” “=SUM((($A$51:$A$150=3))*(D$51:D$150<=B$36))” “=SUM((($A$51:$A$150=4))*(D$51:D$150<=B$36))” ④在单元格B42输入公式:“=SUM(B38:B41)”,然后按键确认。即可验证上述各班统计数之和是否等于年级统计数。 ⑤选中单元格区域B37:B42,向右拖曳单元格B42右下角的填充柄至E列完成公式填充,即可得到“数学“、”英语“和”总分“后20名中年级和各班所占人数。
Step4统计中各班所占年级后20名的比例 ①选中单元格区域G37:J37,输入“100%”,然后按组合键确认。 ②在单元格G38中输入公式:“=B38/B$37”然后按键确认。 ③选中单元格G38,向下拖曳单元格G38右下角的填充柄至G41完成公式填充。 ④在单元格G42中输入公式:“=SUM(G38:G41)”,然后按键确认。 ⑤选中单元格区域G38: G42,向右拖曳单元格G42右下角的填充柄至J列完成公式填充。
4.6.6在成绩表中标示前20名和后20名的分数 Step 1设置条件格式 ①选中单元格D51,单击菜单“格式”→“条件格式”,弹出“条件格式”对话框。 ②在“条件格式”对话框中单击 “条件1”的下箭头选择“公式”,在其右侧的文本框中输入:“=D51>=B$25”。 ③单击“格式”按钮弹出“单元格格式”对话框。 ④在“单元格格式”对话框中切换到“字体”选项卡,在“字形”列表框中选择“加粗加斜”。单击“颜色”右侧的下箭头按钮,在弹出的“颜色”调色板中选择“黄色”,切换到“图案”选项卡,在颜色框中选择“红色”。 ⑤单击“确定”按钮返回“条件格式”对话框。
⑥单击“添加”按钮,单击“条件2”的下箭头选择“公式”,在其右侧的文本框中输入:“=D51<=B$36”。 ⑦单击“格式”按钮弹出“单元格格式”对话框。在“单元格格式”对话框中切换到“字体”选项卡,在“字形”列表框中选择“加粗”。单击“颜色”右侧的下箭头按钮,在弹出的“颜色”调色板中选择“红色”,切换到“图案”选项卡,在颜色框中选择“浅绿色”。 ⑧单击“确定”按钮返回“条件格式”对话框。
⑨单击“确定”按钮,即可完成单元格D51中条件格式的设置。
Step2传递条件格式 ①选中单元格D51,单击“格式刷”按钮。 ②选中数据区域D51:G150即可完成整个数据区域条件格式的设置。 ③选中数据区域D51:G150,重新设置边框(修复边框)。
关键知识点讲解: IS 类函数 本部分描述了用来检验数值或引用类型的九个工作表函数。这些函数,概括为 IS 类函数,可以检验数值的类型并根据参数取值返回 TRUE 或 FALSE。例如,如果数值为对空白单元格的引用,函数 ISBLANK 返回逻辑值 TRUE,否则返回 FALSE。 使用格式: ISBLANK(value) ISERR(value) ISERROR(value) ISLOGICAL(value) ISNA(value) ISNONTEXT(value) ISNUMBER(value) ISREF(value) ISTEXT(value) Value 为需要进行检验的数值。分别为:空白(空白单元格)、错误值、逻辑值、文本、数字、引用值或对于以上任意参数的名称引用。
案例公式解析: 4.6.2 Step3之①的单元格B4中公式为: “=SUM(ISNUMBER(D$51:D$150)*(D$51:D$150>=B3)) ” 其中ISNUMBER(D$51:D$150)含有100个元素为一维数组,ISNUMBER判断单元格区域D$51:D$150是否是数值,因为可能会有学生没有参加该课程考试,这时会在相应位置出现“缺考”或类似字样文本,因为单元格区域D$51:D$150都有数值,所以ISNUMBER(D$51:D$150)返回含有100个“1”的一维数组, (D$51:D$150>=B3)为一含有100个逻辑判断的一维数组,返回含有86个“1”和14个“0”的一维数组,因此上面公式最后返回“86”。
|