下载
加入VIP
  • 专属下载特权
  • 现金文档折扣购买
  • VIP免费专区
  • 千万文档免费下载

上传资料

关闭

关闭

关闭

封号提示

内容

首页 EXCEL函数用法

EXCEL函数用法.doc

EXCEL函数用法

我凭什么不幸福呢
2017-10-17 0人阅读 举报 0 0 暂无简介

简介:本文档为《EXCEL函数用法doc》,可适用于IT/计算机领域

EXCEL函数用法EexcelExcel函数教程目录一、Excel函数应用之函数简介„„„„„„„„页二、Excel函数应用之数学和三角函数„„„„页三、Excel函数应用之逻辑函数„„„„„„页四、Excel函数应用之文本日期时间函数„„页五、Excel函数应用之查询与引用函数„„„„页六、Excel函数应用之统计函数„„„„„„„页七、Excel函数应用之工程函数„„„„„„„页八、Excel函数应用之财务函数„„„„„„„页九、Excel函数应用之信息函数„„„„„„„页十、Excel函数应用之数据库函数„„„„„„页一、Excel函数应用之函数简介(陆元婕年月日:)编者语:Excel是办公室自动化中非常重要的一款软件很多巨型国际企业都是依靠Excel进行数据管理。它不仅仅能够方便的处理表格和进行图形分析其更强大的功能体现在对数据的自动处理和计算然而很多缺少理工科背景或是对Excel强大数据处理功能不了解的人却难以进一步深入。编者以为对Excel函数应用的不了解正是阻挡普通用户完全掌握Excel的拦路虎然而目前这一部份内容的教学文章却又很少见所以特别组织了这一个《Excel函数应用》系列希望能够对Excel进阶者有所帮助。《Excel函数应用》系列将每周更新逐步系统的介绍Excel各类函数及其应用敬请关注~Excel的数据处理功能在现有的文字处理软件中可以说是独占鳌头几乎没有什么软件能够与它匹敌。在您学会了Excel的基本操作后是不是觉得自己一直局限在Excel的操作界面中而对于Excel的函数功能却始终停留在求和、求平均值等简单的函数应用上呢,难道Excel只能做这些简单的工作吗,其实不然函数作为Excel处理数据的一个最重要手段功能是十分强大的在生活和工作实践中可以有多种应用您甚至可以用Excel来设计复杂的统计管理表格或者小型的数据库系统。请跟随笔者开始Excel的函数之旅。这里笔者先假设您已经对于Excel的基本操作有了一定的认识。首先我们先来了解一些与函数有关的知识。一、什么是函数Excel中所提的函数其实是一些预定义的公式它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列运算如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等等。例如SUM"码`瓦特"W"埃"ang"皮卡(英寸)"Pica"特斯拉"T"高斯"ga"年"yr"日"day"摄氏度"C"小时"hr"华氏度"F"分钟"mn"开尔文度"K"秒"sec"茶匙"tsp"帕斯卡"Pa"汤匙"tbs"大气压"atm"液量盎司"oz"毫米汞柱"mmHg"杯"cup"US品"pt"脱UK品牛顿"N""ukpt"脱达因"dyn"夸脱"qt"磅力"lbf"加仑"gal"升"l"、与积分运算有关的函数ERF与ERFCERF为返回误差函数在上下限之间的积分。其语法形式为:ERF(lowerlimit,upperlimit)其中Lowerlimit为ERF函数的积分下限。Upperlimit为ERF函数的积分上限。如果省略默认为零。ERFC为返回从x到(无穷)积分的ERF函数的余误差函数。其语法形式为:ERFC(x)其中X为ERF函数积分的下限。、与复数运算有关的函数还记得中学时代学过的复数吗,是不是还记得当时求复数的模等计算的繁复,Excel的工程函数中提供的多种与复数运算有关的函数你可以用它来验证自己的运算结果的正确性啊。关于有哪些函数与复数运算有关可以察看所附的表格。这里将以简单的事例说明函数的使用方法。注意到在工程函数中有一些前缀为im的函数了吗,这些就是与复数运算有关的函数。举例已知复数i请用函数求解该复数的共轭复数、实系数、虚系数、模等。图附表:Excel的工程函数BESSELI返回修正Bessel函数BESSELI(x,n)值它与用纯虚数参数运算时的Bessel函数值相等。BESSELJ返回Bessel函数值。BESSELJ(x,n)返回修正Bessel函数值它与用纯虚数参数运BESSELKBESSELK(x,n)算时的Bessel函数值相等。返回Bessel函数值也BESSELY称为Weber函数或BESSELY(x,n)Neumann函数。将二进制数转换为十进BINDECBINDEC(number)制数。将二进制数转换为十六BINHEXBINHEX(number,places)进制数。将二进制数转换为八进BINOCTBINOCT(number,places)制数。将实系数及虚系数转换COMPLEX为xyi或xyj形式COMPLEX(realnum,inum,suffix)的复数。将数字从一个度量系统CONVERT转换到另一个度量系统CONVERT(number,fromunit,tounit)中。将十进制数转换为二进DECBINDECBIN(number,places)制数。将十进制数转换为十六DECHEXDECHEX(number,places)进制数。将十进制数转换为八进DECOCTDECOCT(number,places)制数。测试两个数值是否相等。DELTA如果number=numberDELTA(number,number)则返回否则返回。返回误差函数在上下限ERFERF(lowerlimit,upperlimit)之间的积分。返回从x到(无穷)ERFC积分的ERF函数的余误ERFC(x)差函数如果Number大于等于step返回否则返回GESTEPGESTEP(number,step)。使用该函数可筛选数据。将十六进制数转换为二HEXBINHEXBIN(number,places)进制数。将十六进制数转换为十HEXDECHEXDEC(number)进制数。将十六进制数转换为八HEXOCTHEXOCT(number,places)进制数。返回以xyi或xyjIMABS文本格式表示的复数的IMABS(inumber)绝对值(模)。返回以xyi或xyjIMAGINARY文本格式表示的复数的IMAGINARY(inumber)虚系数。IMARGUMENT返回以弧度表示的角IMARGUMENT(inumber)返回以xyi或xyjIMCONJUGATE文本格式表示的复数的IMCONJUGATE(inumber)共轭复数。返回以xyi或xyjIMCOS文本格式表示的复数的IMCOS(inumber)余弦。返回以xyi或xyjIMDIV文本格式表示的两个复IMDIV(inumber,inumber)数的商。返回以xyi或xyjIMEXP文本格式表示的复数的IMEXP(inumber)指数。返回以xyi或xyjIMLN文本格式表示的复数的IMLN(inumber)自然对数。返回以xyi或xyj文本格式表示的复数的IMLOGIMLOG(inumber)常用对数(以为底数)。返回以xyi或xyjIMLOGIMLOG(inumber)文本格式表示的复数的以为底数的对数。返回以xyi或xyjIMPOWER文本格式表示的复数的IMPOWER(inumber,number)n次幂。返回以xyi或xyjIMPRODUCT文本格式表示的至IMPRODUCT(inumber,inumber,)个复数的乘积。返回以xyi或xyjIMREAL文本格式表示的复数的IMREAL(inumber)实系数。返回以xyi或xyjIMSIN文本格式表示的复数的IMSIN(inumber)正弦值。返回以xyi或xyjIMSQRT文本格式表示的复数的IMSQRT(inumber)平方根。返回以xyi或xyjIMSUB文本格式表示的两个复IMSUB(inumber,inumber)数的差。返回以xyi或xyjIMSUM文本格式表示的两个或IMSUM(inumber,inumber,)多个复数的和。将八进制数转换为二进OCTBINOCTBIN(number,places)制数。OCTDEC将八进制数转换为十进OCTDEC(number)制数。将八进制数转换为十六OCTHEXOCTHEX(number,places)进制数。(责任编辑Rayhotrayccidnetcom)八、Excel函数应用之财务函数(陆元婕年月日:)编者语:Excel函数应用系列不知不觉已连载到了第篇了其间这一个系列得到了不少网友的肯定这是对作者和小编的最大支持在此我代表作者陆元婕女士向大家表示最真诚的谢意。我们会继续把Excel的精华介绍给大家如果你有什么建议请写信给我。像统计函数、工程函数一样在Excel中还提供了许多财务函数。财务函数可以进行一般的财务计算如确定贷款的支付额、投资的未来值或净现值以及债券或息票的价值。这些财务函数大体上可分为四类:投资计算函数、折旧计算函数、偿还率计算函数、债券及其他金融函数。它们为财务分析提供了极大的便利。使用这些函数不必理解高级财务知识只要填写变量值就可以了。在下文中凡是投资的金额都以负数形式表示收益以正数形式表示。在介绍具体的财务函数之前我们首先来了解一下财务函数中常见的参数:未来值(fv)在所有付款发生后的投资或贷款的价值。期间数(nper)为总投资(或贷款)期即该项投资(或贷款)的付款期总数。付款(pmt)对于一项投资或贷款的定期支付数额。其数值在整个年金期间保持不变。通常pmt包括本金和利息但不包括其他费用及税款。现值(pv)在投资期初的投资或贷款的价值。例如贷款的现值为所借入的本金数额。利率(rate)投资或贷款的利率或贴现率。类型(type)付款期间内进行支付的间隔如在月初或月末用或表示。日计数基准类型(basis)为日计数基准类型。Basis为或省略代表US(NASD)为代表实际天数实际天数为代表实际天数为代表实际天数为代表欧洲。接下来我们将分别举例说明各种不同的财务函数的应用。在本文中主要介绍各类型的典型财务函数更多的财务函数请参看附表及相关书籍。如果下文中所介绍的函数不可用返回错误值#NAME请安装并加载"分析工具库"加载宏。操作方法为:、在"工具"菜单上单击"加载宏"。、在"可用加载宏"列表中选中"分析工具库"框再单击"确定"。一、投资计算函数投资计算函数可分为与未来值fv有关与付款pmt有关与现值pv有关与复利计算有关及与期间数有关几类函数。、与未来值fv有关的函数FV、FVSCHEDULE、与付款pmt有关的函数IPMT、ISPMT、PMT、PPMT、与现值pv有关的函数NPV、PV、XNPV、与复利计算有关的函数EFFECT、NOMINAL、与期间数有关的函数NPER在投资计算函数中笔者将重点介绍FV、NPV、PMT、PV函数。(一)求某项投资的未来值FV在日常工作与生活中我们经常会遇到要计算某项投资的未来值的情况此时利用Excel函数FV进行计算后可以帮助我们进行一些有计划、有目的、有效益的投资。FV函数基于固定利率及等额分期付款方式返回某项投资的未来值。语法形式为FV(rate,nper,pmt,pv,type)。其中rate为各期利率是一固定值nper为总投资(或贷款)期即该项投资(或贷款)的付款期总数pv为各期所应付给(或得到)的金额其数值在整个年金期间(或投资期内)保持不变通常Pv包括本金和利息但不包括其它费用及税款pv为现值或一系列未来付款当前值的累积和也称为本金如果省略pv则假设其值为零type为数字或用以指定各期的付款时间是在期初还是期末如果省略t则假设其值为零。例如:假如某人两年后需要一笔比较大的学习费用支出计划从现在起每月初存入元如果按年利按月计息(月利为)那么两年以后该账户的存款额会是多少呢,公式写为:FV(,,,,)图(二)求投资的净现值NPVNPV函数基于一系列现金流和固定的各期贴现率返回一项投资的净现值。投资的净现值是指未来各期支出(负值)和收入(正值)的当前值的总和。语法形式为:NPV(rate,value,value,)其中rate为各期贴现率是一固定值value,value,代表到笔支出及收入的参数值value,value,所属各期间的长度必须相等而且支付及收入的时间都发生在期末。需要注意的是:NPV按次序使用value,value来注释现金流的次序。所以一定要保证支出和收入的数额按正确的顺序输入。如果参数是数值、空白单元格、逻辑值或表示数值的文字表示式则都会计算在内如果参数是错误值或不能转化为数值的文字则被忽略如果参数是一个数组或引用只有其中的数值部分计算在内。忽略数组或引用中的空白单元格、逻辑值、文字及错误值。例如假设开一家电器经销店。初期投资,,而希望未来五年中各年的收入分别为,,、,,、,,、,,和,,。假定每年的贴现率是(相当于通贷膨胀率或竞争投资的利率)则投资的净现值的公式是:=NPV(A,A:A)A在该例中一开始投资的,,并不包含在v参数中因为此项付款发生在第一期的期初。假设该电器店的营业到第六年时要重新装修门面估计要付出,,则六年后书店投资的净现值为:=NPV(A,A:A,A)A如果期初投资的付款发生在期末则投资的净现值的公式是:=NPV(A,A:A)图(三)求贷款分期偿还额PMTPMT函数基于固定利率及等额分期付款方式返回投资或贷款的每期付款额。PMT函数可以计算为偿还一笔贷款要求在一定周期内支付完时每次需要支付的偿还额也就是我们平时所说的"分期付款"。比如借购房贷款或其它贷款时可以计算每期的偿还额。其语法形式为:PMT(rate,nper,pv,fv,type)其中rate为各期利率是一固定值nper为总投资(或贷款)期即该项投资(或贷款)的付款期总数pv为现值或一系列未来付款当前值的累积和也称为本金fv为未来值或在最后一次付款后希望得到的现金余额如果省略fv则假设其值为零(例如一笔贷款的未来值即为零)type为或用以指定各期的付款时间是在期初还是期末。如果省略type则假设其值为零。例如需要个月付清的年利率为的,,贷款的月支额为:PMT(,,)计算结果为:,,。(四)求某项投资的现值PVPV函数用来计算某项投资的现值。年金现值就是未来各期年金现在的价值的总和。如果投资回收的当前价值大于投资的价值则这项投资是有收益的。其语法形式为:PV(rate,nper,pmt,fv,type)其中Rate为各期利率。Nper为总投资(或贷款)期即该项投资(或贷款)的付款期总数。Pmt为各期所应支付的金额其数值在整个年金期间保持不变。通常pmt包括本金和利息但不包括其他费用及税款。Fv为未来值或在最后一次支付后希望得到的现金余额如果省略fv则假设其值为零(一笔贷款的未来值即为零)。Type用以指定各期的付款时间是在期初还是期末。例如假设要购买一项保险年金该保险可以在今后二十年内于每月末回报,。此项年金的购买成本为,假定投资回报率为。那么该项年金的现值为:PV(,*,,)计算结果为:,,。负值表示这是一笔付款也就是支出现金流。年金(,,)的现值小于实际支付的(,,)。因此这不是一项合算的投资。图二、折旧计算函数折旧计算函数主要包括AMORDEGRC、AMORLINC、DB、DDB、SLN、SYD、VDB。这些函数都是用来计算资产折旧的只是采用了不同的计算方法。这里对于具体的计算公式不再赘述具体选用哪种折旧方法则须视各单位情况而定。三、偿还率计算函数偿还率计算函数主要用以计算内部收益率包括IRR、MIRR、RATE和XIRR几个函数。(一)返回内部收益率的函数IRRIRR函数返回由数值代表的一组现金流的内部收益率。这些现金流不一定必须为均衡的但作为年金它们必须按固定的间隔发生如按月或按年。内部收益率为投资的回收利率其中包含定期支付(负值)和收入(正值)。其语法形式为IRR(values,guess)其中values为数组或单元格的引用包含用来计算内部收益率的数字values必须包含至少一个正值和一个负值以计算内部收益率函数IRR根据数值的顺序来解释现金流的顺序故应确定按需要的顺序输入了支付和收入的数值如果数组或引用包含文本、逻辑值或空白单元格这些数值将被忽略guess为对函数IRR计算结果的估计值excel使用迭代法计算函数IRR从guess开始函数IRR不断修正收益率直至结果的精度达到如果函数IRR经过次迭代仍未找到结果则返回错误值#NUM~在大多数情况下并不需要为函数IRR的计算提供guess值如果省略guess假设它为()。如果函数IRR返回错误值#NUM~或结果没有靠近期望值可以给guess换一个值再试一下。例如如果要开办一家服装商店预计投资为,,并预期为今后五年的净收益为:,,、,,、,,、,,和,,。分别求出投资两年、四年以及五年后的内部收益率。图在工作表的B:B输入数据"函数xls"所示计算此项投资四年后的内部收益率IRR(B:B)为计算此项投资五年后的内部收益率IRR(B:B)为计算两年后的内部收益率时必须在函数中包含guess即IRR(B:B)为。(二)用RATE函数计算某项投资的实际赢利在经济生活中经常要评估当前某项投资的运作情况或某个新企业的现状。例如某承包人建议你贷给他元用作公共工程建设资金并同意每年付给你元共付五年以此作为这笔贷款的最低回报。那么你如何去决策这笔投资,如何知道这项投资的回报率呢,对于这种周期性偿付或是一次偿付完的投资用RATE函数可以很快地计算出实际的赢利。其语法形式为RATE(nper,pmt,pv,fv,type,guess)。Excel函数应用之函数简具体操作步骤如下:介Excel函数、选取存放数据的单元格并按上述相似的方法把此单元格指定应用之数学和为"百分数"的格式。三角函数Excel函数、插入函数RATE打开"粘贴函数"对话框。应用之逻辑函数、在"粘贴函数"对话框中在"Nper"中输入偿还周期(年),在Excel函数"Pmt"中输入(每年的回报额)在"Pv"中输入,(投资应用之文本日金额)。即公式为=RATE(,,)期时间函数Excel函数、确定后计算结果为,。这就是本项投资的每年实际赢利应用之查询与你可以根据这个值判断这个赢利是否满意或是决定投资其它项引用函数目或是重新谈判每年的回报。Excel函数应用之统计函四、债券及其他金融函数数Excel函数债券及其他金融函数又可分为计算本金、利息的函数与利息支应用之工程函付时间有关的函数、与利率收益率有关的函数、与修正期限有关数的函数、与有价证券有关的函数以及与证券价格表示有关的函数。、计算本金、利息的函数CUMPRINC、ACCRINT、ACCRINTM、CUMIPMT、COUPNUM、与利息支付时间有关的函数COUPDAYBS、COUPDAYS、COUPDAYSNC、COUPNCD、COUPPCD、与利率收益率有关的函数INTRATE、ODDFYIELD、ODDLYIELD、TBILLEQ、TBILLPRICE、TBILLYIELD、YIELD、YIELDDISC、YIELDMAT、与修正期限有关的函数DURATION、MDURATION、与有价证券有关的函数DISC、ODDFPRICE、ODDLPRICE、PRICE、PRICEDISC、PRICEMAT、RECEIVED、与证券价格表示有关的函数DOLLARDE、DOLLARFR在债券及其他金融函数中笔者将重点介绍函数ACCRINT、CUMPRINC、DISC。(一)求定期付息有价证券的应计利息的函数ACCRINTACCRINT函数可以返回定期付息有价证券的应计利息。其语法形式为ACCRINT(issue,firstinterest,settlement,rate,par,frequency,basis)其中issue为有价证券的发行日firstinterest为有价证券的起息日settlement为有价证券的成交日即在发行日之后有价证券卖给购买者的日期rate为有价证券的年息票利率par为有价证券的票面价值如果省略par函数ACCRINT就会自动将par设置为,frequency为年付息次数basis为日计数基准类型。例如某国库券的交易情况为:发行日为年月日起息日为年月日成交日为年月日息票利率为票面价值为,,按半年期付息日计数基准为那么应计利息为:图(二)求本金数额CUMPRINCCUMPRINC函数用于返回一笔货款在给定的st到en期间累计偿还的本金数额。其语法形式为CUMPRINC(rate,nper,pv,startperiod,endperiod,type)其中rate为利率nper为总付款期数pv为现值startperiod为计算中的首期付款期数从开始计数endperiod为计算中的末期type为付款时间类型。例如一笔住房抵押贷款的交易情况如下:年利率为期限为年现值为,。由上述已知条件可以计算出:r==np=*=。图那么该笔贷款在第下半年偿还的全部本金之中(第期到第期)为:=CUMPRINC(A,A*,A,,,)计算结果为:。该笔贷款在第一个月偿还的本金为:=CUMPRINC(A,A*,A,,,)计算结果为:。(三)求有价证券的贴现率DISCDISC函数返回有价证券的贴现率。其语法形式为DISC(settlement,maturity,pr,redemption,basis)其中settlement为有价证券的成交日即在发行日之后有价证券卖给购买者的日期maturity为有价证券的到日期到期日是有价证券有效期截止时的日期pr为面值为","的有价证券的价格redemption为面值为","的有价证券的清偿价格basis为日计数基准类型。例如:某债券的交易情况如下:成交日为年月日到期日为年月日价格为,清偿价格为,日计数基准为实际天数。那么该债券的贴现率为:DISC("","",,,)计算结果为:。返回定期付息有价证券的ACCRINT(issue,firstinterest,ACCRINT应计利息。settlement,rate,par,frequency,basis)返回到期一次性付息有价ACCRINTM(issue,maturity,rate,ACCRINTM证券的应计利息。par,basis)返回每个会计期间的折旧AMORDEGRC(cost,datepurchased,AMORDEGRC值。此函数是为法国会计firstperiod,salvage,period,系统提供的。rate,basis)返回每个会计期间的折旧AMORLINC(cost,datepurchased,AMORLINC值该函数为法国会计系firstperiod,salvage,period,统提供。rate,basis)返回当前付息期内截止到COUPDAYBS(settlement,maturity,COUPDAYBS成交日的天数。frequency,basis)返回成交日所在的付息期COUPDAYS(settlement,maturity,COUPDAYS的天数。frequency,basis)返回从成交日到下一付息COUPDAYSNC(settlement,maturity,COUPDAYSNC日之间的天数。frequency,basis)返回成交日过后的下一付COUPNCD(settlement,maturity,COUPNCD息日的日期。frequency,basis)返回成交日和到期日之间COUPNUM(settlement,maturity,COUPNUM的利息应付次数向上取frequency,basis)整到最近的整数。返回成交日之前的上一付COUPPCD(settlement,maturity,COUPPCD息日的日期。frequency,basis)返回一笔贷款在给定的CUMIPMT(rate,nper,pv,startperiod,CUMIPMTstartperiod到endperiod,type)endperiod期间累计偿还的利息数额。返回一笔贷款在给定的startperiod到CUMPRINC(rate,nper,pv,startperiod,CUMPRINCendperiod期间累计偿endperiod,type)还的本金数额。使用固定余额递减法计DB算一笔资产在给定期间内DB(cost,salvage,life,period,month)的折旧值。使用双倍余额递减法或其他指定方法计算一笔资DDBDDB(cost,salvage,life,period,factor)产在给定期间内的折旧值。DISC(settlement,maturity,pr,DISC返回有价证券的贴现率。redemption,basis)将按分数表示的价格转换为按小数表示的价格如DOLLARDE(fractionaldollar,DOLLARDE证券价格转换为小数表fraction)示的数字。将按小数表示的价格转换为按分数表示的价格。如DOLLARFRDOLLARFR(decimaldollar,fraction)证券价格转换为分数型数字。返回假设面值$的定DURATION(settlement,maturity,couponDURATION期付息有价证券的修正期yld,frequency,basis)限。期限定义为一系列现金流现值的加权平均值用于计量债券价格对于收益率变化的敏感程度。利用给定的名义年利率和EFFECT一年中的复利期次计算EFFECT(nominalrate,npery)实际年利率。基于固定利率及等额分期FV付款方式返回某项投资FV(rate,nper,pmt,pv,type)的未来值。基于一系列复利返回本金的未来值。函数FVSCHEDULEFVSCHDULE用于计算某项FVSCHEDULE(principal,schedule)投资在变动或可调利率下的未来值。返回一次性付息证券的利INTRATE(settlement,maturity,INTRATE率。investment,redemption,basis)基于固定利率及等额分期付款方式返回投资或贷IPMTIPMT(rate,per,nper,pv,fv,type)款在某一给定期次内的利息偿还额。返回由数值代表的一组现IRRIRR(values,guess)金流的内部收益率。计算特定投资期内要支付ISPMTISPMT(rate,per,nper,pv)的利息。返回假设面值$的有MDURATION(settlement,maturity,MDURATION价证券的Macauley修正coupon,yld,frequency,basis)期限。返回某一连续期间内现金MIRR(values,financerate,MIRR流的修正内部收益率。reinvestrate)基于给定的实际利率和年NOMINAL复利期数返回名义年利NOMINAL(effectrate,npery)率。基于固定利率及等额分期NPER付款方式返回某项投资NPER(rate,pmt,pv,fv,type)(或贷款)的总期数。通过使用贴现率以及一系列未来支出(负值)和收NPVNPV(rate,value,value,)入(正值)返回一项投资的净现值。返回首期付息日不固定的ODDFPRICE(settlement,maturity,ODDFPRICE面值$的有价证券的issue,firstcoupon,rate,yld,价格redemption,frequency,basis)返回首期付息日不固定的ODDFYIELD(settlement,maturity,ODDFYIELD有价证券(长期或短期)issue,firstcoupon,rate,pr,的收益率。redemption,frequency,basis)返回末期付息日不固定的ODDLPRICE(settlement,maturity,ODDLPRICE面值$的有价证券lastinterest,rate,yld,redemption,(长期或短期)的价格。frequency,basis)ODDLYIELD返回末期付息日不固定的ODDLYIELD(settlement,maturity,有价证券(长期或短期)lastinterest,rate,pr,redemption,的收益率。frequency,basis)基于固定利率及等额分期PMT付款方式返回贷款的每PMT(rate,nper,pv,fv,type)期付款额。基于固定利率及等额分期付款方式返回投资在某PPMTPPMT(rate,per,nper,pv,fv,type)一给定期间内的本金偿还额。PRICE(settlement,maturity,返回定期付息的面值PRICErate,yld,redemption,frequency,$的有价证券的价格。basis)返回折价发行的面值PRICEDISC(settlement,maturity,PRICEDISC$的有价证券的价格。discount,redemption,basis)返回到期付息的面值PRICEMAT(settlement,maturity,PRICEMAT$的有价证券的价格。issue,rate,yld,basis)返回投资的现值。现值为一系列未来付款的当前值PV的累积和。例如借入方PV(rate,nper,pmt,fv,type)的借入款即为贷出方贷款的现值。返回年金的各期利率。函数RATE通过迭代法计算RATERATE(nper,pmt,pv,fv,type,guess)得出并且可能无解或有多个解。返回一次性付息的有价证RECEIVED(settlement,maturity,RECEIVED券到期收回的金额。investment,discount,basis)返回某项资产在一个期间SLNSLN(cost,salvage,life)中的线性折旧值。返回某项资产按年限总和SYD折旧法计算的指定期间的SYD(cost,salvage,life,per)折旧值。返回国库券的等效收益TBILLEQ(settlement,maturity,TBILLEQ率。discount)返回面值$的国库券TBILLPRICE(settlement,maturity,TBILLPRICE的价格。discount)TBILLYIELD返回国库券的收益率。TBILLYIELD(settlement,maturity,pr)使用双倍余额递减法或其他指定的方法返回指定的任何期间内(包括部分VDB(cost,salvage,life,startperiod,VDB期间)的资产折旧值。函endperiod,factor,noswitch)数VDB代表可变余额递减法。返回一组现金流的内部收益率这些现金流不一定XIRR定期发生。若要计算一组XIRR(values,dates,guess)定期现金流的内部收益率请使用函数IRR。返回一组现金流的净现XNPVXNPV(rate,values,dates)值这些现金流不一定定期发生。若要计算一组定期现金流的净现值请使用函数NPV。返回定期付息有价证券的YIELD(settlement,maturity,rate,YIELD收益率函数YIELD用于pr,redemption,frequency,basis)计算债券收益率。返回折价发行的有价证券YIELDDISC(settlement,maturity,YIELDDISC的年收益率。pr,redemption,basis)返回到期付息的有价证券YIELDMAT(settlement,maturity,YIELDMAT的年收益率。issue,rate,pr,basis)(责任编辑Rayhotrayccidnetcom)九、Excel函数应用之信息函数(陆元婕年月日:)在Excel函数中有一类函数它们专门用来返回某些指定单元格或区域等的信息比如单元格的内容、格式、个数等这一类函数我们称为信息函数。在本文中我们将对这一类函数做以概要性了解同时对于其中一些常用的函数及其参数的应用做出示例。一、用于返回有关单元格格式、位置或内容的信息的函数CELLCELL函数用于返回某一引用区域的左上角单元格的格式、位置或内容等信息。其语法形式为CELL(infotype,reference)其中Infotype为一个文本值指定所需要的单元格信息的类型。Reference则表示要获取其有关信息的单元格。如果忽略则在infotype中所指定的信息将返回给最后更改的单元格。首先看一下infotype的可能值及相应的结果。类型引用中第一个单元格的引用文本类型。引用中单元格的列标。引用中单元格的行号。位置包含引用的文件名(包括全部路径)文本类型。如果包含目标引用的工作表尚未保存则返回空文本("")。如果单元格中的负值以不同颜色显示则为否则返回。与单元格中不同的数字格式相对应的文本值。下表列出不同格式的文本值。如果单元格中负值以不同颜色显示则在返回的文本值的结尾处加“”如果单元格中为正值或所有单元格均加括号则在文格式本值的结尾处返回“()”。如果单元格中为正值或全部单元格均加括号则为否则返回。与单元格中不同的“标志前缀”相对应的文本值。如果单元格文本左对齐则返回单引号(')如果单元格文本右对齐则返回双引号(")如果单元格文本居中则返回插入字符(^)如果单元格文本两端对齐则返回反斜线()如果是其他情况则返回空文本("")。如果单元格没有锁定则为如果单元格锁定则为。与单元格中的数据类型相对应的文本值。如果单元格为空则返回“b”。如果单元格包含文本常量则返回“l”如果单元格包含其他内容则返回“v”。取整后的单元格的列宽。列宽以默认字号的一个字符的宽度为单位。内容引用中左上角单元格的值:不是公式。再看一下当infotype为"format"以及引用为用内置数字格式设置的单元格时函数CELL返回文本值的情况。图函数CELL主要用于与其他电子表格程序兼容。在随后的示例中我们来学习一下如何使用CELL函数来获取单元格的格式、位置及内容的信息。例:想要获知单元格A到B区域内比如行号、列宽、单元格内容等信息。图二、用于计算区域内空白单元格的个数COUNTBLANKCOUNTBLANK用于计算指定单元格区域中空白单元格的个数。其语法形式为COUNTBLANK(range)其中Range为需要计算其中空白单元格个数的区域。需要注意的是即使单元格中含有返回值为空文本("")的公式该单元格也会计算在内但包含零值的单元格不计算在内。在如图所示的例子中单元格B包括公式=IF(A,,"",A)但该公式计算返回的值为空文本""所以该单元格被计算为空单元格。而单元格A为零值的单元格不计作空单元格。试比较图A与图B的结果的区别两者的差别在于图B中单元格B的公式为=IF(A,,"",A)计算后返回的结果为因此不计作空单元格。图A图B三、返回对应于错误类型的数字的函数ERRORTYPEERRORTYPE返回对应于MicrosoftExcel中某一错误值的数字或者如果没有错误则返回#NA。语法形式为ERRORTYPE(errorval)其中Errorval为需要得到其标号的一个错误值。尽管errorval可以为实际的错误值但它通常为一个单元格引用而此单元格中包含需要检测的公式。以下即为errorval的函数返回结果。图还记得逻辑函数IF吗,在函数IF中可以使用ERRORTYPE检测错误值并返回文本字符串(如消息)来取代错误值。具体参看示例。图四、返回有关当前操作环境的信息的函数INFOINFO函数用于返回有关当前操作环境的信息。其语法形式为INFO(typetext)其中Typetext为文本指明所要返回的信息类型。关于Typetext所返回的具体结果参看下表。Typetext返回当前目录或文件夹的路径。可用的内存空间以字节为单位。数据占用的内存空间。打开的工作簿中活动工作表的数目。A样式的绝对引用文本形式加上前缀“$A:”与Lotus的x版兼容。以当前滚动位置为基准返回窗口中可见的最右上角的单元格。当前操作系统的版本号文本值。当前的重新计算方式返回“自动”或“手动”。MicrosoftExcel的版本号文本值。操作系统名称:Macintosh="mac"Windows="pcdos"全部内存空间包括已经占用的内存空间以字节为单位。举例说明如何利用INFO函数获知当前操作环境的信息。图五、用来检验数值或引用类型的函数IS类函数Excel函数应用之函数简介Excel函数应用之数学和三角函数Excel函数应用之逻辑函数Excel函数应用之文本日期时间函数Excel函数应用之查询与引用函数Excel函数应用之统计函数Excel函数应用之工程函数Excel函数应用之财务函数IS类函数是指用来检验数值或引用类型的工作表函数在Excel中一共有九个此类函数。就几个函数包括:()ISBLANK如果值为空则返回TRUE()ISERR如果值为除#NA以外的任何错误值则返回TRUE()ISERROR如果值为任何错误值则返回TRUE()ISLOGICAL如果值为逻辑值则返回TRUE()ISNA如果值为#NA错误值则返回TRUE()ISNONTEXT如果值不是文本则返回TRUE()ISNUMBER如果值为数字则返回TRUE()ISREF如果值为引用则返回TRUE()ISTEXT如果值为文本则返回TRUE这些函数概括为IS类函数可以检验数值的类型并根据参数取值返回TRUE或FALSE。例如如果数值为对空白单元格的引用函数ISBLANK返回逻辑值TRUE否则返回FALSE。其语法形式为函数名(value)其中Value为需要进行检验的数值。针对不同的IS类函数分别为:空白(空白单元格)、错误值、逻辑值、文本、数字、引用值或对于以上任意参数的名称引用。需要说明的是IS类函数的参数value是不可转换的。例如在其他大多数需要数字的函数中文本值""会被转换成数字。然而在公式ISNUMBER("")中""并不由文本值转换成别的类型的值函数ISNUMBER返回FALSE。IS类函数主要用于检验公式计算结果。当它与函数IF结合在一起使用时可以提供一种方法用来在公式中查出错误值。图六、检验参数奇偶性的函数ISEVEN与ISODDISEVEN与ISODD为检验参数奇偶性的函数。其中ISEVEN是当参数number为偶数时返回TRUE否则返回FALSE。而ISODD则恰恰相反如果参数number为奇数返回TRUE否则返回FALSE。关于这两个函数的具体用法请参看示例。图七、返回转化为数值后的值得函数N函数N为返回转化为数值后的值。其语法形式为N(value)其中Value为要转化的值。函数N可以转化下表列出的值:图需要注意的是:一般情况下不必在公式中使用函数N因为Excel将根据需要自动对值进行转换。提供此函数是为了与其他电子表格程序兼容。MicrosoftExcel可将日期存储为可用于计算的序列号。默认情况下年月日的序列号是而年月日的序列号是这是因为它距年月日有天。而ExcelfortheMacintosh使用另外一个默认日期系统。关于函数N的具体用法可从以下示例中更详细地了解。图八、返回错误值#NA的函数NANA函数用于返回错误值#NA。错误值#NA表示"无法得到有效值"。建议使用NA标志空白单元格。在没有内容的单元格中输入#NA可以避免不小心将空白单元格计算在内而产生的问题(当公式引用到含有#NA的单元格时会返回错误值#NA)。其语法形式为NA()。需注意的是在函数名后面必须包括圆括号否则MicrosoftExcel无法识别该函数。也可直接在单元格中键入#NA。提供NA函数是为了与其他电子表格程序兼容。九、返回数值的类型的函数TYPE函数TYPE可用来返回数值的类型。当某一个函数的计算结果取决于特定单元格中数值的类型时可使用函数TYPE。其语法形式为TYPE(value)其中Value可以为任意MicrosoftExcel数值如数字、文本以及逻辑值等等。要说明的是当使用能接受不同类型数据的函数(例如函数ARGUMENT和函数INPUT)时函数TYPE十分有用。可以使用函数TYPE来查找函数或公式所返回的数据是何种类型。可以使用TYPE来确定单元格中是否含有公式。TYPE仅确定结果、显示或值的类型。如果某个值是一个单元格引用它所引用的另一个单元格中含有公式则TYPE将返回此公式结果值的类型。图以上我们对Excel函数的信息函数做了大致的了解。信息函数是用来返回某些指定单元格或区域等的信息比如单元格的内容、格式、个数等的一类函数。在实际应用中通常是与逻辑函数IF等配合使用来达到对单元格信息的确定。(责任编辑Rayhotrayccidnetcom)十、Excel函数应用之数据库函数(陆元婕年月日:)编者语:Excel函数应用系列到今天终于连载结束了如果大家有什么好的建议还请写信给我。在MicrosoftExcel中包含了一些工作表函数它们用于对存储在数据清单或数据库中的数据进行分析这些函数统称为数据库函数Dfunctions。一、函数的共同特点这一类函数具有一些共同特点:()每个函数均有三个参数:database、field和criteria。这些参数指向函数所使用的工作表区域。()除了GETPIVOTDATA函数之外其余十二个函数都以字母D开头。()如果将字母D去掉可以发现其实大多数数据库函数已经在Excel的其他类型函数中出现过了。比如DAVERAGE将D去掉的话就是求平均值的函数AVERAGE。二、数据库函数列表在Excel包含的数据库函数及其应用为:图三、数据库函数的参数含义Excel函数应用之函数简介Excel函数应用之数学和三角函数Excel函数应用之逻辑函由于数据库函数具有相同的三个参数因此笔者将首先介绍一下该类数函数的几个参数。然后再以具体示例来说明数据库函数的应用方法。Excel函数应用之文本日该类函数的语法形式为函数名称(database,field,criteria)。期时间函数Excel函数Database为构成数据清单或数据库的单元格区域。数据库是包含一应用之查询与组相关数据的数据清单其中包含相关信息的行为记录而包含数据引用函数的列为字段。数据清单的第一行包含着每一列的标志项。Excel函数应用之统计函Field为指定函数所使用的数据列。数据清单中的数据列必须在第一数行具有标志项。Field可以是文本即两端带引号的标志项如“使Excel函数用年数”或“产量”此外Field也可以是代表数据清单中数据列应用之工程函位置的数字:表示第一列表示第二列等等。数Excel函数Criteria为一组包含给定条件的单元格区域。可以为参数criteria应用之财务函指定任意区域只要它至少包含一个列标志和列标志下方用于设定条数件的单元格。Excel函数应用之信息函四、举例说明数、例:某果园的果树的高度、使用年数、产量与利润的统计数据表如图所示图希望求出()有多少种苹果树的树高在~英尺之间()苹果树与梨树的最大利润值是多少()高度大于英尺的苹果树的最小利润是多少()苹果树的总利润()高度大于英尺的苹果树的平均产量()果园中所有树种的平均使用年数()求苹果树和梨树产量的估算标准偏差、真实标准偏差、估算方差、真实方差。、求解步骤()创建空白工作簿或工作表将数据录入Excel中图()由于第一问需要求解有多少种苹果树的树高在~英尺之间因此建立查询条件高度在与之间图()利用函数DCOUNT求解满足条件的单元格数来计算高度在~英尺的苹果树的种类。公式为:DCOUNT(A:E,"使用年数",B:G)求出有一种苹果树满足条件。()由于第二问为求苹果树与梨树的最大利润值因此再建立一个查询条件梨树如图。图()利用函数DMAX求出苹果树与梨树的最大利润同理可以利用函数DMIN求出高度大于英尺苹果树的最小利润。具体公式为:=DMAX(A:E,"利润",A:A)求出苹果树与梨树的最大利润为=DMIN(A:E,"利润",A:B)求出高度大于英尺苹果树的最小利润为()利用函数DSUM可以求出所有苹果树的总利润。公式为:=DSUM(A:E,"利润",A:A)总利润为()其他各问均可采用类似的函数求解详细的公式如图所示。图五、需要注意的地方、可以为参数criteria指定任意区域只要它至少包含一个列标志和列标志下方用于设定条件的单元格。例如如果区域A:A在A中包含列标志“树种”在A中包含名称苹果树可将此区域命名为苹果树树种那么在数据库函数中就可使用该名称作为参数criteria。、虽然条件区域可以在工作表的任意位置但不要将条件区域置于数据清单的下方。因为如果使用“数据”菜单中的“记录单”命令在数据清单中添加信息新的信息将被添加在数据清单下方的第一行上。如果数据清单下方的行非空MicrosoftExcel将无法添加新的信息。、确定条件区域没有与数据清单相重叠。、若要对数据库的整个列进行操作需要在条件区域中的列标志下方输入一个空白行。六、关于条件的建立在上面的示例中我们简单介绍了条件区域的建立在这里详细介绍有关在Excel中利用高级条件进行数据筛选的方法。、有关概念条件是指所指定的限制查询或筛选的结果集中包含哪些记录的条件。例如上面示例中条件选择“高度”字段的值大于的记录:高度>。清单是指包含相关数据的一系列工作表行例如发票数据库或一组客户名称和电话号码。清单的第一行具有列标志。、建立条件区域的基本要求()在可用作条件区域的数据清单上插入至少三个空白行。()条件区域必须具有列标志。()请确保在条件值与数据清单之间至少留了一个空白行。如在上面的示例中A:F就是一个条件区域其中第一行为列标志如树种、高度。、筛选条件的建立在列标志下面的一行中键入所要匹配的条件。所有以该文本开始的项都将被筛选。例如如果您键入文本“Dav”作为条件MicrosoftExcel将查找“Davolio”、“David”和“Davis”。如果只匹配指定的文本可键入公式=''=text''其中“text”是需要查找的文本。如果要查找某些字符相同但其他字符不一定相同的文本值则可使用通配符。Excel中支持的通配符为:图、几种不同条件的建立()单列上具有多个条件如果对于某一列具有两个或多个筛选条件那么可直接在各行中从上到下依次键入各个条件。例如上面示例的条件区域显示“树种”列中包含“苹果树”或“梨树”的行。()多列上具有单个条件若要在两列或多列中查找满足单个条件的数据请在条件区域的同一行中输入所有条件。例如下面示例的条件区域显示所有在“高度”列中大于且“产量”大于的数据行。图()某一列或另一列上具有单个条件若要找到满足一列条件或另一列条件的数据请在条件区域的不同行中输入条件。例如上面示例的条件区域显示所有在“高度”列中大于的数据行。()两列上具有两组条件之一若要找到满足两组条件(每一组条件都包含针对多列的条件)之一的数据行请在各行中键入条件。例如下面的条件区域将显示所有在“树种”列中包含“苹果树”且“高度”大于的数据行同时也显示“樱桃树”的“使用年数”大于年的行。图()一列有两组以上条件若要找到满足两组以上条件的行请用相同的列标包括多列。例如上面示例的条件区域显示介于和之间的高度。()将公式结果用作条件Excel中可以将公式(公式:单元格中的一系列值、单元格引用、名称或运算符的组合可生成新的值。公式总是以等号(=)开始。)的计算结果作为条件使用。用公式创建条件时不要将列标志作为条件标记使用应该将条件标记置空或者使用清单中非列标志的标记。例如下面的条件区域显示在列C中其值大于单元格区域C:C平均值的行。=C>AVERAGE($C$:$C$)需要注意的是用作条件的公式必须使用相对引用来引用列标志(例如“高度”)或者引用第一个记录的对应字段。公式中的所有其他引用都必须是绝对引用并且公式必须计算出结果TRUE或FALSE。在本公式示例中C引用了数据清单中第一个记录(行)的字段(列C)。当然也可以在公式中使用列标志来代替相对的单元格引用或区域名称。当MicrosoftExcel在包含条件的单元格中显示错误值#NAME或#VALUE!时您可以忽略这些错误因为它们不影响列表的筛选。此外MicrosoftExcel在计算数据时不区分大小写。(责任编辑Rayhotrayccidnetcom)开根号:=sqrt()保留小数位数:=round(

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

文档小程序码

使用微信“扫一扫”扫码寻找文档

1

打开微信

2

扫描小程序码

3

发布寻找信息

4

等待寻找结果

我知道了
评分:

/126

EXCEL函数用法

VIP

在线
客服

免费
邮箱

爱问共享资料服务号

扫描关注领取更多福利