大学计算机基础 课件 6.2.2 任务实现

大学计算机基础——基于计算思维（Windows10+Office2016）6.2.2任务实现第6章Excel综合应用6.2成绩分析1.任务分析

李老师要对电子信息工程专业13级的全体学生成绩做分析，要求：（1）计算学生的平均课程绩点。（2）按平均课程绩点及德育绩点降序排序。（3）如图所示，分类汇总每个班的平均课程绩点的平均值。1.任务分析

李老师要对电子信息工程专业13级的全体学生成绩做分析，要求：（4）如图所示，分类汇总每个班的平均课程绩点的平均值及班级人数。1.任务分析

李老师要对电子信息工程专业13级的全体学生成绩做分析，要求：（5）如图所示，不改变原始数据的前提下统计每个班的德育及平均课程绩点平均值。1.任务分析

李老师要对电子信息工程专业13级的全体学生成绩做分析，要求：（6）如图所示，不改变原始数据的前提下统计每个班的德育绩点的最大值和最小值。1.任务分析

李老师要对电子信息工程专业13级的全体学生成绩做分析，要求：（7）如图所示，筛选出可能获得奖学金的学生名单，即德育绩点3.0以上（含，下同），平均课程绩点2.88以上，单科课程绩点2.2以上（因为“主要干部”成绩绩点可以下调0.3）。由于学生成绩数据多，在进行成绩操作时，希望表题、学生姓名以及课程信息等一直显示在屏幕上，可以通过冻结窗格来实现。操作步骤如下：2.实现过程

1）复制信息并冻结窗格①新建工作簿“成绩分析.xlsx”，打开“相关素材.xlsx”，同时选择“成绩素材”和“课程学分”工作表，将它们复制到“成绩分析.xlsx”工作簿，关闭“相关素材.xlsx”。将“成绩分析.xlsx”的“成绩素材”工作表重命名为“平均课程绩点计算”。②选择“平均课程绩点计算”工作表的B3单元格。③单击“视图”选项卡/“窗口”组/“冻结窗格”按钮的“冻结拆分窗格”。就实现了表题、学生姓名以及课程信息等一直显示在屏幕上。可以通过鼠标左键拖动窗口的滚动条来查看冻结的效果。平均课程绩点（不含德育）计算公式为：，平均课程绩点带两位小数位（四舍五入)。具体操作如下：2.实现过程

2）平均课程绩点计算①选择I3单元格，输入“=”，用鼠标选取D3单元格，输入“*”，用鼠标选取“课程学分”工作表的B2单元格，按下F4键，将B2转换为绝对地址，此时，I3单元格编辑区域的内容为“=D3*课程学分!$B$2”。在此基础上，输入“+”，重复前面的操作，完成课程学分绩点=的计算。I3单元格编辑区域的内容最终为“=D3*课程学分!$B$2+平均课程绩点计算!E3*课程学分!$B$3+平均课程绩点计算!F3*课程学分!$B$4+平均课程绩点计算!G3*课程学分!$B$5+平均课程绩点计算!H3*课程学分!$B$6)”。

=

2.实现过程

2）平均课程绩点计算②剪切I3单元格编辑栏中“=”之后的内容，在“=”之后输入“(”，粘贴刚才剪切的内容，再输入“)”，I3单元格编辑区域的内容为“=(D4*课程学分!$B$2+平均课程绩点计算!E4*课程学分!$B$3+平均课程绩点计算!F4*课程学分!$B$4+平均课程绩点计算!G4*课程学分!$B$5+平均课程绩点计算!H4*课程学分!$B$6)”。③将鼠标定位在I3单元格编辑区域中内容的最后，输入“/”。④单击编辑栏的名称框区域，选取SUM函数（如果没有显示SUM函数，则通过“其他函数”来选取），如图所示。2.实现过程

