【資料名稱】：Excel2003函數(shù)應(yīng)用完全手冊

【資料作者】：EXCEL HOME

【資料日期】：2008

【資料語言】：中文

【資料格式】：DOC

【資料目錄和簡介】：

Excel2003函數(shù)應(yīng)用完全手冊


Excel 2003函數(shù)應(yīng)用完全手冊
目 錄
一、函數(shù)應(yīng)用基礎(chǔ)......................................................................................................................................... 10
（一）函數(shù)和公式......................................................................................................................................... 10
1.什么是函數(shù)................................................................................................................................................. 10
2.什么是公式................................................................................................................................................. 10
（二）函數(shù)的參數(shù)..........................................................................................................................................10
1.常量............................................................................................................................................................. 10
2.邏輯值......................................................................................................................................................... 10
3.數(shù)組............................................................................................................................................................. 10
4.錯誤值......................................................................................................................................................... 10
5.單元格引用................................................................................................................................................. 10
6.嵌套函數(shù)..................................................................................................................................................... 10
7.名稱和標(biāo)志................................................................................................................................................. 10
（三）函數(shù)輸入方法..................................................................................................................................... 11
1.“插入函數(shù)”對話框................................................................................................................................. 11
2.編輯欄輸入................................................................................................................................................. 11
二、函數(shù)速查一覽..........................................................................................................................................11
(一)數(shù)據(jù)庫函數(shù).............................................................................................................................................. 12
1.DAVERAGE..................................................................................................................................................... 12
3.DCOUNTA....................................................................................................................................................... 12
4.DGET............................................................................................................................................................. 12
5.DMAX............................................................................................................................................................. 12
6.DMIN............................................................................................................................................................. 12
7.DPRODUCT..................................................................................................................................................... 12
8.DSTDEV......................................................................................................................................................... 13
9.DSTDEVP....................................................................................................................................................... 13
10.DSUM........................................................................................................................................................... 13
11.DVAR........................................................................................................................................................... 13
12.DVARP......................................................................................................................................................... 13
13.GETPIVOTDATA ......................................................................................................................................... 13
(二)日期與時間函數(shù)...................................................................................................................................... 13
1.DATE............................................................................................................................................................. 13
2.DATEVALUE ................................................................................................................................................. 14
3.DAY............................................................................................................................................................... 14
4.DAYS360....................................................................................................................................................... 14
5.EDATE........................................................................................................................................................... 14
6.EOMONTH....................................................................................................................................................... 14
7.HOUR............................................................................................................................................................. 15
8.MINUTE......................................................................................................................................................... 15
9.MONTH........................................................................................................................................................... 15
10.NETWORKDAYS ........................................................................................................................................... 15
11.NOW............................................................................................................................................................. 15
12.SECOND....................................................................................................................................................... 15
13.TIME........................................................................................................................................................... 16
14.TIMEVALUE ............................................................................................................................................... 16
15.TODAY......................................................................................................................................................... 16
16.WEEKDAY..................................................................................................................................................... 16
17.WEEKNUM..................................................................................................................................................... 16
18.WORKDAY..................................................................................................................................................... 16
19.YEAR........................................................................................................................................................... 17
20.YEARFRAC ................................................................................................................................................. 17
(三)外部函數(shù).................................................................................................................................................. 17
1.EUROCONVERT ............................................................................................................................................. 17
2.SQL.REQUEST ............................................................................................................................................. 17
(四)工程函數(shù).................................................................................................................................................. 18
1.BESSELI....................................................................................................................................................... 18
2.BESSELJ....................................................................................................................................................... 18
3.BESSELK....................................................................................................................................................... 18
4.BESSELY....................................................................................................................................................... 18
5.BIN2DEC....................................................................................................................................................... 18
6.BIN2HEX....................................................................................................................................................... 18
7.BIN2OCT....................................................................................................................................................... 18
8.COMPLEX....................................................................................................................................................... 18
9.CONVERT....................................................................................................................................................... 19
10.DEC2BIN..................................................................................................................................................... 19
11.DEC2HEX..................................................................................................................................................... 19
12.DEC2OCT..................................................................................................................................................... 19
13.DELTA......................................................................................................................................................... 19
14.ERF............................................................................................................................................................. 19
15.ERFC........................................................................................................................................................... 19
16.GESTEP....................................................................................................................................................... 19
17.HEX2BIN..................................................................................................................................................... 19
18.HEX2DEC..................................................................................................................................................... 20
19.HEX2OCT..................................................................................................................................................... 20
20.IMABS......................................................................................................................................................... 20
21.IMAGINARY ............................................................................................................................................... 20
22.IMARGUMENT ............................................................................................................................................. 20
23.MCONJUGATE ............................................................................................................................................. 20
24.IMCOS......................................................................................................................................................... 2
25.IMDIV.........................................................................................................................................................20
26.IMEXP......................................................................................................................................................... 20
27.IMLN........................................................................................................................................................... 21
28.IMLOG10................................................................................................................................................... 21
29.IMLOG2..................................................................................................................................................... 21
30.IMPOWER................................................................................................................................................... 21
31.IMPRODUCT ............................................................................................................................................. 21
32.IMREAL..................................................................................................................................................... 21
33.IMSIN....................................................................................................................................................... 21
34.IMSQRT..................................................................................................................................................... 21
35.IMSUB....................................................................................................................................................... 21
36.IMSUM....................................................................................................................................................... 22
37.OCT2BIN................................................................................................................................................... 22
38.OCT2DEC................................................................................................................................................... 22
39.OCT2HEX................................................................................................................................................... 22
(五)財務(wù)函數(shù)................................................................................................................................................ 22
1.ACCRINT..................................................................................................................................................... 22
2.ACCRINTM................................................................................................................................................... 22
3.AMORDEGRC ............................................................................................................................................... 22
4.AMORLINC................................................................................................................................................... 22
5.COUPDAYBS ............................................................................................................................................... 23
6.COUPDAYS................................................................................................................................................... 23
7.COUPDAYSNC ............................................................................................................................................. 23
8.COUPNUM..................................................................................................................................................... 23
9.COUPPCD..................................................................................................................................................... 23
10.CUMIPMT................................................................................................................................................... 23
11.CUMPRINC ............................................................................................................................................... 23
12.DB............................................................................................................................................................. 24
13.DDB........................................................................................................................................................... 24
14.DISC......................................................................................................................................................... 24
15.DOLLARDE ............................................................................................................................................... 24
16.DOLLARFR ............................................................................................................................................... 24
17.DURATION ............................................................................................................................................... 24
18.EFFECT..................................................................................................................................................... 24
19.FV............................................................................................................................................................. 25
20.FVSCHEDULE ........................................................................................................................................... 25
21.INTRATE................................................................................................................................................... 25
22.IPMT......................................................................................................................................................... 25
23.IRR........................................................................................................................................................... 25
24.ISPMT....................................................................................................................................................... 25
25.MDURATION ............................................................................................................................................. 25
26.MIRR......................................................................................................................................................... 26
27.NOMINAL................................................................................................................................................... 26
28.NPER......................................................................................................................................................... 26
29.NPV........................................................................................................................................................... 26
30.ODDFPRICE ............................................................................................................................................. 26
31.ODDFYIELD ............................................................................................................................................. 26
32.ODDLPRICE ............................................................................................................................................. 26
33.ODDLYIELD ............................................................................................................................................. 27
34.PMT........................................................................................................................................................... 27
35.PPMT......................................................................................................................................................... 27
36.PRICE....................................................................................................................................................... 27
37.PRICEDISC ............................................................................................................................................. 27
38.PRICEMAT ............................................................................................................................................... 27
39.PV............................................................................................................................................................. 28
40.RATE......................................................................................................................................................... 28
41.RECEIVED ............................................................................................................................................... 28
42.SLN........................................................................................................................................................... 28
43.SYD........................................................................................................................................................... 28
44.TBILLEQ................................................................................................................................................... 28
45.TBILLPRICE ........................................................................................................................................... 28
46.TBILLYIELD ........................................................................................................................................... 28
47.VDB........................................................................................................................................................... 29
48.XIRR......................................................................................................................................................... 29
49.XNPV......................................................................................................................................................... 29
50.YIELD....................................................................................................................................................... 29
51.YIELDDISC ............................................................................................................................................. 29
52.YIELDMAT ............................................................................................................................................... 29
(六)信息函數(shù)................................................................................................................................................ 29
1.CELL........................................................................................................................................................... 29
2.ERROR.TYPE ............................................................................................................................................. 30
3.INFO........................................................................................................................................................... 30
4.IS 類函數(shù)................................................................................................................................................. 30
5.ISEVEN....................................................................................................................................................... 30
6.ISODD......................................................................................................................................................... 31
7.N................................................................................................................................................................. 31
8.NA............................................................................................................................................................... 31
9.TYPE........................................................................................................................................................... 31
(七)邏輯運(yùn)算符............................................................................................................................................ 31
1.AND............................................................................................................................................................. 31
2.FALSE......................................................................................................................................................... 31
3.IF............................................................................................................................................................... 32
4.NOT............................................................................................................................................................. 32
5.OR............................................................................................................................................................... 32
6.TRUE........................................................................................................................................................... 32
(八)查找和引用函數(shù).................................................................................................................................... 32
1.ADDRESS..................................................................................................................................................... 32
2.AREAS......................................................................................................................................................... 33
3.CHOOSE....................................................................................................................................................... 33
4.COLUMN....................................................................................................................................................... 33
5.COLUMNS..................................................................................................................................................... 33
6.HLOOKUP..................................................................................................................................................... 33
7.HYPERLINK ............................................................................................................................................... 33
8.INDEX......................................................................................................................................................... 34
9.INDIRECT................................................................................................................................................... 34
10.LOOKUP..................................................................................................................................................... 34
11.MATCH....................................................................................................................................................... 34
12.OFFSET..................................................................................................................................................... 35
13.ROW........................................................................................................................................................... 35
14.ROWS......................................................................................................................................................... 35
15.RTD........................................................................................................................................................... 35
16.TRANSPOSE ............................................................................................................................................. 35
17.VLOOKUP................................................................................................................................................... 35
(九)數(shù)學(xué)和三角函數(shù).................................................................................................................................... 36
1.ABS............................................................................................................................................................. 36
2.ACOS........................................................................................................................................................... 36
3.ACOSH......................................................................................................................................................... 36
4.ASIN........................................................................................................................................................... 36
5.ASINH......................................................................................................................................................... 36
6.ATAN........................................................................................................................................................... 36
7.ATAN2......................................................................................................................................................... 36
8.ATANH......................................................................................................................................................... 37
9.CEILING..................................................................................................................................................... 37
10.COMBIN..................................................................................................................................................... 37
11.COS........................................................................................................................................................... 37
12.COSH......................................................................................................................................................... 37
13.COUNTIF................................................................................................................................................... 37
14.DEGREES................................................................................................................................................... 38
15.EVEN......................................................................................................................................................... 38
16.EXP........................................................................................................................................................... 38
17.FACT......................................................................................................................................................... 38
18.FACTDOUBLE ........................................................................................................................................... 38
19.FLOOR....................................................................................................................................................... 38
20.GCD........................................................................................................................................................... 38
21.INT........................................................................................................................................................... 39
22.LCM........................................................................................................................................................... 39
23.LN............................................................................................................................................................. 39
24.LOG........................................................................................................................................................... 39
25.LOG10....................................................................................................................................................... 39
26.MDETERM................................................................................................................................................... 39
27.MINVERSE ............................................................................................................................................... 39
28.MMULT....................................................................................................................................................... 40
29.MOD...........................................................................................................................................................40
30.MROUND..................................................................................................................................................... 40
31.MULTINOMIAL ......................................................................................................................................... 40
32.ODD........................................................................................................................................................... 40
33.PI............................................................................................................................................................. 40
34.POWER....................................................................................................................................................... 40
35.PRODUCT...................................................................................................................................................41
36.QUOTIENT ............................................................................................................................................... 41
37.RADIANS................................................................................................................................................... 41
38.RAND......................................................................................................................................................... 41
39.RANDBETWEEN ......................................................................................................................................... 41
40.ROMAN....................................................................................................................................................... 41
41.ROUND....................................................................................................................................................... 42
42.ROUNDDOWN ............................................................................................................................................. 42
43.ROUNDUP................................................................................................................................................... 42
44.SERIESSUM ............................................................................................................................................. 42
45.SIGN......................................................................................................................................................... 42
46.SIN........................................................................................................................................................... 42
47.SINH......................................................................................................................................................... 42
48.SQRT......................................................................................................................................................... 43
49.SQRTPI..................................................................................................................................................... 43
50.SUBTOTAL ............................................................................................................................................... 43
51.SUM........................................................................................................................................................... 43
52.SUMIF....................................................................................................................................................... 43
53.SUMPRODUCT ........................................................................................................................................... 44
54.SUMSQ....................................................................................................................................................... 44
55.SUMX2MY2 ............................................................................................................................................... 44
56.SUMX2PY2 ............................................................................................................................................... 44
57.SUMXMY2................................................................................................................................................... 44
58.TAN........................................................................................................................................................... 44
60.TRUNC....................................................................................................................................................... 44
(十)統(tǒng)計(jì)函數(shù)................................................................................................................................................ 45
1.AVEDEV....................................................................................................................................................... 45
2.AVERAGE..................................................................................................................................................... 45
3.AVERAGEA................................................................................................................................................... 45
4.BETADIST................................................................................................................................................... 45
5.BETAINV..................................................................................................................................................... 45
6.BINOMDIST ............................................................................................................................................... 45
7.CHIDIST..................................................................................................................................................... 46
8.CHIINV....................................................................................................................................................... 46
9.CHITEST..................................................................................................................................................... 46
10.CONFIDENCE ........................................................................................................................................... 46
11.CORREL..................................................................................................................................................... 46
12.COUNT......................................................................................................................................................46
13.COUNTA..................................................................................................................................................... 46
14.COUNTBLANK ........................................................................................................................................... 47
15.COUNTIF................................................................................................................................................... 47
16.COVAR....................................................................................................................................................... 47
17.CRITBINOM ............................................................................................................................................. 47
18.DEVSQ....................................................................................................................................................... 47
19.EXPONDIST ............................................................................................................................................. 47
20.FDIST....................................................................................................................................................... 48
21.FINV......................................................................................................................................................... 48
22.FISHER..................................................................................................................................................... 48
23.FISHERINV ............................................................................................................................................. 48
24.FORECAST ............................................................................................................................................... 48
25.FREQUENCY ............................................................................................................................................. 48
26.FTEST....................................................................................................................................................... 48
27.GAMMADIST ............................................................................................................................................. 49
28.GAMMAINV ............................................................................................................................................... 49
29.GAMMALN................................................................................................................................................... 49
30.GEOMEAN................................................................................................................................................... 49
31.GROWTH..................................................................................................................................................... 49
32.HARMEAN................................................................................................................................................... 49
33.HYPGEOMDIST ......................................................................................................................................... 50
34.INTERCEPT ............................................................................................................................................. 50
35.KURT......................................................................................................................................................... 50
36.LARGE....................................................................................................................................................... 50
37.LINEST..................................................................................................................................................... 50
38.LOGEST..................................................................................................................................................... 50
39.LOGINV..................................................................................................................................................... 51
40.LOGNORMDIST ......................................................................................................................................... 51
41.MAX........................................................................................................................................................... 51
42.MAXA......................................................................................................................................................... 51
43.MEDIAN..................................................................................................................................................... 51
44.MIN........................................................................................................................................................... 51
45.MINA......................................................................................................................................................... 52
46.MODE......................................................................................................................................................... 52
47.NEGBINOMDIST ....................................................................................................................................... 52
48.NORMDIST ............................................................................................................................................... 52
49.NORMSINV ............................................................................................................................................... 52
50.NORMSDIST ............................................................................................................................................. 52
51.NORMSINV ............................................................................................................................................... 52
52.PEARSON................................................................................................................................................... 53
53.PERCENTILE ........................................................................................................................................... 53
54.PERCENTRANK ......................................................................................................................................... 53
55.PERMUT..................................................................................................................................................... 53
56.POISSON................................................................................................................................................... 53
57.PROB......................................................................................................................................................... 53
58.QUARTILE ............................................................................................................................................... 54
59.RANK......................................................................................................................................................... 54
60.RSQ........................................................................................................................................................... 54
61.SKEW......................................................................................................................................................... 54
62.SLOPE....................................................................................................................................................... 54
63.SMALL....................................................................................................................................................... 54
64.STANDARDIZE ......................................................................................................................................... 55
65.STDEV....................................................................................................................................................... 55
66.STDEVA..................................................................................................................................................... 55
67.STDEVP..................................................................................................................................................... 55
68.STDEVPA................................................................................................................................................... 55
69.STEYX....................................................................................................................................................... 56
70.TDIST....................................................................................................................................................... 56
71.TINV......................................................................................................................................................... 56
72.TREND....................................................................................................................................................... 56
73.TRIMMEAN ............................................................................................................................................... 56
74.TTEST....................................................................................................................................................... 56
75.VAR....................................................................................................................... ................................... 56
76.VARA............................................................................................................... ......................................... 57
77.VARP......................................................................................................... ............................................... 57
78.VARPA........................................................................................................................................ .............. 57
79.WEIBULL.................................................................................................................................... .............. 57
80.ZTEST........................................................................................................................................ .............. 57
(十一)文本和數(shù)據(jù)函數(shù)................................................................................................................................. 57
1.ASC........................................................................................................................................... .................. 57
2.CHAR......................................................................................................................................... .................. 58
3.CLEAN....................................................................................................................................... .................. 58
4.CODE......................................................................................................................................... .................. 58
5.CONCATENATE ......................................................................................................................... .................. 58
6.DOLLAR 或RMB ....................................................................................................................... .................. 58
7.EXACT......................................................................................................................................... ................ 58
8.FIND....................................................................................................................................... .................... 58
9.FINDB.......................................................................................................................................... .................... 59
10.FIXED........................................................................................................................................ .................... 59
11.JIS............................................................................................................................................ .................... 59
12.LEFT 或LEFTB ...................................................................................................................... ...................... 59
13.LEN 或LENB .......................................................................................................................................... ...... 59
14.LOWER....................................................................................................................................................... .... .59
15.MID 或MIDB ........................................................................................................................................ ......... 60
16.PHONETIC ...................................................................................................................................... .............. 60
17.PROPER...................................................................................................................................... .................... 60
18.REPLACE 或REPLACEB ...................................................................................................... ............................ 60
19.REPT............................................................................................................................. ..... ............................. 60
20.RIGHT 或RIGHTB .................................................................................................. ........................................ 60
21.SEARCH 或SEARCHB ........................................................................................ .............................................. 61
22.SUBSTITUTE ........................................................................................................ .......................................... 61
23.T................................................................................................................. ..................................................... 61
24.TEXT................................................................................................ ................................................................ 61
25.TRIM..................................................................................... ........................................................................... 61
26.UPPER........................................................................ ...................................................................................... 62
27.VALUE............................................................. ................................................................................................. 62
28.WIDECHAR .......................................... ............................................................................................................ 62
三、函數(shù)應(yīng)用案例──算賬理財....................................................................................................................... 62
1.零存整取儲蓄................................................................................................................................................... 62
2.還貸金額............................................................................................................................................................ 62
3.保險收益............................................................................................................................................................ 63
4.個稅繳納金額.................................................................................................................................................... 63
四、函數(shù)應(yīng)用案例──信息統(tǒng)計(jì)....................................................................................................................... 64
1.性別輸入............................................................................................................................................................ 64
2.出生日期輸入.................................................................................................................................................... 64
3.職工信息查詢.................................................................................................................................................... 65
4.職工性別統(tǒng)計(jì).................................................................................................................................................... 66
5.年齡統(tǒng)計(jì)............................................................................................................................................................ 66
6.名次值統(tǒng)計(jì)........................................................................................................................................................ 66
7.位次閾值統(tǒng)計(jì).................................................................................................................................................... 67
五、函數(shù)應(yīng)用案例──管理計(jì)算....................................................................................................................... 67
1.授課日數(shù)............................................................................................................................................................ 67
2.折舊值計(jì)算........................................................................................................................................................ 67
3.客流均衡度計(jì)算................................................................................................................................................ 68
4.銷售額預(yù)測........................................................................................................................................................ 68
5.客流與營業(yè)額的相關(guān)分析................................................................................................................................ 68
Excel2003函數(shù)應(yīng)用完全手冊.doc (620.5 KB)