EXCEL2003公式·函数应用大全
1、SUMPRODUCT函数：该函数的功能是在给定的几组数组中将数组间对应的元素相乘并返回乘积之和。例如：如图1，如果想计算B3：C6和D3：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所示，然后利用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（星期日）之间的数字。
“=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(Y EAR(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(H OUR(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
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="男","先生","女士")”，该公式表示在姓名中取出左边的第一个字，用&连接上"先生"或者"女士"称呼。
图56
图55
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中输入以下公式：
60、PROPER函数：此函数可以自动转换大小写。首先在工作表中输入一些字母或者英文句子，
如图61所示，然后在单元格C3中输入以下公式：“=PROPER(B3)”。
Excel2007函数公式收集了688个实例，涉及到137个函数、7个行业、41类用途，为大家提供一个参考，拓展思路的机会。公式由{}包括的为数组公式，在复制粘贴到单元后先去掉{}然后按住Shift键+Ctrl键再按Enter键，自动生成数组公式。 对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7) 对生产表于100的产量进行求和:{=SUM((B2:B11>100)*B2:B11)} 对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)} 对一车间男性职工的工资求和:{=SUM((B2:B10="一车间")*(C2:C10="男")*D2:D10)} 对姓的女职工工资求和:{=SUM((LEFT(A2:A10)="")*(C2:C10="女")*D2:D10)} 求前三名产量之和:=SUM(LARGE(B2:B10,{1,2,3})) 求所有工作表相同区域数据之和:=SUM(A组:E组!B2:B9) 求图书订购价格总和:{=SUM((B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)} 求当前表以外的所有工作表相同区域的总和:=SUM(一月:五月!B2) 用SUM函数计数:{=SUM((B2:B9="男")*1)} 求1累加到100之和:{=SUM(ROW(1:100))} 多个工作表不同区域求前三名产量和:{=SUM(LARGE(CHOOSE({1,2,3,4,5},A组!B2:B9,B组!B2:B9,C组!B2:B9,D 组!B2:B9,E组!B2:B9),ROW(1:3)))} 计算仓库进库数量之和:=SUMIF(B2:B10,"=进库",C2:C10) 计算仓库大额进库数量之和:=SUMIF(B2:B8,">1000") 对1400到1600之间的工资求和:{=SUM(SUMIF(B2:B10,"<="&{1400,1600})*{-1,1})} 求前三名和后三名的数据之和:=SUMIF(B2:B10,">"&LARGE(B2:B10,4))+SUMIF(B2:B10,"<"&SMALL(B2:B10,4)) 对所有车间人员的工资求和:=SUMIF(A2:A10,"?车间",C2) 对多个车间人员的工资求和:=SUMIF(A2:A10,"??车间*",C2) 汇总姓、、的业务员提成金额:=SUM(SUMIF(A2:A10,{"","",""}&"*",C2:C10)) 汇总鼠标所在列于600的数据:=SUMIF(INDIRECT("R2C"&CELL("col")&":R8C"&CELL("col"),FALSE),">600")
excel常用函数公式及技巧搜集（常用的）【身份证信息？提取】 从身份证号码中提取出生年月日 =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0 =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1 =IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,) 显示格式均为yyyy-m-d。（最简单的公式，把单元格设置为日期格式） =IF(LEN(A2)=15,"19"&MID(A2,7,2)&"-"&MID(A2,9,2)&"-"&MID(A2,11,2),MID(A2,7,4)& "-"&MID(A2,11,2)&"-"&MID(A2,13,2)) 显示格式为yyyy-mm-dd。（如果要求为“1995/03/29”格式的话，将”-”换成”/”即可） =IF(D4="","",IF(LEN(D4)=15,TEXT(("19"&MID(D4,7,6)),"0000年00月00日 "),IF(LEN(D4)=18,TEXT(MID(D4,7,8),"0000年00月00日")))) 显示格式为yyyy年mm月dd日。（如果将公式中“0000年00月00日”改成“0000-00-00”,则显示格式为yyyy-mm-dd） =IF(LEN(A1:A2)=18,MID(A1:A2,7,8),"19"&MID(A1:A2,7,6)) 显示格式为yyyymmdd。 =TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0 =IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2)) =MID(A1,7,4)&"年"&MID(A1,11,2)&"月"&MID(A1,13,2)&"日" =IF(A1<>"",TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")) 从身份证号码中提取出性别 =IF(MOD(MID(A1,15,3),2),"男","女") （最简单公式） =IF(MOD(RIGHT(LEFT(A1,17)),2),"男","女") =IF(A2<>””,IF(MOD(RIGHT(LEFT(A2,17)),2),”男”,”女”),) =IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,"男","女") 从身份证号码中进行年龄判断 =IF(A3<>””,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#-00-00”),T ODAY(),”Y”),) =DATEDIF（A1，TODAY（），“Y”） （以上公式会判断是否已过生日而自动增减一岁） =YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900 =YEAR(TODAY())-IF(LEN(A1)=15,"19"&MID(A1,7,2),MID(A1,7,4)) =YEAR(TODAY())-V ALUE(MID(B1,7,4))&"岁" =YEAR(TODAY())-IF(MID(B1,18,1)="",CONCATENATE("19",MID(B1,7,2)),MID(B1,7,4)) 按身份证号号码计算至今天年龄 =DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(),"y") 以2006年10月31日为基准日,按按身份证计算年龄(周岁)的公式
Excel2007函数大全 一、函数应用基础 1.函数和公式 (1)什么是函数 Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。以常用的求和函数SUM为例，它的语法是“SUM(number1,number2,......)”。其中“SUM”称为函数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后用一个右括号表示函数结束。 参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。使得用户可以对某个单元格或区域进行处理，如分析存款利息、确定成绩名次、计算三角函数值等。 按照函数的来源，Excel函数可以分为内置函数和扩展函数两大类。前者只要启动了Excel，用户就可以使用它们；而后者必须通过单击“工具→加载宏”菜单命令加载，然后才能像内置函数那样使用。 (2)什么是公式 函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的公式。以公式“=SUM(E1:H1)*A1+26”为例，它要以等号“=”开始，其内部可以包括函数、引用、运算符和常量。上式中的“SUM(E1:H1)”是函数，“A1”则是对单元格A1的引用(使用其中存储的数据)，“26”则是常量，“*”和“+”则是算术运算符(另外还有比较运算符、文本运算符和引用运算符)。 如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数本身。 2.函数的参数 函数右边括号中的部分称为参数，假如一个函数可以使用多个参数，那么参数与参数之间使用半角逗号进行分隔。参数可以是常量(数字和文本)、逻辑值(例如TRUE或FALSE)、数组、错误值(例如#N/A)或单元格引用(例如E1:H1)，甚至可以是另一个或几个函数等。参数的类型和位置必须满足函数语法的要求，否则将返回错误信息。 (1)常量 常量是直接输入到单元格或公式中的数字或文本，或由名称所代表的数字或文本值，例如数字“2890.56”、日期“2003-8-19”和文本“黎明”都是常量。但是公式或由公式计算出的结果都不是常量，因为只要公式的参数发生了变化，它自身或计算出来的结果就会发生变化。 (2)逻辑值 逻辑值是比较特殊的一类参数，它只有TRUE(真)或FALSE(假)两种类型。例如在公式“=IF(A3=0,"",A2/A3)”中，“A3=0”就是一个可以返回TRUE(真)或FALSE(假)两种结果的参数。当“A3=0”为TRUE(真)时在公式所在单元格中填入“0”，否则在单元格中填入“A2/A3”的计算结果。 (3)数组 数组用于可产生多个结果，或可以对存放在行和列中的一组参数进行计算的公式。Excel中有常量和区域两类数组。前者放在“{}”(按下Ctrl+Shift+Enter组合键自动生成)内部，而且内部各列的数值要用逗号“,”隔开，各行的数值要用分号“;”隔开。假如你要表示第1行中的56、78、89和第2行中的90、76、80，就应该建立一个2行3列的常量数组“{56,78,89;90,76,80}。 区域数组是一个矩形的单元格区域，该区域中的单元格共用一个公式。例如公式“=TREND(B1:B3,A1:A3)”作为数组公式使用时，它所引用的矩形单元格区域“B1:B3,A1:A3”就是一个区域数组。 (4)错误值 使用错误值作为参数的主要是信息函数，例如“ERROR.TYPE”函数就是以错误值作为参数。它的语法为“ERROR.TYPE(error_val)”，如果其中的参数是#NUM!，则返回数值“6”。 (5)单元格引用 单元格引用是函数中最常见的参数，引用的目的在于标识工作表单元格或单元格区域，并指明公式或函数所使用的数据的位置，便于它们使用工作表各处的数据，或者在多个函数中使用同一个单元格的数据。还可以引用同一工作簿不同工作表的单元格，甚至引用其他工作簿中的数据。 根据公式所在单元格的位置发生变化时，单元格引用的变化情况，我们可以引用分为相对引用、绝对引用和混合引用三种类型。以存放在F2单元格中的公式“=SUM(A2:E2)”为例，当公式由F2单元格复制到F3单元格以后，公式中的引用也会变化为“=SUM(A3:E3)”。若公式自F列向下继续复制，“行标”每增加1行，公式中的行标也自动加1。
EXCEL2007新增的几个多条件函数解析 内容来自网络，在此感谢作者。整理成文档仅作学习之用。查原文请点击链接：https://www.doczj.com/doc/fb9523954.html,/7909652.html或者https://www.doczj.com/doc/fb9523954.html,/1674104/forum/
AVERAGEIF返回某个区域内满足给定条件的所有单元格的平均值（算术平均值）。其语法格式 是: AVERAGEIF(range,criteria,average_range) Range是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。Criteria 是数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。例如，条件可以表示为 32、"32"、">32"、"apples" 或 B4。Average_range 是要计算平均值的实际单元格集。如果忽略，则使用 range。 EXCEL2003时没有此新增函数公式为 方法一:数组公式 =AVERAGE(IF(B4:B11="一班",E4:E11)) 方法二:=SUMIF(B4:B11,"一班",E4:E11)/COUNTIF(B4:B11,"一班") EXCEL2007版AVERAGEIF公式为 =AVERAGEIF(B5:B12,"一班",E5:E12)
公式中range是指B5:B12，即“班级”这列的所有班组情况。criteria是指“一班”，即满足班级为“一班”，average_range是指E5：E12，即对应实际计算的成绩集。 AVERAGEIFS返回满足多重条件的所有单元格的平均值（算术平均值）。其语法格式是: AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…) Average_range 是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。Criteria_range1, criteria_range2, …是计算关联条件的 1 至 127 个区域。 Criteria1, criteria2, …是数字、表达式、单元格引用或文本形式的 1 至 127 个条件，用于定义要对哪些单元格求平均值。例如，条件可以表示为 32、""32""、"">32""、""apples"" 或 B4。EXCEL2003时没有此新增函数公式为 方法一:数组公式
excel2007函数练习题 1.常用函数 2015年职称计算机考试Excel2007模块常用函数考点，考试频率：★★★★★掌握难度：★★ 具体详细讲解如下： 1.职称考试Excel2007模块：求和函数SUM 功能：SUM为数学与三角函数类，功能是求列表中所有参数的和。 格式为：SUM 参数：number1,number2,???为1~255个需要求和的参数。能够作为参数的有数字、逻辑值、文本数字、单元格和区域的引用地址、、名称及标志。 单击要存放的单元格，单击按钮，打开对话框，选择常用函数类型中的SUM，选取求和区域，单击按钮。 2.计算机职称考试Excel2007：求平均值函数AVERAGE 功能：AVERAGE函数用于统计数据集的算术平均值。 格式：AVERAGE 参数：包括数字、逻辑值、文本数字、单元格和区域引用地址、名称及标志。Number1为1~255个需要计算平均值的参数。 计算时，参数中逻辑值TRUE被转换为1，FALSE被转换为0。文本数字也被转换为数值。参数中引用单元格或区域，只计算其中的数字，其他被忽略。
打开对话框输入求平均值函数公式。 3.计算机职称考试Excel2007模块：最大值函数MAX、最小值函数MIN 功能：求包含数据集中的最大数值MAX，最小值MIN，都属于统计函数。 格式：MAX/MIN 参数：最多有255个参数，数字、逻辑值、文本数字、空白单元格、单元格和区域的引用地址、名称及标志都可以作参数。 直接在编辑框栏中输入公式或者在对话框中输入函数求平均值。 4.计算机职称考试Excel2007模块：计数函数COUNT 和COUNTA 功能：计数函数为统计函数，用于统计数字项个数。COUNTA为不含空值。 格式：COUNT/COUNT 参数：可以引用各种类型的数据，最多有55个参数。 COUNT函数计算时会忽略非数字文本和错误值。如果参数是单元格和区域，则只统计引用中的数字、日期值。 COUNTA函数可以计算任意类型参数。如果参数为单元格和区域，则只忽略空白单元格，其他都能被统计。如果不需要统计逻辑值、文字或错误值，就用COUNT函数。
EXCEL中所有函数的使用2007-09-05 17:051．求和函数SUM 语法：SUM（number1，number2，．．．）。 参数：number1、number2．．．为1到30个数值（包括逻辑值和文本表达式）、区域或引用，各参数之间必须用逗号加以分隔。 注意：参数中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为1，文本则被转换为数字。如果参数为数组或引用，只有其中的数字参与计算，数组或引用中的空白单元格、逻辑值、文本或错误值则被忽略。 应用实例一：跨表求和 使用SUM函数在同一工作表中求和比较简单，如果需要对不同工作表的多个区域进行求和，可以采用以下方法：选中Excel XP“插入函数”对话框中的函数，“确定”后打开“函数参数”对话框。切换至第一个工作表，鼠标单击“number1”框后选中需要求和的区域。如果同一工作表中的其他区域需要参与计算，可以单击“number2”框，再次选中工作表中要计算的其他区域。上述操作完成后切换至第二个工作表，重复上述操作即可完成输入。“确定”后公式所在单元格将显示计算结果。 应用实例二：SUM函数中的加减混合运算 财务统计需要进行加减混合运算，例如扣除现金流量表中的若干支出项目。按照规定，工作表中的这些项目没有输入负号。这时可以构造“=SUM（B2:B6，C2:C9，-D2，-E2）”这样的公式。其中B2:B6，C2:C9引用是收入，而D2、E2为支出。由于Excel不允许在单元格引用前面加负号，所以应在表示支出的单元格前加负号，这样即可计算出正确结果。即使支出数据所在的单元格连续，也必须用逗号将它们逐个隔开，写成“=SUM（B2:B6，C2:C9，-D2，-D3，D4）”这样的形式。 应用实例三：及格人数统计 假如B1:B50区域存放学生性别，C1:C50单元格存放某班学生的考试成绩，要想统计考试成绩及格的女生人数。可以使用公式“=SUM（IF（B1:B50=〃女〃，IF（C1:C50>=60，1，0）））”，由于它是一个数组公式，输入结束后必须按住Ctrl+Shift键回车。公式两边会自动添加上大括号，在编辑栏显示为“{=SUM（IF（B1:B50=〃女〃，IF（C1:C50& gt;=60，1，0）））}”，这是使用数组公式必不可少的步骤。 2.平均值函数A VERAGE 语法：A VERAGE（number1，number2，．．．）。 参数：number1、number2．．．是需要计算平均值的1～30个参数。 注意：参数可以是数字、包含数字的名称、数组或引用。数组或单元格引用中的文字、逻辑值或空白单元格将被忽略，但单元格中的零则参与计算。如果需要将参数中的零排除在外，则要使用特殊设计的公式，下面的介绍。
一、数字处理 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中常用统计函数介绍 2007-04-01 10:41 Excel的统计工作表函数用于对数据区域进行统计分析。例如，统计工作表函数可以用来统计样本的方差、数据区间的频率分布等。是不是觉得好像是很专业范畴的东西？是的，统计工作表函数中提供了很多属于统计学范畴的函数，但也有些函数其实在你我的日常生活中是很常用的，比如求班级平均成绩，排名等。在本文中，主要介绍一些常见的统计函数，而属于统计学范畴的函数不在此赘述，详细的使用方法可以参考Excel帮助及相关的书籍。 在介绍统计函数之前，请大家先看一下附表中的函数名称。是不是发现有些函数是很类似的，只是在名称中多了一个字母A？比如，A VERAGE与A VERAGEA；COUNT与COUNTA。基本上，名称中带A的函数在统计时不仅统计数字，而且文本和逻辑值（如TRUE 和FALSE）也将计算在内。在下文中笔者将主要介绍不带A的几种常见函数的用法。 一、用于求平均值的统计函数A VERAGE、TRIMMEAN 1、求参数的算术平均值函数A VERAGE 语法形式为AVERAGE(number1,number2, ...) 其中Number1, number2, ...为要计算平均值的1～30 个参数。这些参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。 2、求数据集的内部平均值TRIMMEAN 函数TRIMMEAN先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。当希望在分析中剔除一部分数据的计算时，可以使用此函数。比如，我们在计算选手平均分数中常用去掉一个最高分，去掉一个最低分，XX号选手的最后得分，就可以使用该函数来计算。语法形式为TRIMMEAN(array,percent) 其中Array为需要进行筛选并求平均值的数组或数据区域。Percent为计算时所要除去的数据点的比例，例如，如果percent = 0.2，在20 个数据点的集合中，就要除去4 个数据点（20 x 0.2），头部除去2 个，尾部除去2 个。函数TRIMMEAN 将除去的数据点数目向下舍为最接近的2 的倍数。 3、举例说明：示例中也列举了带A的函数A VERAGEA的求解方法。求选手Annie的参赛分数。在这里，我们先假定已经将该选手的分数进行了从高到底的排序，在后面的介绍中我们将详细了解排序的方法。 二、用于求单元格个数的统计函数COUNT 语法形式为COUNT(value1,value2, ...) 其中Value1, value2, ...为包含或引用各种类型数据的参数（1～30个），但只有数字类型的数据才被计数。函数COUNT 在计数时，将把数字、空值、逻辑值、日期或以文字代表的数计算进去；但是错误值或其他无法转化成数字的文字则被忽略。如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组中或引用的空单元格、逻辑值、文字或错误值都将忽略。如果要统计逻辑值、文字或错误值，应当使用函数COUNTA。举例说明COUNT 函数的用途，示例中也列举了带A的函数COUNTA的用途。仍以上例为例，要计算一共有多少评委参与评分（用函数COUNTA），以及有几个评委给出了有效分数（用函数COUNT）。
1. 如果直接复制excel表格应用于其他软件（Word、PoewerPoint），会出现主题颜色、样式不一的问题。对 对错 2. (单选题)在Excel 2007中，（）函数是求最大值的函数。B A. COUNT B. MAX C. MIN D. A VERAGE 3. Excel工作表标签上文字的字号是由Windows系统设置决定的，但是更改的话，在Excel 里也是可以完成的。错 对错 4. (单选题)在Excel 2007中，若选定多个不连续的行所用的键是（）。A A. Crtl B. Shift + Ctrl C. Alt D. Shift 5. (单选题)在Excel 2007中，在输入数据的时候，有些连续单元格要输入的数据是有规律的时候，可以采取（）的快捷功能.C A. 剪切粘贴 B. 逐个输入 C. 自动填充 D. 复制粘贴 6. (单选题)Excel操作中，如果将某些单元格抹黑（选中），然后按一下Del键，将删除单元格中的（）。D错A A. 输入内容（数值或公式），保留格式和附注 B. 数据和公式，只保留格式 C. 附注 D. 全部内容（包括格式和附注） 7. (单选题)Microsoft Office 2007版本的界面，把功能区界面改成（）的形式。D A. 窗口 B. 菜单 C. 工具栏 D. 选项卡 8. (多选题)单元格的数据类型可以是（）。ABCD A. 数值型 B. 文本型 C. 日期型 D. 货币型 9. (单选题)在Excel 2007中，要为一数增加小数位数，应使用“开始”选项卡（）组中的工具。 B A. 样式 B. 数字 C. 字体 D. 对齐方式10. 10. (多选题)下面有关单元格的描述中，正确的有（）。CDE错ACDE A. 单元格的边框线是可以改变的 B. 单元格的高度和宽度不能调整 C. 同列单元格的宽度必须相同 D. 同行单元格的高度必须相同 E. 单元格能有底纹 11. 在Excel 2007中，“隐藏”功能只能对工作簿隐藏，不能隐藏表格的行或列。错 对错 12. (单选题)在Excel 2007中，在单元格内输入电话号码025-*******应如何输入（）。A A. ‘025-******* B. “025-******* C. 025-******* D. ^025-******* 13. (单选题)使用Excel 2007时，在编辑菜单中，可以将选定单元格的内容清空，而保留单元格格式信息的是（）。C A. 撤消 B. 剪切 C. 清除 D. 删除 14. (单选题)在Excel 2007中，“图表”组在（）选项卡中。D错B A. 公式 B. 插入 C. 数据 D. 开始 15. 安装兼容包并不能为旧版本的Excel提供Excel2007新增功能，只能提供打开和编辑文件的功能。错 对错 16. (单选题)Excel 2007中可以把数据表快速变成图片的功能叫做（）。D错C A. 创建文件 B. 另存为 C. 创建快照 D. 快速截图 17. (单选题)在Excel 2007中，下列哪个快捷键可以在一个单元格内容换行。D A. Ctrl + Enter B. Enter C. Shift + Enter D. Alt + Enter 18. (多选题)在Excel 2007中，一般情况会显示（）这些选项卡。BCD错ABCD
Excel2007常用技巧（中） 来源：互联网 1.用鼠标快速复制单元格 在移动单元格的过程中，如果按住Ctrl键，则会在鼠标指针的右上角显示“＋”号，到达目标位置之后，先松开鼠标再松开Ctrl键就可以实现单元格的复制。 2.为隐藏的数据设置密码 对隐藏的数据可以对其设置密码，具体操作如下： （1）首先用隐藏数据的方法，将指定单元格中的数据进行隐藏。 （2）保持选中隐藏数据的单元格，打开“设置单元格格式”对话框，切换到“保护”选项卡，选择“隐藏”项，设置完后单击“确定”按钮。 （3）在Excel窗口中切换到“审阅”选项卡，在“更改”中单击“保护工作表”按钮。 （4）弹出“保护工作表”对话框，输入密码，单击“确定”按钮，弹出“确认密码”对话框，重新输入密码确认，单击“确定”按钮完成设置。 3.取消隐藏的数据 当想显示隐藏的数据时，具体操作如下： （1）切换到“审阅”选项卡，在“更改”选项组中单击“撤消工作表保护”按钮。 （2）弹出“撤消工作表保护”对话框，输入设置的密码，单击“确定”按钮。 （3）用鼠标右键单击隐藏数据的单元格式，选择“设置单元格格式”命令，打开“设置单元格格式”对话框，切换到“保护”选项卡，取消对“隐藏”项的选择，再切换到“数字”选项卡，删除“;;;”符号。 4.让工作簿同时滚动显示 在处理多个工作簿的时候，有时需要将一些工作簿进行对比，Excel为了方便用户进行工作簿之间的对比，提供了工作簿并排并同时滚动显示的功能。 （1）打开要并排显示并且同时滚动显示的工作簿，在其中一个工作簿中选择“视图”选项卡，在“窗口”中单击“并排查看”按钮。 （2）工作簿被并排之后，在“窗口”区域单击“同步滚动”按钮。 5.精确调整单元格的大小 （1）选择要调整大小的单元格。 （2）选择“开始”选项卡，在“单元格”中单击“格式”按钮，在弹出的下拉菜单中选择“行高”项。 （3）弹出“行高”对话框，输入行高的数值，单击“确定”按钮。 （4）同样的，选择“列宽”项，可以设置列宽值。 6.设置分数自动以百分之几方式显示
excel常用函数公式及技巧搜集 【身份证信息？提取】 从身份证号码中提取出生年月日 =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0 =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1 =IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,) 显示格式均为yyyy-m-d。（最简单的公式，把单元格设置为日期格式） =IF(LEN(A2)=15,"19"&MID(A2,7,2)&"-"&MID(A2,9,2)&"- "&MID(A2,11,2),MID(A2,7,4)&"-"&MID(A2,11,2)&"-"&MID(A2,13,2)) 显示格式为yyyy-mm-dd。（如果要求为“1995/03/29”格式的话，将”-”换成”/”即可） =IF(D4="","",IF(LEN(D4)=15,TEXT(("19"&MID(D4,7,6)),"0000年00月00日 "),IF(LEN(D4)=18,TEXT(MID(D4,7,8),"0000年00月00日")))) 显示格式为yyyy年mm月dd日。（如果将公式中“0000年00月00日”改成“0000-00-00”,则显示格式为yyyy-mm-dd） =IF(LEN(A1:A2)=18,MID(A1:A2,7,8),"19"&MID(A1:A2,7,6)) 显示格式为yyyymmdd。 =TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0 =IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2)) =MID(A1,7,4)&"年"&MID(A1,11,2)&"月"&MID(A1,13,2)&"日" =IF(A1<>"",TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")) 从身份证号码中提取出性别 =IF(MOD(MID(A1,15,3),2),"男","女") （最简单公式） =IF(MOD(RIGHT(LEFT(A1,17)),2),"男","女") =IF(A2<>””,IF(MOD(RIGHT(LEFT(A2,17)),2),”男”,”女”),) =IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,"男","女") 从身份证号码中进行年龄判断 =IF(A3<>””,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#-00-00”),TODAY(),”Y”),) =DATEDIF（A1，TODAY（），“Y”） （以上公式会判断是否已过生日而自动增减一岁） =YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900 =YEAR(TODAY())-IF(LEN(A1)=15,"19"&MID(A1,7,2),MID(A1,7,4)) =YEAR(TODAY())-VALUE(MID(B1,7,4))&"岁" =YEAR(TODAY())-IF(MID(B1,18,1)="",CONCATENATE("19",MID(B1,7,2)),MID(B1,7,4)) 按身份证号号码计算至今天年龄 =DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(),"y") 以2006年10月31日为基准日,按按身份证计算年龄(周岁)的公式=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1,"2006-10-31","y") 按身份证号分男女年龄段 按身份证号分男女年龄段，身份证号在K列，年龄段在J列（身份证号为18位）男性16周岁以下为1 男性16周岁（含16周岁）以上至50周岁为 2 男性50周岁（含50周岁）以上至60周岁为 3
Excel 2007函数公式实例汇总 Excel2007函数公式收集了688个实例，涉及到137个函数、7个行业、41类用途，为大家提供一个参考，拓展思路的机会。公式由{}包括的为数组公式，在复制粘贴到单元后先去掉{}然后按住Shift键+Ctrl键再按Enter键，自动生成数组公式。 对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7) 对生产表于100的产量进行求和:{=SUM((B2:B11>100) *B2:B11)} 对生产表大于110或者小于100的数据求 和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)} 对一车间男性职工的工资求和:{=SUM((B2:B10="一车间")*(C2:C10="男 ")*D2:D10)} 对姓的女职工工资求和:{=SUM((LEFT(A2:A10)="")*(C2:C10="女")*D2:D10)} 求前三名产量之和:=SUM(LARGE(B2:B10,{1,2,3})) 求所有工作表相同区域数据之和:=SUM(A组:E组!B2:B9) 求图书订购价格总和:{=SUM((B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)} 求当前表以外的所有工作表相同区域的总和:=SUM(一月:五月!B2)
用SUM函数计数:{=SUM((B2:B9="男")*1)} 求1累加到100之和:{=SUM(ROW(1:100))} 多个工作表不同区域求前三名产量和:{=SUM(LARGE(CHOOSE({1,2,3,4,5},A 组!B2:B9,B组!B2:B9,C组!B2:B9,D组!B2:B9,E组!B2:B9),ROW(1:3)))} 计算仓库进库数量之和:=SUMIF(B2:B10,"=进库",C2:C10) 计算仓库大额进库数量之和:=SUMIF(B2:B8,">1000") 对1400到1600之间的工资求 和:{=SUM(SUMIF(B2:B10,"<="&{1400,1600})*{-1,1})} 求前三名和后三名的数据之 和:=SUMIF(B2:B10,">"&LARGE(B2:B10,4))+SUMIF(B2:B10,"<"&SMALL(B 2:B10,4)) 对所有车间人员的工资求和:=SUMIF(A2:A10,"?车间",C2) 对多个车间人员的工资求和:=SUMIF(A2:A10,"??车间*",C2) 汇总姓、、的业务员提成金额:=SUM(SUMIF(A2:A10,{"","",""}&"*",C2:C10)) 汇总鼠标所在列于600的数 据:=SUMIF(INDIRECT("R2C"&CELL("col")&":R8C"&CELL("col"),FALSE),"> 600")
Excel2007 TEXT函数是干什么用的？怎么用？ TEXT 函数将数值转换为文本，并使您可以使用特殊格式字符串指定显示格式。要以可读性更高的格式显示数字，或要将数字与文本或符号合并时，此函数非常有用。 语法：TEXT(value, format_text) TEXT 函数语法具有下列参数： value 必需。数值、计算结果为数值的公式，或对包含数值的单元格的引用。format_text 必需。用引号括起的文本字符串的数字格式。例如，“m/d/yyyy”或“#,##0.00”。有关特定格式准则，请参阅以下部分。 有关数字格式的准则 显示小数位和有效位若要为包含小数点的分数或数字设置格式，应在format_text参数中包含以下数字占位数、小数点和千位分隔符。 说明： 0（零）：如果数字的位数少于格式中的零的个数，则会显示无效零。例如，如果键入8.9，但希望将其显示为8.90，请使用格式#.00。 #：遵循与0(零) 相同的规则。但是，如果所键入数字的小数点任一侧的位数小于格式中# 符号的个数，则Excel 不会显示多余的零。例如，如果自定义格式为#.##，而在单元格中键入了8.9，则会显示数字8.9。 ?：遵循与0(零) 相同的规则。但Excel 会为小数点任一侧的无效零添加空格，以便使列中的小数点对齐。例如，自定义格式0.0? 将列中数字8.9 和88.99 的小数点对齐。 .（句点）：在数字中显示小数点。如果数字的小数点右侧的位数大于格式中的占位符数，则该数字的小数位数会舍入到与占位符数相同。如果小数点左侧的位数大于格式中的占位符数，则会显示多出的位数。如果格式仅在小数点的左侧包含数字记号(#)，则小于1 的数字都以小数点开头；例如.47。对19700410这个数使用“#-00-00”参数后的结果为“1970-04-10”。 显示内容
如何使用电子表格excel中的一些常用计算公式 一、年龄计算公式： =IF((YEAR(E2)=2008)*(MONTH(E2)>8),0,2008-YEAR(E2)IF(MONTH(E2)<=8,0,-1)) 其中e2为单元格，2008为当前年份 二、身份证号中提取出生年月=MID(J11,7,4)&"年"&MID(J11,11,2)&"月"&MID(J11,13,2)&"日" 三、班级平均分公式=IF(COUNTIF($C$2:$C$24,J26)=0,"",SUMIF($C$2:$C$24,J26,$D$2)/COUNTIF($C$2:$C$24,J26)) 四、Excel表中身份证号码提取出生年月、性别、年龄的使用技巧 如何从Excel表中的身份证号码中提取出生年月、性别、年龄呢？看了本文就知道了。 方法一： 1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数); 2.15位身份证号从第7位到第12位是出生年月日，年份用的是2位数。 18位身份证号从第7位到第14位是出生的年月日，年份用的是4位数。 从身份证号码中提取出表示出生年、月、日的数字，用文本函数MID()可以达到目的。MID()——从指定位置开始提取指定个数的字符（从左向右）。 对一个身份证号码是15位或是18位进行判断，用逻辑判断函数IF()和字符个数计算函数LEN()辅助使用可以完成。综合上述分析，可以通过下述操作，完成形如1978-12-24样式的出生年月日自动提取： 假如身份证号数据在A1单元格，在B1单元格中编辑公式 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2),MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2))回车确认即可。 如果只要“年-月”格式，公式可以修改为 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2),MID(A1,7,4)&"-"&MID(A1,11,2)) 3.这是根据身份证号码（15位和18位通用）自动提取性别的自编公式，供需要的朋友参考： 说明：公式中的B2是身份证号 根据身份证号码求性别： =IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,1)),2)=0,"女","男"),"身份证错")) 根据身份证号码求年龄： =IF(LEN(B2)=15,2007-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2007-VALUE(MID(B2,7,4)),"身份证错")) 4.Excel表中用Year\Month\Day函数取相应的年月日数据; 方法二： 这是根据身份证号码（15位和18位通用）自动提取性别和出生年月的自编公式，供需要的网友参考： 说明：公式中的B2是身份证号 1、根据身份证号码求性别： =IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0," 女","男"),"身份证错")) 2、根据身份证号码求出生年月： =IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2),".",MID(B2,9,2)),IF(LEN(B2)=18,CONCATENATE(MID(B2,7,4),".", MID(B2,11,2)),"身份证错")) 3、根据身份证号码求年龄： =IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),"身份证错")) 在Excel电子表格中，我们需要到达某一单元格，一般是使用鼠标拖动滚动条来进行，但如果数据范围超出一屏幕显示范围或数据行数非常多时，想快速定位到某一单元格可要有点麻烦了。这时候我们可以使用“定位”（快捷键Ctrl G）功能迅速到达想要的单元格。 定位是一种选定单元格的方式，主要用来选定“位置相对无规则但条件有规则的单元格或区域”。 例1：需要选中Y2009单元格（或快速移动到Y2009单元格），我们可以使用“编辑/定位”菜单，在引用位置里输入“Y2009”后按回车即可。 例2：需要选中Y列的2004～2009行的单元格，我们按照相同的方法，在引用位置里输入“Y2004:Y2009”按回车即可。 例3：需要选中2009行的单元格，我们可以在引用位置里输入“2009:2009”按回车即可。 例4：需要选中2004～2009行的单元格，我们可以在引用位置里输入“2004:2009”按回车即可。 这是“定位”功能中最简单的“引用位置”。 下面来看看“定位”的几个应用（“定位条件”）： 应用一：定位“公式” 在实际工作中，我们会设许多公式，为防止一不小心修改了这些公式，我们会把公式单元格字体设置为其他颜色，一个个去设置当然不符合高效办公的原则。定位就算帮上大忙了。操作步骤如下： 1、编辑/定位/定位条件； 2、选择“公式”/确定，所有的公式单元格被选中； 3、格式/单元格/字体/颜色“红色”/确定； 应用二：定位“空值”，定位“常量”