Excle常用函数及技巧(200908)

常用实用函数及操作技巧 EXCLE 2 第一部分 常用实用函数第二部分 操作技巧 报告内容 3 第一部分 常用实用函数 一 逻辑函数二 数学函数三 统计函数四 查询和引用函数五 文本函数六 日期 时间函数七 财务函数 现值 终值 4 一 逻辑函数 一 AND函数 进行交集运算1 说明所有参数的逻辑值为真时 返回TRUE 只要一个参数的逻辑值为假 即返回FALSE 即当所有条件都成立时 返回true 否则 返回false 2 语法AND logical1 logical2 Logical1 logical2 表示待检测的1到30个条件值 各条件值可为TRUE或FALSE 5 实例 1 返回同时满足以下条件的销售员 a 保费超过6万元 b 性别为 男 c 年龄为不超过40岁 2 返回 可递减暂时性差异 6 二 OR函数 进行并集运算1 说明在其参数组中 任何一个参数逻辑值为TRUE 即返回TRUE 所有参数的逻辑值为FALSE 即返回FALSE 即只要有一个逻辑值为true 就返回true 所有逻辑值均为false 才返回false 2 语法OR logical1 logical2 Logical1 logical2 为需要进行检验的1到30个条件 分别为TRUE或FALSE 一 逻辑函数 7 实例 1 返回满足以下条件之一的销售员 a 保费超过6 5万元的男性 b 保费超过3 5万元的男性 8 三 IF函数 条件函数1 说明执行真假值判断 根据逻辑计算的真假值 返回不同结果 可以使用函数IF对数值和公式进行条件检测 2 语法IF logical test value if true value if false Logical test表示计算结果为TRUE或FALSE的任意值或表达式 Value if truelogical test为TRUE时返回的值 Value if falselogical test为FALSE时返回的值 最多可嵌套七层 一 逻辑函数 9 1 返回满足以下条件的销售员 a X8万元为 优秀 实例 10 第一部分 常用实用函数 一 逻辑函数二 数学函数三 统计函数四 查询和引用函数五 文本函数六 日期 时间函数七 财务函数 现值 终值 11 一 ABS函数 1 说明 计算绝对值返回数字的绝对值 绝对值没有符号 2 语法Abs number 二 INT函数 1 说明 向下取整将数字向下舍入到最接近的整数 2 语法Int number Number需要进行向下舍入取整的实数 二 数学函数 实例 INT 8 9 返回8 12 三 MOD函数 1 说明 返回两数相除的余数结果的正负号与除数相同 2 语法Mod number divisor Number为被除数 Divisor为除数 二 数学函数 实例 13 四 Product函数 计算乘积1 说明 将所有以参数形式给出的数字相乘 并返回乘积值 2 语法Product number1 number2 Number1 number2 为1到30个需要相乘的数字参数 3 注意当参数为数字 逻辑值或数字的文字型表达式时可以被计算 当参数为错误值或是不能转换成数字的文字时 将导致错误 4 比较Sum函数是计算数据之和 而product是计算数据之乘积 二 数学函数 实例 14 五 ROUND函数 按位数舍入1 说明返回某个数字按指定位数取整后的数字 2 语法Round number num digits Number需要进行四舍五入的数字 Num digits指定的位数 按此位数进行四舍五入 3 注意如果num digits大于0 则四舍五入到指定的小数位 如果num digits等于0 则四舍五入到最接近的整数 如果num digits小于0 则在小数点左侧进行四舍五入 二 数学函数 实例 15 六 SUM函数 计算数字之和1 说明返回某一单元格区域中所有数字之和 也可返回符合条件的数据之和 即条件求和 2 语法Sum number1 number2 Number1 number2 为1到30个需要求和的参数 3 注意事项在使用条件求和时 在所有内容输入完毕之后 同时按 ctrl shift enter 二 数学函数 16 实例 17 七 SUMIF函数 条件求和1 说明 根据指定条件对若干单元格求和 2 语法Sumif range criteria sum range Range为用于条件判断的单元格区域 Criteria为确定哪些单元格将被相加求和的条件 其形式可以为数字 表达式或文本 例如 条件可以表示为32 32 32 或 apples Sum range是需要求和的实际单元格 3 注意事项 1 只有在区域中相应的单元格符合条件的情况下 sum range中的单元格才求和 如果忽略了sum range 则对区域中的单元格求和 2 MicrosoftExcel还提供了其他一些函数 它们可根据条件来分析数据 例如 如果要计算单元格区域内某个文本字符串或数字出现的次数 则可使用COUNTIF函数 如果要让公式根据某一条件返回两个数值中的某一值 例如 根据指定销售额返回销售红利 则可使用IF函数 二 数学函数 18 第一部分 常用实用函数 一 逻辑函数二 数学函数三 统计函数四 查询和引用函数五 文本函数六 日期 时间函数七 财务函数 现值 终值 19 一 COUNT和COUNTA函数 统计单元格个数1 说明 Count 返回包含数字以及包含参数列表中的数字的单元格的个数 Counta 返回参数列表中非空值的单元格个数 2 语法Count value1 value2 Value1 value2 为包含或引用各种类型数据的参数 1到30个 3 注意事项 1 函数COUNT在计数时 将把数字 日期 或以文本代表的数字计算在内 但是错误值或其他无法转换成数字的文字将被忽略 2 如果参数是一个数组或引用 那么只统计数组或引用中的数字 数组或引用中的空白单元格 逻辑值 文字或错误值都将被忽略 如果要统计逻辑值 文字或错误值 请使用函数COUNTA 三 统计函数 20 实例 A 通过 车牌号码 统计车辆台数 使用count函数 返回结果为 0 B 通过 车牌号码 统计车辆台数 使用counta函数 返回结果为 8 C 通过 序号 统计车辆台数 使用count函数 返回结果为 8 21 二 COUNTIF函数 按条件统计1 说明计算区域中满足给定条件的单元格的个数 2 语法Countif range criteria Range为需要计算其中满足条件的单元格数目的单元格区域 Criteria为确定哪些单元格将被计算在内的条件 其形式可以为数字 表达式或文本 例如 条件可以表示为32 32 32 或 apples 3 注意事项MicrosoftExcel提供其他函数 可用来基于条件分析数据 例如 若要计算基于一个文本字符串或某范围内的一个数值的总和 可使用SUMIF工作表函数 若要使公式返回两个基于条件的值之一 例如某指定销售量的销售红利 可使用IF工作表函数 三 统计函数 22 D 统计 车牌号码 中以 苏 开头的车辆台数 使用countif函数 返回 8 E 统计 序号 中数字大于 4 的个数 通过countif函数 返回 4 实例 23 三 MAX和MAXA函数 返回最大值1 说明返回一组值中的最大值 2 语法MAX number1 number2 Number1 number2 是要从中找出最大值的1到30个数字参数 3 注意事项 1 可以将参数指定为数字 空白单元格 逻辑值或数字的文本表达式 如果参数为错误值或不能转换成数字的文本 将产生错误 2 如果参数为数组或引用 则只有数组或引用中的数字将被计算 数组或引用中的空白单元格 逻辑值或文本将被忽略 3 如果逻辑值和文本不能忽略 请使用函数MAXA来代替 4 如果参数不包含数字 函数MAX返回0 零 三 统计函数 24 实例 25 四 MIN和MINA函数 返回最小值1 说明返回一组值中的最小值 2 语法MIN number1 number2 Number1 number2 是要从中找出最小值的1到30个数字参数 3 注意事项 1 可以将参数指定为数字 空白单元格 逻辑值或数字的文本表达式 如果参数为错误值或不能转换成数字的文本 将产生错误 2 如果参数是数组或引用 则函数MIN仅使用其中的数字 空白单元格 逻辑值 文本或错误值将被忽略 3 如果逻辑值和文本字符串不能忽略 请使用MINA函数 4 如果参数中不含数字 则函数MIN返回0 三 统计函数 26 实例 27 一 逻辑函数二 数学函数三 统计函数四 查询和引用函数五 文本函数六 日期 时间函数 第一部分 常用实用函数 28 一 HLOOKUP函数 实现水平查找1 说明在表格的首行查找指定的数值 并由此返回表格当前列中指定行处的数值 当比较值位于数据表的首行 并且要查找下面给定行中的数据时 请使用函数HLOOKUP HLOOKUP中的H代表 行 2 语法 1 HLOOKUP lookup value table array row index num range lookup Lookup value为需要在数据表第一行中进行查找的数值 Lookup value可以为数值 引用或文本字符串 Table array为需要在其中查找数据的数据表 可以使用对区域或区域名称的引用 Table array的第一行的数值可以为文本 数字或逻辑值 如果range lookup为TRUE 则table array的第一行的数值必须按升序排列 2 1 0 1 2 A Z FALSE TRUE 否则 函数HLOOKUP将不能给出正确的数值 如果range lookup为FALSE 则table array不必进行排序 2 文本不区分大小写 四 查询函数 29 一 HLOOKUP函数 实现水平查找3 注意事项 1 可以用下面的方法实现数值从左到右的升序排列 选定数值 在 数据 菜单中单击 排序 再单击 选项 然后单击 按行排序 选项 最后单击 确定 在 排序依据 下拉列表框中 选择相应的行选项 然后单击 升序 选项 2 Range lookup为一逻辑值 指明函数HLOOKUP查找时是精确匹配 还是近似匹配 如果为TRUE或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于lookup value的最大数值 如果range value为FALSE 函数HLOOKUP将查找精确匹配值 如果找不到 则返回错误值 N A 3 如果函数HLOOKUP找不到lookup value 且range lookup为TRUE 则使用小于lookup value的最大值 4 如果函数HLOOKUP小于table array第一行中的最小数值 函数HLOOKUP返回错误值 N A 30 实例 31 二 VLOOKUP函数 实现垂直查找1 说明在表格的首列查找指定的数值 并由此返回表格当前行中指定列处的数值 在VLOOKUP中的V代表垂直 2 语法 1 VLOOKUP lookup value table array col index num range lookup Lookup value为需要在表格第一列中查找的数值 Lookup value可以为数值 引用或文本字符串 Table array为需要在其中查找数据的数据表 可以使用对区域或区域名称的引用 例如数据库或列表 如果range lookup为TRUE 则table array的第一列中的数值必须按升序排列 2 1 0 1 2 Z FALSE TRUE 否则 函数VLOOKUP不能返回正确的数值 如果range lookup为FALSE table array不必进行排序 2 Table array的第一列中的数值可以为文本 数字或逻辑值 3 文本不区分大小写 四 查询函数 32 二 VLOOKUP函数 实现垂直查找 4 Col index num为table array中待返回的匹配值的列序号 Col index num为1时 返回table array第一列中的数值 col index num为2 返回table array第二列中的数值 以此类推 如果col index num小于1 函数VLOOKUP返回错误值值 VALUE 如果col index num大于table array的列数 函数VLOOKUP返回错误值 REF 5 Range lookup为一逻辑值 指明函数VLOOKUP返回时是精确匹配还是近似匹配 如果为TRUE或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于lookup value的最大数值 如果range value为FALSE 函数VLOOKUP将返回精确匹配值 如果找不到 则返回错误值 N A 6 如果函数VLOOKUP找不到lookup value 且range lookup为TRUE 则使用小于等于lookup value的最大值 7 如果lookup value小于table array第一列中的最小数值 函数VLOOKUP返回错误值 N A 8 如果函数VLOOKUP找不到lookup value且range lookup为FALSE 函数VLOOKUP返回错误值 N A 四 查询函数 33 实例 34 第一部分 常用实用函数 一 逻辑函数二 数学函数三 统计函数四 查询和引用函数五 文本函数六 日期 时间函数七 财务函数 现值 终值 35 一 CONCATENATE函数 合并字符串1 说明将几个文本字符串合并为一个文本字符串 2 语法CONCATENATE text1 text2 Text1 text2 为1到30个将要合并成单个文本项的文本项 这些文本项可以为文本字符串 数字或对单个单元格的引用 3 注意事项也可以用 和号 运算符代替函数CONCATENATE实现文本项的合并 五 文本函数 36 二 EXACT函数 比较字符串是否完全相同1 说明该函数测试两个字符串是否完全相同 如果它们完全相同 则返回TRUE 否则 返回FALSE 函数EXACT能区分大小写 但忽略格式上的差异 利用函数EXACT可以测试输入文档内的文本 2 语法EXACT text1 text2 Text1待比较的第一个字符串 Text2待比较的第二个字符串 五 文本函数 37 三 LEFT函数 返回左边字符1 说明LEFT基于所指定的字符数返回文本字符串中的第一个或前几个字符 LEFTB基于所指定的字节数返回文本字符串中的第一个或前几个字符 此函数用于双字节字符 2 语法LEFT text num chars LEFTB text num bytes Text是包含要提取字符的文本字符串 Num chars指定要由LEFT所提取的字符数 Num chars必须大于或等于0 如果num chars大于文本长度 则LEFT返回所有文本 如果省略num chars 则假定其为1 Num bytes按字节指定要由LEFTB所提取的字符数 五 文本函数 38 实例 39 四 RIGHT函数 返回右边字符1 说明RIGHT根据所指定的字符数返回文本字符串中最后一个或多个字符 RIGHTB根据所指定的字符数返回文本字符串中最后一个或多个字符 此函数用于双字节字符 2 语法RIGHT text num chars RIGHTB text num bytes Text是包含要提取字符的文本字符串 Num chars指定希望RIGHT提取的字符数 Num bytes指定希望RIGHTB根据字节所提取的字符数 3 注意事项Num chars必须大于或等于0 如果num chars大于文本长度 则RIGHT返回所有文本 如果忽略num chars 则假定其为1 五 文本函数 40 实例 41 五 MID函数 返回特定字符1 说明MID返回文本字符串中从指定位置开始的特定数目的字符 该数目由用户指定 MIDB返回文本字符串中从指定位置开始的特定数目的字符 该数目由用户指定 此函数用于双字节字符 2 语法MID text start num num chars MIDB text start num num bytes Text是包含要提取字符的文本字符串 Start num是文本中要提取的第一个字符的位置 文本中第一个字符的start num为1 以此类推 Num chars指定希望MID从文本中返回字符的个数 Num bytes指定希望MIDB从文本中返回字符的个数 按字节 3 注意事项如果start num大于文本长度 则MID返回空文本 如果start num小于文本长度 但start num加上num chars超过了文本的长度 则MID只返回至多直到文本末尾的字符 五 文本函数 42 实例 43 第一部分 常用实用函数 一 逻辑函数二 数学函数三 统计函数四 查询和引用函数五 文本函数六 日期 时间函数七 财务函数 现值 终值 44 一 DADEDIF函数 计算两个日期之间的天数 月数 年数1 说明计算两个日期之间的天数 月数 年数2 语法DATEDIF start date end date unit start date表示时间段内的第一个日期或起始日期 end date表示时间段内的最后一个日期或结束日期 unit表示所需信息的返回类型 具体是 y 表示 年 ym 表示 月 md 表示 天 六 日期 时间函数 45 二 YEAR函数 返回年份1 说明返回某日期对应的年份 返回值为1900到9999之间的整数 2 语法YEAR serial number Serial number为一个日期值 其中包含要查找年份的日期 应使用DATE函数来输入日期 或者将日期作为其他公式或函数的结果输入 例如 使用DATE 2008 5 23 输入2008年5月23日 3 注意事项MicrosoftExcel可将日期存储为可用于计算的序列号 默认情况下 1900年1月1日的序列号是1而2008年1月1日的序列号是39448 这是因为它距1900年1月1日有39448天 六 日期 时间函数 46 三 MONTH函数 返回月份1 说明返回以序列号表示的日期中的月份 月份是介于1 一月 到12 十二月 之间的整数 2 语法MONTH serial number Serial number表示一个日期值 其中包含要查找的月份 应使用DATE函数来输入日期 或者将日期作为其他公式或函数的结果输入 六 日期 时间函数 47 第一部分 常用实用函数 一 逻辑函数二 数学函数三 统计函数四 查询和引用函数五 文本函数六 日期 时间函数七 财务函数 现值 终值 48 一 FV函数 返回终值1 说明计算在固定利率条件下等额分期付款方式 返回某项投资的未来值 2 语法FV Rate Nper Pmt Pv Type Rate为各期利率 各期利率应固定不变 Nper为总投资期 即该项投资的付款期总数 Pmt 为各期所应支付的金额 其数值在整个年金期间保持不变 如果忽略pmt 则pv参数必须有数据 Pv为现值 即从该项投资开始计算时已经入帐的款项 或一系列未来付款的当前值的累积和 也称为本金 如果省略PV 则假设其值为零 但pmt参数必须有数据 Type数字0或1 用以指定各期的付款时间是在期初还是期末 如果省略type 则假设其值为零 0为期末 1为期初 七 财务函数 49 一 FV函数 返回终值3 注意事项 1 应确认所指定的rate和nper单位的一致性 例如 同样是四年期年利率为12 的贷款 如果按月支付 rate应为12 12 nper应为4 12 如果按年支付 rate应为12 nper为4 2 在所有参数中 支出的款项 用负数表示 收入的款项 用正数表示 4 实例 如下 可点击进入 50 二 PV函数 返回现值1 说明返回投资的现值 现值为一系列未来付款的当前值的累积和 2 语法PV Rate Nper Pmt Fv Type Rate为各期利率 各期利率应固定不变 Nper为总投资期 即该项投资的付款期总数 Pmt 为各期所应支付的金额 其数值在整个年金期间保持不变 如果忽略pmt 则Fv参数必须有数据 Fv为终值 即最终清理时所得的现金 如果省略fv 则假设其值为零 一笔贷款的未来值即为零 如果省略Fv 则假设其值为零 但pmt参数必须有数据 Type数字0或1 用以指定各期的付款时间是在期初还是期末 如果省略type 则假设其值为零 0为期末 1为期初 七 财务函数 51 二 PV函数 返回现值3 注意事项 1 应确认所指定的rate和nper单位的一致性 例如 同样是四年期年利率为12 的贷款 如果按月支付 rate应为12 12 nper应为4 12 如果按年支付 rate应为12 nper为4 2 在所有参数中 支出的款项 用负数表示 收入的款项 用正数表示 4 实例 如下 可点击进入 52 三 RATE函数 返回年金的各期利率 1 说明返回年金的各期利率 2 语法RATE Nper Pmt Pv Fv Type Guess Nper为总投资期 即该项投资的付款期总数 Pmt为各期所应支付的金额 其数值在整个年金期间保持不变 如果忽略pmt 则Fv参数必须有数据 Pv为现值 为现值 即从该项投资开始计算时已经入帐的款项 或一系列未来付款当前值的累积和 也称为本金 Fv为终值 即最终清理时所得的现金 如果省略fv 则假设其值为零 一笔贷款的未来值即为零 如果省略Fv 则假设其值为零 但pmt参数必须有数据 Type数字0或1 用以指定各期的付款时间是在期初还是期末 如果省略type 则假设其值为零 0为期末 1为期初 Guess为预期利率 如果省略 则假设该值为10 七 财务函数 53 三 RATE函数 返回年金的各期利率 3 注意事项 1 函数RATE通过迭代法计算得出 并且可能无解或有多个解 如果在进行20次迭代计算后 函数RATE的相邻两次结果没有收敛于0 0000001 函数RATE将返回错误值 NUM 如果函数RATE不收敛 请改变guess的值 通常当guess位于0到1之间时 函数RATE是收敛的 2 在所有参数中 支出的款项 用负数表示 收入的款项 用正数表示 3 应确认所指定的guess和nper单位的一致性 对于年利率为12 的4年期贷款 如果按月支付 guess为12 12 nper为4 12 如果按年支付 guess为12 nper为4 54 三 RATE函数 返回年金的各期利率 4 实例 1 某人建议你贷给他30000元 并同意每年年末付给你9000元 共付五年 你是否应接受该建议 在Excel工作表的单元格中录入 RATE 5 9000 30000 回车确认 结果自动显示为15 24 结果表明 如果15 24 高于其他投资项目的报酬率 则可以接受该建议 2 计算分期收款的折现率 某公司出售一套设备 协议约定采用分期收款方式 从销售当年年末开始分5年收款 每年收200万元 合计1000万元 不考虑增值税 假定购货方在销售成立日支付货款 付800万元即可 购货方在销售成立日支付的800万元可以看做是应收金额的公允价值 该笔业务的账务处理 涉及折现率的计算问题 即要计算每年年末的 未实现融资收益 和 财务费用 数据 首先要计算年金为200万元 期数为5年 现值为800万元的折现率 在Excel工作表的单元格中录入 RATE 5 200 800 回车确认 结果显示为7 93 55 四 NPER函数 返回某项投资的总期数 1 说明返回基于固定利率及等额分期付款方式 返回某项投资的总期数 2 语法NPER Rate Pmt Pv Fv Type Rate为各期利率 各期利率应固定不变 Pmt 为各期所应支付的金额 其数值在整个年金期间保持不变 如果忽略pmt 则Fv参数必须有数据 Pv为现值 即从该项投资开始计算时已经入帐的款项 或一系列未来付款的当前值的累积和 也称为本金 Fv为终值 或在最后一次付款后希望得到的现金余额 如果省略fv 则假设其值为零 如果省略Fv 则假设其值为零 但pmt参数必须有数据 Type数字0或1 用以指定各期的付款时间是在期初还是期末 如果省略type 则假设其值为零 0为期末 1为期初 七 财务函数 56 四 NPER函数 返回某项投资的总期数3 注意事项在所有参数中 支出的款项 用负数表示 收入的款项 用正数表示 4 实例 如下 可点击进入 1 某一投资 要求的最低回报率为6 现在一次投入500万元 每期支付200万元 投资清理时可收回3000万元 请计算投资期 2 某一投资 要求的最低回报率为5 现在一次投入300万元 每期支付100万元 投资清理时可收回1000万元 请计算投资期 57 五 PMT函数 返回计算等额还款额 1 说明基于固定利率及等额分期付款方式 返回贷款的每期付款额 2 语法PMT Rate Nper Pv Fv Type Rate为各期利率 各期利率应固定不变 Nper该项贷款的付款总数 Pv为现值 即从该项投资开始计算时已经入帐的款项 或一系列未来付款的当前值的累积和 也称为本金 Fv为终值 或在最后一次付款后希望得到的现金余额 如果省略fv 则假设其值为零 如果省略Fv 则假设其值为零 但pmt参数必须有数据 Type数字0或1 用以指定各期的付款时间是在期初还是期末 如果省略type 则假设其值为零 0为期末 1为期初 七 财务函数 58 五 PMT函数 返回计算等额还款额 3 注意事项 1 在所有参数中 支出的款项 用负数表示 收入的款项 用正数表示 2 PMT返回的支付款项包括本金和利息 3 应确认所指定的rate和nper单位的一致性 4 实例 如下 可点击进入 1 某贷款60万元 银行贷款年利率6 贷款期为25年 请计算各月支付房贷的金额 2 某人储蓄 银行存款利率为4 要求10年之后达到100万元请计算各月需储蓄的金额 59 六 PPMT函数 返回计算等额还款方式下各期本金 1 说明基于固定利率及等额分期付款方式 返回投资在某一给定期间内的本金偿还额 2 语法PPMT Rate Per Nper Pv Fv Type Rate为各期利率 各期利率应固定不变 Per用于计算其本金数额的期数 必须介于1到nper之间 Nper该项贷款的付款总数 Pv为现值 即从该项投资开始计算时已经入帐的款项 或一系列未来付款的当前值的累积和 也称为本金 Fv为终值 或在最后一次付款后希望得到的现金余额 如果省略fv 则假设其值为零 如果省略Fv 则假设其值为零 但pmt参数必须有数据 Type数字0或1 用以指定各期的付款时间是在期初还是期末 如果省略type 则假设其值为零 0为期末 1为期初 七 财务函数 60 六 PPMT函数 返回计算等额还款方式下各期本金 3 注意事项 1 在所有参数中 支出的款项 用负数表示 收入的款项 用正数表示 2 应确认所指定的rate和nper单位的一致性 4 实例 如下 可点击进入 1 某贷款60万元 银行贷款年利率6 贷款期为25年 请计算第15个月 第150个月的月支付房贷款中本金的金额 61 七 IPMT函数 返回计算等额还款方式下各期利息 1 说明基于固定利率及等额分期付款方式 返回给定期数内对投资的利息偿还额 2 语法IPMT Rate Per Nper Pv Fv Type Rate为各期利率 各期利率应固定不变 Per用于计算其本金数额的期数 必须介于1到nper之间 Nper该项贷款的付款总数 Pv为现值 即从该项投资开始计算时已经入帐的款项 或一系列未来付款的当前值的累积和 也称为本金 Fv为终值 或在最后一次付款后希望得到的现金余额 如果省略fv 则假设其值为零 如果省略Fv 则假设其值为零 但pmt参数必须有数据 Type数字0或1 用以指定各期的付款时间是在期初还是期末 如果省略type 则假设其值为零 0为期末 1为期初 七 财务函数 62 七 IPMT函数 返回计算等额还款方式下各期利息 3 注意事项 1 在所有参数中 支出的款项 用负数表示 收入的款项 用正数表示 2 应确认所指定的rate和nper单位的一致性 4 实例 如下 可点击进入 1 某贷款60万元 银行贷款年利率6 贷款期为25年 请计算第15个月 第150个月的月支付房贷款中利息的金额 63 八 NPV函数 返回计算净现值 1 说明通过使用贴现率以及一系列未来支出 负值 和收入 正值 返回一项投资的净现值 2 语法NPV Rate value1 value2 value3 Rate为某一期间的贴现率 是一固定值 Value1 value2 为1到29个参数 代表支出及收入 3 注意事项 1 Value1 value2 在时间上必须具有相等间隔 并且都发生在期末 2 NPV使用Value1 Value2 的顺序来解释现金流的顺序 所以务必保证支出和收入的数额按正确的顺序输入 3 函数NPV假定投资开始于value1现金流所在日期的前一期 并结束于最后一笔现金流的当期 函数NPV依据未来的现金流来进行计算 如果第一笔现金流发生在第一个周期的期初 则第一笔现金必须添加到函数NPV的结果中 而不应包含在values参数中 七 财务函数 64 八 NPV函数 返回计算净现值 3 注意事项 续 4 在所有参数中 支出的款项 用负数表示 收入的款项 用正数表示 5 应确认所指定的rate和nper单位的一致性 4 实例 如下 可点击进入 1 某投资年初投入100万元 第一年末收益30万元 第二年收益40万元 第三年收益为0元 第四年收益为60万元 第五年处置收益30万元 假设收益率要求为10 请问此投资是否合算 净现值为正数18 13万元 适合投资 65 九 IRR函数 内涵报酬率函数 1 说明返回由数值代表的一组现金流的内部收益率 这些现金流不必为均衡的 但作为年金 它们必须按固定的间隔产生 如按月或按年 内部收益率为投资的回收利率 其中包含定期支付 负值 和定期收入 正值 2 语法IRR Values Guess Values为数组或单元格的引用 包含用来计算返回的内部收益率的数字 Guess为对函数IRR计算结果的估计值 如果省略guess 假设它为10 3 注意事项 1 Values必须包含至少一个正值和一个负值 以计算返回的内部收益率 2 函数IRR根据数值的顺序来解释现金流的顺序 故应确定按需要的顺序输入了支付和收入的数值 七 财务函数 66 九 IRR函数 返回计算内涵报酬率 3 注意事项 续 3 在所有参数中 支出的款项 用负数表示 收入的款项 用正数表示 4 如果函数IRR返回错误值 NUM 或结果没有靠近期望值 可用另一个guess值再试一次 4 实例 如下 可点击进入 1 某投资年初投入100万元 第一年末收益30万元 第二年收益40万元 第三年收益为0元 第四年收益为60万元 第五年处置收益30万元 请计算其内涵报酬率 67 十 财务函数的小结 1 财务函数的基本语法顺序是 Rate Nper Pmt Pv Fv Type 当计算Pmt时 其函数语法为Pmt Rate Nper Pv Fv Type 当计算Nper时 其函数是Nper Rate Pmt Pv Fv Type 其他的一次类推 2 Pmt Ppmt Ipmt3 函数IRR与函数NPV 净现值函数 的关系十分密切 函数IRR计算出的收益率即净现值为0时的利率 见NPV实例S2 4 NPV与PV的功能都是计算现值 不同的是PV要求各期的金额一致 而NPV没有此要求 七 财务函数 68 第一部分 常用实用函数第二部分 操作技巧 报告内容 69 第二部分 操作技巧 一 数据透视表二 分列三 打印批注四 快捷健五 其他 70 一 数据透视表介绍在Excle中 数据透视表是最具有创造性的分析工具 同时具有很强的技术性和强大的分析功能 熟悉掌握数据透视表 用户可以有效分析 组织复杂数据 并根据不同的标准对数据进行分类汇总 尤其对于数量众多 结构复杂的工作表 数据透视表可以十分便利的将其中的数据分布规律显现出来 并将工作表重新组合 添加算法等 二 创建数据透视表1 启动数据透视表和数据透视图向导 选择数据源区域的任一单元格 然后选择 数据 数据透视表和数据透视图 命令 启动向导 如图所示 一 数据透视表 71 2 设置数据源类型在向导的第一个对话框中 用户需要选择数据源类型 在本例中实用的是excle工作表 因此选中 MicrosoftExcel数据清单或数据库 单选按钮 同时选中 数据透视表 单选按钮 在默认情况下 这些按钮都是自动选中的 我们不作修改即可 如图 3 选择数据源区域单击向导对话框的 下一步 按钮后 在下一对话框中选择数据源区域 如果在开始操作之前我们选中了数据源中的单元格 在此时就会默认选中整个数据源区域 如果修改 重选等 实用 选定区域 后代按钮 红色 自行选择区域 如图 72 4 选择数据透视表的插入位置单击对话框 下一步 按钮 在下一个对话框中选择数据透视表的插入位置 是使用 新建工作表 还是 现有工作表 如图 5 打开 布局 对话框单击对话框中的 布局 按钮 打开布局对话框 在该对话框中能定义数据透视表的布局和集结数据的方式 如图 布局对话框的界面主要有3部分 顶部的指导文本 中部的工作区和右侧的 可得字段 列表 其中工作区由 列 行 数据 3部分组成 可得字段 列表中列出了可以向工作区的3个部分添加的数据字段 73 6 设置数据透视表的布局将 可得字段 直接拖动至指定位置 我们将 预付赔款 进行分险种分机构列示 可将 公司 拖动至行 险种 拖动至列 本币金额 拖动至数据 如图 7 查看数据透视表的选项设置数据透视表的布局后 单击 布局 对话框中 确定 按钮 返回到向导对话框中 单击 选项 按钮 打开 数据透视表选项 对话框 查看数据透视表的选项 如图 74 8 查看完成的数据透视表单击 数据透视表选项 对话框中的 确定 按钮 返回到向导中 然后单击 完成 按钮 查看完成的数据透视表 如图 75 9 数据透视表数据源修改的刷新数据透视表的数据源修改后 数据透视表并不能自动刷新 必须使用 刷新 按钮方可完成 如在2102机构下增加0330险种的预付赔款1000元 76 第二部分 操作技巧 一 数据透视表二 分列三 打印批注四 快捷健五 其他 77 一 分列介绍顾名思义即是把一列数据 包括文本 数值等 分成若干列 二 使用分列直接从SAP中复制出的数据 不同字段没有分单元格列示 如图 二 分列 78 1 选中要 分列 的单元格 如图 看起来数据占据了很多单元格 但其实只是只有 A列 有数据 故选中A列即可 2 启动 分列 向导选择 数据 分列 命令 启动向导 如图 79 3 选择 分列 类型支持两种分列类型 一种是使用分隔符号分列 一种是固定宽度分列 如图 固定分列 适合于排列均匀 字段长度相等的情况 分隔符号 适合于字段之间都有一定的规律 如都有空格 等 4 固定宽度分列 1 如为固定宽度分列 直接按 下一步 按钮 一般情况下 系统会自动实现分列 如系统分列不能符合要求 可手工分列 取消为双击带箭头的竖线 选择为在想要的刻度位置单击 80 4 固定宽度分列 2 设置每列数据类型选择完成 单击 下一步 进行数据类型设置 具体类型有 常规 文本 日期 不导入此列等 根据需要自行设置 如无特别要求 可不作修改 3 完成分列单击 完成 按钮 完成固定分列 81 4 分隔符号分列 1 固定宽度不能分列出符合要求的列 可使用分隔符号分列 选择 分隔符号 选项 进入分隔符号分列向导 如图 82 4 分隔符号分列 2 选择分隔符号分隔符号有 table 空格 分号 等 可以复选 3 单击 下一步 进入 数据类型选择 选项 单击 完成 完成 分隔符号 分列 如图 83 第二部分 操作技巧 一 数据透视表二 分列三 打印批注四 快捷健五 其他 84 1 在 文件 菜单上 单击 页面设置 再单击 工作表 选项卡 2 请执行下列操作之一 若要在工作表中出现批注的原地点打印批注 请在 批注 框中选择 如同工作表中的显示 选项 3 若要在工作表的底部打印批注 请在 批注 下拉列表框中选择 工作表末尾 选项 单击 打印 三 打印批注 85 第二部分 操作技巧 一 数据透视表二 分列三 打印批注四 快捷健五 其他 86 Ctrl W或Ctrl F4关闭选定的工作簿窗口 Ctrl F5恢复选定的工作簿窗口的大小 Ctrl F6打开多个工作簿窗口时 切换到下一个工作簿窗口 Ctrl Sh