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

大学计算机基础——基于计算思维（Windows10+Office2016）6.3.2任务实现第6章Excel综合应用6.3奖学金评定（1）确定其级别身份为“主要干部”、“一般干部”还是“无”。如图所示。（2）根据奖学金条件限制的单科课程绩点要求，分别统计单科课程绩点>=2.5的门次，单科课程绩点∈[2.4,2.5)的门次以及单科课程绩点∈[2.2,2.5)的门次。1.任务分析

要计算某学生是否获得奖学金，需要：

（3）根据级别身份，计算每个学生达到奖学金要求的课程门次，如表所示。1.任务分析要计算某学生是否获得奖学金，需要：表6-1达到奖学金要求的课程门次级别身份达到奖学金要求的课程门次主要干部单科课程绩点>=2.5的门次+最多2门单科课程绩点∈[2.2,2.5)一般干部单科课程绩点>=2.5的门次+最多2门单科课程绩点∈[2.4,2.5)无单科课程绩点>=2.5的门次

（4）奖学金资格判定：如果达到奖学金要求的课程门次=修读课程门次，则表明其有评选奖学金资格，否则就没有评选奖学金资格。（5）筛选出有奖学金资格的名单，并对名单按平均课程绩点排序，当平均课程绩点相同时，按德育绩点排序。（6）计算奖学金名额。一等奖学金名额不超过专业总人数2％（含，下同），二等奖学金名额不超过专业总人数5％，三等奖学金名额不超过专业总人数8％，人数出现小数的，采用去尾法计算。（7）按给定名额数量，对符合奖学金的学生进行评定。（8）确定最终奖学金名单，并给出按姓名查询详细情况。1.任务分析

在相关素材中，“职务级别”工作表存放的是如右上图所示的职务与级别的对应关系，“任职一览表”工作表存放的是如左上图所示的所有担任学生干部的名单，“奖学金计算”工作表存放的是如右下图所示的筛选出来的可能符合奖学金评选条件的名单。因此，新建“奖学金评定.xlsx”工作簿，将上述3张工作表复制到该工作簿中。2.实现过程1）数据准备根据“职务级别”工作表的对应关系，将“任职一览表”工作表中学生的担任职务转换为对应的职务级别。要得到“陈俊晔”同学的职务级别（D3），就需要根据他的“担任职务”（C3单元格），在“职务级别”工作表中的!$A$2:$B$18单元格区域去查找。操作步骤如下：

2.实现过程2）确定担任职务的对应级别①选择“任职一览表”工作表的D3单元格，单击“公式”选项卡/“函数库”组/“查找与引用”按钮的VLOOKUP函数，弹出如图所示的“函数参数”对话框。

2.实现过程2）确定担任职务的对应级别②在“函数参数”对话框中，选择C3作为Lookup_value参数值，选择职务级别!$A$2:$B$18作为Table_array参数值，Col_index_num设置为2，Range_lookup是指为0。对应的公式为“=VLOOKUP(C3,职务级别!$A$2:$B$18,2,0)”，其含义是：根据当前C3单元格的值，去单元格区域!$A$2:$B$18的第一列查找，如果找到匹配项，则将单元格区域!$A$2:$B$18的对应匹配项所在行的第二列的值填充到当前位置。③选择D3单元格，移动鼠标，当鼠标变成黑色填充柄时，双击鼠标左键，完成函数及公式的复制。可以通过在“奖学金计算”工作表运用VLOOKUP函数，将“任职一览表”中的相应数据查找出来。操作步骤如下：

2.实现过程3）确定奖学金名单的级别身份①如图所示，选择“任职一览表”工作表的A3:D57单元格区域，单击“公式”选项卡/“定义的名称”组的“定义名称”按钮，在弹出的“新建名称”对话框中，将其命名为“职务信息”。这样，需要引用“任职一览表”工作表A3:D57单元格区域的地方都可以用“职务信息”名称来替代。

