Excel高级应用技术培训

东软培训讲师 陆元婕 二零零五年四月 2005年 4月 东软信息学院培训中心 陆元婕 2 画表 单元格、行列格式 、条件格式 计算 公式、函数 绘图 图表创建、 艺术字、自选图形 筛选 自动筛选、高级筛选、排序 分析 数据透视、分类汇总 2005年 4月 东软信息学院培训中心 陆元婕 3 培训日程表 创建电子表格 体验试算乐趣（第 1、 2章） 编修表格 美化优化报表格式（第 3、 4章） 创建图表 体验数据分析效果（第 5、 7章） （第 6章） 函数应用与高级算法处理（第 8章及 补充 ） 表格数据筛选、排序和分析（第 9、 10章） 表格数据筛选、排序和分析（ 补充 ） 与 （ 补充 ） 宏的录制和执行 （ 补充 ） 2005年 4月 东软信息学院培训中心 陆元婕 4 A 创建电子表格 体验试算乐趣 介绍 体验 创建表格的六个基本过程 表格创建的应用技巧 2005年 4月 东软信息学院培训中心 陆元婕 5 体验 案例 1 住房贷款计算器 （ 1） 控制住房贷款的月偿还额的主要因素包括：贷款额度 、 贷款年限及贷款利率； （ 2） 第 1种测算方法为传统的处理方法 ， 即采用显示多变量测算结果的表格来方便查询； （ 3） 第 2种测算方法能充分展示电子表格的优势 ， 即输入条件立刻得出计算效果 。 案例 2 各部销售统计 （ 1） 展示 （ 2） 展示 预警提示方面的能力 （ 3） 展示 案例 3 工资计算表 （ 1） 通过 （ 2） 通过 （ 3）展示 2005年 4月 东软信息学院培训中心 陆元婕 6 认识 借助修饰效果增强数据管理能力 图表在多视角观察和分析数据的能力 建立数据管理模型的能力 随时随地挖掘和分析数据的能力 网络数据共享和协同分析 2005年 4月 东软信息学院培训中心 陆元婕 7 标题栏 菜单栏 工具栏 名称框 编辑栏 列标或行标控制栏 工作区 单元格 垂直或水平滚动条 工作表标签 状态栏 2005年 4月 东软信息学院培训中心 陆元婕 8 2005年 4月 东软信息学院培训中心 陆元婕 9 创建表格的六个基本过程 1. 明确表格管理目标与结构关系 2. 表格数据的输入与精度控制 3. 表格的简单编辑和调整 4. 表格的简单修饰技巧 5. 表格数据的保存 6. 表格数据的打印输出 2005年 4月 东软信息学院培训中心 陆元婕 10 明确管理目标 建立表格结构 管理目标 表现三个部门间各项销售指标的比较情况 分别表现三个部门的各项销售指标完成情况 案例 创建多部门的销售统计表 表格创建技巧 单元格合并 当前日期的输入 ; 函数： ） ） 表格结构 标题区 表格名称和制表时间等 正文区 按照管理目标建立表格形式 落款区 比如填写制表人 2005年 4月 东软信息学院培训中心 陆元婕 11 其实是预定义的公式，它们使用称为参数的特定数值按特定的顺序或结构进行计算。 函数的结构 以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。 公式选项板 帮助创建或编辑公式的工具，还可提供有关函数及其参数的信息。单击编辑栏中的 编辑公式 按钮，或是单击 常用 工具栏中的 粘贴函数 按钮之后，就会在编辑栏下面出现公式选项板。 2005年 4月 东软信息学院培训中心 陆元婕 12 表格数据的输入与精度控制 输入原始数据 调用已有数据 利用“ =”号 使用公式计算 公式的编写 公式的复制 快速求和 单元格的错误提示 输入和定位工具 各种光标的含义 黑色方框 、 空心十字 、 竖线 回车键按下后的移动方向 （ 工具菜单 “ 选项 ” ） 2005年 4月 东软信息学院培训中心 陆元婕 13 表格的简单编辑和调整 针对单元格的编辑 单元格内容修改 单元格内容移动 单元格复制 简单函数的应用 平均值的求解 单元格编辑的技巧 选择性粘贴 清除与删除 定位（名称框） 2005年 4月 东软信息学院培训中心 陆元婕 14 表格的简单修饰技巧 “格式”菜单“单元格”功能的应用 数字格式 货币格式、小数点精确位数、百分数 文本对齐方式 跨列居中 边框与底纹 2005年 4月 东软信息学院培训中心 陆元婕 15 表格数据的保存与打印 常规保存文件 打印表格 预览表格 简单调整版面 打印输出 2005年 4月 东软信息学院培训中心 陆元婕 16 本章总结 数据输入 直接输入、引用、日期输入、公式计算 数据编辑 修改、移动、复制、清除、删除 表格修饰 数字格式、文字对齐方式、边框与底纹 打印输出 打印预览、调整版面、打印 2005年 4月 东软信息学院培训中心 陆元婕 17 本章练习 创建一个多部门人员工资情况统计表 标题：某单位各部门工资统计表 字段名：部门、姓名、基本工资、奖金、住房基金、保险费、实发工资 求：合计和平均值 住房基金 =基本工资 *10% 保险费 =基本工资 *10% 实发工资 =基本工资 +奖金 保险费 数字格式设为小数点保留两位 修饰表格 2005年 4月 东软信息学院培训中心 陆元婕 18 B 编修表格 美化优化报表格式 修饰数据表现形式的方法 表格数据的安全保护 条件格式的应用 报表的打印与输出 报表和数据的格式化 2005年 4月 东软信息学院培训中心 陆元婕 19 案例： 创建工资统计表 输入数据内容 填充法输入 月份 复制粘贴导入数据 用语音进行数字校对 保存文档 工资统计表的编辑 查找与替换 表格区域命名 语言公式 快速定位 填充（自定义序列）选项 行列的编辑与调整 插入 /删除行或列 调整行高和列宽 行或列的互换操作 工作表的控制 工作表的命名 工作表的添加和删除 多工作表控制 间隔表 连续表 统计工资发放情况 按月份进行单向统计 按季度进行双向统计 按年份进行三维统计 “ 工具 ” 菜单 “ 选项 ” 功能的应用 视图 编辑 常规 2005年 4月 东软信息学院培训中心 陆元婕 20 表格修饰的种类 数字格式 千元数字格式 特殊货币符号的设置 ,# 文本型数字格式的设置 电话号码、会计科目 文字格式 单元格中个别文字格式修饰 艺术字 对齐方式 跨列居中与合并居中 自动换行与人工换行 边框和背景 自动套用格式 自定义样式 保护工作表 单元格的保护 工作表的保护 利用定位功能保护工作表的公式 隐藏公式 允许用户编辑保护区域 条件格式 条件格式的预警效果 条件格式的应用 突出预警 快速画表 =1),3)=1 案例 修饰多部门的销售统计表 多个工作表的批量修改 2005年 4月 东软信息学院培训中心 陆元婕 21 表格文档的保存技巧 新文件的保存 特定意义的文件名，文档属性 旧文件的保存 备份保存 “工具”的“常规选项” 何时使用另存为 加密保存 2005年 4月 东软信息学院培训中心 陆元婕 22 表格的打印技巧 小表格打印 大表格的缩放打印 连续页面的打印 选择部分内容打印 超大表格的打印 强制分页打印 页眉页脚的设置 2005年 4月 东软信息学院培训中心 陆元婕 23 5种不良习惯： 1. 报表题目、表头不完整或语意不确切（多在时间、主体和报表内容等方面）； 2. 报表布局、数据项目顺序不合理；钩稽关系不明确甚至错误； 3. 数据缺失、漏填漏报；忘记注明金额单位和制表人等项目； 4. 部门名称或员工姓名出现错字、别字； 5. 部门名称或员工姓名等项目用字每次不能保持一致； 6. 为了美化格式或由于输入手误，字串中间、前后使用空格； 7. 为求得格式好看，大量使用“合并居中”功能； 8. 不论是文字数据还是数字数据一概“居中对齐”； 9. 文字和数字的字体、大小和格式不合适、不统一； 10. 行列的宽、高不适合，或是过宽或是过窄以至数字不能正确显示； 11. 字体或行列使用多种颜色，且搭配失当、杂乱； 12. 大量使用框线致使版面呆板； 13. 时间项目不按照 未能保持统一； 14. 标点符号全角、半角混用（如左右括号的输入）； 15. 公式计算结果如为错误值符号，一概保留，不加以清除。 2005年 4月 东软信息学院培训中心 陆元婕 24 本章总结 数字格式的处理方法 对齐方式的使用技巧 格线和背景的修饰 保护格式与处理手段 条件格式的设置方法 大表格打印技巧 2005年 4月 东软信息学院培训中心 陆元婕 25 练习 在“各部销售统计 ”的“一月”、“二月”、“三月”三张工作表中，将其中的数字型单元格区域的“ 6”设置为“货币”格式，并添加 ” 批量设置边框和背景 保护工作表并隐藏公式 设置条件预警信息 2005年 4月 东软信息学院培训中心 陆元婕 26 创建图表 体验数据分析效果 图表的类型与分析目标 用表格数据创建分析图表 图表的修饰与编辑技巧 关于图表应用的综合练习 借助图表的扩展能力突出显示数据关系。认识图表类型变化与数据管理分析之间的内在关系。学习运用图表类型，并在转置中熟悉分析数据的方法和技巧。 2005年 4月 东软信息学院培训中心 陆元婕 27 图表的类型与分析目标 直方图突出显示数据间差异的比较情况 线形图分析数据间趋势的变化 饼形图描述数据间比例分配关系的差异 雷达图显示多组有内部关联的数据对比 组合图表可进行多组数据关系的综合分析 月 三月部门一部门二部门三0100020003000400050006000700080001月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月1999年2000年2001年部门一一月二月三月012345学历资格证书工作经验合作能力沟通能力员工甲 员工乙 基本要求 员工丙- 1 . 0 01 . 0 03 . 0 05 . 0 07 . 0 09 . 0 01 1 . 0 01 3 . 0 0一月 二月 三月千6 , 6 6 5 . 8 06 , 6 6 6 . 0 06 , 6 6 6 . 2 06 , 6 6 6 . 4 06 , 6 6 6 . 6 0 千部门一 部门二 部门三 历年同业同期累计2005年 4月 东软信息学院培训中心 陆元婕 28 用表格数据创建图表的步骤 确定图表的分析目标 明确表格数据的取值范围 使用图表向导确定图表类型 对图表进行编辑和修饰 案例： 用图表展示销售情况 数据添加删除的技巧： 通过菜单选项添加 直接用数据拖拉数据 删除图表中的数据 图表的修饰技巧 修改边框 修改数据点或图块 修饰文本或数字 改变坐标轴 （ 显示方向 、 刻度 ） 添加标记 （ 数据点 、 数据表 、 备注 ） 图表的组合编辑技巧 间隔数据的图表制作技巧 不同类型的直方图表现不同的管理目标 (柱形图、条形图 ) 2005年 4月 东软信息学院培训中心 陆元婕 29 图表的修饰与编辑技巧 改变图表框的大小 移动图表框、图表对象 修饰图表中的对象 图表框、标题框 框线、轴线、刻度线、图形线 系列图块、独立图块 为图表添加标记 数值型标记 表格式标记 备注式标记 2005年 4月 东软信息学院培训中心 陆元婕 30 关于图表应用的综合练习 设置第 2 用于将两组相差悬殊的数据组合在同张表中 利用两个刻度分离两组数据 使相差悬殊的数据更易于阅读 综合练习 进度表 0 400 800 1200 1600 2000部门一部门二部门三0 400 800 1200 1600 2000第一季度计划 第二季度计划 第三季度计划 第四季度计划 第一季度完成 第二季度完成 第三季度完成 第四季度完成2005年 4月 东软信息学院培训中心 陆元婕 31 建立行为规范 保证建表质量 制作过程 重点 技巧 构思和设置结构 控制表格结构的合理性 数据项目拆 （ 独立列 ） 与逻辑关系 建立和输入数据 把握表格数据的准确性 输入技巧与建立和复制公式关系 编辑和修饰表格 增强表格数据的可读性 编辑对象 、 数字 、 对齐条件格式等 简单使用和分析 有效运用数据的分析性 组织数据 、 创建 、 编辑和修饰图表 保存表格数据 正确进行文件归档保存 保存 、 另存为及安全性 打印输出数据 掌握表格数据打印输出 小表格 、 大表格及超表格打印技巧 2005年 4月 东软信息学院培训中心 陆元婕 32 函数应用与高级算法处理 公式的创建与引用关系 函数的分类 几种常用函数介绍 函数功能的自学方法 非对每个人都有同等的价值。因此，本课程将通过说明函数类型、明确函数作用、应用范围以及帮助的使用方法，来掌握函数的使用规律，进而为自学应用奠定基础。 2005年 4月 东软信息学院培训中心 陆元婕 33 公式的创建与引用关系 创建公式的方法：“ =”号 普通的四则运算 单向统计 双向统计 三维统计 引用关系：“ $” 绝对引用 相对引用 混合引用 2005年 4月 东软信息学院培训中心 陆元婕 34 其实是预定义的公式，它们使用称为参数的特定数值按特定的顺序或结构进行计算。 嵌套函数 就是指在某些情况下，您可能需要将某函数作为另一函数的参数使用。 函数的结构 以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。 公式选项板 帮助创建或编辑公式的工具，还可提供有关函数及其参数的信息。单击编辑栏中的 编辑公式 按钮，或是单击 常用 工具栏中的 粘贴函数 按钮之后，就会在编辑栏下面出现公式选项板。 2005年 4月 东软信息学院培训中心 陆元婕 35 1 数据库函数 当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数。 2 日期与时间函数 通过日期与时间函数，可在公式中分析和处理日期值和时间值。 3 工程函数 工程工作表函数用于工程分析。 4 财务函数 财务函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值 等 5 信息函数 可以使用信息工作表函数确定存储在单元格中的数据的类型。 6 逻辑函数 使用逻辑函数可以进行真假值判断，或者进行复合检验。 7 查询和引用函数 当需要在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用时，可以使用查询和引用工作表函数。 8 数学和三角函数 通过数学和三角函数，可以处理简单的计算，例如对数字取整、计算单元格区域中的数值总和或复杂计算。 9 统计函数 统计工作表函数用于对数据区域进行统计分析。 10 文本函数 通过文本函数，可以在公式中处理文字串。 11 用户自定义函数 如果要在公式或计算中使用特别复杂的计算，而工作表函数又无法满足需要，则需要创建用户自定义函数。 2005年 4月 东软信息学院培训中心 陆元婕 36 统计函数 求平均值 求区域中数据的频率分布 按 按 求数据集的满足不同要求的数值的函数 求数据集的最大值 求数据集中第 求数据集中的中位数 求数据集中出现频率最多的数 用来排位的函数 统计学常用函数 方差 2005年 4月 东软信息学院培训中心 陆元婕 37 查询与引用函数 引用的作用 在 指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一单元格的数值。还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格、甚至其它应用程序中的数据。 可以根据搜索区域内最左列的值，去查找区域内其它列的数据，并返回该列的数据，对于字母来说，搜索时不分大小写。所以，函数 是精确的查找。二是近似的查找。 2005年 4月 东软信息学院培训中心 陆元婕 38 L O O K U 只是便于在不便中说明列 1 列 2 列 3 列 4 列 5 列 6元月 10 20 30 40 50二月 80 90 100 110 120三月 97 69 45 51 77输入要查找的月份 : 三月需要挑选出的列 : 4结果是 : 45= V L O O K U P ( G 1 1 , C 6 : H 8 , G 1 2 , S E )功能这个函数在表格左侧的行标题中查找指定的内容当找到时，它再挑选出该行对应的指定列的单元格内容。2005年 4月 东软信息学院培训中心 陆元婕 39 文本 /日期 /时间函数 文本函数是可以在公式中处理文字串的函数 大小写转换函数 取出字符串中的部分字符 去除字符串的空白 字符串的比较 合并文本函数 将数值转换为按指定数字格式表示的文本 取出当前系统时间 /日期信息 取得日期 /时间的部分字段值 005年 4月 东软信息学院培训中心 陆元婕 40 逻辑函数 来判断真假值，或者进行复合检验的 们称为逻辑函数。 据逻辑测试的真假值返回不同的结果。 示例：制作美观简洁的人事分析表 2005年 4月 东软信息学院培训中心 陆元婕 41 示例的函数解释 出生日期 19,17,7,2),/,17,9,2),/,17,11,2) 司龄（年资） 17,y),年 ,17,个月 ) 司龄（年资） 2 17,y),00)&年&17,00)&个月 性别 17,3)/2=17,3)/2),女 ,男 ) 2005年 4月 东软信息学院培训中心 陆元婕 42 数学函数 与求和有关的函数 四舍五入函数 数字相乘，并返回乘积值 计算给定区域内满足特定条件的单元格的数目 005年 4月 东软信息学院培训中心 陆元婕 43 函数的自学方法 2005年 4月 东软信息学院培训中心 陆元婕 44 数据的汇总分析 表格数据的整理与排序 对表格数据进行筛选分析 用分类汇总方法统计数据 数据透视表与数据透视图 数据库功能的其他应用技巧 数据筛选、排序；创建数据透视表与数据透视图、常用的数据汇总方法。 2005年 4月 东软信息学院培训中心 陆元婕 45 表格数据的整理与排序 常规排序和应用 多重排序和相互关系 排序原则 排序的技巧 数据表设置编号列 ,以便能恢复数据的原始排列 案例： 人事资料表 2005年 4月 东软信息学院培训中心 陆元婕 46 对表格数据进行筛选分析 自动筛选 高级筛选 数据筛选前的整理 统一数据类型 填补空白单元格 正确修饰单元格内容格式 ,避免输入多余的空格 去掉输入的多余空格的方法 : 查找替换功能 除了单词之间的单个空格外，清除文本中所有的空格。在从其他应用程序中获取带有不规则空格的文本时，可以使用函数 语法 ) 等于 2005年 4月 东软信息学院培训中心 陆元婕 47 自动筛选 筛选条件和规则 数字筛选的规则 文字筛选的规则 通配符“ ?”和“ *” 单一条件筛选 多重条件筛选 排名筛选 针对数字型字段 案例： 人事资料表 2005年 4月 东软信息学院培训中心 陆元婕 48 高级筛选 建立筛选条件区域 筛选条件区由表头和数据区构成 筛选条件区表头应与原始数据区表头一致 如果字段的筛选条件为多重 ,需要设置两个字段来填写条件式 案例： 人事资料表 2005年 4月 东软信息学院培训中心 陆元婕 49 用分类汇总方法统计数据 整理数据 需要分类处理的项目必须在独立列，并有名称 需汇总的列，表格数据不允许空白 每一列的数据格式要统一 集中排列需分类的项目 排序 纲目结构查看 筛选式查看 分类求和 案例： 人事资料表 2005年 4月 东软信息学院培训中心 陆元婕 50 数据透视 重组表格数据并添加算法，对数据进行深入分析 创建数据透视表的操作步骤 数据透视表的修改 2005年 4月 东软信息学院培训中心 陆元婕 51 数据功能的其他应用技巧 记录单 有效性 下拉式列表 限制数值输入 分列 导入外部数据 2005年 4月 东软信息学院培训中心 陆元婕 52 V 网络协同与 共享工作簿的编辑与修订 数据源的导入 从网络化办公的需求出发，介绍一些网络环境下处理数据的方法。包括工作簿共