Excel表格函数应用大全

Excel表格函数应用大全

1、SUMPRODUCT函数：该函数的功能是在给定的几组数组中将数组间对应的元素相乘并返回乘积之和。例如：如图1，假如想运算B3：C6和C3：E6这两组区域的值，能够用以下公式：

〝=Sumproduct(B3:C6,D3:E6)〞。

图1

2、ABS函数：假如在A1、B1单元格中分别输入120、90，那么假如要求A1与B1之间的差的绝对值，

能够在C1单元格中输入以下公式：〝=ABS(A1-B1)〞。

3、IF函数：如图2，假如C3单元格的数据大于D3单元格，那么在E3单元格显示〝完成任务，超出：〞，否那么显示〝未完成任务，差额：〞，能够在E3单元格中输入以下公式：〝=IF(C3>D3, 〝完成任务，

超出：〞,〞未完成任务，差额：〞〞。

图2

4、Ceiling函数：该数值向上舍入基础的倍数。如图3，在C3单元格中输入以下公式：

〝=CEILING(B3,C3)〞；而〝=FLOOR(B3,C3)〞那么是向下舍入。

图3

5、GCD函数：该函数运算最大公约数。如图4，假如要运算B3：D3这一区域中3个数字的最大公约

数，能够在E3单元格中输入以下公式：〝=GCD(B3,C3,D3)〞。

图4

6、INT函数：该函数是向下舍入取整函数。如图5，假如要运算显示器和机箱的购买数量，能够在

E3单元格中输入以下公式：〝=INT(D3/C3)〞。

图5

7、LCM函数：该函数是运算最小公倍数。如图6，假如要运算B3：D3这一区域中3个数字的最小公

倍数，能够在E3单元格中输入以下公式：〝=LCM(B3,C3,D3)〞。

图6

8、LN函数：该函数是运算自然对数，公式为：〝=LN(B3)〞。 9、LOG函数：该函数是运算指定底数的对数，公式为：〝=LOG10(B3)〞。

10、MOD函数：该函数是运算两数相除的余数。如图7，判定C3能否被B3整除，能够在D4单元格中

输入以下公式：〝=IF(MOD(B3,C3)=0,\"是\否\")〞。

图7

11、PI函数：使用此函数能够返回数字3.141592653579，即数学常量PI，可精确到小数点后14位。如图8，运算球体的面积，能够在C4单元格中输入以下公式：〝=PI()*(B3^2)*4)〞；运算球体

的体积，能够在D4单元格中输入以下公式：〝= (B3^3)*〔4* PI())〕/3〞。

图8

12、POWER函数：此函数用来运算乘幂。如图9，第一在单元中输入底数和指数，然后在D3中输入以

下公式：〝=POWER(B3,C3)〞。

图9

13、PRODUCT函数：此函数能够对所有的以参数形式给出的数字相乘，并返回乘积。例如：某企业2005年度贷款金额为100000元，利率为1.5%，贷款期限为12个月。如图10所示，直截了当在单元格E4

中输入以下公式：〝 =PRODUCT(B4,C4,D4)〞。

图10

14、RADIANS函数：此函数是用来将弧度转换为角度的。能够在C3单元格中输入以下公式：〝=RADIANS

(B3)〞。

15、RAND函数：此函数能够返回大于等于0及小于1的平均分布随机数，每次运算工作表时都将返回一个新的数值。假如要使用函数RAND生成一个随机数，同时使之不随单元格的运算而改变，能够在编辑栏中输入〝=RAND()〞，保持编辑状态，然后按[F9]键，将公式永久性地改为随机数。例如：在全班50名同学中以随机方式抽出20名进行调查，如图11，在单元格中输入开始号码以及终止号

码，然后在单元格B4中输入以下公式：〝=1+RAND()*49〞。

图11

16、ROUND函数：此函数为四舍五入函数。如图12，例如：将数字〝12.3456〞按照指定的位数进行

四舍五入，能够在D3单元格中输入以下公式：〝=ROUND(B3,C3)〞。

17、ROUNDDOWN函数：此函数为向下舍入函数。例如：出租车的计费标准是：起步价为5元，前

10公里每一公里跳表一次，以后每半公里就跳表一次，每跳一次表要加收2元。输入不同的公里数，

如图13所示，然后运算其费用。能够在C3单元格中输入以下公式：〝=IF(B3<=10,5+ROUNDDOWN(B3,0)*2,20+ROUNDDOWN((B3-10)*2,0)*2)〞。

图13

18、ROUNDUP函数：此函数为向上舍入函数。例如：现在网吧的治理一样是采纳向上舍入法，不满一个单元按照一个单位运算。现假设每30分钟计价0.5元，请运算如图14中所示的上网所花费的费用。1〕运算上网天数：第一在单元格C3中输入以下公式：〝=B3-A3〞；2〕运算上网分钟数：上

网分钟数实际上就等于上网天数乘以60再乘以24，因此应在单元格D3中输入以下公式：〝=C3*60*24〞；3〕运算计费时刻：本例中规定每30分钟计费一次，不满30分钟以30分钟计价，因此应在单元格E3中输入以下公式：〝=ROUNDUP(D3/30,0)〞；4〕运算上网费用：在单元格G3

中输入以下公式：〝=E3*F3〞。

图14

19、SUBTOTAL函数：使用该函数能够返回列表或者数据库中的分类汇总。通常利用[数据]—[分类

汇总]菜单项能够专门容易地创建带有分类汇总的列表。

Function_num

函数返回值

Function_num

函数返回值

Function_num

1

Average

2

Count

3

Counta

5

Min

6

Product

7

Stdev

函数返回值

9

Sum

Var

10

11

4

warp

max

8

Stdevp

例如某班部分同学的考试成绩如图15，1〕显示最低的语文成绩：第一在单元格B9中输入〝显示最低的语文成绩〞的字样，然后在单元格E9中输入以下公式：〝=SUBTOTAL(5,C3:C7)〞；2〕显示最高的数学成绩：第一在单元格B10中输入〝显示最高的数学成绩〞的字样，然后在单元格E10中输

入以下公式：〝=SUBTOTAL(4,D3:D7)〞。

图15

20、运算库存量和奖金：假设某公司在月底要依照职员的业绩发放工资并进行产品的库存统计，本例中规定职员的差不多工资为600元，奖金按照销售业绩的8%提成，总工资等于差不多工资与奖金之和。如图16，1〕在工作表中输入相应的数据信息；2〕运算〝现存库量〞：在单元格C15中输入以

下公式：〝=C14-SUM(C3:C9)〞；3〕运算〝销售业绩〞：在单元格G3中输入以下公式：〝=SUMPRODUCT(C3:F3,$C$13:$F$13)〞,函数SUMPRODUCT是运算数组C3：F3与数组$C$13:$F$13乘积的和，用数学公式表示出来确实是：〝=10*3050.5+10*1560.99+5*44.9+20*2119〞；4〕运算奖金：奖金是按照销售业绩的8%提成得到的，如此运算出来的结果可能会是小数，不行找零

钱，因此那个地点采纳向上舍入的方式得到整数，在单元格H3中输入以下公式：

