ECEL财务应用函数

1、 EXCEL常用函数介绍数学函数的基本语法及运用文本函数的基本语法及运用子字符串函数的基本语法及运用逻辑函数的基本语法及运用查找和引用函数的基本语法及运用财务函数的基本语法及运用对于这各类函数，要求尽可能能熟悉其语法形式，了解其功能，掌握各参数的含义，又因为函数间可以嵌套使用，在实践中运用非常灵活。 函数基础EXCEL函数基本上由三部分组成，即函数名称、括号和参数，其表示形式为函数名称（参数1，参数2）其中，函数名称是指函数的含义，如SUM求和，AVERAGE求平均值，括号即括住参数的符号，即括号中包含所有参数，参数是告诉EXCEL所欲执行的目标单元或数值，参数应用逗号隔开。函数的使用有两种方

2、式直接在公式中输入函数。这种方法是选择单元格，输入=，然后按照函数的语法直接键入。这种方法适用于用户对函数非常熟悉，清楚的了解函数的结构及其参数的意义的情形。使用插入菜单的函数命令。这种方法是通过函数向导完成函数的输入，适用于用户对函数的功能或结构不太熟悉或函数本身过于复杂等情况。数学函数的基本语法及运用函数的基本组成函数名称（参数1，参数2，）常用的数学函数主要有：Sum( )、sumif( )用于分类汇总、ABS( )返回参数的绝对值、sign( )用于判断参数的正负还是0，分别用1，-1，0表示、INT( )取整（小于等于参数）、MOD( )求余、SQRT( )求正数的平方根、round

3、( )四舍五入,例：=ROUND(355.26,-2)、rand( )产生0-1之间的随机数例：假设要投资一项产品的研发，预计销售量、单价均为某一范围内的随机数，成本固定（简化模型），试判断是否值得投资。(生成图表)Frequency ( data_array, bins_array )以一列垂直数组返回某个区域中数据的频率分布。 Count( ) ：在计数时将把数字、空值、逻辑值、日期或文字代表的数计算进去，但错误值或其他无法转化成数字的文字则被忽略。但是，如果参数是一个数组或引用，那么只统计其中的数字，数组或引用的空单元格、逻辑值、文字或错误值都将被忽略。Counta ( ) :返回参数列

4、表中非空值的单元格个数。利用它可以计算单元格区域或数组中包含数据的单元格个数。如果不需要统计逻辑值、文字或错误值，则使用COUNT（）。例：数据的拆分与合并字符串函数逻辑函数的基本语法及使用用来判断指定的条件是否成立。比较运算符：=,=,IF （逻辑表达式，真时值，假时值）、AND （）、OR （）、NOT （）、SUMIF （）、COUNTIF （）。IF( )逻辑判断函数格式：if(逻辑表达式，value-if-true,value-if-false)功能：执行真假值判断，根据逻辑条件的真假值，返回不同的结果。最多可嵌套7层。例题： 对某公司的销售业绩进行分级评价。 销售额大于100万元，

5、销售评价为A级； 70-100万元，销售评价为B级； 32”,”apples”Countif( )格式： Countif( range,criteria)作用：根据条件统计符合条件的数字或字符串出现的次数若要根据某一条件返回两个备选项中的某一值，用IF( )若要计算基于一个文本字符串或某范围内的一个数值的总和，可用SUMIF( )AND( )所有参数的计算结果为 TRUE 时，返回 TRUE；只要有一个参数的计算结果为 FALSE，即返回 FALSE。AND 函数的一种常见用途就是扩大用于执行逻辑检验的其他函数的效用。例如，IF 函数用于执行逻辑检验，它在检验的计算结果为 TRUE 时返回一个

6、值，在检验的计算结果为 FALSE 时返回另一个值。通过将 AND 函数用作 IF 函数的 logical_test 参数，可以检验多个不同的条件，而不仅仅是一个条件。AND(logical1, logical2, .) 最多可包含 255 个条件。 参数的计算结果必须是逻辑值（如 TRUE 或 FALSE），而参数必须是包含逻辑值的数组或引用。 如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。 如果指定的单元格区域未包含逻辑值，则 AND 函数将返回错误值 #VALUE!。 OR( )在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE；任何一个参数的逻辑值为 FALS

