[经济学]第2讲_excel财务应用基础.ppt

1、主讲：刘 会 齐 liuhq-,第2讲 Excel财务应用基础(二),第2讲 Excel财务应用基础(二),一、excel公式 二、excel函数 三、数据分析工具,一、excel公式,1、公式概念 2、运算符 3、公式编辑 4、输入数组公式 5、使用名称建立公式 6、公式的显示与隐藏 7、公式审核及出错检查,1、公式概念,Excel的公式由运算符、数值、字符串、变量和函数组成。 公式必须以等号“=”开头，在等号的后面可以跟数值、运算符、变量或函数，在公式中还可以使用括号。 输入：单元格输入公式或者编辑栏输入公式,2、运算符,在公式中可以使用运算符 Excel中的运算符如下表,续,3、公式编辑

2、,修改公式 复制或移动公式,单击公式编辑栏 选择单元格，按【F2】功能键,利用工具栏上的复制、剪切、粘贴按钮 利用【Ctrl+C】、【Ctrl+X】、【Ctrl+V】组合键操作 利用鼠标拖动填充柄复制 复制公式的过程中需要注意公式中对单元格具有不同的引用方式 注意选择性粘贴,4、输入数组公式,组合键 编辑数组公式 删除数组公式 数组公式的应用,选取输入公式单元格或单元格区域 输入计算公式 按 【Ctrl+Shift+Enter】组合键,参见演示文件,适用于有规律性批量数据输入,5、使用名称建立公式,利用名称框定义名称 利用菜单定义名称插入/名称/定义,第一个字符必须是字母、下划线、或汉字 不能

3、与单元格引用相同 不区分大小写,直接输入公式 利用粘贴名称方法,命名的方法 单元格名称的命名规定 使用名称建立公式 为已有的公式套用名称,参见演示文件,6、公式的显示与隐藏,使用菜单命令 工具/选项/视图 利用快捷键切换 按【Ctrl+】组合键,7、公式审核及出错检查,（1）追踪引用单元格 工具/公式审核/追踪引用单元格 工具/公式审核/取消所有追踪箭头 （2）追踪从属单元格 工具/公式审核/追踪从属单元格 工具/公式审核/取消所有追踪箭头 （3）公式审核工具栏 工具/公式审核/显示“公式审核”工具栏,二、函数,函数的简介 常用工作表函数 逻辑函数 数学和三角函数 日期函数 查找函数 财务函数

4、,1、函数简介,函数的概念,函数是能够完成特定功能的程序。在Excel中，它是系统预定义的一些公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算,然后把计算的结果存放在某个单元格中。 在大多数情况下，函数的计算结果是数值。当然，它也可以返回文本、引用、逻辑值、数组或工作表的信息,Excel函数分类：,函数的语法：函数名(参数1, 参数2, 参数3,) 注意： 函数名与其后的括号“（”之间不能有空格。 当有多个参数时，参数之间要用逗号“，”分隔。 参数部分总长度不能超过1024个字符。 参数可以是数值、文本、逻辑值、单元格地址或单元格区域地址，也可以是各种表达式或函数。 函数中的“，

5、”、“”等都是半角字符，而非全角的中文字符。,函数调用 单一函数的调用方法 直接输入函数 利用菜单命令或工具按钮调用函数 函数调用向导 嵌套函数的输入方法 Excel的帮助系统,2、常用函数,（1）条件函数IF （2）求和函数 （3）平均值函数 （4）计数函数 （5）求最大值、最小值函数,（1）条件函数IF,格式：IF(条件, 表达式1, 表达式2)。 功能：当条件成立时，计算出表达式1的值；当条件不成立时，计算出表达式2的值 IF函数的嵌套调用,参见演示文件,（2）求和函数,无条件求和SUM函数 条件求和SUMIF函数 SUMPRODUCT函数,续,1）求和函数,自动求和按钮,图示工作表中的

