大学计算机基础-基于计算思维课件 第5章 Excel基本应用5.5 课程成绩统计

大学计算机基础——基于计算思维（Windows10+Office2016）5.5.1知识点解析第5章Excel基本应用5.5课程成绩统计1.套用表格格式

Excel提供了自动格式化的功能，它可以根据预设的格式，将选择的单元格区域进行格式化。这些被格式化的单元格区域称之为表格。表格具有数据筛选、排序、汇总和计算等多项功能，并能自动扩展数据区域，构造动态报表等。通常，可以先套用表格的预定义格式来格式化工作表，然后用手工方式对其中不太满意的部分进行修改，就可以快速完成工作表的格式化。其中，number1,number2,...可以是具体的数值、单元格或单元格区域等。MAX(number1,number2,...)2.统计函数MAX、MIN与AVERAGE

MAX函数是求最大值函数，例如用来计算学生最高成绩、员工最高工资等。MAX函数语法格式为MIN函数是求最小值函数。其中，number1,number2,...可以是具体的数值、单元格或单元格区域等。MIN(number1,number2,...)MIN函数语法格式为其中，number1,number2,...可以是具体的数值、单元格或单元格区域等。AVERAGE(number1,number2,...)2.统计函数MAX、MIN与AVERAGE

AVERAGE函数是求平均数函数AVERAGE函数语法格式为需要注意的是，number1,number2,...中为空的单元格不会被计算，但为0的单元格会被计算。3.条件统计函数COUNTIF

如图所示，要统计游戏积分<16000的人数，可以按照如下方式来进行手动实现：（1）确定要查找的单元格区域（B3:B10）。（2）将游戏积分<16000的单元格找出来。（3）对找出来的单元格进行计数，得到的值就是要统计的游戏积分<16000的人数。其中，Range为要统计的单元格区域（即第1步中确定的单元格区域），Criteria为指定的条件（即第2步中给定的条件），COUNTIF函数将得到符合条件的单元格计数的结果（即第3步中的单元格计数）。COUNTIF(Range,Criteria)3.条件统计函数COUNTIF

而要实现上述功能的自动计算，可以采用COUNTIF函数来完成。COUNTIF函数的语法格式为针对上图，B11单元格中的公式为“=COUNTIF(B3:B10,"<

16000")”。其含义是：对B3:B10单元格区域中满足条件<

16000的单元格进行计数，得到的结果作为函数的计算结果。其中，criteria_range1和criteria1为统计单元格区域criteria_range1中满足条件criteria1的单元格个数。criteria_range2和criteria2为统计单元格区域criteria_range2中满足条件criteria2的单元格个数。依次类推。4.多条件统计函数COUNTIFS

COUNTIFS函数与COUNTIF函数类似，用来统计同时满足多个条件的单元格个数。COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]…)COUNTIFS函数的语法格式为COUNTIFS函数返回的最终结果是同时满足上述所有区域及条件的单元格个数。4.多条件统计函数COUNTIFS

例如，要统计图中B3:B10单元格区域中数值在16000～20000（含16000，不含20000）的单元格个数，可以采用COUNTIFS函数来实现。其对应的公式为“=COUNTIFS(B3:B10,">=16000",B3:B10,"<20000")”，计算结果为5。找出B3:B10区域中>=15000的单元格继续找出B3:B10区域中<20000的单元格5.数据图表

Excel图表可以用来表现数据间的某种相对关系，在常规状态下一般运用柱形图比较数据间的多少关系，用折线图反映数据间的趋势关系，用饼图表现数据间的比例分配关系等。图表区坐标轴绘图区图表标题数据系列数据表图表通常分为内嵌式图表和独立式图表。内嵌式图表是以“嵌入”的方式把图表和数据存放于同一个工作表，而独立式图表是图表独占一张工作表。如图所示，图表通常包含有图表区、绘图区、图表标题、坐标轴、数据系列、数据表等。5.数据图表

制作图表的通常步骤是：（1）选择要制作图表的数据区域。（2）选择图表类型，插入图表。（3）利用“图表工具”选项卡，对图表进行美化。（4）确定图表位置。大学计算机基础——基于计算思维（Windows10+Office2016）5.5.2任务实现第5章Excel基本应用5.5课程成绩统计1.任务分析

（1）如图所示，套用表格格式，快速格式化成绩统计表。计算总评成绩最高分、最低分、平均分，分别统计高于和低于总评成绩平均分的学生人数，以及分别统计期末成绩90以上、80～89、70～79、60～69、60以下的学生人数，平时和期末均85分以上的学生人数和学生总人数。对“课程成绩”工作表中的数据进行统计分析，要求：（2）如图所示，制作期末成绩及总评成绩的成绩分析图，要求图表下方显示数据表，图形上方显示数据标签，在顶部显示图例，绘图区和图表区设置纹理填充，图表不显示网格线。1.任务分析

将素材中的“统计素材”复制到“课程成绩”工作表之后，将其重命名为“成绩统计”工作表，完成如图所示的工作表的样式及内容设置。操作步骤如下：2.实现过程

