2.2WPS 表格使用公式和函数计算数据（课件）-《信息技术》同步教学（大连理工大学出版社）

模块二 电子表格制作 　　WPS 表格是一款功能完善的电子表格处理软件 , 能够集数据 、 图形 、 图表于一体 。 在日常学习与工作中 , 对数据的处理越来越集中化 、 智能化 。WPS 表格作为重要工具 , 可以进行数据处理 、 分析和辅助决策 , 并广泛应用于管理 、 统计 、 金融等众多领域 。 对于广大操作用户来说 ,WPS 表格不仅可以制作各类精美的电子表格 , 还可以用来统计和分析各种数据 , 制作复杂的图表 。 　　本模块通过制作项目实例的方式 , 介绍 WPS 表格的具体操作方法 , 包括 WPS 表格基本操作 、 公式及函数的应用 、 使用图表分析数据 , 数据排序筛选 、 合并计算 、 分类汇总 、 数据透视表 、 跨工作表单元格引用 、 条件格式设置等 。 1 项目一　员工信息表 ——— WPS 表格数据的录入与编辑 2 项目二　员工工资表 ——— WPS 表格使用公式和函数计算数据 3 项目三　产品销售统计表 ——— WPS 表格数据分析及处理 4 项目四　销售统计图表 ——— WPS 表格图表统计与分析数据 5 项目五　绩效考核表 ———WPS 表格综合应用实战 2 02 员工工资表 ——— WPS 表格使用公式和函数计算数据 项目描述 　　本项目以制作员工工资表为载体 , 介绍 WPS 表格基本公式和函数的相关操作 。 通过本项目的学习 , 学生能够通过函数批量处理数据 ; 使用所提供的 “ 员工工资素材 ”, 利用 WPS 表格制作一份 “ 员工工资表 ”, 并以 “ 员工工资表 ” 为名保存 , 参考效果如图 2-2-1 所示 。 项目描述 项目分析 　　本项目首先需要将 “ 原始信息 ” 表中的对应信息填充到 “ 员工工资表 ”。 根据图 2-2-1, 需要进行的分析任务有 : 计算员工人数 、 工资总和 、 平均工资 、 最高工资和最低工资 , 填充 “ 计算各部门人数 ” 表生成 “ 各部门人数统计 ” 图表 。 　　本项目操作步骤分析如下 : 　　任务一 : 原始数据处理 , 将 “ 原始信息 ” 表中的相关信息填充到 “ 员工工资表 ” 中 。 　　任务二 :“ 员工工资表 ” 数据统计分析 。 项目实现 　　1.处理原始数据 　　(1) 打开所提供的员工工资素材文件 , 选择员工工资表模板 , 将其另存为 “ 员工工资表 .et”。 　　具体操作 : 单击功能区 “ 文件 ” 按钮 , 在下拉菜单中选择 “ 另存为 ”→“WPS 表格文件 (*.et)”选项 。 在弹出的 “ 另存文件 ” 对话框中设置文件保存位置 , 给文件命名为 “ 员工工资表 .et”, 单击 “ 保存 ” 按钮 。 如图 2-2-2 所示 。 项目实现 　　(2) 根据 “ 模板 ” 表中所给出的各个对应 “ 表头 ”, 在 “ 原始信息 ” 表中选取相对应的数据素材 , 使用复制功能依次将信息素材复制到对应单元格中 。“ 扣款 ” 列信息复制后 , 右键单击 , 选择 “ 粘贴为数值 ”, 如图 2-2-3 所示 。 项目实现 　　2.“员工工资表(9月份)”数据计算 　　(1) 计算员工 “ 绩效工资 ” 所在列的单元格区域数据 。 绩效工资 = 加班时长 × 加班费用 。单击 J4 单元格 , 输入公式 “=F4*G4”, 按 Enter 键计算出结果 , 如图 2-2-4 所示 ; 将鼠标指针移动到单元格右下角 , 当鼠标指针变成 “+” 时拖动鼠标至 J18 单元格 , 如图 2-2-5 所示 。 项目实现 项目实现 　　(2) 计算员工 “ 应发工资 ” 所在列单元格区域数据 , 应发工资 = 岗位工资 + 绩效工资 + 奖金 。 单击 L4 单元格 , 单击 “ 公式 ” 选项卡 , 在功能区中单击 “ 插入函数 ” 选项 , 在弹出的 “ 插入函数 ” 对话框中搜索 “SUM” 函数 , 单击 “ 确定 ” 按钮 。 打开 “ 函数参数 ” 对话框 , 如图 2-2-6 所示 。选择 I4:K4 单元格区域 , 单击 “ 确定 ” 按钮计算出结果 , 如图 2-2-7 所示 ; 将鼠标指针移动到单元格右下角 , 当鼠标指针变成 “+” 时拖动鼠标至 L18 单元格 , 如图 2-2-8 所示 。 项目实现 项目实现 项目实现 项目实现 　　(3) 计算员工 “ 实发工资 ” 所在列单元格区域数据 , 实发工资 = 应发工资 - 扣款 。 单击 N4单元格 , 输入公式 “=L4-M4”, 按 Enter 键计算出结果 , 如图 2-2-9 所示 ; 将鼠标放到该单元格右下角处 , 左键拖曳填充 N4:N18 单元格区域 , 如图 2-2-10 所示 。 项目实现 项目实现 项目实现 　　(4) 填充 “ 是否全勤 ” 所在列单元格区域数据 。 判断是否全勤的标准为 “ 出勤天数等于 23天 ”, 使用函数 “IF” 进行统计 。 选定 O4 单元格 , 单击 “ 公式 ” 选项卡 , 在功能区中单击 “ 插入函数 ”, 在弹出的 “ 插入函数 ” 对话框中搜索 “IF” 函数 , 如图 2-2-11 所示 ; 单击 “ 确定 ” 按钮 , 在弹出的 “ 函数参数 ” 对话框中 ,“ 测试条件 ” 文本框输入 “H4=23”,“ 真值 ” 文本框中输入 “″ 全勤 ″”, 在“ 假值 ” 文本框中按一下空格键 , 如图 2-2-12 所示 , 单击 “ 确定 ” 按钮 , 显示出结果 ; 将鼠标放到O4 单元格右下角处 , 左键拖曳填充 O4:O18 单元格区域 , 如图 2-2-13 所示 。 项目实现 项目实现 项目实现 项目实现 　　 (5) 定义单元格区域名称 , 选择 “ 实发工资 ” 所在列的 N4:N18 单元格区域 , 单击 “ 公式 ” 选项卡 , 在功能区中单击 “ 名称管理器 ”, 在弹出的 “ 名称管理器 ” 对话框中单击 “ 新建 ” 按钮 , 如图 2-2-14 所示 。 项目实现 　　 在 “ 名称 ” 文本框中输入 “ 实发工资 ”, 单击 “ 确定 ” 按钮 , 如图 2-2-15 所示 。 返回 “ 名称管理器 ” 对话框 , 单击 “ 关闭 ” 按钮 。 项目实现 　　(6) 计算 “ 员工人数 ” 信息 。 选定 R4 单元格 , 单击 “ 公式 ” 选项卡 , 在功能区中单击 “ 插入函数 ” 选项 , 在弹出的 “ 插入函数 ” 对话框中搜索 “COUNT”, 如图 2-2-16 所示 ; 单击 “ 确定 ” 按钮 ,在 “ 函数参数 ” 对话框的 “ 值 1” 文本框中输入 “ 实发工资 ”, 如图 2-2-17 所示 。 单击 “ 确定 ” 按钮 。 项目实现 25 项目实现 　　(7) 计算 “ 工资总和 ”, 选定 R5 单元格 , 单击 “ 公式 ” 选项卡 , 在功能区中单击 “ 插入函数 ” 选项 , 搜索 “SUM” 函数 , 如图 2-2-18 所示 ; 单击 “ 确定 ” 按钮 , 在 “ 函数参数 ” 对话框的 “ 数值 1” 文本框中输入 “ 实发工资 ”, 如图 2-2-19 所示 。 单击 “ 确定 ” 按钮 , 计算出结果 。 项目实现 项目实现 　　(8) 计算 “ 平均工资 ” 信息 , 选定 R6 单元格 , 单击 “ 公式 ” 选项卡 , 在功能区中单击 “ 插入函数 ” 选项 , 搜索 “AVERAGE” 函数 , 如图 2-2-20 所示 ; 单击 “ 确定 ” 按钮 , 在 “ 函数参数 ” 对话框的“ 数值 1” 文本框中输入 “ 实发工资 ”, 如图 2-2-21 所示 。 单击 “ 确定 ” 按钮计算出结果 。 项目实现 项目实现 　　 (9) 计算 “ 最高工资 ” 信息 , 选定 R7 单元格 , 单击 “ 公式 ” 选项卡 , 在功能区中单击 “ 插入函数 ”选项 , 搜索 “MAX” 函数 , 如图 2-2-22 所示 ; 单击 “ 确定 ” 按钮 , 在 “ 函数参数 ” 对话框的 “ 数值 1” 文本框中输入 “ 实发工资 ”, 如图 2-2-23 所示 。 单击 “ 确定 ” 按钮计算出结果 。 项目实现 项目实现 　　(10) 计算 “ 最低工资 ” 信息 , 选定 R8 单元格 , 单击 “ 公式 ” 选项卡 , 在功能区中单击 “ 插入函数 ”选项 , 搜索 “MIN” 函数 , 如图 2-2-24 所示 ; 单击 “ 确定 ” 按钮 , 在 “ 函数参数 ” 对话框的 “ 数值 1” 文本框中输入 “ 实发工资 ”, 如图 2-2-25 所示 。 单击 “ 确定 ” 按钮计算出结果 。 项目实现 项目实现 　　(11) 计算各部门人数 , 使用 “COUNTIF” 函数分别计算各部门人数 。 选择 E4:E18 单元格区域 , 单击选项卡中 “ 公式 ” 选项卡 , 在功能区中单击 “ 名称管理器 ”, 单击 “ 新建 ” 按钮 , 在 “ 名称 ”文本框中输入 “ 部门 ”, 如图 2-2-26 所示 , 单击 “ 确定 ” 按钮 , 再单击 “ 关闭 ” 按钮 。 使用函数填充数据计算表格中各部门人数信息 , 单击 R13 单元格 , 单击 “ 公式 ” 选项卡 , 在功能区中单击 “ 插入函数 ” 选项 , 搜索 “COUNTIF” 函数 , 如图 2-2-27 所示 。 单击 “ 确定 ” 按钮 , 在 “ 函数参数 ” 对话框的 “ 区域 ” 文本框中输入 “ 部门 ”,“ 条件 ” 文本框中输入 “″ 财务 ″”, 如图 2-2-28 所示 ; 单击 “ 确定 ” 按钮计算出结果 。 重复上一步骤 , 依次完成各个部门人数计算 。 计算出结果后 , 会自动生成图表 , 如图 2-2-29 所示 。 项目实现 项目实现 项目实现 项目实现 相关知识 　　1.VLOOKUP 　　格式 :VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 　　功能 : 在表格或数值数组 ( 参数 table_array 指定单元格区域或数值数组 ) 的首列查找指定的数值 ( 参数 lookup_value 指定数值 ), 并由此返回表格或数组当前行中指定列 ( 参数 col_index_num 指定列号 ) 处的数值 。 　　参数 : 　　lookup_value 为需要在数据单元格区域第一列中查找的数值 。lookup_value 可以为数值 、 引用或文本字符串 。 　　table_array 为需要在其中查找数据的数据表 。 可以使用对区域或区域名称的引用 。 　　col_index_num 为 table_array 中待返回的匹配值的列序号 。col_index_num 为 1 时 , 返回 table_array 第一列中的数值 ;col_index_num 为 2, 返回 table_array 第二列中的数值 , 以此类推 。 如果 col_index_num 小于 1, 函数 VLOOKUP 返回错误值 #VALUE!; 如果 col_index_num 大于 table_array 的列数 , 函数 VLOOKUP 返回错误值 #REF!。 　　range_lookup 为逻辑值 TRUE 或 FALSE, 指明函数 VLOOKUP 返回时是精确匹配还 是近似匹配 。 如果为 TRUE 或省略 , 则返回近似匹配值 , 也就是说 , 如果找不到精确匹配值 ,则返回小于 lookup_value 的最大数值 ; 如果 range_value 为 FALSE, 函数 VLOOKUP 将返回精确匹配值 。 如果找不到 , 则返回错误值 #N/A。 相关知识 　　2.TEXT函数与设置单元格格式的区别 　　单击 “ 开始 ” 选项卡 “ 数字 ” 组中的按钮 , 或在 “ 单元格格式 ” 对话框 “ 数字 ” 选项卡中设置有关格式后 , 单元格的数字将按设置的方式予以显示 。WPS 表格还提供了 TEXT 函数来设置数字格式 。 　　但是两者有着本质的区别 。 　　设置单元格的格式 , 仅仅是数字所显示外观的改变 , 其实质仍然是数值本身 , 仍然可以进一步直接用于求和等运算 ; 如果单元格中输入的是带撇号 (') 的强制以文本形式存储的数字 ,无法通过设置单元格格式方式改变 , 得到的效果是 “ 显示 ” 效果 。 　　使用 TEXT 函数可以将数值转换为带格式的文本 , 其实质已经是文本 , 即使除了数字之外不包含其他字符 , 也是以文本形式存储的数字而不能直接作为数值用于求和等运算 ; 得到的效果是“ 现实 ”效果 。 相关知识 　　3.“符号”基本功能描述 　　公式是指以等号 “=” 为引导 , 使用运算符并按照一定的顺序组合进行数据运算的等式 , 通常包含运算符 、 单元格引用 、 数值 、 工作表函数等元素 。 公式可以用在单元格中 , 也可以用于条件格式 、 数据验证 、 名称等其他允许使用公式的地方 。 　　函数是预先定义并按照特定的顺序和结构 , 来执行计算 、 分析等数据处理任务的功能模块 。 函数具有简化公式 、 提高编辑效率的特点 。 调用函数的语法形式为 := 函数名 ( 参数 1, 参数 2, 参数 3……)。 　　“ 公式记忆键入 ” 功能能够根据用户输入公式时的关键字 , 显示备选的函数和已定义的名称列表 , 按上 、 下方向键可以选择要使用的函数 , 按 Tab 或 Enter 键可以快速补齐函数名称 , 或直接用鼠标选择函数并双击插入 。 　　“ 参数中文提示 ” 功能可以帮助用户了解函数语法中的参数名称 、 可选参数或必需参数等 ,函数屏幕提示工具条会自动悬浮在函数编辑位置附近 。 相关知识 　　4.常用函数介绍 　　(1) 逻辑函数 　　①=AND( 条件 1,[ 条件 2]...[ 条件n]) 　　所有条件成立返回 TRUE, 所有条件都不成立返回 FALSE。 　　②=OR( 条件 1,[ 条件 2]...[ 条件n]) 　　所有条件都不成立返回 FALSE, 任意条件成立返回 TRUE。 　　③=IF( 测试条件 , 真返回值 ,[ 假返回值 ]) 　　判断一个条件是否满足 , 如果满足返回一个值 , 否则返回另一个值 。 相关知识 　　(2) 日期时间函数 　　①=DATE( 年 , 月 , 日 ) 　　组合日期 , 例如 =DATE(2019,1,2) 则返回日期 2019-1-2。 　　②=TIME( 小时 , 分 , 秒 ) 　　组合时间 , 例如 =TIME(10,50,13) 则返回时间 10:50:13。 　　③=TODAY() 　　返回当前日期 。 　　④=DATEDIF( 开始日期 , 终止日期 , 比较单位 ) 　　计算两个日期相距的年月日 。 其中 , 比较单位为 “Y”、“M” 或 “D”,Y 代表年 ,M 代表月 ,D 代表天 , 只能按其中一个单位计算相距的长度 。 相关知识 (3) 文本函数 ①=LEN( 字符串 ) 返回字符个数 。 ②=LEFT( 字符串 ,[ 字符个数 ]) 从字符串左边开始取字符 。 ③=RIGHT( 字符串 ,[ 字符个数 ]) 从字符串右边开始取字符 。 ④=MID( 字符串 , 开始位置 , 字符个数 ) 从字符串的指定位置取字符 。 相关知识 　　(4) 查找引用函数 　　①=VLOOKUP( 查找值 , 数据表 , 列序数 ,[ 匹配条件 ]) 　　数据匹配 。 其中 , 匹配条件为 0 或者 1, 当为 0 时代表精确查找 ( 常用于查找文本条件 ),为 1 时代表模糊查找 ( 常用于查找数字区间 )。 　　②=MATCH( 查找值 , 查找区域 ,[ 匹配类型 ]) 　　返回指定内容在指定区域的次序 。 其中 , 匹配条件为 0 或者 1, 当为 0 时代表精确查找( 常用于查找文本条件 ), 为 1 时代表模糊查找 ( 常用于查找数字区间 )。 　　③=INDEX( 数组 , 行序数 ,[ 列序数 ],[ 区域序数 ]) 　　返回指定区域内指定次序的内容 。 创新作业 　　按如下要求完成 , 结果如图 2-2-30 所示 。 创新作业 　　1.定义单元格区域名称 　　(1) 定义 “ 销售数量 ” 一列数据区域单元格名称为 “ 销售数量 ”。 　　(2) 定义 “ 销售额 ” 一列数据区域单元格名称为 “ 销售额 ”。 　　2.公式 　　在单元格区域 G3:G12 中使用公式统计出 “ 销售额 ”。( 提示 : 销售额 = 销售数量 * 产品单价 ) 　　3.函数 　　(1)IF 函数 : 在单元格区域 H3:H12 中使用函数统计出 “ 销售情况 ”, 若 “ 销售数量 ” 大于4 000, 则 “ 销售情况 ” 为 “ 突出 ”, 否则为 “ 一般 ”。 　　(2)MAX 函数 : 在单元格区域 E14 中使用函数统计出 “ 销售数量最高值 ”。 　　(3)MIN 函数 : 在单元格区域 E15 中使用函数统计出 “ 销售数量最低值 ”。 　　(4)SUM 函数 : 在单元格区域 E16 中使用函数统计出 “ 所有商品销售额 ”。 $$