2.实现过程3）确定奖学金名单的级别身份③如图所示，将鼠标定位在Table_array参数区域，单击“公式”选项卡/“定义的名称”组/“用于公式”的“职务信息”，将名称“职务信息”作为Table_array参数值。对应的公式为“=VLOOKUP(A4,职务信息,4,0)”，结果显示为“#N/A”。②选择“奖学金计算”工作表的C4单元格，单击“公式”选项卡/“函数库”组/“查找与引用”的VLOOKUP函数，选择A4作为Lookup_value参数值，Col_index_num设置为4，Range_lookup是指为0。

2.实现过程3）确定奖学金名单的级别身份⑤选择C4单元格，单击“公式”选项卡/“函数库”组/“逻辑”的IFERROR函数。在如图所示的对话框中，Value值为剪切的内容（“VLOOKUP(A4,职务信息,4,0)”），Value_if_error值为“无”。对应的公式为“=IFERROR(VLOOKUP(A4,职务信息,4,0),"无")”，结果显示为“无”。⑥选择C4单元格，移动鼠标，当鼠标变成黑色填充柄时，双击鼠标左键，完成函数及公式的复制。④将C4单元格编辑栏区域中“=”之后的内容剪切下来。根据奖学金条件限制的单科课程绩点要求，分别统计单科课程绩点>=2.5的门次，单科课程绩点∈[2.4,2.5)的门次以及单科课程绩点∈[2.2,2.5)的门次，可以采用COUNTIF及COUNTIFS函数来实现。操作步骤如下：

2.实现过程4）统计单科课程绩点范围①K4单元格的公式为“=COUNTIF(E4:I4,">=2.5")”。②L4单元格的公式为“=COUNTIFS(E4:I4,"<2.5",E4:I4,">=2.4")”。③M4单元格的公式为“=COUNTIFS(E4:I4,"<2.5",E4:I4,">=2.2")”。④分别选择K4、L4及M4单元格，移动鼠标，当鼠标变成黑色填充柄时，双击鼠标左键，完成函数及公式的复制。根据级别身份，计算每个学生达到奖学金要求的课程门次。由于“主要干部”最多可以有2门单科课程绩点∈[2.2,2.5)，“一般干部”最多可以有2门单科课程绩点∈[2.4,2.5)，因此可以用IF函数来对不同级别身份身份进行判断，而MIN函数则可以求出最多可以有2门单科课程绩点∈[2.2,2.5)和最多可以有2门单科课程绩点∈[2.4,2.5)，其逻辑表达如表所示。操作步骤如下：

2.实现过程5）符合奖学金课程门次计算表6-2达到奖学金要求的课程门次计算逻辑级别身份达到奖学金要求的课程门次主要干部单科课程绩点>=2.5的门次+MIN(单科课程绩点∈[2.2,2.5)的门次,2)一般干部单科课程绩点>=2.5的门次+MIN(单科课程绩点∈[2.4,2.5)的门次,2)无单科课程绩点>=2.5的门次

2.实现过程5）符合奖学金课程门次计算①选择N4单元格，插入IF函数，IF函数的Logical_test的值为“C4="主要干部"”，鼠标定位在Value_if_true区域，选择K4单元格，然后输入“+”，在名称框选择需要嵌入的MIN函数，如图所示。

2.实现过程5）符合奖学金课程门次计算②在如图所示的MIN函数的对话框中，Number1区域选择M4单元格，Number2的值输入2。

2.实现过程5）符合奖学金课程门次计算③单击确定后，鼠标定位在N4单元格编辑区域的IF中间位置，单击编辑栏前的“插入函数”图标，弹出IF函数对话框。在Value_if_false的输入框中，在名称框选择需要嵌入的IF函数，IF函数的参数分别为：Logjcal_test的值为“C4="一般干部"”，Value_if_true的值为“K4+MIN(L4,2)”，Value_if_false的值为“K4”。N4单元格的最后公式为“=IF(C4="主要干部",K4+MIN(M4,2),IF(C4="一般干部",K4+MIN(L4,2),K4))”，结果显示为5。④选择N4单元格，移动鼠标，当鼠标变成黑色填充柄时，双击鼠标左键，完成函数及公式的复制。如果达到奖学金的课程门次与本学期所学的课程门次相等，就表示该学生具备奖学金资格。因此O4单元格可以根据N4单元格的值是否与课程总门次（B2）是否相等，来填写是否具备奖学金资格。需要注意的是，由于课程总门次（B2）不随公式的位置变化而发生变化，因此，需要使用绝对地址。操作步骤如下：

