116146.rar
大小:(151.25 KB)

只需: 10 个论坛币  马上下载

本附件包括：

• Excel.2003函数应用完全手册.pdf

[此贴子已经被作者于2007-5-27 13:25:39编辑过]

Excel 2003

函数应用完全手册

张迎新 著

目 录

一、函数应用基础......................................................................................................................................................... 1

（一）函数和公式................................................................................................................................................. 1

1.什么是函数................................................................................................................................................. 1

2.什么是公式................................................................................................................................................. 1

（二）函数的参数................................................................................................................................................. 1

1.常量............................................................................................................................................................. 1

2.逻辑值......................................................................................................................................................... 1

3.数组............................................................................................................................................................. 1

4.错误值......................................................................................................................................................... 1

5.单元格引用................................................................................................................................................. 1

6.嵌套函数..................................................................................................................................................... 2

7.名称和标志................................................................................................................................................. 2

（三）函数输入方法............................................................................................................................................. 2

1.“插入函数”对话框................................................................................................................................. 2

2.编辑栏输入................................................................................................................................................. 3

二、函数速查一览......................................................................................................................................................... 3

(一)数据库函数...................................................................................................................................................... 3

1.DAVERAGE..................................................................................................................................................... 3

2.DCOUNT......................................................................................................................................................... 3

3.DCOUNTA....................................................................................................................................................... 3

4.DGET............................................................................................................................................................. 3

5.DMAX............................................................................................................................................................. 3

6.DMIN............................................................................................................................................................. 3

7.DPRODUCT..................................................................................................................................................... 3

8.DSTDEV......................................................................................................................................................... 3

9.DSTDEVP....................................................................................................................................................... 4

10.DSUM........................................................................................................................................................... 4

11.DVAR........................................................................................................................................................... 4

12.DVARP......................................................................................................................................................... 4

13.GETPIVOTDATA ........................................................................................................................................... 4

(二)日期与时间函数.............................................................................................................................................. 4

1.DATE............................................................................................................................................................. 4

2.DATEVALUE ................................................................................................................................................... 4

3.DAY............................................................................................................................................................... 4

4.DAYS360....................................................................................................................................................... 5

5.EDATE........................................................................................................................................................... 5

6.EOMONTH....................................................................................................................................................... 5

7.HOUR............................................................................................................................................................. 5

8.MINUTE......................................................................................................................................................... 5

9.MONTH........................................................................................................................................................... 5

10.NETWORKDAYS .............................................................................................................................................

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..................................................................................................................................................... 1

33.IMSIN....................................................................................................................................................... 10

34.IMSQRT..................................................................................................................................................... 10

35.IMSUB....................................................................................................................................................... 10

36.IMSUM....................................................................................................................................................... 10

37.OCT2BIN................................................................................................................................................... 10

38.OCT2DEC................................................................................................................................................... 10

39.OCT2HEX................................................................................................................................................... 10

(五)财务函数........................................................................................................................................................ 10

1.ACCRINT..................................................................................................................................................... 10

2.ACCRINTM................................................................................................................................................... 11

3.AMORDEGRC ................................................................................................................................................. 11

4.AMORLINC................................................................................................................................................... 11

5.COUPDAYBS ................................................................................................................................................. 11

6.COUPDAYS................................................................................................................................................... 11

7.COUPDAYSNC ............................................................................................................................................... 11

8.COUPNUM..................................................................................................................................................... 11

9.COUPPCD..................................................................................................................................................... 11

10.CUMIPMT................................................................................................................................................... 11

11.CUMPRINC ................................................................................................................................................. 12

12.DB............................................................................................................................................................. 12

13.DDB........................................................................................................................................................... 12

14.DISC......................................................................................................................................................... 12

15.DOLLARDE ................................................................................................................................................. 12

16.DOLLARFR ................................................................................................................................................. 12

17.DURATION ................................................................................................................................................. 12

18.EFFECT..................................................................................................................................................... 12

19.FV............................................................................................................................................................. 12

20.FVSCHEDULE ............................................................................................................................................. 12

21.INTRATE................................................................................................................................................... 13

22.IPMT......................................................................................................................................................... 13

23.IRR........................................................................................................................................................... 13

24.ISPMT....................................................................................................................................................... 13

25.MDURATION ............................................................................................................................................... 13

26.MIRR......................................................................................................................................................... 13

27.NOMINAL................................................................................................................................................... 13

28.NPER......................................................................................................................................................... 13