7、E，即返回 FALSE。语法OR(logical1,logical2,.)Logical1,logical2,. 是 1 到 255 个需要进行测试的条件，测试结果可以为 TRUE 或 FALSE。注解参数必须能计算为逻辑值，如 TRUE 或 FALSE，或者为包含逻辑值的数组或引用。 NOT( )对参数值求反。当要确保一个值不等于某一特定值时，可以使用 NOT。语法NOT(logical)Logical可以计算出 TRUE 或 FALSE 的值或表达式。注解如果 logical 为 FALSE，NOT 返回 Yes；如果 logical 为 TRUE，NOT 返回 No。例：已知各月份的销售

8、明细记录，试统计各种产品的销售情况。（数据透视表、SUMIF （）、分类汇总）第三课 文本函数的基本语法及使用数字形式的文本的输入方法：公式输入法在该数字串前加上单引号value（）将文本转化成数字、text （）用指定的格式将数字转换为一个文本字符串、len （）用来测出一个文本字符串的长度、trim （）将前导空格、后随空格、字符间多于一个的空格滤去、exact （）用于比较两个字符串是否完全匹配，是一个条件函数，返回值为true或false子字符串函数的基本语法及使用子字符串函数用来解决一个字符串与其本身某个部分的关系问题，或一个较短的字符串与一个较长的字符串之间的关系问题。right

9、（）用于指定字符串中提取右边N个字符、left （）用于指定字符串中提取左边N个字符、mid （）用于从指定字符串中第M个字符开始提取N个字符、find （字符串1，字符串2，M）用于在字符串2中，从第M个字符开始寻找字符串1，并返回位置数、rept （字符串，M）用于将指定字符串重复M次以组成一个新字符串、substitute(字符串，旧子符串，新字符串，M,N)其中字符串中包含N个旧子字符串，执行此操作后，将用新子字符串替换字符串中的第M个旧字符串。例： 数据的分分合合数据的拆分与提取数据的合并数据的合并计算数据的拆分与提取例：想利用邮件合并功能给每位客户发送贺卡或邮件，需要把通讯地址和邮

10、政编码分开。数据|分列Left( )、right( )、mid( ):分别从左、右和中间提取单元格的文本字符。LEN( )返回某一单元格的字符总个数数据的合并例：客户资料中有客户的身份证信息，现在想要根据身份证号把客户出生年月日的信息提取出来，在客户生日时给他意外的惊喜，以提高客户的满意度和忠诚度。 两种方法：1、& 2、concatenate( ) 功能：将几个文本字符串合并为一个文本字符串。也可以用 &运算符代替函数 CONCATENATE 实现文本项的合并。从身份证信息中提取顾客的性别信息（在第17位）=if(mod(mid(身份证号，17，1)，2)=1，“男”，“女”)数据的合并计算

11、例：资本成本是指企业为筹集和使用资金而付出的代价。在筹资决策中，比较各个方案的综合资本成本，选择综合资本成本小者进行投资。一般的做法是计算出每种筹资方式的资金成本，然后再根据该种资金占总资金的比重计算出加权平均资金成本。但是如果我们改用数组，就可以只键入一个公式来完成这些运算。 数组公式录入+sum( ):ctrl+shift+回车sumproduct（）：返回若干组彼此对应元素乘积之和。数组：数组就是单元的集合或是一组处理的值集合。可以写一个数组公式，即输入一个单个的公式，它执行多个输入的操作并产生多个结果每个结果显示在一个单元中。数组公式可以看成是有多重数值的公式。与单值公式的不同之处在于

12、它可以产生一个以上的结果。一个数组公式可以占用一个或多个单元。执行后的结果中会出现用大括弧“ ”框住的单元区域，这个区域在运算中当作一个整体来处理，所以不能对其中的任一格作任何单独处理，必须针对整个数组来处理。如果数组公式计算所得的数组比选定的数组区域还要大，则超过的值不会出现在工作表上。 第四课 查找和引用函数用来查找或引用工作表中的检索的信息，如科目汇总表的生成过程、如客户应收账款的管理等，应用非常广泛。CHOOSE （）、MATCH （）、VLOOKUP （）、DGET （）、COLUMN （）、ROW （）、OFFSET （） CHOOSE( )、MATCH( )、DGET( )、IN

13、DEX( )、VLOOKUP( )、OFFSET( )、COLUNM( )、COLUMNS( )、ROW( )、ROWS( )、TRANSPOSE( )CHOOSE( )CHOOSE函数用于从一系列元素中检索出一项，其格式为=CHOOSE（索引数，元素1，元素2，元素N）索引数是待查找项在列表中的位置，它必须是大于0且小于等于N的整数元素可以是数值、文本或单元格引用。MATCH( )函数用于从一系列元素中检索出一项与查找值最相近的元素的序号。格式：MATCH（查找值，查找范围，类型）类型取值1，-1，0。类型为1或省略，查找小于等于查找值的最大值，查找范围内的各元素必须升序排列。类型为0精确查

