高级办公自动化课件.ppt

实用与应用高效与专业 绍兴文理学院 高级办公自动化AOA 第二讲Excel高级应用与应用实例 Page 3 第一节Excel常用操作技巧 教学内容 Page 4 第一节Excel常用操作技巧 教学知识点数字输入技巧数据输入有效性设置选择性粘贴单元格的引用区域与名称数组应用工作表的安全 Page 5 第一节Excel常用操作技巧 数据输入技巧快速输入大写的人民币值方法 格式 单元格 数字 特殊 输入负数快速输入有部分重复的数据 如学号 方法 选中单元格 格式 单元格 数字 自定义 例 340822 形式在不连续的单元格中输入相同的数据方法 利用Ctrl键 将不连续的单元格选中 在最后一个选中的单元格中输入要显示的内容 按Ctrl Enter Page 6 第一节Excel常用操作技巧 长串数字的输入 包括0001型数据 输入以0开头的数字序列 单引号 开始或单元格格式设置为文本超过15位的数据以科学计数法显示的问题 方法 单元格格式为文本或 分数的输入方法 输入0 按空格后 输入分数当前日期 Ctrl Page 7 第一节Excel常用操作技巧 数据输入有效性设置 案例 限制单元格中数据的输入范围若数据输入不符合 显示出错信息鼠标在单元格上移动时显示提示信息让单元格提供下拉列表项进行选择输入 Page 8 第一节Excel常用操作技巧 选择性粘贴 案例2 行列转置数学运算 加 减 乘 除 方法 在空白单元格内输入要进行运算的数 按Ctrl C 选中要进行此运算的单元格 进行 选择性粘贴 引用某些通过公式计算的数据 Page 9 第一节Excel常用操作技巧 公式和运算符公式以 开始的表达式运算符算术运算文本运算 引用运算 区域运算符 如 Sum A1 B3 共6个单元格 联合运算符 如 Sum A1 B3 C1 D2 共10个空格交叉运算符 如 Sum A1 D2C1 E3 共4个 三维引用运算符 如 Sheet1 B2 Page 10 第一节Excel常用操作技巧 单元格的引用相对引用以当前单元格的位置为基准 在复制公式时 当前单元格改变时 单元格中的地址也随之发生变化例 A1绝对引用 例 A 1混合引用 例 A1 A 1案例 制作一张乘法口诀表 Page 11 第一节Excel常用操作技巧 三维引用 不同工作表之间的引用同一工作簿中不同工作表之间的引用格式 工作表标签 单元格引用如 Sheet1 B2不同工作簿中工作表的引用格式 路径 工作簿名 xls 工作表标签 单元格引用如 C 工资表 xls sheet1 A1思考 Sum sheet1 sheet4 A1 表示意思 三维引用案例 Page 12 第一节Excel常用操作技巧 区域与名称单元格与区域单元格本身就为一区域 多个单元格也是一个区域 一张表就是一个大区域区域名称例 A1 C3 就是一个区域 问题是它的名称是什么 可以把 A1 C3 当成它的名称 并对它进行引用 例 SUM A1 C3 但它不够形象 Page 13 第一节Excel常用操作技巧 区域名称的定义方法选中区域 直接在名称框中输入名称 插入 名称 选中区域 然后添加区域名称案例 Page 14 第一节Excel常用操作技巧 数组数组是单元的集合或是一组处理值的集合 一个数组公式可以占用一个或多个单元格区域 数组公式与普通公式的区别输入方法不同 普通公式按回车键 Enter 数组公式按 Shift Ctrl Enter 组合键在计算时 数组公式把多个数据做为一个整体进行计算 结果是返回多个数值 普通公式只是返回单个数值 即使在引用时引用了多个数值 但实际返回的只是其中的一个案例 了解数组是如何工作的 Page 15 第一节Excel常用操作技巧 数组数组常量数组中的参数直接是数值本身 这样的数组称为数组常量 分为一维数组和二维数组 一维数组包括水平数组和垂直数组 水平数组中元素用逗号隔开 垂直数组中元素用分号隔开 如 10 20 30 40 50 100 200 300 400 二维数组 用逗号将一行内的数据分开 用分号将隔行隔开 Page 16 第一节Excel常用操作技巧 编辑数组一个数组中包含多个单元格 这些单元格形成一个整体 数组中的单元格不能单独进行编辑 必须选取整个数组才能进行相应的操作 数组选取 单击数组中的任一单元格 编辑 定位 定位条件 当前数组 编辑数组 选择要编辑的数组 在编辑栏上单击鼠标 表示数组公式的括号消失 对公式进行编辑 再按Shift Ctrl Enter 完成对数组公式的修改删除数组 选定要删除的数组 按 Ctrl Delete 键或 编辑 清除 Page 17 第一节Excel常用操作技巧 数组应用在工作表中有时使用函数不能直接得到期望的结果 数组公式却能轻松解决 数组应用案例 Page 18 第一节Excel常用操作技巧 工作表的安全隐藏含有重要数据的行 列保护工作表隐藏数据计算的公式设置可编辑的区域 需输入密码才能在指定的区域输入数据 其他区域不能输入 设置可编辑区域的权限 无需输入密码就可在指定的区域输入数据 Page 19 教学内容 第二节Excel函数与应用案例 Page 20 第二节Excel函数与应用案例 教学知识点求和相关函数 Sum SumIf 文本函数 Mid Concatenate SubStitute Replace和EXACT 统计函数 Count CountIf Max Min Rank 条件函数与条件格式 If 查询与引用函数 LookUp HlookUp VlookUp 财务函数 PMT IPMT FV PV和SLN 日期时间函数 Date Year Month Day和Today Page 21 第二节Excel函数与应用案例 SumIf 按指定条件对若干单元格求和语法 SumIf Range Criteria Sum range 说明 Range 条件判断区域Criteria 条件表达式Sum Range 需要求和的单元格区域例 SUMIF A2 A11 A产品 C2 C11 案例 在房产销售表中统计个销售员的总销总额 Page 22 第二节Excel函数与应用案例 文本函数Mid函数 从文本指定的位置起返回指定长度的文本语法 Mid text start num num chars 说明 text 指定文本start num 起始位置Num chars 返回字符的个数 Page 23 第二节Excel函数与应用案例 文本函数Concatenate函数 将多个文本连接在一起 显示在一个单元格中语法 Concatenate text1 text2 Page 24 第二节Excel函数与应用案例 文本函数Substitute函数 替换文本中某个特定文本字符串语法 Substitute text1 old text new text instance num 说明 text1 原始文本old text 将被替换的字符串new text 替换old text的新字符串Instance num 若文本中有多组相同的old texts 可以使用该参数来指定被替换的字符串是文本中的第几组 若不指定instance num值 文本中所有的old text都会被替换 Page 25 第二节Excel函数与应用案例 文本函数Replace函数 将文本中某几位字符以新的字符替换语法 Replace old text start num num chars new text 说明 Old text 原始文本start num 替换的起始位置num char 多少个字符被替换new text 用来替换的新字符串 Page 26 第二节Excel函数与应用案例 文本函数Exact函数 比较两个文本字符串是否相同语法 Replace text1 text2 说明 相同返回True 不相同返回False此外 常用的文本函数还有Lower Upper更改大小写文本函数应用案例 Page 27 第二节Excel函数与应用案例 If函数与条件格式条件函数语法 If Logical Test value True value False 注意 条件函数与条件格式的区别条件函数的嵌套案例分析 Page 28 第二节Excel函数与应用案例 统计函数Count 统计区域内数字的个数案例 统计销售表中 A产品 销售的纪录的条数 COUNT IF A2 A12 A2 C2 C12 统计A2 A12区域中值为A2 在C2 C12中的数字的个数说明 输完公式后 按Ctrl shift Enter键 数组CountIf 统计区域内满足特定条件的单元格个数语法 CountIf Range Criteria 案例 按条件进行统计 Page 29 第二节Excel函数与应用案例 Rank 返回一个数值在一批数中的位置语法 Rank number range order 说明 Number 参加排序的数值Range 排序的范围Order 0为升序 非0为降序案例 按年龄大小进行排名统计函数还有CountBlank Max和Min等常用函数 Page 30 第二节Excel函数与应用案例 查询与引用函数LookUp 根据条件查询 分为向量型和数组型向量型 在单行或单列中查找指定的值 返回第二个单行或单列相同位置的值语法 LookUp value lookup vector result vector 说明 value 表示要搜索的值Lookup vector 要搜索的值所在的区域Result vector 返回值所在区域案例 根据员工的编号查询销售情况 Page 31 第二节Excel函数与应用案例 LookUp 根据条件查询 分为向量型和数组型数组型 在数组的第一行或第一列查找指定的值 返回数组最后一行或最后一列内同一位置的值语法 look up lookup value array 案例 根据员工的编号查询销售情况 Page 32 第二节Excel函数与应用案例 Hlookup 在表格首行查找指定的值 返回表格指定行的同一列上的值语法 Hlookup value array index 说明 value 表示在数据表的第一行中要查找的值Array 查找的数据表Index 表示返回数据表中第几行的值 如index 1表示返回第一行的值案例 统计每个销售员的提成比例 Page 33 第二节Excel函数与应用案例 VLookup 在表格或数组的首列查找指定的值 返回表格当前行中其他列的值语法 VLookup value array col index True False 说明 Value 表示要查找的值Array 表示查找的数据表Col index 返回数据表第几列值 如Col index 2 返回第2列的值True False 表示返回近似值还是精确值案例 填充商品的价格 Page 34 第二节Excel函数与应用案例 财务函数PMT函数 基于固定利率及等额分期付款方式 返回贷款的每期付款额语法 PMT rate nper pv fv type 说明 Rate 贷款利率nper 贷款的时间Pv 贷款的本金Fv 未来值 最后一次付款后的现金余额 可选Type 各期的付款时间是在期初还是期末 可选应用案例 Page 35 第二节Excel函数与应用案例 IPMT 用于固定利率及等额分期付款方式 返回贷款在某一给定期限内的利息金额语法 IPMT rate per nper pv fv type 说明 per用于计算利息数额的期数 其值在1 nper之间案例 某公司向银行贷款5万元 期限是5年 年利率为6 请计算该公司每年的偿还金额和每年偿还的利息金额 Page 36 第二节Excel函数与应用案例 FV 用于固定利率及等额分期付款方式 返回某项投资的未来值语法 FV rate nper pmt fv type rate 各期利率nper 总投资期pmt 各期应支付的金额Pv 本金type 付款时间是在期初 1 还是在期末 0 应用案例 某人用于某项投资5万元 年利率6 并在接下来的5年中每年再投资5000元 5年后应得到的金额是多少 Page 37 第二节Excel函数与应用案例 PV 用于计算某项投资所需的金额语法 FV rate nper pmt pv type rate 贷款利率nper 总投资期pmt 各期应支付的金额fv 未来值type 付款时间是在期初 1 还是在期末 0 应用案例 某项投资1 5万元 投资年限10年 回报年利率是15 那么预计投资多少金额 Page 38 第二节Excel函数与应用案例 SLN 用于计算设备每日 每月 每年的折旧值语法 SLN cost salvage life cost 资产原值salvage 资产在折旧末期的价值 即资产残值Life 资产的使用寿命应用案例 某店铺拥有固定资产5万元 使用10年后的资产残值为8000元 那么每天 每月 每年固定资产的折旧值是多少 Page 39 第二节Excel函数与应用案例 日期时间函数DATE函数 计算某一特定日期的系列编号语法 Date year month day 说明 year 指定的年份month 月份day 月份中的第几天注意 若输入的月份大于12 将从指定年份一月份开始往上累加 如date 2008 14 2 返回2009 2 2若输入的天数大于该月的最大天数 则从指定月数的第一天往上累加 如date 2008 1 35 返回2008 2 4 Page 40 第二节Excel函数与应用案例 日期时间函数DAY函数 返回指定日期所对应月份中的第几天语法 Day serial number 说明 Serial number 指定的日期或数值例 Day 2008 1 1 返回值为1Day 39448 返回值为1 系统日期将1900 1 1对应于序列号1 后面的日期都是相对于这个序列号进行累加拓展 Year函数 Month函数 Page 41 第二节Excel函数与应用案例 日期时间函数TODAY函数 返回系统当前的日期语法 TODay 应用案例 设备定期提醒 小王从事设备管理工作 有近千台机械设备需要定期进行精度检测 每天小王都要翻阅 设备鉴定台账 来寻找 到期 的设备 耗时耗力 请你运用所学的知识帮助小王建立一本 设备鉴定台账 实现设备 到期 自动提醒功能 Page 42 第二节Excel函数与应用案例 数据表概念字段 数据表中的列称为字段 列标题称为字段名记录 数据表中的每一行称为一条记录数据表的几点说明一个工作表一般只创建一个数据表同一列的数据类型相同避免使用空行或空列记录单的使用 Page 43 第二节Excel函数与应用案例 数据库函数数据库函数的语法 函数名 database field criteria 说明 database 数据清单field 指定函数所使用的数据列criteria 包含给定条件的单元格区域数据库函数的特点数据库函数均有上述三个参数数据库函数几乎都是以D开头 将字母D去掉 就是Excel中其他类型的函数 如DCount Count Page 44 第二节Excel函数与应用案例 数据库函数Dcount 返回数据库中满足指定条件的记录字段中包含数值单元格的个数DGet 用于从列表或数据库列中提取符合指定条件的单个值DAverage 计算列表或数据库的列中满足指定条件的数值的平均值DMax 返回列表或数据库的列中满足指定条件的最大值DSum 返回列表或数据库的列中满足指定条件的数字之和数据库函数案例 Page 45 教学内容 第三节数据管理与分析 Page 46 第三节数据管理与分析 教学知识点数据排序筛选分类汇总模拟运算表单变量求解数据透视表 图 Page 47 第三节数据管理与分析 数据排序普通排序自定义排序案例 对商品销售表进行排序 Page 48 第三节数据管理与分析 筛选 只显示满足特定条件的记录 不符合条件的记录隐藏起来自动筛选案例 对商品销售表进行筛选高级筛选说明 高级筛选首先要构造一个条件区域 注意条件区域不能与数据表连接 必须空出一行或一列条件区域构造的规则为 同一列中的条件是 或 关系 同一行的条件是 与 关系 Page 49 第三节数据管理与分析 分类汇总分类汇总是对数据表指定的行或列中的数据进行汇总统计说明 进行分类汇总前 必须对汇总的类进行排序分类汇总可以完成不同的计算 求和 平均值等案例 统计销售表中各个类别的商品销售数量 Page 50 第三节数据管理与分析 案例 对每个类别的不同品牌商品的销售金额与利润金额进行汇总分析 该题是按类别进行汇总 还是按品牌进行汇总思路 先对类别进行分类汇总 然后在此基础上再对品牌进行分类汇总排序 以类别为主关键字 品牌为此关键字进行排序说明 嵌套汇总后发现汇总有问题 可以修改Regedit修改方法 HKEY CURRENT USER Software Microsoft Office 11 0 Excel Options下新建一个 DWORD值 名称为Excel97Subtotals 值为1 Page 51 第三节数据管理与分析 模拟运算表模拟运算表是在一个单元格区域中 显示公式中变量不同取值时的结果 分为 单变量模拟运算表和双变量模拟运算表单变量模拟运算表单变量模拟运算表是对一个变量输入不同值时 对公式产生影响案例 分析银行贷款中不同的利率对每月还款的影响 Page 52 第三节数据管理与分析 双变量模拟运算表双变量模拟运算表是对两个变量输入不同的值时 对公式产生影响案例 分析银行贷款中不同的利率和不同的年限对每月还款的影响说明 模拟运算结果是以数组形式存放的 无法删除单个单元格中的值 若要删除 选中模拟运算的所有区域 按删除键 Page 53 第三节数据管理与分析 单变量求解使用单变量求解可以求出满足条件的值案例 根据自己的月还款能力来计算贷款年限 Page 54 第三节数据管理与分析 数据透视表 图 用于数据分析 可以从庞大的数据库中产生一个动态的汇总表格 能够根据字段的调整快速得出不同的分析结果创建数据透视表 利用向导创建数据透视表 图 案例 Page 55 教学内容 第四节数据的图示化表达 Page 56 第四节数据的图示化表达 教学知识点图表概述图表建立的原则错误或失败的图表分析不同类型图表的建立方法 Page 57 第四节数据的图示化表达 图表概述图表是指将工作表中的数据用图形表示出来 方便用户查阅信息图表中的图形与数据源关联在一起 当数据源发生变化时 图表相应的发生变化 自动更新 图表分为图表工作表和嵌入式图表 区别是图表工作表存在于一个单独的工作表中 而嵌入式图表与原始数据位于同一工作表中 Page 58 第四节数据的图示化表达 图表建立的原则一般用柱形图或条形图比较数据的多少关系 用折线图反映数据间的趋势关系 用饼图表现数据间的比例分配关系条形图的数据要从大到小排序 最大的在最上面柱型图数据要从小到大排序 最小的在最左边在条形图或柱型图中 同一种数据系列 应使用同一颜色图表的标题尽量说出你的观点在图表中尽量少使用3D效果图表美观 布局合理 注释文字恰当 Page 59 第四节数据的图示化表达 图表案例分析新华网上失败或错误的图表案例分析2002 2007中央财政 三农 支出情况 Page 60 第四节数据的图示化表达 存在问题 标题没有特色 要直接说出想通过图表表达的观点 所有图表标题改为 中央财政三农支出逐年增加 更好 显示的数据过细 既然关心中央财政支出 它是一个宏观层面的数据 谁还关注小数点后面的数字 过细的数据细节只能增加干扰2002年使用了不同的颜色标题与图表的柱型使用相同颜色 没有对比 单位使用深红色 没有必要 它属于弱化的元素 而深红色起到强调作用建议将增长率信息加入图表 Page 61 第四节数据的图示化表达 2002 2007中央财政 三农 支出情况 Page 62 第四节数据的图示化表达 存在问题 这是个完全失败的图表 难看 凌乱标题没有观点 大标题和小标题完全重复超过7个多达10个的项目 已不适合用饼图来表现 会显得很凌乱 而应该用条形图 从大到小排序 突出重点较长的标签在饼图上显示 较乱 且修饰过多分裂效果难看 各项目颜色不协调柔和注释竖排不习惯 注释应使用最弱化的形式 Page 63 第四节数据的图示化表达 修改后的图表 Page 64 第四节数据的图示化表达 单一图表的创建 图表向导 部分数据创建图表全部数据创建图表向图表中添加数据系列删除图表中的数据系列按行产生与按列产生图表 Page 65 第四节数据的图示化表达 图表的编辑与美化 操作案例 图表的标题标题的旋转标题的链接让数据源显示在图表中图表的转换系列与分类的交换使用数据标签 删除坐标轴 绘图区背景颜色数据系列的颜色图例的颜色 Page 66 第四节数据的图示化表达 在同一个图表中使用两种图表类型在一个图表中使用两种或者以上的图表类型 在这种情况下 需要理解 使用不同的图表类型是针对不同的数据系列而言的 在具体操作时 要注意选择恰当的数据系列进行图表类型的修改案例 将预测值用条形图表显示 实际值用折线图显示 Page 67 第四节数据的图示化表达 使用次坐标轴若图表中包含的两个数据系列之间的值相差很大 如果使用一个数值轴 那么另一个数据系列的差别情况可能显示得很不明显 在这种情况下 可以通过使用次坐标轴来改善另一个数据系列的显示情况设置方法 图表可采用两种类型 选中没有坐标轴值的数据系列 右击 数据系列格式 次坐标轴 案例 Page 68 第四节数据的图示化表达 双坐标轴图表和次坐标轴相似案例分析 2008年绍兴每月的平均气温和降水量见表格 请制作图表 平均气温用柱型图表示 降水量用折线图表示 其