excel公式应用大全.docx
《excel公式应用大全.docx》由会员分享，可在线阅读，更多相关《excel公式应用大全.docx（42页珍藏版）》请在冰豆网上搜索。
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.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,D37）”。
图15
20、计算库存量和奖金：
假设某公司在月底要根据员工的业绩发放工资并进行产品的库存统计，本例中规定员工的基本工资为600元，奖金按照销售业绩的8%提成，总工资等于基本工资与奖金之和。
如图16，1）在工作表中输入相应的数据信息；2）计算“现存库量”：
在单元格C15中输入以下公式：
“=C14-SUM（C3:
C9）”；3）计算“销售业绩”：
在单元格G3中输入以下公式：
“=SUMPRODUCT（C3:
F3,$C$13F$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$11G$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
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$3G$4,2）”；3）分别在单元格D8、D9、D10中输入以下公式：
“=HLOOKUP（E3,$B$3G$4,2）”、“=HLOOKUP（F3,$B$3G$4,2）”、“=HLOOKUP（G3,$B$3G$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,
升级会员 