《EXCEL函数培训》PPT课件.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 digits 0 在小数点左侧指定要四舍五入的位数 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 iRate 利率 指年金公式的iNper 年金期数 或总投资期 指年金公式的nPmt 年金 或者每期投资金额 指年金公式的A 以负数表示 PV 为现值 即从该项投资开始计算时已经入帐的款项 或一系列未来付款的当前值的累积和 以负数表示 Type 为数字0或1 省略为0 数字0表期末支付 数字1表示期初支付 与年金有关的函数 单位 亿元 19 2 年金现值函数PV 语法形式为PV rate nper pmt fv type P A 1 1 i n iRate 利率 指年金公式的iNper 年金期数 或总投资期 指年金公式的nPmt 年金 或者每期投资金额 指年金公式的A 以负数表示 FV 为未来值 或最后一次支付后希望得到的现金余额 Type 为数字0或1 省略为0 数字0表期末 数字1表示期初 与年金有关的函数 单位 亿元 20 3 每期支付的金额 年金 函数PMT 语法形式为PMT rate nper pv fv type Rate 利率 指年金公式的iNper 年金期数 或总投资期 指年金公式的nPV 为现值 或一系列未来付款的当前值的累积和 也称为本金 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计算结果的估计值 MicrosoftExcel使用迭代法计算函数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 npe