14、找，不要求排序。经常与INDEX( )、VLOOKUP( )、OFFSET（）函数等组合使用，非常实用。INDEX( )函数INDEX函数的用途是返回列表或数组中的指定值。格式：公式：INDEX(区域,行号,列号，区域号)例：返回A1：C10区域，第五行，第二列的值=INDEX（(A1:C10,E5:H20),5,2,1）(B5,F9)例：查找工号为0028的员工的实发工资例：根据商品编码查找并自动填充单价vlookup( )格式： =VLOOKUP（查找值，查找表或区域，索引列号，检索类型） 功能：VLOOKUP函数用于搜索区域首列满足条件的元素，确定待检索单元格在区域中的行列号，再进一步返

15、回选定单元格的值。查找值：需要在数组或区域的第一列中查找的数值，可以为数值、单元引用或文本字符串；查找范围：需要在其中查找数据的数据表或数组；索引列号：指待返回值的列序号；检索类型为一逻辑值，true或省略，表示返回近似匹配值，false则返回精确匹配值。查到不到，则返回错误值#N/A，所以，函数VLOOKUP的查找可以达到两种目的：一是精确的查找。二是近似的查找。 HLOOKUP与VLOOKUPHLOOKUP用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。VLOOKUP用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。

16、当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数 HLOOKUP。当比较值位于要进行数据查找的左边一列时，请使用函数 VLOOKUP。语法形式对比： HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)其中，Lookup_value表示要查找的值，它必须位于自定义查找区域的最左列。Lookup_value 可以为数值、引用或文字串。Table_array查找的区域，用于查找数据的区域，上面

17、的查找值必须位于这个区域的最左列或最上面一行。可以使用对区域或区域名称的引用。Row_index_num为 table_array 中待返回的匹配值的行序号。Col_index_num为相对列号。最左列为1，其右边一列为2，依此类推.Range_lookup为一逻辑值，指明函数 HLOOKUP 查找时是精确匹配，还是近似匹配。true或省略，表示返回近似匹配值，false则返回精确匹配值。LOOKUP函数与MATCH函数当比较值位于要进行数据查找的左边一列时，使用函数 VLOOKUP。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用函数 MATCH 而不是函数 LOOKUP。MATCH

18、函数用来返回在指定方式下与指定数值匹配的数组中元素的相应位置。从以上分析可知，查找函数的功能，一是按搜索条件，返回被搜索区域内数据的一个数据值；二是按搜索条件，返回被搜索区域内某一数据所在的位置值。DGET( )DGET函数用于从数据清单或数据库中提取符合给定条件且唯一存在的值。格式：=DGET(database,field,criteria)Database构成列表或数据库的单元格区域。列表的第一行包含着每一列的标志项。 FieldField指定函数所使用的数据列。可以是文本，即两端带引号的标志项，如“使用年数”或“产量”；此外，Field 也可以是代表列表中数据列位置的数字：1 表示第一列

19、，2 表示第二列，等等。 criteria为一组包含给定条件的单元格区域。 OFFSET( )OFFSET函数用于以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以是一个单元格或者单元格区域，并可以指定返回的行数或者列数。其基本语法形式为：OFFSET(reference, rows, cols, height, width)。 其中，reference变量作为偏移量参照系的引用区域(reference必须为对单元格或相连单元格区域的引用，否则，OFFSET函数返回错误值VALUE!)。rows变量表示相对于偏移量参照系的左上角单元格向上(向下)偏移的行数，行数可为正数(代表在起

20、始引用单元格的下方)或者负数(代表在起始引用单元格的上方)或者0(代表起始引用单元格)。cols表示相对于偏移量参照系的左上角单元格向左(向右)偏移的列数，列数可为正数(代表在起始引用单元格的右边)或者负数(代表在起始引用单元格的左边)。如果行数或者列数偏移量超出工作表边缘，OFFSET函数将返回错误值REF！。height变量表示高度，即所要返回的引用区域的行数(height必须为正数)。width变量表示宽度，即所要返回的引用区域的列数(width必须为正数)。如果省略height或者width，则假设其高度或者宽度与reference相同。例如，公式OFFSET(A1,2,3,4,5)表