2.实现过程6）奖学金资格判定①选择O4单元格，选择IF函数并填写相应参数。O4的公式为“=IF(N4=$B$2,"是","否")”，结果显示为“是”。②选择O4单元格，移动鼠标，当鼠标变成黑色填充柄时，双击鼠标左键，完成函数及公式的复制。将有奖学金资格的学生名单筛选出来，并对名单按平均课程绩点排序，当平均课程绩点相同时，按德育绩点排序。操作步骤如下：

2.实现过程7）筛选和排序符合奖学金名单①选择A3:P92单元格区域，单击“开始”选项卡/“编辑”组/“排序和筛选”按钮的“筛选”。②单击O3单元格的筛选下三角，在弹出的菜单中只勾选“是”。状态栏显示“在89条记录中找到69个”，表明有69个学生具备奖学金资格。③鼠标定位在“平均课程绩点”列的任一单元格，单击“开始”选项卡/“编辑”组/“排序和筛选”按钮的“降序”，J3单元格旁就出现了，表明数据就按照平均课程绩点的大小进行了降序排序。④鼠标定位在当前工作表的任一位置，单击“开始”选项卡/“编辑”组/“排序和筛选”按钮的“自定义排序”，在弹出的“排序”对话框中添加如图所示的条件。

2.实现过程7）筛选和排序符合奖学金名单根据奖学金评定规则，首先要确定每个等级的获奖人数：一等奖学金名额不超过专业总人数2％（含，下同），二等奖学金名额不超过专业总人数5％，三等奖学金名额不超过专业总人数8％，人数出现小数的，采用去尾法计算。操作步骤如下：

2.实现过程8）奖学金名额确定①将相关素材的“奖学金名额”工作表复制到“奖学金评定.xlsx”工作簿中。②计算每个等级的获奖人数，由于人数出现小数的，要采用去尾法计算，因此，可以使用ROUNDDOWM函数来实现。D2单元格的公式为“=ROUNDDOWN($B$1*B2,0)”，结果为4。③选择D2单元格，移动鼠标，当鼠标变成黑色填充柄时，双击鼠标左键，完成函数及公式的复制。计算得到一等奖4名，二等奖10名，三等奖16名。要根据奖学金名额来评定奖学金，还需要考虑如下规则：当平均课程绩点和德育绩点相同导致奖学金等级不同时，奖学金等级按最高等级计算，下一等级奖学金名额相应减少。如果平均课程绩点和德育绩点相同导致最后一个等级名额不够时，则自动扩充奖学金。操作步骤如下：

2.实现过程9）奖学金评定①单击“奖学金计算”工作表的J3单元格的筛选按钮，在弹出的菜单中选择“数字筛选”的“10个最大的值”，如图所示。②在弹出的“自动筛选前10个”对话框中选择显示4项，如图所示。

2.实现过程9）奖学金评定③状态栏显示“在89个记录中找到4个”，将其奖学金区域填充为“一等奖”。④单击J3单元格的筛选按钮，筛选出前14项（一、二等奖的人数），将其奖学金区域为空的10个单元格填写为“二等奖”。

2.实现过程9）奖学金评定⑤单击J3单元格的筛选按钮，筛选出前30项（一、二、三等奖的人数），状态栏显示“在89个记录中找到31个”。观察最后的数据，发现第30和第31个数据的平均课程绩点和德育绩点相同，因此符合三等奖的学生有17名，将其奖学金区域为空的17个单元格填写为“三等奖”。⑥选择整个工作表，将其内容复制，粘贴到一个新的工作表，命名为“奖学金最终名单”。需要按奖学金最终名单数据，按“姓名”查询每个学生的详细情况。由于查询的姓名是奖学金最终名单的学生，因此可以用数据有效性来进行姓名限制。而要查询到其他的具体情况，可以根据姓名去查询奖学金