Excel2003函数应用完全手册.pdf

目 录

一、函数应用基础......................................................................................................................................................... 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
3
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
4
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
5
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
6
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
7
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
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
8
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
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
9
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
10
6.名次值统计........................................................................................................................................................ 45
7.位次阈值统计.................................................................................................................................................... 46

五、函数应用案例──管理计算............................................................................................................................... 46
1.授课日数............................................................................................................................................................ 46
2.折旧值计算........................................................................................................................................................ 46
3.客流均衡度计算................................................................................................................................................ 47
4.销售额预测........................................................................................................................................................ 47
5.客流与营业额的相关分析................................................................................................................................ 47