Excel进阶教程完美版-（最新版）

《Excel进阶教程完美版-（最新版）》由会员分享，可在线阅读，更多相关《Excel进阶教程完美版-（最新版）（127页珍藏版）》请在金锄头文库上搜索。

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、.2 函数 1.2.1 输入带函数的公式 1.2.2 函数和公式的深入使用 1.3 财务函数 财务函数 统计函数 数据库函数 函数应用案例,各章分目录,第一章 公式与函数,1.1 公式： 1.1.1 输入公式和编辑公式 Excel中公式的格式包括： 1.“=”符：表示用户输入的内容是公式而不是数据。 2. 操作符：如加、减、乘、除、乘方等 3.引用的单元：参加运算的单元格的名称，如A1、C3等 Tips:可以直接输入单元格的名称或用鼠标单击 要选用的单元格。 例如：,在F4中输入公式“=D40”即可自动计算F4的值。,Tips:公式的基本概念 公式和公式语法：公式是对单元格中数值进行计算的等式

3、。 Excel中的公式是按照特定顺序进行数值计算的，这一特定顺序即为语法。 Excel中的公式遵守一个特定的语法：最前面是等号（=），后面是参与计算的元素和运算符。 默认状态下，Excel是从等号开始，从左到右计算公式，可以通过修改公式语法来控制计算的顺序。如可以通过添加括号来改变语法。,第一章 公式与函数,1.1.2 单元格引用 公式的灵活性是通过单元格的引用实现的。单元格的引用是指将公式所使用的单元格与公式挂在一起，公式可以自动调用单元格的值进行运算。 单元格的引用分绝对引用和相对引用。 绝对引用是指公式所引用的单元格是固定不变的。即，无论将它剪切或复制到哪里，都将因用同一个单元格。绝对引

4、用使用$符号。 相对引用将公式剪切或复制到其他单元格，引用会根据当前行和列的内容自动改变。相对引用只需直接输入单元格的名称，Excel默认为相对引用。 例如：,B5单元格输入=$E$4*2，则其值为1672，将该公式复制到D5,其值也是1672。,如果在C5中输入=C$4,其值将为36，复制到D5,公式变为D$4,值也变为0。,Tips: 可以使用F4键实现相对引用和绝对引用的切换,第一章 公式与函数,1.1.3 公式计算和循环引用 公式的计算可设置为： 自动重算：当公式引用的单元格中数据改变时，公式自动重新计算。 除模拟运算表外，自动重算。 手工重算：当公式引用的单元格中数据改变时，公式不会

5、自动重新计算，只有用户双击含有该公式的单元格之后，才重新计算。 保存前自动重算：在手工重算方式下才有效，但保存工作表时，工作表中公式才自动重新计算。 设置方法：工具-选项， 进入选项对话框。选择 重新计算窗体。,第一章 公式与函数,循环引用：公式计算的另一个问题是循环引用。 自动重算方式下某个公式直接或间接引用了该公式的单元格时，就会产生公式调用自己单元格中数据的情况，这时共识会反复调用进行运算，这成为公式的循环引用。 出现循环引用时，Excel将在一定的循环计算次数之后，或在两次计算结果之间的差值小于某个误差值时，停止迭代。具体设置可在重新计算框中设置。,Tips: 可以引用同一工作簿不同工

6、作表的单元格、不同工作簿的单元格。如：在Book1的Sheet1中引用Sheet2中的A1单元格应表示为Sheet2!A1，若要引用Book2的Sheet2中的A1单元格，应表示为Book2Sheet2!A1,第一章 公式与函数,1.1.4 合并计算 在要对具有相似表结构的多个表进行类似数据的汇总计算时，可以使用Excel提供的“合并计算”功能。 简单实例：要对Sheet1(一月份)、 Sheet2(二月份)、 Sheet3(三月份)的对应单元格C4:C8的对应值进行求和，生成结果保存在Sheet4(一季度汇总)，用合并计算生成一季度的汇总任务。,第一章 公式与函数,步骤： 1.选择“一季度汇

7、总”中C4单元格位置，结果将保存在这里。选择主菜单数据中合并计算命令。 2.选择函数组合框中求和项，在选择引用位置的选取范围按钮，然后选择“一月份”表的C4:C8单元格，按返回按钮，选择添加按钮，将其添加到引用为之列表中，然后依次将要进行合并计算的“二月份”和“三月份”表的C4:C8单元格引用位置添加到引用列表中。,第一章 公式与函数,3.如果设定参加合并计算的引用位置有错误，可在引用位置列表中，先选择该项再点删除按钮，删除该引用项，最后选择确定按钮，完成合并计算。,注意： 合并计算不同于表间函数运算，当他引用的数据源中数据发生改变后，合并计算的结果并不会随之改变，如有这种情况发生就需要重新进

