学生成绩综合统计表

《学生成绩综合统计表》由会员分享，可在线阅读，更多相关《学生成绩综合统计表（15页珍藏版）》请在装配图网上搜索。

1、学生成绩综合统计表4.6学生成绩综合统计表（一）案例背景期末考试后，除了要统计各个分数段人数分布情况外（前面章节已经介绍了多种统计分数段的方法）可能还要统计年级和各班的及格人数与及格率，优秀人数与优秀率，以及各班里前、后若干名（如年级前20名和后20名）的人数和比例等。本案例以某中学高一年级4个教学班100名学生的期末考试的3科成绩和总分为样本，统计全年级4个班级学生的期末考试及格人数与及格率，优秀人数与优秀率，以及各班里年级前20名和后20名的人数和比例，以便进行综合分析。同时将前20名和后20名学生的成绩在统计表中标示出来，以使得前、后20名的成绩能够在成绩表突出显示。要实现本案例中的功能

2、，学员应该掌握以下EXCEL技术点基础知识条件格式函数应用ISNUMBER函数SUM函数，LARGE函数，SMALL函数综合应用函数嵌套数组公式。最总效果展示4.6.1创建原始数据表Step1创建工作簿、重名工作表创建工作簿“综合统计表.xls”，然后将工作表重命名为统计表，删除多余工作表。 Step2输入原始成绩在单元格A49输入表格标题：“高一年级期末考试成绩统计表，选择你好单元格区域A49:G49,单击“合并及居中”按钮，字体、字型、字号设置为黑体加粗16号。在单元格区域A50:G50输入字段名：“班级”，“学号”,“姓名”,“语文”，“数学“，“英语”和“总分“。 在单元格区域A51:

3、F150输入全年级4个班100名学生的“语文”，“数学“，“英语”成绩在单元格G51输入公式：=SUM(D51:F51)”,然后按键确认，计算出第一个学生的“总分”。 选中单元格G51，双击单元格G51右下角的填充柄即可完成其余学生“总分”的统计工作。选中单元格区域A50:G150,按图2样式设置边框，添加底纹，文本居中显示4.6.2统计及格人数与及格率Step1创建统计表框架在单元格区域A1:J9按图3样式输入“高一年级各班及格人数与及格率统计表”框架信息。 Step2输入统计分数线选中单元格区域B3:D3，输入“60”，按组合键确认。 在单元格E3中输入“180”。 Step3统计及格人数

4、在单元格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分别输入如下公式，按组合键确认。a=SUM($A$51:$A$150=2)*(D$51:D$150=B$3)”a=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中输入公式：a=SUM(B5:B8)然后按键确认，即可

5、验证上述各班统计数之和是否等于年级统计数。 选中单元格区域B4:B9,向右拖曳单元格B9右下角的填充柄至E列即可完成“数学“、”英语“和”总分“的相应统计工作。 Step4统计及格率在单元格G4输入公式=B4/100“，然后按键确认。 选中单元格G4,向下拖曳单元格G4右下角的填充柄至单元格G8即可完成各班：“语文“学科及格率的统计。 在单元格G9输入公式：=SUM(G5:G8)然后按键确认。 选中单元格区域G4:G9,向右拖曳单元格G9右下角的填充柄至J列即可完成“数学“、”英语“和”总分“及格率的统计和验证工作。 选中单元格区域G4:J9,单击菜单“格式“7”单元格“，弹出”单元格格式“对

6、话框，切换到数字选项卡，在”分类“选项框中选择”百分比“，然后单击”确定“按钮，即可将该区域数值设置成百分比形式显示。 4.6.3统计优秀人数与优秀率Step1创建统计表框架在单元格区域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$

7、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列即可完成“数学“、英语“和”总分“的相应统计工作

8、。 Step4统计优秀率在单元格G15输入公式=B15/100“，然后按键确认。 选中单元格G15，向下拖曳单元格G15右下角的填充柄至单元格G19即可完成各班：“语文“学科及格率的统计。 在单元格G20输入公式：=SUM(G16:G19)“，然后按键确认。 选中单元格区域G15:G20,向右拖曳单元格G20右下角的填充柄至J列即可完成“数学“、英语“和”总分“优秀率的统计和验证工作。 选中单元格区域G15:J20,单击菜单“格式“r”单元格“，弹出”单元格格式“对话框，切换到数字选项卡，在”分类“选项框中选择”百分比“，然后单击”确定“按钮，即可将该区域数值设置成百分比形式显示。4.6.4统

9、计年级前20名中各班所占人数和比例Step1创建统计表框架在单元格区域A23:J31按图12样式输入统计表框架信息。Step2计算”前20名”分数线在单元格B25输入公式“=LARGE(D$51:D$150,$B$23)”，然后按键确认。选中单元格B25，向右拖曳单元格B25右下角的填充柄至e列完成公式填充，即可得到“数学英语“和”总分“第20名的分数。 Step3统计年级和各班所占前20名人数在单元格B26输入如下公式，然后按组合键确认。 “=SUM(ISNUMBER(D$51:D$150)*(D$51:D$150=B25)”在单元格B27输入如下公式，然后按组合键确认。 “=SUM($A$

10、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名

11、中年级和各班所占人数。 Step4统计中各班所占年级前20名的比例选中单元格区域G26:J26,输入“100%”，然后按组合键确认。 在单元格G27中输入公式：“=B27/B$26”然后按键确认。 选中单元格G27,向下拖曳单元格G27右下角的填充柄至G30完成公式填充。 在单元格G31中输入公式：“=SUM(G27:G30)”，然后按键确认。 选中单元格区域G27:G31,向右拖曳单元格G31右下角的填充柄至J列完成公式填充。4.6.5统计年级后20名中各班所占人数和比例Step1创建统计表框架在单元格区域A34:J42按图17样式输入统计表框架信息。Step2计算”后20名”分数线在单元格

12、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)

13、*(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$25”。 单击“格式”按钮弹出“单元格格式”对话框。 在“单元格格式”对话框中切换到“字体”选项卡，在“字形”列表框中选择“加粗加斜”。单击“颜色”右侧的下箭头按钮，在弹出的“颜色”调色板中选择“黄色”，切换到“图案”选项卡，在颜色框中选择“红色”。 单击“确定”按钮返回“条件格式”对话框。 单击“添加”按钮，单击“条件2”的下箭头选择“公式”，在其右侧的文本框中输入:=D51=B3)”其中ISNUMBER(D$51:D$150)含有100个元素为一维数组，ISNUMBER判断单元格区域D$51:D$150是否是数值，因为可能会有学生没有参加该课程考试，这时会在相应位置出现缺考”或类似字样文本，因为单元格区域D$51:D$150都有数值，所以ISNUMBER(D$51:D$150)返回含有100个“T的一维数组，(D$51:D$150=B3)为一含有100个逻辑判断的一维数组，返回含有86个“1”和14个“0”的一维数组，因此上面公式最后返回“86”。