搜档网
当前位置:搜档网 › Excel表格函数应用大全

Excel表格函数应用大全

Excel表格函数应用大全
Excel表格函数应用大全

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元,前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函数:使用该函数可以返回列表或者数据库中的分类汇总。通常利用[数据]—[分类

例如某班部分同学的考试成绩如图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所示,然后利用DA YS360函数来计算其借款的时间,并且计算出还款利息。1)计算“借款天数”:在单元格D3中输入以下公式:“=DA YS360(B3,C3)”;2)计算“还款利息”:在单元格G3中输

入以下公式:“=D3*E3*F”。

图20

25、WEEKDAY函数:使用此函数可以返回某个日期为星期几。语法:W EEKDA Y

(serial_number,return_type):其中参数serial_number代表要查找的那一天的日期,参数return_type为确

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

例如:计算当前日期是星期几:如图21所示,在单元格B3中输入计算当前日期的公式:“=W EEKDA Y(B3,2)

图21

26、WEEKN UM函数:使用此函数可以计算一年中的第几周。例如:已知2006年6月9日是星期五,下面用WEEKNUM函数计算在参数不同的情况下返回的周数。如图22所示,在单元格B3中输入计算当前日期的

式:“=WEEKNUM(B3,C3)”。

图22

27、WORKDAY函数:使用此函数可以返回某个日期(起始日期)之前或之后相隔指定工作日的某一日期的期值,工作日不包括周末和专门指定的日期。假设某出版社要求某个编辑从2006年3月1日起开始写稿,利80天将其完成(其中不包括三天节假日),此时可以利用WORKDA Y函数计算出完成日期。如图23所示,单元格中输入上述信息,然后在单元格C7中输入以下公式:“=WORKDA Y(C2,C3,C4:C6)”。

图23

