Excel-2003函数应用完全手册

Excel-2003函数应用完全手册
Excel 2003
函数应用完全手册
二〇〇四年二月二日
目录
一、函数应用基础..................................................................................................... (1)
(一)函数和公式..................................................................................................... (1)
1.什么是函数..................................................................................................... (1)
2.什么是公式..................................................................................................... (1)
(二)函数的参数..................................................................................................... . (1)
1.常量..................................................................................................... . (1)
2.逻辑值..................................................................................................... (1)
3.数组..................................................................................................... . (1)
4.错误值..................................................................................................... (1)
5.单元格引用..................................................................................................... . (1)
6.嵌套函数..................................................................................................... .. (2)
7.名称和标志..................................................................................................... . (2)
(三)函数输入方法..................................................................................................... . (2)
6.DMIN........................................................................................... .. (3)
7.DPRODUCT............................................................................... (3)
8.DSTDEV...................................................................................... (3)
9.DSTDEVP................................................................................... .. (4)
10.DSUM........................................................................................ . (4)
11.DV AR......................................................................................... (4)
12.DV ARP...................................................................................... .. (4)
13.GETPIVOTDATA.................................................................... .. (4)
(二)日期与时间函数........................................................................................................ . (4)
1.DATE........................................................................................... .. (4)
2.DATEV ALUE.............................................................................. (4)
3.DAY..............................................................................................
4.DAYS360..................................................................................... (5)
5.EDATE........................................................................................ . (5)
6.EOMONTH................................................................................. . (5)
7.HOUR.......................................................................................... (5)
8.MINUTE...................................................................................... (5)
9.MONTH...................................................................................... (5)
WORKDAYS ................................................................... . (5)
11.NOW........................................................................................... . (6)
12.SECOND................................................................................... .. (6)
13.TIME......................................................................................... .. (6)
14.TIMEV ALUE ........................................................................... (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)
PLEX.................................................................................. (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)
............................................................................................ . (16)
4.IS 类函数..................................................................................................... .. (17)
5.ISEVEN....................................................................................... .. (17)
6.ISODD......................................................................................... . (17)
7.N................................................................................................... .. (17)
8.NA................................................................................................ (17)
9.TYPE...........................................................................................
(七)逻辑运算符......................................................................................................... (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........................................................................................
3.CHOOSE..................................................................................... (19)
4.COLUMN.................................................................................... . (19)
5.COLUMNS.................................................................................. . (19)
6.HLOOKUP.................................................................................. . (19)
7.HYPERLINK ............................................................................. .. (19)
8.INDEX......................................................................................... . (20)
9.INDIRECT.................................................................................. .. (20)
10.LOOKUP................................................................................... (20)
11.MATCH..................................................................................... (20)
12.OFFSET.................................................................................... .. (21)
13.ROW.......................................................................................... .. (21)
14.ROWS........................................................................................ .. (21)
15.RTD............................................................................................ (21)
16.TRANSPOSE ........................................................................... .. (21)
17.VLOOKUP................................................................................ . (21)
(九)数学和三角函数........................................................................................................ (21)
1.ABS.............................................................................................. (21)
2.ACOS........................................................................................... . (21)
3.ACOSH........................................................................................ .. (22)
4.ASIN............................................................................................ (22)
5.ASINH......................................................................................... . (22)
6.ATAN...........................................................................................
(22)
7.ATAN2......................................................................................... . (22)
8.ATANH........................................................................................ .. (22)
9.CEILING..................................................................................... . (22)
BIN................................................................................... (22)
11.COS............................................................................................ (22)
12.COSH......................................................................................... . (23)
13.COUNTIF.................................................................................. .. (23)
14.DEGREES................................................................................. (23)
15.EVEN......................................................................................... . (23)
16.EXP............................................................................................ (23)
17.FACT......................................................................................... . (23)
(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..........................................................................................
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)
(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.......................................................................................
53.SUMPRODUCT ...................................................................... .. (27)
54.SUMSQ...................................................................................... .. (27)
55.SUMX2MY2 ............................................................................. .. (27)
56.SUMX2PY2 .............................................................................. . (27)
57.SUMXMY2................................................................................ . (28)
58.TAN............................................................................................ (28)
59.TANH......................................................................................... (28)
60.TRUNC...................................................................................... .. (28)
(十)统计函数........................................................................................................... (28)
1.A VEDEV...................................................................................... .. (28)
(28)
3.A VERAGEA................................................................................ . (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...................................................................................
14.COUNTBLANK ...................................................................... .. (30)
15.COUNTIF.................................................................................. .. (30)
16.COV AR...................................................................................... .. (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)
(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)
RGE......................................................................................。