29.NPV........................................................................................................................................................... 13

30.ODDFPRICE ............................................................................................................................................... 13

31.ODDFYIELD ............................................................................................................................................... 14

32.ODDLPRICE ............................................................................................................................................... 14

33.ODDLYIELD ............................................................................................................................................... 14

34.PMT........................................................................................................................................................... 14

35.PPMT......................................................................................................................................................... 14

36.PRICE....................................................................................................................................................... 14

37.PRICEDISC ............................................................................................................................................... 14

38.PRICEMAT ................................................................................................................................................. 15

5

39.PV............................................................................................................................................................. 15

40.RATE......................................................................................................................................................... 15

41.RECEIVED ................................................................................................................................................. 15

42.SLN........................................................................................................................................................... 15

43.SYD........................................................................................................................................................... 15

44.TBILLEQ................................................................................................................................................... 15

45.TBILLPRICE ............................................................................................................................................. 15

46.TBILLYIELD ............................................................................................................................................. 15

47.VDB........................................................................................................................................................... 15

48.XIRR......................................................................................................................................................... 16

49.XNPV......................................................................................................................................................... 16

50.YIELD....................................................................................................................................................... 16

51.YIELDDISC ............................................................................................................................................... 16

52.YIELDMAT ................................................................................................................................................. 16

(六)信息函数........................................................................................................................................................ 16

1.CELL........................................................................................................................................................... 16

2.ERROR.TYPE ............................................................................................................................................... 16

3.INFO........................................................................................................................................................... 16

4.IS 类函数.................................................................................................................................................. 17

5.ISEVEN....................................................................................................................................................... 17

6.ISODD......................................................................................................................................................... 17

7.N................................................................................................................................................................. 17

8.NA............................................................................................................................................................... 17

9.TYPE........................................................................................................................................................... 18

(七)逻辑运算符.................................................................................................................................................... 18

1.AND............................................................................................................................................................. 18

2.FALSE......................................................................................................................................................... 18

3.IF............................................................................................................................................................... 18

4.NOT............................................................................................................................................................. 18

5.OR............................................................................................................................................................... 18

6.TRUE........................................................................................................................................................... 18

(八)查找和引用函数............................................................................................................................................ 19

1.ADDRESS..................................................................................................................................................... 19

2.AREAS......................................................................................................................................................... 19

3.CHOOSE....................................................................................................................................................... 19

4.COLUMN....................................................................................................................................................... 19

5.COLUMNS..................................................................................................................................................... 19

6.HLOOKUP..................................................................................................................................................... 19

7.HYPERLINK ................................................................................................................................................. 19

8.INDEX......................................................................................................................................................... 20

9.INDIRECT................................................................................................................................................... 20

10.LOOKUP..................................................................................................................................................... 20

11.MATCH....................................................................................................................................................... 20

12.OFFSET..................................................................................................................................................... 21

13.ROW........................................................................................................................................................... 21

14.ROWS......................................................................................................................................................... 21

6

15.RTD........................................................................................................................................................... 21

16.TRANSPOSE ............................................................................................................................................... 21

17.VLOOKUP................................................................................................................................................... 21

(九)数学和三角函数............................................................................................................................................ 21

1.ABS............................................................................................................................................................. 21

2.ACOS........................................................................................................................................................... 21

3.ACOSH......................................................................................................................................................... 22

4.ASIN........................................................................................................................................................... 22

5.ASINH......................................................................................................................................................... 22

6.ATAN........................................................................................................................................................... 22

7.ATAN2......................................................................................................................................................... 22

8.ATANH......................................................................................................................................................... 22

9.CEILING..................................................................................................................................................... 22

10.COMBIN..................................................................................................................................................... 22

11.COS........................................................................................................................................................... 22

12.COSH......................................................................................................................................................... 23

13.COUNTIF................................................................................................................................................... 23

14.DEGREES................................................................................................................................................... 23

15.EVEN......................................................................................................................................................... 23

16.EXP........................................................................................................................................................... 23

17.FACT......................................................................................................................................................... 23

18.FACTDOUBLE ............................................................................................................................................. 23

19.FLOOR....................................................................................................................................................... 23

20.GCD........................................................................................................................................................... 23

21.INT........................................................................................................................................................... 23

22.LCM........................................................................................................................................................... 24

23.LN............................................................................................................................................................. 24

24.LOG........................................................................................................................................................... 24

