神马文学网Excel常用函數的應用技巧
来源：百度文库 编辑：神马文学网 时间：2024/04/29 11:07:53
前言：Office 97(Office 2000)是我们处理日常事务和办公自动化的首选软件,其中的Excel是一个优秀的制表软件，它不但可以完成多种表格的制作，而且还有很强大的计算功能，特别是利用它所提供的九大类约200个内置函数，可以完成各种复杂的运算和数据处理。
这九类函数列表如下：
1 逻辑函数 AND
FALSE
IF
NOT
OR
TURE
2 日期与时间函数 DATE NOW
DATEVALUE SECOND
DAY TIME
DAYS360 TIMEVALUE
HOUR TODAY
MINUTE WEEKDAY
MONTH YEAR
3 文字函数
ASC LENB TEXT
BIG5 LOWER TRIM
CHAR MID UPPER
CLEAN MIDB USDOLLAR
CODE PROPER VALUE
CONCATENATE REPLACE
DOLLAR REPLACEB
EXACT REPEAT
FIND RIGHT
FINDB RIGHTB
FIXED SEARCH
LEFT SEARCHB
LEFTB SUBSTITUTE
LEN T
4 检视与参照函数
ADRESS INDIRECT
AREAS LOOKUP
CHOOSE MATCH
COLUMN OFFSET
COLUMNS ROW
GETPOVOTDATA ROWS
HLOOKUP TRANSPOSE
HYPERLINK VLOOKUP
INDEX
5 信息函数 CELL INNUMBER
ERROR.TYPE ISREF
INFO ISTEXT
ISBLANK N
ISERR NA
ISLOGICAL PHONETIC
ISNA TYPE
ISNONTEXT
6 数据库函数
DAVERAGE DPRODUCT
DCOUNT DSTDEV
DCOUNTA DSTDEVP
DGET DSUM
DMAX DVAR
DMIN DVARP
7 财务函数
DB NPV
DDB PMT
FV PPMT
IPMT PV
IRR RATE
ISPMT SLN
MIRR SYD
NPER VDB
8 数学与三角函数 ABS LOG10 SUMIF
ACOS MDETERM SUMPRODUCT
ACOSH MINVERSE SUMSQ
ASIN MMULT SUMX2PY2
ASINH MOD SUMX2MY2
ATAN ODD SUMXMY2
ATAN2 PI TAN
ATANH POWER TANH
CEILING PRODUCT TRUNC
COMBIN RADIANS
COS RAND
COSH ROMAN
DEGREES ROUND
EVEN ROUNDDOWN
EXP ROUNDUP
FACT SIGN
FLOOR SINH
INT SQRT
LN SUBTOTAL
LOG SUM
9 统计函数
AVEDEV GAMMAINV PERMUT
AVERAGE GAMMALN POISSON
AVERAGEA GEOMEAN PROB
BETADIST GROWTH QUARTILE
BETAINV HARMEAN RANK
BINOMDIST HYPGEOMDIST RSQ
CHIDIST INTERCEPT SKEW
CHIINV KURT SLOPE
CHITEST LARGE SMALL
CONFIDENCE LINEST STONDARDIZE
CORREL LOGEST STDEV
COUNT LOGINV STDEVA
COUNTA LOGNORMDIST STDEVP
COUNTBLANK MAX STDEVPA
COUNTIF MAXA STEYX
COVAR MEDIAN TDIST
CRITBINOM MIN TINV
DEVSQ MINA TREND
EXPONDIST MODE TRIMMEAN
FDIST NEGBINOMDIST TTEST
FINV NORMDIST VAR
FISHER NORMINV VARA
FISHERINV NORMSDIST VARP
FORECAST NORMSINV VARA
FREQUENCY PEARSON WEIBULL
FTEST PERCENTILE ZETST
GAMMADIST PERCENTRANK
这九类函数中逻辑函数不仅在EXCEL中是基础，基本的逻辑运算也是各种编程语言的基础。我们将着重讲解。对于其它常用函数也会介绍一些，如“数学”类函数中的“SUM”、“SUMIF”、“SUBTOTAL”；“统计”类函数中的“AVERAGE”、“MAX”、“MIN”、“COUNT”、“COUNTA”、“COUNTBLANK”、“COUNTIF”、“MEDIAN”、“MODE”、“RANK”；“查找”类函数中“VLOOKUP”、“MATCH”、“INDEX”；“财务”类函数中的最常用的三个函数“PMT”、“FV”、“RATE”等函数。
自定义函数
虽然Excel中已有大量的内置函数，但有时可能还会碰到一些计算无函数可用的情况。假如某公司采用一个特殊的数学公式计算产品购买者的折扣，如果有一个函数来计算岂不更方便？下面就说一下如何创建这样的自定义函数。
自定义函数，也叫用户定义函数，是Excel最富有创意和吸引力的功能之一，下面我们在Visual Basic模块中创建一个函数。 在下面的例子中，我们要给每个人的金额乘一个系数，如果是上班时的工作餐，就打六折；如果是加班时的工作餐，就打五折；如果是休息日来就餐，就打九折。首先打开“工具”菜单，单击“宏”命令中的“Visual Basic编辑器”，进入Visual Basic编辑环境，在“工程－VBAobject”栏中的当前表上单击鼠标右键，选择“插入”-“模块”，在右边栏创建下面的函数rrr，代码如下： Function rrr(tatol, rr) If rr = "上班" Then rrr = 0.6 * tatol ElseIf rr = "加班" Then rrr = 0.5 * tatol ElseIf rr = "休息日" Then rrr = 0.9 * tatol End If End Function （如图9）。
图９
这时关闭编辑器，只要我们在相应的列中输入rrr(F２，B２)，那幺打完折后的金额就算出来了（如图10）。
图10
宏的应用
宏是一个指令集，用来告诉EXCEL来完成用户指定的动作。宏类似于计算机程序，但是它是完全运行于EXCEL之中的，我们可以使用宏来完成枯燥的、频繁的重复性工作。 宏完成动作的速度比用户自己做要快得多。例如，我们可以创建一个宏，用来在工作表的每一行上输入一组日期，并在每一单元格内居中对齐日期，然后对此行应用边框格式。我们还可以创建一个宏，在“页面设置”对话框中指定打印设置并打印文档。
由于宏病毒的影响和对编程的畏惧心理，使很多人不敢用“宏”，或是不知道什幺时候可以找宏来帮忙。其实你尽管放心大胆地去用，如果只是用“录制宏”的方法，根本就没有什幺难的，只是把一些操作象用录音机一样录下来，到用的时候，只要执行这个宏，系统就会把那操作再执行一遍。
下面给出了宏的应用场合，只要用“录制宏”就可以帮你完成任务，而不需要编程。如果想对所录制的宏再进行编辑，就要有一定的VBA知识了。
* 设定一个每个工作表中都需要的固定形式的表头；
* 将单元格设置成一种有自己风格的形式；
* 每次打印都固定的页面设置；
* 频繁地或是重复地输入某些固定的内容，比如排好格式的公司地址、人员名单等；
* 创建格式化表格；
* 插入工作表或工作薄等。
需要指出的是，EXCEL中的宏与WORD中的宏有些不同之处，对于录制的操作，它会记住单元格的坐标（即所有的引用都是绝对的），所以在涉及到与位置有关的操作时，要格外注意。如果相用相对引用，可以借助于Offset方法，比如下面的语句： ActiveCell.Offset(1,0). range("A1").select 宏的应用是很广的，上面提到的只是一点点，如果真的用起来，你会发现它有更丰富的内容和更灵活的应用方法。
VBA函数简介
VBA是Microsoft Office组件的内置编程语言， 其强大的功能在EXCEL中体现得淋漓尽致。如果能掌握基本的VBA语句，再结合EXCEL的各种功能，会有如鱼得水之感。我们将举例说明VBA 在EXCEL中的应用。
Msgbox函数，shell函数，for…next 语句，if then 语句，shells函数, RGB函数等。
我们以课堂教学为主，以下资料仅供参考。
在Excel中利用函数自动填写月份
月度报表的标题中常含有月份数值。笔者在工作中利用函数自动填写月份，感觉十分方便。
??笔者使用excel制作的一个报表（模板）标题是“烟台市农机局×月份在职职工工资表”。这个表是当月修改，当月打印。所以，笔者采用以下两个步骤输入标题：
??1．将标题所占据的各单元格合并；
??2．在合并的单元格中输入：=“烟台市农机局”＆month（now（））＆“月份在职职工工资表”。
??这样，每月编写（修改）打印工资表时，函数month（）和函数now（）便自动将机内的月份数返回并写入标题中。
??另一个报表的标题是“×月份收入支出情况表”。与第一个工资表不同的是，此表编报、汇总并打印的是上一个月的收入／支出情况，所以不能直接套用工资表中对两个函数的应用。笔者采用下述方法解决了这一问题，即在合并后的单元格中输入：=if（month（now（））=1，12，month（now（））－1）＆“月份收入支出情况”。这样，当1月份编报此表时，标题中自动显示“12月份收入／支出情况表”；而在2～12月编报报表时，标题中自动显示上月的月份数值，例如2月份编报的报表标题是“1月份收入／支出情况表”。
EXCEL的六大“条件”功能
>
EXCEL97/2000，除了具有强大的表格功能外，更具有强大的数据统计与处理功能，尤其是使用其“条件”功能，常常能收到事半功倍的效果，在此笔者就同大家谈谈EXCEL的条件功能（为方便起见，笔者在此以如图 1包含工程基本情况的二维表格为例）。
一、条件求和。
1、单条件求和：统计C1公司施工的工程总建筑面积，并将结果放在E18单元格中，我们只要在E18单元格中输入公式“=SUMIF（D2：D17,"C1公司",E2：E17）”即完成这一统计。
友情提醒：如果对EXCEL的函数不太熟悉，在单元格中直接输入公式有困难，我们可以用“插入函数”命令（或直接按工具栏上的“粘贴函数”命令按钮），选中你需要的函数后，按其提示操作即可完成公式的输入。
2、多条件求和：统计C2公司施工的质量等级为“合格”的工程总建筑面积，并将结果放在E19单元格中，我们用“条件求和”功能来实现：
①选“工具→向导→条件求和”命令（若没有此命令选项，可以用“加载宏”的方式来增加这一命令选项），在弹出的对话框中，按右下带“―”号的按钮（此时对话框变成类似工具条的窗口形式浮于桌面上），用鼠标选定D1：I17区域，并按窗口右边带红色箭头的按钮（恢复对话框状态）。
②按“下一步”，在弹出的对话框中，按“求和列”右边的下拉按钮选中“建筑面积”项，再分别按“条件列、运算符、比较值”右边的下拉按钮，依次选中“施工单位”、“=”（默认）、“C2公司”选项，最后按“添加条件”按钮。重复前述操作，将“条件列、运算符、比较值”设置为“质量等级”、“=”、“合格”，并按“添加条件”按钮。
③两次点击“下一步”，在弹出的对话框中，按右下带“―”号的按钮，用鼠标选定E19单元格，并按窗口右边带红色箭头的按钮。
④按“完成”按钮，此时符合条件的汇总结果将自动、准确地显示在 E19单元格中。
友情提醒：上述操作实际上是输入了一个数组公式，我们也可以先在 E19单元格中直接输入公式：=SUM(IF(D2:D17="C2公司",IF(I2:I17="合格",E2:E17)))，然后在按住Ctrl+Shift键（非常关键！！！）的同时按下Enter键，也可以达到上述多条件求和之目的。
二、条件计数。
统计质量等级为“合格”工程的数目，并将结果存放在I18单元格中，在I18单元格中输入公式：=COUNTIF(I2:I17,"合格")，当按下确定按钮后，统计结果――数字5即自动在I18单元格中显示出来。
三、条件格式。
将工程造价在500万元（含500万元）以上的工程造价数值以红颜色显示出来：
①选中F2至F17单元格；
②用“格式→条件格式”命令，打开“条件格式”对话框（如图 2）；
③按第二个方框旁的下拉按钮，选中“大于或等于”选项，再在后面的方框中输入数字500；
④按上述对话框中的“格式”按钮，打开“单元格格式”对话框（如图 3），在“文字”卡片下，按“颜色”旁的下拉按钮，将文字颜色设置成红色后，按“确定”按钮关闭“单元格格式”对话框，回到“条件格式”对话框中；
⑤按“确定”按钮。
此时造价大于500万元的工程，其造价数值将以红色显示在单元格中。
友情提醒：继续按上述“条件格式”对话框中的“添加”按钮，可以设置多级“条件”，如“工程造价大于500万元以红色显示，大于1000万元以蓝色显示”等（可以对一个单元格设置三个条件）。
四、条件函数。
我们在对竣工工程观感质量进行评定后，当观感得分率超过85%，该工程质量等级可以评定为“优良”。
①在I2单元格中输入公式：=IF(H2>=85,"优良",IF(H2>0,"合格","未竣工"))；
②选中I2单元格，将鼠标移至该单元格右下角成细十字线时（我们称之为“填充柄”），按住左键向下拖拉，将上公式复制到I列下面的单元格中；
③以后当工程竣工后，我们将该工程的观感得分率填入H列内相应的单元格中，则质量等级自动显示在相应工程的I列单元格中（若没有填观感得分率，则I列相应单元格中显示“未竣工”字样）。
友情提醒：在第①步输入公式时，条件判断数字按从大到小的顺序排列，否则会出错。
五、条件查找。
当工程基本情况表中登记的工程非常之多时，如果我们用普通浏览的方式查找某个具体的工程是非常困难的，此时我们可以用“查找”功能来快速定位：
① 用“编辑查找”命令，打开“查找”对话框（如图 4）；
②在“查找内容”下面的方框中输入该工程的关键词；
③对“搜索方式、搜索范围”作适当设置后，连续按“查找下一个”按钮，符合模糊条件的单元格依次被选中，即可快速查找到某个具体的工程。
友情提醒：在“查找内容”下面的方框中输入的内容不一定非得与某一单元格数值完成相同的文本，如查找“C1公司”的某个工程，只要输入“C1”即可以进行模糊查找了。
六、条件筛选。
如果要打印C1公司施工的工程基本情况一览表，我们可以用“自动筛选”功能来实现：
①选中表格中的任一单元格，用“数据→筛选→自动筛选”命令，启动“自动筛选”功能（列标题旁出现一个下拉按钮，如图 5）；
②按“施工单位”旁的下拉按钮，选中C1公司，则表格中只显示C1公司所施工的工程；
③接上打印机，装上打印纸，按“打印”按钮，即可打印出C1公司施工的工程基本情况一览表（如图 5）；
④打印结束后，重复第①的操作关闭“自动筛选”功能，则全部工程又重新显示在表格中。
友情提醒：此处的“条件筛选”可以进行“多级”筛选，如再上述筛选的基础上，再按“进度”旁的下拉按钮，选中“在建”选项，则表格中只剩下“C1公司”施工的所有“在建”工程。
其实，EXCEL的条件功能还有很多，如果有感兴趣的朋友不妨一试，若发现其什幺更好的功能，别忘了告诉一声，我在此先谢过了。
Excel函数应用之财务函数
像统计函数、工程函数一样，在Excel中还提供了许多财务函数。财务函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值。这些财务函数大体上可分为四类：投资计算函数、折旧计算函数、偿还率计算函数、债券及其它金融函数。它们为财务分析提供了极大的便利。使用这些函数不必理解高级财务知识，只要填写变量值就可以了。在下文中，凡是投资的金额都以负数形式表示，收益以正数形式表示。
??在介绍具体的财务函数之前，我们首先来了解一下财务函数中常见的参数：
??未来值 (fv)--在所有付款发生后的投资或贷款的价值。
??期间数 (nper)--为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。
??付款 (pmt)--对于一项投资或贷款的定期支付数额。其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其它费用及税款。
??现值 (pv)--在投资期初的投资或贷款的价值。例如，贷款的现值为所借入的本金数额。
??利率 (rate)--投资或贷款的利率或贴现率。
??类型 (type)--付款期间内进行支付的间隔，如在月初或月末，用0或1表示。
??日计数基准类型（basis）--为日计数基准类型。Basis为0 或省略代表US (NASD) 30/360，为1代表实际天数/实际天数 ，为2代表实际天数/360，为3代表实际天数/365 ，为4代表欧洲30/360。
??接下来，我们将分别举例说明各种不同的财务函数的应用。在本文中主要介绍各类型的典型财务函数，更多的财务函数请参看附表及相关书籍。如果下文中所介绍的函数不可用，返回错误值 #NAME?，请安装并加载"分析工具库"加载宏。操作方法为：
?? 1、在"工具"菜单上，单击"加载宏"。
?? 2、在"可用加载宏"列表中，选中"分析工具库"框，再单击"确定"。
?? 一、投资计算函数
??投资计算函数可分为与未来值fv有关，与付款pmt有关，与现值pv有关，与复利计算有关及与期间数有关几类函数。
?? 1、与未来值fv有关的函数--FV、FVSCHEDULE
?? 2、与付款pmt有关的函数--IPMT、ISPMT、PMT、PPMT
?? 3、与现值pv有关的函数--NPV、PV、XNPV
?? 4、与复利计算有关的函数--EFFECT、NOMINAL
?? 5、与期间数有关的函数--NPER
??在投资计算函数中，笔者将重点介绍FV、NPV、PMT、PV函数。
??（一） 求某项投资的未来值FV
??在日常工作与生活中，我们经常会遇到要计算某项投资的未来值的情况，此时利用Excel函数FV进行计算后，可以帮助我们进行一些有计划、有目的、有效益的投资。FV函数基于固定利率及等额分期付款方式，返回某项投资的未来值。
??语法形式为FV(rate,nper,pmt,pv,type)。其中rate为各期利率，是一固定值，nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数，pv为各期所应付给（或得到）的金额，其数值在整个年金期间（或投资期内）保持不变，通常Pv包括本金和利息，但不包括其它费用及税款，pv为现值，或一系列未来付款当前值的累积和，也称为本金，如果省略pv，则假设其值为零，type为数字0或1，用以指定各期的付款时间是在期初还是期末，如果省略t，则假设其值为零。
??例如：假如某人两年后需要一笔比较大的学习费用支出，计划从现在起每月初存入2000元，如果按年利2.25%，按月计息（月利为2.25%/12），那幺两年以后该账户的存款额会是多少呢？
??公式写为：FV(2.25%/12, 24,-2000,0,1)
图1
（二） 求投资的净现值NPV
?? NPV函数基于一系列现金流和固定的各期贴现率，返回一项投资的净现值。投资的净现值是指未来各期支出（负值）和收入（正值）的当前值的总和。
??语法形式为：NPV(rate,value1,value2, ...) 其中，rate为各期贴现率，是一固定值；value1,value2,...代表1到29笔支出及收入的参数值，value1,value2,...所属各期间的长度必须相等，而且支付及收入的时间都发生在期末。需要注意的是：NPV按次序使用value1,value2，来注释现金流的次序。所以一定要保证支出和收入的数额按正确的顺序输入。如果参数是数值、空白单元格、逻辑值或表示数值的文字表示式，则都会计算在内；如果参数是错误值或不能转化为数值的文字，则被忽略，如果参数是一个数组或引用，只有其中的数值部分计算在内。忽略数组或引用中的空白单元格、逻辑值、文字及错误值。
??例如，假设开一家电器经销店。初期投资￥200,000，而希望未来五年中各年的收入分别为￥20,000、￥40,000、￥50,000、￥80,000和￥120,000。假定每年的贴现率是8%（相当于通贷膨胀率或竞争投资的利率），则投资的净现值的公式是：
?? =NPV(A2, A4:A8)+A3
??在该例中，一开始投资的￥200,000并不包含在v参数中，因为此项付款发生在第一期的期初。假设该电器店的营业到第六年时，要重新装修门面，估计要付出￥40,000，则六年后书店投资的净现值为：
?? =NPV(A2, A4:A8, A9)+A3
??如果期初投资的付款发生在期末，则 投资的净现值的公式是：
?? =NPV(A2, A3:A8)
图2
（三） 求贷款分期偿还额PMT
?? PMT函数基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。PMT函数可以计算为偿还一笔贷款，要求在一定周期内支付完时，每次需要支付的偿还额，也就是我们平时所说的"分期付款"。比如借购房贷款或其它贷款时，可以计算每期的偿还额。
??其语法形式为：PMT(rate,nper,pv,fv,type) 其中，rate为各期利率，是一固定值，nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数，pv为现值，或一系列未来付款当前值的累积和，也称为本金，fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零），type为0或1，用以指定各期的付款时间是在期初还是期末。如果省略type，则假设其值为零。
??例如，需要10个月付清的年利率为8%的￥10,000贷款的月支额为：
?? PMT（8%/12,10,10000） 计算结果为：-￥1,037.03。
??（四） 求某项投资的现值PV
?? PV函数用来计算某项投资的现值。年金现值就是未来各期年金现在的价值的总和。如果投资回收的当前价值大于投资的价值，则这项投资是有收益的。
??其语法形式为：PV(rate,nper,pmt,fv,type) 其中Rate为各期利率。Nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。Pmt为各期所应支付的金额，其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其它费用及税款。Fv 为未来值，或在最后一次支付后希望得到的现金余额，如果省略 fv，则假设其值为零（一笔贷款的未来值即为零）。Type用以指定各期的付款时间是在期初还是期末。
??例如，假设要购买一项保险年金，该保险可以在今后二十年内于每月末回报￥600。此项年金的购买成本为80,000，假定投资回报率为8%。那幺该项年金的现值为：
?? PV(0.08/12, 12*20,600,0) 计算结果为：￥-71,732.58。
??负值表示这是一笔付款，也就是支出现金流。年金（￥-71,732.58）的现值小于实际支付的（￥80,000）。因此，这不是一项合算的投资。
图3
二、 折旧计算函数
??折旧计算函数主要包括AMORDEGRC、AMORLINC、DB、DDB、SLN、SYD、VDB。这些函数都是用来计算资产折旧的，只是采用了不同的计算方法。这里，对于具体的计算公式不再赘述，具体选用哪种折旧方法，则须视各单位情况而定。
?? 三、偿还率计算函数
??偿还率计算函数主要用以计算内部收益率，包括IRR、MIRR、RATE和XIRR几个函数。
??（一） 返回内部收益率的函数--IRR
?? IRR函数返回由数值代表的一组现金流的内部收益率。这些现金流不一定必须为均衡的，但作为年金，它们必须按固定的间隔发生，如按月或按年。内部收益率为投资的回收利率，其中包含定期支付（负值）和收入（正值）。
??其语法形式为IRR(values,guess) 其中values为数组或单元格的引用，包含用来计算内部收益率的数字，values必须包含至少一个正值和一个负值，以计算内部收益率，函数IRR根据数值的顺序来解释现金流的顺序，故应确定按需要的顺序输入了支付和收入的数值，如果数组或引用包含文本、逻辑值或空白单元格，这些数值将被忽略；guess为对函数IRR计算结果的估计值，excel使用迭代法计算函数IRR从guess开始，函数IRR不断修正收益率，直至结果的精度达到0.00001%，如果函数IRR经过20次迭代，仍未找到结果，则返回错误值#NUM！，在大多数情况下，并不需要为函数IRR的计算提供guess值，如果省略guess，假设它为0.1（10%）。如果函数IRR返回错误值#NUM！，或结果没有靠近期望值，可以给guess换一个值再试一下。
??例如，如果要开办一家服装商店，预计投资为￥110,000，并预期为今后五年的净收益为：￥15,000、￥21,000、￥28,000、￥36,000和￥45,000。分别求出投资两年、四年以及五年后的内部收益率。
图4
在工作表的B1：B6输入数据"函数.xls"所示，计算此项投资四年后的内部收益率IRR（B1：B5）为-3.27%；计算此项投资五年后的内部收益率IRR（B1：B6）为8.35%;计算两年后的内部收益率时必须在函数中包含guess，即IRR（B1：B3，-10%）为-48.96%。
二） 用RATE函数计算某项投资的实际赢利
??在经济生活中，经常要评估当前某项投资的运作情况，或某个新企业的现状。例如某承包人建议你贷给他30000元，用作公共工程建设资金，并同意每年付给你9000元，共付五年，以此作为这笔贷款的最低回报。那幺你如何去决策这笔投资？如何知道这项投资的回报率呢？对于这种周期性偿付或是一次偿付完的投资，用RATE函数可以很快地计算出实际的赢利。其语法形式为RATE(nper,pmt,pv,fv,type,guess)。
??具体操作步骤如下：
?? 1、选取存放数据的单元格，并按上述相似的方法把此单元格指定为"百分数"的格式。
?? 2、插入函数RATE，打开"粘贴函数"对话框。
?? 3、在"粘贴函数"对话框中，在"Nper"中输入偿还周期5(年),在"Pmt"中输入7000(每年的回报额)，在"Pv"中输入－30000(投资金额)。即公式为=RATE(5,9000,-30000)
?? 4、确定后计算结果为15.24％。这就是本项投资的每年实际赢利，你可以根据这个值判断这个赢利是否满意，或是决定投资其它项目，或是重新谈判每年的回报。
?? 四、债券及其它金融函数
??债券及其它金融函数又可分为计算本金、利息的函数，与利息支付时间有关的函数、与利率收益率有关的函数、与修正期限有关的函数、与有价证券有关的函数以及与证券价格表示有关的函数。
?? 1、计算本金、利息的函数--CUMPRINC、ACCRINT、ACCRINTM、CUMIPMT、COUPNUM
?? 2、与利息支付时间有关的函数--COUPDAYBS、COUPDAYS、COUPDAYSNC、COUPNCD、COUPPCD
?? 3、 与利率收益率有关的函数--INTRATE、ODDFYIELD、ODDLYIELD、TBILLEQ、TBILLPRICE、TBILLYIELD、YIELD、YIELDDISC、YIELDMAT
?? 4、与修正期限有关的函数--DURATION、MDURATION
?? 5、与有价证券有关的函数--DISC、ODDFPRICE、ODDLPRICE、PRICE、PRICEDISC、PRICEMAT、RECEIVED
?? 6、与证券价格表示有关的函数--DOLLARDE、DOLLARFR
??在债券及其它金融函数中，笔者将重点介绍函数ACCRINT、CUMPRINC、DISC。
??（一）求定期付息有价证券的应计利息的函数ACCRINT
?? ACCRINT函数可以返回定期付息有价证券的应计利息。
??其语法形式为ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis)
??其中issue为有价证券的发行日，first_interest为有价证券的起息日，settlement为有价证券的成交日，即在发行日之后，有价证券卖给购买者的日期，rate为有价证券的年息票利率，par为有价证券的票面价值，如果省略par，函数ACCRINT就会自动将par设置为￥1000，frequency为年付息次数，basis为日计数基准类型。
??例如，某国库券的交易情况为：发行日为2008年3月1日；起息日为2008年8月31日；成交日为2008年5月1日，息票利率为10.0%；票面价值为￥1,000；按半年期付息；日计数基准为30/360，那幺应计利息为：
图5
（二）求本金数额CUMPRINC
?? CUMPRINC函数用于返回一笔货款在给定的st到en期间累计偿还的本金数额。其语法形式为CUMPRINC(rate,nper,pv,start_period,end_period,type) 其中rate为利率，nper为总付款期数，pv为现值，start_period为计算中的首期，付款期数从1开始计数，end_period为计算中的末期，type为付款时间类型。
??例如，一笔住房抵押贷款的交易情况如下：年利率为9.00%；期限为30年；现值为￥125，000。由上述已知条件可以计算出：r=9.00%/12=0.0075，np=30*12=360。
图6
那幺该笔贷款在第下半年偿还的全部本金之中（第7期到第12期）为： =CUMPRINC(A2/12,A3*12,A4,7,12,0)计算结果为：-436.568194。
??该笔贷款在第一个月偿还的本金为：=CUMPRINC(A2/12,A3*12,A4,1,1,0)计算结果为：-68.27827118。
??（三） 求有价证券的贴现率DISC
?? DISC函数返回有价证券的贴现率。
??其语法形式为DISC(settlement,maturity,pr,redemption,basis) 其中settlement为有价证券的成交日，即在发行日之后，有价证券卖给购买者的日期，maturity为有价证券的到日期，到期日是有价证券有效期截止时的日期，pr为面值为"￥100"的有价证券的价格，redemption为面值为"￥100"的有价证券的清偿价格，basis为日计数基准类型。
??例如：某债券的交易情况如下：成交日为99年3月18日，到期日为99年8月7日，价格为￥48.834，清偿价格为￥52，日计数基准为实际天数/360。那幺该债券的贴现率为： DISC("99/3/18","99/8/7",48.834,52,2) 计算结果为：0.154355363。
函数名称 函数说明 语法形式
ACCRINT 返回定期付息有价证券的应计利息。 ACCRINT(issue,first_interest, settlement,rate,par,frequency, basis)
ACCRINTM 返回到期一次性付息有价证券的应计利息。 ACCRINTM(issue,maturity,rate, par,basis)
AMORDEGRC 返回每个会计期间的折旧值。此函数是为法国会计系统提供的。 AMORDEGRC(cost,date_purchased, first_period,salvage,period, rate,basis)
AMORLINC 返回每个会计期间的折旧值，该函数为法国会计系统提供。 AMORLINC(cost,date_purchased, first_period,salvage,period, rate,basis)
COUPDAYBS 返回当前付息期内截止到成交日的天数。 COUPDAYBS(settlement,maturity, frequency, basis)
COUPDAYS 返回成交日所在的付息期的天数。 COUPDAYS(settlement,maturity, frequency, basis)
COUPDAYSNC 返回从成交日到下一付息日之间的天数。 COUPDAYSNC(settlement,maturity, frequency, basis)
COUPNCD 返回成交日过后的下一付息日的日期。 COUPNCD(settlement,maturity, frequency, basis)
COUPNUM 返回成交日和到期日之间的利息应付次数，向上取整到最近的整数。 COUPNUM(settlement,maturity, frequency, basis)
COUPPCD 返回成交日之前的上一付息日的日期。 COUPPCD(settlement,maturity, frequency, basis)
CUMIPMT 返回一笔贷款在给定的 start-period 到 end-period 期间累计偿还的利息数额。 CUMIPMT(rate,nper,pv,start_period, end_period,type)
CUMPRINC 返回一笔贷款在给定的 start-period 到 end-period 期间累计偿还的本金数额。 CUMPRINC(rate,nper,pv,start_period, end_period,type)
DB 使用固定余额递减法，计算一笔资产在给定期间内的折旧值。 DB(cost,salvage,life,period,month)
DDB 使用双倍余额递减法或其它指定方法，计算一笔资产在给定期间内的折旧值。 DDB(cost,salvage,life,period,factor)
DISC 返回有价证券的贴现率。 DISC(settlement,maturity,pr, redemption,basis)
DOLLARDE 将按分数表示的价格转换为按小数表示的价格，如证券价格，转换为小数表示的数字。 DOLLARDE(fractional_dollar, fraction)
DOLLARFR 将按小数表示的价格转换为按分数表示的价格。如证券价格，转换为分数型数字。 DOLLARFR(decimal_dollar, fraction)
DURATION 返回假设面值 $100 的定期付息有价证券的修正期限。期限定义为一系列现金流现值的加权平均值，用于计量债券价格对于收益率变化的敏感程度。 DURATION(settlement,maturity, coupon yld,frequency,basis)
EFFECT 利用给定的名义年利率和一年中的复利期次，计算实际年利率。 EFFECT(nominal_rate,npery)
FV 基于固定利率及等额分期付款方式，返回某项投资的未来值。 FV(rate,nper,pmt,pv,type)
FVSCHEDULE 基于一系列复利返回本金的未来值。函数 FVSCHDULE 用于计算某项投资在变动或可调利率下的未来值。 FVSCHEDULE(principal,schedule)
INTRATE 返回一次性付息证券的利率。 INTRATE(settlement,maturity, investment,redemption,basis)
IPMT 基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的利息偿还额。 IPMT(rate,per,nper,pv,fv,type)
IRR 返回由数值代表的一组现金流的内部收益率。 IRR(values,guess)
ISPMT 计算特定投资期内要支付的利息。 ISPMT(rate,per,nper,pv)
MDURATION 返回假设面值 $100 的有价证券的 Macauley 修正期限。 MDURATION(settlement,maturity, coupon,yld,frequency,basis)
MIRR 返回某一连续期间内现金流的修正内部收益率。 MIRR(values,finance_rate, reinvest_rate)
NOMINAL 基于给定的实际利率和年复利期数，返回名义年利率。 NOMINAL(effect_rate,npery)
NPER 基于固定利率及等额分期付款方式，返回某项投资（或贷款）的总期数。 NPER(rate, pmt, pv, fv, type)
NPV 通过使用贴现率以及一系列未来支出（负值）和收入（正值），返回一项投资的净现值。 NPV(rate,value1,value2, ...)
ODDFPRICE 返回首期付息日不固定的面值 $100 的有价证券的价格 ODDFPRICE(settlement,maturity, issue,first_coupon,rate,yld, redemption, frequency,basis)
ODDFYIELD 返回首期付息日不固定的有价证券（长期或短期）的收益率。 ODDFYIELD(settlement,maturity, issue,first_coupon,rate,pr, redemption, frequency,basis)
ODDLPRICE 返回末期付息日不固定的面值 $100 的有价证券（长期或短期）的价格。 ODDLPRICE(settlement,maturity, last_interest,rate,yld,redemption, frequency,basis)
ODDLYIELD 返回末期付息日不固定的有价证券（长期或短期）的收益率。 ODDLYIELD(settlement,maturity, last_interest,rate,pr,redemption, frequency,basis)
PMT 基于固定利率及等额分期付款方式，返回贷款的每期付款额。 PMT(rate,nper,pv,fv,type)
PPMT 基于固定利率及等额分期付款方式，返回投资在某一给定期间内的本金偿还额。 PPMT(rate,per,nper,pv,fv,type)
PRICE 返回定期付息的面值 $100 的有价证券的价格。 PRICE(settlement,maturity, rate,yld,redemption,frequency, basis)
PRICEDISC 返回折价发行的面值 $100 的有价证券的价格。 PRICEDISC(settlement,maturity, discount,redemption,basis)
PRICEMAT 返回到期付息的面值 $100 的有价证券的价格。 PRICEMAT(settlement,maturity, issue,rate,yld,basis)
PV 返回投资的现值。现值为一系列未来付款的当前值的累积和。例如，借入方的借入款即为贷出方贷款的现值。 PV(rate,nper,pmt,fv,type)
RATE 返回年金的各期利率。函数 RATE 通过迭代法计算得出，并且可能无解或有多个解。 RATE(nper,pmt,pv,fv,type,guess)
RECEIVED 返回一次性付息的有价证券到期收回的金额。 RECEIVED(settlement,maturity, investment,discount,basis)
SLN 返回某项资产在一个期间中的线性折旧值。 SLN(cost,salvage,life)
SYD 返回某项资产按年限总和折旧法计算的指定期间的折旧值。 SYD(cost,salvage,life,per)
TBILLEQ 返回国库券的等效收益率。 TBILLEQ(settlement,maturity, discount)
TBILLPRICE 返回面值 $100 的国库券的价格。 TBILLPRICE(settlement,maturity, discount)
TBILLYIELD 返回国库券的收益率。 TBILLYIELD(settlement,maturity,pr)
VDB 使用双倍余额递减法或其它指定的方法，返回指定的任何期间内（包括部分期间）的资产折旧值。函数 VDB 代表可变余额递减法。 VDB(cost,salvage,life,start_period, end_period,factor,no_switch)
XIRR 返回一组现金流的内部收益率，这些现金流不一定定期发生。若要计算一组定期现金流的内部收益率，请使用函数 IRR。 XIRR(values,dates,guess)
XNPV 返回一组现金流的净现值，这些现金流不一定定期发生。若要计算一组定期现金流的净现值，请使用函数 NPV。 XNPV(rate,values,dates)
YIELD 返回定期付息有价证券的收益率，函数 YIELD 用于计算债券收益率。 YIELD(settlement,maturity,rate, pr,redemption,frequency,basis)
YIELDDISC 返回折价发行的有价证券的年收益率。 YIELDDISC(settlement,maturity, pr,redemption,basis)
YIELDMAT 返回到期付息的有价证券的年收益率。 YIELDMAT(settlement,maturity, issue,rate,pr,basis)
Excel自动计算与数据校验
但凡提到会计核算问题，很多人动辄就会想到要用财务软件。然而杀鸡焉用牛刀，其实有时候不必请动这个巨无霸，用Excel足矣。作为当前最流行的办公自动化软件，Excel绝非浪得虚名，其灵活的自动计算和数据校验功能，使得它在工资和财务报表的编制方面大有作为。在工资和财务中，要到对大量的数据进行汇总计算，通常要在计算之前对某些特定的列进行判断，根据判断结果来校验数据的正确性，最后决定要采用那种方式进行计算。还要具有一定的容错性，能够自动捕获一些不符合要求的输入，并给出相应的出错信息，以便操作者能够及时加以纠正。以上这些功能，专门的财务软件自然可以胜任，但未免有一种大炮打蚊子的感觉。而对Excel，只需稍加挖掘其潜力，虽然是小米加步枪，也能强过飞机大炮。好，闲话少说，下面以工资报表编制为实例，向大家介绍如何运用Excel2000强大的计算与数据校验功能。
首先我们创建一个工资统计报表，该表分成两部分，第一部分是工资主表部分，它反映工资表的各项内容（如图1）；第二部分是该工资表的基础数据部分，这里称为基础表（如图2）。这两部分合在一张工作表中完成。
实现步骤：
1、创建如图1所示的工资报表样式和如图2所示的工资报表基础资料部分的样式。
2、修改手工录入列的字段名：显然最后做好的工作表要用密码加以保护，以防泄密，加密固然需要，但也不能误伤好人，应该既有集中、又有民主。有些字段应该允许操作人员进行实时修改（像姓名、性别、出生年月、工作年月、岗位代码、技术职务代码等），应该对这些字段网开一面。非常简单，鼠标单击这些字段所在列的顶端字母栏，选中它，再单击击鼠标右键，在弹出菜单中选择“设置单元格格式”，切换至“保护”选项卡，确保“锁定”复选框清空（如图3）。
3、设置自动计算列的背景色：为了让自动计算各列以更加醒目显示出来，提示操作人员，这些列不用修改，我们可以修改这些列的背景色。单击这些列的顶端字母栏，选中它，再单击击鼠标右键，在弹出菜单中选择“设置单元格格式”，切换至“图案”选项卡，将单元格底纹设置为蓝色（当然也可以是其它颜色）（如图4）所示：
4、创建基础表：一些字段（如岗位名称、技术职务、岗位等级、岗位系列、岗位工资、年功能津贴、专业技术职等级津贴）是从图2中所示的基础部分取得数据或是经计算得出，因此不需要操作人员进行手工输入。我们在同一张工作表的右边创建如图2所示的表格，并输入必要的内容。
5、 好，现在要讲到精华部分了──利用Excel函数设置自动填充数据列的计算公式， Excel提供了大量的应用函数，可谓十八般武器样样俱全。随便拿出一种足以制胜。就举一个查找函数LOOKUP的例子吧。
拿到一样武器，总得熟悉一下它的性能，函数 LOOKUP可以在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值（详细说明请参见有关帮助文档）。
（1）设置“岗位名称”F列的自动填充公式：选中“岗位名称”这一列的单元格F6，在工具栏下的公式输入框中输入如下公式：
=IF(E6="","",LOOKUP(T(E6),R$6:R$16,S$6:S$16))（如图5）所示：
这个公式所表达的意思是，F6单元格的内容是根据前一个单元“岗位代码”E6单元格中内容，完成自动的填充，如果E6为空时，则F6为空，否则从R列的6-16行中查找E6单元格中的值，如果找到则将S列中6-16行与之对应行的值填充到F6中。如我们在E6中输入了“1401”，则F6中自动显示出“县局局长及相当职务”。同样，我们可以将此公式复制到F列的所有F6以下的行中，使所有F列所有单元都具有自动填充功能。完成：“岗位名称”列
（2）以同样的方法，我们可以设置以下各列的公式：
技术职务（H列）：=IF(G6="","",LOOKUP(T(G6),W$5:W$16,X$5:X$16))
岗位等级（I列）：=IF(E6="","",LOOKUP(T(E6),R$6:R$16,U$6:U$16))
岗位系列（J列）：=IF(E6="","",LOOKUP(T(E6),R$6:R$16,T$6:T$16))
专业技术职务资格等级津贴（M列）：
=IF(G6="","",LOOKUP(T(G6),W$5:W$16,Y$5:Y$16))
（3）岗位工资（K列）公式设置：由于岗位工资的设置不仅仅是和E列的值有关，而且要根据O列的值（布尔类型）进行判断，如果O列的值为“是”时，说明该人员是“组长”，岗位工资要增加40，所以公式设置如下：
=IF(O6="是",LOOKUP(E6,R$6:R$16,V$6:V$16)+40,
LOOKUP(E6,R$6:R$16,V$6:V$16))
（4）年功津贴（L列）公式设置：年功能津贴是该工作人员参加工作的年限乘以每年津贴10元，所以L6单元格的公式为：
=IF(D6="","",(2002-INT(D6))*10)
（5）工资合计（N列）公式设置：这是一个求和公式，N6单元格的公式设置如为：=K6+L6+M6或=SUM（K6：M6）
（6）年龄（P列）公式设置：这里在进行计算这前，需要验证输入数据的合法性，必须对C6单元格输入的值进行校验，在1940-1985这间（很显然，不在这个年龄范围的，要幺应该已经退休，要幺就算是非法雇用童工），如果不是，那幺就会在P列相应的单元格中显示"╳"，以提醒操作员所输入的数据不正确。该P6单元格的公式如下
=IF(AND(INT(C6)>1940,INT(C6)<1985),2002-INT(C6),"╳")
（7）工龄（Q列）公式设置：同样，在计算工龄之前要保证参加工作时间在1955-2002年之间，否则会在Q列相应的单元格中显示"╳"，以提醒操作员所输入的数据不正确。该列Q6单元格的公式设置如下：
=IF(OR(INT(D6)<1955,INT(D6)>2002),"╳",2002-INT(D6))
完成以上工作之后，我们可以在允计操作的单元格中输入数据，相应的单元格就会非常听话地自动填上数据。如果要对工资进行调整，只需要改动基础数据部分的表格内容，那幺工资表会自动更新，再用不着费时费心地逐项修改整个工资表了。但我们对编制好的工资表进行预览时会发现，右边基础部分的数据也显示出来了。这样肯定是不行的，既不符合要求，也易造成基础数据的意外修改。但是Excel考虑周到，为我们提供了隐藏表格各列的方法。用鼠标左键单击要隐藏的列的顶端位置，选中该列，然后单击鼠标右键，在弹出菜单中选择“隐藏”，那幺这个列就不会再出来捣乱。这样我们就可以隐藏基础部分的表格，最后形成的工资报表能够基本符合安全的需要。
将工作表设置为保护状态是为了使操作员只能对允许的单元格进行修改，而对工资表自动计算部分则应拒绝随意修改，以保证数据的完整性。设置方法是：选择菜单“工具”—“保护”—“保护工作表”，输入密码（如图6）。
确定后，就完成了工作表的保护。
至此，一个集基础数据管理、自动计算，且满足一定安全需要的工资报表系统就完成了。
Excel函数应用之信息函数
在Excel函数中有一类函数，它们专门用来返回某些指定单元格或区域等的信息，比如单元格的内容、格式、个数等，这一类函数我们称为信息函数。在本文中，我们将对这一类函数做以概要性了解，同时对于其中一些常用的函数及其参数的应用做出示例。
??一、用于返回有关单元格格式、位置或内容的信息的函数CELL
??CELL函数用于返回某一引用区域的左上角单元格的格式、位置或内容等信息。其语法形式为，CELL(info_type,reference) 其中Info_type为一个文本值，指定所需要的单元格信息的类型。Reference则表示要获取其有关信息的单元格。如果忽略，则在 info_type 中所指定的信息将返回给最后更改的单元格。
??首先看一下，info_type 的可能值及相应的结果。
??
类型 Info_type 返回结果
位置 "address" 引用中第一个单元格的引用，文本类型。
"col" 引用中单元格的列标。
"row" 引用中单元格的行号。
"filename" 包含引用的文件名（包括全部路径），文本类型。如果包含目标引用的工作表尚未保存，则返回空文本 ("")。
格式 "color" 如果单元格中的负值以不同颜色显示，则为 1，否则返回 0。
"format" 与单元格中不同的数字格式相对应的文本值。下表列出不同格式的文本值。如果单元格中负值以不同颜色显示，则在返回的文本值的结尾处加“-”；如果单元格中为正值或所有单元格均加括号，则在文本值的结尾处返回“()”。
"parentheses" 如果单元格中为正值或全部单元格均加括号，则为 1，否则返回 0。
"prefix" 与单元格中不同的“标志前缀”相对应的文本值。如果单元格文本左对齐，则返回单引号 (‘)；如果单元格文本右对齐，则返回双引号 (")；如果单元格文本居中，则返回插入字符 (^)；如果单元格文本两端对齐，则返回反斜线 ()；如果是其它情况，则返回空文本 ("")。
"protect" 如果单元格没有锁定，则为 0；如果单元格锁定，则为 1。
"type" 与单元格中的数据类型相对应的文本值。如果单元格为空，则返回“b”。如果单元格包含文本常量，则返回“l”；如果单元格包含其它内容，则返回“v”。
"width" 取整后的单元格的列宽。列宽以默认字号的一个字符的宽度为单位。
内容 "contents" 引用中左上角单元格的值：不是公式。
??再看一下当info_type 为"format"，以及引用为用内置数字格式设置的单元格时，函数 CELL 返回文本值的情况。
图1
函数CELL主要用于与其它电子表格程序兼容。在随后的示例中我们来学习一下如何使用CELL函数来获取单元格的格式、位置及内容的信息。
??例：想要获知单元格A1到B4区域内比如行号、列宽、单元格内容等信息。
图2
二、用于计算区域内空白单元格的个数COUNTBLANK
??COUNTBLANK用于计算指定单元格区域中空白单元格的个数。其语法形式为COUNTBLANK(range) 其中Range为需要计算其中空白单元格个数的区域。需要注意的是，
??即使单元格中含有返回值为空文本 ("")的公式，该单元格也会计算在内，但包含零值的单元格不计算在内。
??在如图所示的例子中，单元格B3包括公式=IF(A3＜30,"",A3)，但该公式计算返回的值为空文本""，所以该单元格被计算为空单元格。而单元格A3为零值的单元格，不计作空单元格。
??试比较图3-A与图3-B的结果的区别，两者的差别在于图3-B中单元格B3的公式为=IF(A3＞30,"",A3)，计算后返回的结果为0，因此不计作空单元格。
图3A
图3B
三、返回对应于错误类型的数字的函数ERROR.TYPE
??ERROR.TYPE返回对应于 Microsoft Excel 中某一错误值的数字，或者，如果没有错误则返回 #N/A。语法形式为ERROR.TYPE(error_val) 其中Error_val为需要得到其标号的一个错误值。尽管 error_val 可以为实际的错误值，但它通常为一个单元格引用，而此单元格中包含需要检测的公式。以下即为error_val的函数返回结果。
图4
还记得逻辑函数IF吗？在函数 IF 中可以使用 ERROR.TYPE 检测错误值，并返回文本字符串（如，消息）来取代错误值。具体参看示例。
图5
四、返回有关当前操作环境的信息的函数INFO
??INFO函数用于返回有关当前操作环境的信息。其语法形式为INFO(type_text) 其中Type_text为文本，指明所要返回的信息类型。关于Type_text所返回的具体结果参看下表。
Type_text 返回
"directory" 当前目录或文件夹的路径。
"memavail" 可用的内存空间，以字节为单位。
"memused" 数据占用的内存空间。
"numfile" 打开的工作簿中活动工作表的数目。
"origin" A1-样式的绝对引用，文本形式，加上前缀“$A:”，与 Lotus 1-2-3 的 3.x 版兼容。以当前滚动位置为基准，返回窗口中可见的最右上角的单元格。
"osversion" 当前操作系统的版本号，文本值。
"recalc" 当前的重新计算方式，返回“自动”或“手动”。
"release" Microsoft Excel 的版本号，文本值。
"system" 操作系统名称：Macintosh = "mac" Windows = "pcdos"
"totmem" 全部内存空间，包括已经占用的内存空间，以字节为单位。
??举例说明如何利用INFO函数获知当前操作环境的信息。
图6
五、用来检验数值或引用类型的函数--IS类函数
IS类函数是指用来检验数值或引用类型的工作表函数，在Excel中一共有九个此类函数。就几个函数包括：
??（1）ISBLANK 如果值为空，则返回 TRUE
??（2）ISERR 如果值为除 #N/A 以外的任何错误值，则返回 TRUE
??（3）ISERROR 如果值为任何错误值，则返回 TRUE
??（4）ISLOGICAL 如果值为逻辑值，则返回 TRUE
??（5）ISNA 如果值为 #N/A 错误值，则返回 TRUE
??（6）ISNONTEXT 如果值不是文本，则返回 TRUE
??（7）ISNUMBER 如果值为数字，则返回 TRUE
??（8）ISREF 如果值为引用，则返回 TRUE
??（9）ISTEXT 如果值为文本，则返回 TRUE
??这些函数，概括为 IS 类函数，可以检验数值的类型并根据参数取值返回 TRUE 或 FALSE。例如，如果数值为对空白单元格的引用，函数 ISBLANK 返回逻辑值 TRUE，否则返回 FALSE。其语法形式为 函数名（value）其中Value为需要进行检验的数值。针对不同的IS类函数分别为：空白（空白单元格）、错误值、逻辑值、文本、数字、引用值或对于以上任意参数的名称引用。
??需要说明的是IS 类函数的参数 value 是不可转换的。例如，在其它大多数需要数字的函数中，文本值"19"会被转换成数字 19。然而在公式 ISNUMBER("19") 中，"19"并不由文本值转换成别的类型的值，函数 ISNUMBER 返回 FALSE。 IS 类函数主要用于检验公式计算结果。当它与函数 IF 结合在一起使用时，可以提供一种方法用来在公式中查出错误值。
图7
六、检验参数奇偶性的函数ISEVEN与ISODD
??ISEVEN与ISODD为检验参数奇偶性的函数。其中ISEVEN是当参数 number 为偶数时返回 TRUE，否则返回 FALSE。而ISODD则恰恰相反，如果参数 number 为奇数，返回 TRUE，否则返回 FALSE。
??关于这两个函数的具体用法请参看示例。
图8
七、返回转化为数值后的值得函数N
??函数N为返回转化为数值后的值。其语法形式为N(value) 其中Value为要转化的值。函数 N 可以转化下表列出的值：
图9
需要注意的是：一般情况下不必在公式中使用函数 N，因为 Excel 将根据需要自动对值进行转换。提供此函数是为了与其它电子表格程序兼容。Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。而Excel for the Macintosh 使用另外一个默认日期系统。
??关于函数N的具体用法可从以下示例中更详细地了解。
图10
八、返回错误值#N/A的函数NA
??NA函数用于返回错误值 #N/A。错误值 #N/A 表示"无法得到有效值"。建议使用 NA 标志空白单元格。在没有内容的单元格中输入 #N/A，可以避免不小心将空白单元格计算在内而产生的问题（当公式引用到含有 #N/A 的单元格时，会返回错误值 #N/A）。
??其语法形式为NA( )。
??需注意的是在函数名后面必须包括圆括号，否则，Microsoft Excel 无法识别该函数。也可直接在单元格中键入 #N/A。提供 NA 函数是为了与其它电子表格程序兼容。
??九、返回数值的类型的函数TYPE
??函数TYPE可用来返回数值的类型。当某一个函数的计算结果取决于特定单元格中数值的类型时，可使用函数 TYPE。其语法形式为TYPE(value) 其中Value可以为任意 Microsoft Excel 数值，如数字、文本以及逻辑值等等。
??要说明的是当使用能接受不同类型数据的函数（例如函数 ARGUMENT 和函数 INPUT）时，函数 TYPE 十分有用。可以使用函数 TYPE 来查找函数或公式所返回的数据是何种类型。可以使用 TYPE 来确定单元格中是否含有公式。TYPE 仅确定结果、显示或值的类型。如果某个值是一个单元格引用，它所引用的另一个单元格中含有公式，则 TYPE 将返回此公式结果值的类型。
图11
以上，我们对Excel函数的信息函数做了大致的了解。信息函数是用来返回某些指定单元格或区域等的信息，比如单元格的内容、格式、个数等的一类函数。在实际应用中，通常是与逻辑函数IF等配合使用来达到对单元格信息的确定。
巧用Excel函数，减少数据冗余
在当今的计算机应用领域中，微软的Office 2000以其阵容整齐、功能强大，成为广大用户最基本、最常用的软件。在Office 2000中，Excel则又是以制表、计算、数据分析功能见长。
在用Excel做数据处理时，经常需要录入有关人员的身份证号码、出生年月、性别、参加工作时间、工龄等信息。其实这些信息中有不少在身份证号码中已经包括了，大可不必重复输入。以下，通过Excel几个函数的使用介绍，来说明三点：
（1）自动从身份证号码中提取出生年月、性别信息。
（2）自动从参加工作时间中提取工龄信息。
（3）根据工龄的大小来确定名次。
在下表中，录入的数据仅两列：身份证号码、参加工作时间，相应的数据类型为：文本、日期。其它数据如出生年月、性别、工龄、名次等均是利用Excel 2000的函数及公式表达式的运算，实现自动提取的。
A B C D E F
1 身份证号码 出生年月 性别 参加工作时间 工龄 名次
2 340111630306701 1963年03月06日 男 1980年8月 21 2
3 352012197810295026 1978年10月29日 女 1997年3月 4 3
4 221217520419509 1952年04月19日 男 1970年5月 31 1
说明：上表中A、B、C、D、E、F……表示的是Excel工作表的列号，1、2、3、4……表示的是Excel工作表的行号。
一、身份证号码
众所周道，当今的身份证号码有15/18位之分。早期签发的身份证号码是15位的，现在签发的身份证由于年份的扩展（由两位变为四位）和末尾加了效验码，就成了18位。这两种身份证号码将在相当长的一段?*谀诠泊妗Ａ街稚矸葜ず怕氲暮迦缦拢?br />（1）15位的身份证号码：1~6位为地区代码，7~8位为出生年份(2位)，9~10位为出生月份，11~12位为出生日期，15位(即最后一位)为性别(奇数为男，偶数为女)。
（2）18位的身份证号码：1~6位为地区代码，7~10位为出生年份(4位)，11~12位为出生月份，13~14位为出生日期，17位(即倒数第二位)为性别(奇数为男，偶数为女)，18位(即最后一位)为效验位。
二、有关函数
要实现数据的自动提取，将用到以下的8个Excel函数,简介如下：
（1）IF（logical_test, value_if_true,value_if_false）：根据逻辑表达式测试的结果，返回相应的值。IF函数允许嵌套。
（2）LEN(text)：返回文本字符串中字符个数。
（3）CONCATENATE(text1,text2……)：将若干个文字项合并至一个文字项中。
（4）MID(text,start_num,num_chars)：从文本字符串中指定的起始位置起，返回指定长度的字符。
（5）MOD(number,divisor)：返回两数相除后的余数。
（6）TODAY（）：返回计算机系统内部的当前日期。
（7）YEAR（serial_number）：返回日期序列数对应的年份数。
（8）RANK（number,ref,order）：返回指定数字在一列数字中的排位。
三、公式表达式
（1）B2=IF(LEN(A2)=15,CONCATENATE("19",MID(A2,7,2),"年",MID(A2,9,2),"月",MID(A2,11,2),"日"),CONCATENATE(MID(A2,7,4),"年",MID(A2,11,2),"月",MID(A2,13,2),"日"))
（2）C2=IF(LEN(A2)=15,IF(MOD(MID(A2,15,1),2)=1,"男","女"),IF(MOD(MID(A2,17,1),2)=1,"男","女"))
（3）E2=YEAR(TODAY())-YEAR(D2)
（4）F2=RANK(E2,$E$2:$E$4)
说明：
（1）将上述四个公式表达式顺序输入至表格第二行的B2、C2、E2、F2单元格中，求出表中数据第一行的结果。
（2）分别移光标至B2、C2、E2、F2四个单元格的右下角，呈黑色“+”符号时，向下拖曳鼠标即可求出其它各单元格的值。
（3）在F2单元格的公式中，为了确保数据的比较范围在公式的复制中保持不变，采用了绝对引用——在单元格的行列标号前，均加上“$”符号。
这九类函数列表如下：
1 逻辑函数 AND
FALSE
IF
NOT
OR
TURE
2 日期与时间函数 DATE NOW
DATEVALUE SECOND
DAY TIME
DAYS360 TIMEVALUE
HOUR TODAY
MINUTE WEEKDAY
MONTH YEAR
3 文字函数
ASC LENB TEXT
BIG5 LOWER TRIM
CHAR MID UPPER
CLEAN MIDB USDOLLAR
CODE PROPER VALUE
CONCATENATE REPLACE
DOLLAR REPLACEB
EXACT REPEAT
FIND RIGHT
FINDB RIGHTB
FIXED SEARCH
LEFT SEARCHB
LEFTB SUBSTITUTE
LEN T
4 检视与参照函数
ADRESS INDIRECT
AREAS LOOKUP
CHOOSE MATCH
COLUMN OFFSET
COLUMNS ROW
GETPOVOTDATA ROWS
HLOOKUP TRANSPOSE
HYPERLINK VLOOKUP
INDEX
5 信息函数 CELL INNUMBER
ERROR.TYPE ISREF
INFO ISTEXT
ISBLANK N
ISERR NA
ISLOGICAL PHONETIC
ISNA TYPE
ISNONTEXT
6 数据库函数
DAVERAGE DPRODUCT
DCOUNT DSTDEV
DCOUNTA DSTDEVP
DGET DSUM
DMAX DVAR
DMIN DVARP
7 财务函数
DB NPV
DDB PMT
FV PPMT
IPMT PV
IRR RATE
ISPMT SLN
MIRR SYD
NPER VDB
8 数学与三角函数 ABS LOG10 SUMIF
ACOS MDETERM SUMPRODUCT
ACOSH MINVERSE SUMSQ
ASIN MMULT SUMX2PY2
ASINH MOD SUMX2MY2
ATAN ODD SUMXMY2
ATAN2 PI TAN
ATANH POWER TANH
CEILING PRODUCT TRUNC
COMBIN RADIANS
COS RAND
COSH ROMAN
DEGREES ROUND
EVEN ROUNDDOWN
EXP ROUNDUP
FACT SIGN
FLOOR SINH
INT SQRT
LN SUBTOTAL
LOG SUM
9 统计函数
AVEDEV GAMMAINV PERMUT
AVERAGE GAMMALN POISSON
AVERAGEA GEOMEAN PROB
BETADIST GROWTH QUARTILE
BETAINV HARMEAN RANK
BINOMDIST HYPGEOMDIST RSQ
CHIDIST INTERCEPT SKEW
CHIINV KURT SLOPE
CHITEST LARGE SMALL
CONFIDENCE LINEST STONDARDIZE
CORREL LOGEST STDEV
COUNT LOGINV STDEVA
COUNTA LOGNORMDIST STDEVP
COUNTBLANK MAX STDEVPA
COUNTIF MAXA STEYX
COVAR MEDIAN TDIST
CRITBINOM MIN TINV
DEVSQ MINA TREND
EXPONDIST MODE TRIMMEAN
FDIST NEGBINOMDIST TTEST
FINV NORMDIST VAR
FISHER NORMINV VARA
FISHERINV NORMSDIST VARP
FORECAST NORMSINV VARA
FREQUENCY PEARSON WEIBULL
FTEST PERCENTILE ZETST
GAMMADIST PERCENTRANK
这九类函数中逻辑函数不仅在EXCEL中是基础，基本的逻辑运算也是各种编程语言的基础。我们将着重讲解。对于其它常用函数也会介绍一些，如“数学”类函数中的“SUM”、“SUMIF”、“SUBTOTAL”；“统计”类函数中的“AVERAGE”、“MAX”、“MIN”、“COUNT”、“COUNTA”、“COUNTBLANK”、“COUNTIF”、“MEDIAN”、“MODE”、“RANK”；“查找”类函数中“VLOOKUP”、“MATCH”、“INDEX”；“财务”类函数中的最常用的三个函数“PMT”、“FV”、“RATE”等函数。
自定义函数
虽然Excel中已有大量的内置函数，但有时可能还会碰到一些计算无函数可用的情况。假如某公司采用一个特殊的数学公式计算产品购买者的折扣，如果有一个函数来计算岂不更方便？下面就说一下如何创建这样的自定义函数。
自定义函数，也叫用户定义函数，是Excel最富有创意和吸引力的功能之一，下面我们在Visual Basic模块中创建一个函数。 在下面的例子中，我们要给每个人的金额乘一个系数，如果是上班时的工作餐，就打六折；如果是加班时的工作餐，就打五折；如果是休息日来就餐，就打九折。首先打开“工具”菜单，单击“宏”命令中的“Visual Basic编辑器”，进入Visual Basic编辑环境，在“工程－VBAobject”栏中的当前表上单击鼠标右键，选择“插入”-“模块”，在右边栏创建下面的函数rrr，代码如下： Function rrr(tatol, rr) If rr = "上班" Then rrr = 0.6 * tatol ElseIf rr = "加班" Then rrr = 0.5 * tatol ElseIf rr = "休息日" Then rrr = 0.9 * tatol End If End Function （如图9）。
图９
这时关闭编辑器，只要我们在相应的列中输入rrr(F２，B２)，那幺打完折后的金额就算出来了（如图10）。
图10
宏的应用
宏是一个指令集，用来告诉EXCEL来完成用户指定的动作。宏类似于计算机程序，但是它是完全运行于EXCEL之中的，我们可以使用宏来完成枯燥的、频繁的重复性工作。 宏完成动作的速度比用户自己做要快得多。例如，我们可以创建一个宏，用来在工作表的每一行上输入一组日期，并在每一单元格内居中对齐日期，然后对此行应用边框格式。我们还可以创建一个宏，在“页面设置”对话框中指定打印设置并打印文档。
由于宏病毒的影响和对编程的畏惧心理，使很多人不敢用“宏”，或是不知道什幺时候可以找宏来帮忙。其实你尽管放心大胆地去用，如果只是用“录制宏”的方法，根本就没有什幺难的，只是把一些操作象用录音机一样录下来，到用的时候，只要执行这个宏，系统就会把那操作再执行一遍。
下面给出了宏的应用场合，只要用“录制宏”就可以帮你完成任务，而不需要编程。如果想对所录制的宏再进行编辑，就要有一定的VBA知识了。
* 设定一个每个工作表中都需要的固定形式的表头；
* 将单元格设置成一种有自己风格的形式；
* 每次打印都固定的页面设置；
* 频繁地或是重复地输入某些固定的内容，比如排好格式的公司地址、人员名单等；
* 创建格式化表格；
* 插入工作表或工作薄等。
需要指出的是，EXCEL中的宏与WORD中的宏有些不同之处，对于录制的操作，它会记住单元格的坐标（即所有的引用都是绝对的），所以在涉及到与位置有关的操作时，要格外注意。如果相用相对引用，可以借助于Offset方法，比如下面的语句： ActiveCell.Offset(1,0). range("A1").select 宏的应用是很广的，上面提到的只是一点点，如果真的用起来，你会发现它有更丰富的内容和更灵活的应用方法。
VBA函数简介
VBA是Microsoft Office组件的内置编程语言， 其强大的功能在EXCEL中体现得淋漓尽致。如果能掌握基本的VBA语句，再结合EXCEL的各种功能，会有如鱼得水之感。我们将举例说明VBA 在EXCEL中的应用。
Msgbox函数，shell函数，for…next 语句，if then 语句，shells函数, RGB函数等。
我们以课堂教学为主，以下资料仅供参考。
在Excel中利用函数自动填写月份
月度报表的标题中常含有月份数值。笔者在工作中利用函数自动填写月份，感觉十分方便。
??笔者使用excel制作的一个报表（模板）标题是“烟台市农机局×月份在职职工工资表”。这个表是当月修改，当月打印。所以，笔者采用以下两个步骤输入标题：
??1．将标题所占据的各单元格合并；
??2．在合并的单元格中输入：=“烟台市农机局”＆month（now（））＆“月份在职职工工资表”。
??这样，每月编写（修改）打印工资表时，函数month（）和函数now（）便自动将机内的月份数返回并写入标题中。
??另一个报表的标题是“×月份收入支出情况表”。与第一个工资表不同的是，此表编报、汇总并打印的是上一个月的收入／支出情况，所以不能直接套用工资表中对两个函数的应用。笔者采用下述方法解决了这一问题，即在合并后的单元格中输入：=if（month（now（））=1，12，month（now（））－1）＆“月份收入支出情况”。这样，当1月份编报此表时，标题中自动显示“12月份收入／支出情况表”；而在2～12月编报报表时，标题中自动显示上月的月份数值，例如2月份编报的报表标题是“1月份收入／支出情况表”。
EXCEL的六大“条件”功能
>
EXCEL97/2000，除了具有强大的表格功能外，更具有强大的数据统计与处理功能，尤其是使用其“条件”功能，常常能收到事半功倍的效果，在此笔者就同大家谈谈EXCEL的条件功能（为方便起见，笔者在此以如图 1包含工程基本情况的二维表格为例）。
一、条件求和。
1、单条件求和：统计C1公司施工的工程总建筑面积，并将结果放在E18单元格中，我们只要在E18单元格中输入公式“=SUMIF（D2：D17,"C1公司",E2：E17）”即完成这一统计。
友情提醒：如果对EXCEL的函数不太熟悉，在单元格中直接输入公式有困难，我们可以用“插入函数”命令（或直接按工具栏上的“粘贴函数”命令按钮），选中你需要的函数后，按其提示操作即可完成公式的输入。
2、多条件求和：统计C2公司施工的质量等级为“合格”的工程总建筑面积，并将结果放在E19单元格中，我们用“条件求和”功能来实现：
①选“工具→向导→条件求和”命令（若没有此命令选项，可以用“加载宏”的方式来增加这一命令选项），在弹出的对话框中，按右下带“―”号的按钮（此时对话框变成类似工具条的窗口形式浮于桌面上），用鼠标选定D1：I17区域，并按窗口右边带红色箭头的按钮（恢复对话框状态）。
②按“下一步”，在弹出的对话框中，按“求和列”右边的下拉按钮选中“建筑面积”项，再分别按“条件列、运算符、比较值”右边的下拉按钮，依次选中“施工单位”、“=”（默认）、“C2公司”选项，最后按“添加条件”按钮。重复前述操作，将“条件列、运算符、比较值”设置为“质量等级”、“=”、“合格”，并按“添加条件”按钮。
③两次点击“下一步”，在弹出的对话框中，按右下带“―”号的按钮，用鼠标选定E19单元格，并按窗口右边带红色箭头的按钮。
④按“完成”按钮，此时符合条件的汇总结果将自动、准确地显示在 E19单元格中。
友情提醒：上述操作实际上是输入了一个数组公式，我们也可以先在 E19单元格中直接输入公式：=SUM(IF(D2:D17="C2公司",IF(I2:I17="合格",E2:E17)))，然后在按住Ctrl+Shift键（非常关键！！！）的同时按下Enter键，也可以达到上述多条件求和之目的。
二、条件计数。
统计质量等级为“合格”工程的数目，并将结果存放在I18单元格中，在I18单元格中输入公式：=COUNTIF(I2:I17,"合格")，当按下确定按钮后，统计结果――数字5即自动在I18单元格中显示出来。
三、条件格式。
将工程造价在500万元（含500万元）以上的工程造价数值以红颜色显示出来：
①选中F2至F17单元格；
②用“格式→条件格式”命令，打开“条件格式”对话框（如图 2）；
③按第二个方框旁的下拉按钮，选中“大于或等于”选项，再在后面的方框中输入数字500；
④按上述对话框中的“格式”按钮，打开“单元格格式”对话框（如图 3），在“文字”卡片下，按“颜色”旁的下拉按钮，将文字颜色设置成红色后，按“确定”按钮关闭“单元格格式”对话框，回到“条件格式”对话框中；
⑤按“确定”按钮。
此时造价大于500万元的工程，其造价数值将以红色显示在单元格中。
友情提醒：继续按上述“条件格式”对话框中的“添加”按钮，可以设置多级“条件”，如“工程造价大于500万元以红色显示，大于1000万元以蓝色显示”等（可以对一个单元格设置三个条件）。
四、条件函数。
我们在对竣工工程观感质量进行评定后，当观感得分率超过85%，该工程质量等级可以评定为“优良”。
①在I2单元格中输入公式：=IF(H2>=85,"优良",IF(H2>0,"合格","未竣工"))；
②选中I2单元格，将鼠标移至该单元格右下角成细十字线时（我们称之为“填充柄”），按住左键向下拖拉，将上公式复制到I列下面的单元格中；
③以后当工程竣工后，我们将该工程的观感得分率填入H列内相应的单元格中，则质量等级自动显示在相应工程的I列单元格中（若没有填观感得分率，则I列相应单元格中显示“未竣工”字样）。
友情提醒：在第①步输入公式时，条件判断数字按从大到小的顺序排列，否则会出错。
五、条件查找。
当工程基本情况表中登记的工程非常之多时，如果我们用普通浏览的方式查找某个具体的工程是非常困难的，此时我们可以用“查找”功能来快速定位：
① 用“编辑查找”命令，打开“查找”对话框（如图 4）；
②在“查找内容”下面的方框中输入该工程的关键词；
③对“搜索方式、搜索范围”作适当设置后，连续按“查找下一个”按钮，符合模糊条件的单元格依次被选中，即可快速查找到某个具体的工程。
友情提醒：在“查找内容”下面的方框中输入的内容不一定非得与某一单元格数值完成相同的文本，如查找“C1公司”的某个工程，只要输入“C1”即可以进行模糊查找了。
六、条件筛选。
如果要打印C1公司施工的工程基本情况一览表，我们可以用“自动筛选”功能来实现：
①选中表格中的任一单元格，用“数据→筛选→自动筛选”命令，启动“自动筛选”功能（列标题旁出现一个下拉按钮，如图 5）；
②按“施工单位”旁的下拉按钮，选中C1公司，则表格中只显示C1公司所施工的工程；
③接上打印机，装上打印纸，按“打印”按钮，即可打印出C1公司施工的工程基本情况一览表（如图 5）；
④打印结束后，重复第①的操作关闭“自动筛选”功能，则全部工程又重新显示在表格中。
友情提醒：此处的“条件筛选”可以进行“多级”筛选，如再上述筛选的基础上，再按“进度”旁的下拉按钮，选中“在建”选项，则表格中只剩下“C1公司”施工的所有“在建”工程。
其实，EXCEL的条件功能还有很多，如果有感兴趣的朋友不妨一试，若发现其什幺更好的功能，别忘了告诉一声，我在此先谢过了。
Excel函数应用之财务函数
像统计函数、工程函数一样，在Excel中还提供了许多财务函数。财务函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值。这些财务函数大体上可分为四类：投资计算函数、折旧计算函数、偿还率计算函数、债券及其它金融函数。它们为财务分析提供了极大的便利。使用这些函数不必理解高级财务知识，只要填写变量值就可以了。在下文中，凡是投资的金额都以负数形式表示，收益以正数形式表示。
??在介绍具体的财务函数之前，我们首先来了解一下财务函数中常见的参数：
??未来值 (fv)--在所有付款发生后的投资或贷款的价值。
??期间数 (nper)--为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。
??付款 (pmt)--对于一项投资或贷款的定期支付数额。其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其它费用及税款。
??现值 (pv)--在投资期初的投资或贷款的价值。例如，贷款的现值为所借入的本金数额。
??利率 (rate)--投资或贷款的利率或贴现率。
??类型 (type)--付款期间内进行支付的间隔，如在月初或月末，用0或1表示。
??日计数基准类型（basis）--为日计数基准类型。Basis为0 或省略代表US (NASD) 30/360，为1代表实际天数/实际天数 ，为2代表实际天数/360，为3代表实际天数/365 ，为4代表欧洲30/360。
??接下来，我们将分别举例说明各种不同的财务函数的应用。在本文中主要介绍各类型的典型财务函数，更多的财务函数请参看附表及相关书籍。如果下文中所介绍的函数不可用，返回错误值 #NAME?，请安装并加载"分析工具库"加载宏。操作方法为：
?? 1、在"工具"菜单上，单击"加载宏"。
?? 2、在"可用加载宏"列表中，选中"分析工具库"框，再单击"确定"。
?? 一、投资计算函数
??投资计算函数可分为与未来值fv有关，与付款pmt有关，与现值pv有关，与复利计算有关及与期间数有关几类函数。
?? 1、与未来值fv有关的函数--FV、FVSCHEDULE
?? 2、与付款pmt有关的函数--IPMT、ISPMT、PMT、PPMT
?? 3、与现值pv有关的函数--NPV、PV、XNPV
?? 4、与复利计算有关的函数--EFFECT、NOMINAL
?? 5、与期间数有关的函数--NPER
??在投资计算函数中，笔者将重点介绍FV、NPV、PMT、PV函数。
??（一） 求某项投资的未来值FV
??在日常工作与生活中，我们经常会遇到要计算某项投资的未来值的情况，此时利用Excel函数FV进行计算后，可以帮助我们进行一些有计划、有目的、有效益的投资。FV函数基于固定利率及等额分期付款方式，返回某项投资的未来值。
??语法形式为FV(rate,nper,pmt,pv,type)。其中rate为各期利率，是一固定值，nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数，pv为各期所应付给（或得到）的金额，其数值在整个年金期间（或投资期内）保持不变，通常Pv包括本金和利息，但不包括其它费用及税款，pv为现值，或一系列未来付款当前值的累积和，也称为本金，如果省略pv，则假设其值为零，type为数字0或1，用以指定各期的付款时间是在期初还是期末，如果省略t，则假设其值为零。
??例如：假如某人两年后需要一笔比较大的学习费用支出，计划从现在起每月初存入2000元，如果按年利2.25%，按月计息（月利为2.25%/12），那幺两年以后该账户的存款额会是多少呢？
??公式写为：FV(2.25%/12, 24,-2000,0,1)
图1
（二） 求投资的净现值NPV
?? NPV函数基于一系列现金流和固定的各期贴现率，返回一项投资的净现值。投资的净现值是指未来各期支出（负值）和收入（正值）的当前值的总和。
??语法形式为：NPV(rate,value1,value2, ...) 其中，rate为各期贴现率，是一固定值；value1,value2,...代表1到29笔支出及收入的参数值，value1,value2,...所属各期间的长度必须相等，而且支付及收入的时间都发生在期末。需要注意的是：NPV按次序使用value1,value2，来注释现金流的次序。所以一定要保证支出和收入的数额按正确的顺序输入。如果参数是数值、空白单元格、逻辑值或表示数值的文字表示式，则都会计算在内；如果参数是错误值或不能转化为数值的文字，则被忽略，如果参数是一个数组或引用，只有其中的数值部分计算在内。忽略数组或引用中的空白单元格、逻辑值、文字及错误值。
??例如，假设开一家电器经销店。初期投资￥200,000，而希望未来五年中各年的收入分别为￥20,000、￥40,000、￥50,000、￥80,000和￥120,000。假定每年的贴现率是8%（相当于通贷膨胀率或竞争投资的利率），则投资的净现值的公式是：
?? =NPV(A2, A4:A8)+A3
??在该例中，一开始投资的￥200,000并不包含在v参数中，因为此项付款发生在第一期的期初。假设该电器店的营业到第六年时，要重新装修门面，估计要付出￥40,000，则六年后书店投资的净现值为：
?? =NPV(A2, A4:A8, A9)+A3
??如果期初投资的付款发生在期末，则 投资的净现值的公式是：
?? =NPV(A2, A3:A8)
图2
（三） 求贷款分期偿还额PMT
?? PMT函数基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。PMT函数可以计算为偿还一笔贷款，要求在一定周期内支付完时，每次需要支付的偿还额，也就是我们平时所说的"分期付款"。比如借购房贷款或其它贷款时，可以计算每期的偿还额。
??其语法形式为：PMT(rate,nper,pv,fv,type) 其中，rate为各期利率，是一固定值，nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数，pv为现值，或一系列未来付款当前值的累积和，也称为本金，fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零），type为0或1，用以指定各期的付款时间是在期初还是期末。如果省略type，则假设其值为零。
??例如，需要10个月付清的年利率为8%的￥10,000贷款的月支额为：
?? PMT（8%/12,10,10000） 计算结果为：-￥1,037.03。
??（四） 求某项投资的现值PV
?? PV函数用来计算某项投资的现值。年金现值就是未来各期年金现在的价值的总和。如果投资回收的当前价值大于投资的价值，则这项投资是有收益的。
??其语法形式为：PV(rate,nper,pmt,fv,type) 其中Rate为各期利率。Nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。Pmt为各期所应支付的金额，其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其它费用及税款。Fv 为未来值，或在最后一次支付后希望得到的现金余额，如果省略 fv，则假设其值为零（一笔贷款的未来值即为零）。Type用以指定各期的付款时间是在期初还是期末。
??例如，假设要购买一项保险年金，该保险可以在今后二十年内于每月末回报￥600。此项年金的购买成本为80,000，假定投资回报率为8%。那幺该项年金的现值为：
?? PV(0.08/12, 12*20,600,0) 计算结果为：￥-71,732.58。
??负值表示这是一笔付款，也就是支出现金流。年金（￥-71,732.58）的现值小于实际支付的（￥80,000）。因此，这不是一项合算的投资。
图3
二、 折旧计算函数
??折旧计算函数主要包括AMORDEGRC、AMORLINC、DB、DDB、SLN、SYD、VDB。这些函数都是用来计算资产折旧的，只是采用了不同的计算方法。这里，对于具体的计算公式不再赘述，具体选用哪种折旧方法，则须视各单位情况而定。
?? 三、偿还率计算函数
??偿还率计算函数主要用以计算内部收益率，包括IRR、MIRR、RATE和XIRR几个函数。
??（一） 返回内部收益率的函数--IRR
?? IRR函数返回由数值代表的一组现金流的内部收益率。这些现金流不一定必须为均衡的，但作为年金，它们必须按固定的间隔发生，如按月或按年。内部收益率为投资的回收利率，其中包含定期支付（负值）和收入（正值）。
??其语法形式为IRR(values,guess) 其中values为数组或单元格的引用，包含用来计算内部收益率的数字，values必须包含至少一个正值和一个负值，以计算内部收益率，函数IRR根据数值的顺序来解释现金流的顺序，故应确定按需要的顺序输入了支付和收入的数值，如果数组或引用包含文本、逻辑值或空白单元格，这些数值将被忽略；guess为对函数IRR计算结果的估计值，excel使用迭代法计算函数IRR从guess开始，函数IRR不断修正收益率，直至结果的精度达到0.00001%，如果函数IRR经过20次迭代，仍未找到结果，则返回错误值#NUM！，在大多数情况下，并不需要为函数IRR的计算提供guess值，如果省略guess，假设它为0.1（10%）。如果函数IRR返回错误值#NUM！，或结果没有靠近期望值，可以给guess换一个值再试一下。
??例如，如果要开办一家服装商店，预计投资为￥110,000，并预期为今后五年的净收益为：￥15,000、￥21,000、￥28,000、￥36,000和￥45,000。分别求出投资两年、四年以及五年后的内部收益率。
图4
在工作表的B1：B6输入数据"函数.xls"所示，计算此项投资四年后的内部收益率IRR（B1：B5）为-3.27%；计算此项投资五年后的内部收益率IRR（B1：B6）为8.35%;计算两年后的内部收益率时必须在函数中包含guess，即IRR（B1：B3，-10%）为-48.96%。
二） 用RATE函数计算某项投资的实际赢利
??在经济生活中，经常要评估当前某项投资的运作情况，或某个新企业的现状。例如某承包人建议你贷给他30000元，用作公共工程建设资金，并同意每年付给你9000元，共付五年，以此作为这笔贷款的最低回报。那幺你如何去决策这笔投资？如何知道这项投资的回报率呢？对于这种周期性偿付或是一次偿付完的投资，用RATE函数可以很快地计算出实际的赢利。其语法形式为RATE(nper,pmt,pv,fv,type,guess)。
??具体操作步骤如下：
?? 1、选取存放数据的单元格，并按上述相似的方法把此单元格指定为"百分数"的格式。
?? 2、插入函数RATE，打开"粘贴函数"对话框。
?? 3、在"粘贴函数"对话框中，在"Nper"中输入偿还周期5(年),在"Pmt"中输入7000(每年的回报额)，在"Pv"中输入－30000(投资金额)。即公式为=RATE(5,9000,-30000)
?? 4、确定后计算结果为15.24％。这就是本项投资的每年实际赢利，你可以根据这个值判断这个赢利是否满意，或是决定投资其它项目，或是重新谈判每年的回报。
?? 四、债券及其它金融函数
??债券及其它金融函数又可分为计算本金、利息的函数，与利息支付时间有关的函数、与利率收益率有关的函数、与修正期限有关的函数、与有价证券有关的函数以及与证券价格表示有关的函数。
?? 1、计算本金、利息的函数--CUMPRINC、ACCRINT、ACCRINTM、CUMIPMT、COUPNUM
?? 2、与利息支付时间有关的函数--COUPDAYBS、COUPDAYS、COUPDAYSNC、COUPNCD、COUPPCD
?? 3、 与利率收益率有关的函数--INTRATE、ODDFYIELD、ODDLYIELD、TBILLEQ、TBILLPRICE、TBILLYIELD、YIELD、YIELDDISC、YIELDMAT
?? 4、与修正期限有关的函数--DURATION、MDURATION
?? 5、与有价证券有关的函数--DISC、ODDFPRICE、ODDLPRICE、PRICE、PRICEDISC、PRICEMAT、RECEIVED
?? 6、与证券价格表示有关的函数--DOLLARDE、DOLLARFR
??在债券及其它金融函数中，笔者将重点介绍函数ACCRINT、CUMPRINC、DISC。
??（一）求定期付息有价证券的应计利息的函数ACCRINT
?? ACCRINT函数可以返回定期付息有价证券的应计利息。
??其语法形式为ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis)
??其中issue为有价证券的发行日，first_interest为有价证券的起息日，settlement为有价证券的成交日，即在发行日之后，有价证券卖给购买者的日期，rate为有价证券的年息票利率，par为有价证券的票面价值，如果省略par，函数ACCRINT就会自动将par设置为￥1000，frequency为年付息次数，basis为日计数基准类型。
??例如，某国库券的交易情况为：发行日为2008年3月1日；起息日为2008年8月31日；成交日为2008年5月1日，息票利率为10.0%；票面价值为￥1,000；按半年期付息；日计数基准为30/360，那幺应计利息为：
图5
（二）求本金数额CUMPRINC
?? CUMPRINC函数用于返回一笔货款在给定的st到en期间累计偿还的本金数额。其语法形式为CUMPRINC(rate,nper,pv,start_period,end_period,type) 其中rate为利率，nper为总付款期数，pv为现值，start_period为计算中的首期，付款期数从1开始计数，end_period为计算中的末期，type为付款时间类型。
??例如，一笔住房抵押贷款的交易情况如下：年利率为9.00%；期限为30年；现值为￥125，000。由上述已知条件可以计算出：r=9.00%/12=0.0075，np=30*12=360。
图6
那幺该笔贷款在第下半年偿还的全部本金之中（第7期到第12期）为： =CUMPRINC(A2/12,A3*12,A4,7,12,0)计算结果为：-436.568194。
??该笔贷款在第一个月偿还的本金为：=CUMPRINC(A2/12,A3*12,A4,1,1,0)计算结果为：-68.27827118。
??（三） 求有价证券的贴现率DISC
?? DISC函数返回有价证券的贴现率。
??其语法形式为DISC(settlement,maturity,pr,redemption,basis) 其中settlement为有价证券的成交日，即在发行日之后，有价证券卖给购买者的日期，maturity为有价证券的到日期，到期日是有价证券有效期截止时的日期，pr为面值为"￥100"的有价证券的价格，redemption为面值为"￥100"的有价证券的清偿价格，basis为日计数基准类型。
??例如：某债券的交易情况如下：成交日为99年3月18日，到期日为99年8月7日，价格为￥48.834，清偿价格为￥52，日计数基准为实际天数/360。那幺该债券的贴现率为： DISC("99/3/18","99/8/7",48.834,52,2) 计算结果为：0.154355363。
函数名称 函数说明 语法形式
ACCRINT 返回定期付息有价证券的应计利息。 ACCRINT(issue,first_interest, settlement,rate,par,frequency, basis)
ACCRINTM 返回到期一次性付息有价证券的应计利息。 ACCRINTM(issue,maturity,rate, par,basis)
AMORDEGRC 返回每个会计期间的折旧值。此函数是为法国会计系统提供的。 AMORDEGRC(cost,date_purchased, first_period,salvage,period, rate,basis)
AMORLINC 返回每个会计期间的折旧值，该函数为法国会计系统提供。 AMORLINC(cost,date_purchased, first_period,salvage,period, rate,basis)
COUPDAYBS 返回当前付息期内截止到成交日的天数。 COUPDAYBS(settlement,maturity, frequency, basis)
COUPDAYS 返回成交日所在的付息期的天数。 COUPDAYS(settlement,maturity, frequency, basis)
COUPDAYSNC 返回从成交日到下一付息日之间的天数。 COUPDAYSNC(settlement,maturity, frequency, basis)
COUPNCD 返回成交日过后的下一付息日的日期。 COUPNCD(settlement,maturity, frequency, basis)
COUPNUM 返回成交日和到期日之间的利息应付次数，向上取整到最近的整数。 COUPNUM(settlement,maturity, frequency, basis)
COUPPCD 返回成交日之前的上一付息日的日期。 COUPPCD(settlement,maturity, frequency, basis)
CUMIPMT 返回一笔贷款在给定的 start-period 到 end-period 期间累计偿还的利息数额。 CUMIPMT(rate,nper,pv,start_period, end_period,type)
CUMPRINC 返回一笔贷款在给定的 start-period 到 end-period 期间累计偿还的本金数额。 CUMPRINC(rate,nper,pv,start_period, end_period,type)
DB 使用固定余额递减法，计算一笔资产在给定期间内的折旧值。 DB(cost,salvage,life,period,month)
DDB 使用双倍余额递减法或其它指定方法，计算一笔资产在给定期间内的折旧值。 DDB(cost,salvage,life,period,factor)
DISC 返回有价证券的贴现率。 DISC(settlement,maturity,pr, redemption,basis)
DOLLARDE 将按分数表示的价格转换为按小数表示的价格，如证券价格，转换为小数表示的数字。 DOLLARDE(fractional_dollar, fraction)
DOLLARFR 将按小数表示的价格转换为按分数表示的价格。如证券价格，转换为分数型数字。 DOLLARFR(decimal_dollar, fraction)
DURATION 返回假设面值 $100 的定期付息有价证券的修正期限。期限定义为一系列现金流现值的加权平均值，用于计量债券价格对于收益率变化的敏感程度。 DURATION(settlement,maturity, coupon yld,frequency,basis)
EFFECT 利用给定的名义年利率和一年中的复利期次，计算实际年利率。 EFFECT(nominal_rate,npery)
FV 基于固定利率及等额分期付款方式，返回某项投资的未来值。 FV(rate,nper,pmt,pv,type)
FVSCHEDULE 基于一系列复利返回本金的未来值。函数 FVSCHDULE 用于计算某项投资在变动或可调利率下的未来值。 FVSCHEDULE(principal,schedule)
INTRATE 返回一次性付息证券的利率。 INTRATE(settlement,maturity, investment,redemption,basis)
IPMT 基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的利息偿还额。 IPMT(rate,per,nper,pv,fv,type)
IRR 返回由数值代表的一组现金流的内部收益率。 IRR(values,guess)
ISPMT 计算特定投资期内要支付的利息。 ISPMT(rate,per,nper,pv)
MDURATION 返回假设面值 $100 的有价证券的 Macauley 修正期限。 MDURATION(settlement,maturity, coupon,yld,frequency,basis)
MIRR 返回某一连续期间内现金流的修正内部收益率。 MIRR(values,finance_rate, reinvest_rate)
NOMINAL 基于给定的实际利率和年复利期数，返回名义年利率。 NOMINAL(effect_rate,npery)
NPER 基于固定利率及等额分期付款方式，返回某项投资（或贷款）的总期数。 NPER(rate, pmt, pv, fv, type)
NPV 通过使用贴现率以及一系列未来支出（负值）和收入（正值），返回一项投资的净现值。 NPV(rate,value1,value2, ...)
ODDFPRICE 返回首期付息日不固定的面值 $100 的有价证券的价格 ODDFPRICE(settlement,maturity, issue,first_coupon,rate,yld, redemption, frequency,basis)
ODDFYIELD 返回首期付息日不固定的有价证券（长期或短期）的收益率。 ODDFYIELD(settlement,maturity, issue,first_coupon,rate,pr, redemption, frequency,basis)
ODDLPRICE 返回末期付息日不固定的面值 $100 的有价证券（长期或短期）的价格。 ODDLPRICE(settlement,maturity, last_interest,rate,yld,redemption, frequency,basis)
ODDLYIELD 返回末期付息日不固定的有价证券（长期或短期）的收益率。 ODDLYIELD(settlement,maturity, last_interest,rate,pr,redemption, frequency,basis)
PMT 基于固定利率及等额分期付款方式，返回贷款的每期付款额。 PMT(rate,nper,pv,fv,type)
PPMT 基于固定利率及等额分期付款方式，返回投资在某一给定期间内的本金偿还额。 PPMT(rate,per,nper,pv,fv,type)
PRICE 返回定期付息的面值 $100 的有价证券的价格。 PRICE(settlement,maturity, rate,yld,redemption,frequency, basis)
PRICEDISC 返回折价发行的面值 $100 的有价证券的价格。 PRICEDISC(settlement,maturity, discount,redemption,basis)
PRICEMAT 返回到期付息的面值 $100 的有价证券的价格。 PRICEMAT(settlement,maturity, issue,rate,yld,basis)
PV 返回投资的现值。现值为一系列未来付款的当前值的累积和。例如，借入方的借入款即为贷出方贷款的现值。 PV(rate,nper,pmt,fv,type)
RATE 返回年金的各期利率。函数 RATE 通过迭代法计算得出，并且可能无解或有多个解。 RATE(nper,pmt,pv,fv,type,guess)
RECEIVED 返回一次性付息的有价证券到期收回的金额。 RECEIVED(settlement,maturity, investment,discount,basis)
SLN 返回某项资产在一个期间中的线性折旧值。 SLN(cost,salvage,life)
SYD 返回某项资产按年限总和折旧法计算的指定期间的折旧值。 SYD(cost,salvage,life,per)
TBILLEQ 返回国库券的等效收益率。 TBILLEQ(settlement,maturity, discount)
TBILLPRICE 返回面值 $100 的国库券的价格。 TBILLPRICE(settlement,maturity, discount)
TBILLYIELD 返回国库券的收益率。 TBILLYIELD(settlement,maturity,pr)
VDB 使用双倍余额递减法或其它指定的方法，返回指定的任何期间内（包括部分期间）的资产折旧值。函数 VDB 代表可变余额递减法。 VDB(cost,salvage,life,start_period, end_period,factor,no_switch)
XIRR 返回一组现金流的内部收益率，这些现金流不一定定期发生。若要计算一组定期现金流的内部收益率，请使用函数 IRR。 XIRR(values,dates,guess)
XNPV 返回一组现金流的净现值，这些现金流不一定定期发生。若要计算一组定期现金流的净现值，请使用函数 NPV。 XNPV(rate,values,dates)
YIELD 返回定期付息有价证券的收益率，函数 YIELD 用于计算债券收益率。 YIELD(settlement,maturity,rate, pr,redemption,frequency,basis)
YIELDDISC 返回折价发行的有价证券的年收益率。 YIELDDISC(settlement,maturity, pr,redemption,basis)
YIELDMAT 返回到期付息的有价证券的年收益率。 YIELDMAT(settlement,maturity, issue,rate,pr,basis)
Excel自动计算与数据校验
但凡提到会计核算问题，很多人动辄就会想到要用财务软件。然而杀鸡焉用牛刀，其实有时候不必请动这个巨无霸，用Excel足矣。作为当前最流行的办公自动化软件，Excel绝非浪得虚名，其灵活的自动计算和数据校验功能，使得它在工资和财务报表的编制方面大有作为。在工资和财务中，要到对大量的数据进行汇总计算，通常要在计算之前对某些特定的列进行判断，根据判断结果来校验数据的正确性，最后决定要采用那种方式进行计算。还要具有一定的容错性，能够自动捕获一些不符合要求的输入，并给出相应的出错信息，以便操作者能够及时加以纠正。以上这些功能，专门的财务软件自然可以胜任，但未免有一种大炮打蚊子的感觉。而对Excel，只需稍加挖掘其潜力，虽然是小米加步枪，也能强过飞机大炮。好，闲话少说，下面以工资报表编制为实例，向大家介绍如何运用Excel2000强大的计算与数据校验功能。
首先我们创建一个工资统计报表，该表分成两部分，第一部分是工资主表部分，它反映工资表的各项内容（如图1）；第二部分是该工资表的基础数据部分，这里称为基础表（如图2）。这两部分合在一张工作表中完成。
实现步骤：
1、创建如图1所示的工资报表样式和如图2所示的工资报表基础资料部分的样式。
2、修改手工录入列的字段名：显然最后做好的工作表要用密码加以保护，以防泄密，加密固然需要，但也不能误伤好人，应该既有集中、又有民主。有些字段应该允许操作人员进行实时修改（像姓名、性别、出生年月、工作年月、岗位代码、技术职务代码等），应该对这些字段网开一面。非常简单，鼠标单击这些字段所在列的顶端字母栏，选中它，再单击击鼠标右键，在弹出菜单中选择“设置单元格格式”，切换至“保护”选项卡，确保“锁定”复选框清空（如图3）。
3、设置自动计算列的背景色：为了让自动计算各列以更加醒目显示出来，提示操作人员，这些列不用修改，我们可以修改这些列的背景色。单击这些列的顶端字母栏，选中它，再单击击鼠标右键，在弹出菜单中选择“设置单元格格式”，切换至“图案”选项卡，将单元格底纹设置为蓝色（当然也可以是其它颜色）（如图4）所示：
4、创建基础表：一些字段（如岗位名称、技术职务、岗位等级、岗位系列、岗位工资、年功能津贴、专业技术职等级津贴）是从图2中所示的基础部分取得数据或是经计算得出，因此不需要操作人员进行手工输入。我们在同一张工作表的右边创建如图2所示的表格，并输入必要的内容。
5、 好，现在要讲到精华部分了──利用Excel函数设置自动填充数据列的计算公式， Excel提供了大量的应用函数，可谓十八般武器样样俱全。随便拿出一种足以制胜。就举一个查找函数LOOKUP的例子吧。
拿到一样武器，总得熟悉一下它的性能，函数 LOOKUP可以在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值（详细说明请参见有关帮助文档）。
（1）设置“岗位名称”F列的自动填充公式：选中“岗位名称”这一列的单元格F6，在工具栏下的公式输入框中输入如下公式：
=IF(E6="","",LOOKUP(T(E6),R$6:R$16,S$6:S$16))（如图5）所示：
这个公式所表达的意思是，F6单元格的内容是根据前一个单元“岗位代码”E6单元格中内容，完成自动的填充，如果E6为空时，则F6为空，否则从R列的6-16行中查找E6单元格中的值，如果找到则将S列中6-16行与之对应行的值填充到F6中。如我们在E6中输入了“1401”，则F6中自动显示出“县局局长及相当职务”。同样，我们可以将此公式复制到F列的所有F6以下的行中，使所有F列所有单元都具有自动填充功能。完成：“岗位名称”列
（2）以同样的方法，我们可以设置以下各列的公式：
技术职务（H列）：=IF(G6="","",LOOKUP(T(G6),W$5:W$16,X$5:X$16))
岗位等级（I列）：=IF(E6="","",LOOKUP(T(E6),R$6:R$16,U$6:U$16))
岗位系列（J列）：=IF(E6="","",LOOKUP(T(E6),R$6:R$16,T$6:T$16))
专业技术职务资格等级津贴（M列）：
=IF(G6="","",LOOKUP(T(G6),W$5:W$16,Y$5:Y$16))
（3）岗位工资（K列）公式设置：由于岗位工资的设置不仅仅是和E列的值有关，而且要根据O列的值（布尔类型）进行判断，如果O列的值为“是”时，说明该人员是“组长”，岗位工资要增加40，所以公式设置如下：
=IF(O6="是",LOOKUP(E6,R$6:R$16,V$6:V$16)+40,
LOOKUP(E6,R$6:R$16,V$6:V$16))
（4）年功津贴（L列）公式设置：年功能津贴是该工作人员参加工作的年限乘以每年津贴10元，所以L6单元格的公式为：
=IF(D6="","",(2002-INT(D6))*10)
（5）工资合计（N列）公式设置：这是一个求和公式，N6单元格的公式设置如为：=K6+L6+M6或=SUM（K6：M6）
（6）年龄（P列）公式设置：这里在进行计算这前，需要验证输入数据的合法性，必须对C6单元格输入的值进行校验，在1940-1985这间（很显然，不在这个年龄范围的，要幺应该已经退休，要幺就算是非法雇用童工），如果不是，那幺就会在P列相应的单元格中显示"╳"，以提醒操作员所输入的数据不正确。该P6单元格的公式如下
=IF(AND(INT(C6)>1940,INT(C6)<1985),2002-INT(C6),"╳")
（7）工龄（Q列）公式设置：同样，在计算工龄之前要保证参加工作时间在1955-2002年之间，否则会在Q列相应的单元格中显示"╳"，以提醒操作员所输入的数据不正确。该列Q6单元格的公式设置如下：
=IF(OR(INT(D6)<1955,INT(D6)>2002),"╳",2002-INT(D6))
完成以上工作之后，我们可以在允计操作的单元格中输入数据，相应的单元格就会非常听话地自动填上数据。如果要对工资进行调整，只需要改动基础数据部分的表格内容，那幺工资表会自动更新，再用不着费时费心地逐项修改整个工资表了。但我们对编制好的工资表进行预览时会发现，右边基础部分的数据也显示出来了。这样肯定是不行的，既不符合要求，也易造成基础数据的意外修改。但是Excel考虑周到，为我们提供了隐藏表格各列的方法。用鼠标左键单击要隐藏的列的顶端位置，选中该列，然后单击鼠标右键，在弹出菜单中选择“隐藏”，那幺这个列就不会再出来捣乱。这样我们就可以隐藏基础部分的表格，最后形成的工资报表能够基本符合安全的需要。
将工作表设置为保护状态是为了使操作员只能对允许的单元格进行修改，而对工资表自动计算部分则应拒绝随意修改，以保证数据的完整性。设置方法是：选择菜单“工具”—“保护”—“保护工作表”，输入密码（如图6）。
确定后，就完成了工作表的保护。
至此，一个集基础数据管理、自动计算，且满足一定安全需要的工资报表系统就完成了。
Excel函数应用之信息函数
在Excel函数中有一类函数，它们专门用来返回某些指定单元格或区域等的信息，比如单元格的内容、格式、个数等，这一类函数我们称为信息函数。在本文中，我们将对这一类函数做以概要性了解，同时对于其中一些常用的函数及其参数的应用做出示例。
??一、用于返回有关单元格格式、位置或内容的信息的函数CELL
??CELL函数用于返回某一引用区域的左上角单元格的格式、位置或内容等信息。其语法形式为，CELL(info_type,reference) 其中Info_type为一个文本值，指定所需要的单元格信息的类型。Reference则表示要获取其有关信息的单元格。如果忽略，则在 info_type 中所指定的信息将返回给最后更改的单元格。
??首先看一下，info_type 的可能值及相应的结果。
??
类型 Info_type 返回结果
位置 "address" 引用中第一个单元格的引用，文本类型。
"col" 引用中单元格的列标。
"row" 引用中单元格的行号。
"filename" 包含引用的文件名（包括全部路径），文本类型。如果包含目标引用的工作表尚未保存，则返回空文本 ("")。
格式 "color" 如果单元格中的负值以不同颜色显示，则为 1，否则返回 0。
"format" 与单元格中不同的数字格式相对应的文本值。下表列出不同格式的文本值。如果单元格中负值以不同颜色显示，则在返回的文本值的结尾处加“-”；如果单元格中为正值或所有单元格均加括号，则在文本值的结尾处返回“()”。
"parentheses" 如果单元格中为正值或全部单元格均加括号，则为 1，否则返回 0。
"prefix" 与单元格中不同的“标志前缀”相对应的文本值。如果单元格文本左对齐，则返回单引号 (‘)；如果单元格文本右对齐，则返回双引号 (")；如果单元格文本居中，则返回插入字符 (^)；如果单元格文本两端对齐，则返回反斜线 ()；如果是其它情况，则返回空文本 ("")。
"protect" 如果单元格没有锁定，则为 0；如果单元格锁定，则为 1。
"type" 与单元格中的数据类型相对应的文本值。如果单元格为空，则返回“b”。如果单元格包含文本常量，则返回“l”；如果单元格包含其它内容，则返回“v”。
"width" 取整后的单元格的列宽。列宽以默认字号的一个字符的宽度为单位。
内容 "contents" 引用中左上角单元格的值：不是公式。
??再看一下当info_type 为"format"，以及引用为用内置数字格式设置的单元格时，函数 CELL 返回文本值的情况。
图1
函数CELL主要用于与其它电子表格程序兼容。在随后的示例中我们来学习一下如何使用CELL函数来获取单元格的格式、位置及内容的信息。
??例：想要获知单元格A1到B4区域内比如行号、列宽、单元格内容等信息。
图2
二、用于计算区域内空白单元格的个数COUNTBLANK
??COUNTBLANK用于计算指定单元格区域中空白单元格的个数。其语法形式为COUNTBLANK(range) 其中Range为需要计算其中空白单元格个数的区域。需要注意的是，
??即使单元格中含有返回值为空文本 ("")的公式，该单元格也会计算在内，但包含零值的单元格不计算在内。
??在如图所示的例子中，单元格B3包括公式=IF(A3＜30,"",A3)，但该公式计算返回的值为空文本""，所以该单元格被计算为空单元格。而单元格A3为零值的单元格，不计作空单元格。
??试比较图3-A与图3-B的结果的区别，两者的差别在于图3-B中单元格B3的公式为=IF(A3＞30,"",A3)，计算后返回的结果为0，因此不计作空单元格。
图3A
图3B
三、返回对应于错误类型的数字的函数ERROR.TYPE
??ERROR.TYPE返回对应于 Microsoft Excel 中某一错误值的数字，或者，如果没有错误则返回 #N/A。语法形式为ERROR.TYPE(error_val) 其中Error_val为需要得到其标号的一个错误值。尽管 error_val 可以为实际的错误值，但它通常为一个单元格引用，而此单元格中包含需要检测的公式。以下即为error_val的函数返回结果。
图4
还记得逻辑函数IF吗？在函数 IF 中可以使用 ERROR.TYPE 检测错误值，并返回文本字符串（如，消息）来取代错误值。具体参看示例。
图5
四、返回有关当前操作环境的信息的函数INFO
??INFO函数用于返回有关当前操作环境的信息。其语法形式为INFO(type_text) 其中Type_text为文本，指明所要返回的信息类型。关于Type_text所返回的具体结果参看下表。
Type_text 返回
"directory" 当前目录或文件夹的路径。
"memavail" 可用的内存空间，以字节为单位。
"memused" 数据占用的内存空间。
"numfile" 打开的工作簿中活动工作表的数目。
"origin" A1-样式的绝对引用，文本形式，加上前缀“$A:”，与 Lotus 1-2-3 的 3.x 版兼容。以当前滚动位置为基准，返回窗口中可见的最右上角的单元格。
"osversion" 当前操作系统的版本号，文本值。
"recalc" 当前的重新计算方式，返回“自动”或“手动”。
"release" Microsoft Excel 的版本号，文本值。
"system" 操作系统名称：Macintosh = "mac" Windows = "pcdos"
"totmem" 全部内存空间，包括已经占用的内存空间，以字节为单位。
??举例说明如何利用INFO函数获知当前操作环境的信息。
图6
五、用来检验数值或引用类型的函数--IS类函数
IS类函数是指用来检验数值或引用类型的工作表函数，在Excel中一共有九个此类函数。就几个函数包括：
??（1）ISBLANK 如果值为空，则返回 TRUE
??（2）ISERR 如果值为除 #N/A 以外的任何错误值，则返回 TRUE
??（3）ISERROR 如果值为任何错误值，则返回 TRUE
??（4）ISLOGICAL 如果值为逻辑值，则返回 TRUE
??（5）ISNA 如果值为 #N/A 错误值，则返回 TRUE
??（6）ISNONTEXT 如果值不是文本，则返回 TRUE
??（7）ISNUMBER 如果值为数字，则返回 TRUE
??（8）ISREF 如果值为引用，则返回 TRUE
??（9）ISTEXT 如果值为文本，则返回 TRUE
??这些函数，概括为 IS 类函数，可以检验数值的类型并根据参数取值返回 TRUE 或 FALSE。例如，如果数值为对空白单元格的引用，函数 ISBLANK 返回逻辑值 TRUE，否则返回 FALSE。其语法形式为 函数名（value）其中Value为需要进行检验的数值。针对不同的IS类函数分别为：空白（空白单元格）、错误值、逻辑值、文本、数字、引用值或对于以上任意参数的名称引用。
??需要说明的是IS 类函数的参数 value 是不可转换的。例如，在其它大多数需要数字的函数中，文本值"19"会被转换成数字 19。然而在公式 ISNUMBER("19") 中，"19"并不由文本值转换成别的类型的值，函数 ISNUMBER 返回 FALSE。 IS 类函数主要用于检验公式计算结果。当它与函数 IF 结合在一起使用时，可以提供一种方法用来在公式中查出错误值。
图7
六、检验参数奇偶性的函数ISEVEN与ISODD
??ISEVEN与ISODD为检验参数奇偶性的函数。其中ISEVEN是当参数 number 为偶数时返回 TRUE，否则返回 FALSE。而ISODD则恰恰相反，如果参数 number 为奇数，返回 TRUE，否则返回 FALSE。
??关于这两个函数的具体用法请参看示例。
图8
七、返回转化为数值后的值得函数N
??函数N为返回转化为数值后的值。其语法形式为N(value) 其中Value为要转化的值。函数 N 可以转化下表列出的值：
图9
需要注意的是：一般情况下不必在公式中使用函数 N，因为 Excel 将根据需要自动对值进行转换。提供此函数是为了与其它电子表格程序兼容。Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。而Excel for the Macintosh 使用另外一个默认日期系统。
??关于函数N的具体用法可从以下示例中更详细地了解。
图10
八、返回错误值#N/A的函数NA
??NA函数用于返回错误值 #N/A。错误值 #N/A 表示"无法得到有效值"。建议使用 NA 标志空白单元格。在没有内容的单元格中输入 #N/A，可以避免不小心将空白单元格计算在内而产生的问题（当公式引用到含有 #N/A 的单元格时，会返回错误值 #N/A）。
??其语法形式为NA( )。
??需注意的是在函数名后面必须包括圆括号，否则，Microsoft Excel 无法识别该函数。也可直接在单元格中键入 #N/A。提供 NA 函数是为了与其它电子表格程序兼容。
??九、返回数值的类型的函数TYPE
??函数TYPE可用来返回数值的类型。当某一个函数的计算结果取决于特定单元格中数值的类型时，可使用函数 TYPE。其语法形式为TYPE(value) 其中Value可以为任意 Microsoft Excel 数值，如数字、文本以及逻辑值等等。
??要说明的是当使用能接受不同类型数据的函数（例如函数 ARGUMENT 和函数 INPUT）时，函数 TYPE 十分有用。可以使用函数 TYPE 来查找函数或公式所返回的数据是何种类型。可以使用 TYPE 来确定单元格中是否含有公式。TYPE 仅确定结果、显示或值的类型。如果某个值是一个单元格引用，它所引用的另一个单元格中含有公式，则 TYPE 将返回此公式结果值的类型。
图11
以上，我们对Excel函数的信息函数做了大致的了解。信息函数是用来返回某些指定单元格或区域等的信息，比如单元格的内容、格式、个数等的一类函数。在实际应用中，通常是与逻辑函数IF等配合使用来达到对单元格信息的确定。
巧用Excel函数，减少数据冗余
在当今的计算机应用领域中，微软的Office 2000以其阵容整齐、功能强大，成为广大用户最基本、最常用的软件。在Office 2000中，Excel则又是以制表、计算、数据分析功能见长。
在用Excel做数据处理时，经常需要录入有关人员的身份证号码、出生年月、性别、参加工作时间、工龄等信息。其实这些信息中有不少在身份证号码中已经包括了，大可不必重复输入。以下，通过Excel几个函数的使用介绍，来说明三点：
（1）自动从身份证号码中提取出生年月、性别信息。
（2）自动从参加工作时间中提取工龄信息。
（3）根据工龄的大小来确定名次。
在下表中，录入的数据仅两列：身份证号码、参加工作时间，相应的数据类型为：文本、日期。其它数据如出生年月、性别、工龄、名次等均是利用Excel 2000的函数及公式表达式的运算，实现自动提取的。
A B C D E F
1 身份证号码 出生年月 性别 参加工作时间 工龄 名次
2 340111630306701 1963年03月06日 男 1980年8月 21 2
3 352012197810295026 1978年10月29日 女 1997年3月 4 3
4 221217520419509 1952年04月19日 男 1970年5月 31 1
说明：上表中A、B、C、D、E、F……表示的是Excel工作表的列号，1、2、3、4……表示的是Excel工作表的行号。
一、身份证号码
众所周道，当今的身份证号码有15/18位之分。早期签发的身份证号码是15位的，现在签发的身份证由于年份的扩展（由两位变为四位）和末尾加了效验码，就成了18位。这两种身份证号码将在相当长的一段?*谀诠泊妗Ａ街稚矸葜ず怕氲暮迦缦拢?br />（1）15位的身份证号码：1~6位为地区代码，7~8位为出生年份(2位)，9~10位为出生月份，11~12位为出生日期，15位(即最后一位)为性别(奇数为男，偶数为女)。
（2）18位的身份证号码：1~6位为地区代码，7~10位为出生年份(4位)，11~12位为出生月份，13~14位为出生日期，17位(即倒数第二位)为性别(奇数为男，偶数为女)，18位(即最后一位)为效验位。
二、有关函数
要实现数据的自动提取，将用到以下的8个Excel函数,简介如下：
（1）IF（logical_test, value_if_true,value_if_false）：根据逻辑表达式测试的结果，返回相应的值。IF函数允许嵌套。
（2）LEN(text)：返回文本字符串中字符个数。
（3）CONCATENATE(text1,text2……)：将若干个文字项合并至一个文字项中。
（4）MID(text,start_num,num_chars)：从文本字符串中指定的起始位置起，返回指定长度的字符。
（5）MOD(number,divisor)：返回两数相除后的余数。
（6）TODAY（）：返回计算机系统内部的当前日期。
（7）YEAR（serial_number）：返回日期序列数对应的年份数。
（8）RANK（number,ref,order）：返回指定数字在一列数字中的排位。
三、公式表达式
（1）B2=IF(LEN(A2)=15,CONCATENATE("19",MID(A2,7,2),"年",MID(A2,9,2),"月",MID(A2,11,2),"日"),CONCATENATE(MID(A2,7,4),"年",MID(A2,11,2),"月",MID(A2,13,2),"日"))
（2）C2=IF(LEN(A2)=15,IF(MOD(MID(A2,15,1),2)=1,"男","女"),IF(MOD(MID(A2,17,1),2)=1,"男","女"))
（3）E2=YEAR(TODAY())-YEAR(D2)
（4）F2=RANK(E2,$E$2:$E$4)
说明：
（1）将上述四个公式表达式顺序输入至表格第二行的B2、C2、E2、F2单元格中，求出表中数据第一行的结果。
（2）分别移光标至B2、C2、E2、F2四个单元格的右下角，呈黑色“+”符号时，向下拖曳鼠标即可求出其它各单元格的值。
（3）在F2单元格的公式中，为了确保数据的比较范围在公式的复制中保持不变，采用了绝对引用——在单元格的行列标号前，均加上“$”符号。
Excel常用函數的應用技巧
Excel常用函數的應用技巧
常用的Excel技巧整理
常用函數的應用技巧
用Excel做数据排序的常用方法与技巧
Excel常用函數的應用技巧 比亚迪汽车俱乐部 迪车会 比亚迪官方论坛 比亚迪汽车官方论坛...
[Excel使用技巧] EXCEL中常用的快捷键·
excel编辑常用技巧
excel编辑常用技巧1
比较常用的25条Excel技巧整理放送
比较常用的25条Excel技巧整理
比较常用的25条Excel技巧整理放送
比较常用的25条Excel技巧整理
办公技巧 比较常用的25条Excel命令
比较常用的25条微软Excel技巧
办公技巧 比较常用的25条Excel命令
比较常用的25条Excel技巧整理放送
EXCEL操作常用技巧--会飞的鱼
[Excel使用技巧] EXCEL中常用的快捷键－AIt 数字小键盘 - 琴箫 - 新浪BLOG
[Excel使用技巧] EXCEL中常用的快捷键－AIt 数字小键盘
（一）EXCEL中常用 公式集合 ,excel 输入数据的方法技巧总结
EXCEL中常用公式集合 ,excel 输入数据的方法技巧总结
（一）EXCEL中常用公式集合 ,excel 输入数据的方法技巧总结
（一）EXCEL中常用公式集合 ,excel 输入数据的方法技巧总结1