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