学生成绩综合统计表.docx

学生成绩综合统计表.docx

《学生成绩综合统计表.docx》由会员分享，可在线阅读，更多相关《学生成绩综合统计表.docx（10页珍藏版）》请在冰点文库上搜索。

学生成绩综合统计表

学生成绩综合统计表

4.6学生成绩综合统计表

（一）案例背景

期末考试后，除了要统计各个分数段人数分布情况外（前面章节已经介绍了多种统计分数段的方法）可能还要统计年级和各班的及格人数与及格率，优秀人数与优秀率，以及各班里前、后若干名（如年级前20名和后20名）的人数和比例等。

本案例以某中学高一年级4个教学班100名学生的期末考试的3科成绩和总分为样本，统计全年级4个班级学生的期末考试及格人数与及格率，优秀人数与优秀率，以及各班里年级前20名和后20名的人数和比例，以便进行综合分析。

同时将前20名和后20名学生的成绩在统计表中标示出来，以使得前、后20名的成绩能够在成绩表突出显示。

要实现本案例中的功能，学员应该掌握以下EXCEL技术点

●基础知识条件格式

●函数应用ISNUMBER函数SUM函数，LARGE函数，SMALL函数

●综合应用函数嵌套数组公式。

最总效果展示

②在单元格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统计优秀人数与优秀率

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

Step4统计优秀率

①在单元格G15输入公式“=B15/100“，然后按键确认。

②选中单元格G15，向下拖曳单元格G15右下角的填充柄至单元格G19即可完成各班：

“语文“学科及格率的统计。

③在单元格G20输入公式：

“=SUM（G16:

G19）“，然后按键确认。

④选中单元格区域G15：

G20，向右拖曳单元格G20右下角的填充柄至J列即可完成“数学“、”英语“和”总分“优秀率的统计和验证工作。

⑤选中单元格区域G15：

J20，单击菜单“格式“→”单元格“，弹出”单元格格式“对话框，切换到数字选项卡，在”分类“选项框中选择”百分比“，然后单击”确定“按钮，即可将该区域数值设置成百分比形式显示。

4.6.4统计年级前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$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名中各班所占人数和比例

Step1创建统计表框架

在单元格区域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名的分数

Step1设置条件格式

①选中单元格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.2Step3之①的单元格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”。