25.LOG10....................................................................................................................................................... 24

26.MDETERM................................................................................................................................................... 24

27.MINVERSE ................................................................................................................................................. 24

28.MMULT....................................................................................................................................................... 24

29.MOD........................................................................................................................................................... 24

30.MROUND..................................................................................................................................................... 24

31.MULTINOMIAL ........................................................................................................................................... 25

32.ODD........................................................................................................................................................... 25

33.PI............................................................................................................................................................. 25

34.POWER....................................................................................................................................................... 25

35.PRODUCT................................................................................................................................................... 25

36.QUOTIENT ................................................................................................................................................. 25

37.RADIANS................................................................................................................................................... 25

38.RAND......................................................................................................................................................... 25

39.RANDBETWEEN ........................................................................................................................................... 25

40.ROMAN....................................................................................................................................................... 26

41.ROUND....................................................................................................................................................... 26

42.ROUNDDOWN ............................................................................................................................................... 26

7

43.ROUNDUP................................................................................................................................................... 26

44.SERIESSUM ............................................................................................................................................... 26

45.SIGN......................................................................................................................................................... 26

46.SIN........................................................................................................................................................... 26

47.SINH......................................................................................................................................................... 26

48.SQRT......................................................................................................................................................... 26

49.SQRTPI..................................................................................................................................................... 27

50.SUBTOTAL ................................................................................................................................................. 27

51.SUM........................................................................................................................................................... 27

52.SUMIF....................................................................................................................................................... 27

53.SUMPRODUCT ............................................................................................................................................. 27

54.SUMSQ....................................................................................................................................................... 27

55.SUMX2MY2 ................................................................................................................................................. 27

56.SUMX2PY2 ................................................................................................................................................. 27

57.SUMXMY2................................................................................................................................................... 28

58.TAN........................................................................................................................................................... 28

60.TRUNC....................................................................................................................................................... 28

(十)统计函数........................................................................................................................................................ 28

1.AVEDEV....................................................................................................................................................... 28

2.AVERAGE..................................................................................................................................................... 28

3.AVERAGEA................................................................................................................................................... 28

4.BETADIST................................................................................................................................................... 28

5.BETAINV..................................................................................................................................................... 28

6.BINOMDIST ................................................................................................................................................. 29

7.CHIDIST..................................................................................................................................................... 29

8.CHIINV....................................................................................................................................................... 29

9.CHITEST..................................................................................................................................................... 29

10.CONFIDENCE ............................................................................................................................................. 29

11.CORREL..................................................................................................................................................... 29

12.COUNT....................................................................................................................................................... 29

13.COUNTA..................................................................................................................................................... 30

14.COUNTBLANK ............................................................................................................................................. 30

15.COUNTIF................................................................................................................................................... 30

16.COVAR....................................................................................................................................................... 30

17.CRITBINOM ............................................................................................................................................... 30

18.DEVSQ....................................................................................................................................................... 30

19.EXPONDIST ............................................................................................................................................... 30

20.FDIST....................................................................................................................................................... 30

21.FINV......................................................................................................................................................... 30

22.FISHER..................................................................................................................................................... 31

23.FISHERINV ............................................................................................................................................... 31

24.FORECAST ................................................................................................................................................. 31

25.FREQUENCY ............................................................................................................................................... 31

26.FTEST....................................................................................................................................................... 31

27.GAMMADIST ............................................................................................................................................... 31

28.GAMMAINV ................................................................................................................................................. 31

8

29.GAMMALN................................................................................................................................................... 31

30.GEOMEAN................................................................................................................................................... 31

31.GROWTH..................................................................................................................................................... 32

32.HARMEAN................................................................................................................................................... 32

33.HYPGEOMDIST ........................................................................................................................................... 32

34.INTERCEPT ............................................................................................................................................... 32

35.KURT......................................................................................................................................................... 32

36.LARGE....................................................................................................................................................... 32

37.LINEST..................................................................................................................................................... 32

38.LOGEST..................................................................................................................................................... 33

39.LOGINV..................................................................................................................................................... 33

40.LOGNORMDIST ........................................................................................................................................... 33

41.MAX........................................................................................................................................................... 33

42.MAXA......................................................................................................................................................... 33

43.MEDIAN..................................................................................................................................................... 33

44.MIN........................................................................................................................................................... 33

45.MINA......................................................................................................................................................... 33

46.MODE......................................................................................................................................................... 33

47.NEGBINOMDIST ......................................................................................................................................... 34

