企业管理套表E表格函数应用大全.docx
《企业管理套表E表格函数应用大全.docx》由会员分享，可在线阅读，更多相关《企业管理套表E表格函数应用大全.docx（20页珍藏版）》请在冰豆网上搜索。
企业管理套表E表格函数应用大全
（企业管理套表）E表格函数应用大全
EXCEL2003公式·函数应用大全
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元，前10km每壹km跳表壹次，以后每半km就跳表壹次，每跳壹次表要加收2元。
输入不同的km数，如图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为确定返回值类型的数字，详细内容如下表：
例如：
计算当前日期是星期几：
如图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
30、AND函数：
当所有参数的逻辑值为真时，AND函数的返回值为TRUE；只要有壹个参数的逻辑值为假，该函数的返回值则为FALSE。
例如：
假设有壹组民意调查数据或者调查结果，如图26所示，下面根据各个年龄段（18～34、35～49、50～64和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<=64）,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
升级会员 