项目8 学生成绩分析与统计（课件）-《计算机应用基础》同步教学（电子工业出版社）

项目8 学生成绩分析与统计 一、项目提出 二、项目分析 三、相关知识点 四、项目实施 五、总结与提高 项目提出 项目背景 老师期末需要计算学生的成绩并制作相应的成绩分布图。 学生成绩由学生考勤情况、作业情况、期中成绩和期末成绩组成 。 项目提出 四张工作表 （1）学生考勤表 项目提出 四张工作表 （2）学生作业表 项目提出 四张工作表 （3）学生成绩表 项目提出 四张工作表 （4）期末成绩分析表 项目提出 需解决以下问题 问题1 计算“考勤分”、作业“平均分”和“总评分” 问题4 筛选出“期末成绩”不及格的同学，并降序排列 问题2 根据“总评分”计算“评级”,统计各分数段的学生数 问题3 对工作表进行各种格式设置 问题5 用图表显示“期末成绩”各分数段的学生人数 7 “学生考勤表”记录学生的到课情况，用“√”、“△”或“×”分别表示学生到课、迟到、旷课三种情况。 “学生作业表”输入的是百分制的作业成绩。 1、工作表中的数据 项目分析 在“学生考勤表”中，每一个“√”得10分，每一个“△”得5分，“×”不得分。 利用COUNTIF函数计算“√”、“△”的次数，再分别乘以10和5后相加得到考勤分。 在“作业表”中，利用AVERAGE函数计算6次作业成绩的“平均分”。 2、计算和统计数据 项目分析 在“成绩表”中，复制前两张表中的统计结果，根据公式计算“总评分”： 总评分=考勤分*10%+作业平均分*20%+期中成绩*20%+期末成绩*50% 总评分>=90，评为“优秀”；90>总评分>=80，评为“良好”；80>总评分>=70，评为“中等”；70>总评分>=60，评为“及格”；总评分<60，评为“不及格” 2、计算和统计数据 项目分析 为了使表格更加美观易读，可对工作表中的字体、框线等进行设置。 设置条件格式，让“期末成绩”不及格的分数用红色显示。 设置自动筛选条件为“期末成绩<60”,筛选出“期末成绩”不及格的同学，再对“期末成绩”进行降序排列。 3、格式和图表 项目分析 在“分析表”中，用图表的方式显示统计结果。 由于统计的是“期末成绩”各分数段的学生人数，所以用柱形图显示相对比较直观。 3、格式和图表 项目分析 5、操作流程图 项目分析 6、效果图 项目分析 6、效果图 项目分析 6、效果图 项目分析 相关知识点 在Excel 2019中，工作簿（又称Excel文件）是处理和存储数据的文件，其扩展名为“.xlsx”。 1、工作簿 相关知识点 Excel 2019中的工作表用于存储和处理数据，它行和列组成。每张工作表都有自己的名字。每个工作薄在新建的时候，默认包含一张标签名为Sheet1的工作表。 工作表的行号由1、2、3、…表示，列号由A、B、C、…表示。 2、工作表 相关知识点 单元格是表格的最小单位，单元格名称（单元格地址）由列号和行号组成，如A1单元格。 活动单元格是指当前正在操作的单元格，由一个加粗的边框标识。任何时候只能有一个活动单元格，只有在活动单元格中才可以输入数据。活动单元格右下角的小黑点，称为填充柄，拖动填充柄可把单元格内容自动填充或复制到相邻单元格中。 单元格区域由若干个相邻的单元格组成的矩形块，引用一个区域可用它的左上角单元格地址和右下角单元格地址表示，中间用冒号分隔，如“B2:F5”。 3、单元格 相关知识点 相对引用：如A1，这是最常见的引用方式，定义为：在复制公式时，公式中的引用地址会跟着发生变化。如D1单元格中有公式“=A1+B1”，当将公式复制到D2单元格时，公式变为“=A2+B2”。 4、单元格的引用 相关知识点 绝对引用：如$A$1，即在列号和行号前加一个“$”符号，定义为：在复制公式时，公式中的引用地址（$A$1）不会跟着发生变化。如D1单元格中有公式“=$A$1+$B$1”，当将公式复制到D2单元格时，公式仍为“=$A$1+$B$1”。 4、单元格的引用 相关知识点 混合引用：混合引用，如$A1，即在列号或行号前加一个“$”符号，定义为：在复制公式时，公式中引用地址的部分内容（列号或行号）会跟着发生变化。如D1单元格中有公式“=$A1+B$1”，当将公式复制到D2单元格时，公式变为“=$A2+B$1”。 4、单元格的引用 相关知识点 公式以等号“=”开始，其后才是公式的内容。公式的输入、编辑等操作都可以在编辑栏中完成。在单元格中显示的并不是公式本身，而是公式计算的结果。 公式中通常包含有函数。 5、公式 相关知识点 Excel中提供了大量的函数，利用函数可实现各种复杂的计算和统计。 Excel函数共有13类， 400多个函数，涵盖了财务、日期、工程、逻辑、数学、统计、文本等各种不同领域的数据处理任务。 其中有一类特别的函数称为“兼容性函数”，这些函数实际上已经由新函数替换，但为了与以前的版本兼容，依然在Excel 2019中提供这些函数。 6、函数 相关知识点 函数的语法为：函数名(参数1，参数2，…) 单击编辑栏左侧的“插入函数”按钮 ，可方便地插入各种函数。 6、函数 相关知识点 AVERAGE函数 主要功能：求出所有参数的算术平均值。 使用格式：AVERAGE(number1,number2,…) 参数说明：number1,number2,…，需要求平均值的数值或引用单元格（区域），参数个数不能超过255个。 应用举例：在B8单元格中输入公式：“＝AVERAGE(B7:D7,F7:H7,7,8)”，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值 6、函数 相关知识点 COUNTIF函数 主要功能：统计某个单元格区域中符合指定条件的单元格数目。 使用格式：COUNTIF(range,criteria) 参数说明：range代表要统计的单元格区域；criteria表示指定的条件表达式。 应用举例：在C17单元格中输入公式：“＝COUNTIF(B1:B13,"＞＝80")”，即可统计出B1至B13单元格区域中，数值大于或等于80的单元格数目。 6、函数 相关知识点 IF函数 主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。 使用格式：IF(logical,value_if_true,value_if_false) 参数说明：logical代表逻辑判断表达式；value_if_true表示当判断条件为逻辑“真（TRUE）”时的显示内容；value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容。 6、函数 相关知识点 IF函数 应用举例：在C29单元格中输入公式：“＝IF(C26＞＝18,"符合要求","不符合要求")”，如果C26单元格中的数值大于或等于18，则C29单元格显示“符合要求”字样，反之显示“不符合要求”字样。 6、函数 相关知识点 IFS函数 主要功能：检查是否满足一个或多个条件，且返回符合第一个TRUE条件的值。IFS可以取代多个嵌套IF语句，并且有多个条件时更方便阅读。 使用格式：IFS(logical_test1,value_if_true1,logical_test2,value_if_true2,…) 参数说明：logical_test1代表第1个逻辑判断表达式；value_if_true1表示当第1个判断条件为逻辑“真（TRUE）”时的显示内容。logical_test2代表第2个逻辑判断表达式；value_if_true2表示当第2个判断条件为逻辑“真（TRUE）”时的显示内容。以此类推。 6、函数 相关知识点 IFS函数 应用举例： 6、函数 相关知识点 数据筛选是使数据清单中只显示满足条件的数据记录，而将不满足条件的数据记录从视图中隐藏起来。 可以按颜色筛选、或按文本筛选。 7、筛选 相关知识点 排序方式有升序和降序两种，升序是指数据按照从小到大的顺序排列，降序是指数据按照从大到小的顺序排列，空格总是排在最后。 排序并不是针对某一列进行的，而是以某一列的数据大小为顺序，对所有的记录进行排序，即无论是升序还是降序排列，每一条记录的内容都不容改变，改变的只是它在数据清单中显示的位置。 8、排序 相关知识点 图表是指将工作表中的数据用图形表示出来。 Excel 2019提供了17类的标准图表类型，分别是柱形图、折线图、饼图、条形图、面积图、XY散点图、地图、股价图、曲面图、雷达图、树状图、旭日图、直方图、箱形图、瀑布图、漏斗图和组合图。 当工作表中的数据发生变化时，相应的图表也会跟着改变。 9、图表 相关知识点 Excel中有多种数据类型，常用的数据类型主要有文本型、数值型、日期型等。 文本型数据可以包括中文、字母、数字、空格和符号等，其对齐方式默认为左对齐。 要输入由纯数字组成的文本（如电话号码），须在其前加单引号，或者先输入一个等号(=)，再在文本前后加上双引号，如=“010” 10、常用数据类型及输入技巧 相关知识点 数值型数据包括0~9、( )、+、-等，其对齐方式默认为右对齐。当输入绝对值很大或很小的数时，自动改为科学计数法表示（如2.34E+12）。 小数位数超过设定值时，自动“四舍五入”。 输入分数时，要先输入0和空格，如要输入分数1/4，正确的输入方法是：“0 1/4”，否则Excel会将分数当成日期。 10、常用数据类型及输入技巧 相关知识点 日期型数据的格式为“年-月-日”或“年/月/日”，当年的年份可省略不输入，但“月”和“日”必须输入，如输入5/4，一般在单元格中显示为“5月4日”，其对齐方式默认为右对齐。 快速输入的方法有很多，如利用填充柄自动填充、自定义序列、按Ctrl+Enter键可在选定区域中自动填充相同数据等。 10、常用数据类型及输入技巧 项目实施 任务1、计算考勤分、作业平均分和总评分 （1）计算考勤分 在M3单元格中输入公式“＝COUNTIF(C3:L3,$L$3)＊10＋COUNTIF(C3:L3,$G$3)＊5” 双击M3单元格的填充柄 项目实施 任务1、计算考勤分、作业平均分和总评分 （2）计算作业“平均分” 选中I3单元格，单击“编辑”组中的“自动求和”下拉按钮，选择“平均值”选项 拖动I3单元格的填充柄至I32单元格，计算其他同学的作业“平均分” 项目实施 任务1、计算考勤分、作业平均分和总评分 “平均分”保留0位小数 项目实施 任务1、计算考勤分、作业平均分和总评分 （3）计算“总评分” 把“考勤分”和作业“平均分”选择性粘贴到“成绩表”的相应单元格区域中 总评分＝考勤分＊10%＋作业平均分＊20%＋期中成绩＊20%＋期末成绩＊50% 在G3单元格中输入公式“＝C3＊0.1＋D3＊0.2＋E3＊0.2＋F3＊0.5”，并拖动G3单元格的填充柄至G32单元格，再设置G3:G32单元格区域的小数位数为1。 项目实施 任务2、计算“评级”，统计各分数段学生数 如果总评分＞＝90，评级为“优秀”；90＞总评分＞＝80，评级为“良好”；80＞总评分＞＝70，评级为“中等”；70＞总评分＞＝60，评级为“及格”；总评分＜60，评级为“不及格”。 在H3单元格中输入公式“＝IF(G3＞＝90,"优秀",IF(G3＞＝80,"良好",IF(G3＞＝70,"中等",IF(G3＞＝60,"及格","不及格")))) ” 也可使用公式“＝IFS(G3＞＝90,"优秀",G3＞＝80,"良好",G3＞＝70,"中等",G3＞＝60,"及格",TRUE,"不及格")”。 拖动H3单元格的填充柄至H32单元格 项目实施 任务2、计算“评级”，统计各分数段学生数 项目实施 任务2、计算“评级”，统计各分数段学生数 当统计“期末成绩”在80～90之间的人数时，这里有二个统计条件要同时满足，一个是“＞＝80”，另一个是“＜90”，可以先用COUNTIF函数计算出“期末成绩＞＝80”的人数，再减去用COUNTIF函数计算出的“期末成绩＞＝90”的人数即可。计算其他分数段的人数时，可用类似的方法处理。 项目实施 任务2、计算“评级”，统计各分数段学生数 在B3单元格中输入公式“＝COUNTIF(成绩表!$F$3:$F$32,"＞＝90")” 在B4单元格中输入公式“＝COUNTIF(成绩表!$F$3:$F$32,"＞＝80")－B3” 在B5单元格中输入公式“＝COUNTIF(成绩表!$F$3:$F$32,"＞＝70")－B3－B4” 在B6单元格中输入公式“＝COUNTIF(成绩表!$F$3:$F$32,"＞＝60")－B3－B4－B5” 在B7单元格中输入公式“＝COUNTIF(成绩表!$F$3:$F$32,"＜60")” 项目实施 任务3、设置表格格式 设置表格的字体和对齐方式 为表格添加边框 项目实施 任务3、设置表格格式 设置条件格式：“期末成绩”不及格的分数显示为红色 项目实施 任务4、筛选“期末成绩”不及格的同学 单击“编辑”组中的“排序和筛选”下拉按钮 ，选择“筛选”选项 单击“期末(50%)”单元格右侧的下拉箭头，选择“数字筛选”→“小于”选项 项目实施 任务4、筛选“期末成绩”不及格的同学 选中某个期末成绩所在的单击格，单击“编辑”组中的“排序和筛选”下拉按钮 ，在打开的下拉列表中选择“降序”选项 项目实施 任务5、图表显示各分数段的学生数 在“分析表”中，选中A2:B7单元格区域，在“插入”选项卡中，单击“图表”组中的“柱形图”下拉按钮，选择“簇状柱形图” 项目实施 任务5、图表显示各分数段的学生数 设置图表格式 项目实施 任务5、图表显示各分数段的学生数 移动图表 总结与提高 总结与提高 在Excel 2019中有很多快速输入数据的技巧，如自动填充、自定义序列等，熟练掌握这些技巧可以提高输入速度。 在输入数据时，要注意数据的类型，对于学号、邮编、电话号码等数据应该设置为文本型，即在前面加单引号。 工作表的格式设置，主要包括对工作表中数据的格式化、字体格式、行高和列宽、数据的对齐方式、表格的边框和底纹等进行设置。 总结与提高 总结与提高 在使用公式和函数时，要注意以下几点 （1）输入公式前应先输入“＝”。 （2）函数的引用形式为：函数名(参数1，参数2，… )，参数之间用逗号隔开。如果是单独使用函数，要在函数名称前输入“＝”构成公式。如果单击编辑栏左侧的“插入函数”按钮 来插入函数，会自动在函数名称前面加上“＝”。 （3）复制公式时，公式中使用的单元格引用需要随着所在位置的不同而变化时，应该使用单元格的“相对引用”；不随所在位置变化的使用单元格的“绝对引用”。 总结与提高 总结与提高 使用COUNTIF函数时要注意，在复制公式时，如果参数“Range”的引用区域固定不变，应使用“绝对引用”；如果参数“Criteria”不是单元格引用，而是表达式或字符串，应使用西文双引号括起来。 使用IF函数时要判断给出的条件是否满足，如果满足，返回逻辑值为真时的值；如果不满足，返回逻辑值为假的值。如果判断条件超过两个时，采用IF函数的嵌套，就是将一个IF函数返回值作为另一个IF函数的参数。 IFS可以取代多个嵌套IF语句，并且有多个条件时更方便阅读。 $$