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

大学计算机基础——基于计算思维（Windows10+Office2016）6.3.1知识点解析第6章Excel综合应用6.3奖学金评定1.VLOOKUP函数

如图所示，要填写“录取名录表”中每个学生的录取专业名称及辅导员，就需要根据每个学生的录取专业代码去查找“专业代码对应表”中的数据。例如，要填写“陈萍萍”的录取专业名称及辅导员，就需要根据“陈萍萍”的专业代码“1301”，在“专业代码对应表”中找到专业代码“1301”所在的行，然后将“1301”所在行对应的录取专业名称及辅导员，将其填写到对应位置。1.VLOOKUP函数

因此，实现上述填充查找的关键是：（1）明确要填充的结果（录取专业名称，辅导员）。（2）要填充的结果所依据的查找元素（录取专业代码）。（3）被查找的元素在哪里（专业代码对应表），称之为查找区域。（4）在查找区域的什么位置查找（专业代码列）。（5）找到被查找的元素后，哪些结果作为填充值（对应专业代码行所在的专业名称及专职辅导员）。Excel提供了VLOOKUP函数来实现上述功能。VLOOKUP函数可以在查找区域的第一列中查找指定的值，然后返回与该值同行的其他列的数据。

VLOOKUP函数的语法格式为VLOOKUP(Lookup_value,Table_arrary,Col_index_num,Range_lookup)，参数的含义分别是：1.VLOOKUP函数（1）Lookup_value：表示要填充的结果所依据的查找元素。（2）Table_arrary：被查找的元素所在的查找区域，并要求在查找区域的第1列去查找。（3）Col_index_num：找到被查找的匹配项后，被查找到的结果位于查找区域的第几列。（4）Range_lookup：查找时是大致匹配查找（1或ture）还是精确匹配查找（0或false）。VLOOKUP的返回结果为要填充的元素。

根据VLOOKUP函数的语法格式，要填写“陈萍萍”的录取专业名称，对应的VLOOKUP参数值是：1.VLOOKUP函数（1）Lookup_value为B15，即根据“陈萍萍”的录取专业代码值去查找。（2）Table_arrary为A3:B8，A3:B8为查找区域，要确保所依据的查找元素（录取专业代码）对应的专业代码在查找区域的第1列。（3）Col_index_num为2，表示找到对应的匹配项后，返回A3:B8区域中找到的行数据中的第2列，即对应的专业名称值作为函数的结果。（4）Range_lookup为0，表示精确查找。

1.VLOOKUP函数因此，C15单元格对应的公式为“=VLOOKUP(B15,A3:B8,2,0)”。由于公式复制时，查找区域A3:B8是不发生改变的，因此C15单元格对应的最终公式为“=VLOOKUP(B15,$A$3:$B$8,2,0)”。而要填写“陈萍萍”的辅导员，D15单元格对应的公式为“=VLOOKUP(B15,$A$3:$C$8,3,0)”。也可将单元格区域A3:C8定义为“专业代码区域”名称，这样，C15单元格格的公式可以写为“=VLOOKUP(B15,专业代码区域,2,0)”，而D15单元格对应的公式为“=VLOOKUP(B15,专业代码区域,3,0)”。

2.IFERROR函数如图所示，由于“刘嘉楠”的录取专业代码在“专业代码对应表”（A3:B7）中找不到，因此填充结果为#N/A，表示公式执行过程中出现了“值不可用”的错误。

5.数据透视表可以使用IFERROR函数来捕获和处理公式中的错误。IFERROR函数的语法格式为IFERROR(value,value_if_error)，其参数的含义是：（1）Value：为可能出错的公式。如果公式不出错，就将Value公式的结果作为返回值。（2）Value_if_error：公式计算结果错误时返回的值。如上例所示，可以将C15单元格的公式修改为“=IFERROR(VLOOKUP(B15,$A$3:$B$7,2,0),"无此专业代码")”来解决专业代码不存在的情况。这样，在进行公式复制后，“刘嘉楠”的录取专业名称显示为“无此专业代码”。大学计算机基础——基于计算思维（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个单元格填写为“三等奖”。⑥选择整个工作表，将其内容复制，粘贴到一个新的工作表，命名为“奖学金最终名单”。需要按奖学金最终名单数据，按“姓名”查询每个学生的详细情况。由于查询的姓名是奖学金最终名单的学生，因此可以用数据有效性来进行姓名限制。而要查询到其他的具体情况，可以根据姓名去查询奖学金最终名单中对应的数据，可以使用VLOOKUP来实现。操作步骤如下：

2.实现过程10）奖学金名单查询①将相关素材中的“奖学金名单查询”工作表复制到“奖学金评定.xlsx”工作簿中。②选择“奖学金最终名单”工作表的A4:P34单元格区域，将其名称定义为“奖学金名单”。③选择“奖学金名单查询”的B2单元格，将其数据有效性的条件设置为允许“序列”，来源于“=奖学金最终名单!$A$4:$A$34”。④选择B3单元格，如图所示，定义其公式为“=VLOOKUP($B$2,奖学金名单,2,0)”，表示根据B2单元格值，去查找“奖学金名单”名称区域，找到后将其第2列数据返回。需要注意的是，由于B3:B12单元格都需要根据姓名（B2单元格的值）去查找，因此将B2定义为绝对地址$B$2。

2.实现过程10）奖学金名单查询⑤选择B3单元格，动鼠标，当鼠标变成黑色填充柄时，双击鼠标左键，完成函数及公式的复制。⑥选择B4单元格，修改公式中的参数，返回数据为第3列，对应公式为“=VLOOKUP($B$2,奖学金名单,3,0)”。⑦按照对应关系，分别选择B5:B12单元格，修改公式中的参数。大学计算机基础——基于计算思维（Windows10+Office2016）6.3.3总结与提高第6章Excel综合应用6.3奖学金评定