大学计算机基础-基于计算思维课件 第6章 Excel综合应用6.4 奖学金统计

大学计算机基础——基于计算思维（Windows10+Office2016）6.4.1知识点解析第6章Excel综合应用6.4奖学金统计1.批注

通过批注，可以对单元格的内容添加注释或者说明。如图所示，通过对“小计”添加批注，可以清晰知道小计金额的由来。由于批注类似于Word中的文本框，因此，可以通过设置批注的填充效果为图片，而让“活页夹”添加图片批注。2.SUMIF函数

（1）确定要过滤的单元格区域。（2）将“北京”地区的所有数据过滤出来。（3）在过滤出来的数据中，找到对应的游戏积分区域。（4）对找到的对应游戏积分区域求和。如图所示，要统计“北京”地区的游戏总积分，需要：SUMIF(Range,Criteria,Sum_range)2.SUMIF函数

而要实现上述功能的自动计算，可以采用SUMIF函数来完成。如图所示，要统计“北京”地区的游戏总积分，需要：SUMIF函数的语法格式为其中，Range为要过滤的单元格区域（即第1步中确定的单元格区域），Criteria为指定的条件（即第2步中给定的条件），Sum_range表示需要对哪些单元格求和（即第3步中找到的单元格区域）。SUMIF函数将得到符合条件的和（即第4步中的对应单元格区域的求和）。对应的公式为“=SUMIF(B3:B10,"北京",C3:C10)”。其含义是：将B3:B10单元格区域中为“北京”的数据过滤出来，在过滤出来的数据中求对应的C3:C10单元格数值的和。3.COUNTIFS函数的条件区域

如图所示，要统计“北京”地区游戏积分在17000分以上（含）的人数，需要：（1）将“北京”地区的所有数据过滤出来。（2）在过滤出来的数据中，对积分>=17000的数据进行再次过滤。（3）统计最后过滤出来的积分>=17000的单元格数。

因此，可以使用COUNTIFS函数来实现。其对应的参数是：（1）Criteria_range1参数值为B3:B10单元格区域。（2）Criteria1参数值为“北京”。（3）Criteria_range2参数值为C3:C10单元格区域。（4）Criteria2参数值为“>=17000”。3.COUNTIFS函数的条件区域公式为“=COUNTIFS(B3:B10,"北京",C3:C10,">=17000")”。其含义是：将B3:B10单元格区域中为“北京”的数据过滤出来，在过滤出来的数据中，再将C3:C10单元格区域中>=170000的数据过滤出来，在得到的数据区域中统计C3:C10单元格区域的单元格个数。大学计算机基础——基于计算思维（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.实现过程⑤将纵坐标轴标题改为“获奖人数”。⑥单击“图表工具/布局”选项卡/“坐标轴”组/“数据标签”按钮，设置为“显示”。⑦单击“图表工具/布局”选项卡/“坐标轴”组/“网格线”按钮，将“主要横网格线”设置为“无”。⑧单击“图表工具/设计”选项卡/“位置”组/“移动图表”按钮，将其移到“奖学金统计图”的新工作表中。大学计算机基础——基于计算思维（Windows10+Office2016）6.4.3总结与提高第6章Excel综合应用6.4奖学金统计1.SUMIFS函数

（1）将“北京”地区的所有数据过滤出来。（2）在过滤出来的数据中，对积分>=17000的数据进行再次过滤。（3）统计最后过滤出来的积分>=17000的和。如图所示，要统计“北京”地区游戏积分在17000分以上（含）的总积分，需要：1.SUMIFS函数

SUMIFS(Sum_range,Criteria_range1,Criteria1,[Criteria_range2,Criteria2],…)可以使用SUMIFS函数来实现。SUMIF函数用来对区域中满足多个条件的单元格求和。SUMIF函数的语法格式为参数的含义是：Criteria_range1和Criteria1：用Criteria1条件对criteria_range1区域进行过滤。Criteria_range2和Criteria2：用Criteria2条件对criteria_range2区域进行过滤。依次类推。Sum_range：要求和的单元格区域。1.SUMIFS函数

因此，统计“北京”地区游戏积分在17000分以上（含）的总积分的公式为“=SUMIFS(C3:C10,B3:B10,"北京",C3:C10,">=17000")”。需要注意的是，SUMIFS和SUMIF函数的参数顺序有所不同。Sum_range参数在SUMIFS中是第一个参数，而在SUMIF中则是第三个参数。2.双轴线图表制作

如图所示，要制作国内生产总值及增速比较图，由于数据差异大，采用统一的坐标轴来展现是不合适的。因此可以通过调整分别设置国内生产总值的坐标轴和增速的坐标轴，即用双轴线图表来实现数据差异过大的图表展现。2.双轴线图表制作

①选择数据区域，插入二维簇状柱形图。②将其布局方式设置为“布局4”。③选中图表中代表增速的数据图形，单击鼠标右键，在弹出的快捷菜单中选择“设置数据系列格式”，将其设置为次坐标，如图所示。操作步骤如下：2.双轴线图表制作

④双击图表右边的百分比坐标轴数字标签，将其最大值设置为0.3，主要刻度线类型为“内部”，如图所示。操作步骤如下：2.双轴线图表制作

⑤选中图表中代表增速的数据图形，单击鼠标右键，在弹出的快捷菜单中选择“更改图标类型”，将其设置为折线图，如图所示。操作步骤如下：2.双轴线图表制作

⑥选中图表中代表增速的数据图形，单击鼠标右键，在弹出的快捷菜单中选择“设置数据系列格式”，将其“数据标记选项”的数据标记类型设为“内置”，大小为7，如图所示。⑦选中图表中代表国内生产总值的数据图形，通过“图表工具—格式”选项卡，把柱状图填充的颜色去掉，换成框线。操作步骤如下：3.查找与替换高级应用

Excel2010的“查找与替换”功能与Word2010类似，除了可以在工作表中查找数据外，也可以进行特殊格式的查找和替换。通过“开始”选项卡/“编辑”组/“查找和选择”按钮，可以对选定的单元格区域、工作表或整个工作簿，查找出使用了公式、批注、条件格式或数据验证等特殊格式的单元格，也可以查找和替换含公式的自定义高级格式。3.查找与替换高级应用

如左图所示，由于部分员工的奖金是用美元计算（货币格式为“-US$1,234.10”），因此