1）工作表制作①打开“相关素材.xlsx”工作簿，选择“统计素材”工作表，复制到“课程成绩”工作表之后，并重命名为“成绩统计”工作表。②选择A2:B14单元格区域，单击“开始”选项卡/“样式”组/“套用表格样式”按钮，选择“表样式浅色16”，在弹出的“套用表格式”对话框中勾选“表包含标题”，如图所示。将素材中的“统计素材”复制到“课程成绩”工作表之后，将其重命名为“成绩统计”工作表，完成如图所示的工作表的样式及内容设置。操作步骤如下：2.实现过程

1）工作表制作③单击“表格工具—设计”选项卡/“工具”组/“转换为区域”，将表格转换为普通的单元格区域。④选择B3:B14单元格区域，设置其格式为“数值”，小数位数为0。计算总评成绩最高分、最低分、平均分，可以使用MAX函数、MIN函数及AVERAGE函数。2.实现过程

2）计算与统计统计高于和低于总评成绩平均分的学生人数、期末成绩90分以上以及60分以下的学生人数可以使用COUNTIF函数。统计期末成绩80～89、70～79、60～69的学生人数以及平时和期末均85分以上的学生人数，可以使用COUNTIFS函数。而要统计学生总人数，可以对学生期末考试成绩区域统计（使用COUNT）或对学生姓名区域统计（COUNTA函数）。

操作步骤如下：（1）.选择“成绩统计”工作表的B3单元格，单击“开始”选项卡/“编辑”组/“求和”按钮旁的下拉三角，在弹出的下来列表中选择“最大值（M）”，选择“课程成绩”工作表的I6:I41单元格区域，按下回车键，就完成了“成绩统计”工作表中B3单元格的总评成绩最高分的计算。B3单元格的公式为“=MAX(课程成绩!I6:I41)”，结果为97。（2）.分别选择“成绩统计”工作表的B4、B5单元格，完成总评成绩最低分和总评成绩平均分的计算，并将总评平均成绩四舍五入为整数。（3）.选择“成绩统计”工作表的B6单元格，插入COUNTIF函数，完成高于总评成绩平均分的学生人数统计。其中，参数Range为“课程成绩!I6:I41”，参数Creteria为“>81”。2.实现过程

操作步骤如下：（4）.选择“成绩统计”工作表的B7单元格，插入COUNTIF函数，完成低于总评成绩平均分的学生人数统计。其中，参数Range为“课程成绩!I6:I41”，参数Creteria为“<81”。（5）.选择“成绩统计”工作表的B8单元格，插入COUNTIF函数，完成期末成绩90以上的学生人数统计。其中，参数Range为“课程成绩!H6:H41”，参数Creteria为“>=90”。（6）.选择“成绩统计”工作表的B12单元格，插入COUNTIF函数，完成期末成绩60以下的学生人数统计。其中，参数Range为“课程成绩!H6:H41”，参数Creteria为“<60”。2.实现过程

操作步骤如下：（7）.选择“成绩统计”工作表的B9单元格，插入COUNTIFS函数，完成期末成绩80～89的学生人数统计。其中，Creteria_range1为“课程成绩!H6:H41”，参数Creteria1为“<90“，Creteria_range2为“课程成绩!H6:H41”，参数Creteria2为“>=80”。（8）.选择“成绩统计”工作表的B10单元格，插入COUNTIFS函数，完成期末成绩70～79的学生人数统计。其中，Creteria_range1为“课程成绩!H6:H41”，参数Creteria1为“<80”，Creteria_range2为“课程成绩!H6:H41”，参数Creteria2为“>=70”。（9）.选择“成绩统计”工作表的B11单元格，插入COUNTIFS函数，完成期末成绩60～69的学生人数统计。其中，Creteria_range1为“课程成绩!H6:H41”，参数Creteria1为“<70”，Creteria_range2为“课程成绩!H6:H41”，参数Creteria2为“>=60”。2.实现过程

操作步骤如下：（10）.选择“成绩统计”工作表的B13单元格，插入COUNTIFS函数，完成平时和期末均85分以上的学生人数统计。其中，Creteria_range1为“课程成绩!G6:G41”，参数Creteria1为“>=85”，Creteria_range2为“课程成绩!H6:H41”，参数Creteria2为“>=85”。（11）.选择“成绩统计”工作表的B14单元格，插入COUNT函数，通过统计期末成绩区域中有数值的单元格个数来获得学生总人数，B14单元格的公式为“=COUNT(课程成绩!H6:H41)”。2.实现过程2.实现过程

3）制作成绩分析图根据相关素材中的数据制作期末成绩及总评成绩的成绩分析图，要求图表下方显示数据表，图形上方显示数据标签，在顶部显示图例，绘图区和图表区设置纹理填充，图表不显示网格线，图表标题为“成绩分析图”，垂直轴标题为“学生人数”，生成的成绩分析图放置于新的工作表“成绩统计图”。