2）平均课程绩点计算⑤SUM函数的Number1参数设置为“课程学分!$B$2:$B$6”。I3单元格的内容显示为“3.686666667”。⑥剪切I3单元格编辑栏中“=”之后的内容，单击“插入函数”按钮，在I3单元格插入ROUND函数。⑦ROUND函数的Number参数设置为剪切的内容“D3*课程学分!$B$2+平均课程绩点计算!E3*课程学分!$B$3+平均课程绩点计算!F3*课程学分!B4+平均课程绩点计算!G3*课程学分!$B$5+平均课程绩点计算!H3*课程学分!$B$6)/SUM(课程学分!$B$2:$B$6)”，Num_digits参数设置为2。I3单元格编辑区域的内容为“=ROUND((D3*课程学分!$B$2+平均课程绩点计算!E3*课程学分!$B$3+平均课程绩点计算!F3*课程学分!$B$4+平均课程绩点计算!G3*课程学分!$B$5+平均课程绩点计算!H3*课程学分!$B$6)/SUM(课程学分!$B$2:$B$6),2)”，I3单元格显示的结果为“3.69”。⑧选择I3单元格，移动鼠标，当鼠标变成填充柄时，双击鼠标左键，完成公式的复制。由于排序和分类汇总会改变原有数据的结构，而筛选会导致数据的显示发生变化，因此可以将“平均课程绩点计算”工作表复制成新的工作表。操作步骤如下：2.实现过程

3）排序、筛选和分类汇总数据准备①选择“平均课程绩点计算”工作表，复制为“平均课程绩点计算(2)”工作表。②由于被复制的工作表引用了“平均课程绩点计算”工作表的数据，为防止数据混乱，需要将其I列的数据复制后再“值粘贴”。③选择“平均课程绩点计算(2)”工作表，复制为“平均课程绩点计算(3)”工作表、“平均课程绩点计算(4)”工作表及“平均课程绩点计算(5)”工作表。④将“平均课程绩点计算(2)”工作表重命名为“成绩排序”，将“平均课程绩点计算(3)”工作表重命名为“分类汇总1”，将“平均课程绩点计算(4)”工作表重命名为“分类汇总2”，将“平均课程绩点计算(5)”工作表重命名为“数据筛选”。按平均课程绩点及德育绩点降序排序。操作步骤如下：2.实现过程

4）成绩排序①鼠标定位在“成绩排序”工作表数据区域的任一单元格。②选择“数据”选项卡/“排序和筛选”组/“排序”按钮，在弹出的如图所示对话框中，勾选“数据包含标题”，选择主要关键字为“平均课程绩点”，排序依据为“数值”，次序为“降序”。按平均课程绩点及德育绩点降序排序。操作步骤如下：2.实现过程

4）成绩排序③如图所示，单击“添加条件”，选择次要关键字为“德育绩点”，排序依据为“数值”，次序为“降序”。要分类汇总每个班的平均课程绩点的平均值，需要先按班级排序后再进行分类汇总。具体操作如下：2.实现过程

5）按班级分类汇总平均课程绩点平均值①如图所示，鼠标定位在“分类汇总1”工作表B列的任意单元格。选择“开始”选项卡/“编辑”组/“排序和筛选”按钮的“升序”，按班级升序排序数据。要分类汇总每个班的平均课程绩点的平均值，需要先按班级排序后再进行分类汇总。具体操作如下：2.实现过程

5）按班级分类汇总平均课程绩点平均值②如图所示，选择“数据”选项卡/“分级显示”组/“分类汇总”按钮，在弹出的对话框中选择分类字段为“班级”，汇总方式为“平均值”，选定汇总项为“平均课程绩点”。③切换到“2级”显示，就可以看到分类汇总的结果了。要分类汇总每个班的平均课程绩点平均值及班级人数，就需要先按班级对平均课程绩点分类汇总，然后再按班级对姓名进行分类汇总。具体操作步骤如下：2.实现过程

6）按班级分类汇总平均课程绩点平均值及人数①鼠标定位在“分类汇总2”工作表B列的任意单元格。选择“开始”选项卡/“编辑”组/“排序和筛选”按钮的“升序”，按班级升序排序数据。②选择“数据”选项卡/“分级显示”组/“分类汇总”按钮，在弹出的对话框中选择分类字段为“班级”，汇总方式为“平均值”，选定汇总项为“平均课程绩点”。要分类汇总每个班的平均课程绩点平均值及班级人数，就需要先按班级对平均课程绩点分类汇总，然后再按班级对姓名进行分类汇总。具体操作步骤如下：2.实现过程

6）按班级分类汇总平均课程绩点平均值及人数③如图所示，再次选择“数据”选项卡/“分级显示”组/“分类汇总”按钮，在弹出的对话框中选择分类字段为“班级”，汇总方式为“计数”，选定汇总项为“姓名”，不勾选“替换当前分类汇总”（表明在原有的分类汇总基础上再分类汇总）。④切换到“3级”显示，就可以看到分类汇总的结果了。要不改变原始数据的前提下统计每个班的德育绩点及平均课程绩点的平均值，可以使用数据筛选功能。具体操作步骤如下：2.实现过程

