函数高级应用实验项目

【实验目的】

➢掌握复杂函数的应用。

➢掌握公式与函数的综合应用。

➢掌握Excel函数的嵌套使用。

【实验技术要点】

1.复杂函数的应用

（1）逻辑判断函数——IF函数。

功能：对指定的条件进行判断，若条件为TRUE，函数将返回一个值；如果条件为FALSE，则返回另一个值。

语法：IF（Logical_test，［Value_if_true］，［Value_if_false］）

Logical_test：表示计算结果为TRUE或FALSE的任意值或表达式。

Value_if_true：表示Logical_test为TRUE时返回的值。

Value_if_false：表示Logical_test为FALSE时返回的值。

（2）单条件求和函数——SUMIF函数。

功能：对区域中符合指定的一个条件的单元格求和。

语法：SUMIF（Range，Criteria，Sum_range）

Range：条件判断的单元格区域。

Criteria：求和条件。

Sum_range：需要求和的单元格区域或者引用。

（3）多条件求和函数——SUMIFS函数。

功能：对区域中符合指定的多个条件的单元格求和。

语法：SUMIFS（Sum_range，Criteria_range1，Criteria1，Criteria_range2，Criteria2…）

Sum_range：需要求和的实际单元格，包括数字或包含数字的名称、区域或单元格引用。

Criteria_range1：计算关联条件的第一个区域。

Criteria1：条件1，表示要作为条件进行判断的第一个单元格区域。条件的形式为数字、表达式、单元格引用或者文本，可用来定义将对Criteria_range1参数中的哪些单元格求和。

Criteria_range2：计算关联条件的第二个区域。

Criteria2：条件2，表示要作为条件进行判断的第二个单元格区域。条件的形式为数字、表达式、单元格引用或者文本，可用来定义将对Criteria_range2参数中的哪些单元格求和。

Criteria和Criteria_range均成对出现。最多允许127个区域、条件对，即参数总数不超过255个。

（4）逻辑与函数——AND函数。

功能：所有参数的计算结果为TRUE时，返回TRUE；只有一个参数的计算结果为FALSE，即返回FALSE。

语法：AND（Logical1，［Logical2］，…）

AND函数用作IF函数的Logical_test参数，可以检验多个不同的条件。

（5）纵向查找函数——VLOOKUP函数。

语法：VLOOKUP（Lookup_value，Table_array，Col_index_num，Range_lookup）

功能：在表格或数值数组的首列查找指定的数值，最终返回该列所需查询序列所对应的值。

Lookup_value：需要在数据表第一列中进行查找的数值。Lookup_value可以为数值、引用或文本字符串。当VLOOKUP函数第一参数省略查找值时，表示用0查找。

Table_array：需要在其中查找数据的数据表。使用对区域或区域名称的引用。

Col_index_num：Table_array中查找数据的数据列序号。

Range_lookup：逻辑值，指明函数VLOOKUP查找时是精确匹配，还是近似匹配。

（6）横向查找函数——HLOOKUP函数。

语法：HLOOKUP（Lookup_value，Table_array，Row_index_num，Range_lookup）

功能：在表格或数值数组的首行查找指定的数值，并返回表格或数组中与指定值同列的其他行的数值。

Lookup_value：需要在数据表第一行中进行查找的数值。Lookup_value可以为数值、引用或文本字符串。当HLOOKUP函数第一参数省略查找值时，表示用0查找。

Table_array：需要在其中查找数据的数据表。使用对区域或区域名称的引用。

Row_index_num：Table_array中查找数据的数据行序号。

Range_lookup：逻辑值，指明函数HLOOKUP查找时是精确匹配，还是近似匹配。

2.Excel函数的嵌套使用

在Excel中，有时一个函数不能实现所需功能，需要多个函数配合使用，就要用到函数的嵌套。使用时要注意如下两点：

（1）有效返回值。当将嵌套函数作为参数使用时，该嵌套函数返回的值的类型必须与参数使用的值的类型相同。例如，如果参数返回一个TRUE或FALSE值，那么嵌套函数也必须返回一个TRUE或FALSE值。否则，Excel会显示错误值。

（2）嵌套层数。一个公式可以包含多达64层的嵌套函数。如果将一个函数（我们称此函数为B）用作另一个函数（我们称此函数为A）的参数，则函数B相当于第二级函数。

【实验内容】

一、任务描述

（1）把本节实验项目1的工作表“成绩统计表”复制到工作簿“成绩管理”的“Sheet3”中，按图1.119所示内容对表格进行编辑操作，编辑操作完成后保存文件并命名为“成绩统计表-计算2.xlsx”，同时在该工作表中创建“查询结果表”。