48.NORMDIST ................................................................................................................................................. 34

49.NORMSINV ................................................................................................................................................. 34

50.NORMSDIST ............................................................................................................................................... 34

51.NORMSINV ................................................................................................................................................. 34

52.PEARSON................................................................................................................................................... 34

53.PERCENTILE ............................................................................................................................................. 34

54.PERCENTRANK ........................................................................................................................................... 34

55.PERMUT..................................................................................................................................................... 35

56.POISSON................................................................................................................................................... 35

57.PROB......................................................................................................................................................... 35

58.QUARTILE ................................................................................................................................................. 35

59.RANK......................................................................................................................................................... 35

60.RSQ........................................................................................................................................................... 35

61.SKEW......................................................................................................................................................... 35

62.SLOPE....................................................................................................................................................... 35

63.SMALL....................................................................................................................................................... 36

64.STANDARDIZE ........................................................................................................................................... 36

65.STDEV....................................................................................................................................................... 36

66.STDEVA..................................................................................................................................................... 36

67.STDEVP..................................................................................................................................................... 36

68.STDEVPA................................................................................................................................................... 36

69.STEYX....................................................................................................................................................... 36

70.TDIST....................................................................................................................................................... 37

71.TINV......................................................................................................................................................... 37

72.TREND....................................................................................................................................................... 37

73.TRIMMEAN ................................................................................................................................................. 37

74.TTEST....................................................................................................................................................... 37

9

75.VAR........................................................................................................................................................... 37

76.VARA......................................................................................................................................................... 37

77.VARP......................................................................................................................................................... 37

78.VARPA....................................................................................................................................................... 38

79.WEIBULL................................................................................................................................................... 38

80.ZTEST....................................................................................................................................................... 38

(十一)文本和数据函数........................................................................................................................................ 38

1.ASC............................................................................................................................................................. 38

2.CHAR........................................................................................................................................................... 38

3.CLEAN......................................................................................................................................................... 38

4.CODE........................................................................................................................................................... 38

5.CONCATENATE ............................................................................................................................................. 38

6.DOLLAR 或RMB ........................................................................................................................................... 38

7.EXACT......................................................................................................................................................... 39

8.FIND........................................................................................................................................................... 39

9.FINDB......................................................................................................................................................... 39

10.FIXED....................................................................................................................................................... 39

11.JIS........................................................................................................................................................... 39

12.LEFT 或LEFTB ......................................................................................................................................... 39

13.LEN 或LENB ............................................................................................................................................. 39

14.LOWER....................................................................................................................................................... 40

15.MID 或MIDB ............................................................................................................................................. 40

16.PHONETIC ................................................................................................................................................. 40

17.PROPER..................................................................................................................................................... 40

18.REPLACE 或REPLACEB .............................................................................................................................. 40

19.REPT......................................................................................................................................................... 40

20.RIGHT 或RIGHTB ..................................................................................................................................... 40

21.SEARCH 或SEARCHB ................................................................................................................................. 41

22.SUBSTITUTE ............................................................................................................................................. 41

23.T............................................................................................................................................................... 41

24.TEXT......................................................................................................................................................... 41

25.TRIM......................................................................................................................................................... 41

26.UPPER....................................................................................................................................................... 41

27.VALUE....................................................................................................................................................... 41

28.WIDECHAR ................................................................................................................................................. 41

三、函数应用案例──算账理财............................................................................................................................... 42

1.零存整取储蓄................................................................................................................................................... 42

2.还贷金额............................................................................................................................................................ 42

3.保险收益............................................................................................................................................................ 42

4.个税缴纳金额.................................................................................................................................................... 43

四、函数应用案例──信息统计............................................................................................................................... 43

1.性别输入............................................................................................................................................................ 43

2.出生日期输入.................................................................................................................................................... 44

3.职工信息查询.................................................................................................................................................... 44

4.职工性别统计.................................................................................................................................................... 45

5.年龄统计............................................................................................................................................................ 45

10

6.名次值统计........................................................................................................................................................ 45

7.位次阈值统计.................................................................................................................................................... 46

五、函数应用案例──管理计算............................................................................................................................... 46

1.授课日数............................................................................................................................................................ 46

2.折旧值计算........................................................................................................................................................ 46

3.客流均衡度计算................................................................................................................................................ 47

4.销售额预测........................................................................................................................................................ 47

5.客流与营业额的相关分析................................................................................................................................ 47