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

大学计算机基础——基于计算思维（Windows10+Office2016）6.4.2任务实现第6章Excel综合应用6.4奖学金统计1.任务分析

（5）用图表展示每个班的各等级奖学金人数，如图所示。辅导员李老师现在要统计奖学金获奖情况，包括：2.实现过程

1）数据准备在相关素材中，“奖学金名单”工作表存放的是所有获得奖学金的名单，“奖学金标准”工作表存放的是各等级奖学金对应的奖学金金额。因此，新建“奖学金统计.xlsx”工作簿，将上述2张工作表复制到该工作簿中，并将“奖学金名单”工作表重命名为“奖学金发放统计”。要填写每个学生的奖学金数额，需要根据每个人的获奖等级，查找到对应的奖学金标准，因此可以用VLOOKUP函数来实现。操作步骤如下：

2）计算学生应发奖学金①选择“奖学金发放统计”工作表的E3单元格，单击“公式”选项卡/“函数库”组/“查找与引用”的VLOOKUP函数。②在“函数参数”对话框中，选择D3作为Lookup_value参数值，选择“奖学金标准!$A$2:$B$4”作为Table_array参数值，Col_index_num设置为2，Range_lookup是指为0。对应的公式为“=VLOOKUP(D3,奖学金标准!$A$2:$B$4,2,0)”。③选择E3单元格，移动鼠标，当鼠标变成黑色填充柄时，双击鼠标左键，完成函数及公式的复制。2.实现过程要从奖学金学生名单的班级信息（B2:B33单元格区域）而得到每个获奖班级的班级名称，实际上可以认为是从有重复数据的序列中去掉重复数据，可以使用数据筛选的高级筛选功能来实现。操作步骤如下：

3）填充班级名称①选择B2:B33单元格区域。②单击“数据”选项卡/“排序和筛选”组/“高级”按钮，弹出如图所示的“高级筛选”对话框。2.实现过程要从奖学金学生名单的班级信息（B2:B33单元格区域）而得到每个获奖班级的班级名称，实际上可以认为是从有重复数据的序列中去掉重复数据，可以使用数据筛选的高级筛选功能来实现。操作步骤如下：

3）填充班级名称③在“高级筛选”对话框中，选择方式为“将筛选结果复制到其他位置”，复制到$G$3，并勾选“选择不重复的记录”。这样，就从B2:B33单元格区域筛选出了获奖学生的对应班级名称。④选择G3:G8单元格区域，将其复制到G12:G17单元格区域。2.实现过程为H3单元格添加“按班级统计奖学金总额”批注，为G12单元格添加“按班统计各等级奖学金获奖人数”批注。操作步骤如下：

4）添加批注①如图所示，选择H3单元格，单击“审阅”选项卡/“批注”组/“新建批注”按钮，在对应的批注框中输入“按班级统计奖学金总额”。此时，“新建批注”按钮变成“编辑批注”。2.实现过程为H3单元格添加“按班级统计奖学金总额”批注，为G12单元格添加“按班统计各等级奖学金获奖人数”批注。操作步骤如下：

4）添加批注②选择“按班级统计奖学金总额”文本，单击鼠标右键，在弹出的快捷菜单中选择“设置批注格式”将其设置为红色，加粗显示。③调整批注栏到合适大小。④单击“审阅”选项卡/“批注”组/“显示所有批注”按钮，将批注显示在屏幕上。⑤为G12单元格添加“按班统计各等级奖学金获奖人数”批注。2.实现过程要统计每个班的奖学金总额，实际上是对获奖名单按“班级”来对“应发奖学金”进行求和。例如，要统计“13电子1”的奖学金总额，就是要将获奖名单中属于“13电子1”的数据过滤出来，然后将过滤出来的“应发奖学金”相加即可。上述要求可以使用SUMIF函数。操作步骤如下：