（2）在同一工作簿中新建工作表“2018年竣工工程一览表”，如图1.120所示。

（3）利用函数对“成绩统计表”中的总分、平均成绩、成绩五级制（总分）、评选结果进行计算。评选结果计算规则：成绩五级制（总分）为“优秀”时显示“三好”，否则单元格不显示任何内容。

（4）用VLOOKUP函数查找指定学生的总分。

（5）利用函数对“2018年竣工工程一览表”中“数据统计”区域中的建筑面积和工程造价进行计算。

图1.119　成绩统计表和查询结果表

图1.120　工程一览表

（6）对表格进行相应的格式化设置。

二、任务目标

实验任务完成后，最后的效果如图1.121和图1.122所示。

三、任务实施

（1）对本节实验项目1的工作表“成绩统计表”进行相应的编辑操作。

在本节实验项目1中工作表“成绩统计表”的基础上，按图1.119所示内容对表格进行编辑操作，编辑操作完成后保存文件并命名为“成绩统计表-计算2.xlsx”，同时在该工作表中创建“查询结果表”。

（2）以“成绩统计表-计算2.xlsx”为基础，做如下计算统计操作。

①计算总分和平均成绩。

图1.121　成绩统计表和查询结果表效果示意图

图1.122　工程一览表效果示意图

利用SUM函数和AVERAGE函数计算总分和平均成绩。

②计算成绩五级制（总分）。

方法一：利用IF函数的嵌套计算成绩的五级制（总分）。

设置K3=IF（I3＞=450，"优秀"，IF（I3＞=400，"良好"，IF（I3＞=350，"中等"，IF（I3＞=300，"及格"，"不及格"）））），这里用到了IF函数的嵌套，详细函数参数如图1.123所示。

本例IF函数嵌套解析：公式先对第一个条件进行判断，I3＞=450，如果大于或等于450则显示第一个逗号后面的值“优秀”，如果不大于450则进行下一个IF函数的判断，I3＞=400，如果大于或等于400则显示“良好”，如果不大于400，则依次和后面的条件匹配判断下去，直到满足某个条件，返回相应的值为止。

方法二：利用IF函数和AND函数的嵌套来计算成绩五级制（总分）。

设置K3=IF（I3＞=450，"优秀"，IF（AND（I3＞=400，I3＜450），"良好"，IF（AND（I3＞=350，I3＜400），"中等"，IF（AND（I3＞=300，I3＜350），"及格"，"不及格"））））。

图1.123　IF函数参数界面一

本例AND函数解析：本例中IF函数里面嵌套的AND函数用于检查每个分数段的上限和下限的条件，当上限和下限条件都满足时，才返回TRUE；只要上限条件或下限条件中有一个的计算结果为FALSE，即返回FALSE。

③计算评选结果。

利用IF函数的一般用法来计算评选结果。(www.chuimin.cn)

设置L3=IF（K3="优秀"，"三好"，""），详细函数参数如图1.124所示。

图1.124　IF函数参数界面二

（3）用VLOOKUP函数纵向查找指定学生的总分。

在“查询结果表”里，设置B15=VLOOKUP（A15，成绩统计表！A2：I12，9，0），详细函数参数如图1.125所示。B15单元格数据计算出来后，利用自动填充的方法，将鼠标指针指向B15单元格右下角的填充柄处，当出现符号“+”时，拖动鼠标至B24单元格，B15：B24单元格会分别计算出指定学生的总分。

图1.125　VLOOKUP函数参数界面

说明：

本例中，四个参数分析如下：

①查找目标是“学号”；

②查找区域是“成绩统计表”中的A2到I12，注意查找目标必须位于查找区域的第一列；

③返回值（总分）的列数为9，即从左到右第9列；

④查找方式，选择精确查找，录入FALSE或者0（模糊查找：TRUE或者1）；

完成上述实验操作后，效果图如图1.121所示。

思考：请同学们查找资料进一步学习函数HLOOKUP（）的使用方法。

（4）计算“2018年竣工工程一览表”中的建筑面积和工程造价。

①求建筑面积总和：设置C15=SUM（B3：B11）。

②求工程造价总和：设置D15=SUM（C3：C11）。

③求住宅工程的建筑面积总和：设置C16=SUMIF（$A$3：$A$11，"住宅"，B3：B11）。

④求住宅工程的工程造价总和：设置D16=SUMIF（$A$3：$A$11，"住宅"，C3：C11）。

⑤求优良工程的建筑面积总和：设置C17=SUMIF（$D$3：$D$11，"优良"，B3：B11）。

⑥求优良工程的工程造价总和：设置D17=SUMIF（$D$3：$D$11，"优良"，C3：C11）。

