Excel 2003 函数应用完全手册张迎新著

发表于 2017-9-21 15:56:14

目录
一、函数应用基础...................... 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

请点击此处下载

请先注册会员后在进行下载

已注册会员，请先登录后下载

提取码:q8q6
下载次数:2 状态:您未购买 售价:5℃
下载权限: 氦级以上或提升等级 [充铜板] [兑话费] [发资源赚铜板] [赚℃值]

发表于 2017-9-21 15:56:41

(九)数学和三角函数......... 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
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
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