6、所有汇总数据可用自动求和按钮计算出来！,2）SUM函数,格式：SUM(x1, x2,x30)， x1，x2，x30是需要求和的参数 ，可以是数据或单元格区域 功能：无条件求和SUM函数，计算所选取的单元格区域中所有数值的和。,3）条件求和函数SUMIF,格式：SUMIF (range, criteria, sum_range) range是用于条件判断的单元格区域， criteria条件，其形式可以为数字、表达式或文本 sum_range是需求和的实际单元格。只有当range中的相应单元格满足条件时，才对sum_range中的单元格求和。若省略sum_range，则直接对range中的单元格求

7、和。 功能 ：对range单元格区域中的数据进行Criteria条件检查，然后对满足条件行的sum_range同行进行求和。,续,某商场的销售记录如图所示。如果统计各类商品的总销售额。 彩电的销售总额：,4）SUMPRODUCT函数,格式：SUMPRODUCT（ARRAY1，ARRAY2，.） 其中：array为1-30个数组。 功能：在给定的几组数组中将数组间对应的元素相乘，并返回乘积之和。 注意：参数数组必须有相同的维数，否则出错。,求和函数使用演示,（3）平均值函数,格式：AVERAGE(n1，n2，n30) AVERAGEA(n1，n2，n30) 其中，n1，n2，n30是要计算平均值

8、的参数，该函数最多允许有30个参数。参数可以是数字，或者是涉及数字的名称、数组或引用 功能：两函数都是求参数的平均值。AverAge不对文本和逻辑值类数据进行计算；AverageA要对文本和逻辑函数进行平均值计算：文本被视为0，逻辑值true被视为1，false被视为0，空文本（）也作为0计算,AVERAGE函数与AVERAGEA函数的区别 ：,（4）计数函数,格式： COUNT(v1, v2, ) COUNTA(v1, v2, ) COUNTBLANK (range) COUNTIF (range, criteria) 功能： Count 统计数字的个数 CountA统计数字和文本的个数 C

9、ountBlank统计空白单元格的个数 Countif统计满足条件Criteria的单元格个数,(5)求最大值、最小值函数,格式： MAX(number1,number2,.) MIN(number1,number2,.) 其中：number1, number2, .是要从中找出最大值或最小值的 1 到 30 个数字参数，也可以是单元格或单元格区域的引用。 功能：MAX函数返回一组值中的最大值，MIN函数返回一组值中的最小值,3、 逻辑函数,1）比较运算 比较运算就是人们常说的比较式，又称关系运算。比较运算只有两种不同的结果，要么“正确”，要么“错误”，不可能有第三种结果。 2）逻辑运算 AN

10、D(l1, l2, ) OR(l1, l2, ) NOT(logical) TRUE( ) FALSE( ) 其中， l1, l2是关系式或逻辑值。,某单位有3000名职工，要按其工资缴纳个人收入所得税，税率与工资的关系如表所示。该单位的职工表如下所示，用IF函数求出各职工的税率！,在E4中输入编辑栏中的公式！,4、 数学和三角函数,Excel提供了许多数学和三角函数，它们能够完成大多数数学和三角运算，这些函数可以在公式中直接引用，然后将公式的计算结果返回到输入公式的单元格中。 常见的数学函数：MOD 、TRUNC 、ABS 、SQRT 、SIN 、ASIN(n1)，ACOS(n1)，ATAN

