项目四 任务三 统计并分析学生成绩表（课件）《计算机应用基础项目教程》（航空工业出版社）同步教学

计算机应用基础项目教程 模板来自于 http://docer.wps.cn 1 Excel是Office办公套装软件中的另一个重要成员，它是一款优秀的电子表格制作软件，具有强大的数据组织和处理功能。利用它可以快速制作出各种美观、实用的电子表格，以及对表格中的数据进行计算、统计、分析和预测等，并可按需要将表格打印出来。本项目主要学习Excel的使用方法。 项目四 使用Excel设计与制作电子表格 Transition Page 过渡页 学习 目标 01 掌握在工作表中输入、编辑数据并设置表格格式的方法。 02 掌握使用公式和函数对工作表数据进行计算的方法。 03 掌握使用排序、筛选、分类汇总和条件格式对工作表数据进行管理的方法。 04 掌握利用图表对工作表数据进行统计分析的方法。 05 掌握利用数据透视表和数据透视图对工作表数据进行统计分析的方法。 Transition Page 过渡页 过渡页 任务三 统计并分析学生成绩表 ——数据排序与函数的高级应用 Transition Page 任务背景 学生成绩是学籍管理中必不可少的数据信息。在学校的教学工作中，对学生的成绩进行统计分析是一项非常重要的工作。每个学期结束后，教师都会统计学生本学期的成绩，一份合理的学生成绩表可以帮助教师了解每个学生及整个班级的学习情况。 任务三 统计并分析学生成绩表 分析任务 本任务主要完成的工作是制作“学生成绩表”，通过该任务让读者了解Excel的数据有效性、数据排序和复杂函数的高级应用等知识。任务完成效果如图4-58所示。 图4-58 计算后的学生成绩表 任务三 统计并分析学生成绩表 任务目标 能利用公式对工作表数据进行计算。 能对工作表中的数据进行单条件和多条件排序。 能利用函数对工作表数据进行统计和分析。 任务实施 一、设置数据有效性 在建立工作表的过程中，为了保证输入的数据都在其有效范围内，用户可以使用Excel提供的“有效性”命令为单元格设置条件，以便在出错时得到提醒，从而快速、准确地输入数据。 任务三 统计并分析学生成绩表 步骤1 ☞ 新建“学生成绩表”工作簿，并将其“Sheet1”工作表重命名为“成绩表”。 步骤2 ☞ 假设在学生成绩表中各门课程对应的成绩范围在0～100之间，下面在工作表中设置数据有效性。先在工作表中选定需要设置数据有效性的单元格区域D3:F32，然后在“数据”选项卡“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框。 步骤3 ☞ 在“设置”选项卡的“允许”下拉列表中选择的类型不同，其有效检验的数据类型就不同。这里选择“小数”项，然后在“数据”下拉列表中选择“介于”，并分别在“最小值”和“最大值”编辑框中输入0和100，如图4-59（a）所示。 任务三 统计并分析学生成绩表 步骤4 ☞ 单击“输入信息”选项卡，选中“选定单元格时显示输入信息”复选框。在“标题”编辑框中输入文本标题“输入成绩”，在“输入信息”编辑框中输入提示内容“请输入对应课程成绩（0～100之间）”，如图4-59（b）所示。 图4-59 设置数据有效性、输入提示信息 （a） （b） 任务三 统计并分析学生成绩表 步骤5 ☞ 单击“出错警告”选项卡，选中“输入无效数据时显示出错警告”复选框，在“样式”下拉列表中选择“停止”选项。在“标题”和“错误信息”编辑框中输入相应的出错信息文字，如图4-60所示。 步骤6 ☞ 单击“输入法模式”选项卡，在“模式”下拉列表中选择“关闭（英文模式）”选项，如图4-61所示，单击“确定”按钮完成数据有效性的设置。 图4-60 输入出错警告 图4-61 选择输入法模式 任务三 统计并分析学生成绩表 步骤7 ☞ 单击“出错警告”选项卡，选中“输入无效数据时显示出错警告”复选框，在“样式”下拉列表中选择“停止”选项。在“标题”和“错误信息”编辑框中输入相应的出错信息文字，如图4-60所示。 图4-62 提示信息及出错提示框 任务三 统计并分析学生成绩表 二、在工作表中输入基础数据 在设置了数据有效性的工作表中输入表格标题、数据表的列标签及各项记录，操作步骤如下。 步骤1 ☞ 在工作表的第1行和第2行输入表格标题和数据清单的列标签，如图4-63所示。 图4-63 输入表格标题和列标签 数据清单列标签 表格标题 任务三 统计并分析学生成绩表 步骤2 ☞ 在A3单元格输入学号，然后将鼠标指针移至该单元格右下角的填充柄上，向下拖动至A32单元格，释放鼠标后，单击 按钮，在展开的列表中选择“填充序列”选项，如图4-64所示。 图4-64 自动填充学号序列 任务三 统计并分析学生成绩表 提示 以上使用Excel内置的序列快速输入了学号。若要自定义序列，可在“文件”列表中选择“选项”选项，打开“Excel选项”对话框，在“高级”选项的“常规”列表中单击“编辑自定义列表”按钮，打开“自定义序列”对话框，在“自定义序列”列表中选择“新序列”，再在“输入序列”编辑框中输入要自定义的序列，如“第1日”、“第2日”、“第3日”（按【Enter】键在各日之间换行），最后单击“添加”按钮。之后即可使用填充柄快速输入自定义的序列了。 任务三 统计并分析学生成绩表 步骤3 ☞ 分别输入除总分、平均分和名次之外的其他记录，以及在表格下方制作统计表格，如图4-65所示。 图4-65 输入其他记录 任务三 统计并分析学生成绩表 三、计算平均分、总分、名次和是否合格数据 在设置了数据有效性的工作表中输入表格标题、数据表的列标签及各项记录，操作步骤如下。 1 函数结构 函数以等号（=）开始，后面紧跟函数名称、括号和函数参数，如图4-66所示。 图4-66 函数的结构 函数名称 函数参数 函数结构 AVERAGE(number1, ( number2), ( number3), ( number4), …) 参数提示 =AVERAGE(A1,B2:C3,D6) 任务三 统计并分析学生成绩表 2 函数参数 参数可以是数字、文本、逻辑值（例如TRUE或FALSE）、数组、错误值（例如 #N/A）或单元格引用。指定的参数都必须为有效参数值。参数也可以是常量、公式或其他函数。 插入函数的常用方法有两种：在单元格或“编辑栏”中直接输入函数及公式，此法多用于已熟悉的函数；通过“插入函数”和“函数参数”对话框插入函数，此法简单易用。 在Excel中，系统已经将常用的求和、平均值、计数、最大值和最小值5个函数直接放入功能区命令列表，方便用户快速调用。统计平均分、总分、名次和是否合格等各项数据的操作步骤如下。 任务三 统计并分析学生成绩表 步骤1 ☞ 计算平均分。单击G3单元格，然后单击“开始”选项卡“编辑”组中“自动求和”按钮右侧的三角按钮 ，在展开的列表中选择“平均值”选项，系统自动对所选单元格左侧有数据的连续单元格进行求平均值计算，如图4-67所示。 图4-67 计算第1个学生的平均分 任务三 统计并分析学生成绩表 步骤2 ☞ 确认要进行计算的数据区域是否正确，本例保持默认值不变，按【Enter】键，得到计算结果。向下拖动G3单元格右下角的填充柄到G32单元格后释放鼠标，可计算出所有学生的平均分。 步骤3 ☞ 计算总分。单击H3单元格，然后单击“自动求和”按钮，同样可看到系统自动对所选单元格左侧有数据的连续单元格进行求和计算。因为该单元格区域引用不正确，所以重新在工作表中选择D3:F3单元格区域，如图4-68所示。 图4-68 计算第1个学生的总分 任务三 统计并分析学生成绩表 步骤4 ☞ 按【Enter】键，得到计算结果。向下拖动H3单元格右下角的填充柄到H32单元格后释放鼠标，可计算出所有学生的总分。 步骤5 ☞ 根据总分排名次。在I3单元格单击，然后单击编辑栏中的“插入函数”按钮 ，打开“插入函数”对话框，在“或选择类别”下拉列表中选择“统计”类别，然后在下方的“选择函数”列表中选择“RANK.EQ”函数，如图4-69所示。 图4-69 选择函数 任务三 统计并分析学生成绩表 步骤6 ☞ 单击“确定”按钮，打开“函数参数”对话框，在第1个参数编辑框中单击，然后在工作表中单击要进行排名的单元格H3；在第2个参数编辑框中单击，然后在工作表中选择要参与排名的单元格区域H3:H32，返回工作表后按【F4】键，将相对引用转换为绝对引用；在第3个编辑框中输入0，如图4-70所示。 图4-70 设置函数参数 任务三 统计并分析学生成绩表 步骤7 ☞ 单击“确定”按钮，得到排名结果，如图4-71所示。 图4-71 根据总分排名次 步骤8 ☞ 向下拖动I3单元格右下角的填充柄到I32单元格后释放鼠标，可根据总分排出学生的名次。 任务三 统计并分析学生成绩表 提示 RANK.EQ()函数的作用是计算一个数字在数字列表中的排位。数字的排位是依据其与列表中其他值的大小进行比较得到的。若有多个值具有相同的排位，则其排位相同。 语法：RANK.EQ(number, ref, order) number：需要查找排位的数字。 ref：排位数字列表或对数字列表的引用区域。ref中的非数值型参数将被忽略。 order：为一数字，指明排位的方式。如果order为0（零）或省略，则排位是对ref按照降序排列排位；如果order不为零，则排位是对ref按照升序排列排位。 函数RANK.EQ()对相同的数排位相同，但将影响后续数值的排位。例如，在一列按升序排列的整数中，如果整数25出现两次，其排位为7，则26的排位为9（顺序上第8位被第二个排位7占用）。 任务三 统计并分析学生成绩表 步骤9 ☞ 根据平均分判断成绩合格与否。假设平均分大于等于60分为合格，否则不合格。在J3单元格输入公式“=IF(G3>=60,"合格","不合格")”，按【Enter】键得到计算结果，如图4-72所示。同样地，利用拖动填充柄的方法判断出其他学生的成绩合格与否。 图4-72 根据平均分判断合格与否 任务三 统计并分析学生成绩表 提示 IF()函数的作用是根据指定条件，执行真假值判断，返回真假情况下的结果。 语法：IF(logical_test, value_if_true, value_if_false) logical_test：判断条件，为计算结果是True或False的任意值或表达式。 value_if_true：真值，当logical_test条件为True时的结果（值）。value_if_true也可以是其他公式（含嵌套函数）。若省略该参数，则该项结果默认为0。 value_if_false：假值，当logical_test条件为False时的结果（值）。value_if_false也可以是其他公式（含嵌套函数）。若省略该参数，则该项结果默认为0。 任务三 统计并分析学生成绩表 四、设置表格格式 步骤1 ☞ 选中A1:J1单元格区域，单击“开始”选项卡“对齐方式”组中的“合并及居中”按钮 ，合并居中表格标题，然后设置标题字符的字体为隶书，字号为26，字形为加粗，字体颜色为深红。 步骤2 ☞ 选中A2:J2单元格区域即数据清单列标题，设置其字体为黑体，字号为13，单元格填充颜色为蓝色；再设置该行行高为20。 步骤3 ☞ 选中A3:J32单元格区域，设置其字体颜色为紫色。 步骤4 ☞ 选中A3:A32数据区域即学号所在的列，在“数字格式”列表中选择“文本”选项，将此区域的数字作为文本处理。 任务三 统计并分析学生成绩表 步骤5 ☞ 选中D3:G32数据区域即三科成绩，在“设置单元格格式”对话框的“数字”选项卡的“分类”列表中选择“数值”选项，然后设置小数点位数为“1”，负数为“-1234.0”，如图4-73所示。 图4-73 设置成绩的数值格式 任务三 统计并分析学生成绩表 步骤6 ☞ 选中H3:I32数据区域即平均分和总分，在“单元格格式”对话框的“数字”选项卡的“分类”列表中选择“数值”选项，然后设置小数点位数为“2”，负数为“-1234.10”，如图4-74所示。 图4-74 设置平均分和总分的数值格式 任务三 统计并分析学生成绩表 步骤7 ☞ 选中A2:J36单元格区域，即除标题外的所有单元格区域，在“设置单元格格式”对话框中设置内外边框为蓝色单实线，参数设置如图4-75所示。 图4-75 设置蓝色内外单边框线 任务三 统计并分析学生成绩表 步骤8 ☞ 选中A2:J2单元格区域，即列标题项数据，设置该区域的上下边框为黑色粗实线，参数设置如图4-76所示。 图4-76 设置黑色上下边框线 任务三 统计并分析学生成绩表 步骤9 ☞ 将各项统计表格中的相关单元格进行合并，然后为其设置填充颜色，调整各列列宽，使列标题和记录完全显示，最后将A2:J36单元格区域的数据居中显示，格式化后的工作表如图4-77所示。 图4-77 格式化工作表效果 任务三 统计并分析学生成绩表 五、数据排序 排序是对工作表中的数据进行重新组织安排的一种方式。Excel可以对整个工作表或选定的某个单元格区域进行排序。在Excel中，可以对一列或多列中的数据按文本、数字以及日期和时间进行排序，还可以按自定义序列（如大、中和小）进行排序。大多数排序操作都是针对列进行的，但也可以针对行进行。 1 简单排序 简单排序是指对工作表中的单列数据按照Excel默认的升序或降序方式进行排列。 任务三 统计并分析学生成绩表 1）升序排序 数字：按从最小的负数到最大的正数进行排序。 日期：按从最早的日期到最晚的日期进行排序。 文本：按照特殊字符、数字（0…9）、小写英文字母（a…z）、大写英文字母（A…Z）、汉字（以拼音排序）进行排序。 逻辑值：FALSE排在TRUE之前。 错误值：所有错误值（如#NUM!和#REF!）的优先级相同。 空白单元格：总是放在最后。 任务三 统计并分析学生成绩表 2）降序排序 与升序排序的顺序相反。 在Excel中，如果只是对一列数据进行排序，即进行简单排序，可选中该列中的任意单元格，如成绩表中的“总分”列数据，再单击“数据”选项卡“排序和筛选”组中的“升序”按钮 或“降序”按钮 ，如图4-78所示。此时，同一行其他单元格的位置也将随之变化。 图4-78 对“总分”列进行降序排序 任务三 统计并分析学生成绩表 2 多关键字排序 Excel允许数据按多个关键字进行排序。对多个关键字进行排序时，在主要关键字完全相同的情况下，会根据指定的次要关键字进行排序；在次要关键字完全相同的情况下，会根据指定的下一个次要关键字进行排序，依次类推。另外，为了获得最佳结果，要排序的单元格区域应包含列标题。 任务三 统计并分析学生成绩表 例如，要将成绩表按总分、性别和网络基础进行降序排序，操作步骤如下。 步骤1 ☞ 复制“成绩表”副本，然后将其重命名为“多关键字排序”，然后删除下方的统计表格。单击要进行排序操作的工作表中有数据的任意单元格，再单击“数据”选项卡“排序和筛选”组中的“排序”按钮 ，如图4-79所示。 图4-79 单击“排序”按钮 步骤2 ☞ 在打开的“排序”对话框中分别设置主要关键字、次要关键字和第二次要关键字条件。本例分别在“主要关键字”、“次要关键字”和第二“次要关键字”下拉列表中选择总分、性别和网络基础，并设置相应的排序方式，如图4-80所示。 单击“添加条件”按钮，可为排序添加更多次要关键字 任务三 统计并分析学生成绩表 步骤3 ☞ 单击“确定”按钮，可看到主要关键字相同的，会根据次要关键字进行排序，依此类推，如图4-81所示。最后保存工作簿。 图4-81 排序结果 任务三 统计并分析学生成绩表 提示 选中“排序”对话框中的“数据包含标题”复选框，表示选定区域的第一行作为标题，不参加排序，始终放在原来的行位置；如果取消该复选框，表示将选定区域第一行作为普通数据看待，参与排序。 任务三 统计并分析学生成绩表 六、批注及函数的高级应用 下面先为单元格添加批注，然后利用函数计算统计表格中的各项数据，操作步骤如下。 步骤1 ☞ 添加批注。在“全班合格率”单元格单击，然后单击“审阅”选项卡“批注”组中的“新建批注”按钮，在出现的批注框中输入批注文本，如图4-82所示。用同样的方法可为“全班优秀率”单元格添加批注。 图4-82 添加批注 任务三 统计并分析学生成绩表 步骤2 ☞ 在D33单元格单击，然后输入带函数的公式，得到计算结果，如图4-83所示。 图4-83 输入函数 提示 COUNT()函数的作用是返回包含数字以及包含参数列表中的数字的单元格的个数，还可以统计单元格区域或数字数组中数字字段的输入项的个数。 语法：COUNT(value1, value2, …) value1, value2等为包含或引用各种类型数据的参数（1～255个），但只有数字类型的数据才被计算。至少要有一个参数。 若需计算单元格区域中不为空的单元格的个数，则用COUNTA()函数。 任务三 统计并分析学生成绩表 步骤3 ☞ 在D34单元格单击，然后输入带函数的公式，计算出男生人数，如图4-84所示。 图4-84 输入公式计算出男生人数 提示 COUNTIF()函数的作用是统计某个单元格区域中符合指定条件的单元格个数。 语法：COUNTIF(range, criteria) range：为要统计单元格个数的单元格区域。 criteria：为指定的条件表达式。 任务三 统计并分析学生成绩表 步骤4 ☞ 在D35单元格输入公式“=D33-D34”，计算出女生人数。 步骤5 ☞ 在D36单元格输入公式“=COUNTIF(E3:E32,"<60")”，如图4-85所示，计算出数学不及格的人数。 图4-85 输入公式计算出数学不及格的人数 任务三 统计并分析学生成绩表 步骤6 ☞ 在G33单元格输入公式，如图4-86所示，计算出专业英语缺考人数。 图4-86 输入公式计算专业英语缺考人数 提示 COUNTBLANK()函数的作用是计算指定单元格区域中空白单元格的个数。 语法：COUNTBLANK(range) range：指定的单元格区域。 任务三 统计并分析学生成绩表 步骤7 ☞ 分别在G34、G35和G36单元格输入公式，计算出总分最高分、平均分最低分和平均分大于85的人数，如图4-87所示。 图4-87 计算出总分最高分、平均分最低分和平均分大于85的人数 任务三 统计并分析学生成绩表 提示 MAX()函数的作用是返回一组值中的最大值。 语法：MAX(number1, number2, …) number1, number2等为要从中找出最大值的1～255个数字参数，至少要有一个参数。 MIN()函数的作用是返回一组值中的最小值。 语法：MIN(number1, number2, …) number1, number2等为要从中找出最小值的1～255个数字参数，至少要有一个参数。 任务三 统计并分析学生成绩表 步骤8 ☞ 分别在J33、J34、J35和J36单元格输入公式，计算出专业英语实考人数、平均分小于60的人数、全班合格率和全班优秀率，然后选中后两项数据所在单元格，单击“开始”选项卡“数字”组中的“百分比样式”按钮 ，设置其数字格式为百分比，如图4-88所示。 图4-88 计算专业英语实考人数、平均分小于60的人数、全班合格率和全班优秀率 任务三 统计并分析学生成绩表 高手秘籍 1 如何输入数组公式 数组公式是指可以同时对一组或两组以上的数据进行计算的公式，计算的结果可能是一个，也可能是多个。 数组公式中使用的数据称为数组参数。数组参数可以是一个区域数组，也可以是一个常量数组。 提示 区域数组是一个矩形的单元格区域，如$A$1:$D$5；常量数组是一组给定的常量，如{1,2,3}或{1,2,3;1,2,3}。 数组公式中的参数必须为矩形区域，否则无法引用。例如，无法引用{1,2,3;1,2}。 任务三 统计并分析学生成绩表 要输入数组公式，首先必须选择用来存放结果的单元格区域（也可以是一个单元格），然后输入公式，最后按【Ctrl+Shift+Enter】组合键锁定数组公式，Excel将在公式两边自动加上花括号“{}”。 例如，当需要计算两个相同矩形区域对应单元格的数据之积时，可以用数组公式一次性计算出所有的乘积值，并保存在另一个大小相同的矩形区域中。 步骤1 ☞ 选择要放置计算结果的单元格区域E3:E11，输入数组公式“=C3:C11*D3:D11”，如图4-89所示。 图4-89 选择单元格区域并输入数组公式 任务三 统计并分析学生成绩表 步骤2 ☞ 按【Ctrl+Shift+Enter】组合键，得到所有商品的销售额，如图4-90所示。 图4-90 计算结果 提示 如果在步骤2中按下【Enter】键，则输入的只是一个简单公式，Excel只会在选中的单元格区域的第一个单元格（选定区域的左上角单元格）中显示一个计算结果。 任务三 统计并分析学生成绩表 2 使用IF嵌套函数划分学生成绩等级 假设成绩评定条件（以平均分为依据）为：90～100分为优，80～90分为良，60～80分为中，60分以下为不合格。 以学生成绩表中的数据为例，在“是否合格”列的右侧添加“等级”列，然后根据评定条件在K3单元格输入公式“=IF(G3>90,"优",IF(G3>80,"良",IF(G3>60,"中","不及格")))”，即可根据评定条件划分学生成绩等级，如图4-91所示。向下复制公式可将所有学生的成绩根据平均分进行等级划分。 图4-91 利用IF函数划分学生成绩等级 任务三 统计并分析学生成绩表 拓展训练 统计和分析会计班成绩 打开本书配套素材“项目四”>“13会计班成绩表素材”工作簿，如图4-92所示。利用所学知识，对其进行计算、统计和分析。 图4-92 素材文件效果图 任务三 统计并分析学生成绩表 1 训练目的 练习公式、RANK.EQ、IF、COUNTIF、AVERAGE、COUNTIFS函数的使用方法。 2 训练内容 （1）求出每个同学的平均分（要求保留1位小数）、总分和名次。 （2）求出每个学生的等级。平均分大于等于90，为“优”；平均分大于等于80而小于90，为“良”；平均分大于等于70而小于80，为“中”；平均分大于等于60而小于70，为“及格”；平均分小于60，为“差”。 （3）求出每门课程和平均分的最高分和最低分、大于等于90分的人数、大于等于80而小于90分的人数、大于等于70而小于80分的人数、大于等于60而小于70分的人数、不及格的人数。 任务三 统计并分析学生成绩表 $$