2023年Ecel函數(shù)和公式的應(yīng)用

Excel2003函鼎應(yīng)用黨全手冊

目錄

一、函數(shù)應(yīng)用基礎(chǔ)........................................................................1

（一）函數(shù)和公式........................................................................1

L什么是函數(shù)............................................................................1

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

（二）函數(shù)的參數(shù)........................................................................1

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

2.邏輯值...............................................................................1

3.數(shù)組.................................................................................1

4.錯誤值...............................................................................1

5.單元格引用..........................................................................1

6.嵌套函數(shù)............................................................................2

7.名稱和標(biāo)志..........................................................................2

（三）函數(shù)輸入方法......................................................................2

L“插入函數(shù)”對話框....................................................................2

2.編輯欄輸入............................................................................3

二、函數(shù)速查一覽........................................................................3

（一）數(shù)據(jù)庫函數(shù)...........................................................................3

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

2.DC0UNT...............................................................................3

3.DC0UNTA..............................................................................3

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

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

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

7.DPR0DUCT.............................................................................3

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

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

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

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

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

13.GETPIV0TDATA.......................................................................4

（二）日期與時間函數(shù)......................................................................4

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

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

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

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

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

6.E0M0NTH..............................................................................5

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

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

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

10.NETWORKDAYS........................................................................5

11.NOW.................................................................................6

12.SECOND..............................................................................6

13.TIME................................................................................6

14.TIMEVALUE..........................................................................6

15.TODAY...............................................................................6

16.WEEKDAY.............................................................................6

17.WEEKNUM.............................................................................6

18.WORKDAY.............................................................................6

19.YEAR................................................................................7

20.YEARFRAC..........................................................................7

（三）外部函數(shù).............................................................................7

1.EUROCONVERT........................................................................7

2.SQL.REQUEST........................................................................7

（四）工程函數(shù).............................................................................7

1.BESSELI..............................................................................7

2.BESSELJ..............................................................................8

3.BESSELK..............................................................................8

4.BESSELY..............................................................................8

5.BIN2DEC..............................................................................8

6.BIN2HEX..............................................................................8

7.BIN20CT..............................................................................8

8.COMPLEX..............................................................................8

9.CONVERT..............................................................................8

10.DEC2BIN.............................................................................8

11.DEC2HEX.............................................................................8

12.DEC20CT.............................................................................8

13.DELTA...............................................................................8

14.ERF.................................................................................8

15.ERFC................................................................................9

16.GESTEP..............................................................................9

17.HEX2BIN.............................................................................9

18.HEX2DEC.............................................................................9

19.HEX20CT.............................................................................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.IML0G10............................................................................10

29.IML0G2.............................................................................10

30.IMPOWER.............................................................................10

31.IMPRODUCT.........................................................................10

32.IMREAL.............................................................................10

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

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

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

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

37.0CT2BIN............................................................................10

38.0CT2DEC............................................................................10

39.0CT2HEX............................................................................10

（五）財務(wù)函數(shù)............................................................................10

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

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

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

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

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

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

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

8.COUPNUM.............................................................................Il

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

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

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

（六）信息函數(shù)............................................................................16

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

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

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

4.IS類函數(shù)...........................................................................17

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

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

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

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

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

（七）邏輯運(yùn)算符..........................................................................18

LAND...................................................................................18

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

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

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

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

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

（八）查找和引用函數(shù)......................................................................19

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

（九）數(shù)學(xué)和三角函數(shù).....................................................................21

LABS..................................................................................21

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

（十）統(tǒng)計函數(shù)............................................................................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

ll.CORREL.............................................................................29

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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