7）统计每个班的德育及平均课程绩点平均值①如同所示，鼠标定位在“平均课程绩点计算”工作表的任一单元格，选择“插入”选项卡/“表格”组/“数据透视表”按钮的“数据透视表”。要不改变原始数据的前提下统计每个班的德育绩点及平均课程绩点的平均值，可以使用数据筛选功能。具体操作步骤如下：2.实现过程

7）统计每个班的德育及平均课程绩点平均值②弹出如图所示的“创建数据透视表”对话框。在该对话框中，系统已经自动选择要分析的数据区域（用户也可以自己修改数据区域）。选择将产生的数据透视表放置在新工作表中。要不改变原始数据的前提下统计每个班的德育绩点及平均课程绩点的平均值，可以使用数据筛选功能。具体操作步骤如下：2.实现过程

7）统计每个班的德育及平均课程绩点平均值③在如图所示的工作表中，选择要添加到报表的“班级”、“德育绩点”及“平均课程绩点”字段，就在工作表的左侧生成了每个班的德育及平均课程绩点的和。选择“数据透视表工具/设计”选项卡/“数据透视表样式”组的“数据透视表样式浅色17”。要不改变原始数据的前提下统计每个班的德育绩点及平均课程绩点的平均值，可以使用数据筛选功能。具体操作步骤如下：2.实现过程

7）统计每个班的德育及平均课程绩点平均值④单击上图所示的“求和项：德育绩点”旁的下三角，弹出如图所示的快捷菜单，选择“值字段设置(N)…”。要不改变原始数据的前提下统计每个班的德育绩点及平均课程绩点的平均值，可以使用数据筛选功能。具体操作步骤如下：2.实现过程

7）统计每个班的德育及平均课程绩点平均值⑤在弹出的如图所示的“值字段设置”对话框中，将计算类型改为“平均值”。⑥将“求和项：平均课程绩点”的计算类型改为“平均值”，就可以得到每个班的德育及平均课程绩点的平均值了。⑦将透视结果所在的工作表重命名为“数据透视表1”。要统计每个班的德育绩点的最大值和最小值，可以使用数据筛选功能。具体操作步骤如下：2.实现过程

8）统计每个班的德育绩点的最大值和最小值①鼠标定位在“平均课程绩点计算”工作表的任一单元格，选择“插入”选项卡/“表格”组/“数据透视表”按钮的“数据透视表”。②在数据透视工作表中，选择要添加到报表的“班级”及“德育绩点”字段。③如图所示，鼠标指向“德育绩点”，单击鼠标右键，在弹出的快捷菜单中选择“添加到数值”。要统计每个班的德育绩点的最大值和最小值，可以使用数据筛选功能。具体操作步骤如下：2.实现过程

8）统计每个班的德育绩点的最大值和最小值④将数值中的“求和项：德育绩点”的计算类型改为“最大值”，“求和项：德育绩点2”的计算类型改为“最小值”，就可以得到每个班的德育绩点的最大值和最小值了。⑤透视结果所在的工作表重命名为“数据透视表2”。根据奖学金评选规则，可能获得奖学金的学生成绩应该满足：德育绩点3.0以上，平均课程绩点2.88以上，单科课程绩点2.2以上。因此，可以筛选出满足上述条件的数据。具体操作步骤如下：2.实现过程

9）筛选出可能获得奖学金的学生名单①如图所示，鼠标定位在“数据筛选”工作表的任一单元格。选择“数据”选项卡/“排序和筛选”组的“筛选”按钮。根据奖学金评选规则，可能获得奖学金的学生成绩应该满足：德育绩点3.0以上，平均课程绩点2.88以上，单科课程绩点2.2以上。因此，可以筛选出满足上述条件的数据。具体操作步骤如下：2.实现过程

9）筛选出可能获得奖学金的学生名单②如图所示，单击C2单元格（德育绩点）旁的下三角，在弹出的快捷菜单中选择“数字筛选”的“大于或等于(O)…”。根据奖学金评选规则，可能获得奖学金的学生成绩应该满足：德育绩点3.0以上，平均课程绩点2.88以上，单科课程绩点2.2以上。因此，可以筛选出满足上述条件的数据。具体操作步骤如下：2.实现过程

9）筛选出可能获得奖学金的学生名单③在弹出的如图所示的“自定义自动筛选方式”对话框中，将“德育绩点”设置为“大于或等于3.0”。根据奖学金评选规则，