〝=ROUNDUP(G3*8%,0)〞；5〕运算总工资：由于总工资=差不多工资+奖金，因此在单元格J3中输

入以下公式：〝=SUM(H3:I3)〞。

图16

21、运算工资和票面金额：假设某公司的销售人员的销售情形如图17所示，按照销售业绩的5%运算销售提成，下面需要结合上例中的函数来运算销售人员的销售业绩以及奖金工资，然后再运算动身放

工资时需要预备的票面数量。1〕运算销售业绩：在单元格H13中输入以下公式：

〝=SUMPRODUCT(C3:G3,$C$11:$G$11)〞；2〕运算提成：在本例中假设提成后显现小于1元的金额

那么舍入为1，因此需要使用ROUNDUP函数，在单元格I3中输入以下公式：

〝=ROUNDUP(H3*5%,0)〞；3〕运算工资：在单元格K3中输入以下公式：〝=I3+J3〞；4〕运算100元的面值：在单元格L3中输入以下公式：〝=INT(K3/$L$2)〞；5〕运算50元的面值：在单元格M3

中输入以下公式：〝=INT(MOD(K3,$L$2)/$M$2)〞，此公式是使用MOD函数运算发放〝MOD(K3,$L$2)〞张100元后剩下的工资，然后利用取整函数INT得到50元票面的数量；6〕运算10元的面值：在单元格N3中输入以下公式：〝=INT(MOD(K3,$M$2)/$N$2)〞；7〕运算5元的面值：在单元格O3中输入以下公式：〝=INT(MOD(K3,$N$2)/$O$2)〞；8〕运算1元的面值：在单元格P3

中输入以下公式：〝=INT(MOD(K3,$O$2)/$P$2)〞。

图17

22、DATE函数：在实际工作中经常会用到此函数来显示日期。例如：如图18，在单元格中输入相应的年、

月和图书馆日等信息，然后在单元格E3中输入以下公式：〝=DATE(B3,C3,D3)〞。

图18

23、DATEIF函数：假设有两个日期——开始日期和截止日期，那么能够利用DATEIF函数来运算它们之间相差的年数、月数或者天数等。如图19，在单元格D3中输入以下公式：〝=DATEDIF(B3,C3,\"y\")〞。

图19

24、DAYS360函数：该函数运算两个日期之间的天数，在财务中经常会用到，假如财务系统是基于一年12个月同时每月30天，能够使用该函数关心运算借款天数或者支付款项等。例如：某企业不同时刻的贷款如图20所示，然后利用DAYS360函数来运算其借款的时刻，同时运算出还款利息。1〕运算〝借款天数〞：在单元格D3中输入以下公式：〝=DAYS360(B3,C3)〞；2〕运算〝还款利息〞：在单元格G3中输

入以下公式：〝=D3*E3*F〞。

图20

25、WEEKDAY函数：使用此函数能够返回某个日期为星期几。语法：WEEKDAY

〔serial_number,return_type〕：其中参数serial_number代表要查找的那一天的日期，参数return_type为确

定返回值类型的数字，详细内容如下表：

例如：运算当前日期是星期几：如图21所示，在单元格B3中输入运算当前日期的公式：〝=WEEKDAY(B3,2)〞。

图21

26、WEEKNUM函数：使用此函数能够运算一年中的第几周。例如：2006年6月9日是星期五，下面利用WEEKNUM函数运算在参数不同的情形下返回的周数。如图22所示，在单元格B3中输入运算当前日期的公式：

〝=WEEKNUM(B3,C3)〞。

图22

27、WORKDAY函数：使用此函数能够返回某个日期〔起始日期〕之前或之后相隔指定工作日的某一日期的日期值，工作日不包括周末和专门指定的日期。假设某出版社要求某个编辑从2006年3月1日起开始写稿，利用80天将其完成〔其中不包括三天节假日〕，现在能够利用WORKDAY函数运算出完成日期。如图23所示，在

单元格中输入上述信息，然后在单元格C7中输入以下公式：〝=WORKDAY(C2,C3,C4:C6)〞。

图23

28、运算年假天数和工龄补贴：假设某公司规定，职员任职满1年的开始有年假，第1至5年每年7天，第6年开始每年10天。截止到2005年6月9日，以工龄运算每年补贴100元，任职不足一年的按每人50元运算。如图24所示：1〕第一在工作表中输入数据信息，然后依照公司规定的内容在单元格F5中输入以下公式：〝=IF(DATEDIF($D5,TODAY(),\"y\")<1,\"入职不够一年\今年没到期\〞，以此能够运算出职员的休假天数；2〕在单元格G5中输入以下公式：〝=IF(DATEDIF($D5,DATE($C$2,6,9),\"y\")>=1,DATEDIF($D5,DATE($C$2,6,9),\"y\")*100,50)〞，以

此可运算出职员的工龄补贴。

图24

29、运算火车站寄存包裹费用：在火车站寄存包裹是按小时数收费的。有些按整小时计数，有些按半小时计数，没有超过半小时的以半小时计，半小时以上一小时以内的按一小时计。同时包裹的大小不同收费也不同，在本例中假设大的每小时6元，中型的每小时4元，小型的每小时2元，运算在火车站寄存包裹的费用。如图25所示：

1〕运算寄存天数：第一输入相关的信息，然后在单元格E4中输入以下公式：

〝=IF(TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINUTE(D4),SECOND(D4)),DATE(YEAR(D4),MONTH(D4),DAY(D4))-DATE(YEAR(C4),MONTH(C4),DAY(C4))-1,DATE(YEAR(D4),MONTH(D4),DAY(D4))-DATE(YEAR(C4),MONTH(C4),DAY(C4)))〞，现在可运算出所有型号的包裹寄存的天数，在此公式中用到

了IF函数，函数中的条件为

〝TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINUTE(D4),SECOND(D4)〞，它是用来判

定取走时刻是否超过了寄存时刻，假如条件为真那么表示还没有超过一天，那么寄存的天数确实是〝DATE(YEAR(D4),MONTH(D4),DAY(D4))-DATE(YEAR(C4),MONTH(C4),DAY(C4))-1〞，即走取的日期减去

寄存的日期再减1，假如时刻超过了，那么寄存的天数确实是

〝DATE(YEAR(D4),MONTH(D4),DAY(D4))-DATE(YEAR(C4),MONTH(C4),DAY(C4))〞，即取走的日期与寄存

时的日期之差；2)运算寄存小时数：在单元格F4中输入以下公式：

〝=IF(TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINUTE(D4),SECOND(D4)),HOUR(1-TIME(HOUR(C4),MINUTE(C4),SECOND(C4))+TIME(HOUR(D4),MINUTE(D4),SECOND(D4))),HOUR(TIME(HOUR(D4),MINUTE(D4),SECOND(D4))-TIME(HOUR(C4),MINUTE(C4),SECOND(C4))))〞，此公式中的IF函数中的条件与运算天数时的条件是一样的，也是判定取走时刻是否超过了寄存时刻，假如没有超过小时数那么为〝TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINUTE(D4),SECOND(D4))〞，其中

