资源简介

(共52张PPT)
任务 三　
任务 一
计算总分及各科平均分、最高峰、最低分
任务 二　
评定奖励等级
计算总分排名
实战与小结
任务 四　
使用VOOKUP函数制作学生成绩查询表
任务一
计算总分及各科平均分、最高分、最低分
能够使用SUM、AVERAGE、MAX 及MIN 函数进行计算。
一、认识常用函数及其语法格式
函数实际上是一个预先定义的特定计算公式。使用函数不仅可以完成复杂的计算，还可以简化公式的繁杂程度。函数作为公式的一种特殊形式，也是以“=”开始，如“=SUM(C3:G3)”，其含义是对C3、D3、E3、F3、G3 单元格数值进行求和。
1. SUM 函数
功能：返回所有数值之和。
语法：SUM( 数值1, 数值2,…)。其中“数值1，数值2，…”为需要求和的1 到255 个数值。
2. AVERAGE 函数
功能：返回所有数值的平均值（算术平均值）。
语法：AVERAGE( 数值1, 数值2,…)。其中“数值1，数值2，…”为需要计算平均值的1 到255 个数值。
3. MAX 函数
功能：返回数值列表中的最大值，忽略文本值和逻辑值。
语法：MAX( 数值1, 数值2,…)。其中“数值1，数值2，…”为需要计算最大值的1 到255 个数值。
4. MIN 函数
功能：返回参数列表中的最小值，忽略文本值和逻辑值。
语法：MIN( 数值1, 数值2,…)。其中“数值1，数值2，…”为需要计算最小值的1 到255 个数值。
以上函数中，均可像前例一样，用“：”标示数据范围。
二、计算总分
1. 插入SUM 函数
打开素材文件“学生成绩表.xlsx”中的“学生成绩表”工作表，选定H3 单元格，单击“公式”选项卡下的“自动求和”下拉按钮，在下拉列表中，单击“求和”命令，如下图a 所示；参数部分直接输入或拖动鼠标选中C3:G3 单元格区域，按下“Enter”键，完成学生“张丽”总分的计算，如下图b 所示。
a）选项位置 b）输入公式效果
插入SUM 函数
2. 使用填充柄复制公式
选定H3 单元格，将光标移动至H3 单元格的右下角，当鼠标指针变成如下图a所示。黑色粗十字形标志“+”时，按住鼠标左键向下拖动至H22 单元格，完成总分列的计算，如下图b 所示。
a）鼠标指针变为十字形 b）填充效果
使用填充柄复制公式
数值的引用
1. 在计算总分时，除了可使用SUM 函数计算外，还可以使用手动录入公式的方法计算。使用公式进行计算时，先在编辑栏录入“=”，再输入参数和运算符，按下“Enter”键。例如，本任务中计算总分，选定H3 单元格，在编辑栏中录入“=C3+D3+E3+F3+G3”，按下“Enter”键，即可计算出“张丽”的总分。
2. 在实际工作中，很少直接输入单元格的数值进行数据计算，而是通过引用数值所在的单元格或单元格区域进行数据计算。通过单击选定或拖动选定直接引用数值所在的单元格或单元格区域，既方便又可提高数据的准确性。
3. 使用填充柄复制公式的过程，是使用了相对引用，即公式所在单元格的位置改变，则引用也会随之改变。
三、计算平均分
1. 插入AVERAGE 函数
选定C23 单元格，单击“公式”选项卡下的“自动求和”下拉按钮，在下拉列表中，单击“平均值”命令；参数部分直接输入或拖动鼠标选定C3:C22 单元格区域，按下“Enter”键，完成“思政”平均分的计算，如右图所示。
a）选项位置 b）完成效果
插入AVERAGE 函数
2. 使用填充柄复制公式
选定C23 单元格，使用填充柄向右填充至H23 单元格，完成语文、数学、英语、计算机及总分平均分的计算，如下图所示。
使用填充柄计算平均分
四、计算最高分、最低分
使用MAX 函数在C24 单元格中计算出“思政”的最高分，其公式为“=MAX
(C3:C22)”，如下图a 所示；使用MIN 函数在C25 单元格中计算出“思政”的最低分，其公式为“=MIN(C3:C22)”，如下图b 所示；然后使用填充柄向右复制公式分别计算语文、数学、英语、计算机及总分的最高分和最低分。
a）计算最高分 b）计算最低分
使用MAX 函数、MIN 函数计算最高分、最低分
任务二
评定奖励等级
能够使用IF 函数根据条件进行计算。
一、认识IF 函数的功能和语法格式
功能：判断一个条件是否满足；如果满足返回一个值，如果不满足则返回另外一个值。
语法：IF( 测试条件，真值，假值)。其中，测试条件是作为判断条件的表达式，表达式成立结果为真，不成立则结果为假；真值表示测试条件为真时返回的值；假值表示测试条件为假时返回的值。
本任务中，奖励等级档次划分见下表。
奖励等级档次划分表
上一任务完成的学生成绩表中， 评定“张丽” 的奖励等级， 其公式为“=IF(H3>=450," 一等",IF(H3>=425," 二等",IF(H3>=400," 三等"," 无")))”。
二、插入IF 函数
选定J3 单元格，单击“公式”选项卡下的“插入函数”按钮，如下图a 所示；弹出“插入函数”对话框，单击“选择函数”列表框中的“IF”函数；单击“确定”按钮，如下图b 所示。
a）按钮位置 b）“插入函数”对话框
插入IF 函数
三、设定参数并嵌套两条IF 函数
1. 输入参数
在弹出的“函数参数”对话框，设置“测试条件”为“H3>=450”，“真值”为“一等”，然后单击“假值”文本框后，单击“名称框”列表中的“IF”函数，如下图a 所示。
2. 嵌套第二条IF 函数
单击“名称框”列表的中“IF”函数后，弹出“函数参数”对话框，设置“测试条件”为“H3>=425”，“真值”为“二等”，单击“假值”文本框后，单击名称框中“IF”函数，如下图b 所示。
3. 嵌套第三条IF 函数
在弹出“函数参数” 的对话框中， 设置“测试条件” 为“H3>=400”，“真值”
为“三等”，“假值”为“无”；单击“确定”按钮，计算出“张丽”的奖励等级，如下图c 所示。
函数使用熟练后，也可在公式编辑栏或单元格中直接输入函数表达式。
4. 使用填充柄复制公式
选定J3 单元格，使用填充柄向下填充至J22 单元格，完成每位学生奖励等级的计
算，如下图d 所示。
a）输入参数 b）嵌套第二条IF 函数
c）嵌套第三条IF 函数 d）使用填充柄复制公式
设定参数并嵌套两条IF 函数
函数嵌套
函数嵌套是指函数内嵌套另外一个函数或一个函数成为另外一个函数的参数。在实际应用中，如果一条IF 函数无法满足计算需求，这时可以使用多条IF 函数进行嵌套。
如本任务中， 使用了如下的公式：“=IF(H3>=450," 一",IF(H3>=425," 二等",IF(H3>=400," 三等"," 无")))”， 上述IF 函数语句的语法解释为： 如果H3>=450 成立，则执行第二个参数（返回结果“一等”）；如果不成立，则执行第三个参数，在这里第三个参数为第二个IF 函数，继续判定单元格H3 的值是否大于425，成立则返回“二等”，否则继续执行第三个IF 函数，最后完成四个奖励等级的判断。
IF 函数最多可嵌套七层，在嵌套下一条函数时，要确保插入点光标在正确位置，否则会出现语法错误。
任务三
计算总分排名
能够使用RANK 函数进行计算。
一、认识RANK 函数的功能和语法格式
功能：返回某数字在一列数字中相对于其他数值的大小排名。
语法：RANK（数值，引用，排位方式），其中“数值”为需要找到排位的指定数字；“引用”为一组数或对一个数据列表的引用，非数字值将被忽略；“排位方式”为指定排位的方式，如为0 或空，为降序；如为非零值，为升序。
本任务中，根据学生的总分以降序方式进行排名。其中计算“张丽”名次的公式为“=RANK(H3,$H$3:$H$22,0)”，其中“$H$3:$H$22”使用了绝对引用。“$”表示在使用填充柄复制公式时，不改变其后的行号或列号。
二、插入RANK 函数
选定I3 单元格，单击“公式”选项卡下的“插入函数”按钮，如下图a 所示；弹出“插入函数”对话框，在“查找函数”文本框中输入“rank”，选择“选择函数”列表框中的“RANK”函数；单击“确定”按钮，如下图b 所示。
a）按钮位置 b）“插入函数”对话框
插入RANK 函数
三、设定参数并进行计算
1. 设定参数
在弹出的“函数参数”对话框中，设置“数值”为“H3”，“引用”为“$H$3:
$H$22”（选定单元格范围后按“F4”键可快速添加绝对引用符“$”，反复按“F4”键还可切换不同添加形式），“排位方式”为“0”；单击“确定”按钮，如下图a所示。
2. 使用填充柄复制公式
选定I3 单元格，使用填充柄向下填充至I22 单元格，完成每位学生排名的计算，如下图b 所示。
a）“函数参数”对话框 b）完成效果
设定RANK 函数参数
引用的类型和相同数值的排序规则
绝对引用是指在计算过程中，公式所在单元格位置改变，但公式中引用的单元格保持不变。使用绝对引用时，被引用的单元格需添加绝对引用符“$”，如$A$1。按“F4”键可快速添加绝对引用符。在本任务中，“$H$3:$H$22”添加绝对引用符，是为确保使用填充柄向下复制时，被引用的H3:H22 单元格区域保持不变。
使用函数RANK 排名时，经常会出现相同数值，相同的数值排位相同，但会影响后一位的排位。如本任务中，出现了两个相同数值，其排位都是1，此时就不再有排位2 的数值，后一位的排位是3。
任务四
使用VLOOKUP函数
制作学生成绩查询表
能够使用VLOOKUP 函数查找数据。
一、认识VLOOKUP 函数的功能和语法格式
功能：给定一个需要查找的目标，再从指定的查找区域中查找到相应的数据，并将此数据返回到指定位置。
语法：VLOOKUP( 查找值, 数据表, 列序数, 匹配条件)VLOOKUP 函数参数说明见下表。
VLOOKUP 函数参数说明
二、新建工作表
在素材文件“学生成绩表.xlsx”中，新建一个名为“成绩查询表”的工作表；在A1 单元格中录入内容“学生成绩查询表”，设置其字体为“黑体”，字号为“12”，字形为“加粗”，合并居中A1:I1 单元格区域；在A2:I2 单元格区域中录入成绩查询表的各项标题，如下图所示。
学生成绩查询表
三、设置数据有效性
在A3 单元格中，使用数据有效性，引用“学生成绩表”工作表中的B2:B22 单元格区域，制作姓名下拉列表，如下图所示。
a）“数据有效性”对话框 b）完成效果
使用数据有效性制作姓名下拉列表
四、使用VLOOKUP 函数
1. 插入VLOOKUP 函数
选定B3 单元格；单击编辑栏中的“插入函数”按钮，弹出“插入函数”对话框， 在“查找函数” 文本框中输入“VLOOKUP”， 选择“选择函数” 列表框中的“VLOOKUP”函数；单击“确定”按钮。
2. 设定参数
弹出“函数参数”对话框，设置“查找值”为“A3”，“数据表”为“学生成绩表!$B$3:$J$22”，“列序数”为“2”，“匹配条件”为“FALSE”，单击“确定”按钮；完成后可查询A3 单元格显示的同学的“思政”成绩，如下图所示。
a）“函数参数”对话框 b）完成效果
选择姓名查询“思政”成绩
3. 查询其他各项成绩信息
按照相同的方法，在“C3”“D3”“E3”“F3”“G3”“H3”“I3”单元格中分别使用VLOOKUP 函数查询各项成绩信息，如下图所示。
选择姓名查询学生成绩
VLOOKUP 函数使用技巧
查找区域可以跨工作表引用，且可使用绝对引用，形式如“学生成绩表!$B$3:$H$22”。
“函数参数”对话框中设定“数据表”项的查找区域时，查找值必须在此单元格区域中的第一列，且该区域中必须包含返回值。
在VLOOKUP 函数中可嵌套COLUMN 函数获取当前列序号，提高效率，例如制作学生成绩查询表时，具体操作步骤如下：
在B3 单元格中插入VLOOKUP 函数，弹出“函数参数”对话框，设置“查找值”为“$A3”，“数据表” 为“学生成绩表!$B$3:$J$22”，“列序数” 为“COLUMN()”，“匹配条件”为“FALSE”，单击“确定”按钮；完成后，使用填充柄向右填充至I3 单元格即可实现整行内容的查询，不必再逐一输入函数，如右图 所示。
VLOOKUP+COLUMN 函数的使用
实战与小结
制作员工工资表
某企业每月都需要制作员工工资表，制作完成后打印成工资条发放给每位员工。此工资表由6 部分组成，其中，基本工资、代扣保险和其他扣款是固定的，工龄工资、绩效奖金、岗位津贴则根据员工自身情况浮动。打开素材文件“员工工资表.xlsx”中的“员工工资表”工作表，参照以下思路完成制作。制作完成后的效果图如下两图所示。
“员工工资表”效果图
“员工工资条”效果图
思路如下：
1. 使用IF 函数计算员工工龄工资。员工工龄低于5 年（不含5 年），工龄工资为每年50 元；其余人员工龄工资为每年100 元。
2. 使用IF 函数计算员工绩效奖金。员工绩效评分大于或等于80 分，绩效奖金为1 000 元；绩效评分在60 分至80 分（不含80 分）之间，绩效奖金为每分10 元，小于60 分则绩效奖金为0 元。
3. 使用VLOOKUP 函数查询员工岗位津贴。员工的岗位津贴是根据员工职务而定的，“岗位津贴标准”表中列举了各职务的岗位津贴数据。
4. 使用SUM 函数计算员工实发工资。
5. 使用VLOOKUP 函数制作工资条。在“工资条”工作表的A2:M2 单元格区域中录入工资条的各项标题；在A3 单元格中录入员工工号“S-001”；在“B3”单元格中使用VLOOKUP 函数和COLUMN 函数，查询出员工的姓名数据，使用填充柄复制公式至B3:M3 单元格区域；第一名员工工资条制作完成后选定A1:M4 单元格区域，使用填充柄向下填充至第60 行。
6. 保存文件。
制作员工培训成绩表
结合所学公式和函数的相关知识，完成“员工培训成绩表”的制作。制作完成后的效果图如下图所示。
“员工培训成绩表”效果图
思路如下：
1. 打开素材文件“员工培训成绩表.xlsx”。
2. 使用SUM 函数计算总分列数据。
3. 使用AVERAGE 函数计算平均分列数据。
4. 使用RANK 函数对总分以降序方式计算排名。
5. 使用IF 函数评定奖励等级。其中，总分≥360 分，奖励等级为一等；360＞总分≥330 分，奖励等级为二等；330＞总分≥300 分，奖励等级为三等；其余为无。
6. 使用VLOOKUP 函数根据奖励等级计算奖励金额。
7. 保存表格。
本项目主要介绍了SUM、AVERAGE、MAX、MIN、IF、RANK、VLOOKUP 函数的使用方法，其知识内容如下。
1. 单元格的引用
单元格的引用是指在公式中使用单元格的地址来代替单元格中的数据，见下表。
引用定义表
2. 常用函数公式
函数是WPS 表格预先定义好的内置公式，由标识符、函数名、参数组成，并按特定的顺序或结构进行计算。函数的参数与参数之间用半角逗号分隔，使用函数运算后得出的结果称为返回值。WPS 表格常用的函数见下表。
常用函数表

展开更多......

收起↑