21、示比单元格A1靠下2行并靠右3列的4行5列的区域(即D3:H7区域)。由此可见，OFFSET函数实际上并不移动任何单元格或者更改选定区域，它只是返回一个引用。动态图表用OFFSET定义名称生成图表数据列表生成列表区域只绘制可见单元格图表实现动态第五课 财务函数EXCEL提供了许多财务函数，为财务分析提供了极大的便利。财务函数大体上可以分为四类：投资决策计算函数偿还率计算函数折旧计算函数债券及其他金融函数投资决策函数投资决策有其评价投资方案是否可行或孰优孰劣的指标。根据所选择的投资方案的现金流量是否贴现，分为贴现现金流量指标和非贴现现金流量指标。 非贴现法是没有考虑资金时间价值因素的方法，也称静

22、态法，主要包括投资回收期法、平均投资报酬率法。 投资回收期法是指根据回收全部原始投资金额的时间长短来判断方案是否可行的方法。当每年现金流量相等时，投资回收期=原始投资额每年现金流量当年现金流量不等时，采用逐年测试，直到每年现金净流量合计达到原始投资金额。(投资利润率法)平均投资利润率法是指根据投资方案预期平均盈利率大小选择最优方案的方法。投资利润率年平均利润原始投资总额决策时，高于必要的投资利润率的方案或多方案中的最高平均投资利润率方案入选。贴现现金流量指标法是指考虑货币的时间价值的投资决策方法，主要有：净现值法现值指数法内部报酬率法修正内部报酬率法净现值法是指按照货币时间价值观点，将未来现金

23、净流量与投资总额都折算为现值，按净现值大小选择投资方案的方法。 如果净现值为正数，表示方案可取，此投资计划有值得投资的可能，净现值越大越好。如果净现值为0，说明该方案的投资报酬率恰好就是所采用的最低报酬率；如果投资方案的净现值小于0，说明该方案是不可取的。EXCEL中有两个计算净现值的函数：PV( )、和NPV（ ）。EXCEL函数（一）PV1含义：返回投资净现值。2语法：PV (rate, nper, pmt, fv, type)。 Type为0或忽略，表示期末rate为各期利率。 nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。例如，对于一笔4年期按月偿还的汽车贷款，共有4

24、8（即4x12)个偿款期次。可以在公式中输入48作为nper的值。pmt为各期所应付给（或得到）的金额，其数值在整个年金期间（或投资期内）保持不变。通常pmt包括本金和利息，但不包括其他费用及税款。fv为未来值或在最后一次支付后希望得到的现金余额，如果省略fv，则假设其值为零（一笔贷款的未来值即为零）。 type为数字0或1，用以指定各期的付款时间是在期初还是期末。如果省略type，则假设其值为零，期末付款。 说明：应确认所指定的rate和nper单位的一致性。例如，同样是4年期年利率为12的贷款，如果按月支付，rate应为12 % /12, nper应为48（即4x12)；如果按年支付，ra