28、计算年假天数和工龄补贴:假设某公司规定,员工任职满1年的开始有年假,第1至5年每年7天,第年开始每年10天。截止到2005年6月9日,以工龄计算每年补贴100元,任职不足一年的按每人50元计算如图24所示:1)首先在工作表中输入已知数据信息,然后根据公司规定的内容在单元格F5中输入以下公式“=IF(DATEDIF($D5,TODA Y(),"y")<1,"入职不够一年",IF(DATE(C$2,MONTH($D5),DA Y($D5))>TODA Y(),"年没到期",IF(DATEDIF($D5,TODA Y(),"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(Y AR(D4),MONTH(D4),DA Y(D4))-DATE(YEA R(C4),MONTH(C4),DA Y(C4))-1,DATE(YEA R(D4),MONTH(D4),D Y(D4))-DATE(YEA R(C4),MONTH(C4),DA Y(C4)))”,此时可计算出所有型号的包裹寄存的天数,在此公式中

到了IF函数,函数中的条件为

“TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MI NUTE(D4),SECOND(D4)”,它是用来断取走时间是否超过了寄存时间,如果条件为真则表示还没有超过一天,那么寄存的天数就是“DATE(YEA R(D4),MONTH(D4),DA Y(D4))-DATE(YEA R(C4),MONTH(C4),DA Y(C4))-1”,即走取的日期减寄存的日期再减1,如果时间超过了,那么寄存的天数就是

“DATE(YEA R(D4),MONTH(D4),DA Y(D4))-DATE(YEA R(C4),MONTH(C4),DA Y(C4))”,即取走的日期与寄时的日期之差;2)计算寄存小时数:在单元格F4中输入以下公式:

“=IF(TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINUTE(D4),SECOND(D4)),HOUR(1 ME(HOUR(C4),MINUTE(C4),SECOND(C4))+TIME(HOUR(D4),MINUTE(D4),SECOND(D4))),HOUR(TIME(HO R(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 -TIME(HOUR(C4),MINUTE(C4),SECOND(C4))+TIME(HOUR(D4),MINUTE(D4),SECOND(D4))),MINUTE(TIM 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”,即判断是否有1年出生的人,然后在单元格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)”;

计算奖金:在单元格E7中输入以下公式:“=C7*D7”。

图33

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

图34

39、INDEX函数:该函数返回指定单元格中的内容。假设在图35所示的课程表中:1)查找出星期三第节课所上的课程:只需在单元格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、OFFS ET函数:OFFSET函数的功能是返回的引用可以为一个单元格或者单元格区域,并且可以指定返的行数或者列数。其语法为:OFFSET(reference,rows,cols,height,width)。其中reference表示作为偏移量参照系引用区域,此参数必须为单元格或相邻单元格区域的引用,否则函数OFFSET返回错误值“#VA LUE!”;ro 表示相对于偏移量参照系的左上角单元格上(下)偏移的行数;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%至4的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 ,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所示。

EXCEL函数表(最全的函数大全)

函数大全一、数据库函数(13条) 二、日期与时间函数(20条) 三、外部函数(2条) 四、工程函数(39条) 五、财务函数(52条)

六、信息函数(9条) 七、逻辑运算符(6条) 八、查找和引用函数(17条) 九、数学和三角函数(60条)

十、统计函数(80条)

十一、文本和数据函数(28条)

一、数据库函数(13条) 1.DAVERAGE 【用途】返回数据库或数据清单中满足指定条件的列中数值的平均值。 【语法】DAVERAGE(database,field,criteria) 【参数】Database构成列表或数据库的单元格区域。Field指定函数所使用的数据列。Criteria为一组包含给定条件的单元格区域。 2.DCOUNT 【用途】返回数据库或数据清单的指定字段中,满足给定条件并且包含数字的单元格数目。 【语法】DCOUNT(database,field,criteria) 【参数】Database构成列表或数据库的单元格区域。Field指定函数所使用的数据列。Criteria为一组包含给定条件的单元格区域。 3.DCOUNTA 【用途】返回数据库或数据清单指定字段中满足给定条件的非空单元格数目。 【语法】DCOUNTA(database,field,criteria) 【参数】Database构成列表或数据库的单元格区域。Field指定函数所使用的数据列。Criteria为一组包含给定条件的单元格区域。 4.DGET 【用途】从数据清单或数据库中提取符合指定条件的单个值。 【语法】DGET(database,field,criteria) 【参数】Database构成列表或数据库的单元格区域。Field指定函数所使用的数据列。Criteria为一组包含给定条件的单元格区域。 5.DMAX 【用途】返回数据清单或数据库的指定列中,满足给定条件单元格中的最大数值。 【语法】DMAX(database,field,criteria) 【参数】Database构成列表或数据库的单元格区域。Field指定函数所使用的数据列。Criteria为一组包含给定条件的单元格区域。 6.DMIN 【用途】返回数据清单或数据库的指定列中满足给定条件的单元格中的最小数字。 【语法】DMIN(database,field,criteria) 【参数】Database构成列表或数据库的单元格区域。Field指定函数所使用的数据列。Criteria为一组包含给定条件的单元格区域。 7.DPRODUCT 【用途】返回数据清单或数据库的指定列中,满足给定条件单元格中数值乘积。 【语法】DPRODUCT(database,field,criteria) 【参数】同上

Excel中常用函数应用举例

Excel中常用函数应用举例 1.求和函数SUM 求和 SUM(number1,number2,...)。 使用求和函数SUM,操作步骤如下: (1)打开“员工业绩表”工作簿,选择D10单元格,如图所示。 (2)单击“插入函数”按钮,在弹出的“插入函数”对话框中选择SUM函数,单击“确定”按钮,如图所示。

(3)在打开的“函数参数”对话框中,“Number1”文本框中默认引用D3:D9单元格区域,单击“确定”按钮,如图所示。 (4)求出的和值即可显示在D10单元格中,如图所示。

2.平均值函数A VERAGE 平均值函数的原理是将所选单元格区域中的数据相加,然后除以单元格个数,返回作为结果的算术平均值,其语法结构为:A VERAGE(number1,number2,...)。 使用平均值函数A VERAGE,操作步骤如下: (1)打开“员工业绩表”工作簿,选择D11单元格,如图所示。

(2)单击“插入函数”按钮,在弹出的“插入函数”对话框中选择A VERAGE函数,单击“确定”按钮,如图所示。 (3)在打开的“函数参数”对话框中,在“Number1”文本框中输入D3:D9,设定计算平均值的单元格区域,单击“确定”按钮,如图所示。

(4)求出的平均值即显示在D11单元格中,如图所示。 3.条件函数IF 条件函数可以实现真假值的判断,它根据逻辑计算的真假值返回两种结果。该函数的语法结构为:IF(logical_test,value_if_true,value_if_false)。其中,logical_test表示计算结果为true或false的任意值或表达式;value_if_true表示当logical_test为true时返回的值;value_if_false表示当logical_test为false时返回的值。

Excel常用电子表格公式大全【汇总篇】

Excel 常用电子表格公式大全【汇总篇】 篇一:Excel 常用电子表格公式汇总 Excel 常用电子表格公式汇总 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式: =TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的 18 位身份证号的出生年月计算公式: =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式: =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1," 男 "," 女 "),IF(MOD(MID(C2,17,1),2)=1," 男 "," 女 ")) 公式内的“C2”代表的是输入身份证号码的单元格。 5、求和: =SUM(K2:K56)——对 K2 到 K56 这一区域进行求和; 6、平均数: =AVERAGE(K2:K56)——对 K2 K56 这一区域求平均数; 7、排名: =RANK(K2,K$2:K$56)——对 55 名学生的成绩进行排名; 8、等级: =IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 9、 学期总评: =K2*0.3+M2*0.3+N2*0.4 ——假设 K 列、 M 列和 N 列分别存放着学生的“平 时总评”、“期中”、“期末”三项成绩; 10、最高分: =MAX(K2:K56) ——求 K2 到 K56 区域(55 名学生)的最高分; 11、最低分: =MIN(K2:K56) ——求 K2 到 K56 区域(55 名学生)的最低分; 12、分数段人数统计: (1) =COUNTIF(K2:K56,"100") ——求 K2 到 K56 区域 100 分的人数;假设把结果存放于 K57 单元格; (2)=COUNTIF(K2:K56,">=95")-K57 ——求 K2 到 K56 区域 95~99.5 分的人数;假设把结 果存放于 K58 单元格; (3)=COUNTIF(K2:K56,">=90")-SUM(K57:K58)——求 K2 到 K56 区域 90~94.5 分的人数; 假设把结果存放于 K59 单元格; (4) =COUNTIF(K2:K56,">=85")-SUM(K57:K59)——求 K2 到 K56 区域 85~89.5 分的人数; 假设把结果存放于 K60 单元格; (5) =COUNTIF(K2:K56,">=70")-SUM(K57:K60)——求 K2 到 K56 区域 70~84.5 分的人数; 假设把结果存放于 K61 单元格; (6) =COUNTIF(K2:K56,">=60")-SUM(K57:K61)——求 K2 到 K56 区域 60~69.5 分的人数; 假设把结果存放于 K62 单元格; (7) =COUNTIF(K2:K56," 说明:COUNTIF 函数也可计算某一区域男、女生人数。 如:=COUNTIF(C2:C351,"男") ——求 C2 到 C351 区域(共 350 人)男性人数; 1 / 10

excel常用工作表格20个函数应用介绍

1、IF函数条件判断 IF函数是最常用的判断类函数之一,能完成非此即彼的判断。 如下图,考核得分的标准为9分,要判断B列的考核成绩是否合格。 =IF(B4>=9,"合格","不合格") IF,相当于普通话的“如果”,常规用法是: IF(判断的条件,符合条件时的结果,不符合条件时的结果) 2、多条件判断 如下图所示,如果部门为生产、岗位为主操有高温补助。在D列使用公式:=IF(AND(B2="生产",C2="主操"),"有","无") AND函数对两个条件判断,如果同时符合,IF函数返回“有”,否则为无。 3、条件求和

如下图所示,使用SUMIF函数计算一班的总成绩: =SUMIF(D2:D5,F2,C2:C5) SUMIF用法是: =SUMIF(条件区域,指定的求和条件,求和的区域) 用通俗的话描述就是: 如果D2:D5区域的班级等于F2单元格的“一班”,就对C2:C5单元格对应的区域求和。 4、多条件求和 如下图所示,要统计部门为生产,并且岗位为主操的补助总额。 公式为: =SUMIFS(D2:D9,B2:B9,F2,C2:C9,G2) SUMIFS用法是: =SUMIFS(求和的区域,条件区域1,指定的求和条件1,条件区域2,指定的求和条件2,……) 5、条件计数 如下图,要统计指定店铺的业务笔数。也就是统计B列中有多少个指定的店铺名称。 =COUNTIF(B2:B12,E3)

COUNTIF函数统计条件区域中,符合指定条件的单元格个数。常规用法为: =COUNTIF(条件区域,指定条件) 6、多条件计数 要求:统计统计部门为生产,并且岗位为主操的人数 公式为: =COUNTIFS(B2:B9,F2,C2:C9,G2) COUNTIFS函数统计条件区域中,符合多个指定条件的单元格个数。常规用法为:=COUNTIFS(条件区域1,指定条件1,条件区域2,指定条件2……)

EXCEL的函数大全(完整版)

实用EXCE的函数 1.ADDRESS 用途:以文字形式返回对工作簿中某一单元格的引用。 语法:ADDRESS(row_num,column_num,abs_num,a1,sheet_text) 参数:Row_num是单元格引用中使用的行号;Column_num是单元格引用中使用的列 标;Abs_num指明返回的引用类型(1或省略为绝对引用,2绝对行号、相对列标,3相对行号、绝对列标,4是相对引用);A1是一个逻辑值,它用来指明是以A1或R1C1返回引用样式。如果A1为TRUE或省略,函数ADDRESS返回A1样式的引用;如果A1为FALSE,函数ADDRESS 返回R1C1样式的引用。Sheet_text为一文本,指明作为外部引用的工作表的名称,如果省略sheet_text,则不使用任何工作表的名称。 实例:公式“=ADDRESS(1,4,4,1)”返回D1。 2.AREAS 用途:返回引用中包含的区域个数。 语法:AREAS(reference)。 参数:Reference是对某一单元格或单元格区域的引用,也可以引用多个区域。 注意:如果需要将几个引用指定为一个参数,则必须用括号括起来,以免Excel将逗号作为参数间的分隔符。 实例:公式“=AREAS(a2:b4)”返回1,=AREAS((A1:A3,A4:A6,B4:B7,A16:A18))返回4。 3.CHOOSE 用途:可以根据给定的索引值,从多达29个待选参数中选出相应的值或操作。 语法:CHOOSE(index_num,value1,value2,...)。 参数:Index_num是用来指明待选参数序号的值,它必须是1到29之间的数字、或者是包含数字1到29的公式或单元格引用;value1,value2,...为1到29个数值参数,可以是数字、单元格,已定义的名称、公式、函数或文本。 实例:公式“=CHOOSE(2,"电脑","爱好者")返回“爱好者”。公式“=SUM(A1:CHOOSE(3,A10,A20,A30))”与公式“=SUM(A1:A30)”等价(因为CHOOSE(3,A10,A20,A30)返回A30)。 4.COLUMN

Excel常用函数公式大全(实用)

Excel常用函数公式大全 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式: =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式: =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和:=SUM(K2:K56) ——对K2到K56这一区域进行求和; 2、平均数:=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数; 3、排名:=RANK(K2,K$2:K$56) ——对55名学生的成绩进行排名; 4、等级:=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评:=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩; 6、最高分:=MAX(K2:K56) ——求K2到K56区域(55名学生)的最高分; 7、最低分:=MIN(K2:K56) ——求K2到K56区域(55名学生)的最低分; 8、分数段人数统计: (1)=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数;假设把结果存放于K57单元格; (2)=COUNTIF(K2:K56,">=95")-K57 ——求K2到K56区域95~99.5分的人数;假设把结果存放于K58单元格; (3)=COUNTIF(K2:K56,">=90")-SUM(K57:K58) ——求K2到K56区域90~94.5分的人数;假设把结果存放于K59单元格; (4)=COUNTIF(K2:K56,">=85")-SUM(K57:K59) ——求K2到K56区域85~89.5分的人数;假设把结果存放于K60单元格;

常用excel函数公式大全

常用的excel函数公式大全 一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式:C2 =IFERROR(A2/B2,"") 说明:如果是错误值则显示为空,否则正常显示。

2、IF多条件判断返回值 公式:C2 =IF(AND(A2<500,B2="未到期"),"补款","") 说明:两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的内容 公式:B2 =COUNTIF(Sheet15!A:A,A2) 说明:如果返回值大于0说明在另一个表中存在,0则不存在。

2、统计不重复的总人数 公式:C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。 四、求和公式

1、隔列求和 公式:H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)说明:如果标题行没有规则用第2个公式 2、单条件求和 公式:F2 =SUMIF(A:A,E2,C:C) 说明:SUMIF函数的基本用法

3、单条件模糊求和 公式:详见下图 说明:如果需要进行模糊求和,就需要掌握通配符的使用,其中星号是表示任意多个字符,如"*A*"就表示a前和后有任意多个字符,即包含A。

4、多条件模糊求和 公式:C11 =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明:在sumifs中可以使用通配符* 5、多表相同位置求和 公式:b2 =SUM(Sheet1:Sheet19!B2) 说明:在表中间删除或添加表后,公式结果会自动更新。 6、按日期和产品求和

EXCEL常用函数大全

EXCEL常用函数大全(做表不求人!) 2013-12-03 00:00 我们在使用Excel制作表格整理数据的时候,常常要用到它的函数功能来自动统计处理表格中的数据。这里整理了Excel中使用频率最高的函数的功能、使用方法,以及这些函数在实际应用中的实例剖析,并配有详细的介绍。 1、ABS函数 函数名称:ABS 主要功能:求出相应数字的绝对值。 使用格式:ABS(number) 参数说明:number代表需要求绝对值的数值或引用的单元格。 应用举例:如果在B2单元格中输入公式:=ABS(A2),则在A2单元格中无论输入正数(如100)还是负数(如-100),B2中均显示出正数(如100)。 特别提醒:如果number参数不是数值,而是一些字符(如A等),则B2中返回错误值“#VALUE!”。

2、AND函数 函数名称:AND 主要功能:返回逻辑值:如果所有参数值均为逻辑“真(TRUE)”,则返回逻辑“真(TRUE)”,反之返回逻辑“假(FALSE)”。 使用格式:AND(logical1,logical2, ...) 参数说明:Logical1,Logical2,Logical3……:表示待测试的条件值或表达式,最多这30个。 应用举例:在C5单元格输入公式:=AND(A5>=60,B5>=60),确认。如果C5中返回TRUE,说明A5和B5中的数值均大于等于60,如果返回FALSE,说明A5和B5中的数值至少有一个小于60。 国美提醒:如果指定的逻辑条件参数中包含非逻辑值时,则函数返回错误值“#VALUE!”或“#NAME”。 3、AVERAGE函数 函数名称:AVERAGE 主要功能:求出所有参数的算术平均值。

Excel表格中的一些基本函数使用方法

Excel表格中的一些基本函数使用方法 一、输入三个“=”,回车,得到一条双直线; 二、输入三个“~”,回车,得到一条波浪线; 三、输入三个“*”或“-”或“#”,回车,惊喜多多; 在单元格内输入=now()显示日期 在单元格内输入=CHOOSE(WEEKDAY(I3,2),"星期一","星期二","星期三","星期四","星期五","星期六","星期日") 显示星期几 Excel常用函数大全 1、ABS函数 函数名称:ABS 主要功能:求出相应数字的绝对值。 使用格式:ABS(number) 参数说明:number代表需要求绝对值的数值或引用的单元格。 应用举例:如果在B2单元格中输入公式:=ABS(A2),则在A2单元格中无论输入正数(如100)还是负数(如-100),B2中均显示出正数(如100)。 特别提醒:如果number参数不是数值,而是一些字符(如A等),则B2中返回错误值“#VALUE!”。 2、AND函数 函数名称:AND 主要功能:返回逻辑值:如果所有参数值均为逻辑“真(TRUE)”,则返回逻辑“真(TRUE)”,反之返回逻辑“假(FALSE)”。

使用格式:AND(logical1,logical2, ...) 参数说明:Logical1,Logical2,Logical3……:表示待测试的条件值或表达式,最多这30个。 应用举例:在C5单元格输入公式:=AND(A5>=60,B5>=60),确认。如果C5中返回TRUE,说明A5和B5中的数值均大于等于60,如果返回FALSE,说明A5和B5中的数值至少有一个小于60。 特别提醒:如果指定的逻辑条件参数中包含非逻辑值时,则函数返回错误值“#VALUE!”或“#NAME”。 3、AVERAGE函数 函数名称:AVERAGE 主要功能:求出所有参数的算术平均值。 使用格式:AVERAGE(number1,number2,……) 参数说明:number1,number2,……:需要求平均值的数值或引用单元格(区域),参数不超过30个。 应用举例:在B8单元格中输入公式: =AVERAGE(B7:D7,F7:H7,7,8),确认后,即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。 特别提醒:如果引用区域中包含“0”值单元格,则计算在内;如果引用区域中包含空白或字符单元格,则不计算在内。 4、COLUMN 函数 函数名称:COLUMN 主要功能:显示所引用单元格的列标号值。

电子表格常用函数公式

电子表格常用函数公式 1.去掉最高最低分函数公式: =SUM(所求单元格…注:可选中拖动?)—MAX(所选单元格…注:可选中拖动?)—MIN(所求单元格…注:可选中拖动?) (说明:“SUM”是求和函数,“MAX”表示最大值,“MIN”表示最小值。)2.去掉多个最高分和多个最低分函数公式: =SUM(所求单元格)—large(所求单元格,1)—large(所求单元格,2) —large(所求单元格,3)—small(所求单元格,1) —small(所求单元格,2) —small(所求单元格,3) (说明:数字123分别表示第一大第二大第三大和第一小第二小第三小,依次类推) 3.计数函数公式: count 4.求及格人数函数公式:(”>=60”用英文输入法) =countif(所求单元格,”>=60”) 5.求不及格人数函数公式:(”<60”用英文输入法) =countif(所求单元格,”<60”) 6.求分数段函数公式:(“所求单元格”后的内容用英文输入法) 90以上:=countif(所求单元格,”>=90”) 80——89:=countif(所求单元格,”>=80”)—countif(所求单元格,”<=90”) 70——79:=countif(所求单元格,”>=70”)—countif(所求单元

格,”<=80”) 60——69:=countif(所求单元格,”>=60”)—countif(所求单元格,”<=70”) 50——59:=countif(所求单元格,”>=50”)—countif(所求单元格,”<=60”) 49分以下: =countif(所求单元格,”<=49”) 7.判断函数公式: =if(B2,>=60,”及格”,”不及格”) (说明:“B2”是要判断的目标值,即单元格) 8.数据采集函数公式: =vlookup(A2,成绩统计表,2,FALSE) (说明:“成绩统计表”选中原表拖动,“2”表示采集的列数) 公式是单个或多个函数的结合运用。 AND “与”运算,返回逻辑值,仅当有参数的结果均为逻辑“真(TRUE)”时返回逻辑“真(TRUE)”,反之返回逻辑“假(FALSE)”。条件判断 AVERAGE 求出所有参数的算术平均值。数据计算 COLUMN 显示所引用单元格的列标号值。显示位置 CONCATENATE 将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中。字符合并 COUNTIF 统计某个单元格区域中符合指定条件的单元格数目。条件统计 DATE 给出指定数值的日期。显示日期

电子表格(EXCEL)函数大全

EXCEL函数大全 数据库和清单管理函数 AVERAGE 返回选定数据库项的平均值 DCOUNT计算数据库中包含数字的单元格的个数 DCOUNTA 计算数据库中非空单元格的个数 DGET 从数据库中提取满足指定条件的单个记录 DMAX 返回选定数据库项中的最大值 DMIN 返回选定数据库项中的最小值 DPRODUCT 乘以特定字段(此字段中的记录为数据库中满足指定条件的记录)中的值 DSTDEV 根据数据库中选定项的示例估算标准偏差 DSTDEVP 根据数据库中选定项的样本总体计算标准偏差DSUM 对数据库中满足条件的记录的字段列中的数字求和 DVAR 根据数据库中选定项的示例估算方差 DVARP 根据数据库中选定项的样本总体计算方差 GETPIVOTDATA 返回存储在数据透视表中的数据 日期和时间函数 DATE返回特定时间的系列数 DATEDIF 计算两个日期之间的年、月、日数 DATEVALUE将文本格式的日期转换为系列数 DAY 将系列数转换为月份中的日 DAYS360按每年360天计算两个日期之间的天数 EDATE返回在开始日期之前或之后指定月数的某个日期的系列数EOMONTH 返回指定月份数之前或之后某月的最后一天的系列数 HOUR 将系列数转换为小时 MINUTE 将系列数转换为分钟 MONTH 将系列数转换为月 NETWORKDAYS返回两个日期之间的完整工作日数 NOW 返回当前日期和时间的系列数 SECOND 将系列数转换为秒 TIME 返回特定时间的系列数 TIMEVALUE 将文本格式的时间转换为系列数 TODAY 返回当天日期的系列数 WEEKDAY 将系列数转换为星期 WORKDAY 返回指定工作日数之前或之后某日期的系列数 YEAR 将系列数转换为年 YEARFRAC 返回代表START_DATE(开始日期)和END_DATE(结束日期)之间天数的以年为单位的分数

电子表格常用函数公式

电子表格常用函数公式 1、自动排序函数: =RANK(第1数坐标,$第1数纵坐标$横坐标:$最后数纵坐标$横坐标,升降序号1降0升) 例如:=RANK(X3,$X$3:$X$155,0) 说明:从X3 到X 155自动排序 2、多位数中间取部分连续数值: =MID(该多位数所在位置坐标,所取多位数的第一个数字的排列位数,所取数值的总个数) 例如:612730************在B4坐标位置,取中间出生年月日,共8位数 =MID(B4,7,8) =19820711 说明:B4指该数据的位置坐标,7指从第7位开始取值,8指一共取8个数字 3、若在所取的数值中间添加其他字样, 例如:612730************在B4坐标位置,取中间出生年、月、日,要求****年**月**日格式 =MID(B4,7,4)&〝年〞&MID(B4,11,2) &〝月〞& MID(B4,13,2) &〝月〞&

=1982年07月11日 说明:B4指该数据的位置坐标,7、11指开始取值的第一位数排序号,4、2指所取数值个数,引号必须是英文引号。 4、批量打印奖状。 第一步建立奖状模板:首先利用Word制作一个奖状模板并保存为“奖状.doc”,将其中班级、姓名、获奖类别先空出,确保打印输出后的格式与奖状纸相符(如图1所示)。 第二步用Excel建立获奖数据库:在Excel表格中输入获奖人以及获几等奖等相关信息并保存为“奖状数据.xls”,格式如图2所示。 第三步关联数据库与奖状:打开“奖状.doc”,依次选择视图→工具栏→邮件合并,在新出现的工具栏中选择“打开数据源”,并选择“奖状数据.xls”,打开后选择相应的工作簿,默认为sheet1,并按确定。将鼠标定位到需要插入班级的地方,单击“插入域”,在弹出的对话框中选择“班级”,并按“插入”。同样的方法完成姓名、项目、等第的插入。 第四步预览并打印:选择“查看合并数据”,然后用前后箭头就可以浏览合并数据后的效果,选择“合并到新文档”可以生成一个包含所有奖状的Word文档,这时就可以批量打印了。

Excel表格公式大全 太强大了!

Excel表格公式大全太强大了! 2014-02-23免费订阅??每天一点经济学 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式: =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式: =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女 "),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和:=SUM(K2:K56) ——对K2到K56这一区域进行求和; 2、平均数:=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数; 3、排名:=RANK(K2,K$2:K$56) ——对55名学生的成绩进行排名; 4、等级:=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评:=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩; 6、最高分:=MAX(K2:K56) ——求K2到K56区域(55名学生)的最高分; 7、最低分:=MIN(K2:K56) ——求K2到K56区域(55名学生)的最低分; 8、分数段人数统计: (1)=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数;假设把结果存放于K57单元格; (2)=COUNTIF(K2:K56,">=95")-K57 ——求K2到K56区域95~99.5分的人数;假设把结果存放于K58单元格; (3)=COUNTIF(K2:K56,">=90")-SUM(K57:K58) ——求K2到K56区域90~94.5分的 人数;假设把结果存放于K59单元格; (4)=COUNTIF(K2:K56,">=85")-SUM(K57:K59) ——求K2到K56区域85~89.5分的 人数;假设把结果存放于K60单元格;

excel表格中关于函数的运用.doc

Excel表格中关于函数的运用 一,Vlookup的使用 表格一为作品信息 表格二为作品具体版权情况,现在需要将两个表格信息进行汇总 曹叫I 抹叫刘齐方式匚| 口SUM▼X # 長=vlookup(A2, A B C D E F 1 1 2皇帝由来甘断肠崖居dt=vlookup(A2, 3夏末天空赵彦君 4嫗变之间郎万鹏 1 5身边的高岸王正平1 ■6新的战争宋达威 1 7感动漠海边城1 8萤火虫的莠蝶花逐梦飞1 1 9人生的十与周继云 1 .0 ■字体对齐方式 * 7 匸二vlookup(A2, A2:C9, 3,0) B C D E F 来祐断肠崖居才通过=vlookup(A2, A2:C9, 3,0): 返回表格一

该公式为=vlookup (a2,表格二bn:表格二dm, y列,0)完成。 二,Excel中导入数据出现“#REF!”解决方案 当单元格引用无效时,就会出现这种错谋。 复制■选择性粘贴■选择“数值” ?确定 完成 附:常见错谋形式 1.#N/A当引用单元格无法得到有效值吋,显示#N/A错误。一般情况显示#N/A错误的情况主要冇:查找函数没冇发现匹配的对彖;某处使川了迭代计算的统计函数,如GAMMAINV 或BETAINV等,函数经过若干次迭代后,仍未收敛。有时,在单元格屮输入#N/A(或函数NA())还有特殊的含义:单元格屮输入#N/A表示单元格屮没有数据,当其他单元格引用这些单元格时,不进行数值计算;另外,在创建一个折线图时,缺少的数据以#"/人表示,折线图通过内插而画出;如果仅以空单元格表示,图表中则以一个缺口表示缺少的数据。 2.#NULL!当一个公式试图将两个不相交的范围进行交叉运算时,显示#NULL!错误。在Excel 的工作表屮可使用的引用运算符包括:(1)冒号(:)表示区域运算符,通过区域运算符产纶对包括在两个引用之间的所有单元格的引用。如SUM(A1:A1O)是对A1到A10的所有单元格求合;(2)逗号(,)表示联合运算符,将多个引用合并为一个引用。如SUM (A1:A1O,B1:C3)是对这两块区域屮的所侑单元格求和;(3)空格()表示交叉运算符,产生对两个引用交叉的单元格的引用。当二个引用或范围没有交叉单元格,但要进行交叉运算时,就显示#NULL! 错误。或者使用了不适当的运算符时,也出现这种错误。如,本是区域运算符的,却错成交叉运算符(把冒号给漏掉了)。 3.#NUM!当公式或函数屮使用无效数字值时,岀现这种错课。在出现下列情况时,发生此错误: (1)部分使用了迭代计算的工作表函数,如IRR或RATE,在函数无法得到有效的结果时,显示#NUM错误。此时或为工作表函数使用不同的初始值或更改Microsoft Excel迭代公式的次数来解决错谋。 (2)输入的公式产生的数字太大或太小,无法在Microsoft Excel中表示。Excel支持的数值范围是1E-307到1E+307。如在单元格中输入=1/(10八310),则显示此错误。 (3)当公式或函数中输入了无效数字时,显示此错谋。如不少统计函数要使用到概率参数,如果该参数大于1或小于0时显示此错误。如果有些参数要求为非负,当参数是负数时也显示此错误。

Excel电子表格计算公式使用方法技巧总结

Excel电子表格计算公式使用方法25条公式技巧总结 对于Excel表格计算公式的方法实在太多,今天就整理了一个公式大全需要对有需要的朋友有些帮助。 1、两列数据查找相同值对应的位置 =MATCH(B1,A:A,0) 2、已知公式得结果 定义名称=EVALUATE(Sheet1!C1) 已知结果得公式 定义名称=GET.CELL(6,Sheet1!C1) 3、强制换行 用Alt+Enter 4、超过15位数字输入 这个问题问的人太多了,也收起来吧。一、单元格设置为文本;二、在输入数字前先输入' 5、如果隐藏了B列,如果让它显示出来? 选中A到C列,点击右键,取消隐藏 选中A到C列,双击选中任一列宽线或改变任一列宽 将鼠标移到到AC列之间,等鼠标变为双竖线时拖动之。 6、EXCEL中行列互换 复制,选择性粘贴,选中转置,确定即可 7、Excel是怎么加密的 (1)、保存时可以的另存为>>右上角的"工具">>常规>>设置 (2)、工具>>选项>>安全性

8、关于COUNTIF COUNTIF函数只能有一个条件,如大于90,为=COUNTIF(A1:A10,">=90") 介于80与90之间需用减,为=COUNTIF(A1:A10,">80")-COUNTIF(A1:A10,">90") 9、根据身份证号提取出生日期 (1)、=IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),IF (LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)),"错误身份证号")) (2)、=TEXT(MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")*1 10、想在SHEET2中完全引用SHEET1输入的数据 工作组,按住Shift或Ctrl键,同时选定Sheet1、Sheet2 11、一列中不输入重复数字 [数据]--[有效性]--[自定义]--[公式] 输入=COUNTIF(A:A,A1)=1 如果要查找重复输入的数字 条件格式》公式》=COUNTIF(A:A,A5)>1》格式选红色 12、直接打开一个电子表格文件的时候打不开 “文件夹选项”-“文件类型”中找到.XLS文件,并在“高级”中确认是否有参数1%,如果没有,请手工加上 13、excel下拉菜单的实现 [数据]-[有效性]-[序列] 14、10列数据合计成一列 =SUM(OFFSET($A$1,(ROW()-2)*10+1,,10,1)) 15、查找数据公式两个(基本查找函数为VLOOKUP,MATCH) (1)、根据符合行列两个条件查找对应结果

excel函数公式大全

EXCEL2003公式·函数应用大全 1、SUMPRODUCT函数:该函数的功能是在给定的几组数组中将数组间对应的元素相乘并返回乘积之和。例如:如图1,如果想计算B 3: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.979,即数学常量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”。

office-excel表格函数大全

EXCEL函数大全 数据库和清单管理函数 DAVERAGE 返回选定数据库项的平均值 DCOUNT 计算数据库中包含数字的单元格的个数 DCOUNTA 计算数据库中非空单元格的个数 DGET 从数据库中提取满足指定条件的单个记录 DMAX 返回选定数据库项中的最大值 DMIN 返回选定数据库项中的最小值 DPRODUCT 乘以特定字段(此字段中的记录为数据库中满足指定条件的记录)中的值 DSTDEV 根据数据库中选定项的示例估算标准偏差 DSTDEVP 根据数据库中选定项的样本总体计算标准偏差 DSUM 对数据库中满足条件的记录的字段列中的数字求和 DVAR 根据数据库中选定项的示例估算方差 DVARP 根据数据库中选定项的样本总体计算方差 GETPIVOTDATA 返回存储在数据透视表中的数据 日期和时间函数 DATE 返回特定时间的系列数 DATEDIF 计算两个日期之间的年、月、日数 DATEVALUE 将文本格式的日期转换为系列数 DAY 将系列数转换为月份中的日 DAYS360 按每年360天计算两个日期之间的天数 EDATE 返回在开始日期之前或之后指定月数的某个日期的系列数 EOMONTH 返回指定月份数之前或之后某月的最后一天的系列数 HOUR 将系列数转换为小时 MINUTE 将系列数转换为分钟 MONTH 将系列数转换为月 NETWORKDAYS 返回两个日期之间的完整工作日数 NOW 返回当前日期和时间的系列数 SECOND 将系列数转换为秒 TIME 返回特定时间的系列数 TIMEVALUE 将文本格式的时间转换为系列数 TODAY 返回当天日期的系列数 WEEKDAY 将系列数转换为星期 WORKDAY 返回指定工作日数之前或之后某日期的系列数 YEAR 将系列数转换为年 YEARFRAC 返回代表START_DATE(开始日期)和END_DATE(结束日期)之间天数的以年为单位的分数DDE 和外部函数 CALL 调用动态链接库(DLL)或代码源中的过程 REGISTER.ID 返回已注册的指定DLL或代码源的注册ID SQL.REQUEST 连接外部数据源,并从工作表中运行查询,然后将结果作为数组返回,而无需进行宏编程。 有关CALL和REGISTER函数的其他信息 工程函数 BESSELI 返回经过修改的贝塞尔函数IN(X)

相关主题