2.5WPS 表格综合应用实战（课件）-《信息技术》同步教学（大连理工大学出版社）

模块二 电子表格制作 　　WPS 表格是一款功能完善的电子表格处理软件 , 能够集数据 、 图形 、 图表于一体 。 在日常学习与工作中 , 对数据的处理越来越集中化 、 智能化 。WPS 表格作为重要工具 , 可以进行数据处理 、 分析和辅助决策 , 并广泛应用于管理 、 统计 、 金融等众多领域 。 对于广大操作用户来说 ,WPS 表格不仅可以制作各类精美的电子表格 , 还可以用来统计和分析各种数据 , 制作复杂的图表 。 　　本模块通过制作项目实例的方式 , 介绍 WPS 表格的具体操作方法 , 包括 WPS 表格基本操作 、 公式及函数的应用 、 使用图表分析数据 , 数据排序筛选 、 合并计算 、 分类汇总 、 数据透视表 、 跨工作表单元格引用 、 条件格式设置等 。 1 项目一　员工信息表 ——— WPS 表格数据的录入与编辑 2 项目二　员工工资表 ——— WPS 表格使用公式和函数计算数据 3 项目三　产品销售统计表 ——— WPS 表格数据分析及处理 4 项目四　销售统计图表 ——— WPS 表格图表统计与分析数据 5 项目五　绩效考核表 ———WPS 表格综合应用实战 2 05 绩效考核表 ——— WPS 表格综合应用实战 项目描述 　　本项目主要介绍 WPS 表格综合应用 , 使学生能够更加灵活地掌握和使用 WPS 表格 , 轻松完成工作 。 订单管理系统结果如图 2-5-1 所示 , 绩效考核表结果如图 2-5-2 所示 。 项目描述 项目分析 　　完成绩效考核情况的统计 , 本项目任务如下 : 　　任务一 : 订单管理系统工作表数据的整理 、 筛选 、 合并计算 。 　　任务二 : 为绩效考核表工作表填充数据 , 为 “ 岗位目标销售额 ” 列设置下拉列表以输入数据 。 　　计算 “ 考勤总计 ”、“ 岗位绩效 ” 和 “ 总计 ” 数据 。 　　任务三 : 创建图表 , 利用 “ 总计 ” 数据创建柱形图表 。 项目分析 项目实现 　　1.订单管理系统工作表 　　(1) 单击 “ 数据源 ” 工作表 , 拖曳选择 A2:H30 单元格区域 , 将数据复制到 “ 订单管理系统 ”工作表中 , 如图 2-5-3 所示 。 项目实现 　　 (2) 在 “ 订单管理系统 ” 工作表中 , 选择 A1:I1 单元格区域 , 选择 “ 开始 ” 选项卡 , 在功能区中单击 “ 筛选 ”, 在表头的单元格区域建立筛选功能 , 如图 2-5-4 所示 ; 在 “ 订单数量 ” 所在列 , 单击下拉按钮 , 在展开的下拉面板中 , 单击 “ 数字筛选 ” 按钮 , 选择 “ 大于 ”, 在 “ 自定义自动筛选方式 ” 对话框中设置筛选条件为 “ 大于 120” 的筛选规则 , 对表格进行筛选 , 如图 2-5-5 所示 。 项目实现 项目实现 项目实现 　　为 “ 客户姓名 ” 所在列单元格区域进行颜色填充 。 左键拖曳选择 “ 客户姓名 ” 所在列的单元格区域 , 单击 “ 开始 ” 选项卡 , 在功能区中单击 “ 填充颜色 ” 按钮 , 在下拉列表 “ 主题颜色 ” 中选择“ 浅绿 , 着色 6, 浅色 40%”( 第四行第十列 ), 如图 2-5-6 所示 。 再次单击 “ 筛选 ” 按钮 , 退出筛选状态 。 项目实现 　　(3) 单击选择 “ 销售人员 ”(A1) 单元格 , 对 “ 销售人员 ” 单元格所在列进行 “ 升序 ” 排列 。 选择 “ 开始 ” 选项卡 , 在功能区中单击 “ 排序 ” 下拉按钮 , 选择 “ 升序 ” 排列 , 如图 2-5-7 所示 。 项目实现 　　(4) 计算 “ 订单总价 ” 数据信息 。 选定 I2 单元格 , 输入公式 “=E2*F2”, 按 Enter 键确认 。将鼠标放置在 I2 单元格右下角 , 左键拖曳填充 I2:I30 单元格区域 , 如图 2-5-8 所示 。 项目实现 　　单击 L4 单元格 , 选择 “ 数据 ” 选项卡 , 在功能区中单击 “ 合并计算 ” 按钮 , 在弹出的 “ 合并计算 ” 对话框中 , 在 “ 函数 ” 下拉列表中选择 “ 求和 ” 选项 , 左键拖曳选择 H2:I30 单元格区域 , 单击“ 添加 ” 按钮 , 勾选 “ 最左列 ” 复选框 , 如图 2-5-9 所示 。 完成合并计算 , 结果如图 2-5-10 所示 。 项目实现 项目实现 项目实现 　　2.绩效考核表 　　将 “ 合并计算 ” 后的数据复制到 “ 绩效考核表 ” 工作表中的 H3:H10 单元格区域中 , 如图 2-5-11 所示 。 项目实现 　　(1) 计算 “ 考勤总计 ” 单元格所在列的数据信息 　　“ 考勤总计 ” 计算方法为 : 满分 30 分 , 迟到 、 早退每次扣 2 分 , 病 、 事假每次扣 1 分 , 旷工每次扣 4 分 。 单击选择 G3 单元格 , 输入公式 “=30-D3*2-E3*1-F3*4”, 如图 2-5-12 所示 , 按 Enter 键确认 。 将光标移到 G3 单元格右下角 , 左键拖曳填充 G3:G10 单元格区域 , 如图 2-5-13 所示 。 项目实现 项目实现 　　(2) 使用下拉列表快速输入数据 　　在 “ 岗位目标销售额 ” 工作表中 , 拖曳选择 B2:B4 单元格区域 , 复制到“ 绩效考核表 ” 工作表中的表格外区域 , 用作数据参考信息 , 如图 2-5-14 所示 ; 在 “ 绩效考核表 ” 中左键拖曳选择 I3:I10 单元格区域 , 选择 “ 数据 ” 选项卡 , 单击 “ 插入下拉列表 ” 按钮 , 在弹出的 “ 插入下拉列表 ” 对话框中 , 选择 “ 从单元格选择下拉选项 ” 单选按钮 , 在下面的文本框中输入单元格区域地址 , 或者利用 “ 折叠 ” 按钮选择刚刚复制过来的数据 , 如图 2-5-15 所示 。 对照 “ 职务 ” 列信息 , 依次填入 “ 岗位目标销售额 ” 列信息 , 对齐方式设置为 “ 水平居中对齐 ”, 结果如图 2-5-16 所示 。 项目实现 项目实现 项目实现 项目实现 　　(3) 计算 “ 岗位绩效 ” 数值信息 　　“ 岗位绩效 ” 计算方法为 :“( 销售金额 - 岗位目标销售额 )/ 岗位目标销售额 *100”。 选定J3 单元格 , 输入公式 “=(H3-I3)/I3*100”, 按 Enter 键确认 , 如图 2-5-17 所示 。 将光标移到J3 单元格右下角 , 左键拖曳填充 J3:J10 单元格区域 , 如图 2-5-18 所示 。 项目实现 项目实现 　　 (4) 计算 “ 总计 ” 数值信息 　　“ 总计 ” 计算方法为 :“ 岗位绩效 *0.7+ 考勤总计 ”。 选定 K3 单元格 , 输入公式 “=J3*0.7+G3”, 按 Enter 键确认 , 如图 2-5-19 所示 。 将光标移到 K3 单元格右下角 , 左键拖曳填充K3:K10 单元格区域 , 如图 2-5-20 所示 。 项目实现 项目实现 　　 (5) 设置条件格式 　　选择 K3:K10 单元格区域 , 单击 “ 开始 ” 选项卡 , 在功能区中单击 “ 条件格式 ” 下拉按钮 , 打开下拉列表 , 选择 “ 色阶 ” 选项 , 选择 “ 蓝 - 白 - 红色阶 ”( 第二行第一列 ), 为 “ 总计 ” 单元格设置条件格式 , 如图 2-5-21 所示 。 　　选择 G3:G10 单元格区域 , 单击 “ 开始 ” 选项卡 , 在功能区中单击 “ 条件格式 ” 按钮 , 打开下拉列表 , 选择 “ 突出显示单元格规则 ” 选项 , 选择 “ 小于 ”, 在 “ 小于 ” 对话框中 , 左侧文本框中输入 “20”, 在 “ 设置为 ” 下拉列表中选择 “ 浅红色填充 ”, 为 “ 考勤总计 ” 列设置条件格式如图 2-5-22 所示 。 项目实现 项目实现 项目实现 　　(6) 添加批注 　　选择 B10“ 张佳佳 ” 单元格 , 单击 “ 开始 ” 选项卡 , 在功能区中单击 “ 填充颜色 ”, 为单元格填充颜色 “ 黄色 ”( 标准色 : 第四个 ), 右击 , 在弹出的快捷菜单中选择 “ 插入批注 ” 命令 ; 在新建的 “ 批注 ” 对话框中输入 “ 最佳员工 ”, 调整批注大小和位置 , 如图 2-5-23 所示 。 项目实现 项目实现 　　3.创建图表 　　选择 K3:K10 单元格区域 , 选择 “ 插入 ” 选项卡 , 在功能区中单击 “ 全部图表 ” 按钮 , 在 “ 插入图表 ” 对话框中选择 “ 柱形图 ” 中的 “ 簇状柱形图 ”, 单击 “ 插入 ” 按钮 , 调整图表大小和位置达到美观 , 如图 2-5-24 所示 。 项目实现 　　编辑 “ 图表标题 ”, 输入图表标题 “ 绩效考核图 ”, 如图 2-5-25 所示 。 项目实现 　　选中图表 , 选择 “ 图表工具 ” 选项卡 , 在功能区中单击 “ 添加元素 ” 下拉按钮 , 在下拉列表中单击 “ 数据标签 ”, 选择 “ 数据标签外 ” 选项 , 为图表添加数据标签 , 如图 2-5-26 所示 。 项目实现 　　选择 “ 图表工具 ” 选项卡 , 在功能区中单击 “ 添加元素 ” 下拉按钮 , 在下拉列表中单击 “ 网格线 ”, 选择 “ 主轴主要水平网格线 ” 选项 , 取消 “ 主轴主要水平网格线 ”, 如图 2-5-27 所示 。 项目实现 　　选择 “ 图表工具 ” 选项卡 , 在功能区中单击 “ 选择数据 ” 按钮 , 在 “ 编辑数据源 ” 对话框中 , 单击 “ 类别 ” 右侧的 “ 编辑 ” 按钮 , 在 “ 轴标签 ” 对话框 “ 轴标签区域 ” 文本框中选择 B3:B10 单元格区域 , 单击 “ 确定 ” 按钮 , 为图表添加 “ 轴标签 ”, 如图 2-5-28 所示 。 项目实现 　　在 “ 图表工具 ” 选项卡中选择 “ 系列 1 数据标签 ” 选项 , 单击 “ 设置格式 ” 按钮 , 在右侧打开“ 属性 ” 任务窗格 , 选择 “ 文本选项 ” 选项卡 , 选择 “ 文本填充 ” 方式为 “ 渐变填充 ”, 颜色为 “ 橙红色 - 褐色渐变 ”( 渐变填充第二个 ), 如图 2-5-29 所示 。 项目实现 　　对图表柱形填充颜色 , 依次选中每个系列柱形 , 依次在 “ 属性 ” 任务窗格中按渐变填充中的颜色 , 为系列柱形添加颜色 , 如图 2-5-30 所示 。 相关知识 　　IF函数 　　(1)IF 函数的功能 　　判断给出的条件是否满足 , 如果满足将返回一个值 , 如果不满足则返回另一个值 。 　　(2)IF 函数的语法格式 　　IF(Logical_test,Value_if_true,Value_if_false) 　　其中 , 包括如下三个参数 。 　　Logical_test: 逻辑判断表达式 。 　　Value_if_true: 表达式为真时返回的值 。 　　Value_if_false: 表达式为假时返回的值 。 　　IF 函数可以最多使用 64 层嵌套构造出复杂的判断条件 。 可以根据实际情况 , 设计复杂的判断条件 , 示例见表 2-5-1。 相关知识 　　在 “ 成绩等级 ” 工作表中的 D2 单元格中的公式可以改为 “=IF( 各科成绩 ! D2<60,″E″, IF( 各科成绩 ! D2<70,″D″, IF( 各科成绩 ! D2<80,″C″, IF( 各科成绩 ! D2<90,″B″, IF( 各科成绩 ! D2<=100,″A″,″ 缺考 ″)))))”。 相关知识 　　将公式分解后 , 各部分含义如下 : 　　IF( 各科成绩 ! D2<60,″E″,IF(……)): 如果 “ 各科成绩 ” 中 D2 单元格的值小于 60, 返回成绩等级 “E”; 否则 , 执行下一个 IF 函数 , 即 IF( 各科成绩 ! D2<70,″D″,IF(……)) 。 　　IF( 各科成绩 ! D2<70,″D″,IF(……)): 如果 “ 各科成绩 ” 中 D2 单元格的值小于 70 并大于或等于 60, 返回成绩等级 “D”; 否则 , 执行下一个 IF 函数 , 即 IF( 各科成绩 ! D2<80,″C″,IF(……)) 。 　　表达式中虽然没有 “>=60” 的形式存在 , 但由于“D2<60” 的条件已经在上一个 IF 函数中被执行 , 并且这种 IF 函数的嵌套中第 1 个 IF 函数的优先级要高于第 2 个 IF 函数 , 所以能进入第 2 个 IF 函数判断的只有 60 分以上的情况 。 　　IF( 各科成绩 ! D2<80,″C″,IF(……)) 和 IF( 各科成绩 ! D2<90,″B″,IF(……)) 的含义与 IF( 各科成绩 ! D2<70,″D″,IF(……)) 相似 。 　　IF( 各科成绩 ! D2<=100,″A″,″ 缺考 ″)): 如果 “ 各科成绩 ” 中 D2 单元格的值小于或等于100, 返回成绩等级 “A”; 否则 , 返回 “ 缺考 ”。 创新作业 　　1.数据处理 　　(1) 使用 “ 成绩单模板 ” 工作表中的数据计算总分 (SUM)、 班级平均分 (AVERAGE)、 班级最高分 (MAX) 及班级最低分 (MIN)。 　　(2) 为 “ 总分 ” 一列数据设置条件格式 ——— 色阶 。 　　(3) 为总分最高的学生添加批注 “ 总分第一 ”。 　　(4) 使用 “ 成绩单模板 ” 工作表中的数据填充成绩分析表 , 并计算参考人数 (COUNT)、 应考人数 (COUNTA)、 缺考人数 (COUNTIFS)。 如图 2-5-31 和图 2-5-32 所示 。 创新作业 创新作业 　　2.图表 　　使用成绩分析表中 A2:E5 单元格区域数据 , 制作 “ 成绩分析图 ”( 簇状柱形图 )。 如图 2-5-33所示 。 $$