EXCEL函数培训课件.ppt

EXCEL函数学习,2010年7月,1,单位：亿元,常用函数-目录,2,1、IF函数,2、SUM函数,3、SUMIF函数,5、COUNT函数,4、SUMPRODUCT函数,6、COUNTA函数,7、COUNTBLANK函数,8、COUNTIF函数,10、ROUND函数,11、CONCATENATE函数,13、LOOKUP函数,14、VLOOKUP函数,9、RANK函数,12、TEXT函数,单位：亿元,常用函数,3,1、IF函数,IF函数是一个逻辑函数，当达到某一个条件是，取某一个值，它经常与其函数合并使用，在财务上典型应用是个税计算。,单位：亿元,常用函数,4,2、SUM求和函数,SUM求和函数是最简单也是最常用的函数，函数表示方式为“=SUM（Number1,Number2,）最多可带30个，Number1可发是数字，也可以是单元格，还可以是一个区域。,单位：亿元,常用函数,5,3、SUMIF条件求和函数,公式：SUMIF(range,criteria,sum_range) Range ：为用于条件判断的单元格区域。 Criteria ：为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。 Sum_range：是需要求和的实际单元格,单位：亿元,常用函数,6,4、SUMPRODUCT返回数组元数对应相乘的和,公式： SUMPRODUCT(array1,array2,array3) array ：为数组，至少为2个，最多为30个。,单位：亿元,常用函数,7,5、COUNT数值计数函数,公式：COUNT(Value1, Value2, Value2) value：可以为数值，也可以为单元格，也可为空字符串，如果单元格为非数值类型的数据将不被统计，但日期型的数据可以被统计。,单位：亿元,常用函数,8,6、COUNTA非空单元格计数函数,公式：COUNTA(Value1, Value2, Value2) value：可以为数值、文本，也可以为单元格引用，不包括空白单元格，但包括空字符（”)。,单位：亿元,常用函数,9,7、COUNTBLANK计数函数,公式：COUNTBLANK(rang) rang：为一个区域，只能有一个参数，如果单元为0值将不被统计，如果单元包括（”）空字符的公式，也将被统计。,单位：亿元,常用函数,10,8、COUNTIF计数函数,公式：COUNTIF(rang, creteria) rang：为需要计算其中满足条件的单元格数目的单元格区域。 Criteria: 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、“32“、“32“ 或 “apples“。,单位：亿元,常用函数,11,10、Round四舍五入按指定位数取整的函数,公式：Round(number, num_digits) number：需要四舍五入的数字。 Num_digits：指定要四舍五入的位数， Num_digits=0,指定要四舍五入的小数， Num_digits0,在小数点左侧指定要四舍五入的位数。,9、RANK排名函数,公式：RANK(number, REF，ORDER) 表示数字number在ref中的排序，order等于零或省略表示按升序排序，order等于表示按降序排序,单位：亿元,常用函数,12,11、concatenate文本连接函数,公式：concatenate(text1, text2) Text1, text2, . 为 1 到 30 个将要合并成单个文本项的文本项。这些文本项可以为文本字符串、数字或对单个单元格的引用。 也可以用 &（和号）运算符代替函数 CONCATENATE 实现文本项的合并。,1、TEXT文本转换函数,语法:TEXT(value,format_text) Value 为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用。 Format_text 为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。,单位：亿元,常用函数,13,13、lookup查找并返回指定值，,公式：lookup(lookup_value,lookup_vector,result_vector) 表示在LOOKUP_VECTOR中查找LOOKUP_VALUE的值，并返回RESULT_VECTOR中同一位置的值，要求LOOKUP_VECTOR必须按数字、字母从小到大排序，LOOKUP_VETOR与RESULT_VECTOR只能包含一列或一行的值，且必须大小一致，如果LOOKUP_VALUE少于LOOKUP_VECTOR最少值，则出错，如果LOOKUP_VALUE大于LOOKUP_VECTOR最少值，但找不到相等值，则返回最近值。,单位：亿元,常用函数,14,14、Vlookup查找并返回指定值，,公式：Vlookup(lookup_value,table_array,col_index_num,rang_lookup) 表示在table_array中首列查找LOOKUP_VALUE的值，并返回table_array中指定列号col_index_num中同一位置的值，rang_lookup为逻辑值，省略或为true，1表示模糊匹配，要求table_array必须按数字、字母从小到大排序，如rang_lookup的值为0或FALSE则表示精确匹配， table_array无须排序，如找不到则返回#N/A。,15,单位：亿元,16,1、FV年金终值,2、PV年金现值,3、PMT求年金,4、NPER年金中周期,7、RATE年金中利率,8、NPV非固定投资现值,9、IRR内部收益率,与年金有关的函数-目录,单位：亿元,与年金有关的函数,17,在介绍具体与年金有关的函数之前，我们首先来了解一下年金函数中常见的参数： 未来值 (fv)-在所有付款发生后的投资或贷款的价值。 投资期数 (nper)-为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。 付款 (pmt)-对于一项投资或贷款的定期支付数额。其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其他费用及税款。 现值 (pv)-在投资期初的投资或贷款的价值。例如，贷款的现值为所借入的本金数额。 利率 (rate)-投资或贷款的利率或贴现率。 类型 (type)-付款期间内进行支付的间隔，如在月初或月末，用0或1表示。 从上面的参数，我们也引出了5个年金函数，分别为：FV、PV、NPER、PMT、RATE，他们分别对应年金公式中的年金终值S，年金现值P，期数N，年金A，利率I,单位：亿元,18,1、年金终值函数FV,语法形式为FV(rate,nper,pmt,pv,type)， S=A*(（1+i）n-1)/i Rate：利率，指年金公式的i Nper：年金期数，或总投资期，指年金公式的n Pmt：年金，或者每期投资金额，指年金公式的A，以负数表示； PV：为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，以负数表示。 Type：为数字0或1，省略为0，数字0表期末支付，数字1表示期初支付。,与年金有关的函数,单位：亿元,19,2、年金现值函数PV,语法形式为PV(rate,nper,pmt,fv,type) P=A*(1-（1+i）-n)/i Rate：利率，指年金公式的i Nper：年金期数，或总投资期，指年金公式的n Pmt：年金，或者每期投资金额，指年金公式的A，以负数表示； FV：为未来值，或最后一次支付后希望得到的现金余额。 Type：为数字0或1，省略为0，数字0表期末，数字1表示期初。,与年金有关的函数,单位：亿元,20,3、每期支付的金额(年金)函数PMT,语法形式为PMT(rate,nper,pv,fv,type) Rate：利率，指年金公式的i Nper：年金期数，或总投资期，指年金公式的n PV：为现值，或一系列未来付款的当前值的累积和，也称为本金。 ； FV：为未来值，或最后一次支付后希望得到的现金余额。 Type：为数字0或1，省略为0，数字0表期末，数字1表示期初。 典型应用：融资租赁每期租金的确定,与年金有关的函数,单位：亿元,21,4、投资总期数NPER,语法形式为NPER(rate,nper,pv,fv,type)， Rate：利率。 Npmt：年金为各期所应支付的金额，其数值在整个年金期间保持不变。 PV：为现值，或一系列未来付款的当前值的累积和，也称为本金。 FV：为未来值，或最后一次支付后希望得到的现金余额。 Type：为数字0或1，省略为0，数字0表期末，数字1表示期初。,与年金有关的函数,单位：亿元,财务函数,22,5、年金利率RATE,语法形式为RATE(nper,npmt,pv,fv,type,guess)， Nper：总投资期，即该项目投资付款期总和。 Npmt：各期所支付的金额，其数值在整个期间保持不变。 PV：为现值，或一系列未来付款的当前值的累积和，也称为本金。 FV：为未来值，或最后一次支付后希望得到的现金余额。 Type：为数字0或1，省略为0，数字0表期末，数字1表示期初。,单位：亿元,23,6、投资净现值NPV,语法形式为NPV(rate,value1,value2,)， Rate：利率， value：未来一系列的收入（收入）与支出（负值），为1到29个参数。 注：函数 NPV 假定投资开始于 value1 现金流所在日期的前一期，并结束于最后一笔现金流的当期。函数 NPV 依据未来的现金流来进行计算。如果第一笔现金流发生在第一个周期的期初，则第一笔现金必须添加到 函数 NPV 的结果中，而不应包含在 values 参数中。,与年金有关的函数,单位：亿元,24,7、内部收益率IRR和XIRR,IRR语法 ：(Values, guess) Values：为数组或单元格的引用，包含用来计算返回的内部收益率的数字，必须包括一个负数和正数。 Guess: 为对函数 IRR 计算结果的估计值。Microsoft Excel 使用迭代法计算函数 IRR。,与年金有关的函数,XIRR语法： (Values, dates，guess)，表示一组不固定日期收益的内部收益率，与IRR的区别在于：IRR的收益均假设在期末，而XIRR的收益可以一年中的任何一天，收益的时间无规率性。,25,单位：亿元,26,1、ACCRINTM到期债 应计利息,2、CUMPRINC等额 偿还贷款中本金,3、DISC贴现率,4、PRICEDISC贴现价格,5、EFFECT实际利率,6、MOMINAL名义利率,与债券、票据有关的函数 目录,单位：亿元,27,1、求到期付息有价证券的应计利息的函数ACCRINTM,语法：ACCRINT(issue，maturity ，rate，par，basis) 其中issue为有价证券的发行日，maturity为有价证券的到期日，settlement为有价证券的成交日，即在发行日之后，有价证券卖给购买者的日期，rate为有价证券的年息票利率，par为有价证券的票面价值，如果省略par，函数ACCRINTM就会自动将par设置为￥1000，basis为日计数基准类型。,与债券、票据有关的函数,单位：亿元,28,2、计算等额还本付中偿还本金数额CUMPRINC,语法：CUMPRINC(rate，nper，pv,start_period，end_period，type) 。其中rate为利率，nper为总付款期数，pv为现值，start_period为计算中的首期，付款期数从1开始计数，end_period为计算中的末期，type为付款时间类型。 典型应用：融资租赁未确认融资收益的计算。 与此函数功能相同的函数为PPMT（RATE，PER，NPER，PV，FV，TYPE），求偿还的利息函数为IPMT（ RATE，PER，NPER，PV，FV，TYPE ），区别就是PPMT只能计算某一期间偿还的本金，而CUMPRNINC可以计算一连续期间偿还本金之和。,与债券、票据有关的函数,单位：亿元,29,3、有价证券的贴现率DISC,其语法形式为： DISC(settlement,maturity,pr,redemption,basis) 其中settlement为有价证券的成交日，即在发行日之后，有价证券卖给购买者的日期，maturity为有价证券的到日期，到期日是有价证券有效期截止时的日期，pr为面值为“￥100“的有价证券的价格，redemption为面值为“￥100“的有价证券的清偿价格，basis为日计数基准类型。,与债券、票据有关的函数,单位：亿元,30,4、有价证券的贴现价格PRICEDISC,其语法形式为： DISC(settlement,maturity,discount,redemption,basis) 其中settlement为有价证券的成交日，即在发行日之后，有价证券卖给购买者的日期，maturity为有价证券的到日期，到期日是有价证券有效期截止时的日期，discount有价证券的贴现率，redemption为有价证券的清偿价格，basis为日计数基准类型。,与债券、票据有关的函数,单位：亿元,31,5、计算实际利率函数EFFECT,其语法形式为：EFFECT(nominal_rate,npery) 表示以名义利率为nominal_rate,复利npery期的实际利率，计算公式为（1+ nominal_rate/ npery） npery-1,与债券、票据有关的函数