8、行合并计算。,Tips: 对比较复杂的统计汇总表进行汇总时，对于经常修改数据的表，推荐用多表间函数和公式运算来完成，他们会在数据源发生改变后自动更新数据，不再需要重新运算。,第一章 公式与函数,1.2 函数 函数是Excel实现它强大的计算功能的有力工具。 函数实际上就是一种公式，Excel将用户经常用到的公式和一些特殊的计算应用作为内置的公式来提供给用户。 Excel提供了超过200个的函数用于不同的场合，为用户的各种数据计算、处理和分析提供了强大的功能。,一个简单的例子： 比如，在计算“合计”值时，要在某单元格输入=C3+C4+C5+C6,当记录很多时，输入公式将成为一个很麻烦的事情。如果

9、使用函数，只需输入=SUM(C3:C6)即可。提高了工作效率。,第一章 公式与函数,1.2.1 输入带函数的公式 1.函数的格式： “=”符号：表示是公式、函数。 函数名称：表示进行什么操作，是英文单词的缩写。 括号()：包含函数的参数，及函数的输入值。 参数：要在函数中使用的值和单元格。 2.函数的输入： 键盘输入：对用户要求较高，必须知道函数的确切格式。 使用函数向导： 1.单击要输入函数的单元。 2.单击插入-函数弹出对话框 3.选择函数类别 4.在选择函数找到所需函数 5.确定，弹出另一对话框。,步骤：,第一章 公式与函数,这个对话框中，上面是参数文本框，可输入参数值或单元格。或单击文

10、本框右边的按钮，切入工作表，用鼠标选择单元格。,设定好函数参数后，单击确定按钮。,第一章 公式与函数,1.2.2 函数和公式的深入使用 1. 工作表间的函数和公式运算 在合并计算中，提到过可以利用函数来完成多表数据计算。仍然以此为例： 1.“在季度汇总表”中，选择C4单元格作为函数的添加位置。 2.选择插入-函数，选择SUM函数，弹出函数向导 3.点击 按钮，选择“一月份”表中C4单元格，点击 按钮返回,第一章 公式与函数,4.仿照前面步骤，分别指定Number2和Number3项单元格计算位置。 结果如右 图所示： 5.点确定 求和结果如 下图所示：,第一章 公式与函数,6.在C4单元格右下

11、角位置拖动鼠标的“拖动句柄”，拖至C8单元格处释放，复制公式如下图所示： 2.工作簿间的函数和公式运算 例如需要进行一季度工作簿和二季度工作簿的求和，以完成半年度的汇总。,第一章 公式与函数,1.打开一季度工作簿和二季度工作簿，新建半年度工作簿，选择窗口-重排窗口-垂直并排，结果如下图所示： 2.选择半年度汇总表C4单元格，使用前面介绍的方法启动函数向导，选择SUM函数，既可利用向导选择要计算的单元格引用的位置。,第一章 公式与函数,设置结果如下图所示： 3.最后可得半年度 销量汇总，结果 如右下图所示：,第一章 公式与函数,1.3 财务函数 财务函数可以进行一般的财务计算，如确定贷款的支付额

12、、投资的未来值或净现值，以及债券或息票的价值。 1.财务函数中常见的参数： 未来值(fv):在所有付款发生后的投资或贷款的价值 期间数(nper):投资的总支付期间数 付款(pmt):对于一项投资或贷款的定期支付数额 现值(pv):在投资初期的投资或贷款的价值。例如，贷款的现值为所借入的本金数额。 利率(rate):投资或贷款的利率或贴现率 类型(type):付款期间内进行支付的间隔，如在月初或月末,第一章 公式与函数,1.ACCRINT 用途：返回定期付息有价证券的应计利息。 语法：ACCRINT(issue，first_interest， settlement，rate，par，frequ

13、ency， basis) 参数：Issue 为有价证券的发行日，First_interest 是证券的起息日，Settlement 是证券的成交日(即发行日之后证券卖给购买者的日期)，Rate 为有价证券的年息票利率，Par 为有价证券的票面价值(如果省略par，函数ACCRINT 将par 看作$1000)，Frequency 为年付息次数(如果按年支付，frequency = 1；按半年期支付，frequency = 2；按季支付，frequency = 4)。 2.ACCRINTM 用途：返回到期一次性付息有价证券的应计利息。 语法：ACCRINTM(issue，maturity，rat

14、e， 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_purcha

15、sed 为购入资产的日期，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

16、为购入资产的日期，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.统计函数 统计工作表函数用于对数据区域进行统计分析。例如，统计