11、(n2)、EXP(n)、POWER(x, n)、LN(n)、FACT(n)、LOG(n, base)、MINVERSE(array)、MMULT(array1, array2) ,5、日期及时间函数,Excel处理日期的方式:Excel将日期存储为一系列连续的序列数，将时间存储为小数。 NOW( ) ：返回当前系统日期和时间 TODAY( )：返回当前系统日期 YEAR(serial_number)：返回日期的年份值 MONTH(serial_number)：返回日期的月份值 DAY(serial_number)：返回月份中第几天的值 WEEKDAY(serial_number, return

12、_type)：返回一周中第几天的值，结果1-7之间的数字，周日是第1天，周一是第2天，。 HOUR（serial_number）：返回小时数值 INUTE（serial_number）：返回分钟数值 SECOND(serial_number)：返回秒数值 DATE(year, month, day)：返回特定日期序列 DATEDIF(start_date, end_date, unit) :返回天数 NETWORKDAYS(start_date, end_date, holidays):返回完整的工作日数据,6、查找函数,LOOKUP(value, r1, r2) HLOOKUP(value,

13、 table, n, range_lookup) VLOOKUP(lookup_value, table_array,col_index_num, range_lookup) MATCH(lookup_value,lookup_array,match_type),参见演示文件,MATCH( lookup_value, lookup_array, match_type) Lookup_value:为需要在数据表中查找的数值，可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。 Lookup_array:可能包含所要查找的数值的连续单元格区域。Lookup_array 应为数组或数

14、组引用。 Match_type：为数字 -1、0 或 1。Match-type 指明 Microsoft Excel 如何在 lookup_array 中查找 lookup_value。 如果 match_type 为 1，函数 MATCH 查找小于或等于 lookup_value 的最大数值。Lookup_array 必须按升序排列：.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE。 如果 match_type 为 0，函数 MATCH 查找等于 lookup_value 的第一个数值。Lookup_array 可以按任何顺序排列。 如果 match_type 为 -1，函数 M

15、ATCH 查找大于或等于 lookup_value 的最小数值。Lookup_array 必须按降序排列：TRUE、FALSE、Z-A、.、2、1、0、-1、-2、.，等等。 如果省略 match_type，则假设为 1。,7、引用函数,（1）Index（array, row_num, column_num） 返回数组中指定单元格的数值 （2） ADDRESS(row_num,col_num,abs_num,a1, sheet_text) 按照给定的行列建立文本类型的单元格地址 （3）INDIRECT（ref_text, a1） INDIRECT函数返回由文字串指定的引用。,INDIRECT

16、：返回由文本字符串指定的引用。此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身，请使用函数 INDIRECT。 语法：INDIRECT(ref_text,a1) Ref_text：为对单元格的引用，此单元格可以包含 A1-样式的引用、R1C1-样式的引用、定义为引用的名称或对文本字符串单元格的引用。如果 ref_text 不是合法的单元格的引用，函数 INDIRECT 返回错误值 #REF!。如果 ref_text 是对另一个工作簿的引用（外部引用），则那个工作簿必须被打开。如果源工作簿没有打开，函数 INDIRECT 返回错误值 #REF!。 A1：为一

17、逻辑值，指明包含在单元格 ref_text 中的引用的类型。如果 a1 为 TRUE 或省略，ref_text 被解释为 A1-样式的引用。如果 a1 为 FALSE，ref_text 被解释为 R1C1-样式的引用。,8、矩阵函数,（1）TRANSPOSE函数：求矩阵的转置矩阵。 （2）MINVERSE函数：返回矩阵的逆矩阵。 （3）MMULT函数：返回两数组的矩阵乘积。,9、财务函数,Excel提供了许多有关财务、投资、偿还、利息及折旧方面的函数，在工作表中运用这些函数可以较松地完成相关的财务运算，或者对其他财务管理软件的运算输出数据进行验证。 折旧函数 投资函数 计算偿还率的函数,（1）

