Excel进阶教程完美版

1、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 公式

2、的基本概念 公式和公式语法 公式是对单元格中数值进行计算的等式 Excel中的公式是按照特定顺序进行数值计算的 这一特定顺序即为语法 Excel中的公式遵守一个特定的语法 最前面是等号 后面是参与计算的元素和运算符 默认状态下 Excel是从等号开始 从左到右计算公式 可以通过修改公式语法来控制计算的顺序 如可以通过添加括号来改变语法 第一章公式与函数 1 1 2单元格引用公式的灵活性是通过单元格的引用实现的 单元格的引用是指将公式所使用的单元格与公式挂在一起 公式可以自动调用单元格的值进行运算 单元格的引用分绝对引用和相对引用 绝对引用是指公式所引用的单元格是固定不变的 即 无论将它剪切或复制到哪里 都将因用同一个单元格 绝对引用使用 符号 相对引用将公式剪切或复制到其他单元格 引用会根据当前行和列的内容自动改变 相对引用只需直接输入单元格的名称 Excel默认为相对引用 例如 B5单元格输入 E 4 2 则其值为1672 将该公式复制到D5 其值也是1672 如果在C5中输入 C 4 其值将为36 复制到D5 公式变为D 4 值也变为0 Tips 可以使用 F4 键实现相对引用和绝

3、对引用的切换 第一章公式与函数 1 1 3公式计算和循环引用公式的计算可设置为 自动重算 当公式引用的单元格中数据改变时 公式自动重新计算 除模拟运算表外 自动重算 手工重算 当公式引用的单元格中数据改变时 公式不会自动重新计算 只有用户双击含有该公式的单元格之后 才重新计算 保存前自动重算 在手工重算方式下才有效 但保存工作表时 工作表中公式才自动重新计算 设置方法 工具 选项 进入 选项 对话框 选择 重新计算 窗体 第一章公式与函数 循环引用 公式计算的另一个问题是循环引用 自动重算方式下某个公式直接或间接引用了该公式的单元格时 就会产生公式调用自己单元格中数据的情况 这时共识会反复调用进行运算 这成为公式的循环引用 出现循环引用时 Excel将在一定的循环计算次数之后 或在两次计算结果之间的差值小于某个误差值时 停止迭代 具体设置可在 重新计算 框中设置 Tips 可以引用同一工作簿不同工作表的单元格 不同工作簿的单元格 如 在Book1的Sheet1中引用Sheet2中的A1单元格应表示为Sheet2 A1 若要引用Book2的Sheet2中的A1单元格 应表示为 Book2

4、 Sheet2 A1 第一章公式与函数 1 1 4合并计算在要对具有相似表结构的多个表进行类似数据的汇总计算时 可以使用Excel提供的 合并计算 功能 简单实例 要对Sheet1 一月份 Sheet2 二月份 Sheet3 三月份 的对应单元格C4 C8的对应值进行求和 生成结果保存在Sheet4 一季度汇总 用合并计算生成一季度的汇总任务 第一章公式与函数 步骤 1 选择 一季度汇总 中C4单元格位置 结果将保存在这里 选择主菜单 数据 中 合并计算 命令 2 选择 函数 组合框中 求和 项 在选择 引用位置 的 选取范围 按钮 然后选择 一月份 表的C4 C8单元格 按 返回 按钮 选择 添加 按钮 将其添加到引用为之列表中 然后依次将要进行合并计算的 二月份 和 三月份 表的C4 C8单元格引用位置添加到引用列表中 第一章公式与函数 3 如果设定参加合并计算的引用位置有错误 可在引用位置列表中 先选择该项再点 删除 按钮 删除该引用项 最后选择 确定 按钮 完成合并计算 注意 合并计算不同于表间函数运算 当他引用的数据源中数据发生改变后 合并计算的结果并不会随之改变 如有这种情

5、况发生就需要重新进行合并计算 Tips 对比较复杂的统计汇总表进行汇总时 对于经常修改数据的表 推荐用多表间函数和公式运算来完成 他们会在数据源发生改变后自动更新数据 不再需要重新运算 第一章公式与函数 1 2函数函数是Excel实现它强大的计算功能的有力工具 函数实际上就是一种公式 Excel将用户经常用到的公式和一些特殊的计算应用作为内置的公式来提供给用户 Excel提供了超过200个的函数用于不同的场合 为用户的各种数据计算 处理和分析提供了强大的功能 一个简单的例子 比如 在计算 合计 值时 要在某单元格输入 C3 C4 C5 C6 当记录很多时 输入公式将成为一个很麻烦的事情 如果使用函数 只需输入 SUM C3 C6 即可 提高了工作效率 第一章公式与函数 1 2 1输入带函数的公式1 函数的格式 符号 表示是公式 函数 函数名称 表示进行什么操作 是英文单词的缩写 括号 包含函数的参数 及函数的输入值 参数 要在函数中使用的值和单元格 2 函数的输入 键盘输入 对用户要求较高 必须知道函数的确切格式 使用函数向导 1 单击要输入函数的单元 2 单击 插入 函数 弹出对话框

6、3 选择函数类别4 在 选择函数 找到所需函数5 确定 弹出另一对话框 步骤 第一章公式与函数 这个对话框中 上面是参数文本框 可输入参数值或单元格 或单击文本框右边的按钮 切入工作表 用鼠标选择单元格 设定好函数参数后 单击 确定 按钮 第一章公式与函数 1 2 2函数和公式的深入使用1 工作表间的函数和公式运算在合并计算中 提到过可以利用函数来完成多表数据计算 仍然以此为例 1 在季度汇总表 中 选择C4单元格作为函数的添加位置 2 选择 插入 函数 选择SUM函数 弹出函数向导3 点击按钮 选择 一月份 表中C4单元格 点击按钮返回 第一章公式与函数 4 仿照前面步骤 分别指定Number2和Number3项单元格计算位置 结果如右图所示 5 点 确定 求和结果如下图所示 第一章公式与函数 6 在C4单元格右下角位置拖动鼠标的 拖动句柄 拖至C8单元格处释放 复制公式如下图所示 2 工作簿间的函数和公式运算例如需要进行一季度工作簿和二季度工作簿的求和 以完成半年度的汇总 第一章公式与函数 1 打开一季度工作簿和二季度工作簿 新建半年度工作簿 选择 窗口 重排窗口 垂直并排 结果如

7、下图所示 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是证券的成交日

8、 即发行日之后证券卖给购买者的日期 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为购入资产

9、的日期 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 参数 Settleme

10、nt是证券的成交日 即发行日之后证券卖给购买者的日期 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用途 计算所有

《Excel进阶教程完美版》由会员ali****an分享，可在线阅读，更多相关《Excel进阶教程完美版》请在金锄头文库上搜索。