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函数：使用该函数可以返回列表或者数据库中的分类汇总。通常利用[数据]—[分类汇总]菜单项可以很容易地创建带有分类汇总的列表。
Function_num
函数返回值Function_num 函数返回值Function_num 函数返回值1
Average
5
Min
9
Sum
2 Count 6 Product 10
Var
3 Counta 7 Stdev
warp
4
max
8
Stdevp
例如某班部分同学的考试成绩如图15，1）显示最低的语文成绩：首先在单元格B9中输入“显示最低的语文成绩”的字样，然后在单元格E9中输入以下公式：“=SUBTOTAL(5,C3:C7)”；2）显示最高的数学成绩：首先在单元格B10中输入“显示最高的数学成绩”的字样，然后在单元格E10中输入以下公式：“=SUBTOTAL(4,D37)”。
20、计算库存量和奖金：假设某公司在月底要根据员工的业绩发放工资并进行产品的库存统计，本例中规定员工的基本工资为600元，奖金按照销售业绩的8%提成，总工资等于基本工资与奖金之和。如图16，1）在工作表中输入相应的数据信息；2）计算“现存库量”：在单元格C15中输入以下公式：“=C14-SUM(C3:C9)”；3）计算“销售业绩”：在单元格G3中输入以下公式：“=SUMPRODUCT(C3:F3,$C$13F$13)”,函数SUMPRODUCT是计
算数组C3：F3与数组$C$13F$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,DA TE(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),SE COND(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),SECON D(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),MIN UTE(C4),SECOND(C4))))”，此时即可计算出所有型号的包裹寄存的分钟数，其公式形式和计算小时数的公式相似，只是将HOUR换成了MINUTE，其判断条件和前面的一样，如果取走时间没有超过寄存时间，分钟数则为“MINUTE(1-TIME(HOUR(C4),MINUTE(C4),SECOND(C4))+TIME(HOUR(D4),MINUTE(D4),SE COND(D4)))”。如果超过了，分钟数则为“MINUTE(TIME(HOUR(D4),MINUTE(D4),SECOND(D4))-TIME(HOUR(C4),MINUTE(C4),SEC OND(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,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$4F$12,3,1))”，此时即可计算出缴纳的所得税；2）在单元格E15中输入以下公式“=$C15-$D15”，此时即可计算出实发工资。
常用电子表格公式 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单元格；
excel公式笔记 一、vlookup 1.查找A列中第一个以”厦门”开头的记录对应B列的值。 =vlookup(H厦门性A:B20) 其中第一个参数为要寻找的文本，第二个参数为一个区域，第二个参数的第一列为要寻找的区域,第三个参数的2表示第二个参数的第二列显示出来,第四个参数的0表示精确查找。 二、countif 1.统计Al:A10区域中型号为" 2.5m*3m"的记录个数。 =countif(Al:A10,,,2.5m~*3m H) 在excel常用函数中,支持通配符的主要有vlookup、hlookup> match> sum讦、courttif、search> searchB,而find、findB> subsitute 不支持通配符。*表示任意字符,？表示单个字符解除字符的通配性。 2.统计Al:A10中不重复数的个数。 =SUMPRODUCT((1/COUNTIF(A1:A10,A1:A10)))
3?求小于60的数据有多少 二count(A2:AKVv6(T) sumproduct 1?求购物总花费,A列表示购买数量月列表示购买单价 =sumproduct(A2:A8,B2:B8) 意思为A2*B2+A3*B3oooo +A8*B8 2.求二班有多少学生学习了数学 =sumproduct((A2:A10=H~ B,,)*(B2:B10=H数学“)) 表示二班的数学有几个 3.求二班数学分数总和 =sumproduct((A2:A10=M Z：B M)*(B2:B10=H数学H)*(C2:C1O)) 4.统计“技术部”考试成绩为0的个数(缺考除外) =sumproduct((B2:B9=H技术部,,)*(E2:E9=0)*(E2:E9o,,H)) excel会将空值看成0,所以在统计成绩为0的考生时，需要把成绩为空的考牛去除。
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日为基准日,按按身份证计算年龄(周岁)的公式
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”。
《Excel中公式与函数的使用》教案 保山中医药高等专科学校孔春丽 教学内容： 1、单元格引用 2、输入和编辑公式 3、插入和使用常用函数 教学目标: １、知识目标: 理解excel中公式与函数、相对地址与绝对地址的概念。 ２、技能目标: 掌握公式、常用函数以及自动求和按钮的使用，并能运用其解决一些实际问题，提高应用能力。 ３、情感目标: 让学生亲身体验excel强大的运算功能，通过系统学习，培养学生科学、严谨的求学态度，和不断探究新知识的欲望。 教学重点与难点： 根据大纲，依据本节课的教学目标，确定本节课的教学重点为： 1、公式的使用 2、excel中相对地址与绝对地址的引用 3、常用函数的使用 教学方法： 1、任务驱动法、设问法 2、自学法、图示说明法、探究法、演示讲解法 3、归纳总结法 教学过程： 一、创设意境，导入新课（2分钟）（讲解法） 导入：excel的主要功能，制作表格，运算和分析数据，可以为决策提供理论依据，前面我们已经讲解了单元格的格式、单元格地址等概念，这一节将一起来完成公式和函数的应用。 新课程：Excel中公式和函数的使用 二、新课教学（总计38分钟）（讲解法、提问法、示范法） 1、单元格的引用（12分钟） 在公式中使用单元格引用的作用是引用一个单元格或一组单元格的内容，这样可以使用工作表不同部分的数据进行所期望的计算。在Excel 2003中，可以使用相对引用，绝对引用及混合引用来表示单元格的位置。所以，在创建的公式中必须正确使用单元格的引用的类型。 （1）单元格的相对引用（5分钟） 下面以计算2门学科的总成绩为例。如下图所示，将D2单元格成为活动单元格，在编辑栏中输入“＝A2+B2+C2”，回车后D2单元格将得到图示的结果。
excel公式计算 Excel计算公式 1. Excel2003最大值，最小值，求和，平均值等 A1写“最大值”，A2公式=MAX(E3:E100) B1写“最小值”，B2公式=MIN(E3:E100) C1写“求和”，C2公式=SUM(E3:E100) D1写“平均值”，D2公式=AVERAGE(E3:E100) 2.在excel表格中不同日期显示不同颜色例如，想再每个月日期为奇数时显示红色，偶数时显示绿色，就是隔天一个颜色，格式----条件格式----公式：=MOD（A1，2）=1，设置单元格格式背景为红色----确定； 3. excel 下拉填充序号 01 02 03 04 05 =TEXT(ROW(A1),"00) 4. EXCEL隔行求和（如从O7单元格开始，到05000，后面每隔16行开始求和）=IF(MOD(ROW()-6,16),"",SUM(OFFSET($O$7,ROW()-22,,16,))) 5. ExcelIf函数有sheet1和sheet2两个页，其中有A，B，C列.A列是人名，B2是语文成绩,B3为数学成绩C为分数. 第一页sheet1已经是填好的表格。现在需要做的是：假如sheet2的A列的人名与sheet1的A列的人名相同，再把“语文成绩”或“数学成绩”输入，则把对应B列的成绩引入到sheet2的C列中来。 =VLOOKUP(A1,SHEET1!A1:C100,2,0) 6. EXCEL里设置时间相加（还是时间）例如在A单元格中输入10：30，在B单元格中输入0：30 - 1 - c输入公式=SUM(A1:B1)C列设置单元格格式为的时间格式或自定义格式h:mm 时间相加，不超过24小时，如果是25就会变成1：00 7. excel函数例如：A1是2011-02-02，在B1显示2012-02-01，如果A1是2011-02-01，就必须显示2012-01-31. B1中输=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)-1) 单元格A1是1*150*250，怎样在B1单元格中显示为1，B2为150，B3为250 数据----分列----分隔符号：*----确定！ 9.在EXCEL里一个格子里面输入DK123+000~002,拖动时000~002,两个数字可以分别以定值增加！="DK123+"&680+(row(a1)-1)*3&"~"&680+row(a1)*3 10. excel在一组数字前全部加一个符号比如一列数字12，23，54，54，65，,,,,我要在全部加个符号A，变成 A12,A23,A54,A54,A65,,,, 假设A列是12,23,34.....，需要在B列完成你所要的结果，那么在B列的第一行的单元格内键入：=CONCATENATE("A",A1) 中，一个百分数，在大于等于100%的情况下，得满分4分，小于50%得0分，但大于等于50%小于100%情况下=IF(A1>1,4,MAX(0,MIN((A1-0.5)*150,20))) 12. excel用函数提取某个符号前的字符串？比如A1=复合PVC-85/1.5，在B1用什么函数提取“/”之前的值，就是提取出“复合PVC-85”？=LEFT(A1,SEARCH("/",A1)-1) 13. EXCEL自动输出某一范围的随机数值,比如A1到E99,全部自己输出1.10-1.99的随机数 =TEXT(RAND()*(1.99-1.10)+1.10,"0.00")*1 14. EXCEL排序例如 326 我想要结果是236 ；353我想要结果是335 - 2 - =MIN(MID(TEXT(B5,"000"),{1,2,3},1)*1)&LARGE(MID(TEXT(B5,"000"),{1,2,3},1)*1,2)&M AX(MID(TEXT(B5,"000"),{1,2,3},1)*1) 15.用excel求和后等于11.5，总是12 一：把单元格拉大点看看是否有变化？二：看看是否设置了小数点位数为0，工具----选项----编辑----自动设置小数点数为0的勾取掉？三：是否你的公式使用了ROUND或者ROUNDUP函数。 16. Excel表格问题：假设表1有1000个数据，表2有5000个数据，怎样才能知道表1中的数据在表2中有没有出现，有哪些数据出现过？假设在A列，B列输入：=IF(COUNTIF(SHEET2!A:A,A1)>0,"有","") 然后对B列进行非空筛选！ 17. EXCEL设置在 A单元格输入30，在B单元格要变成0:30 =INT(A1/60)&":"&MOD(A1,60) 18. Excel判断比如一个表中，A为各分店商品名称，B为库存量，然后想做一个表，由各分店填好后，复制在一个计表中，一个公式就能统计出各种商品的库存总和？ =SUMIF($A:$A,"a*",$B:$B) 19. excel中有3列数字分别表示时分秒，合并到一列里？ =TEXT(A1&":"&B1&":"&C1,"H:MM:SS") 20. EXCEL里，比如：2个，26箱，50根，136台，去掉后面的字，只要数字？=LEFT(A1,LENB(A1)-LEN(A1) 21. excel 三列穿插变一列在EXCEL表中，如 1 2 3 4 5 6 7
Excel常用电子表格公式大全 1、查找重复内容： （格式—条件格式）公式=IF(COUNTIF($D$2:D2,D2)>1,"重复","不重复") 或 =IF(COUNTIF($D$2:D2,D2)>1,"重复","") =COUNTIF($D$2:$D$500,$D2)>=2 2、数据--筛选--高级筛选--输入数据区域--勾选"选择不重复记录"--确定 3、=COUNTIF(D$2:D2,D2)>1这个是提示一列中第二个重复。EXCEL一列内如何区分出重复的文字 问：EXCEL表的客户名录,如果区分是否重复 答：在客户名录后面一列（假设为B列）输入=if(countif($B$1:B1,B1)>1,"重复","不重复") 4、 EXCEL条件格式教程:用条件格式辨别重复数值 在excel2003中，我们会发现辨别一个范围中的重复数值是非常有帮助的。可以使用下面的条件格式公式。在该例子中，格式应用于范围A1:G2中所有非惟一单元格中=COUNTIF($A$1:$G$12,A1)>1 在一个范围中只对非重复数值应用格式，使用下面的公式 =COUNTIF($A$1:$G$12,A1)=1 实例： 将A1:G2中的重复值添加特殊的格式。 1.选择要设置交替行阴影的单元格区域A1:G2，点击菜单栏的“格式”——“条件格式”，将弹出“条件格式”对话框。 2.在“条件格式”对话框中的“条件1”下拉列表框中选择“公式”，然后在公式输入框中输入公式“=COUNTIF($A$1:$G$12,A1)>1”，并设置其格式为蓝色加粗。然后添加“条件2”公式为“=COUNTIF($A$1:$G$12,A1)=1”，并设置其格式为红色加粗。
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单元格； （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,"<60") ——求K2到K56区域60分以下的人数；假设把结果存放于K63单元格； 说明：COUNTIF函数也可计算某一区域男、女生人数。 如：=COUNTIF(C2:C351,"男") ——求C2到C351区域（共350人）男性人数； 9、优秀率：=SUM(K57:K60)/55*100 10、及格率：=SUM(K57:K62)/55*100
excel2003函数公式汇总 excel2003函数公式汇总 ABS 用途：返回某一参数的绝对值。 ACCRINT 用途：返回定期付息有价证券的应计利息。 ACCRINTM 用途：返回到期一次性付息有价证券的应计利息。 ACOSH 用途：返回参数的反双曲余弦值。 ADDRESS 用途：以文字形式返回对工作簿中某一单元格的引用。 AMORDEGRC 用途：返回每个会计期间的折旧值。 AMORLINC 用途：返回每个会计期间的折旧值，该函数为法国会计系统提供。如果某项资产是在会计期间内购入的，则按线性折旧法计算。 ASC 用途：将字符串中的全角(双字节)英文字母更改为半角(单字节)字符。 ASIN 用途：返回参数的反正弦值。 ASINH 用途：返回参数的反双曲正弦值。 ATAN 用途：返回参数的反正切值。返回的数值以弧度表示，大小在-π/2～π/2之间。 ATAN2 用途：返回直角坐标系中给定X及Y的反正切值。它等于X轴与过原点和给定点(x_num，y_num)的`直线之间的夹角，并介于-π～π之间(以弧度表示，不包括-π)。 ATANH 用途：返回参数的反双曲正切值，参数必须在-1～1之间(不包括-1和1)。 AVEDEV 用途：返回一组数据与其平均值的绝对偏差的平均值，该函数可以评测数据(例如学生的某科考试成绩)的离散度。 AVERAGEA 用途：计算参数清单中数值的平均值。它与AVERAGE函数的区别在于不仅数字，而且文本和逻辑值(如TRUE和FALSE)也参与计算。 BETADIST 用途：返回Beta分布累积函数的函数值。Beta分布累积函数通常用于研究样本集合中某些事物的发生和变化情况。例如，人们一天中看电视的时间比率。
电子表格excel公式使用大全详解_3 12、IF函数 函数名称：IF 主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。 运用格式： =IF(Logical,Value_if_true,Value_if_false) 参数说明：Logical代表逻辑判断表达式； Value_if_true表示当判断条件为逻辑“真（TRUE）”时的显示内容，如果忽略返回“TRUE”；Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容，如果忽略返回“FALSE”。 使用举例：在C29单元格中输入公式：=IF(C26>=18,"符合要求","不符合要求")，确信以后，如果C26单元格中的数值大于或等于18，则C29单元格显示“符合要求”字样，反之显示“不符合要求”字样。 特别提醒：本文中类似“在C29单元格中输入公式”中指定的单元格，读者在运用时，并不须要受其约束，此处只是配合本文所附的实例须要而给出的相应单元格，具体请大家参考所附的实例文件。 20、MID函数
函数名称：MID 21、MIN函数 函数名称：MIN 主要功能：求出一组数中的最小值。 运用格式：MIN(number1,number2……) 参数说明：number1,number2……代表须要求最小值的数值或引用单元格（区域），参数不超过30个。 使用举例：输入公式：=MIN(E44:J44,7,8,9,10)，确认后即可显示出E44至J44单元和区域和数值7，8，9，10中的最小值。 特别提醒：如果参数中有文本或逻辑值，则忽略。 们在运用 Excel打造表格整理数据的时候，常常要用到它的函数功能来统计处理表格中的数据。本文以Excel为例（其它版本请仿照操作），向大家介绍一些在Excel中运用频率最高的函数的功能和运用要领。2003 Excel函数系列之后我们还会推出Excel常用函数实例文章系列，欢迎大家关注。 为方便大家阅读，我们按函数名称的字母执行排序。 前面我们介绍了以下函数的功能和用法： ABS、AND、AVERAGE、COLUMN 、CONCATENATE、COUNTIF 、DATE DATEDIF、DAY、DCOUNT、FREQUENCY、IF、INDEX、INT
Excel2003常用函数 1.NOW 用途：返回当前日期和时间所对应的序列号。 语法：NOW() 参数：无 实例：如果正在使用的是1900日期系统，而且计算机的内部时2.TODAY 用途：返回系统当前日期的序列号。 参数：无 语法：TODAY() 间)。 3.CELL 用途：返回某一引用区域的左上角单元格的格式、位置或内容等信息，该函数主要用于保持与其它电子表格程序的兼容性。 语法：CELL(info_type，reference) 参数：Info_type为一个文本值，指明所需要的单元格信息的类型。Reference表示要获取其有关信息的单元格。如果忽略，则在info_type中所指定的信息将返回给最后更改的单元格。实例：公式“=CELL("row"，A20)”返回20，如果A3单元格包含TOTAL，则CELL("contents"，A3)返回"TOTAL"。 4.ISEVEN
用途：测试参数的奇偶性，如果参数为偶数返回TRUE，否则返回FALSE。 语法：ISEVEN(number) Number待测试的数值。如果参数值不是整数，则自动截去小数部分取整。 注意：该函数必须加载“分析工具库”方能使用。如果参数number不是数值，ISEVEN函数返回错误值#VALUE!。 实例：公式“=ISEVEN(11)返回FALSE”，=ISEVEN(6)返回TRUE。 5.ISODD 用途：测试参数的奇偶性，如果参数为奇数返回TRUE，否则返回FALSE。 语法：ISODD(number) 参数：Number待测试的数值。如果参数不是整数，则自动截去小数部分取整。 注意：该函数必须加载“分析工具库”方能使用。 实例：公式“=ISODD(19)”返回TRUE，=ISODD(14.5)返回FALSE。 6.AND 用途：所有参数的逻辑值为真时返回TRUE(真)；只要有一个参数的逻辑值为假，则返回FALSE(假)。 语法：AND(logical1，logical2，…)。 参数：Logical1，logical2，…为待检验的1～30个逻辑表达式，它们的结论或为TRUE(真)或为FALSE(假)。参数必须是逻辑值或
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单元格；
excel表格的函数公式大全(DOC) 这篇教程中将为你介绍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 主要功能：求出所有参数的算术平均值。 使用格式：AVERAGE(number1,number2,……) 参数说明：number1,number2,……：需要求平均值的数值或引用单元格（区域），参数不超过30个。 应用举例：在B8单元格中输入公式：=AVERAGE(B7:D7,F7:H7,7,8)，确认后，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。 特别提醒：如果引用区域中包含“0”值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内。 4、COLUMN 函数 函数名称：COLUMN 主要功能：显示所引用单元格的列标号值。 使用格式：COLUMN(reference) 参数说明：reference为引用的单元格。 应用举例：在C11单元格中输入公式：=COLUMN(B11)，确认后显示为2（即B列）。 特别提醒：如果在B11单元格中输入公式：=COLUMN()，也显示出2；与之相对应的还有一个返回行标号值的函数——ROW(reference)。 5、CONCATENATE函数 函数名称：CONCATENATE 主要功能：将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。 使用格式：CONCATENATE(Text1，Text……)
Excel 2003考题函数汇总（求和、求平均因为简单没有列举） 注意事项： 1、自动套用格式：务必注意不能选择整张工作表； 2、排序操作：务必注意排序内容的选择，特别注意第24题（两年平均招收新生）； 3、工作表引用：工资上涨40%； 4、插入图表：数据源的选择（必须两行或两列）； 5、添加边框线：操作顺序：线条样式、线条颜色、边框线； 01、库存表 H2 = Countif(E2:E11,"<10" ) （答案：3） 02、学生成绩 方法一：F2 = If(Countif(B2:C2,">=90")>=1,"好","须努力") 方法二：F2=IF(OR(B2>=90，C2>=90), "好","须努力") 03、工资表 H2 = Countif(F2:F12,">=2200") （答案：7） 04、四年合计 K2 = Countif(C3:F14,">=650") （答案：29） 05、销售量统计 K2 = Countif ( I3:I15 , ">=600") - Countif ( I3:I15 , ">650") （答案：7） 06、出货单 J2=Sumif(G2:G11,">2000",F2:F11)-Sumif(G2:G11,">=3000",F2:F11) （答案：6238） 07、进货单
A15 = Countif(D2:D11,"<10") （答案：8） 08、流通表 B15 = If(Average(B3:B14)>=200,"畅销","不够畅销") 09、统计表 L2 = Countif(I2:I11,">=8.0") （答案：６） 10、总评 J4=If(I4>=510, "优秀",If(I4>=450, "合格","不合格")) 11、实发工资 K2=Countif(I3:I12,”>=2000”)- Countif(I3:I12,”>2100”) （答案：5） 12、最大值 B12 = Max(B2:B11) 13、档案表 G2=Countif(D2:D11, "已婚") （答案：7） 14、平均情况 B17=If(B16>5, "高","低") 15、进货单2 J2=Sumif(D2:D11, ">=10",F2:F11) （答案：44736） 16、参考工资表 H2=Countif(F2:F15, "<1500") （答案：3） I2=Countif(F2:F15, ">=1500")-Countif(F2:F15, ">=2000") (答案：7）H2=Countif(F2:F15, ">2000") （答案：4）
4.5 数据运算 4.5.1 公式定义及使用 1. 公式输入与编辑 Excel工作表的核心是公式与函数。公式是用户为了减少输入或方便计算而设置的计算式子，它可以对工作表中的数据进行加、减、乘、除等运算。公式可以由值、单元格引用、名称、函数或运算符组成，它可以引用同一个工作表中的其它单元格，同一个工作簿不同工作表中的单元格，或者其它工作簿的工作表中的单元格。使用公式有助于分析工作表中的数据。当改变了工作表内与公式有关的数据，Excel会自动更新计算结果。输入公式的操作类似于输入文本。不同之处在于，输入公式时要以等号（=）或(+)开头。对公式中包含的单元格或单元格区域的引用，可以直接用鼠标拖动进行选定，或单击要引用的单元格输入引用单元格标志或名称，如“＝(C2＋D2＋E2)/3”表示将C2、D2、E2 三个单元格中的数值求和并除以3，把结果放入当前列中。在公式编辑框()中输入和编辑公式十分方便，公式编辑框特别有助于输入工作表函数。 输入公式的步骤如下： ①选定要输入公式的单元格。 ②在单元格中或公式编辑框中输入“=”或“+”。 ③输入设置的公式，按Enter键。 如果公式中含有函数，当输入函数时则可按照以下步骤操作： ①直接输入公式函数名称格式文本，或在“函数”下拉列表框中选中函数名称，即出现公式选项板，选择所用到的函数名，如“SUM()”。 ②输入要引用的单元格或单元格区域，当添加好函数的各个参数后，再输入“)”。 ③单击“确定”按钮。 下面举例来说明公式的使用方法：有如图4-3所示的学生成绩，包括操作填空、单选、判断、单选、多选与填空五门成绩，现在要求计算每个学生的总成绩，最简便的方法就是设置一个公式，即L列=G列＋H列＋I列＋JI列＋K列。设置公式的步骤如下： ①单击L2单元格使其成为活动单元格。 ②在“数据编辑区”，输入公式“=G2+H2+I2+J2+K2”后回车，得到L2的值为86。 ③再将鼠标箭头放在L2的边框右下角的填充柄上，等其显现为“＋”时，按住鼠标左键向下拖动扫过有学生成绩的区域，就可得到所有学生的平均成绩，
电子表格函数公式使用集锦 怎么让excel表格公式只能看不能被改：选定不需要保护的的单元格，单击右键－设置单元格格式—取消锁定—选定需要保护的的单元格，单击右键－设置单元格格式—锁定—工具—保护—保护工作表—输入密码—确定。 一、电子表格中由身份证号自动导出年月日的公式 =IF(LEN(E1)=15,"19"&MID(E1,7,2)&"-"&MID(E1,9,2)&"-"&MID(E1,11 ,2),MID(E1,7,4)&"-"&MID(E1,11,2)&"-"&MID(E1,13,2))转换出生年月如1986-05-23 说明： E：列数 E1890：第E列第1890行。输入身份证号码 LEN（text）:返回本字符串的个数。LEN（“123”）=3 LEN(E1)=15表示如果身份证号码为15个数字 &:表示相加 MID（字符串，M，N）：从该字符串第M位开始，取N位字符。 MID（A1，3，4）=3456，从“A1”单元格中的第“3”位起截取“4”个数 IF（条件表达式，语句1，语句2）：如果条件成立，那么就执行语句1，否则执行语句2 LEFT（A1,14）截取A1单元格前14位数 RIGHT（A1,14）截取A1单元格后14位数 身份证号码有两种，如“352124*********”或“352124************”如果E1是15个,为19加上从第7个开始取2个“86”加上-加上从第9个开始取2个“02”加上-加上从第11个开始取2个“13”合起来为“1986-02-13”，否则为从第7个开始取4个“1986”加上-加上从第11个开始取2个“02”加上-加上从第13个开始取2个“13”合起来为“1986-02-13”。 "19"、"-"为直接写入的数。
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这一区域 “=LCM(B3,C3,D3)”。中3个数字的最小公倍数，可以在E3单元格中输入以下公式：
图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”。
Excel的SUM函数9种公式设置范例 1、数组求和：{=SUM((G12:G21>100)*G12:G21)} [公式说明]：本公式为数组公式，可以对G12:G21区域中大于100的数据进行求和，而排除小于等于100的数据。输入公式时必须按【Ctrl+Shift+Enter】组合键结束，否则无法得到正确结果。 [使用注意]：1、公式中"G12:G21>100"部分表示求和条件，后跟实际求和区域"G12:G21"。如果有多个条件，可一并罗列出来。例如求大于100且小于115的数据之和，公式如下:=SUM(G12:G21>100)*(G12:G21<115)*G12:G21)。2、此数组公式只适用于单个区域求和，如果有多个区域，只能用多个SUM求和，然后相加。例如对G12:G21和H12:H21区域中大于100的数汇总，公式如下:=SUM(SUM((G12:G21>100)*G12:G21),SUM((H12:H21>100)*H12:H21))。3、对于SUM函数的数组公式，可以用SUMPRODUCT函数来代替，从而将数组公式转换成普通公式。例如本案例的公式用SUMPRODUCT函数后，普通公式如下:=SUMPRODUCT((G12:G21>100)*G12:G21)。 2、数据类型转换求和：=SUM(VALUE(H5),H6:H10,J5:J10,L5:L10)；=SUM(--(H5),H6:H10,J5:J10,L5:L10)；=SUM((H5)*1,H6:H10,J5:J10,L5:L10)；=SUM((H5)/1,H6:H10,J5:J10,L5:L10) [公式说明]：SUM函数用于对单元格区域的数据或者逻辑值、表达式进行求和，它有1-255个参数。鉴于本题的特殊性，公式也可以改为"=SUM(区域1)"，函数会忽略区域中的文本。 [使用注意]：1、SUM函数有1-255个参数。参数可以是区域，也可以是表达式。如:=SUM(G5,10*25,G6*8)。2、如果是文本型数字参数，SUM函数是可以直接求和的；如果是引用单元格中的文本型数字(单元格的数字前添加半角单引号“'”),则求和时将忽略。例如G5的值是“'95”，那么以下公式的结果大不同。=SUM(10,"95")—结果为105,=SUM(10,G5)—结果为10。3、如果一定要对单元格中文本型数字进行求和，可以将它转换成数值。转换方法包括多种:利用VALUE函数转换、利用"--"转换和"*1"、"/1"方式转换。例如:=SUM(10,VALUE(G5))、=SUM(10,--(G5))、=SUM(10,(G5)*1) 3、多条件求和1：{=SUM(((G23:G32<100)+(G23:G32>110))*G23:G32)} [公式说明]：对于两个范围求和，即满足两个条件中任意一个条件都进行求和，这和同时满足两个条件才求和的公式思路不同。"案例2"的使用注意"中已提到，多个条件同时满足才求和需要用"*"连接所有条件，表示同时满足多条件，而本案例中对多个条件用"+"来连接，表示对于多条例中满足任意一个条件就可以进行求和。本公式为数组公式。 [使用注意]：1、多条件满足任意条件即求和，在设置公式时对于所有条件需要用"+"连接，如本例中"(G23:G32<100)+(G23:G32>110)",然后对条件用括号括起来再与实际求和区相乘。如