25、te应为12%，nper为4。3示例。假设要购买一项保险年金，该保险可以在今后20年内于每月末回报500元。此项年金的购买成本为60 000元，假定投资回报率为8。现在可以通过函数PV计算一下这笔投资是否值得。该项年金的现值为：PV(0.08 /12,1220,500)一59 777.15（元） 结果为负值，因为这是一笔付款，亦即支出现金流。年金59 777.15元的现值小于实际支付的60 000元。因此，这不是一项合算的投资。（二）NPV 1含义：基于一系列现金流和固定的各期贴现率，返回一项投资的净现值。投资的净现值是指未来各期支出（负值）和收人（正值）的当前值的总和。 2语法：NPV (r

26、ate, value 1, value 2,）。其中： rate为各期贴现率，是一固定值。 value 1, value 2,代表1-29笔支出及收入的参数值。 (1) value 1, value 2,所属各期间的长度必须相等，而且支付及收入的时间都发生在期末。 (2) NPV按次序使用value 1, value 2, 来注释现金流的次序。所以一定要保证支出和收入的数额按正确的顺序输入。 (3)如果参数是数值、空白单元格、逻辑值或表示数值的文字表达式，则都会计算在内；如果参数是错误值或不能转化为数值的文字，则被忽略。 (4）如果参数是一个数组或引用，只有其中的数值部分计算在内。忽略数组或引

27、用中的空白单元格、逻辑值、文字及错误值。 3说明： (1)函数NPV假定投资开始于value 1现金流所在日期的前一期，并结束于最后一笔现金流的当期。函数NPV依据未来的现金流计算。如果第一笔现金流发生在第一个周期的期初，则第一笔现金必须加入到函数NPV的结果中，而不应包含在values参数中。 (2) 函数NPV与函数PV（现值）相似。PV与NPV之间的主要差别在于：函数PV允许现金流在期初或期末（0）开始；而且，PV的每一笔现金流数额在整个投资中必须是固定的；而函数NPV的现金流数额是可变的。 (4)函数NPV与函数IRR内部收益率）也有关，函数IRR是使NPV等于零的比率：NPV(IRR

28、(. ), -) =0 4示例： 假设第一年投资10 000元，而未来3年梦年的收入分别为3 000元，4200元和6800元。假定每年的贴现率是10%，则投资的净现值是： NPV(10%，一10 000,3 000,4 200,6 800)1 188.44（元） 上述的例子中，将开始投资的10 000元作为value参数的一部分。这是因为付款发生在第一个周期的期末。 现值指数法又称获利指数法或利润指数法，是指根据投资方案现值指数进行选优决策的方法。现值指数是指投资项目未来报酬的总现值与初始投资额现值之比。现值指数未来投资报酬总现值初始投资总额现值现值指数大于1或等于1的项目为可行项目。内含报

29、酬率法是指根据投资方案的内含报酬率来确定投资方案是否可行并选择最优投资方案的方法。内含报酬率是指投资方案的净现值等于零时的贴现率，可使用内含报酬率函数IRR（）进行计算。 该方法考虑了货币的时间价值，反映了投资项目的真实报酬率。当只有一个方案时，如果内含报酬率大于或等于企业的资本成本或必要报酬率就可采纳，反之则拒绝。在多个互斥选择方案中，应选择内含报酬率超过资本成本或必要报酬率最多的投资项目。IRR 1含义：返回由数值代表的一组现金流的内部收益率。这些现金流不一定要均衡，但作为年金，它们必须按固定的间隔发生，如按月或按年。内部收益率为投资的回收利率，其中包含定期支付（负值）和收入（正值）。 2

30、语法：IRR (values, guess)。其中： values为数组或单元格的引用，包含用来计算内部收益率的数字，values必须包含至少一个正值和一个负值，以计算内部收益率。 (1)函数IRR根据数值的顺序来解释现金流的顺序。故应确定按需要的顺序输人了支付和收入的数值。 (2）如果数组或引用包含文本、逻辑值或空中单元格，这些数值将被忽略。 guess为对函数IRR计算结果的估计值： (1) Microsoft Excel使用迭代法计算函数IRR。从guess开始，函数IRR不断修正收益率，直至结果的精度达到0.00001%。如果函数IRR经过20次迭代，仍未找到结果，则返回错误值NUM!

31、. (2)在大多数情况下，并不需要为函数IRR的计算提供guess值。如果省略guess，假设它为0.1（即10%）。 (3)如果函数IRR返回错误值NUM!，或结果没有靠近期望值，可以给guess换一个值再试一下。 示例：假设要开办一家饭店。估计需要70 000元的投资，并预期今后5年的净收益为12 000元、15 000元、18 000元、21 000元和26 000元。B1:B6分别包含下面的数值：一70 000元、12 000元、15 000元、18 000元、21 000元和26000元。 计算此项投资4年后的内部收益率：IRR(Bl:B5)一2 1296计算此项投资5年后的内部收益

32、率： IRR(B1:B6)8 66%MIRR 1含义：返回某一连续期间内现金流的修正内部收益率。函数MIRR同时考虑了投资的成本和现金再投资的收益率。 2语法：MIRR (values, finance -rate, reinvestrate)。其中： values为一个数组或对数字单元格区的引用。这些数值代表着各期支出（负值）及收入（正值）。 (1）参数中必须至少包含一个正值和一个负值，才能计算修正后的内部收益率，否则函数MIRR会返回错误值DIV/0! (2)如果数组或引用中包括文字串、逻辑值或空白单元格，这些值将被忽略；但包括数值零的单元格计算在内。 finance rate为投入资金的融资利率（资本成本或必要报酬率）。 reinvest rate为各期收入净额再投资的收益率（再投资报酬率）。 3说明：函数MIRR根据输入值的次序来注释现金流的次序。所以，务必按照实际的顺序输入支出和收入数额，并使用正确的正负号（现金流入用正值，现金流出用负值）。 4示例：假设您正在从事商业性捕鱼工作，现在已经是第5个年头了。5年前以年利率10借款120 000元买了一艘捕鱼船，这5年每年的收入分别为39000元、30000元、21