EXCEL2003公式·函数应用大全(1)_太平洋信风

EXCEL2003公式·函数应用大全(1)

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.14159265358979，即数学常量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
5
Min
9
Sum

2
Count
6
Product
10
Var

3
Counta
7
Stdev
11
warp

4
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*4489.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为确定返回值类型的数字，详细内容如下表：
参数值
函数返回值

1或者省略
返回数字1（星期日）到数字7（星期六）之间的数字。

2
返回数字1（星期一）到数字7（星期日）之间的数字。

3
返回数字0（星期一）到数字6（星期日）之间的数字。

例如：计算当前日期是星期几：如图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,"入职不够一年",IF(DATE(C$2,MONTH($D5),DAY($D5))>TODAY(),"今年没到期",IF(DATEDIF($D5,TODAY(),"y")<6,7,10)))”，以此可以计算出员工的休假天数；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