Excel2007函数公式收集了688个实例14779.pdf

《Excel2007函数公式收集了688个实例14779.pdf》由会员分享，可在线阅读，更多相关《Excel2007函数公式收集了688个实例14779.pdf（43页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Excel2007 函数公式收集了688 个 实例，涉及到137 个函数、7 个行业、41 类用途，为大家提供一个参考，拓展思路的机会。公式由包括的为数组公式，在复制粘贴到单元后先去掉然后按住Shift 键+Ctrl 键再按Enter 键，自动生成数组公式。对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7)对生产表中大于100 的产量进行求和:=SUM(B2:B11100)*B2:B11)对生产表大于110或者小于100的数据求和:=SUM(B2:B11110)*B2:B11)对一车间男性职工的工资求和:=SUM(B2:B10=一车间)*(C2:C10=男)*D2:D10)对姓

2、赵的女职工工资求和:=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,

3、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,&LARGE(B2:B10,4)+SUMIF(B2:B10,600)只汇总60 80 分的成绩:=SUMIFS(B2:B10,B2:B10,=60,B2:B10,25)*1)汇总一班人员获奖次数:=SUMPRODUCT(B2:B11=一班)*C2:C11)汇总一车间男性参保人

4、数:=SUMPRODUCT(A2:A10&B2:B10&C2:C10=一车间男是)*1)汇总所有车间人员工资:=SUMPRODUCT(-NOT(ISERROR(FIND(车间,A2:A10),C2:C10)汇总业务员业绩:=SUMPRODUCT(B2:B11=江西,广东)*(C2:C11=男)*D2:D11)根据直角三角形之勾、股求其弦长:=POWER(SUMSQ(B1,B2),1/2)计算A1：A10区域正数的平方和:=SUMSQ(IF(A1:A100,A1:A10)根据二边长判断三角形是否为直角三角形:=CHOOSE(SUMSQ(MAX(B1:B3)=SUMSQ(LARGE(B1:B3,2

5、,3)+1,非直角,直角)计算1 到 10的自然数的积:=FACT(10)计算50到 60之间的整数相乘的结果:=FACT(60)/FACT(49)计算1 到 15之间奇数相乘的结果:=FACTDOUBLE(15)计算每小时生产产值:=PRODUCT(C2:E2)根据三边求普通三角形面积:=(PRODUCT(SUM(B1:B3)/2,SUM(B1:B3)/2-LARGE(B1:B3,1,2,3)0.5 根据直角三角形三边求三角形面积:=PRODUCT(LARGE(B1:B3,2,3)/2 跨表求积:=PRODUCT(产量表:单价表!B2)求不同单价下的利润:=MMULT(B2:B10,G2:H

6、2)*25%制作中文九九乘法表:=COLUMN()&*&ROW()&=&MMULT(ROW(),COLUMN()计算车间盈亏:=SUM(MMULT(B3:E50)*B3:E5,1;1;1;1),MMULT(B3:E5=TRANSPOSE(ROW(2:11),B2:B11)计算每日库存数:=MMULT(N(ROW(2:11)=TRANSPOSE(ROW(2:11),B2:B11-C2:C11)计算A产品每日库存数:=MMULT(N(ROW(2:17)=TRANSPOSE(ROW(2:17),(B2:B17=A)*(C2:C17-D2:D17)求第一名人员最多有几次:=MAX(MMULT(N(B2

7、:B7=TRANSPOSE(B2:B7),ROW(2:7)0)求几号选手选票最多:=RIGHT(MAX(MMULT(N(B2:B10=TRANSPOSE(B2:B10),ROW(2:10)0)*100+B2:B10)总共有几个选手参选:=SUM(1/(MMULT(N(B2:B10=TRANSPOSE(B2:B10),ROW(2:10)0)在不同班级有同名前提下计算学生人数:=SUM(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17),ROW(2:17)0)计算前进中学参赛人数:=SUM(IFERROR(1/MMULT(N

8、(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17)*(A2:A17=前进中学),ROW(2:17)0),0)串 联 单 元 格 中 的 数 字:=MMULT(10(COLUMNS(B:K)-COLUMN(C:L),TRANSPOSE(B2:K2)或=SUMPRODUCT(B2:K2,10(COLUMNS(B:K)-COLUMN(B:K)-1)计算达标率:=MMULT(TRANSPOSE(N(A2:A1160)*(B2:B1160)*(B2:B1180),ROW(2:11)0)汇总A组男职工的工资:=MMULT(TRANSPOSE(N(B2

9、:B11&C2:C11=男A组)*D2:D11),ROW(2:11)0)计算象棋比赛对局次数l:=COMBIN(B1,B2)计算五项比赛对局总次数:=SUM(COMBIN(B2:B5,2)预计所有赛事完成的时间:=COMBIN(B1,B2)*B3/B4/60 计算英文字母区分大小写做密码的组数:=PERMUT(B1*2,B2)计算中奖率:=TEXT(1/PERMUT(B1,B2),0.00%)计算最大公约数:=GCD(B1:B5)计算最小公倍数:=LCM(B1:B5)计算余数:=MOD(A2,B2)汇总奇数行数据:=SUMPRODUCT(MOD(ROW(2:13),2)*C2:C13)根据单价

10、数量汇总金额:=SUMPRODUCT(MOD(COLUMN(A:I),2)*A2:I2,(MOD(COLUMN(B:J),2)=0)*B2:J2)设计工资条:=IF(MOD(ROW(),3)=1,单行表头工资明细!A$1,IF(MOD(ROW(),3)=2,OFFSET(单行表头工资明细!A$1,ROW()/3+1,0),)根据身份证号计算性别:=IF(MOD(MID(B2,15,3),2),男,女)每隔4 行合计产值:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,4,),D2*E2)工资截尾取整:=B2+MOD(一月!B2,10)-MOD(B2+MOD(一月!B2,

11、10),10)汇总3 的倍数列的数据:=SUM(IF(MOD(COLUMN(A:I),3)=0,A2:I10)将数值逐位相加成一位数:=IF(A2=0,0,MOD(A2-1,9)+1)计算零钞:5角=INT(MOD(SUM(B2:B10),1)/0.5);2角=INT(MOD(MOD(SUM(B2:B10),1),0.5)/0.2);1角=MOD(MOD(MOD(SUM(B2:B10),1),0.5),0.2)/0.1 秒与小时、分钟的换算:=QUOTIENT(MOD($A2,IF(COLUMN()=2,A2+1,60(3-COLUMN(A:A)+1),60(3-COLUMN(A:A)生成隔行

12、累加的序列:=QUOTIENT(ROW()+1,2)根据业绩计算业务员奖金:=CHOOSE(MIN(QUOTIENT(B2,10000)+1,6),0,3%,5%,7%,9%,11%)*B2 计算预报温度与实际温度的最大误差值:=MAX(ABS(C2:C8-B2:B8)计算个人所得税:=ROUND(0.05*SUM(H2-1600-0,500,2000,5000,20000,40000,60000,80000,100000+ABS(H2-1600-0,500,2000,5000,20000,40000,60000,80000,100000)/2,0)产生100 到 200 之间带小数的随机数:

13、=RAND()*(200-100)+100 产生ll 到 20 之间的不重复随机整数:=RANK(A2:A11,A2:A11)+10 将 20 个学生的考位随机排列:=INDEX(A$2:A$11,RANK(H2:H11,H2:H11)将三个学校植树人员随机分组:=OFFSET(A$1,RANK(G2,G$2:G$11),)&:&OFFSET(B$1,RANK(G2,G$2:G$11),)&:&OFFSET(C$1,RANK(G2,G$2:G$11),)产生-50 到 100 之间的随机整数:=RANDBETWEEN(-50,100)产生1到100之问的奇数随机数:=INDEX(IF(MOD(

14、ROW(1:100),2),ROW(1:100),ROW(1:100)-1),RANDBETWEEN(1,100)产生1到10之间随机不重复数:=LARGE(IF(COUNTIF(A$1:A1,ROW($1:$10)=0,ROW($1:$10),RANDBETWEEN(1,12-ROW()根据三角形三边长求证三角形是直角三角形:=IF(POWER(MAX(B1:B3),2)=SUM(POWER(LARGE(B1:B3,2,3),2),是,不是)计算Al:A10 区域开三次方之平均值:=AVERAGE(POWER(A1:A10,1/30)计算Al:A10 区域倒数之积:=PRODUCT(POWE

15、R(A1:A10,-1)根据等边三角形周长计算面积:=SQRT(B1/2*POWER(B1/2-B1/3,3)抽取奇数行姓名:=INDEX(B:B,ODD(RANDBETWEEN(1,ROWS(1:12)-1)统计A1:B10 区域中奇数个数:=SUMPRODUCT(N(ODD(A1:B10)=(A1:B10)统计参考人数:=SUMPRODUCT(EVEN(COLUMN(A1:J12)=COLUMN(A1:J12)*(MOD(ROW(A1:J12),3)=1)*(A1:J12)计算A1:B10 区域中偶数个数:=SUMPRODUCT(N(EVEN(A1:B10)=(A1:B10)合计购物金额、

16、保留一位小数:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),1)将每项购物金额保留一位小数再合计:=SUMPRODUCT(TRUNC(B2:B10*C2:C10,1)将金额进行四舍六入五单双:=IF(A2-TRUNC(A2,1)=0.06,TRUNC(A2,1)+0.1,TRUNC(TRUNC(A2,1)+0.1)/2,1)*2)根据重量单价计算金额，结果以万为单位:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),-4)/10000 计算年假天数:=TRUNC(TODAY()-B2)*(TODAY()-B2)=365)/365*5)根据上机时间计算上网费用

17、:=(TRUNC(B2)+(B2-TRUNC(B2)=0.5)*1.5+(MOD(B2,1)0)成绩表转换:=INDEX($A:$E,CEILING(ROW()*3/5,3)-(COLUMN()=7),MOD(ROW(B2)-1,5)+1)计算机上网费用:=CEILING(B2,30)/30*2 统计可组建的球队总数:=SUMPRODUCT(FLOOR(B2:B10,5)/5)统计业务员提成金额，不足20000 元忽略:=FLOOR(B2,20000)/20000*500 FLOOR 函数处理正负数混合区域:=FLOOR(A1*100,10*(IF(A10,1,-10)将数据转换成接近6 的倍

18、数:=MROUND(A1,6)以超产80 为单位计算超产奖:=SUM(MROUND(B2:B11-700,80*IF(B2:B11=700,1,-1)/80*50 将统计金额保留到分位:=ROUND(SUMPRODUCT(B2:B10,C2:C10),2)将统计金额转换成以万元为单位:=ROUND(SUMPRODUCT(B2:B10,C2:C10)%,)对单价计量单位不同的品名汇总金额:=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10=G,1000,1),(D2:D10=G)*2)将金额保留“角”位，忽略“分”位:=SUM(ROUNDDOWN(B2:B10*C2:C10,1

19、)计算需要多少零钞:=SUM(ROUNDDOWN(B2:B10*C2:C10,0,-1)*1,-1)计算值为l 万的整数倍数的数据个数:=SUM(N(B2:B10*C2:C10)=ROUNDDOWN(B2:B10*C2:C10,-4)计算完成工程需求人数:=SUM(ROUNDUP(B2:B11/C2:C11,)按需求对成绩进行分类汇总:=SUBTOTAL(HLOOKUP(G$1,平均成绩,科目数量,最高成绩,最低成绩,成绩合计;1,2,4,5,9,2,0),B2:D2)不间断的序号:=SUBTOTAL(103,$B$2:B2)仅对筛选出的人员排名次:=CONCATENATE(第,SUM(N(I

20、F(SUBTOTAL(103,OFFSET(优等生!A$1,ROW($2:$31)-2,)=1,$C$2:$C$31,)C2)+1,名)判断两列数据是否相等:计算两列数据同行相等的个数:=SUM(N(A1:A10=B1:B10)计算同行相等且长度为3 的个数:=SUM(A1:A10=B1:B10)*(LEN(A1:A10)=3)提取A 产品最后单价:=INDEX(C:C,MAX(B2:B10=A)*ROW(2:10)判断学生是否符合奖学金发放条件:=AND(B290,C2汉族)所有裁判都给“通过”就进入决赛:=AND(B2:E2=通过)判断身份证长度是否正确:=OR(LEN(B2)=15,18

21、)判断歌手是否被淘汰:=OR(B2:E2=不通过)根据年龄判断职工是否退休:=OR(AND(B2=男,C260),AND(B2=女,C255)根据年龄与职务判断职工是否退休:=OR(AND(B2=男,D260+(C2=干部)*3),AND(B2=女,D255+(C2=干部)*3)没有任何裁判给“不通过”就进行决赛:=NOT(OR(B2:E2=不通过)计彝成绩区域数字个数:=SUM(NOT(ISERROR(NOT(B2:B11)*1)评定学生成绩是否及格:=IF(AVERAGE(B2:D2)=60,及格,不及格)根据学生成绩自动产生评语:=IF(AVERAGE(B2:D2)60,不及格,IF(A

22、VERAGE(B2:D2)90,良好,IF(AVERAGE(B2:D2)80000,1000,500)根据工作时间计算12 月工资:=C2+SUM(IF(B20,1,3,5,10,300,500,500,500,500)合计区域的值并忽略错误值:=SUM(IF(ISERROR(A1:C10),0,A1:C10)既求积也求和:=IF(D2,PRODUCT(C2:D2),SUM(OFFSET(E2,-3,3)分别统计收入和支出:收入=SUM(IF(B2:B130,B2:B13);支出=SUM(IF(SUBSTITUTE(IF(B2:B13,B2:B13,0),负,-)*1COUNT(B$2:B$1

23、1),LARGE(B$2:B$11,ROW(A1)排除空值:=INDEX($A:$B,SMALL(IF($B$1:$B$11,ROW($1:$11),ROWS($1:$11)+1),ROW(),COLUMN(B2)&有选择地汇总数据:=SUM(IF(A2:A11=A 组,C 组,C2:C11)混合单价求金额合计:=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10=K,1000,1),2)计算异常停机时间:=SUM(SUBSTITUTE(SUBSTITUTE(IF(C2:C11,C2:C11,0),修机,),换原料,)*1)计算最大数字行与文本行:=MAX(IF(B:B,ROW

24、(A:A)找出谁夺冠次数最多:=INDEX(B:B,MIN(IF(MAX(COUNTIF(B2:B12,B2:B12)=COUNTIF(B2:B12,B2:B12),ROW(2:12)将全角字符转换为半角:=ASC(A2)计算汉字全角半角混合字符串中的字母个数:=LEN(ASC(A2)*2-LENB(ASC(A2)将半角字符转换成全角显示:=WIDECHAR(A2)计算混合字符串中汉字个数:=LEN(A2)-(LENB(WIDECHAR(A2)-LENB(ASC(A2)判断单元格首字符是否为字母:=OR(AND(CODE(A2)64,CODE(A2)96,CODE(A2)47)*(CODE(M

25、ID(A2,ROW(INDIRECT(1:&LEN(A2),1)64)*(CODE(UPPER(MID(A2,ROW(INDIRECT(1:&LEN(A2),1)91)产生大、小写字母A 到 Z 的序列:大写字母=CHAR(ROW(A65)，小写字母=CHAR(ROW(A65)+32)产生大写字母A 到 ZZ 的字母序列:=IF(ROW()26,CHAR(MOD(ROW()-1,26)+65),)产生三个字母组成的随机字符串:=CHAR(RANDBETWEEN(65,90)&CHAR(RANDBETWEEN(65,90)&CHAR(RANDBETWEEN(65,90)用公式产生换行符:=A2&

26、CHAR(10)&B2 将数字转换成英文字符:字符码=RANDBETWEEN(1,100)，升序位置=CHAR(MOD(A1-1,26)+65)将字母升序排序:=CHAR(SMALL(CODE(A$2:A$13),ROW(A1)返回自动换行单元格的第二行数据:=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2)根据身份证号码提取出生年月日:=CONCATENATE(MID(B2,7,4-2*(LEN(B2)=15),年,MID(B2,11-2*(LEN(B2)=15),2),月,MID(B2,13-2*(LEN(B2)=15),2),日 )计算平均成绩及评判是否及格:=CON

27、CATENATE(INT(AVERAGE(B2:D2),:,IF(AVERAGE(B2:D2)=60,不),及格)提取前三名人员姓名:=CONCATENATE(LOOKUP(0,0/(B2:B11=LARGE(B2:B11,1),A2:A11),|,LOOKUP(0,0/(B2:B11=LARGE(B2:B11,2),A2:A11),|,(LOOKUP(0,0/(B2:B11=LARGE(B2:B11,3),A2:A11)将单词转换成首字母大写:=PROPER(A2)将所有单词转换成小写形式:=LOWER(A2)将所有句子转换成首字母大写其余小写:=CONCATENATE(PROPER(LEF

28、T(A2),LOWER(RIGHT(A2,LEN(A2)-1)将所有字母转换成大写形式:=UPPER(A2)计算字符串中英文字母个数:=SUM(N(NOT(EXACT(UPPER(MID(A2,ROW(INDIRECT(1:&LEN(A2),1),LOWER(MID(A2,ROW(INDIRECT(1:&LEN(A2),1)计算字符串中单词个数:=SUM(N(EXACT(TRIM(MID(UPPER(A2),ROW(INDIRECT(1:&LEN(A2),1),MID(PROPER(A2),ROW(INDIRECT(1:&LEN(A2),1)将文本型数字转换成数值:=SUM(VALUE(B2:

29、B10)计算字符串中的数字个数:=SUMPRODUCT(N(ISNUMBER(VALUE(MID(A2,ROW($1:$100),1)*1)提取混合字符串中的数字:=MAX(IFERROR(VALUE(MID(A2,MIN(FIND(0;1;2;3;4;5;6;7;8;9,A2&1234567890),ROW(INDIRECT(1:&LEN(A2),0)串联区域中的文本:=CONCATENATE(T(A2),T(B2),T(C2)给公式添加运算说明:=CONCATENATE(你好,B2,2008)&T(N(公式含义:连接“你好”和单元格B2、“2008”)根据身份证号码判断性别:=TEXT(M

30、OD(MID(B2,15,3),2),=1男;=0女)将所有数据转换成保留两位小数再求和:=SUM(-TEXT(B2:B11*C2:C11,0.00)将货款显示为“万元”为单位:=TEXT(B2,￥#&.&#,万元)根据身份证号码计算出生日期:=IF(LEN(B2)=15,19,)&TEXT(MID(B2,7,8-(LEN(B2)=15)*2),#年 00 月 00 日)显示今天的英文日期及星期:=资料日期：&TEXT(TODAY(),dddd,mmmm dd,yyyy)显示今天每项工程的预计完成时间:=TEXT(SUM(08:00,B$2:B2),h:mm:ss 上午/下午)统计A 列有多少

31、个星期日:=SUM(N(TEXT(A1:A11,aaa)=日)将数据显示为小数点对齐:=TEXT(B2,#.0?)计算A 列的日期有几个属于第二季度:=SUM(-(TEXT(A1:A11,m)3,6)*1,-1)在 A 列产生1 到 12 月的英文月份名:=TEXT(ROW()&-1,mmmm)将日期显示为中文大写:=TEXT(2008-8-10,DBNum2yyyy 年 m 月 d 日)将数字金额显示为人民币大写:=IF(MOD(B2,1)=0,TEXT(INT(B2),dbnum2G/通用格式元整;负 dbnum2G/通用格式元整;零元整;),IF(B20,负)&TEXT(INT(ABS(

32、B2),dbnum2G/通用格式元;)&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(FIXED(B2),2),dbnum20 角 0分;),零角,IF(ABS(B2)0,零),零分,)判断单元格的数据类型:=TEXT(A2,大于;小于;文本)计算达成率，以不同格式显示:=TEXT(B2/800,=10.0 倍;00.00%;)计算字母“A”的首次出现位置，忽略大小写:=TEXT(SEARCH(a,A2&a),&LEN(A2)&没找到;第&SEARCH(a,A2&a)&个)从身份证号码中提取表示性别的数字:=MID(B2,TEXT(LEN(B2),=1515;17),1)将

33、三列数据交换位置:=TEXT(1,-1,0,C1:C5&;&!&B1:B5&;&A1:A5)计算年终奖:=TEXT(B2,315!0!0;11!0!0!0;5!0!0;)计算星期日完工的工程个数:=COUNT(TEXT(B2:B10+C2:C10-1,AAA)=日)0)计算本月星期日的个数:=SUM(N(TEXT(TODAY()-TEXT(TODAY(),d)+ROW(INDIRECT(1:&DAY(DATE(,TEXT(TODAY(),m)+1,),AAA)=日)检验日期是否升序排列:=TEXT(N(A3=A2),;日期有误;)判断单元格中首字符的类型:=TEXT(IF(AND(CODE(U

34、PPER(A3)64,CODE(UPPER(A3)0,REPT(,5)&REPT(,ABS(B2)&B2&REPT(,5-ABS(B2),REPT(,5-ABS(B2)-LEN(B2)/2)&B2&REPT(,ABS(B2)&REPT(,5)利用公式制作带轴的图表且标示升降:=IF(A2,A2&,)&IF(A2=,REPT(,(MAX(ABS(B$2:B$8)+6)*2),IF(B20,REPT(,4+MAX(ABS(B$2:B$8)&IF(ROW()=2,IF(B2=OFFSET(B2,-1,0),IF(B2OFFSET(B2,-1,0),)&REPT(,ABS(B2)&B2&REPT(,4

35、+MAX(ABS(B$2:B$8)-ABS(B2),REPT(,4+MAX(ABS(B$2:B$8)-ABS(B2)-LEN(B2)/2)&B2&REPT(,ABS(B2)&IF(ROW()=1,IF(B2=OFFSET(B2,-1,0),IF(B2OFFSET(B2,-1,0),)&REPT(,4+MAX(ABS(B$2:B$8)计算单元格中数字个数:=LEN(A2)*2-LENB(A2)将数字倒序排列:=TEXT(SUM(MID(A2,ROW(INDIRECT(1:&LEN(A2),1)*10(ROW(INDIRECT(1:&LEN(A2)-1),REPT(0,LEN(A2)计算购物金额中

36、小数位数最多是几:=MAX(LEN(B2:B10*C2:C10)-LEN(INT(B2:B10*C2:C10)-1 计算英文句子中有几个单词:=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(A2,),)+1 将英文句子规范化:=PROPER(LEFT(A2)&TRIM(RIGHT(A2,LEN(A2)-1)分别提取省市县名:=TRIM(MID(SUBSTITUTE($A2,/,REPT(,100),COLUMN(A2)*100-99,100)提取英文名字:=LEFT(A2,FIND(,A2)-1)将分数转换成小数:=(LEFT(A2,FIND(/,A2)-1)+RIGHT

37、(A2,LEN(A2)-FIND(/,A2)/2 从英文短句中提取每一个单词:=IFERROR(MID($A2,FIND(,SUBSTITUTE(&$A2&,COLUMN(A2),FIND(,SUBSTITUTE(&$A2&,COLUMN(B2)-FIND(,SUBSTITUTE(&$A2&,COLUMN(A2),)将单位为“双”与“片”混合的数量汇总:=SUM(IF(ISNUMBER(FIND(/,C2:C9),(LEFT(C2:C9,FIND(/,C2:C9)-1)+RIGHT(C2:C9,LEN(C2:C9)-FIND(/,C2:C9)/2,C2:C9*IF(B2:B9=片,0.5,1)

38、提取工作表名:=RIGHT(CELL(filename),LEN(CELL(filename)-FIND(,CELL(filename)根据产品规格计算产品体积:=PRODUCT(LEFT(B2,FIND(*,B2)-1),MID(B2,FIND(*,B2)+1,FIND(*,B2,FIND(*,B2)+1)-1-FIND(*,B2),RIGHT(B2,LEN(B2)-FIND(*,B2,FIND(*,B2)+1)提取括号中的字符串:=IFERROR(MID(A2,FIND(,A2)+1,FIND(),A2)-FIND(,A2)-1),)分别提取长、宽、高:=MID($B2,FIND(,SUB

39、STITUTE($B2,（,COLUMN(A1)+1,FIND(,SUBSTITUTE($B2,）,COLUMN(A1)-FIND(,SUBSTITUTE($B2,（,COLUMN(A1)-1)提取学校与医院地址:=IF(OR(IFERROR(FIND(学校,医院,A2),FALSE),A2,)计算密码字符串中字符个数:=COUNT(FIND(CHAR(ROW(65:90),A2),FIND(CHAR(ROW(97:122),A2),FIND(ROW(1:10)-1,A2)通讯录单列转三列:=MID(INDEX($A:$A,SMALL(IF(IFERROR(FIND(C$1,$A$1:$A$1

40、5),FALSE),ROW($1:$15),100000),ROW(A1),LEN(C$1)+1,100)将 15 位身份证号码升级为18位:=IF(LEN(B2)=18,B2,LEFT(REPLACE(B2,7,19),17)&MID(10X98765432,MOD(SUM(MID(REPLACE(B2,7,19),ROW(INDIRECT(1:17),1)*2(18-ROW(INDIRECT(1:17),11)+1,1)将产品型号规范化:=IF(MID(A2,5,2)=00,A2,REPLACE(A2,5,00)求最大时间:=TEXT(MAX(-TEXT(REPLACE(LEFT(A2:A

41、7,7),5,1,RIGHT(A2:A7,2),00!:00 00-00),hmm/dd/mm)分别提取小时、分钟、秒:=REPLACE(REPLACE($A$1&$A2,FIND(B$1,$A$1&$A2),100,),1,FIND(A$1,$A$1&$A2)+1,)将年级或者专业与班级名称分开:=REPLACE(A2,MAX(IFERROR(SEARCH(CHAR(ROW($65:$90),A2),0),10,)提取各软件的版本号:=REPLACE(REPLACE(A2,1,SEARCH(,A2),),LEN(REPLACE(A2,1,SEARCH(,A2),),1,)店名分类:=IF(C

42、OUNT(SEARCH(小吃,酒吧,茶,咖啡,电影,休闲,网吧,A2)=1,餐饮娱乐,IF(COUNT(SEARCH(干洗,医院,药,茶,蛋糕,面包,物流,驾校,开锁,家政,装饰,搬家,维修,中介,卫生,旅馆,A2)=1,便民服务,IF(COUNT(SEARCH(游乐场,旅行社,旅游,A2)=1,旅游)查找编号中重复出现的数字:重复数字个数=COUNT(SEARCH(ROW($1:$10)-1)&*&(ROW($1:$10)-1),A2);重复字符=IF(COUNT(SEARCH(0*0,A2),0,)&SUBSTITUTE(SUMPRODUCT(ISNUMBER(SEARCH(ROW($1:

43、$9)&*&ROW($1:$9),A2)*ROW($1:$9)*10(9-ROW($1:$9),0,)统计名为“刘星”者人数:=COUNT(SEARCH(?刘星,A2:A9)剔除多余的省名:=SUBSTITUTE(A2,IF(ISERROR(SEARCH(重庆市,A2),四川省),)将日期规范化再求差:=SUBSTITUTE(C2,.,-)-SUBSTITUTE(B2,.,-)提取两个符号之间的字符串:=TRIM(MID(SUBSTITUTE(B2,*,REPT(,50),FIND(*,B2),100)产品规格格式转换:=SUBSTITUTE(SUBSTITUTE(A2,：,(),*,)*)&

44、)判断调色配方中是否包含色粉“B”:=LEN(SUBSTITUTE(B2,B,)LEN(B2)提取姓名与省份:=TRIM(MID(A2,1,FIND(|,A2)-1)&MID(SUBSTITUTE(A2,|,REPT(,100),500,100)将 IP 地址规范化:=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(.&A2,.0,.),.0,.),.,1)提取最后一次短跑成绩:=REPLACE(A2,1,FIND(,SUBSTITUTE(A2,|,LEN(A2)-LEN(SUBSTITUTE(A2,|,),)从地址中提取省名:=LEFT(A2,FIND(省,A2)计算小学参

45、赛者人数:=COUNT(0/(LEFT(B2:B11)=小)计算四川方向飞机票总价:=SUMPRODUCT(N(LEFT(A2:A11,2)=四川),N(B2:B11=飞机),C2:C11)通过身份证号码计算年龄:=TEXT(TODAY(),YYYY)-(IF(LEN(B2)=18,19)&LEFT(REPLACE(B2,1,6,),2+(LEN(B2)=18)*2)从混合字符串中取重量:=LOOKUP(9E+307,-LEFT(B2,ROW($1:$10)*C2 将金额分散填充:=LEFT(RIGHT(￥&$A2*100,13-COLUMN()提取成绩并计算平均:=AVERAGE(MID(A

46、2:A7,4,LEN(A2:A7)-3)*1)提取参赛选手姓名:=MID(A2,FIND(：,A2)+1,LEN(A2)从混合字符串中提取金额:=LOOKUP(307,-MID(B2,MIN(FIND(1;2;3;4;5;6;7;8;9,B2&123456789),ROW($1:$99)从卡机数据提取打卡时间:=730-MID(A2,14,4)根据卡机数据判断员工部门:=CHOOSE(MATCH(-RIGHT(A2,3),1,38,14,11,8,21,43,9,28,0),生产部,业务部,总务部,人事部,食堂,保卫部,采购部,送货部,财务部)根据身份证号码统计男性人数:=SUM(MOD(LE

47、FT(RIGHT(B2:B11,1+(LEN(B2:B11)=18),2)从汉字与数字混合字串中提取温度数据:=MAX(IFERROR(-RIGHT(LEFT(B2,LEN(B2)-1),ROW($1:$10),0)将字符串位数统一:=TEXT(RIGHT(A2,LEN(A2)-1),!&LEFT(A2)&REPT(0,MAX(LEN(A$2:A$10)-1)对所有人员按平均分排序:=INDEX(A:A,RIGHT(LARGE(B$2:B$11*1000+ROW($2:$11),ROW()-1),3)取金额的角位与分位叫:=-RIGHT(ROUND(A2*100,),2)从格式不规范的日期中取

48、出日:=TRIM(RIGHT(SUBSTITUTE(A2,.,2),3)计算平均成绩(忽略缺考人员):=ROUND(AVERAGE(B2:B10),2)计算90 分以上的平均成绩:=ROUND(AVERAGE(IF(ISNUMBER(B2:B10)*(B2:B1090),B2:B10),2)计算当前表以外的所有工作表平均值2:=AVERAGE(一班:五班!B:B)计算二车间女职工的平均工资:=AVERAGE(IF(B2:B10=二车间)*(C2:C10=女),D2:D10)计算一车间和三车间女职工的平均工资:=AVERAGE(IF(B2:B10=一车间)+(B2:B10=三车间)*(C2:C1

49、0=女),D2:D10)计算各业务员的平均奖金:=AVERAGE(1500+300*(INT(C2:C11-80000)/10000)计算平均工资(不忽略无薪人员):=ROUND(AVERAGEA(B2:B10),2)计算每人平均出口量:=AVERAGEA(C2:C11=A)*D2:D11)计算平均成绩，成绩空白也计算:=AVERAGEA(B2:B11*1)计算二年级所有人员的平均获奖率:=TEXT(AVERAGEA(IF(LEFT(A2:A10,3)=二年级,B2:B10/C2:C10),0.00%)统计前三名人员的平均成绩:=AVERAGEA(LARGE(B2:B11,1,2,3)求每季度

50、平均支出金额:=AVERAGEIF(B2:B9,支出,C2)计算每个车间大于250 的平均产量:=AVERAGEIF(B2:C11,250)去掉首尾求平均:=AVERAGEIFS(B2:B11,B2:B11,&MIN(B2:B11),B2:B11,=60)/COUNT(B2:B11),0.00%)统计属于餐饮娱乐业的店名个数:=COUNT(SEARCH(小吃,酒吧,茶,咖啡,电影,休闲,网吧,A2:A11)统计各分数段人数:=COUNT(0/(B$2:B$11ROW(A6)*10)*(B$2:B$11=800)/COUNT(B2:B11),0.00)根据毕业学校统计中学学历人数:=COUNTI