操作步骤如下：①打开“相关素材.xlsx”工作簿，选择“图表素材”工作表，复制到“成绩统计”工作表之后，并重命名为“成绩统计图数据”工作表。②选择A2:F4单元格区域，单击“插入”选项卡/“图表”组/“柱形图—二维柱形图”按钮的“簇状柱形图”，在当前工作表中就插入了一张基于选定单元格区域数据的图表。③鼠标左键单击图表的任一位置，该图表被选择（菜单中会出现“图表工具”选项卡）。④单击“图表工具-设计”选项卡/“图表布局”组/“布局5”。2.实现过程⑤单击“图表工具/设计/添加图表元素”选项卡/“网格线”按钮，在弹出的下拉列表中单击“主要横网格线”的“无”。⑥单击“图表工具/设计”选项卡/“添加图表元素”组/“数据标签”按钮，在弹出的下拉列表中单击“数据标签外”。单击“图例”按钮，在弹出的下拉列表中单击“顶部”。⑦单击“图表工具/设计”选项卡/“图表样式”组/“样式30”。

操作步骤如下：2.实现过程

操作步骤如下：⑧单击“图表工具/格式”选项卡/“设置所选内容格式”按钮，在弹出的对话框中单击“填充”选项卡，选择“图片或纹理填充”单选框，单击“纹理”图标，选择“新闻纸”。在图表区边缘单击鼠标左键，选择图表区，单击鼠标右键，在弹出下拉列表中单击“设置图表区格式”，在弹出的对话框中选择“图片或纹理填充”单选框，单击“纹理”图标，选择“新闻纸”，如图所示。⑨单击“图表工具—设计”选项卡/“位置”组/“移动图表”按钮，在弹出的对话框中输入选择新工作表，并输入“成绩统计图”。2.实现过程大学计算机基础——基于计算思维（Windows10+Office2016）5.5.3总结与提高第5章Excel基本应用5.5课程成绩统计1.引用单元格作为函数参数

在统计高于总评成绩平均分的学生人数时，参数Creteria为“>81”，而不是引用的B5单元格的数据。一旦某个学生的成绩发生改变，就会引起平均分的变化，从而需要修改公式的参数值。这显然达不到所期望的自动计算的效果。一旦B6单元格中的公式变为“=COUNTIF(课程成绩!I6:I41,">B5")”，得到的结果就变为0了，显然是错误的。通过“公式审核”功能，可以看到条件不是所期望的“大于B5单元格的值”，而是“大于B5”。那么，是否可以将参数Creteria改为“>B5”呢？因此，如果希望参数Creteria实现“大于B5单元格的值”，条件应该为“">"&B5”，B6中的公式为“=COUNTIF(课程成绩!I6:I41,">"&B5)”。在公式执行时，首先取B5中的值，然后将其与“>”进行连接，形成“>81”，作为参数Creteria的值。其中，&为连接符，实现将2个数据连接在一起。通过“追踪引用单元格”，工作表当中出现蓝色箭头，此箭头说明选中单元格的公式所引用的单元格，还可以继续通过“追踪引用单元格”，查看刚才被引用单元格中公式引用其他单元格的情况。通过“追踪从属单元格”，可以查看选中单元格中的值将会影响到哪些单元格，还可以继续通过“追踪从属单元格”，查看刚才被影响的单元格继续影响到其他的单元格中的值。如可以通过“移去箭头”删除追踪引用。2.公式审核

如图所示，通过“追踪引用单元格”和“追踪从属单元格”，可以查看公式引用是否正确。在上一节的“计算与统计”任务中，在进行总评成绩计算时，需要频繁使用到“课程成绩”工作表的单元格区域I6:I41，在引用过程中很容易出错。此外，对于公式“=MAX(课程成绩!I6:I41)”也无法看出是计算总评成绩中的最高分。3.用名称简化单元格区域的引用

因此，可以将“课程成绩”工作表的单元格区域I6:I41命名为“所有学生总评成绩”，这样，在需要引用“课程成绩”工作表的单元格区域I6:I41的地方，就可以直接用“所有学生总评成绩”来替代，从而简化单元格区域的引用。如计算总评成绩中的最高分的公式就可以写成“=MAX(所有学生总评成绩)”，简单明了。4.数据图表的类型

Excel的图表类型包括（1）柱形图：柱形图通常用于显示一段时间内的数据变化或说明各项之间的比较情况。柱形图分为簇状柱形图和三维簇状柱形图、堆积柱形图和三维堆积柱形图、百分比堆积柱形图和三维百分比堆积柱形图、三维柱形图以及圆柱图、圆锥图和棱锥图。（2）折线图：线图通常用于显示随时间而变化的连续数据。折线图分为折线图和带数据标记的折线图、堆积折线图和带数据标记的堆积折线图以及三维折线图。（3）饼图：饼图通常用于显示一个数据系列中各项所占的比例。饼图分为二维饼图和三维饼图、复合饼图和复合条饼图以及分离型饼图和三维分离型饼图。4.数据图表的类型

Excel的图表类型包括（4）条形图：条形图通常用于比较各项之间的情况。条形图包括簇状条形图和三维簇状条形图、堆积条形图和三维堆积条形图、百分比堆积条形图和三维百分比堆积条形图以及水平圆柱图、圆锥图和棱锥图。（5）面积图：面积图通常用于强调数量随时间而变化的程度