excel2007技巧之6函数高级应用实战技巧Word版

1、嵌套函数实战技巧函数的嵌套是指某个函数引用另一个函数的返回值来进行计算。文本函数实战技巧文本函数用于对文本数据进行提取、删除、替换以及组合等运算。统计函数实战技巧财务函数实战技巧查找和引用函数实战技巧统计函数主要有对单元格数量统计、排位、检验与回归分析等功能。财务函数用于对财务数据进行分析和计算，是财务工作中必不可少的工具。查找和引用函数用于在数据表中查找某一特定的内容，或者引用公式中的相关信息内容。日期和时间函数用于对日期和时间进行计算，修改等操作中。日期函数实战技巧函数高级应用实战技巧6函数是Excel中重要的内容，本章将在上一章的基础上，从不同的函数类型着手，详细为读者讲述函数高级应用的

2、内容和技巧，以帮助读者完全掌握这部分的知识。本章导读203 / 926.1 日期和时间函数实战技巧例1 快速将日期转换为星期输入输入输入在Excel中使用函数可以快速将日期转换为星期，具体操作方法如下： 在工作表中输入几个日期，然后在单元格D3中输入函数“=TEXT(WEEKDAY(D3),”dddd”，如下图所示。 按【Enter】键，将日期转换成星期，在单元格D4中输入“=TEXT(WEEKDAY(C4),”aaaa”，如下图所示。 按【Enter】键，得到中文的星期，结果如下图所示。例2 计算当前日期是全年的第几天在Excel中使用函数可以快速将日期转换为星期，具体操作方法如下： 打开“

3、台历”工作表，复制并修改。在单元格G5中输入公式“=E4-DATE(YEAR(E4),1,0)”，如下图所示。 按【Ctrl+Enter】组合键，得到公式计算结果，如下图所示。例3 计算上个月的天数在Excel中计算上个月的天数，可使用如下的两种方法。方法一：使用DAY函数计算输入 新建工作表并输入数据内容，在单元格C4中输入公式“=DAY(B4-DAY(B4)”，如下图所示。拖动 按【Ctrl+Enter】组合键，得到公式计算结果，然后用拖动填充柄填充公式，最终效果如下图所示。输入DAY函数用于返回所引用日期中的天数，DAY(B4)计算得出天数5，DAY(B4-DAY(B4)的含义是返回5天

4、前的天数，即上个月最后一天的天数。方法二：使用DAY函数计算输入 在单元格D4中输入公式“=DAY(EOMONTH(B4,-1)”，如下图所示。拖动 按【Ctrl+Enter】组合键，得到公式计算结果，然后拖动填充柄填充公式，结果如下图所示。EOMONTH函数用于返回所引用日期之前或之后的月份最后一天的序列号。EOMONTH(B4,-1)的含义是返回单元格B4中日期前一个月的最后一天的序列。例4 轻松求得当前日期所在的周数每一年的1月1日所在的周是一年中的第一周，使用WEEKNUM函数可求得某个日期所在的周数，具体操作方法如下： 打开“台历”工作表，在单元格G6中输入函数“=WEEKNUM(E

5、5)”，如下图所示。 单击【Ctrl+Enter】组合键，得到当前日期所在年中的周数，如下图所示。WEEKNUM函数的语法为WEEKNUM(serial_num,return_type)，Serial_num代表一周中的日期，Return_type为1个数字，确定星期计算从哪一天开始，如果为1，说明一周开始于星期日如果为2，则说明一周开始于星期一，如果省略不写，则默认为1。例5 巧妙计算员工年龄在Excel中，如果已知员工的出生年月，就可使用YEAR函数快速的计算出员工的年龄，具体操作方法如下：输入 在单元格D3中输入公式“=YEAR(TODAY()-C3)-1900”，如下图所示。 按【Ct

6、rl+Enter】组合键，得到结果，如下图所示。输入输入 在“开始”选项卡下“数字”组中的“数字格式”下拉列表框中，将该单元格的数字格式设置为“常规”，该单元格中将自动显示年龄，如下图所示。 将鼠标指针移动单元格D3的右下角，拖动填充柄，填充公式，效果如下图所示。因为Excel中使用了1900年时间系统，公式TODAY()-C3进行计算时将自动将相减的结果加上1900，所有要得到正确的年龄，还需减去1900。例6 计算两个日期之间工作日的天数计算两个日期之间工作日的天数的具体操作方法如下： 在单元格C6中输入公式“=NETWORKDAYS(C4,D4)”，如下图所示。 按【Ctrl+Enter

7、】组合键，得出计算结果，如下图所示。NETWORKDAYS返回参数 start_date 和 end_date 之间完整的工作日数值。其中不包括周日和假期。例7 计算同年中两个日期之间的月数计算同年中两个日期之间的月数的具体操作方法如下：在单元格E4中输入公式“=MONTH(D4)-MONTH(C4)”，按【Ctrl+Enter】键即可计算出结果，如下图所示。输入输入例8 计算跨年份两个日期之间的月数 在单元格E5中输入公式“=YEAR(D5)-YEAR(C5)*12+MONTH(D5)-MONTH(C5)”，按【Ctrl+Enter】键即可计算出结果，如下图所示。例9 计算两个日期之间的年数

8、计算年数的方法很简单，只需用YEAY函数将两个返回值相减即可，如下图所示。输入6.2 查找和引用函数实战技巧例1 使用函数巧查询在Excel中，VLOOKUP函数用于在表格数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值。下面使用该函数来快速查找所需的值，具体操作方法如下： 打开“楼盘概览”工作表，在单元格区域E14:F15中输入数据内容和公式，如下图所示。输入 在单元格E15中输入相应的楼盘名称，将在单元格F15中显示该楼盘的开盘时间，如下图所示。VLOOKUP函数最后一个参数为逻辑值，其中false值代表精确匹配，true值代表模糊匹配。例2 用V.LOOKUP函数逆向查询从上

9、一个技巧中，已知VLOOKUP函数只能根据首列中指定的值查询其他列与之相对应的内容。其实，使用该函数和其他函数的嵌套也可以实现通过其他列的数据来查询首列的内容，即逆向查询，其具体操作方法如下： 如果已知楼盘的开盘时间，要查询楼盘名称，可输入公式“=VLOOKUP(F16,IF(0,1,D5:D12,E5:E12),2,FALSE)”，如下图所示。输入5.单击 按【Ctrl+Enter】组合键，将得到楼盘的名称，如下图所示。例3 CHOOSE函数的用法CHOOSE函数可以根据索引号返回数值参数列表中的数值。下面通过使用该函数得到奖金等级为例，介绍其使用方法。 在员工的奖金名单中，选择单元格D3，

10、在其中输入公式“=CHOOSE(IF(C3<=3000,3,IF(C3>=5000,1,2),"一等奖","二等奖","三等奖")”，如下图所示。输入 按【Ctrl+Enter】组合键，得到奖金等级，如下图所示。 通过拖动填充柄，复制公式，得到其他员工的销售奖金等级，如下图所示。例4 快速查找化工原料的数量继续上一个实例，通过将INDEX函数与MATCH函数配合使用，可以查找某种化学原料的数量，其具体操作方法如下： 在化工原料信息工作表中，单击单元格D48，从中输入公式“=INDEX(C39:C45, MATCH(&quo

11、t;草酸盐",B39:B45,0)”，如下图所示。输入 按【Ctrl+Enter】组合键确认，将得到“草酸盐”的数量为460，如下图所示。例5 使用函数进行动态求和下面通过实例来介绍使用函数进行动态求和的过程，具体操作方法如下：1.输入2.选择拖动 新建工作表，从中输入产品销售额的相关信息，选择单元格D11，然后单击“数据”选项卡下“数据工具”组中的“数据有效性”按钮下方的下拉按钮，在弹出的下拉菜单中选择“数据有效性”选项，如下图所示。单击2.设置1.选择青年以3.单击4.选择 将弹出“数据有效性”对话框，在其中的“允许”下拉列表框中选择“序列”选项，在“来源”文本框中设置序列来源，

12、如下图所示。 单击“确定”按钮，在单元格D11右侧将出现一个下拉按钮，单击它，将弹出下拉列表，可以从中进行选择，效果如下图所示。2.单击1.选择 选择单元格区域C4:C8，然后单击“公式”选项卡下“定义的名称”组中的“定义名称”按钮，将弹出“新建名称”对话框，保持默认即可，如下图所示。 分别选择单元格区域D4:D8、E4:E8和F4:F8，定义名称，然后单击“定义的名称”组中的“名称管理器”按钮，弹出“名称管理器”对话框，从中可查看定义的名称，如下图所示。2.输入1.选择 选择单元格D12，从中输入公式“=SUM(INDIRECT($D$11)”，按【Ctrl+Enter】组合键确认，将得到“

13、销售点1”的所有产品的销售额，如下图所示。例6 判断员工姓名是否重复录入在员工信息表中，要判断是否重复录入了员工的信息，可通过查找员工姓名是否重复，来进行判断（假如员工中没有重名者），其具体操作方法如下： 新建员工信息表，选择单元格H4，从中输入“=IF(ROW()=MATCH(C4,$C$1:$C$16,0),"","重复")”，如下图所示。输入拖动 按【Ctrl+Enter】组合键确认输入，然后拖动填充柄进行填充，将查找出重复的姓名，如下图所示。例7 快速生成数字序列制作工作表时，经常要输入数字序列，输入数字序列的方法有很多，这里我们使用函数来快速输

14、入数字序列，其具体操作方法如下： 新建工作表，选择单元格区域A1:A10，然后按【F2】键，从中输入“=ROW(1:10)”，如下图所示。2.输入1.选择 按【Ctrl+Shift+Enter】组合键，确认数组输入，即可快速填充好数字序列，如下图所示。输入 要在一行中填充数字序列，可以先选择要输入数字序列的单元格区域，然后按【F2】键输入函数“=COLUMN(A:F)”，如下图所示。 按【Ctrl+Shift+Enter】组合键，确认数组输入，也可快速填充字序列，如下图所示。例8 快速引用不同工作表中的数据在制作工作表时，经常用在不同的工作表中引用数据信息，下面介绍一种使用函数引用其他工作表中

15、的数据的方法。 在Sheet3中创建一张员工档案表，存放员工的基本信息，如下图所示。3.选择2.单击1.选择 切换到Sheet1中，输入有关员工工资的信息，然后选择单元格B4，单击“公式”选项卡下“函数库”组中的“查找与引用”下拉按钮，在弹出的下拉菜单中选择VLOOKUP函数，如下图所示。 弹出“函数参数”对话框，从中设置相应的参数，如下图所示。设置拖动 单击【确定】按钮，关闭对话框，得到公式结果，然后拖动填充柄填充公式，如下图所示。例9 返回最后一个非空单元格在包含空的单元格中，要查找最后一个非空单元格中的值，可按如下方法操作： 建工作表，从中制作有关产品的信息数据表，新然后在单元格E13中

16、输入公式“LOOKUP(CHAR(255),B:B)”，然后按【Ctrl+Enter】组合键确认，得到公式查找的结果，如下图所示。输入输入 在单元格E14中输入公式“LOOKUP(9E+307,C:D)，然后按【Ctrl+Enter】组合键，得到最后结果，如下图所示。CHAR函数用于返回对应于数字代码的字符，它可将其他类型计算机文件中的代码转换为字符；9E+307为Excel中可以输入的最大数值。例10 快速查找最后非空单元格的位置快速查找最后一个非空单元格的位置的具体操作步骤如下：输入 在单元格F3中输入公式“=MATCH(CHAR(1),B:B,-1)”，如下图所示。 按【Ctrl+Ent

17、er】组合键，查找到的最后一个非空单元格的位置为11，如下图所示。例11 快速定位到空单元格通过创建单元格名称和使用链接函数可以快速定位到空单元格中。下面以具体的实例介绍快速定位到空单元格的方法。2.单击1.输入 新建工作表，从中输入产品信息，单击“公式”选项卡下“定义的名称”组中的“定义名称”按钮，如下图所示。 弹出“新建名称”对话框，在“名称”文本框中输入文字“空单元格”，在“引用位置”填充2.输入单击2.输入2.输入1.输入文本框中输入公式“=OFFSET($B$1,COUNTA($B:$B)+1,)”如下图所示。 单击“确定”按钮，创建动态名称，然后选择单元格F3，从中输入“=HYPE

18、RLINK("#空单元格","快速定位")”，如下图所示。 公式确认后，将在单元格F3中显示链接文字“快速定位”，单击它，将定位到“产品”列下方的空单元格中，如下图所示。例12 快速返回唯一值列表有的工作表中有不少重复的数据信息，要过滤这些重复的信息，让表中显示唯一的数据值，可以采用如下的方法。 打开一张产品信息表，在该表中“产品”列中有重复的产品信息，如下图所示。 在单元格B14中输入公式“=OFFSET($B$2,SMALL(IF(MATCH($B$3:$B$11,$B$3:$B$11,0)=ROW($B$3:$B$11)-2,ROW($B$3:$B

19、$11)-2),ROW(A1),)”，如下图所示。 按【ctrl+shift+enter】组合键，返回产品值“AC001”，使用填充柄进行填充，将得到不重复的值，结果如下图所示。MATCH($B$3:$B$11,$B$3:$B$11,0)返回各产品名称所在的单元格位置，该位置如果为该产品名称所在表中的位置，说明该产品名称不重复，否则产品名称重复。6.3 财务函数实战技巧例1 使用PMT函数计算还款金额输入选择PMT函数返回在固定利率下，投资或贷款的等额分期偿还额。使用该函数计算贷款的还款额的具体操作方法如下： 打开“分期付款计算表”工作表，选择单元格E4，如下图所示。选择 单击“公式”选项卡下

20、“函数库”组中的“插入函数”按钮，弹出“插入函数”对话框，在“选择函数”列表框中选择PMT函数，如下图所示。 单击“确定”按钮，弹出“函数参数”对话框，从中输入参数，如下图所示。 单击“确定”按钮，得到计算结果，用前面所讲的复制公式的方法，复制公式，计算出其他的月偿还金额，如下图所示。PMT函数的语法结构为PMT（rate, nper,pv,fv,type），其中，rate为贷款利率。nper为该项贷款的付款期总数。pv为现值，或一系列未来付款的当前值的累积和，也称为本金。Fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则假设其值为0，也就是一笔贷款的未来值为0。Type为

21、数字 0 或 1，用以指定各期的付款时间是在期初还是期末。如果省略，则默认为0，代表期末付款。例2 使用PMT函数计算存款金额假设在银行存款，要使10年后得到7万元，则计算每月的存款金额，可使用如下的方法得出： 新建工作表，从中输入数据，并在单元格C5中输入公式“=PMT(C4/12,C3*12,0,70000)”如下图所示。输入 按【Ctrl+Enter】组合键，计算出每个月应存款的金额，如下图所示。例3 使用PPMT函数求本金偿还额PPMT函数基于固定利率及等额分期付款方式，用于返回投资在某一给定期间内的本金偿还额。使用该函数计算本金偿还金额的具体操作方法如下： 新建工作表，从中输入数据，

22、选择单元格C5，输入“=PPMT(”，如下图所示。输入设置 按【Shift+F3】组合键，弹出“函数参数”对话框，从中设置参数，如下图所示。拖动 单击“确定”按钮，求得返还的第一年本金额，然后拖动填充柄，填充公式，最终效果如下图所示。例4 使用IPMT函数求本金偿还额IPMT函数基于固定利率及等额分期付款方式，用于返回给定期数内对的利息偿还额。使用该函数计算利息偿还金额的具体操作方法如下： 以上个技巧中的表格为基础，添加“返还利息”列，在单元格D5中输入函数“=IPMT($D$3,B5,$C$3,$B$3)”，如下图所示。填充输入 按【Ctrl+Enter】组合键，得到第一年的返还利息额，通过

23、拖动填充柄，填充公式，得到每年的返还利息，最终效果如下图所示。例5 使用FV函数计算投资价值使用FV函数可以返回投资的现值。现值为一系列未来付款的当前值的累积和。该函数的使用方法如下： 在“投资计算表”中单击单元格F5，然后在其中输入函数“=FV(C5/12,D5,E5)”，如下图所示。输入 按【Enter】键，因为是表格所以自动填充公式计算出结果，如下图所示。该函数的语法结构为FV(rate,nper,pmt,fv,type)，其中，pmt为各期所应支付的金额，其数值在整个年金期间保持不变。通常，pmt 包括本金和利息，但不包括其他费用或税款。如果忽略 pmt，则必须包含 fv 参数。Fv为

24、未来值，或在最后一次支付后希望得到的现金余额，如果省略 fv，则假设其值为零。可以根据保守估计的利率来决定每月的存款额。如果忽略 fv，则必须包含 pmt参数。例6 使用PV函数计算年金的现值假设要购买一种养老保险，购买成本为10万元，该保险可以在20年内于每月末回报800元、投资报酬率为10，要计算该保险的年金现值，其具体的计算方法如下： 新建工作表，从中输入数据，然后单击单元格C7，从中输入函数“=PV(C4/12,C3*12,C2,0)”，如下图所示。输入该函数的语法结构为PV(rate, nper,pmt,fv,type)。 按【Ctrl+Enter】组合键，计算出年金的现值，如下图所

25、示。计算得出的年金现值为82.899.69元，小于10万元，所以购买该保险不划算。例7 用PV函数求一次性支付金额假设要用5年时间分期付款购车，每年预付6000元，银行利率为6.5，要计算如果要一次性支付完，需支付的金额，其具体的计算方法如下： 新建工作表，从中输入数据，然后单击单元格C7，从中输入函数“=PV(C4,C3,C2,1)”，如下图所示。输入 按【Ctrl+Enter】组合键，计算出的一次性支付金额，如下图所示。例8 使用SLN函数计算资产折旧SLN函数用于计算某项资产在一个期间中的线性折旧值。使用该函数计算线性折旧值的具体操作方法如下： 新建工作表，从中输入数据，选择单元格E3，

26、如下图所示。1.输入选择 单击“公式”选项卡下“函数库”组中的“插入函数”按钮，弹出“插入函数”对话框，在“选择函数”列表框中选择SLN函数，如下图所示。选择 单击“确定”按钮，弹出“函数参数”对话框，从中输入参数，然后单击“确定”按钮，得到每年的线性折旧值，如下图所示。选择输入SLN函数的语法结构为SLN(cost,salvage,life)，其中，cost为固定资产原值，salvage为指定固定资产残值，life为折旧周期。例9 用固定余额递减法求资产折旧DB函数用于计算在固定利率下给定日期内的资产折旧值，其具体操作方法如下：选择 新建工作表，从中输入数据，选择单元格C6，如下图所示。 单

27、击编辑栏左侧的“插入函数”按钮，弹出“插入函数”对话框，在“选择函数”列表框中选择DB函数，如下图所示。DB函数的语法结构为DB(cost,salvage,life,period,month)，其中Period为需要计算折旧值的期间，Month为第一年使用的月数，如省略，则代表12个月。设置 单击“确定”按钮，弹出“函数参数”对话框，从中输入参数，然后单击“确定”按钮，得到每年的线性折旧值，如下图所示。设置2.输入1.填充 通过向下拖动该单元格右下角的填充柄，在单元格区域C7:C15填充公式。然后在单元格C16中输入公式“=B3-C3-SUM(C6:C15)”，计算出固定资产在使用周期内最后6

28、个月的折旧值，如下图所示。使用固定余额递减法计算折旧值，最后一期的折旧值应该通过公式“固定资产原值-剩余残值-每年中折旧值之和”来计算。例10 用双倍余额递减法求资产折旧使用DDB函数可以计算固定资产折旧，其具体操作方法如下：3.选择2.单击1.输入 新建工作表，从中输入数据，然后单击“公式”选项卡下“函数库”组中的“财务”下拉按钮，在弹出的下拉面板中选择DDB函数，如下图所示。DDB函数的语法结构为DDB(cost,salvage,life,period,factor)，其中Factor为余额递减速率。如果factor被省略，默认为2（即双倍余额递减法）。 将弹出“函数参数”对话框，从中设置

29、相应的函数，如下图所示。 单击“确定”按钮，得到使用双倍余额递减法计算出的折旧值，然后在单元格区域C7:C11中填充公式，得到的结果如下图所示。选择还可以使用1.5倍或其他余额递减法计算固定资产折旧值。例11 用年限总和折旧法求资产折旧SYD函数用于返回固定资产按年限总和折旧法计算出的指定期内的折旧值。使用该函数计算资产折旧的具体操作方法如下：设置2.输入1.输入设置3.选择2.单击1.输入 新建工作表，从中输入数据，然后单击“公式”选项卡下“函数库”组中的“财务”下拉按钮，在弹出的下拉面板中选择SYD函数，如下图所示。 将弹出“函数参数”对话框，从中设置相应的函数，如下图所示。填充 单击“确

30、定”按钮，得到使用年限综合折旧法计算出的折旧值，然后在单元格区域C7:C12中填充公式，得到的结果如下图所示。 使用SUM函数可以计算出累计的折旧值，如下图所示。例12 用可变递减折旧法求折旧值VDB函数使用双倍余额递减法或其他指定的方法，返回指定的任何期间内（包括部分期间）的资产折旧值。使用该函数计算资产折旧的具体操作方法如下： 新建工作表，从中输入数据，然后单击在单元格D4中输入“=VDB(”，如下图所示。 按【Shift+F3】组合键，将弹出“函数参数”对话框，在其中可以设置其具体的函数参数，如下图所示。 单击“确定”按钮，得出第一个月的资产折旧值，如下图所示。输入输入 在相应的单元格分

31、别输入函数，如下图所示。输入输入输入 计算得出的各资产折旧值，如下图所示。VDB函数的语法结构为VDB(cost,salvage,life,start_period,end_period,factor,no_switch)，其中，Start_period为进行折旧计算的起始期间；End_period为进行折旧计算的截止期间；No_switch为某个逻辑值，当该参数为TRUE时，表示当折旧值大于余额递减计算值时，不会转用直线折旧法，当为FALSE时，即会专用直线折旧法。例13 使用VDB函数计算累计折旧使用VDB函数计算累计的资产折旧值，具体操作方法如下： 新建工作表，从中输入数据，然后单击单元

32、格D6，从中输入函数“=VDB(B3,C3,D3,0,2+1/2,TRUE)”如下图所示。按【Enter】键，将得到第3年第2个季度末的累计资本折旧。然后在单元格D10中输入函数“=VDB(B3,C3,D3,0,2+1/2,1.5,TRUE)”，如下图所示。 按【Ctrl+Enter】组合键，得到使用1.5倍余额递减法求得的第3年第2个季度末的累计资产折旧，如下图所示。例14 使用NPER函数计算投资期数假设现有银行存款2万元，以后每月月初往银行存款1500元，银行的存款利率是3.3%，如果要计算存多久后，银行存款变为10万元，可按如下的方法计算： 新建工作表，从中输入数据，然后单击单元格C7

33、，从中输入函数“=NPER(”，如下图所示。输入设置输入 按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。 单击“确定”按钮，得到计算结果，然后双击该单元格，显示公式，并将其修改为“=NPER(C3/12,C4,C2,C5,1)/12”，按【Ctrl+Enter】组合键，计算的结果如下图所示。修改NPER函数基于固定利率及等额分期付款方式，返回某项投资的总期数。例15 使用RATE函数计算假设5年前向某公司投入资金10万元作为入股资金，投入后每年年终可获分红6000元，现在公司一次性支付40万元，购买这些股份，要计算这笔资金的年利率，可使用RATE函数来计

34、算，其具体的计算方法如下： 新建工作表，从中输入数据，然后单击单元格C7，从中输入函数“=RATE(”，如下图所示。 按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。2.选择1.选择设置输入 单击“确定”按钮，得到年利率，如下图所示。RATE函数用于返回年金的各期利率。RATE函数的语法结构为RATE(nper,pmt,pv,fv,type,guess)，其中, Guess为预期利率，如果省略，则默认为10%。例16 用IRR函数计算内部收益率IRR函数用于返回由数值代表的一组定期现金流的内部收益率。这些现金流必须按固定的间隔产生，如按月或按年。内部收益率

35、为投资的回收利率，其中包含定期支付（负值）和定期收入（正值）。使用该函数计算内部收益率的具体方法如下： 新建工作表，从中输入数据，然后选择单元格C14，按【Shift+F3】组合键，弹出“插入函数”对话框，从中选择IRR函数，如下图所示。设置 单击“确定”按钮，弹出“函数参数”对话框，从中设置函数的参数，如下图所示。输入 单击“确定”按钮，得到投资三年后的内部收益率，然后在单元格C15中输入函数“=IRR(C5:C10)”，如下图所示。 按【Ctrl+Enter】组合键，计算出投资五年后的内部收益率，如下图所示。例17 用XIRR函数计算内部收益率XIRR函数返回一组不定期发生的现金流的内部收

36、益率。使用该函数计算内部收益率的具体操作方法如下：选择2.选择1.输入设置输入 新建工作表，从中输入数据，然后单击单元格C12，从中输入函数“=XIRR(”，如下图所示。 按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。 单击“确定”按钮，得到计算结果，然后在“开始”选项卡下“数字”组中设置该单元格的数字格式为“百分比”，如下图所示。例18 用MIRR函数计算内部收益率假设投资某个项目，所需资金30万元，从银行的贷款利率是4.5%，投资的收益是8.9%，其具体的计算方法如下： 新建工作表，从中相关的输入数据，然后选择单元格E6，如下图所示。 按【shift

37、+F3】组合键，弹出“插入函数”对话框，从中选择MIRR函数，如下图所示。MIRR函数返回某一连续期间内现金流的修正内部收益率。该函数计算充分考虑了投资的成本和现金再投资的收益率。该函数的语法结构为MIRR(values,finance_rate,reinvest_rate)，其中，Values为一个数组或对包含数字的单元格的引用。这些数值中包括支出（负值）及收入（正值）；Finance_rate为现金流中使用的资金支付的利率；Reinvest_rate为将现金流再投资的收益率。设置设置 单击“确定”按钮，弹出“函数参数”对话框，从中输入各相关参数，如下图所示。 单击“确定”按钮，得到投资一年

38、的修正内部收益率，然后向下拖动填充柄，填充公式，结果如下图所示。在7年内，投资项目的内部收益率都为负，所以该投资不可行。例19 用NPV函数计算净现值假设一年前投资5000元，在本年的年末净收入1200元，在第二年的年末净收入达到5000元，第三年的年末净收入达到15000元，折现率8%，要计算其净现值，可使用NPV函数计算，其具体操作方法如下： 新建工作表，从中输入数据，然后单击单元格C11，从中输入函数“=NPV(”，如下图所示。输入 按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。 单击“确定”按钮，输入公式，然后双击该单元格，将公式修改为“=NPV

39、(C9,C6,C7,C8)+C5”得到净现值，如下图所示。NPV函数通过使用贴现率以及一系列未来支出（投资额）和收入额，返回一项投资的净现值。例20 计算期初投资的净现值假设第一年初投资5000元，第一年年末净收入1000元，第二年年末净收入6000元，第三年年末的净收入为120 00元，折现率10%，要计算其净现值，可按如下方法操作： 新建工作表，从中输入数据，然后单击单元格C11，从中输入函数“=NPV(”，如下图所示。输入修改输入设置 按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。Value1, value2, .在时间上必须具有相等间隔，并且都发

40、生在期末。 单击“确定”按钮，输入公式，然后双击该单元格，将公式修改为“=NPV(C9,C6,C7,C8)+C5”得到净现值，如下图所示。Rate为贴现率，通常为必要收益率或加权资本成本；NPV函数的参数为每年末净现金流量，所以在该实例中不能直接将“年初的投资额”作为NPV函数的参数。例21 用XNPV函数计算净现值XNPV函数用于返回一组不定期发生的现金流的净现值，使用该函数计算净现值的具体操作方法如下： 新建工作表，从中输入数据，然后单击单元格C12，从中输入函数“=XNPV(”，如下图所示。设置 按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。 单击

41、“确定”按钮，得到不定期现金流的净现值，结果如下图所示。6.4 文本函数实战技巧例1 从身份证中提取出生日期拖动2.输入设备输入1.输入设备使用MID函数能够从身份证中提取出生日期，具体操作方法如下： 新建工作表，在单元格B2中输入身份证号，在单元格C2中输入函数，如下图所示。MID函数用于返回文本字符串中从指定位置开始的特定数目的字符，语法为：MID(text,start_num,num_chars)。例如，MID(B2,7,4)是指从单元格B2中第7个字符开始提取，提取4个字符。 按【Enter】键，得到的结果如下图所示。例2 用特殊符号补充空位在财务工作中，通常要将输入金额中的空位补齐，

42、在Excel中使用函数可以轻松的办到，具体操作方法如下。 在单元格中输入数据和函数，如下图所示。 按【Ctrl+Enter】组合键，得到公式的计算结果；然后拖动填充并进行公式填充，得到的结果如下图所示。REPT函数用来不断地重复显示某一文本字符串，对单元格进行填充。例3 创建文本直方图使用TEPT函数还可以创建文本直方图，具体操作方法如下。 输入 打开“楼盘概览”工作表，在单元格I5中输入函数“=REPT(“”,INT(H5/100)，如下图所示。 按【Ctrl+Enter】组合键，得到公式的计算结果；然后将公式复制到单元格区域I6:I12中，得到的结果如下图所示。例4 快速统计单词个数如果单

43、元格中的字符是英文单词，因为英文中的每个单词由不同的字母组成，因此不能简单地用LEN函数来统计，这时可用如下的方法来统计单词的个数。2.输入1.输入2.输入1.输入 新建一张工作表，在单元格B16中输入单词，在单元格B18中输入公式“=LEN(B16)-LEN(SUBSTITUTE(B16,” ”,”)+1”，其中SUBSTITUTE函数用于将单元格B6中的空格替换为空,也就是删除空格，LEN函数用于计算字符数，两个LEN函数求得的字符数相减，即为空格数，空格数再加1，即为空格隔开的单词数，如下图所示。输入 按【Enter】键，得出计算结果，如下图所示。LEN函数用于返回文本字符串中的字符数，

44、是Excel中较常用的函数。例5 快速统计字符出现的次数在工作中，有时需要统计单元格中出现某以相同的字符的次数，这时，也可应用LEN函数。其具体操作方法如下： 新建工作表，在其中输入文本内容，然后在单元格B18中输入公式“=（LEN(B16)-LEN(SUBSTITUTE(B16),”风尚”，”)）/2”，该公式是用于计算单元格B16中“风尚”的个数，如下图所示。 按【Ctrl+Enter】组合键，得到计算的结果，如下图所示。例6 用嵌套函数限定产品的价格使用SUM、N和FREQUENCY嵌套函数也可以限定产品的价格，下面仍以上个技巧中的实例为例进行介绍。 在单元格C3中输入公式“=MIN(1

45、9.8,MAX(12.1,D4)”，如下图所示， 拖动输入输入 按【Ctrl+Enter】组合键，得到公式计算结果，如下图所示。使用MIN和MAX函数的嵌套，通过将价格与最大值、最小值的比较来实现限价。例7 巧妙生成工资条打印工资条是现代办公中经常遇到的问题，在Excel中使用函数也可以自动创建工资条，然后打印出来即可。其操作方法如下： 在Excel中，创建“公司员工工资表”，如下图所示。 选择单元格B19，并在其中输入公式“=IF(MOD(ROW(C3)-2,3),OFFSET($B$3,(MOD(ROW(C3)-3,3)>0)*ROUND(ROW(C3)-2)/3,),COLUMN(

46、C3)-2),"")”如下图所示。 按【Ctrl+Enter】组合键，得到公式的计算结果，然后向右拖动填充柄，再向下拖动填充柄，得到完整的工资条，如下图所示。MOD(ROW(C3)-3,3)>0)*ROUND(ROW(C3)-2)/3,)的含义是将MOD函数求得的余数和0进行比较，如果逻辑值为FALSE，不管ROUND函数求得的数值为多少，它们相乘的结果始终为零，这样就保证了OFFSET函数所返回的行数不变，即显示列标题。如果MOD函数求得余数不为零，则和0进行比较后，得到逻辑值TRUE,与ROUND函数相乘后，得到下移行数，从而通过OFFSET函数显示从单元格B3开

47、始向下移一定行数后的单元格数据。例8 巧妙使用循环引用记录时间显示3.输入1.选择填充输入设置如果能够在工作表中记录数据输入的时准确时间，将给用户的工作带来很大的好处。在Excel中使用函数能够轻松实现用户的要求，其具体操作步骤如下： 新建工作表，从中输入数据，将“时间”列的数字公式设置为“时间”，然后在可修改“时间”的数字格式为“常规”，如下图所示。 在单元格C3中输入公式“=IF(ISBLANK(B3),"",IF(C3="",NOW(),C3)”，如下图所示。要正确得出输入的时间，应该先输入并复制公式，然后在“收入”列中输入数据。 确认公式后，使用

48、填充柄填充公式，如下图所示。2.选中 打开“Excel选项”对话框，在左侧的列表中选择“公式”选项，在右侧的“计算选项”选项区中选中“启用迭代计算”复选框，并将“最多迭代次数”设置为1，如下图所示。 在“收入”列中输入收入额，在“时间”列的相应单元格中将显示输入时间，如下图所示。例9 巧用公式屏蔽出错信息在填充公式时，有时候可能会因为公式中引用的单元格信息改变，导致其不满足公式的参数定义等原因，导致公式计算结果错误，例如，在“合格产品统计”工作表中，由于要统计合格产品的个数，所以使用了公式“=C4*E4”，在产品合格率未检测出来时，使用该公式计算，就会显示错误信息，如下图所示。输入输入要避免这

49、种情况的发生，可以通过对公式进行完善，巧妙地解决单元格中显示错误的公式计算结果，其具体操作方法如下： 在单元格F4中输入公式“=IF(ISNUMBER(C4*E4),C4*E4,"无法计算")”，如下图所示。显示输入 得到计算结果后，拖动填充柄复制公式，单元格F7中将不会得到错误信息，效果如下图所示。 在单元格C9中输入公式“=COS(B9*PI()/180”或者“COS(RADIANS(B9)”，都将求得角度为30°的余弦，如下图所示。COS函数的语法结构为：COS(number)。Number为需要求余弦的角度，以弧度表示。如果角度是以度表示的，则可将其乘以P

50、I()/180或使用RADIANS函数将其转换成弧度。6.5 统计函数实战技巧例1 计算某个单元格值出现的次数用COUNTIF函数可以计算单元格区域中满足给定条件的单元格的个数，下面以求“GHU系列产品种类”为例，介绍该函数的使用方法： 打开“商品价格表”，从中输入数据，然后在单元格F21中输入公式“=COUNTIF(F13:F19,"GHU")”，如下图所示。输入 按【Ctrl+Enter】组合键，得到公式计算结果，如下图所示。例2 求满足多个条件的单元格个数显示求满足多个条件的单元格个数，最常用的方法是使用COUNT和IF函数嵌套来实现。下面以计算“员工考核成绩表”中各

51、科成绩都在75分以上的员工人数为例，介绍其具体方法。 新建工作表，从中输入数据，然后在单元格E12中输入公式“=COUNT(IF(C4:C10>75)*(D4:D10>75)*(E4:E10>75),B4:E10)”，如下图所示。输入 按【Ctrl+shift+Enter】组合键，得到公式计算结果，计算出满足条件的单元格有3个，如下图所示。该实例中的IF条件句用于返回各科成绩均大于75分的单元格区域。再使用COUNT函数计算数值结果的个数。例3 巧用COUNTIFS函数求个数COUNTIFS函数是Excel 2007中新增的函数，该函数也可用于计算某个单元格区域中满足多重条件

52、的单元格数目。其具体操作方法如下： 打开“员工考核成绩表”，在单元格D5中输入公式“=COUNTIFS(C4:C10,">75",D4:D10,">75",E4:E10,">75")”，如下图所示。 按【Ctrl+Enter】组合键，将得到计算的结果，统计出满足条件的人数有1人，如下图所示。使用COUNTIFS函数时，只有当单元格区域中的数据满足为其指定的所有相应条件时才对其进行计算。例4 对考核成绩实现同分同名次排列在用Excel统计成绩时，根据成绩高低进行排序，按序列自动填充出名次。当成绩相同时，填充出名次还是不

53、同的，如下图所示。此时，可以利用使用RANK排位函数实现同分同名次的操作，具体操作方法如下： 打开“员工考核成绩表”，添加“总分”和“排名”列，在单元格G4中输入函数“=RANK(F4,F$4: F$10,0)”，如下图所示。填充输入输入 按【Ctrl+Enter】组合键后，会得到该行总成绩的分数排名。使用拖动填充柄的方法，向下填充公式即可排名，效果如下图所示。例5 用COUNTIF 函数也求名次排列使用COUNTIF计数函数也可以对考核成绩实现同分同名次排列，具体操作方法如下： 在G4单元格中输入函数“=COUNTIF(F$4:F$10,">"&F4)+1”

54、，如下图所示。输入 按【Ctrl+Enter】组合键，显示名次。然后通过拖动该单元格的填充柄向下填充即可实现排名，如下图所示。例6 实现同分同名次排列的嵌套函数采用IF函数和COUNT函数的嵌套照样能够实现同分同名次排列的嵌套，具体操作方法如下： 在单元格G4中输入“1”，然后在G5单元格中输入函数“=IF(F5=F4,G4, COUNT($F$4:F5)”，如下图所示。输入输入输入 按【Ctrl+Enter】组合键，该单元格中显示排名。拖动该单元格中的填充柄向下填充公式，得到排名结果，如下图所示。对于前两种方法，在没有将总分进行排序的情况下，也可以得出正确的排名；而最后一种方法只有在对总分进行降序排序时才能得出正确的排名结果。例7 用FREQUENCY函数求频率填充FREQUENCY频率函数用于计算数值在某个单元格区域内的出现频率，然后返回一个垂直数组。例如，计算不同的分段中考核成绩的个数，其具体使用方法如下：输入 新