〝TIME(HOUR(C4),MINUTE(C4),SECOND(C4)〞表示寄存时刻的序列数，其中

〝TIME(HOUR(D4),MINUTE(D4),SECOND(D4)〞表示取走时刻的序列数。再通过加减运算得到小时数，假如超

过了小时数那么为

〝HOUR(TIME(HOUR(D4),MINUTE(D4),SECOND(D4))-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)))〞，即直

截了当用取走时刻减去存在时刻，取小时数；3)运算寄存分钟数：在单元格G4中输入以下公式：〝=IF(TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINUTE(D4),SECOND(D4)),MINUTE(1-TIME(HOUR(C4),MINUTE(C4),SECOND(C4))+TIME(HOUR(D4),MINUTE(D4),SECOND(D4))),MINUTE(TIME(HOUR(D4),MINUTE(D4),SECOND(D4))-TIME(HOUR(C4),MINUTE(C4),SECOND(C4))))〞，现在即可运算出所有型号的包裹寄存的分钟数，其公式形式和运算小时数的公式相似，只是将HOUR换成了MINUTE，其判定条

件和前面的一样，假如取走时刻没有超过寄存时刻，分钟数那么为

〝MINUTE(1-TIME(HOUR(C4),MINUTE(C4),SECOND(C4))+TIME(HOUR(D4),MINUTE(D4),SECOND(D4)))〞。

假如超过了，分钟数那么为

〝MINUTE(TIME(HOUR(D4),MINUTE(D4),SECOND(D4))-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)))〞，即直截了当用取走时刻减去寄存时刻，取分钟数；4)运算寄存的累计小时数：在单元格H4中输入以下公式：〝=E4*24+F4+IF(G4=0,0,IF(G4<=30,0.5,1))〞，在该公式中，〝E4*24〞表示将天数转换为小时数，在将分钟转换为小时数时，使用IF函数来判定分钟数的范畴，假设分钟数小于等于30那么返回0.5小时，否那么返回1小时，然后将所有的小时数相加即可得到累计小时数；5〕运算寄存总费用：在单元格J4中输入以下公式：〝=I4*H4〞，

现在即可运算出寄存包裹的费用。

图25

30、AND函数：当所有参数的逻辑值为真时，AND函数的返回值为TRUE；只要有一个参数的逻辑值为假，该函数的返回值那么为FALSE。例如：假设有一组调查数据或者调查结果，如图26所示，下面依照各个年龄段〔18～34、35～49、50～和65以上〕对数据进行分类，以判定出各个年龄段的调查结果。1〕

统计年龄在18～34岁之间的人的调查结果，在单元格E7中输入以下公式：