18、折旧函数 Excel折旧函数有DB、DDB、SLN、 SYD 及VDB。运用这5个折旧函数可以确定指定时期内资产的折旧值。 （2）投资函数 Excel投资分析方面的函数，这些函数使用的参数大致相同，意义相近 ，如下表所示。 PMT函数返回的支付款项包括本金和利息， PV函数可以计算投资的现值。 FV函数计算投资在将来某个日期的价值，它可以计算出投资的一次性偿还金额，也可以计算出一系列数额相等的分期偿还金额。 XNPV函数计算一组现金流的净现值，这些现金流不一定定期发生 NPV(rate, value1, value2, .) IPMT(rate, per, nper, pv, fv, type

19、) PPMT(rate, per, nper, pv, fv, type) NPER(rate, PMT, pv, fv, type) （3）计算偿还率的函数 RATE函数用于计算投资的各期利率。可以计算连续分期等额投资的偿还率，也可以计算一次性偿还的投资利率 IRR函数计算由数值代表的一组现金流的内部收益率。内部收益率是指投资偿还的固有率，它是引起投资的净现值等于零的比率,10、数据库函数,数据库函数用于对存储在数据库或数据清单中的数据进行统计分析。 常见DAVERAGE、DSUM、DCOUNT、DMAX、DMIN、DGET等函数,三、数据分析工具,（一）模拟运算 （二）单变量求解 （三）规

20、划求解 （四）方案管理 （五）数据分析工具库,（一）模拟运算,模拟运算是指通过模拟运算测试由一组替换值代替公式中的变量时对公式运算结果的影响。 Excel主要可以构建两种模拟运算：单变量模拟运算和双变量模拟运算。,1、单变量模拟运算,（1）单变量模拟运算含义 （2）单变量模拟运算步骤,（1）单变量模拟运算含义,用于测试一个输入变量的不同变化值对公式运算结果的影响。即使用同一公式对单元格区域依次进行求解，再将这些结果依次输入到相应的单元格中。,（2）单变量模拟运算步骤,续,设计模拟运算表结构； 在第一个运算单元格中输入运算公式； 选取包括公式和需要进行模拟运算的单元格区域在内的单元格区域； 使用

21、excel模拟运算功能（数据/模拟运算表），设置弹出的对话框即可。,计算不同利率下的月还款额：,参见演示文件,Pmt函数，基于固定利率及等额分期付款方式，返回贷款的每期付款额。 格式：PMT(rate,nper,pv,fv,type) Rate：贷款利率； Nper：该项贷款的付款总数； Pv：现值，或一系列未来付款的当前值的累积和，也称为本金； Fv：为未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则假设其值为零，也就是一笔贷款的未来值为零； Type：用以指定各期的付款时间是在期初还是期末，Type值0 或省略表示期末，1表示期初 说明： 应确认所指定的 rate 和 np

22、er 单位的一致性。例如，同样是四年期年利率为 12% 的贷款，如果按月支付，rate 应为 12%/12，nper 应为 4*12；如果按年支付，rate 应为 12%，nper 为 4。 如果要计算贷款期间的支付总额，请用 PMT 返回值乘以 nper。,2、双变量模拟运算,（1）双变量模拟运算含义 （2）双变量模拟运算步骤,（1）双变量模拟运算含义,用于分析两个变量的几组不同数值变化对公式计算结果的影响。即在公式中使用了两个变量，这两个变量在公式中可使用两个空白单元格格表示，分别被引用到行和列单元格。,（2）双变量模拟运算步骤,设计模拟运算表结构 在行列标题交叉的单元格中输入运算公式 选

23、取包括公式和需要进行模拟运算的单元格区域在内的单元格区域 使用excel模拟运算功能（数据/模拟运算表），设置弹出的对话框即可,参见演示文件,（二）单变量求解,1、单变量求解含义 2、单变量求解步骤,1、单变量求解含义,单变量求解是指求解只有一个变量的方程的根。所求解的方程可以是线性方程，也可以是非线性方程。 模拟运算功能是在已知变量的情况下，对目标单元格进行求值。如果知道要从公式获取结果的输入值，需要计算可变单元格的取值，就需要使用单变量求解功能。,2、单变量求解步骤,设计模拟运算表结构（样式、字体、标题等） 在目标结果单元格中输入运算公式 使用excel单变量求解功能（工具/单变量求解），

