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
2
(八)查找和引用函数.... 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 43.ROUNDUP. 26 44.SERIESSUM ... 26 45.SIGN. 26 46.SIN. 26 47.SINH. 26 48.SQRT. 26 49.SQRTPI... 27
50.SUBTOTAL ..... 27 51.SUM. 27 52.SUMIF..... 27
53.SUMPRODUCT . 27 54.SUMSQ..... 27
55.SUMX2MY2 ..... 27 56.SUMX2PY2 ..... 27 57.SUMXMY2. 28 58.TAN. 28 59.TANH...... 28 60.TRUNC..... 28
(十)统计函数...... 28
1.AVEDEV..... 28 2.AVERAGE... 28 3.AVERAGEA....... 28 4.BETADIST. 28 5.BETAINV... 28
6.BINOMDIST ..... 29 7.CHIDIST... 29 8.CHIINV..... 29 9.CHITEST... 29
10.CONFIDENCE . 29
11.CORREL... 29 12.COUNT..... 29 13.COUNTA... 30
14.COUNTBLANK . 30 15.COUNTIF. 30 16.COVAR..... 30 17.CRITBINOM ... 30 18.DEVSQ..... 30 19.EXPONDIST ... 30 20.FDIST..... 30 21.FINV....... 30 22.FISHER... 31 23.FISHERINV ... 31 24.FORECAST ..... 31 25.FREQUENCY ... 31 26.FTEST..... 31
27.GAMMADIST ... 31 28.GAMMAINV ..... 31 29.GAMMALN. 31 30.GEOMEAN. 31 31.GROWTH... 32 32.HARMEAN. 32
33.HYPGEOMDIST . 32 34.INTERCEPT ... 32 35.KURT. 32 36.LARGE..... 32 37.LINEST... 32 38.LOGEST... 33 39.LOGINV... 33
40.LOGNORMDIST . 33 41.MAX. 33 42.MAXA. 33 43.MEDIAN... 33 44.MIN. 33 45.MINA. 33 46.MODE. 33
47.NEGBINOMDIST ..... 34 48.NORMDIST ..... 34 49.NORMSINV ..... 34 50.NORMSDIST ... 34 51.NORMSINV ..... 34
3
52.PEARSON. 34 53.PERCENTILE . 34 54.PERCENTRANK . 34 55.PERMUT... 35 56.POISSON. 35 57.PROB. 35
58.QUARTILE ..... 35 59.RANK. 35 60.RSQ. 35 61.SKEW. 35 62.SLOPE..... 35 63.SMALL..... 36
64.STANDARDIZE . 36 65.STDEV..... 36 66.STDEVA... 36 67.STDEVP... 36 68.STDEVPA. 36 69.STEYX..... 36 70.TDIST..... 37 71.TINV. 37 72.TREND..... 37
73.TRIMMEAN ..... 37 74.TTEST..... 37 75.VAR. 37 76.VARA. 37 77.VARP. 37 78.VARPA..... 38 79.WEIBULL. 38 80.ZTEST..... 38
(十一)文本和数据函数.... 38 1.ASC... 38 2.CHAR. 38 3.CLEAN. 38 4.CODE... 38
5.CONCATENATE . 38 6.DOLLAR 或RMB . 38 7.EXACT. 39 8.FIND. 39 9.FINDB. 39 10.FIXED..... 39
11.JIS. 39
12.LEFT 或LEFTB ..... 39 13.LEN 或LENB . 39 14.LOWER..... 40 15.MID 或MIDB . 40 16.PHONETIC ..... 40 17.PROPER... 40
18.REPLACE 或REPLACEB 40 19.REPT. 40
20.RIGHT 或RIGHTB . 40
21.SEARCH 或SEARCHB ... 41 22.SUBSTITUTE . 41 23.T..... 41 24.TEXT. 41 25.TRIM. 41 26.UPPER..... 41 27.VALUE..... 41
28.WIDECHAR..... 41
三、函数应用案例──算账理财 42 1.零存整取储蓄. 42 2.还贷金额.. 42 3.保险收益.. 42 4.个税缴纳金额.. 43
四、函数应用案例──信息统计.. 43 1.性别输入.. 43 2.出生日期输入.. 44 3.职工信息查询.. 44 4.职工性别统计.. 45 5.年龄统计.. 45 6.名次值统计...... 45 7.位次阈值统计.. 46
五、函数应用案例──管理计算.. 46 1.授课日数.. 46 2.折旧值计算 46
3.客流均衡度计算.... 47 4.销售额预测 47
5.客流与营业额的相关分析.. 47
Excel 2003常用函数完全手册(完善版)