〝=IF(AND(C7>=18,C7<=34),D7,\"\")〞，在该公式中使用AND函数判定单元格C7中的值是否在18～34岁之间，然后依照返回的逻辑值再利用IF函数得到结果，即假如为真那么返回单元格D7中的值，否那么返回

空值；2〕统计年龄在35～49岁之间的人的调查结果，在单元格F7中输入以下公式：

〝=IF(AND(C7>=35,C7<=49),D7,\"\")〞；3〕统计年龄在65岁以上的人的调查结果，在单元格H7中输入以

下公式：〝=IF(AND(C7>=50,C7<=),D7,\"\")〞。

图26

31、OR函数：判定逻辑值并集的运算结果，在所有的参数中只要有一个逻辑值为TRUE，该函数的返回值即为TRUE。例如某企业的职员姓名和出生年份两列值，如图27所示，然后依照输入的年份判定职员中是否有这一年出生的人，同时统计出共有几个。1〕在单元格D3中输入判定值〝1975〞，即判定是否有1975年出生的人，然后在单元格E3中输入以下公式：〝{=OR(D3=C3:C8}〞，在该公式中，表示将D2单元格中的

值与数据区域〝C3:C8〞中的每一个值作比较，判定是否相等。假如任何一人比较结果为真，函数OR那么返回TRUE，也确实是D3单元格中的值位于那个列表中。由因此在一个数组中查找是否存在某个指定的值，因此公式要以数组的形式输入，输入公式后要按[Ctrl]+[Shift]+[Enter]组合键完成；3)运算1975年出生的人数，在单元格E3中输入以下公式：〝{=SUM(IF(D3=C3:C8,1,0) }〞，在该公式中先使用IF函数将单元格D3中的值与数据区域〝C3:C8〞中的每一个值进行比较，假如两个值相等那么返回1，否那么返回0。然后利用SUM函数对所有的返回值求和，最后得到的数据确实是〝1975〞显现的次数，即有几个人是1975

年出生的。该公式要以数组公式的形式输入。

图27

32、ADDRESS函数：该函数使用方法如图28所示。

图28

33、AREAS函数：该函数使用方法如图29所示。

图29

34、CHOOSE函数：例如评定学生成绩，利用该函数能够评定销售人员的业务能力，还能够返回成绩的档次以及是否及格等，其运算方法差不多上一样的。下面以学生成绩表为例看一下CHOOSE函数的应用方法。1)第一在工作表中输入如图30所示的学生成绩，然后在单元格F3中输入以下公式：〝=SUM(C3:E3)/3〞，现在即可运算出学生的平均成绩；2〕利用CHOOSE函数运算成绩名次，在G3单元格中输入以下公式：

〝=CHOOSE(IF(F3>=90,1,IF(F3>=80,2,IF(F3>=70,3,IF(F3>=60,4,5)))),\"优秀\良好\一样\及格\不及格\")〞，在该公式中用到了多个IF函数，用以判定平均成绩属于哪个区间，再使用CHOOSE函数返回不同情形下的结果，那个地点把成绩分为了5个档次，即平均分90以上的是〝优秀〞、80到90之间的

是〝良好〞、70到80之间的为〝一样〞、60到70之间的为〝及格〞、60以下的为〝不及格〞。

图30

35、COLUMN函数：该函数使用方法如图31所示。

图31

36、COLUMNS函数：该函数使用方法如图32所示。

图32

37、HLOOKUP函数：在实际工作中此函数的应用专门广泛，下面举例说明。在运算销售奖金时，不同的销售业绩对应不同的奖金比例，因此第一需要使用HLOOKUP函数查询奖金比例，然后再运算销售奖金。1〕输入如图33所示的业绩奖金以及职员的销售业绩；2〕查找适当的奖金比例，在单元格D7中输入以下公式：

〝=HLOOKUP(D3,$B$3:$G$4,2)〞；3〕分别在单元格D8、D9、D10中输入以下公式：

〝=HLOOKUP(E3,$B$3:$G$4,2)〞、〝=HLOOKUP(F3,$B$3:$G$4,2)〞、〝=HLOOKUP(G3,$B$3:$G$4,2)〞；3〕

运算奖金：在单元格E7中输入以下公式：〝=C7*D7〞。

图33

38、HYPERLINK函数：该函数使用方法如图34所示。

图34

39、INDEX函数：该函数返回指定单元格中的内容。假设在图35所示的课程表中：1〕查找出星期三第4节课所上的课程：只需在单元格C13中输入以下公式：〝=INDEX(C3:H9,C12,C11)〞；2〕返回星期五的所有课程：选中单元格区域〝J2:J9〞，然后输入以下公式：〝{=INDEX(B2:H9,,6)}〞，现在即可显示出星期五的所有课程；3〕运算路程：各地之间相隔的距离如图36所示，那么如何运算A地和D地之间相隔的距离呢？只需在单元格C11中输入以下公式：〝=INDEX(B2:G7,MATCH(C9,B2:B7,0),MATCH(C10,B2:G2,0))〞。

图35

图36

40、INDIRECT函数：该函数使用方法如图37所示。

图37

41、LOOKUP函数：该函数用于在行〔或列〕中查找并返回数值。例如某公司职员的工资表如图38所示，

查找姓名：第一在单元格C11中输入编辑〝0004〞，然后在单元格C12中输入以下公式：〝=LOOKUP(C11,B3:B9,C3:C9)〞，也可输入公式：〝=LOOKUP(C11,B3:C9)〞，现在即可查找到编辑为

〝0004〞的职员的姓名。查找差不多工资、实发工资的公式类似姓名的公式。

图38

42、MATCH函数：在数组中查找数值的相应位置。该函数使用方法如图39所示。

图39

43、OFFSET函数：OFFSET函数的功能是返回的引用能够为一个单元格或者单元格区域，同时能够指定返回的行数或者列数。其语法为：OFFSET(reference,rows,cols,height,width)。其中reference表示作为偏移量参照系的引用区域，此参数必须为单元格或相邻单元格区域的引用，否那么函数OFFSET返回错误值〝#VALUE！〞；rows表示相关于偏移量参照系的左上角单元格上〔下〕偏移的行数；cols表示相关于偏移量参照系的左上角单元格左〔右〕偏移的列数；height表示高度，即所要返回的引用区域的行数，此参数必须为正数；width表示宽度，即

所要返回的引用区域的列数，此参数必须为正数。该函数的应用方法如图40所示。

图40

44、ROW函数：该函数的应用方法如图41所示。

图41

45、ROWS函数：该函数的应用方法如图42所示。

图42

46、VLOOKUP函数：VLOOKUP函数的功能是在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。其语法为：VLOOKUP〔lookup_value,table_array,col_index_num,range_lookup〕。其中lookup_value为需要在数组第一列中查找的数值；col_index_num为table_array中待返回的匹配值的序列号； range_lookup为一个逻辑值，用以指明函数VLOOKUP返回时是精确匹配依旧近似匹配。该函数的应用方法如

图43所示。

图43

47、运算所得税：假设规定：工资、薪金所得按月征收，对每月收入超过800元以上的部分征税，适用5%至45%的9级超额累进税率，即：纳税所得额〔计税工资〕=每月工资〔薪金〕所得—800元〔不计税部分〕；超额累进应纳税款=纳税所得额×按全额累进所用税率—速算扣除数。当工资为〝5800〞和〝3000〞元的时候，运算其应缴纳的所得税的金额，具体操作步骤如下：1〕如图44所示，在单元格C15和C16中输入工资金额〝5800〞

和〝3000〞，然后在单元格D15中输入

〝=IF($C15<=$F$2,0,($C15-$F$2)*VLOOKUP(($C15-$F$2),$D$4:$F$12,2,1)-VLOOKUP(($C15-$F$2),$D$4:$F$12,3,1))〞，现在即可运算出缴纳的所得税；2〕在单元格E15中输入以下公式〝=$C15-$D15〞，现在即可运算出

实发工资。

图44

48、运算考核成绩：在公司或者企业内部为了鼓舞职员更加积极地工作经常会制定一些考核制度，下面以运算某公司职员第一季度的考核成绩为例，介绍一下部分查找函数的实际应用方法，具体的操作步骤如下：1〕新建一个工作薄，将其中的工作表Sheet1、Sheet2和Sheet3分别命名为〝各季度缺勤记录〞、〝意见〞和〝第一季度考核表〞，然后在前两个工作表中输入所需要的数据信息，如图45、图46所示；2〕在工作表〝第一季度考核表〞中输入职员编号、职员姓名以及相关的标题项目，如图47所示；3〕运算〝缺勤记录〞：在单元格D3中输入以下公式：〝=INDEX(各季度缺勤记录!D2:$G$9,2,1)〞；4〕运算〝出勤成绩〞：在单元格E3中输入以下公式：〝=IF(D3<30,30-D3,0)〞，即假如缺勤30天以上出勤成绩确实是0分；5〕运算〝工作能力〞：在单元格F3中输入以下公式：〝=INDEX(意见!D3:E9,1,1)〞；6〕运算〝工作态度〞：在单元格G3中输入以下公式：〝=VLOOKUP(B3,意见!$B$3:$E$9,4)〞；7〕运算〝季度考核成绩〞：在单元格H3中输入以下公式：

〝=SUM(E3:G3)〞，即出勤成绩、工作能力及工作态度之和。

图45

图46

图47

49、ASC函数：此函数用来将全角转换为半角。该函数的用法见图48所示。

图48

50、CONCATENATE函数：此函数用来合并字符串。该函数的用法见图49所示。

图49

51、DOLLAR函数：此函数用来将数字转换为货币形式。该函数的用法见图50所示。

图50

52、RMB函数：此函数用来将数字转换为货币形式。该函数的用法见图51所示。

图51

53、EXACT函数：此函数用来判定字符串是否相同。该函数的用法见图52所示。

图52

54、FIND函数：此函数用来查找文本串。该函数的用法见图53所示。

图53

55、FIXED函数：此函数对数字进行格式化。该函数的用法见图54所示。

图54

56、LEFT函数：返回第一个或前几个字符。例如：在实际工作中，要取得 号码的区号或者取得人名的姓氏等都能够利用LEFT函数来完成。1〕猎取区号：假设一些 号码，如图55所示，下面利用LEFT函数猎取这些 号码的区域。在单元格C3中输入以下公式：〝=LEFT(B3,4)〞；2〕输入称呼：第一在工作表中输入的姓名和性别，如图56所示，然后在单元格E3中输入以下公式：〝=LEFT(C3,1)&IF(D3=\"男\先生\女士\")〞，该公式表示

在姓名中取出左边的第一个字，用&连接上\"先生\"或者\"女士\"称呼。

图55

图56

57、LEN函数：此函数用来查找文本的长度。该函数的用法见图57所示。

图57

58、LOW函数：此函数用来将文本转换为小写。该函数的用法见图58所示。

图58

59、MID函数：此函数能够返回文本字符串中从指定位置开始的特定字符。该数目由用户指定。例如： 1〕如图59所示：从身份证号码中提取生日：在网上注册一些表格时经常需要填写身份证号码，填写完毕系统就会自动地生成出生日期，那个地点以某公司职员为例，依照其身份证号码提取出生年月日。第一在工作表中输入职员的姓名和身份证号码等数据信息，如图59所示，然后在单元格D3中输入以下公式：〝=MID(C3,7,8)〞，在该公式中，利用MID函数返回身份证号码中从第7位字符开始的共8个字符，即该职员的出生日期，众所周知，身份证前6位代表的是省份、市、县编号，然后从第7位开始是出生年月日，共8位，后面的数字代表其他的意义；2〕拆分 号码：工作表中输入的 号码，如图60所示，然后在

单元格C3中输入以下公式：〝=MID(B3,5,7)〞，现在即可获得 。

60、PROPER函数：此函数能够自动转换大小写。第一在工作表中输入一些字母或者英文句子，如图61所

示，然后在单元格C3中输入以下公式：〝=PROPER(B3)〞。

图61

61、REPLACE函数：此函数能够使用其他的文本字符串并依照所指定的字符数替换某个文本字符串中的部分。例如某市的 号码要升位，在原先的 号码的前面加一个〝8〞，下面使用REPLACE函数完成 号码的升位。具体的操作步骤如下：1〕输入的 号码，如图62所示；2〕运算升位后的 号码，在单元格C3中输入以下公式：〝=REPLACE(B3,1,4,\"05328\")〞，在该公式中，使用REPLACE函数用〝0108〞替换B3中字符串中第一位开始的前4位数字，结果相当于区号不变，在原 号码的前面加一个〝8〞。其

中〝05328〞加引号是以文本的形式输入的，否那么忽略0。

图62

62、REPT函数：此函数能够按照给写的次数重复显示文本，也能够通过REPT函数不断地重复显示某一个

文本字符串来对单元格进行填充。该函数的用法见图63所示。

图63

63、RIGHT函数：使用此函数能够依照所指定的字符数返回文本字符串中最后一个或者多个字符。例如：1〕拆分姓名，在实际中人的姓名一样是由姓和名两部分组成的，下面介绍如何利用RIGHT函数将其拆分开，具体的操作步骤如下：在单元格中输入一些姓名，如图所示，然后在单元格C3中输入以下公式：〝=RIGHT(B3,2)〞；2〕判定性别：假设有一个关于生活消费方面的调查，调查者为了书写方便也为了便于进行统计分析，在对被调查者编号时指定其最后一位表示性别，用〝1〞代表男性，用〝2〞代表女性，第一在工作表中输入信息，如图65所示，然后在单元格D3中输入以下公式：〝=IF(RIGHT(C3,1)=\"1\男\女\")〞，在该公式中，使用RIGHT函数返回编号中的最后一个字符，再利用IF函数判定。假如返回的结果为〝1〞那么为〝男〞，反之为〝女〞，由于函数返回的是字符，因此〝1〞要加引号，当有多种情

形时还能够使用嵌套的IF函数。

图

图65

、SEARCH函数：此函数能够查找文本字符串。该函数的用法见图66所示。

图66

65、T函数：此函数能够返加引用的文本。该函数的用法见图67所示。

图67

66、TEXT函数：此函数用来将数值转换为指定格式。该函数的用法见图68所示。

图68

67、TRIM函数：此函数用来清除文本中的空格。该函数的用法见图69所示。

图69

68、UPPER函数：此函数用来将文本转换为大写。该函数的用法见图70所示。

图70

69、处理人员信息：文本函数在实际工作中也是一种常用的函数类型。一些大型的企业为了提高职员的素养，使职员能及时地接触到该行业的最新科技信息，有关负责人会经常请一些专家对自己的职员进行培训。下面介绍如何利用文本函数处理人员信息，具体的操作步骤如下：1〕在工作表中输入需要的标题项目以及人员编号、姓名和性别等数据信息，以便于在后面使用，如图71所示；2〕从姓名中提取姓：在单元格E3中输入以下公式：〝=IF(LEN(C3)=4,LEFT(C3,2),LEFT(C3,1))〞，由于中国人的姓名有两个字的，有3个字的，还有4个字符，4个字的名字一样是复姓，因此要使用IF函数判定姓名的长度是不是4，假如姓名的长度等于4，那么使用LEFT函数返回左边的两个字符，否那么返回左边的1个字符；3〕从姓名中提取名：在单元格E3中输入以下公式：〝=IF(LEN(C3)=2,RIGHT(C3,1),RIGHT(C3,2))〞，在该公式中使用IF函数判定姓名的长度是不是等于2，假设等于2那么利用RIGHT函数返回最右侧的1个字符，假设不等

于2那么返回最右侧的两个字符；4〕添加称呼：在单元格G3中输入以下公式：〝=IF(D3=\"男\先生\"),CONCATENATE(E3,\"女士\"))〞，在该公式中，第一使用IF函数判定性别是〝男〞依旧〝女〞，假如是〝男〞那么返回先生，假如是〝女〞那么返回女士，然后利用CONCATENATE函数将判定结果和姓连接起来组成该专家的称呼；5〕安排入住的宾馆房间号：在单元格H3中输入以下公式：〝=IF(B3<=3,\"滨海假日\"&TEXT(B3,\"300\"),\"清泉宾馆\"&TEXT(B3,\"200\"))〞，在安排专家的宾馆房间时，假设前三名专家在宾馆A中休息，其余的在宾馆B中休息，房间号为他们的编号，在该公式中先使用TEXT函数将B列中的数据转换为对应格式的文本，再使用符号〝&〞将宾馆和房间号连接起来，最后使用IF函数依照专家的编号判定其入住哪个宾馆；6〕输入各个专家的培训人数，然后选中单元格K2，选择[插入]—[符号]菜单位项弹出[符号]对话框，切换到[符号]选项卡中，在[字体]下拉列表中选择[〔一般文本〕]选项，在[子集]下拉列表中选择[零杂丁贝符〔示意符号〕]选项，设置完毕单击[插入]按钮即可在单元格输入选定的符号；7〕绘制人数比较图：在单元格G3中输入以下公式：〝=REPT($K$2,INT(I3/12))〞，在该公式中，使用REPT函数将单元格K2中的方块元素复制〝INT(I3/12)〞次，为了缩小空间也为了减小培

训人数比例，将I列中的培训人数除以12再取整数即可得到需要复制的次数。

图71

70、拆分工资金额：在前面差不多介绍过利用INT函数和MOD函数进行工资数额的拆分，下面介绍如何使用文本函数将工资数额按其位数分隔开。例如某公司部分职员的工资，现要将工资按位数分开，具体的操作步骤如下：1〕在工作表中输入姓名和工资数额以及其他的标题项目，如图72所示；2〕运算千位上的数字：在单元格D4中输入以下公式：〝=IF(LEN(C4)=4,LEFT(C4,1),0)〞,在该公式中使用LEN函数得到C4中字符串的长度，再使用IF函数判定该字符串的长度是否等于4，假如是的话那么利用LEFT函数返回

第一个字符，否那么返回0；3〕运算百位上的数字:在单元格E4中输入以下公式：

〝=IF(D4=0,IF(LEN(C4)=3,LEFT(C4,1),0),LEFT(C4-D4*1000,1))〞,在该公式中，第一使用IF函数判定单元格D4中的值是否等于0，假如等于0那么说明单元格C4中的数字共3位，将使用LEFT函数返回第一个字符；假如不等于0那么返回〝C4-D4*1000〞所得结果的第一个字符；4〕运算十位上的数字：在单元格F4中输入以下公式：〝=LEFT(C4-D4*1000-E4*100,1)〞运算结果的第一个字符。由于工资最少是〝988〞，即3位数字，因此不必再判定是否有两位数的情形；5〕运算个位上的数字：在单元格G4中输

入以下公式：〝=LEFT(C4-D4*1000-E4*100-F4*10,1)〞运算结果的第一个字符。

图72

71、CELL函数：使用此函数能够返回某一个引用区域的左上角单元格的格式、位置或者内容等信息。该函

数的用法见图73所示。

图73

72、COUNTBLANK函数：此函数能够指定空白单元格的个数。该函数的用法见图74所示。

图74

73、ISBLANK函数：此函数能够判定单元格是否为空。例如判定职员是否到岗：1〕输入姓名和上班时刻，

如图75所示；2〕判定其是否到岗，在单元格E3中输入以下公式：〝=IF(ISBLANK(D3),\"请假\到岗\")〞。

图75

74、ISERR函数：此函数能够判定数值是否为任意错误值。例如：运算应收账款：1〕输入的数据信息，

如物资名称、数量、单价和金额等，如图76所示；2〕在单元格E3中输入以下公式：〝=IF(ISERR(C3*D3),\"

确定价格后再做处理\〞。

图76

75、DAVERAGE函数：此函数能够返回列表或者数据库中满足指定条件的列中数值的平均值。例如：1〕在

单元格中输入需要处理的问题，如运算〝语文大于59分的平均成绩〞和〝英语的平均成绩〞，如图77所示；2〕在单元格C12中输入以下公式：〝=DAVERAGE(B2:E8,C10,C10:C11)〞；3〕在单元格C13中输入以

下公式：〝=DAVERAGE(B2:E8,4,E2:E8)〞。

图77

76、DCOUNT函数：使用此函数能够返回数据库或者列表中满足指定条件同时包含数字的单元格个数。具

体的操作步骤如下：1〕如图78所示，第一在单元格中输入需要处理的问题，然后在单元格C12中输入以下公式：〝=DCOUNT(B2:E8,B10,B10:B11)〞，即可得到数学成绩及格的单元格个数；2〕在单元格C13中输入以下公式：〝=DCOUNT(B2:E8,2,B10:B11)〞，即可得到语文成绩大于70同时数学成绩及格的单

元格个数。

图78

77、DGET函数：使用此函数能够从列表或者数据库的列中提取符合指定条件的单个值。如图79所示，在

单元格C12中输入以下公式：〝=DGET(B2:E8,1,D10:D11)〞，即可查找出英语成绩大于分的同学的姓名；在单元格C13中输入以下公式：〝=DGET(B2:E8,1,B10:C11)〞，即可查找出语文和数学成绩全部大于

80分的同学的姓名。

图79

78、DMAX函数：此函数用以返回指定条件的最大数值。第一在单元格中输入需要处理的问题，如图80所

示，然后分别在单元格C12和C13中输入以下公式：〝=DMAX(B2:E8,B10,B10:D11)〞、

〝=DMAX(B2:E8,D10,B10:D11)〞。DMIN函数的使用方法与DMAX函数相似，只是此函数用以返回指

定条件的最小数值。

图80

79、DSUM函数：此函数用以返回指定条件的数字之和。第一在单元格中输入需要处理的问题，如图81所

示，然后在单元格C12和C13中输入以下公式：〝=DSUM(B1:F6,1,C8:C9)〞、〝=DSUM(B1:F6,5,C8:D9)〞。

图81

80、处理采购数据：在EXCEL中提供有专门多种数据库函数，能够满足采购治理中治理人员对大量数据处

理的要求。下面以图82所示，介绍如何使用数据库函数处理采购数据：1〕使用DAVERAGE函数运算采购

数量的平均值，第一建立一个数据模型；2〕在单元格E15中输入以下公式：

〝=DAVERAGE(B2:F12,5,G15:G16)〞，即可得到台灯的平均采购数量；3〕将单元格G16中的〝台灯〞改为〝瓷瓶〞就能够运算出瓷瓶的平均采购数量。此外还能够利用DAVERAGE函数在相交或者相并两种条件下运算数据的平均值。在Excel中输入同行的条件为相交的条件，即必须全部满足的条件，然后在单元格E19中输入以下公式：〝=DAVERAGE(B2:F12,5,C21:D22)〞，即可运算出采购数量少于20的平均数；4〕要运算购买台灯的数量大于10的次数，现在能够使用DCOUNT函数来求解，第一输入需要求解的条件，然后在单元格F26中输入以下公式：〝=DCOUNT(B2:F12,5,C28:D29)〞；5〕要运算新新家具公司沙发的价格，现

在能够使用DGET函数来求解，第一输入需要求解的条件，然后在单元格E33中输入以下公式：〝=DGET(B2:E12,4,C35:D36)〞；6〕要运算家具中沙发的最高价格，现在能够使用DMAX函数来求解，第

一输入需要求解的条件，然后在单元格E39中输入以下公式：〝=DMAX(B2:E12,4,C41:C42)〞。

图82

81、区分函数COUNT和COUNTA：例如：1〕制作1月出勤加班统计表，表中包括职员1月出勤加班统计表以及需要统计的内容，如图83所示；2〕使用COUNT函数统计各列单元格的个数，在单元格B13中输入以下公式：〝=COUNT(B3:B11)〞，现在能够看到包含文字的单元格和空白单元格被忽略了，只统计包含数字

的单元格；3〕使用COUNTA函数统计各列单元格的个数，在单元格B14中输入以下公式：

〝=COUNTA(B3:B11)〞，现在能够看到包含文字的单元格也统计在内了。

图83

82、LARGE函数：该函数用来返回数据集中第K个最大值。例如：依照某企业在一次订货后检验所订产品的合格情形，运算需求量的大小、次品的多少以及不合格率最高、最低的产品等。具体的操作步骤如下：1〕制作检验订购的产品合格情形表，如图84所示；2〕运算各个产品中订购总量的最大需求与最小需求，分别在单元格G2和G3中输入以下公式：〝=LARGE(B3:B12,1)〞、〝=SMALL(B3:B12,1)〞；3〕运算次品

的最大值和最小值，分别在单元格G6和G6中输入以下公式：〝=LARGE(C3:C12,1)〞、〝=LARGE(C3:C12,10)〞；4〕查找出不合格率最高的产品，在单元格G10中输入以下公式：〝=INDEX(A3:A12,MATCH(LARGE(D3:D12,1),D3:D12,0),1)〞,在该公式中第一利用LARGE函数得出不合格率的最大值，然后利用MATCH函数得到该最大值在数据区域〝D3：D12〞中的行号，最后使用INDEX函数在数据区域〝A3：A12〞中查找该行与第1列交叉处的单元格的值，即可得到不合格率最高的产品即〝显

示器〞；查找不合格率最低的产品同理。

图84

83、区分函数MAX和MAXA：现以某公司1月份的出勤和加班记录表为例，来看一下MAX和MAXA函数在实

际工作中的具体的应用。在此表中分别运算迟到、旷工和加班次数最多的职员的姓名，具体的操作步骤如下：1〕制作如图85所示的表格；2〕运算迟到次数最多的职员的姓名，在单元格J5中输入以下公式：〝=INDEX(C3:C11,MATCH(MAX(E3:E11),E3:E11,0),1)〞,即可得到迟到次数最多的职员的姓名即〝李

宁〞；3〕运算旷工次数最多的职员的姓名，在单元格J7中输入以下公式：

〝=INDEX(C3:C11,MATCH(MAXA(F3:F11),F3:F11,0),1)〞，即可得到旷工次数最多的职员的姓名即〝邵

刚〞。

图85

84、MODE函数：该函数用以返回显现频率最高的数值。例如：假设某些同学的语文、数学和英语成绩如图86所示，现运算各科成绩中显现次数最多的分数，在单元格C14中输入以下公式：〝=MODE(C4:C13)〞。

图86

85、PERCENTRANK函数：该函数用以返回百分比排位。该函数的用法见图87所示。

图87

86、RANK函数：该函数用以排名次。该函数的用法见图所示。

87、TRIMMEAN函数：该函数用以运算内部平均值。例如：依照实际运算需求在工作表中添加相应的项目，制作如图88所示的表格，在单元格C15中输入以下公式：〝=TRIMMEAN(C3:C14,0.05)〞，即可得到除去

极值比例为5%的均值结果。

图88

88、统计职员培训成绩：一样来说公司聘请新职员后会对其进行培训，随后人事部会对他们的培训成绩进行统计排名，以此考核新职员的素养状况，具体的操作步骤如下：1〕第一在工作表Sheet1中输入相应的数据信息，然后对输入的内容进行字体、边框等属性的设置，如图所示；2〕运算各位职员的平均成绩：在单元格J4中输入以下公式：〝=AVERAGE(E4:I4)〞；3〕运算总成绩：在单元格K4中输入以下公式：〝=SUM(E4:I4)〞；4〕运算职员的成绩名次：在单元格L4中输入以下公式：〝=RANK(J4,$J$4:$J$21)〞；

5〕运算培训人数：在单元格D23中输入以下公式：〝=COUNT(B4:B21)〞；6〕运算实际参加考试的人数：在单元格D24中输入以下公式：〝=COUNTA(E4:E21)〞；7〕运算无成绩的人数：即缺考人数，在单元格D25中输入以下公式：〝=COUNTBLANK(E4:E21)〞；8〕运算参加培训的男、女职员人数：分别在单元格D26和D27中输入以下公式：〝=COUNTIF(D4:D21,\"女\")〞、〝=COUNTIF(D4:D21,\"男\")〞；9〕运算男、女职员总成绩：分别在单元格D29和D30中输入以下公式：〝=SUMIF(D4:D21,\"男\〞、〝=SUMIF(D4:D21,\"

女\〞；10〕运算平均最高和最低分：分别在单元格D31和D32中输入以下公式：〝=MAXA(J4:J21)〞、〝=MIN(J4:J21)〞；11〕运算倒数第2名：在单元格D33输入以下公式：〝=SMALL(J4:J21,2)〞；12〕运算各个分数段的人数：选中单元格区域〝G29：G33〞，然后输入以下公式：〝{=FREQUENCY(J4:J21,F24:G27)}〞，在该公式中使用FREQUENCY函数得到各个分数段的人数；13〕运算

各个分数段的人数占总人数的比例：在单元格H29输入以下公式：〝=G29/$D$24〞。

图

、CUMPRINC函数：此函数用来运算贷款本金和利息。例如一位消费者获得一项30年的住房贷款，金额为400000元，按月还款，年息是5%，请运算贷款的第五年应该支付的本金和利息。具体的操作步骤如下：1〕制作如图90所示的表格；2〕运算按月还款时第五年内每月月末还款的本金的累计和，在单元格B6中输入以下公式〝=CUMPRINC(D3/12,C3*12,B3,49,60,0)〞，在该公式中〝D3/12〞表示月利率，〝C3*12〞表示总的付款期数，〝49〞表示第五年的第一个月，〝60〞表示第五年的最后一

个月，〝0〞表示付款时刻类型是月末；2〕运算第五年内每月月末所付利累的累计和，在单元格C6中输入以下公式：〝=CUMIPMT(D3/12,C3*12,B3,49,60,0)〞，注意那个公式与上面唯独不同的地点是所用的函数是不同的；3〕运算本利之和，在单元格D6中输入以下公式：〝=SUM(B6:C6)〞；4〕运算按月付款时第五年内共付多少，在单元格E6中输入以下公式：〝=PMT(D3/12,C3*12,B3,0,0)*12〞，在该公式中，〝PMT(D3/12,C3*12,B3,0,0)〞表示使用PMT函数运算每月月末还款数，其中〝D3/12〞

表示月利率，〝C3*12〞表示总的付款期数，所得结果再乘以12表示第五年内总的还款数。

图90

90、DOLLARDE函数：此函数用来将分数形式转换为小数形式。该函数的用法如图91所示。

图91

91、DOLLARFR函数：此函数用来将小数形式转换为分数形式。该函数的用法如图92所示。

图92

92、FV函数：此函数用来运算投资以后值。1〕运算本利和：例如某公司为某项投资存款，银行已有的存款是50000元，以后每年存款20000元，年利率是8%，试运算10年后的本息和为多少？假如每月存入2000元，那么10年后的本利和又是多少？具体的操作步骤如下：制作如图93所示的表格，运算按存款时10年后的本金和利息之和，在单元格D5中输入以下公式：〝=FV(C3,D3,E3,B3,0)〞；运算按月存款时10年后的本金和利息之和，在单元格D6中输入以下公式：〝=FV(C3/12,D3*12,F3, B3,0)〞，在该公式中〝C3/12〞表示月利率，〝D3*12〞表示总的月份数，每月的存款和先期的存款差不多上负值，如此运算出来的结果确实是正值，〝0〞表示每次月末存款；2〕运算累计金额：例如

假如年息为6%，那么5年之后2000元的累计金额是多少？具体的操作步骤如下：将数据和相关项目输入到工作表中建立数据模型，如图94所示，在单元格B6中输入以下公式：〝=FV(B3,B4,0,B5,0)〞；3〕运算到期还款额：例如用户向银行借款5000元，期限6年，利息6%，请问到期还款额为多少？具体的操作步骤如下：将数据和相关项目输入到工作表中建立数据模型，如图95所示，在单元格B6中输入以下公式：〝=FV(B3,B4,0,B5,0)〞；4〕运算帐户总额：如何期初余额为5000元，每月的月末存入600元，假如月息为0.75%，请问三年后此帐户中的总额是多少钱？具体的操作步骤如下：将数据和相关项目输入到工作表中建立数据模型，如图96所示，在单元格B7中输入以下公式：

〝=FV(B3,B4,B5,B6,0)〞，即可求出三年后的账户总额。

93、FVSCHEDULE函数：此函数用来运算本金以后值。例如某人存入银行150000元，一年内年利率在不断地变化，请运算一年后的存款额。具体的操作步骤如下：1〕制作如图97所示的表格，其中包括一年内不同的年利率以及由此得出的月利率；2〕运算在各种利率条件下一年后的总存款数，在单元格D15中输入以下公式：〝=FVSCHEDULE(C2,F3:F14)〞，即运算不同的利率条件下150000元的存款

1年后的存款额是多少。

图97

94、IPMT函数：此函数用来返回利息偿还额。例如运算贷款利息，假设有一位消费者为买房而向银行贷款200000元，贷款期限为10年，年息为4.5%，按月偿还，请运算付款中的利息，具体的操作步骤如下：1〕制作如图98所示的表格，其中包括贷款金额、贷款时刻和年利息；2〕运算第一个月应对的利息：在单元格D5中输入以下公式：〝=IPMT(D3/12,1,C3*12,B3)〞；3〕运算最后一个月应

对的利息：在单元格D6中输入以下公式：〝=IPMT(D3/12,120,C3*12,B3)〞。

图98

95、NPER函数：该函数用来返回投资总期数。该函数的用法如图99所示。

图99

96、PMT函数：该函数用来返回每期付款额。1〕运算偿还额：某公司从银行贷款200000元，分6年偿还，年利率为8%，现需运算按年偿还和按月偿还的还款额，条件为等额偿还，具体的操作步骤如下：制作如图100所示的表格，运算按年还款时的年初偿还额，在单元格E4中输入以下公式：〝=PMT(C7,C5,C3,0,1)〞；运算年末偿还额，在单元格F4中输入以下公式：〝=PMT(C7,C5,C3)〞；运算按月偿还时的月初偿还额，在单元格E8中输入以下公式：〝=PMT(C7/12,C5*12,C3,0,1)〞，在该公式中〝C7/12〞表示月利率，〝C5*12〞表示还款总时刻，因为是按月运算，因此是6*12=72个月，

〝1〞仍表示是月初还款；运算按月还款时的月末偿还额，在单元格F8中输入以下公式：〝=PMT(C7/12,C5*12,C3)〞；2〕运算存款金额：例如假如某公司需要为某个项目预备资金，该项目在两年后估量需要100000元，假设银行的存款年利率为10%，那么从现在起公司每月或者每年应当存入的金额是多少？具体的操作步骤如下：制作如图101所示的表格，运算按年存款时的年初存款额，在单元格E4中输入以下公式：〝=PMT(C7,C5,0,C3,1)〞；运算按年存款时的年末存款额，在单元格F4中输入以下公式：〝=PMT(C7,C5,0,C3)〞；运算按月存款时的月初存款额，在单元格E8中输入以下公式：〝=PMT(C7/12,C5*12,0,C3,1)〞，在该公式中使用PMT函数运算按月存款时的月初存款额，公式中各项参数的意义依次为：〝C7/12〞为月利率，〝C5*12〞为存款总期数，〝0〞为本金，〝100000〞为以后值，〝1〞表示存款方式是期初；运算按月末存款额，在单元格F8中输入以下公式：〝=PMT(C7/12,C5*12,0,C3)〞；3〕运算利润租金：假设一房产的租赁利益当前能够以230000元出售，租期4年，预付每月的租金6000元，不得重新定价或者涨价，假如同意了0.75%的收益，请问能够得到多少利润租金？具体的操作步骤如下：制作如图102所示的表格，运算利润租金，在单元格B7

中输入以下公式：〝=PMT(B6,B4*12,-230000,0,1)+B5〞；4〕运算贷款偿还额：假设有一笔期限为15年，月利息为0.65%的30000元的贷款，请运算月偿还额为多少？具体的操作步骤如下：制作如图103所示的表格，运算月偿还额，在单元格B6中输入以下公式：〝=PMT(B4,B5*12,-B3,0,0)〞。

图100

图101

图102

图103

97、PPMT函数：该函数用来返回本金偿还额。例如运算偿还的本息，某公司从银行贷款500000元，年利率为5%，还款期为10年，条件是等额偿还，按年或者按月还款，请运算付款中的本金和利息。具体的操作步骤如下：1〕制作如图104所示的表格；2〕运算按年还款时的年初和年末应对本金：分

别在单元格D6、D7、D8和D9中输入以下公式：〝=PPMT(E3,1,D3,C3,0,1)〞、

〝=PPMT(E3,1,D3,C3,0)〞、〝=PPMT(E3,10,D3,C3,0,1)〞、〝=PPMT(E3,10,D3,C3,0,0)〞，这4个公式分别是用来运算第一年的期初、期末和第十年的期初及期末的应对本金的。公式的形式差不多相同，只是函数PPMT中的参数per和type发生了改变，分别表示哪一年以及是期初依旧期末的应对本金；3〕运算按月还款时的年初和年末应对本金：分别在单元格D10、D11、D12和D13中输入以下公

式：〝=PPMT(E3/12,1,D3*12,C3,0,1)〞、〝=PPMT(E3/12,1,D3*12,C3,0)〞、

〝=PPMT(E3/12,120,D3*12,C3,0,1)〞、〝=PPMT(E3/12,120,D3*12,C3,0)〞，在这4个公式中〝E3/12〞表示月利率，〝D3*12〞表示还款总月份数，〝120〞表示还款总期数中的最后一个月，由因此还款，因此以后值差不多上0；4〕运算按年还款时的年初和年末应对利息：分别在单元格E6、

E7、E8和E9中输入以下公式：〝=IPMT(E3,1,D3,C3,0,1)〞、〝=IPMT(E3,1,D3,C3,0)〞、〝=IPMT(E3,10,D3,C3,0,1)〞、〝=IPMT(E3,10,D3,C3,0)〞，与运算本金不同的地点确实是所用的函数不一样，其参数的意义差不多上一样的；5〕运算按月还款时的年初和年末应对利息：分别在单元

格E10、E11、E12和E13中输入以下公式：〝=IPMT(E3/12,1,D3*12,C3,0,1)〞、〝=IPMT(E3/12,1,D3*12,C3,0)〞、〝=IPMT(E3/12,120,D3*12,C3,0,1)〞、

〝=IPMT(E3/12,120,D3*12,C3,0)〞；6〕运算各种情形下的本金和利息之和：在单元格F6中输入以下公式：〝=SUM(D6:E6)〞。从上面的运算结果中能够看出：每年的年初所付款差不多上一样的，尽管第一年和最后一年的本金和利息的值不同，但总和是相等的；同样每年年末的本金和利息之和也是

相等的，每月月初以及每月月末的本金和利息之和也是相等的。

图104

98、PV函数：该函数用来返回投资现值。例如：1〕运算贷款额：某公司想贷款进行投资，其能承担的能力为每月支付10000元，以年利息5%进行10年贷款，请运算该公司能承担的最多贷款额是多少？

具体的操作步骤如下：制作如图105所示的表格，在单元格E3中输入以下公式：

〝=PV(C3/12,D3*12,-B3,0,0)〞，即按月运算付款数；2〕运算投资值：现有一项保险年金，购买该保险后能够在今后25年内每月末领回1000元，假设购买时需先缴付15000元，投资回报率为4.5%，请运算这笔投资是否值得。具体的操作步骤如下：制作如图106所示的表格，在单元格E3中输入以下公式：〝=PV(C3/12,D3*12,-B3,0,0)〞，即可得到需要投资的现值。从上面的运算的结果中能够看出，运算出来的投资现值〝179910.32〞大于预交付的购买保险的费用〝150000〞，因此购买此保险

依旧值得的。

图105

图106

99、RATE函数：该函数返回年金的各期利率。该函数的用法如图107所示。

图107