5）统计每个班的奖学金总额2.实现过程①选择H4单元格，单击单元格编辑栏前的插入函数按钮，通过查找，找到并插入SUMIF函数，弹出如图所示的“函数参数”对话框。

5）统计每个班的奖学金总额2.实现过程②选择B3:B33单元格区域作为Range参数值，G4单元格作为Criteria参数值，E3:E33单元格区域作为Sum_range参数。其含义是：将B3:B33单元格区域中等于G4单元格内容的行过滤出来，将过滤得到的行所对应的E列单元格区域中的值相加作为结果。由于在公式复制时，B3:B33单元格区域和E3:E33单元格区域都是不应该变化的，因此将其转化为绝对引用。H4单元格的最终公式为“=SUMIF($B$3:$B$33,G4,$E$3:$E$33)”。③选择H4单元格，移动鼠标，当鼠标变成黑色填充柄时，双击鼠标左键，完成函数及公式的复制。要统计每个班奖学金各等级的人数，实际上是对获奖名单按“班级”及“奖学金资格”来汇总对应的单元格个数。例如，要统计“13电子1”获得“一等奖”的人数，就是要将获奖名单中属于“13电子1”的数据过滤出来，然后在过滤出来的数据中再用“一等奖”进行过滤，最后统计过滤出来的“一等奖”单元格数。上述要求可以使用COUNTIFS函数。操作步骤如下：

6）统计每个班奖学金各等级的人数2.实现过程①选择H13单元格，单击“公式”选项卡/“函数库”组/“其他函数”按钮的“统计”下面的COUNTIFS函数，弹出如图所示的“函数参数”对话框。

6）统计每个班奖学金各等级的人数2.实现过程②选择$B$3:$B$33单元格区域作为Criteria_range1参数值，选择G13作为Criteria1参数值，选择$D$3:$D$33单元格区域作为Criteria_range2参数值，输入“一等奖”作为Criteria2参数值，其含义是：将$B$3:$B$33单元格区域中等于G13单元格内容的数据过滤出来，将过滤得到数据中$D$3:$D$33单元格区域中等于“一等奖”的数据再过滤出来，最后统计过滤出来的$D$3:$D$33单元格区域中的单元格数。H13单元格的最终公式为“=COUNTIFS($B$3:$B$33,G13,$D$3:$D$33,"一等奖")”。

6）统计每个班奖学金各等级的人数2.实现过程③选择H13单元格，移动鼠标，当鼠标变成黑色填充柄时，双击鼠标左键，完成函数及公式的复制。④I13单元格的最终公式为“=COUNTIFS($B$3:$B$33,G13,$D$3:$D$33,"二等奖")”。⑤选择I13单元格，移动鼠标，当鼠标变成黑色填充柄时，双击鼠标左键，完成函数及公式的复制。⑥J13单元格的最终公式为“=COUNTIFS($B$3:$B$33,G13,$D$3:$D$33,"三等奖")”。⑦选择J13单元格，移动鼠标，当鼠标变成黑色填充柄时，双击鼠标左键，完成函数及公式的复制。要制作如图所示的各班级奖学金获奖情况对比图，可以用“三维柱形图”来实现。操作步骤如下：

7）制作各班级奖学金获奖情况对比图2.实现过程①选择G12:J17单元格区域的任一单元格。②单击“插入”选项卡/“图表”组/“柱形图”按钮，在弹出的菜单中选择“三维柱形图”的“三维簇状柱形图”。③单击“图表工具/设计”选项卡/“图表布局”组/“布局5”按钮。④将图表标题改为“2013级电子信息工程专业奖学金统计图”。要制作如图所示的各班级奖学金获奖情况对比图，可以用“三维柱形图”来实现。操作步骤如下：

7）制作各班级奖学金获奖情况对比图2.实现过程⑤将纵坐标轴标题改为“获奖人数”。⑥单击“图表工具/布局”选项卡/“坐标轴”组/“数据