⑦求同时满足“住宅、优良、面积大于2000平方米”三个条件的工程的建筑面积总和：设置C18=SUMIFS（B3：B11，$A$3：$A$11，"住宅"，$D$3：$D$11，"优良"，$B$3：$B$11，"＞2000"）。

⑧求同时满足“住宅、优良、面积大于2000平方米”三个条件的工程的工程造价总和：设置D18=SUMIFS（C3：C11，$A$3：$A$11，"住宅"，$D$3：$D$11，"优良"，$B$3：$B$11，"＞2000"）。

SUMIF函数的详细函数参数如图1.126和图1.127所示（以第③、⑤步为例）。

图1.126　SUMIF函数参数界面（第③步）

图1.127　SUMIF函数参数界面（第⑤步）

SUMIFS函数的详细函数参数如图1.128和图1.129所示（以第⑦步为例）。

完成上述实验操作后，效果如图1.122所示。

说明：

图1.128　SUMIFS函数参数界面（第⑦步）一

图1.129　SUMIFS函数参数界面（第⑦步）二

①在函数参数较多，一个界面显示不完时，请单击函数参数界面右边的小箭头上下滑动进行参数的输入（如图1.129是在图1.128界面上单击右边的滚动条向下滑动而出现的）。

②上述函数中对条件区域的单元格地址进行地址绝对化，是为了方便在后面的计算中利用自动填充功能来简化类似的计算过程，请同学们思考总结。

【实验练习】

（1）用Excel创建“期末成绩表”，内容如图1.130所示。按题目要求完成之后，用Excel的保存功能直接存盘。

要求：

①为表格绘制红色、双线型外边框，绿色、单线型内框，并为分数段统计表的科目和分数段表格绘制绿色斜线头。

②将表中的文字设置为宋体、蓝色、12磅、居中、加粗。

③用函数计算总分，并将计算结果填入对应的单元格中。

④用函数计算成绩五级制（总分），并将计算结果填入对应的单元格中。

说明：

总分成绩等级评定标准：总分＞=360，评定为“优秀”；320＜=总分＜360，评定为“良好”；280＜=总分＜320，评定为“中等”；240＜=总分＜280，评定为“及格”；总分＜240，评定为“不及格”。

⑤用函数统计各科目各分数段的人数，并将计算结果填入对应的单元格中。

（2）用Excel创建“销售明细表”，内容如图1.131所示。按题目要求完成之后，用Excel的保存功能存盘。

图1.130　期末成绩表

图1.131　销售明细表

要求：

①表格要有可视的边框，并将表中的内容均设置为宋体、12磅、居中；

②将表中的月标题单元格填充为灰色-25%，列标题填充为茶色，序号列填充为浅色；

③用函数计算4月、5月和6月的销售总量，填入相应的单元格中；

④用函数计算销售明细表中所有三星手机的销售总量；

⑤用函数计算销售明细表中所有工号为B开头的员工的销售总量。

3.用Excel创建“招聘考试情况统计表”，内容如图1.132所示。按题目要求完成之后，用Excel的保存功能直接存盘。

要求：

（1）表格要有可视的边框，并将表中的内容设置为宋体、12磅、居中。

（2）用公式计算综合成绩，其计算方法为：综合成绩=笔试成绩×30%+面试成绩×30%+操作成绩×40%，计算结果保留一位小数。

（3）用函数计算综合排名，并将计算结果填入对应的单元格中。

（4）用函数计算是否录用（综合排名的前三名被录用），如果录用则在其对应的单元格中显示“录用”，否则不显示任何内容。

4.创建“三好学生评选表”，内容如图1.133所示。按照题目要求完成后，用Excel的保存功能直接存盘。

图1.132　招聘考试情况统计表

图1.133　三好学生评选表

要求：

①表格要有可视的边框，并将表中的内容设置为宋体、10.5磅、居中；

②用函数计算数学、语文、英语、物理四科的平均成绩；

③用IF函数计算评选结果，其中数学、语文、英语、物理和综合评定大于等于85，且平均成绩大于等于90的在单元格中显示“三好”，否则单元格不显示任何内容；

④用函数计算三好学生的人数。

（5）创建“成绩汇总表”，内容如图1.134所示。按照题目要求完成后，用Excel的保存功能直接存盘。

图1.134　成绩汇总表

要求：

①表格要有可视的边框，将表中的表标题、列标题内容设置为宋体、12磅、加粗、居中，其他内容设置为宋体、12磅、居中；

②制作“分数段人数统计表”和“男女生平均分统计表”；

③使用FREQUENCY函数统计英语、高数、政治、计算机四门课程成绩的各分数段人数；

④使用AVERAGEIF函数统计英语、高数、政治、计算机四门课程的平均分。