Excel进阶教程完美版ppt课件

Excel培训教程 中文Excel进阶教程 第一章公式与函数1 1公式1 2函数1 3财务函数第二章Excel数据管理与分析1 记录单的使用2 数据的排序第三章图表处理1 建立图表2 修改图表的设置3 建立数据透视表和数据透视图4 建立一张数据透视表5 由数据透视表创建数据透视图 中文Excel进阶教程 总目录 第四章工具使用1 宏2 VBA控件的使用3 控件与宏使用范例第五章综合使用案例 第一章公式与函数 1 1公式1 1 1输入公式和编辑公式1 1 2单元格引用1 1 3公式计算和循环引用1 1 4合并计算1 2函数1 2 1输入带函数的公式1 2 2函数和公式的深入使用1 3财务函数财务函数统计函数数据库函数函数应用案例 各章分目录 第一章公式与函数 1 1公式 1 1 1输入公式和编辑公式Excel中公式的格式包括 1 符 表示用户输入的内容是公式而不是数据 2 操作符 如加 减 乘 除 乘方等3 引用的单元 参加运算的单元格的名称 如A1 C3等Tips 可以直接输入单元格的名称或用鼠标单击要选用的单元格 例如 在F4中输入公式 D4 0 即可自动计算F4的值 Tips 公式的基本概念 公式和公式语法 公式是对单元格中数值进行计算的等式 Excel中的公式是按照特定顺序进行数值计算的 这一特定顺序即为语法 Excel中的公式遵守一个特定的语法 最前面是等号 后面是参与计算的元素和运算符 默认状态下 Excel是从等号开始 从左到右计算公式 可以通过修改公式语法来控制计算的顺序 如可以通过添加括号来改变语法 第一章公式与函数 1 1 2单元格引用公式的灵活性是通过单元格的引用实现的 单元格的引用是指将公式所使用的单元格与公式挂在一起 公式可以自动调用单元格的值进行运算 单元格的引用分绝对引用和相对引用 绝对引用是指公式所引用的单元格是固定不变的 即 无论将它剪切或复制到哪里 都将因用同一个单元格 绝对引用使用 符号 相对引用将公式剪切或复制到其他单元格 引用会根据当前行和列的内容自动改变 相对引用只需直接输入单元格的名称 Excel默认为相对引用 例如 B5单元格输入 E 4 2 则其值为1672 将该公式复制到D5 其值也是1672 如果在C5中输入 C 4 其值将为36 复制到D5 公式变为D 4 值也变为0 Tips 可以使用 F4 键实现相对引用和绝对引用的切换 第一章公式与函数 1 1 3公式计算和循环引用公式的计算可设置为 自动重算 当公式引用的单元格中数据改变时 公式自动重新计算 除模拟运算表外 自动重算 手工重算 当公式引用的单元格中数据改变时 公式不会自动重新计算 只有用户双击含有该公式的单元格之后 才重新计算 保存前自动重算 在手工重算方式下才有效 但保存工作表时 工作表中公式才自动重新计算 设置方法 工具 选项 进入 选项 对话框 选择 重新计算 窗体 第一章公式与函数 循环引用 公式计算的另一个问题是循环引用 自动重算方式下某个公式直接或间接引用了该公式的单元格时 就会产生公式调用自己单元格中数据的情况 这时共识会反复调用进行运算 这成为公式的循环引用 出现循环引用时 Excel将在一定的循环计算次数之后 或在两次计算结果之间的差值小于某个误差值时 停止迭代 具体设置可在 重新计算 框中设置 Tips 可以引用同一工作簿不同工作表的单元格 不同工作簿的单元格 如 在Book1的Sheet1中引用Sheet2中的A1单元格应表示为Sheet2 A1 若要引用Book2的Sheet2中的A1单元格 应表示为 Book2 Sheet2 A1 第一章公式与函数 1 1 4合并计算在要对具有相似表结构的多个表进行类似数据的汇总计算时 可以使用Excel提供的 合并计算 功能 简单实例 要对Sheet1 一月份 Sheet2 二月份 Sheet3 三月份 的对应单元格C4 C8的对应值进行求和 生成结果保存在Sheet4 一季度汇总 用合并计算生成一季度的汇总任务 第一章公式与函数 步骤 1 选择 一季度汇总 中C4单元格位置 结果将保存在这里 选择主菜单 数据 中 合并计算 命令 2 选择 函数 组合框中 求和 项 在选择 引用位置 的 选取范围 按钮 然后选择 一月份 表的C4 C8单元格 按 返回 按钮 选择 添加 按钮 将其添加到引用为之列表中 然后依次将要进行合并计算的 二月份 和 三月份 表的C4 C8单元格引用位置添加到引用列表中 第一章公式与函数 3 如果设定参加合并计算的引用位置有错误 可在引用位置列表中 先选择该项再点 删除 按钮 删除该引用项 最后选择 确定 按钮 完成合并计算 注意 合并计算不同于表间函数运算 当他引用的数据源中数据发生改变后 合并计算的结果并不会随之改变 如有这种情况发生就需要重新进行合并计算 Tips 对比较复杂的统计汇总表进行汇总时 对于经常修改数据的表 推荐用多表间函数和公式运算来完成 他们会在数据源发生改变后自动更新数据 不再需要重新运算 第一章公式与函数 1 2函数函数是Excel实现它强大的计算功能的有力工具 函数实际上就是一种公式 Excel将用户经常用到的公式和一些特殊的计算应用作为内置的公式来提供给用户 Excel提供了超过200个的函数用于不同的场合 为用户的各种数据计算 处理和分析提供了强大的功能 一个简单的例子 比如 在计算 合计 值时 要在某单元格输入 C3 C4 C5 C6 当记录很多时 输入公式将成为一个很麻烦的事情 如果使用函数 只需输入 SUM C3 C6 即可 提高了工作效率 第一章公式与函数 1 2 1输入带函数的公式1 函数的格式 符号 表示是公式 函数 函数名称 表示进行什么操作 是英文单词的缩写 括号 包含函数的参数 及函数的输入值 参数 要在函数中使用的值和单元格 2 函数的输入 键盘输入 对用户要求较高 必须知道函数的确切格式 使用函数向导 1 单击要输入函数的单元 2 单击 插入 函数 弹出对话框3 选择函数类别4 在 选择函数 找到所需函数5 确定 弹出另一对话框 步骤 第一章公式与函数 这个对话框中 上面是参数文本框 可输入参数值或单元格 或单击文本框右边的按钮 切入工作表 用鼠标选择单元格 设定好函数参数后 单击 确定 按钮 第一章公式与函数 1 2 2函数和公式的深入使用1 工作表间的函数和公式运算在合并计算中 提到过可以利用函数来完成多表数据计算 仍然以此为例 1 在季度汇总表 中 选择C4单元格作为函数的添加位置 2 选择 插入 函数 选择SUM函数 弹出函数向导3 点击按钮 选择 一月份 表中C4单元格 点击按钮返回 第一章公式与函数 4 仿照前面步骤 分别指定Number2和Number3项单元格计算位置 结果如右图所示 5 点 确定 求和结果如下图所示 第一章公式与函数 6 在C4单元格右下角位置拖动鼠标的 拖动句柄 拖至C8单元格处释放 复制公式如下图所示 2 工作簿间的函数和公式运算例如需要进行一季度工作簿和二季度工作簿的求和 以完成半年度的汇总 第一章公式与函数 1 打开一季度工作簿和二季度工作簿 新建半年度工作簿 选择 窗口 重排窗口 垂直并排 结果如下图所示 2 选择半年度汇总表C4单元格 使用前面介绍的方法启动函数向导 选择SUM函数 既可利用向导选择要计算的单元格引用的位置 第一章公式与函数 设置结果如下图所示 3 最后可得半年度销量汇总 结果如右下图所示 第一章公式与函数 1 3财务函数财务函数可以进行一般的财务计算 如确定贷款的支付额 投资的未来值或净现值 以及债券或息票的价值 1 财务函数中常见的参数 未来值 fv 在所有付款发生后的投资或贷款的价值期间数 nper 投资的总支付期间数付款 pmt 对于一项投资或贷款的定期支付数额现值 pv 在投资初期的投资或贷款的价值 例如 贷款的现值为所借入的本金数额 利率 rate 投资或贷款的利率或贴现率类型 type 付款期间内进行支付的间隔 如在月初或月末 第一章公式与函数 1 ACCRINT用途 返回定期付息有价证券的应计利息 语法 ACCRINT issue first interest settlement rate par frequency basis 参数 Issue为有价证券的发行日 First interest是证券的起息日 Settlement是证券的成交日 即发行日之后证券卖给购买者的日期 Rate为有价证券的年息票利率 Par为有价证券的票面价值 如果省略par 函数ACCRINT将par看作 1000 Frequency为年付息次数 如果按年支付 frequency 1 按半年期支付 frequency 2 按季支付 frequency 4 2 ACCRINTM用途 返回到期一次性付息有价证券的应计利息 语法 ACCRINTM issue maturity rate par basis 参数 Issue为有价证券的发行日 Maturity为有价证券的到期日 Rate为有价证券的年息票利率 Par为有价证券的票面价值 Basis为日计数基准类型 0或省略时为30 360 1为实际天数 实际天数 2为实际天数 360 3为实际天数 365 4为欧洲30 360 第一章公式与函数 3 AMORDEGRC用途 返回每个会计期间的折旧值 语法 AMORDEGRC cost date purchased first period salvage period rate basis 参数 Cost为资产原值 Date purchased为购入资产的日期 First period为第一个期间结束时的日期 Salvage为资产在使用寿命结束时的残值 Period是期间 Rate为折旧率 Basis是所使用的年基准 0或省略时为360天 1为实际天数 3为一年365天 4为一年360天 4 AMORLINC用途 返回每个会计期间的折旧值 该函数为法国会计系统提供 如果某项资产是在会计期间内购入的 则按线性折旧法计算 语法 AMORLINC cost date purchased first period salvage period rate basis 参数 Date purchased为购入资产的日期 First period为第一个期间结束时的日期 Salvage为资产在使用寿命结束时的残值 Period为期间 Rate为折旧率 Basis为所使用的年基准 0或省略时为360天 1为实际天数 3为一年365天 4为一年360天 第一章公式与函数 5 COUPDAYBS用途 返回当前付息期内截止到成交日的天数 语法 COUPDAYBS settlement maturity frequency basis 参数 Settlement是证券的成交日 即发行日之后证券卖给购买者的日期 Maturity为有价证券的到期日 Frequency为年付息次数 如果按年支付 frequency 1 按半年期支付 frequency 2 按季支付 frequency 4 Basis为日计数基准类型 0或省略为30 360 1为实际天数 实际天数 2为实际天数 360 3为实际天数 365 4为欧洲30 360 第一章公式与函数 2 统计函数统计工作表函数用于对数据区域进行统计分析 例如 统计工作表函数可以提供由一组给定值绘制出的直线的相关信息 如直线的斜率和y轴截据 或构成直线的实际点数值 1 AVEDEV用途 返回一组数据与其平均值的绝对偏差的平均值 该函数可以评测数据 例如学生的某科考试成绩 的离散度 语法 AVEDEV number1 number2 参数 Number1 number2 是用来计算绝对偏差平均值的一组参数 其个数可以在1 30个之间 实例 如果A1 79 A2 62 A3 45 A4 90 A5 25 则公式 AVEDEV A1 A5 返回20 16 第一章公式与函数 2 AVERAGE用途 计算所有参数的算术平均值 语法 AVERAGE number1 number2 参数 Number1 number2 是要计算平均值的1 30个参数 实例 如果A1 A5区域命名为分数 其中的数值分别为100 70 92 47和82 则公式 AVERAGE 分数 返回78 2 3 AVERAGEA用途 计算参数清单中数值的平均值 它与AVERAGE函数的区别在于不仅数字 而且文本和逻辑值 如TRUE和FALSE 也参与计算 语法 AVERAGEA value1 value2 参数 Value1 value2 为需要计算平均值的1至30个单元格 单元格区域或数值 实例 如果A1 76 A2 85 A3 TRUE 则公式 AVERAGEA A1 A3 返回54 即76 85 1 3 54 第一章公式与函数 数据库函数当要分析数据清单中的数值是否符合特定条件时 可以使用数据库工作表函数 例如 在一个包含销售信息的数据清单中 可以计算出所有销售数值大于1000且小于2500的行或记录总数 某些数据库和数据清单管理工作表函数的名称以字母 D 开头 这些寒暑 也称为Dfunctions 它们都有三个参数 database field和criteria 参数database 为工具表上包含数据清单的区域 在给定该参数是必须包含区域中作为列标志的行 参数field 为需要汇总的列的标志 参数criteria 为工作表上包含制定条件的区域 第一章公式与函数 1 DAVERAGE用途 返回数据库或数据清单中满足指定条件的列中数值的平均值 语法 DAVERAGE database field criteria 参数 Database构成列表或数据库的单元格区域 Field指定函数所使用的数据列 Criteria为一组包含给定条件的单元格区域 2 DCOUNT用途 返回数据库或数据清单的指定字段中 满足给定条件并且包含数字的单元格数目 语法 DCOUNT database field criteria 参数 Database构成列表或数据库的单元格区域 Field指定函数所使用的数据列 Criteria为一组包含给定条件的单元格区域 第一章公式与函数 3 DCOUNTA用途 返回数据库或数据清单指定字段中满足给定条件的非空单元格数目 语法 DCOUNTA database field criteria 参数 Database构成列表或数据库的单元格区域 Field指定函数所使用的数据列 Criteria为一组包含给定条件的单元格区域 第一章公式与函数 函数应用案例 一 用RATE函数计算某项投资的实际赢利在经济生活中 经常要评估当前某项投资的运作情况 或某个新企业的现状 例如某承包人建议你贷给他30000元 用作公共工程建设资金 并同意每年付给你9000元 共付五年 以此作为这笔贷款的最低回报 那么你如何去决策这笔投资 如何知道这项投资的回报率呢 对于这种周期性偿付或是一次偿付完的投资 用RATE函数可以很快地计算出实际的赢利 其语法形式为RATE nper pmt pv fv type guess 具体操作步骤如下 1 选取存放数据的单元格 并按上述相似的方法把此单元格指定为 百分数 的格式 2 插入函数RATE 打开 粘贴函数 对话框 3 在 粘贴函数 对话框中 在 Nper 中输入偿还周期5 年 在 Pmt 中输入7000 每年的回报额 在 Pv 中输入 30000 投资金额 即公式为 RATE 5 9000 30000 4 确定后计算结果为15 24 这就是本项投资的每年实际赢利 你可以根据这个值判断这个赢利是否满意 或是决定投资其它项目 或是重新谈判每年的回报 第一章公式与函数 二 返回内部收益率的函数 IRRIRR函数返回由数值代表的一组现金流的内部收益率 这些现金流不一定必须为均衡的 但作为年金 它们必须按固定的间隔发生 如按月或按年 内部收益率为投资的回收利率 其中包含定期支付 负值 和收入 正值 其语法形式为IRR values guess 其中values为数组或单元格的引用 包含用来计算内部收益率的数字 values必须包含至少一个正值和一个负值 以计算内部收益率 函数IRR根据数值的顺序来解释现金流的顺序 故应确定按需要的顺序输入了支付和收入的数值 如果数组或引用包含文本 逻辑值或空白单元格 这些数值将被忽略 guess为对函数IRR计算结果的估计值 excel使用迭代法计算函数IRR从guess开始 函数IRR不断修正收益率 直至结果的精度达到0 00001 如果函数IRR经过20次迭代 仍未找到结果 则返回错误值 NUM 在大多数情况下 并不需要为函数IRR的计算提供guess值 如果省略guess 假设它为0 1 10 如果函数IRR返回错误值 NUM 或结果没有靠近期望值 可以给guess换一个值再试一下 例如 如果要开办一家服装商店 预计投资为 110 000 并预期为今后五年的净收益为 15 000 21 000 28 000 36 000和 45 000 分别求出投资两年 四年以及五年后的内部收益率 第一章公式与函数 三 求区域中数据的频率分布FREQUENCY由于函数FREQUENCY返回一个数组 必须以数组公式的形式输入 语法形式为FREQUENCY data array bins array 其中Data array为一数组或对一组数值的引用 用来计算频率 如果data array中不包含任何数值 函数FREQUENCY返回零数组 Bins array为一数组或对数组区域的引用 设定对data array进行频率计算的分段点 如果bins array中不包含任何数值 函数FREQUENCY返回data array元素的数目 看起来FREQUENCY的用法蛮复杂的 但其用处很大 比如可以计算不同工资段的人员分布 公司员工的年龄分布 学生成绩的分布情况等 这里以具体示例说明其基本的用法 第一章公式与函数 以计算某公司的员工年龄分布情况为例说明 在工作表里列出了员工的年龄 这些年龄为28 26 31 21 44 33 22和37 并分别输入到单元格C4 C11 这一列年龄就是data array Bins array是另一列用来对年龄分组的区间值 在本例中 bins array是指C13 C16单元格 分别含有值25 30 35 和40 以数组形式输入函数FREQUENCY 就可以计算出年龄在25岁以下 26 30岁 31 35岁 36 40岁和40岁以上各区间中的数目 本例中选择了5个垂直相邻的单元格后 即以数组公式输入下面的公式 返回的数组中的元素个数比bins array 数组 中的元素个数多1 第五个数字1表示大于最高间隔 40 的数值 44 的个数 函数FREQUENCY忽略空白单元格和文本值 FREQUENCY C4 C11 C13 C16 等于 2 2 2 1 1 第二章Excel数据管理与分析 1 记录单的使用2 数据的排序2 1简单排序2 2多条件排序2 3数据的筛选2 4分类汇总报表 各章分目录 第二章Excel数据管理与分析 1 记录单的使用使用Excel数据管理功能时 用户不需特别命名即可直接把表看做数据库工作表 实现数据库功能 在工作表中每一列称为一个字段 它存放的是相同类型的数据 数据表的第一行为每一字段名字 它一般是文字值 表中每一行为一个记录 选定数据清单中的任一单元格 选择 数据 菜单中的 记录单 命令 打开 记录单 对话框 在该对话框顶端显示了数据清单所在的工作表名称 在对话框的左边 显示记录的字段名 在与字段名相对应右边框中显示当前记录单元格数据 单击 上一条 或 下一条 按钮 可以查看记录 第二章Excel数据管理与分析 1 增加记录单击 记录单 对话框中 新建 按钮 在对话框中出现一个空白记录 在对话框的右上方记录号位置显示 增加记录 在空白记录框中输入对应字段的数据 然后单击 关闭 按钮 2 修改记录在 记录单 对话框中单击 上一条 或 下一条 按钮 直接对每条记录进行修改 3 删除记录在 记录单 对话框中 找到需要删除的记录 单击 删除 按钮 4 查找记录在 记录单 对话框中单击 条件 按钮 输入条件表达式 按回车键或单击 下一条 按钮 单击 关闭 按钮 第二章Excel数据管理与分析 2 数据的排序在数据清单中 对某些列的数据可以用 数据 菜单中的 排序 命令重新组织行的顺序 排序时 Excel应遵循以下的原则 如果由某一列来排序 那么在该列上有完全相同项的排列将保持它们的原始次序 在排序数据中有空白单元格的行会被放置在排序数据清单的最后 隐藏行不会被移动 除非它们是分级显示的一部分 排序选项如选定的列 顺序和方向等 在最后一次排序后便会被保存下来 直到修改选定区域或列标记为止 第二章Excel数据管理与分析 2 1简单排序操作方法如下 单击需要进行排序的表格中某列中的任一数据单元格 单击 常用 工具栏上的 升序 按钮或 降序 按钮 即可对工作表数据依据本列数据进行默认状态的升序或降序的排列 第二章Excel数据管理与分析 2 2多条件排序通过 排序 对话框 设定多级排序条件 可对数据库进行多条件排序 操作方法是 选择排序范围 选择 数据 菜单中的 排序 命令 指定排序关键字 在 排序 对话框的 主关键字 框中 选定主要关键排序字段 在该框的右边 选定 递增 或 递减 的排序方式 同样在 次要关键字 及 第三关键字 框中 选定排序字段 在其右边 选定 递增 或 递减 的排序方式 选择排序选项 排序 单击 确定 按钮 第二章Excel数据管理与分析 2 3数据的筛选选择 数据 菜单中的 筛选 命令可以对清单中的指定数据进行查找和其他工作 1 使用自动筛选 因为筛选时并不重新计算自动分类汇总 因此在插入自动分类汇总之前 应先排序 在要筛选的数据的清单中选定单元格后 选择 数据 菜单中的 筛选 命令后弹出子菜单 选择其中的 自动筛选 命令 Excel便在数据清单中每个列标记的右边插入一个下拉式按钮 单击某个列相标记右边的下拉箭头 单击某项筛选条件 则满足条件的记录显示出来 其余的记录被隐藏 在筛选结果中 满足条件的记录的行号和刚才使用过的下拉式按钮以蓝色显示 如果要显示全部记录 可单击刚才使用过的下拉式按钮 选择 全部 如果要显示所有被隐藏的行 并移去 自动筛选 下拉箭头 则选择 数据 菜单中的 筛选 命令 在子菜单中再次选择 自动筛选 命令 第二章Excel数据管理与分析 2 高级筛选在某些情况下 查询条件比较复杂或必须经过计算才能进行有条件的查询 要用高级筛选 其方法主要是定义三个单元可知区域 一是定义查询的数据库区域 二是定义查询的条件区域 三是定义存放查找出满足条件和记录的区域 选择 数据 菜单的 筛选 命令 在弹出的下一级子菜单中选择 高级筛选 命令 打开 高级筛选 对话框 在 数据区域 中输入单元格区域 在 条件区域 中输入存放条件的单元格区域 在 复制到 框中输入存放结果的单元格区域 最多可设置三个条件 恢复数据库原状的方法是 打开数据菜单并选择筛选选项 在弹出的筛选子菜单中选择 全部显示 选项 数据库就恢复了 第二章Excel数据管理与分析 2 4分类汇总报表 先选定需汇总列 对数据清单进行排序 在要分类汇总的数据清单中 单击任一单元格 选择 数据 菜单中的 分类汇总 命令 打开 分类汇总 对话框 打开 分类字段 中的下拉列表框 从中单击需要用来分类汇总的数据列 打开 汇总方式 中的下拉列表框 从中选择所需用于计算分类汇总的函数 在 选定汇总项 列表框中 选定与需要对其汇总计算的数值对应的复选框 单击 确定 按钮 当分类汇总使用完后 可再选 分类汇总 命令 在打开的对话框中选择 全部删除 按钮 则复原工作表 第三章图表处理 1 建立图表2 修改图表的设置修改数值轴的刻度改变柱形图的宽度改变图表的视角3 建立数据透视表和数据透视图4 建立一张数据透视表5 由数据透视表创建数据透视图 各章分目录 第三章图表处理 Excel提供了功能强大切实用灵活的图表功能 利用此功能可以在工作表中创建复杂的图表 使工作表中本来枯燥乏味的数据形象化 1 建立图表对于建立图表 我们可以选择两种方式 一是 如果将图表用于补充工作数据并在工作表内显示 可以在工作表上建立内嵌图表 二是 若是要在工作簿的单独工作表上显示图表 则建立图表 内嵌图表和独立图表都被链接到建立它们的工作表数据上 当更新了工作表时 二者都被更新 当保存工作簿时 内嵌图表被保存在工作表中 内嵌图表是报告或其它文档的理想工具 而在报告或文档中 您想要在工作表数据上下文中显示图表 图表被自动插入到工作簿中 该工作簿位于图表所基于的工作表的左侧 图表适合于展示和训练目的 如投影仪和讲稿胶片 或用于美化书面信息 如报纸文章和广告 第三章图表处理 图表用 图表向导 命令或 按钮来建立 所谓 图表向导 是指一系列的对话框 它指导完成建立新图表或修改已存在图表设置所要求的所有步骤 通过使用 图表向导 可以很快地完成许多任务 否则将会花费很长时间 当使用 图表向导 建立图表时 可以指定工作表区域 选定图表类型和格式 以及指定绘制数据的方式 还可以增加图例 图表标题及每个坐标轴的标题 当修改一个已存在的图表时 可指定不同的工作区域并修改图表方向 即数据系列是在工作表上的行中还是列中 第三章图表处理 若要建立图表 使用 按钮或者 插入 菜单中的 图表 命令 例如 针对一个销售统计表建立一个产品分类的三维饼图 使用 图表向导 建立新图表的步骤如下 1 执行 插入 菜单中的 图表 命令 弹出图表向导 06年1月销售实际表 第三章图表处理 2 在 图表类型 中选择饼图 子图表类型中选择一种类型 3 点击 下一步 第三章图表处理 4 单击 数据区域 编辑框 然后在工作表上用鼠标拖拽选定作图所需的数据和标志区 5 返回完成图表源数据选择 点击 下一步 6 可以对图表的一些选项进行设置 完成后点击 下一步 第三章图表处理 7 选择图表的存放位置 然后点击 完成 即可 8 完成后效果 第三章图表处理 2 修改图表的设置修改数值轴的刻度有时 数值轴的值较大 数据后有很多0 看起来不美观 可以双击数值轴 或从快捷菜单中选择 坐标轴格式 刻度 在 显示单位 中选择合适的单位 第三章图表处理 改变柱形图的宽度柱形图中的柱的宽度是可以改变的 各柱之间的间隔也是可以修改的 双击数据点或数据系列 选中 选项 选项卡 第三章图表处理 改变图表的视角在多个数据系列的三维圆柱形图表中 经常会遇到这样的问题 前方的圆柱体挡住了后面的圆柱体 这时 可以改变图表的视角 以便于观察 1 鼠标直接拖动鼠标指向绘图区背景墙或基底的外角 会出现一个小提示符 角点 鼠标指向控制 光标变成十字光标 拖动光标 图表变成了线框的图像 第三章图表处理 2 对话框方法单击绘图区 图表区或背景墙 点击右键在快捷菜单中选择 设置三维视图格式 鼠标单击旋转按钮或直接输入仰角和转角 调整到满意为止即可 第三章图表处理 用类似的方法可以作出其他更加实用和美观的图表来 第三章图表处理 3 建立数据透视表和数据透视图数据透视表 一种交互的 交叉制表的Excel报表 用于对多种来源 包括Excel的外部数据 的数据 如数据库记录 进行汇总和分析 第三章图表处理 数据透视表是交互式报表 可快速合并和比较大量数据 您可旋转其行和列以看到源数据的不同汇总 而且可显示感兴趣区域的明细数据 何时应使用数据透视表如果要分析相关的汇总值 尤其是在要合计较大的数字清单并对每个数字进行多种比较时 可以使用数据透视表 在上面所述报表中 用户可以很清楚地看到单元格F3中第三季度高尔夫销售额是如何通过其他运动或季度的销售额或总销售额计算出来的 由于数据透视表是交互式的 因此 您可以更改数据的视图以查看更多明细数据或计算不同的汇总额 如计数或平均 第三章图表处理 我的数据是如何组织的在数据透视表中 源数据中的每列或字段都成为汇总多行信息的数据透视表字段 在上例中 运动 列成为 运动 字段 高尔夫的每条记录在单个高尔夫项中进行汇总 数据字段 如 求和项 销售额 提供要汇总的值 上述报表中的单元格F3包含的 求和项 销售额 值来自源数据中 运动 列包含 高尔夫 和 季度 列包含 第三季度 的每一行 第三章图表处理 建立一张数据透视表右图是一张数据清单 以此作为数据透视表的数据源 1 单击清单任一单元格2 选择 数据 数据透视表和数据透视图 第三章图表处理 3 选择建立数据透视表的数据区域4 进入步骤3 数据透视表显示位置 项 如果选 新建工作表 将显示在新建的工作表上 如选择 现有工作表 则如步骤3选择数据透视表的显示区域 第三章图表处理 5 单击 布局 将右边的字段按钮拖到左边的图上6 单击步骤之3图中 选项 按钮 可对数据透视表的格式和数据选项进行些设置 以满足要求 第三章图表处理 7 单击 完成 完成数据透视表的创建 在数据透视表中 可以很清楚地看到例子中每个销售员在不同时间内各种书的销售情况 以及总的销售量 第三章图表处理 由数据透视表创建数据透视图由数据透视表创建数据透视图是十分方便的 选中数据透视表 从快捷菜单中选择 数据透视图 命令 或从 数据透视表 工具栏中单击 图表向导 一幅数据透视图就做好了 第四章工具使用 1 宏1 1记录宏1 2运行宏1 3指定宏1 4编辑宏1 5加载宏2 VBA控件的使用3 控件与宏使用范例 各章分目录 第四章工具使用 1 宏宏是对重复性工作的一种简化它通过将记录的一系列操作过程保存在VisualBasic模块中 从而形成一个宏 这样执行这些操作只需执行一下这个宏即可 如果您对VisualBasic语言熟悉的话 还可以通过Office内部提供的VisualBasic编辑器来编辑宏 Tips 关于宏如果经常在MicrosoftExcel中重复某项任务 那么可以用宏自动执行该任务 宏是一系列命令和函数 存储于VisualBasic模块中 并且在需要执行该项任务时可随时运行 例如 如果经常在单元格中输入长文本字符串 则可以创建一个宏来将单元格格式设置为文本可自动换行 第四章工具使用 1 1记录宏建立宏最简便的方法是使用记录的方法 方法如下 1 将鼠标指向 工具 菜单中的 宏 命令 然后从子菜单中选择 录制新宏 命令屏幕弹出 录制新宏 对话框 2 在 宏名 框中键入宏的名称 首字符必须是字母 其他字符可以是字母 数字或下划线字符 宏名称中不需有空格 下划线字符也可以作为分词符 第四章工具使用 3 如果使用快捷键来运行宏 可以在 快捷键 框中键入一个字母 以后就可以用 Ctrl 字母 或 Ctrl Shift 字母 的方式来运行宏 字母可以是键盘上任意键但在快捷键中使用的字母不能是数字或特殊字符 4 在 保存在 列表框中值定存放宏的位置 如要使某个宏在使用Excel都有效 可将宏存放在XLStart文件夹的 个人宏工作簿 中 如果要将宏保存到新的工作簿中 可单击 新工作簿 项 如果要将宏保存到当前工作簿中 可单击 当前工作簿 项 5 如果要包含宏的说明可在 说明 框中键入相应的文字 Tips 当打开包含宏的工作表时 宏快捷键将使任何Excel默认快捷键无效 第四章工具使用 6 单击 确定 此时出现 停止录制 工具栏 该栏含有两个按钮 如果在录制宏时选中了某些单元格 则该宏在每次运行时都将选中这些单元格 因为宏记录的是对单元格的绝对引用 如果要让宏在选择单元格时不考虑活动单元格的位置 则必须将宏设置为单元格的相对引用 可单击 停止录制 工具栏 相对引用 按钮 Excel将按相对引用格式继续记录宏 直到退出或再次单击 相对引用 按钮为止 7 执行要记录的操作 8 单击 停止录制 工具栏 停止录制 按钮停止宏的录制 第四章工具使用 1 2运行宏在录制宏后 就可在Excel或VisualBasic编辑器中运行宏 1 打开包含宏的工作薄2 将鼠标指向 工具 菜单的 宏 命令 然后从子菜单选择 宏 命令 弹出 宏 对话框如图所示3 在 宏名 框中键入要运行的宏的名称 4 点击 执行 按纽如果要在宏运行完记录的全部操作前终止宏的执行 可按ESC 第四章工具使用 1 3指定宏可以为宏指定为快捷键 或者将宏指定为按钮或图形对象 1 为宏指定快捷键 工具 宏 宏 选项 中可设置快捷键2 将宏指定为图形对象或按钮控件可将宏指定给工作表中的某个图形对象或按钮控件 当单击图形对象或按钮控件时 相应的宏将自动执行 1 在工作表中右键单击图形对象或按钮控件 2 在快捷菜单中选择 指定宏 将弹出 指定宏 对话框 3 选择要运行的宏 或录制新宏 第四章工具使用 1 4编辑宏编辑宏主要使用VisualBasic编辑器1 将鼠标指向 工具 菜单中的 宏 命令 然后从子菜单选择 宏 命令 2 在 宏 对话框的 宏名 框中键入待编辑的宏的名称 3 单击 编辑 按钮 进入VisualBasic编辑器 可从编辑修改宏模块 如果要运行VisualBasic模块表中的宏 VisualBasic工具栏中的 运行 按钮 1 5加载宏加载宏是向EXCEL2000添加可选择命令和功能的补充程序 在使用之前必须将其安装到计算机并加载到EXCEL2000 加载宏文件 XLA缺省时安装到EXCEL2000所在的Library文件夹中 第四章工具使用 调入某个加载宏之后就可以在Excel中使用 并在相关菜单中添加相关的命令 如果要节省内存 可将不常用的加载宏卸载 加载宏卸载之后 与其相关的功能和命令将不能使用 但加载宏仍然保留在计算机中 可以再次加载 此外 还可以将自己的VisualBasic程序作为自定义加载宏使用 在Excel中调入已经安装的加载宏 可在 工具 菜单中选择 加载宏 命令 屏幕弹出 加载宏 对话框 选中待添加宏 或者去掉已加载宏的选择 卸载这一加载宏 第四章工具使用 2 VBA控件的使用VBA VisualBasicforApplication 是微软公司为用户在 内部提供的一种程序设计语言 用以建立 问题的通用 应用程序 在 使用控件选择 视图 菜单 工具栏 的子菜单 而后单击 控件工具箱 命令 即可打开控件工具栏控件工具栏由控件控制按钮 标准控件按钮和其他控件按钮三部分组成 第四章工具使用 使用控件的方法分两种 一种是添加控件后 利用属性按钮启动属性工具栏 然后添加相应控件的属性 通过设置控件的属性建立该控件与Excel表中的数据或其他对象的连接 从而实现利用控件控制数据和其他对象的目的 一种是添加控件后 利用查看代码按钮或双击该按钮进入VisualBasic编辑器来添加一定的程序语言代码 通过语言代码实现控制Excel表中的数据或其他对象的目的 第四章工具使用 3 控件与宏使用范例上图是 公司损益分析 表 在 营业费用 栏放置 数值调节钮 方便调节数值 在 降低成本百分比 栏添加 组合框 控件以便修改数值 为方便数据表间的切换添加 返回主表 按钮 利用录制一个宏实现 然后将宏与命令按钮连接 实现单击此按钮执行宏操作 返回 主表 的功能 下面介绍实现的方法 第四章工具使用 1 选择 控件工具箱 的 数值调节钮 控件 此时鼠标变成 形 在 营业费用 数值右侧单击完成控件添加 2 数值调节钮 选中状态下 选择 控件工具箱 的 属性 按钮 设定 数值调节钮 的调节范围属性 如图设置Max Min SmallChange Linkedcell的属性值 使 数据调节钮 与B5单元格建立连接 第四章工具使用 3 选择 控件工具箱 的 退出设计模式 按钮 此时调节 数值调节钮 可看到B5单元格的数值已经可以控制了 4 选择 控件工具箱 的 组合框 控件 在 降低成本百分比 的右侧添加该控件按钮 如图设置其属性 第四章工具使用 5 退出设计模式 完成组合框的设计 单击察看效果如右图所示 6 为添加命令按钮 事先录制一个简单的宏 名称为 Macro3 对于要切换至 主表 只需录制一步操作 选取 主表 选项卡按钮 切换至 主表 然后停止录制宏 第四章工具使用 7 切换至 损益分析 表 选择 控件工具箱 的 命令按钮 控件 添加一个命令按钮 在 属性 中将Caption属性设为 返回主表 8 选择 控件工具箱 的 察看代码 控件或双击 返回主表 按钮 进入VisualBasic编辑器 要用一个命令按钮运行一个宏很简单 只需在他的过程处添加要运行的宏名称 Macro3 即可 9 控件工具箱 退出设计模式 点击 返回主表 按钮 通过运行 Macro3 从而实现了界面的简单按钮切换 第五章综合使用案例 本章将通过几个Excel使用案例及相关知识点的介绍 以期能够帮助读者尽快掌握中文版Excel的操作方法与技巧 迅速应用到实际的工作学习中去 从而提高工作效率 第五章综合使用案例 1 如下为某段时间LCM综合情况表 希望根据表中数据建立如下图表以方便进行分析 第五章综合使用案例 用到知识点 折线图的制作折线图按照相同间隔显示数据的趋势 它具有以下子图表类型 折线图这种图表类型显示随时间或类别的变化趋势 在每个数据值处还可以显示标记 堆叠折线图这种图表类型显示各个值的分布随时间或类别的变化趋势 在每个数据值处还可以显示标记 百分比堆叠折线图这种图表类型以百分比方式显示各个值的分布随时间或类别的变化趋势 在每个数据值处还可以显示标记 三维折线图这是具有三维效果的折线图 第五章综合使用案例 图表的设置 包括 对图表标题 数据系列 分类名称 刻度线和坐标轴标题等的设置 生产计划 实绩图制作步骤 1 插入 图表 选择 折线图 数据点折线图 下一步 2 数据区域 选择D1 R3区域即如下图所示 注意 要将作为标题的日期行也选中 第五章综合使用案例 3 在 系列 选项卡 选定 系列一 与 系列二 的名称 即表中 计划 与 实绩 OQC 单元格 下一步 4 进入步骤三 图表选项 如图所示填好图表的标题 以及分类轴的标题 并在 数据标志 选项卡中选中 值 标记 使图表中可以显示数据值 5 点击 下一步 将图表作为其中的对象插入 点 完成 即可 第五章综合使用案例 6 如果感觉数值太长影响美观 可以改变数字显示的单位 双击图表中 数值轴 弹出 坐标轴格式 对话框 将显示单位设为 千 即可7 还可以设置数值轴的显示单位标签 直至满意为止 第五章综合使用案例 设备综合效率 图表的制作此图表与前图基本类似 需要注意的只是数值轴刻度的设置最小值为负值 第五章综合使用案例 前面介绍了折线图的制作 Excel提供了丰富的图表类型 而且每种图形下又有几种不同的子类型 对图表类型的正确选择是相当重要的 选择一个能最佳表现数据的图表类型 有助于更清楚的反映数据的差异和变化 从而更有效的反映数据 下面再介绍一下其他图表的类型及其特点 1 柱形图柱形图显示一段时间内数据的变化 或者显示不同项目之间的对比 柱形图具有下列子图表类型 簇状柱形图这种图表类型比较类别间的值 它还可以以三维效果显示 如以下图表所示 水平方向表示类别 垂直方向表示各类别的值 从而强调值随时间的变化 第五章综合使用案例 堆积柱形图这种图表类型显示各个项目与整体之间的关系 从而比较各类别的值在总和中的分布情况 它也可以以三维效果显示 百分比堆积柱形图这种图表类型以百分比形式比较各类别的值在总和中的分布情况 它也可以以三维效果显示 三维柱形图这种图表类型沿着两个数轴比较数据点 数据点 在图表中绘制的单个值 这些值由条形 柱形 折线 饼图或圆环图的扇面 圆点和其他被称为数据标志的图形表示 相同颜色的数据标志组成一个数据系列 例如 在下面的三维图表中 您可以比较欧洲的四个销售部门和其他两个地区的业绩 第五章综合使用案例 条形图条形图显示各个项目之间的对比 它具有以下子图表类型 簇状条形图这种图表类型比较类别间的值 它也可以以三维效果显示 在下面的图表中 水平方向表示类别 垂直方向表示各类别的值 从而关注值的对比情况 堆积条形图这种图表类型显示各个项目与整体之间的关系 它还可以以三维效果显示 百分比堆积条形图这种图表类型以百分比形式比较各类别的值在总和中的分布情况 它也可以以三维效果显示 第五章综合使用案例 饼图饼图显示组成数据系列 数据系列 在图表中绘制的相关数据点 这些数据源自数据表的行或列 图表中的每个数据系列具有唯一的颜色或图案并且在图表的图例中表示 可以在图表中绘制一个或多个数据系列 饼图只有一个数据系列 的项目在项目总和中所占的比例 饼图通常只显示一个数据系列 当您希望强调数据中的某个重要元素时可以采用饼图 饼图具有下列子图表类型 饼图这种图表类型显示各个值在总和中的分布情况 它也可以以三维效果显示 如以右图表所示 分离型饼图这种图表类型显示各个值在总和中的分布情况 同时强调各个值的重要性 它也可以以三维效果显示 第五章综合使用案例 复合饼图这是一种将用户定义的值提取出来并显示在另一个饼图中的饼图 例如 为了看清楚细小的扇区 您可以将它们组合成一个项目 然后在主图表旁的小型饼图或条形图中将该项目的各个成员分别显示出来 复合条饼图这是一种将用户定义的数据提取处理出来并显示在另一个堆叠条形图中的饼图 第五章综合使用案例 XY散点图XY散点图显示若干数据系列 数据系列 在图表中绘制的相关数据点 这些数据源自数据表的行或列 图表中的每个数据系列具有唯一的颜色或图案并且在图表的图例中表示 可以在图表中绘制一个或多个数据系列 饼图只有一个数据系列 中各数值之间的关系 或者将两组数绘制为xy坐标的一个系列 散点图通常用于科学数据 具有下列子图表类型 散点图这种图表类型比较成对的数值 例如 以下散点图显示的是两组数据的不规则间隔 簇 在为绘制散点图而准备数据时 将X值放在一行或一列 并在相邻的行或列中输入对应的y值 第五章综合使用案例 折线散点图显示这种图表时 数据点之间可以显示也可以不显示直的或平滑的连接线 显示连接线时可以显示标记 也可以不显示标记 面积图面积图强调大小随时间发生的变化 它具有下列子图表类型 面积图这种图表类型显示数值随时间或类别的变化趋势 它也可以以三维效果显示 通过显示已绘制的值的总和 面积图还可以显示部分与整体的关系 在下面的示例中 面积图强调了北京地区增长的销售额 并显示了每个地区对销售总额的贡献 第五章综合使用案例 堆叠面积图这种图表类型显示各个值的分布随时间或类别的变化趋势 它也可以以三维效果显示 百分比堆叠面积图这种图表类型以百分比方式显示各个值的分布随时间或类别的变化趋势 它也可以以三维效果显示 圆环图像饼图一样 圆环图显示部分和整体之间的关系 但是它可以包含多个数据系列 数据系列 在图表中绘制的相关数据点 这些数据源自数据表的行或列 图表中的每个数据系列具有唯一的颜色或图案并且在图表的图例中表示 可以在图表中绘制一个或多个数据系列 饼图只有一个数据系列 它具有下列子图表类型 圆环图圆环图的每个环分别代表一个数据系列 例如在右图中 内环代表汽油税的收入 外环代表财产税的收入 分离型圆环图分离型圆环图与分离型饼图类似 但可以包含多个数据系列 第五章综合使用案例 雷达图雷达图比较大量数据系列 数据系列 在图表中