24、设置弹出的对话框即可。,思考：某人在银行存入50000元，存款期为5年，为了在5年末从银行提取出65000元，存款年利率是多少？,定义表格结构 目标单元格定义公式,使用单变量求解功能，设置目标值和可变单元格。,求解结果。,参见演示文件,思考题： 某人先期存入银行10000元，现希望10年末能够获得200000元，每月应该存入等额资金多少元？10期年利率为5.5%。 提示：函数FV的使用。,（三）规划求解,1、规划求解含义 2、规划求解工具的装载 3、规划求解最优化问题 4、规划求解方程组问题,1、规划求解含义,规划求解是excel提供的一个非常有用的工具，不仅可以解决运筹优化问题，还可以解决线

25、性方程组和非线性方程组的问题。 当企业面临决策类的问题需要解决时，可以使用规划求解功能，从而设置规划求解，得到最佳结果。 使用规划求解功能可以计算某个单元格公式的最佳值。规划求解将与目标单元格公式相关联的一组单元格中的数值进行调整，最终在目标单元格中计算出期望的结果。,2、规划求解工具的装载,规划求解工具是excel的一个可选安装模块，通常使用“完全/定制安装”后才可以加载到系统中。 使用工具/加载宏命令，在弹出的对话框中选定规划求解，单击确定即可加载该工具。,3、规划求解最优化问题,以下面例题讲解规划求解工具的使用。,某企业生产A、B两种产品。每生产一盒A产品，需要机器运转1小时，需要耗费原

26、料1.6克，并可以获得50元毛利。每生产一盒B产品，需要机器运转1.5小时，需要耗费原料1.8克，并可以获得65元毛利。现在企业领导决定，每月使用800克原料，并分配580小时机器运转时间来生产这两种产品，那末企业如何分配A、B两种产品的生产才能获得最高的利润？,步骤：,（1）建立优化求解模型 （2）设计工作表结构 （3）设置公式 （4）规划求解,安排条件区和结果区 表的格式设置,启用规划求解功能，工具/规划求解 在弹出对话框中设置目标函数和约束条件设置。,修改约束条件： 进行规划求解后，如果需要重新设置约束条件，即修改规划求解的限制条件，形成其他方案。例如，上例中企业领导修改了原来的方案，将

27、每月原料配额由800克增加到1000克，机器运转时间由580小时增加到600小时。此时又怎样安排生产？ 如果企业领导决定每月生产150盒A产品，利用剩余的原料和时间生产B产品，此时又怎样安排生产？ 分析报告的建立： 规划求解除了显示结果外，还可以产生分析报告。可以产生运算结果报告、敏感性报告和极限值报告。3种报告系统会自动添加一张新的工作表。,参见演示文件,4、规划求解方程组问题,有些财务管理问题可以利用线性或非线性方程组建立定量分析模型，所以涉及对这些方程进行求解，利用规划求解可以方便求解方程组。 例如求解如下方程组的解。,参见演示文件,（四）方案分析,当企业需要比较分析若干个方案时，可以利

28、用方案管理功能，添加不同的方案内容，生成相应的报表，进而比较分析合适的方案，进行选择。 企业利用规划求解计算出结果后，可以将这些结果保存为方案，作为决策的依据。,方案管理的内容：,菜单：工具/方案 1、建立方案 2、显示方案 3、修改、删除和增加方案 4、合并方案 5、建立方案摘要报告,打开方案管理对话框只是显示当前工作表中的方案，其他工作表的方案则不显示。实际工作中，为了便于方案的管理与使用，可以通过“合并方案”功能直接复制其他工作表中的方案。也可以将其他工作簿中的方案合并。,！方案可以来源不同的工作簿,合并后的结果。,如果当前工作表中建立了多个方案，为了更好地对多个方案进行比较分析，可以利

