excel 2003
函数应用完全手册
目 录
一、函数应用基础......................................................................................................................................... 1
(一)函数和公式............................................................................................................................................... 1 1.什么是函数..............................................................................................................................…................. 1 2.什么是公式..............................................................................................................................…................. 1
(二)函数的参数................................................................................................................................................ 1 1.常量..........................................................................................................................................….................. 1 2.逻辑值..........................................................................................................................................….............. 1 3.数组............................................................................................................................................…................ 1 4.错误值........................................................................................................................................…................ 1 5.单元格引用...............................................................................................................................…................. 1 6.嵌套函数.................................................................................................................................…................... 2 7.名称和标志........................................................................................................................…........................ 2
(三)函数输入方法............................................................................................................................................. 2 1.“插入函数”对话框...............................................................................................................................….. 2 2.编辑栏输入...............................................................................................................................................….. 3
二、函数速查一览............................................................................................................................................ 3
(一)数据库函数............................................................................................................................................. 3
1.daverage................................................................................................................................................... 3 2.dcount...........................................................................................................................................…......... 3 3.dcounta.........................................................................................................................................…......... 3 4.dget..........................................................................................................................................………......... 3 5.dmax..........................................................................................................................................……........... 3 6.dmin............................................................................................................................................………....... 3 7.dproduct.................................................................................................................................................... 3 8.dstdev.........................................................................................................................................……......... 3 9.dstdevp........................................................................................................................................…............ 4 10.dsum.............................................................................................................................................……........ 4 11.dvar.............................................................................................................................................………..... 4 12.dvarp...........................................................................................................................................……......... 4 13.getpivotdata........................................................................................................................................... 4
(二)日期与时间函数....................................................................................................................................... 4
1.date...............................................................................................................................................…….......... 4 2.datevalue..................................................................................................................................….............. 4 3.day.................................................................................................................................................……........... 4 4.days360.................................................................................................................................……................... 5 5.edate.....................................................................................................................................…...................... 5 6.eomonth............................................................................................................................…........................ 5 7.hour........................................................................................................................................….................... 5 8.minute...................................................................................................................................…..................... 5 9.month........................................................................................................................................................... 5
10.networkdays ......................................................................................................................................... 5 11.now........................................................................................................................................…................... 6 12.second................................................................................................................................….................... 6 13.time.......................................................................................................................................…................... 6 14.timevalue ................................................................................................................................................ 6 15.today....................................................................................................................................…................... 6 16.weekday..................................................................................................................................................... 6 17.weeknum.................................................................................................................................................... 6 18.workday..................................................................................................................................................... 6 19.year......................................................................................................................................….................... 7 20.yearfrac ................................................................................................................................................... 7
(三)外部函数..................................................................................................................................................... 7
1.euroconvert .............................................................................................................................................. 7 2.sql.request ........................................................................................................................…..................... 7
(四)工程函数..................................................................................................................................................... 7
1.besseli.......................................................................................................................................…….............. 7 2.besselj........................................................................................................................................……............. 8 3.besselk.............................................................................................................................……....................... 8 4.bessely......................................................................................................................................……............... 8 5.bin2dec....................................................................................................................................……................. 8 6.bin2hex..................................................................................................................................……................... 8 7.bin2oct...................................................................................................................................……................... 8 8.complex................................................................................................................................…....................... 8 9.convert.................................................................................................................................…….................... 8 10.dec2bin................................................................................................................................…….................... 8 11.dec2hex...............................................................................................................................…….................... 8 12.dec2oct.................................................................................................................................…….................. 8 13.delta......................................................................................................................................…….................. 8 14.erf...........................................................................................................................................…….................. 8 15.erfc........................................................................................................................................……................. 9 16.gestep....................................................................................................................................…….................. 9 17.hex2bin..................................................................................................................................……................. 9 18.hex2dec..................................................................................................................................……................. 9 19.hex2oct..................................................................................................................................……................. 9 20.imabs........................................................................................................................................……................ 9 21.imaginary ..............................................................................................................................…................... 9 22.imargument .....................................................................................................................…........................ 9 23.mconjugate .....................................................................................................................…........................ 9 24.imcos.....................................................................................................................................……................... 9 25.imdiv....................................................................................................................................…….................... 9 26.imexp...............................................................................................................................……......................... 9 27.imln......................................................................................................................................…….................... 9 28.imlog10............................................................................................................................……...................... 10 29.imlog2..............................................................................................................................……...................... 10 30.impower...............................................................................................................................…….................. 10 31.improduct .........................................................................................................................…...................... 10
32.imreal......................................................................................................................................….............. 10 33.imsin..........................................................................................................................................……........... 10 34.imsqrt......................................................................................................................................…............... 10 35.imsub..........................................................................................................................................….…......... 10 36.imsum..............................................................................................................................................…........ 10 37.oct2bin...........................................................................................................................................…........ 10 38.oct2dec........................................................................................................................................….......... 10 39.oct2hex........................................................................................................................................….......... 10
(五)财务函数................................................................................................................................................... 10
1.accrint.................................................................................................................................…................... 10 2.accrintm.......................................................................................................................….......................... 11 3.amordegrc ..................................................................................................................….......................... 11 4.amorlinc.......................................................................................................................….......................... 11 5.coupdaybs ................................................................................................................................................. 11 6.coupdays........................................................................................................................…......................... 11 7.coupdaysnc ............................................................................................................................................... 11 8.coupnum.........................................................................................................................….......................... 11 9.couppcd.........................................................................................................................…........................... 11 10.cumipmt......................................................................................................................…............................ 11 11.cumprinc ...................................................................................................................…............................ 12 12.db...................................................................................................................................……........................ 12 13.ddb....................................................................................................................................……..................... 12 14.disc..........................................................................................................................................…….............. 12 15.dollarde ............................................................................................................................…................... 12 16.dollarfr ............................................................................................................................…................... 12 17.duration .............................................................................................................................…................... 12 18.effect....................................................................................................................................……............... 12 19.fv..................................................................................................................................................…….......... 12 20.fvschedule ..........................................................................................................................…................. 12 21.intrate..................................................................................................................................……............... 13 22.ipmt.........................................................................................................................................……............... 13 23.irr............................................................................................................................................……............... 13 24.ispmt......................................................................................................................................……................ 13 25.mduration ........................................................................................................................…..................... 13 26.mirr.......................................................................................................................................……................. 13 27.nominal..............................................................................................................................…..................... 13 28.nper.......................................................................................................................................……................. 13 29.npv.............................................................................................................................................…….............. 13 30.oddfprice ..............................................................................................................................…................. 13 31.oddfyield .....................................................................................................…......................................... 14 32.oddlprice .....................................................................................................…......................................... 14 33.oddlyield ......................................................................................................…........................................ 14 34.pmt.......................................................................................................................…….................................. 14 35.ppmt......................................................................................................................……................................. 14 36.price......................................................................................................................……................................ 14 37.pricedisc ............................................................................................................…................................... 14 38.pricemat ...............................................................................................................…................................. 15
39.pv................................................................................................................................……............................ 15 40.rate............................................................................................................................…............................ 15 41.received ................................................................................................................................................. 15 42.sln................................................................................................................................….......................... 15 43.syd.................................................................................................................................…......................... 15 44.tbilleq..........................................................................................................................…........................ 15 45.tbillprice .....................................................................................................................…....................... 15 46.tbillyield ..................................................................................................................….......................... 15 47.vdb.................................................................................................................................….......................... 15 48.xirr...............................................................................................................................….......................... 16 49.xnpv..............................................................................................................................…........................... 16 50.yield.............................................................................................................................….......................... 16 51.yielddisc ...................................................................................................................….......................... 16 52.yieldmat ................................................................................................................................................. 16
(六)信息函数.................................................................................................................................................. 16 1.cell...........................................................................................................................…................................ 16 2.error.type ..................................................................................................................…........................... 16 3.info.................................................................................................................................……........................ 16 4.is 类函数..........................................................................................................................……....................... 17 5.iseven..............................................................................................................................……....................... 17 6.isodd....................................................................................................................................……..................... 17 7.n................................................................................................................................................................. 17 8.na............................................................................................................................................................... 17 9.type........................................................................................................................................................... 18
(七)逻辑运算符................................................................................................................................................ 18 1.and............................................................................................................................................................. 18 2.false......................................................................................................................................................... 18 3.if............................................................................................................................................................... 18 4.not............................................................................................................................................................. 18 5.or............................................................................................................................................................... 18 6.true........................................................................................................................................................... 18
(八)查找和引用函数........................................................................................................................................ 19
1.address..................................................................................................................................................... 19 2.areas......................................................................................................................................................... 19 3.choose....................................................................................................................................................... 19 4.column....................................................................................................................................................... 19 5.columns..................................................................................................................................................... 19 6.hlookup..................................................................................................................................................... 19 7.hyperlink ................................................................................................................................................. 19 8.index......................................................................................................................................................... 20 9.indirect................................................................................................................................................... 20 10.lookup..................................................................................................................................................... 20 11.match....................................................................................................................................................... 20 12.offset..................................................................................................................................................... 21 13.row........................................................................................................................................................... 21
14.rows......................................................................................................................................................... 21 15.rtd........................................................................................................................................................... 21 16.transpose ............................................................................................................................................... 21 17.vlookup................................................................................................................................................... 21
(九)数学和三角函数............................................................................................................................................ 21
1.abs............................................................................................................................................................. 21 2.acos........................................................................................................................................................... 21 3.acosh......................................................................................................................................................... 22 4.asin........................................................................................................................................................... 22 5.asinh......................................................................................................................................................... 22 6.atan........................................................................................................................................................... 22 7.atan2......................................................................................................................................................... 22 8.atanh......................................................................................................................................................... 22 9.ceiling..................................................................................................................................................... 22 10.combin..................................................................................................................................................... 22 11.cos........................................................................................................................................................... 22 12.cosh......................................................................................................................................................... 23 13.countif................................................................................................................................................... 23 14.degrees................................................................................................................................................... 23 15.even......................................................................................................................................................... 23 16.exp........................................................................................................................................................... 23 17.fact......................................................................................................................................................... 23 18.factdouble ............................................................................................................................................. 23 19.floor....................................................................................................................................................... 23 20.gcd........................................................................................................................................................... 23 21.int........................................................................................................................................................... 23 22.lcm........................................................................................................................................................... 24 23.ln............................................................................................................................................................. 24 24.log........................................................................................................................................................... 24 25.log10....................................................................................................................................................... 24 26.mdeterm................................................................................................................................................... 24 27.minverse ................................................................................................................................................. 24 28.mmult....................................................................................................................................................... 24 29.mod........................................................................................................................................................... 24 30.mround..................................................................................................................................................... 24 31.multinomial ........................................................................................................................................... 25 32.odd........................................................................................................................................................... 25 33.pi............................................................................................................................................................. 25 34.power....................................................................................................................................................... 25 35.product................................................................................................................................................... 25 36.quotient ................................................................................................................................................. 25 37.radians................................................................................................................................................... 25 38.rand......................................................................................................................................................... 25 39.randbetween ........................................................................................................................................... 25 40.roman....................................................................................................................................................... 26 41.round....................................................................................................................................................... 26 42.rounddown ............................................................................................................................................... 26