29、用建立“方案摘要”的方法将所有方案集中在一个工作表中显示出来。,参见演示文件,（五）数据分析工具库,数据分析工具库的含义 数据分析工具库的装载 数据分析工具库的使用,1、数据分析工具库的含义,Excel还提供了一组“数据分析工具库”的数据分析工具，利用该工具库可以更好对数据进行分析与处理。数据分析工具库提供了不同的分析工具类型，在进行数据分析操作时，可以选择合适的工具进行，从而达到更好地说明效果。,2、数据分析工具库的装载,数据分析工具库是excel的一个可选安装模块，通常使用“完全/定制安装”后才可以加载到系统中。 使用工具/加载宏命令，在弹出的对话框中选定分析工具库，单击确定即可加载该工具

30、。,3、数据分析工具库的使用,（1）方差分析工具 （2）相关系数 （3）回归分析 （4）抽样分析工具 （5）直方图分析工具 （6）移动平均法 （7）指数平滑法,（1）方差分析工具,方差分析工具提供了几种方差分析工具。具体使用哪一种工具则根据因素的个数以及待检验样本总体中所含样本的个数而定。,单因素方差分析工具可对两个或更多样本的数据执行简单的方差分析。此分析可提供一种假设检验，该假设的内容是：每个样本都取自相同基础概率分布，而不是对所有样本来说基础概率分布都不相同。,双因素方差分析工具可用于当数据按照二维进行分类时的情况。例如，在测量植物高度的实验中，植物可能使用不同品牌的化肥（例如 A、B

31、和 C），并且也可能放在不同温度的环境中（例如高和低）。对于这 6 对可能的组合 化肥，温度，我们有相同数量的植物高度观察值。使用此方差分析工具，我们可检验.,统计学:单因素和双因素方差分析中可以利用F统计量或者P值进行检验。方法如下：,如果F的值大于F的临界值，则拒绝原假设。 如果P值小于显著性水平，则拒绝原假设。,例题1：为检验3家工厂生产的机器混合一批原料所需要的平均时间是否相同，某公司得到了关于混合原料所需时间的数据，利用这些数据检验3家工厂混合生产一批原料所需要的平均时间是否相同。显著性水平=0.05,步骤： 1、表格结构设置 2、选择单因素方差分析，工具/数据分析单因素方差分析。设

32、置对话框,问题：原假设？,SS：离差平方和，Df：自由度，MS：均方差，F:F统计量，P-value：p值，F crit：临界值。 F=10.636，大于临界值4.2565，拒绝原假设，即3家工厂混合原料所需平均时间因该不相同。,参见演示文件,例题2：某试验田采用了2种不同的施肥方式和4种不同的水温。试验方式和产量如表所示，在0.05的显著性水平下,分析施肥方式和水温对产量的影响各自是否显著。,步骤： 1、表格结构设置 2、选择无重复双因素分析，工具/数据分析无重复双因素分析,设置对话框,参见演示文件,（2）相关系数,参见演示文件,相关系数是描述两个测量值变量之间的离散程度的指标 ，相关系数的

33、取值在 -1 和 +1 之间。 在excel中，利用相关系数分析工具或者统计函数correl、pearson可以进行相关系数的计算。从而实现对两个测量值变量的变化是否相关的判断。 使用步骤具体举例说明。,（3）回归分析,使用回归分析工具能够对数据组建立回归方程，拟合回归曲线，对参数进行检验和统计，并且对预测值进行精度检验和置信区间的估计等操作。 利用回归分析可以建立两个变量间相互关系的方程，即回归方程。通常y表示因变量，x表示自变量。一个自变量称为一元线性回归模型，两个自变量以上称为多元现性回归模型。 回归模型的建立有如下两种方法： 方法1：使用函数 方法2：使用数据分析/回归分析工具,例题3：产品销售价格和满意度进行分析(线性回归分析),方法1：使用函数建立线性回归模型：y=a + bx,a=INTERCEPT(known_y , known_x), b=SLOPE(known_y , known_x),使用函数=RSQ(known_y , known_x)求解判定系数。,判定系数=0.203200887，与系数1相差太远，说明拟合程度比较低。,参见演示文件,方法2：使用回归分析工具建立线性回归模型：y=a + bx,1、使用回归