EXCEL2007函数技巧大全

函公式收集了个实例，涉及到个、个类途，大家供一参考拓展路机会。公式{包的数公，复粘到元先掉{然后住Shift键Ctrl键按，自动生成数组公式。对三生数求对产表中大100产量进行和对生产大110者小于数据求和对一车间男性工的工求和车)*(C2:C10="男对姓赵女职工工资求和赵")*(C2:C10="女)*D2:D10)}求前名产之:求所工作相区域数据之=SUM(A组E组!B2:B9)求图订价总{=SUM((B2:E2=参价!考格求前表以外的所有工作表相同区域的总:一:五月用SUM函计数男)*1)}求1累加到之和{=SUM(ROW(1:100))}多个表同域前名产和组组组B2:B9,D组组!B2:B9),ROW(1:3)))}计仓库进库数量之=SUMIF(B2:B10,"=进,C2:C10)计算仓大额进库数量和对1400到之间资{=SUM(SUMIF(B2:B10,"<="&{1400,1600})*{-1,1})}求前三名和后名的数之和对所有间人员的工资和=SUMIF(A2:A10,"?车间",C2)对多个间人员的工资和=SUMIF(A2:A10,"??车间",C2)汇总姓、刘、李的业员提成金=SUM(SUMIF(A2:A10,{"赵,"刘,"李汇鼠标所在列中大的据=SUMIF(INDIRECT("R2C"&CELL("col")&":R8C"&CELL("col"),FALSE),">600")只总60～80分成绩汇总三年级二人员迟次数:=SUMIFS(D2:D10,B2:B10,"三年",C2:C10,"二)汇总间女人:=SUMIFS(C2:C11,A2:A11,"*车女计算车间男性女性人的差:=SUM(SUMIFS(C2:C11,B2:B11,{"女男车计算参人是求岁以男性人男汇一班人员获奖次一汇总一间男性参保人数一车间男是汇所有车间人员工:=SUMPRODUCT(--NOT(ISERROR(FIND("间汇总务员业西广"})*(C2:C11="男")*D2:D11)根据角三形勾、股求其=POWER(SUMSQ(B1,B2),1/2)计算：A10区域正的方{=SUMSQ(IF(A1:A10>0,A1:A10))}根据二边长断三角形是否为直角三角形非直直角计算到的自然的:=FACT(10)计算到之间的数相的结果=FACT(60)/FACT(49)计算到之间奇数的:计算小生产:=PRODUCT(C2:E2)根据三求普通三角形积根据角三形边求三角形:跨表积产表表B2)求不单下利{=MMULT(B2:B10,G2:H2)*25%}制作文九法:=COLUMN()&"*"&ROW()&"="&MMULT(ROW(),COLUMN())计算车盈:计算各组别第名产量多少算产品最入库量{=MAX(MMULT(N(A2:A11="C"),TRANSPOSE((B2:B11)*(A2:A11="C"))))}求库最多的产品数{=MAX(MMULT(TRANSPOSE((B2:B11)*(A2:A11={"A","B","C","D"})),(A2:A11={"A","B","C","D"})*1))}计算计入库:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11)}计算日库存:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11-C2:C11)}计算产品每日库存求第一人员最多有几次求几选选最:总共几选参:在同级同名前提计学人数计前进中学参赛人:")),ROW(2:17)^0),0))}串联元中数:{=MMULT(10^(COLUMNS(B:K)-COLUMN(C:L)),TRANSPOSE(B2:K2))}或算达率{=MMULT(TRANSPOSE(N(A2:A11<=(B2:B11))),ROW(2:11)^0)/ROWS(2:11)}

前进中学计成绩在60-80分之间数数求{，求个数{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)),ROW(2:11)^0)}汇男的资{=MMULT(TRANSPOSE(N(B2:B11&C2:C11="男组)*D2:D11),ROW(2:11)^0)}计象棋比赛对局次数计算五比赛对局总次数预计所赛事完成的时间=COMBIN(B1,B2)*B3/B4/60计英字区分大小做码组数算中率=TEXT(1/PERMUT(B1,B2),"0.00%")计算大公约:=GCD(B1:B5)计算小公倍:=LCM(B1:B5)计算数:=MOD(A2,B2)汇总数行数=SUMPRODUCT(MOD(ROW(2:13),2)*C2:C13)根单价数量汇总金:设计资条单行表头工资明细!A$1,IF(MOD(ROW(),3)=2,OFFSET(单行表头工资细!A$1,ROW()/3+1,0),""))根身份证号计算性:=IF(MOD(MID(B2,15,3),2),"男女")每隔计值:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,,4,)),D2*E2)工资截取:=B2+MOD(一!B2,10)-MOD(B2+MOD(一月总的数的数据将数值位相加成一位数:=IF(A2=0,0,MOD(A2-1,9)+1)计零:5角

角

角秒小时、分钟的换生成行加序:=QUOTIENT(ROW()+1,2)根据业计算业务员奖金=CHOOSE(MIN(QUOTIENT(B2,10000)+1,6),0,3%,5%,7%,9%,11%)*B2计预温与实际温的大差值计算人所得:产100到200带的数:=RAND()*(200-100)+100产生ll到20之间的不重复随机整将个生的考位随机排将个学校植树人员随机

分组=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),)产生-到之间的随:=RANDBETWEEN(-50,100)产生1到100之问的奇数机产1到10之随根三角形三边长求证三角形是直角三角是是)计算区开次之均计算域之{=PRODUCT(POWER(A1:A10,-1))}根据等边三角周长计面积=SQRT(B1/2*POWER(B1/2-B1/3,3))抽取数行姓:=INDEX(B:B,ODD(RANDBETWEEN(1,ROWS(1:12)-1)))计区域中数个数:=SUMPRODUCT(N(ODD(A1:B10)=(A1:B10)))统计参人计算区域中数个数:=SUMPRODUCT(N(EVEN(A1:B10)=(A1:B10)))合计购物金额保留一小数:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),1)将每项物金额保留一小数再合:=SUMPRODUCT(TRUNC(B2:B10*C2:C10,1))将金额进行舍六入五单双)根据重量单价计算金额，结果以万为单=TRUNC(SUMPRODUCT(B2:B10,C2:C10),-4)/10000计算年天:=TRUNC((TODAY()-B2)*((TODAY()-B2)>=365)/365*5)根据上时间计算上网用=(TRUNC(B2)+(B2-TRUNC(B2)>=0.5))*1.5+(MOD(B2,1)<0.5)将金额角进元与见分元见分进元见进=CEILING(TRUNC(A2,1),1)分别统计收支额并忽小数收合计=SUMPRODUCT(INT(B2:B8));支合计=SUMPRODUCT(TRUNC(C2:C8))成绩表的格式转换姓名=INDEX(A:A,INT((ROW(A6))/3));科目=INDEX(B$1:D$1,1,MOD((ROW(A1)-1),3)+1);成绩隔两进行编:=IF(MOD(ROW(),3)=1,INT(ROW(A3)/3),"")

函数序列中复

杂

运用:=INT(SQRT(2*ROW(A1))+0.5);=10^INT((ROW()-1)/2);=INT(10^(ROW())/9);=INT((ROW(A2))*2/3)统计易损金根员工工龄计算年=C2+CEILING(B2*30,30)*(INT(B2)>0)绩表换计算上网费统可组建的球队总:=SUMPRODUCT(FLOOR(B2:B10,5)/5)统计业员提成金额，足20000元:函数处理数区:=FLOOR(A1*100,10*(IF(A1>0,1,-10)))将数转换接的数以产为位计超奖将计金额保留到分:=ROUND(SUMPRODUCT(B2:B10,C2:C10),2)将计金额转换以万元为单:=ROUND(SUMPRODUCT(B2:B10,C2:C10)%%,)对单价量单位不同的名汇总金将金额留“角”位，略“分”:{=SUM(ROUNDDOWN(B2:B10*C2:C10,1))}计算要多零{=SUM(ROUNDDOWN(B2:B10*C2:C10,{0,-1})*{1,-1})}计算为的整数倍数的数据个:{=SUM(N((B2:B10*C2:C10)=ROUNDDOWN(B2:B10*C2:C10,-4)))}计完成工程需求人{=SUM(ROUNDUP(B2:B11/C2:C11,))}按需求对成绩进行分汇总平成科目数量最成"最低成"成合计不间断序=SUBTOTAL(103,$B$2:B2)仅对选出的员排名第优等生!A$1,ROW($2:$31)-2,)))=1,$C$2:$C$31,)>C2))+1,"名判两列数据是否相:计算两列数据行相等个数计同行相等且长度为的数取A产单判断生是符奖学金发放汉)所有判都“过”就进入通判断身证长度是否正确=OR(LEN(B2)={15,18})判断手否淘{=OR(B2:E2="不通"根据年判断职工是否休=OR(AND(B2="男女,C2>55))根据龄与务断职工是否=OR(AND(B2="男,D2>60+(C2="干"女,D2>55+(C2="干部"没任裁给“不通”进决赛不过))}计成绩区域数字个:评学生成绩是否及:及及")生评及良优秀满分根业绩计算需发放多少奖根据作时计算资:=C2+SUM(IF(B2>{0,1,3,5,10},{300,500,500,500,500}))合计区的值并忽略错值:{=SUM(IF(ISERROR(A1:C10),0,A1:C10))}既求积求分统计收和支出收入{=SUM(IF(B2:B13>0,B2:B13))};支出负将成从到排:{=IF(ROW(A1)>COUNT(B$2:B$11),"",LARGE(B$2:B$11,ROW(A1)))}排除值有选地汇数组组混合价金合计算常停时:{=SUM(SUBSTITUTE(SUBSTITUTE(IF(C2:C11<>"",C2:C11,0),"修",""),"换原计算最数字行与文本行:{=MAX(IF(B:B<>"",ROW(A:A)))}找出夺次最将角字符转换为半计汉字全角半角混合字符串中的字母个:=LEN(ASC(A2))*2-LENB(ASC(A2))将半角符转换成全角示:=WIDECHAR(A2)计算混字符串中汉字数:=LEN(A2)-(LENB(WIDECHAR(A2))-LENB(ASC(A2)))判断单元格首符是否字母:=OR(AND(CODE(A2)>64,CODE(A2)<91),AND(CODE(A2)>96,CODE(A2)<123))计算单元格中数字个数计单元格中大写加小写字母个数:{=SUM((CODE(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))>64)*(CODE(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2产生、小字母到的序列大母=CHAR(ROW(A65))，小母=CHAR(ROW(A65)+32)产生大写字母A到的字母列:=IF(ROW()<27,CHAR(MOD(ROW()-1,26)+65),CHAR(65+(ROW()-1)/26-1))&IF(ROW()>26,CHAR(MOD(ROW()-1,26)+65),"")产生三个字母组成的随机字符串用公产生行:=A2&CHAR(10)&B2将字转换成英文字:符=RANDBETWEEN(1,100)，序位置将字升序排:{=CHAR(SMALL(CODE(A$2:A$13),ROW(A1)))}返回动换单格的第二行:=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2))根据身份证号码提取出年月日年",MID(B2,11-2*(LEN(B2)=15),2),"月",MID(B2,13-2*(LEN(B2)=15),2),"日计算平均成绩评判是及格",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)将所有词转换成小写式将有子换成首字大其小写:=CONCATENATE(PROPER(LEFT(A2)),LOWER(RIGHT(A2,LEN(A2)-1)))将所有母转换成大写式计算字串中英文字母数{=SUM(N(NOT(EXACT(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),LOWER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))))))}计字符串中单词个:)))}将文本数字转换成数值:{=SUM(VALUE(B2: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))}串联域中文给公添运说你",B2,"2008")&T(N("公义连接你好”和单元格”根据身证号码判断性别=TEXT(MOD(MID(B2,15,3),2),"[=1]男[=0]")将有据换成保留位数求和将货款显示为万元”单位￥元)根据身份证号计算出日期=IF(LEN(B2)=15,19,"")&TEXT(MID(B2,7,8-(LEN(B2)=15)*2),"#年00月00日显示今的英文日期及期="资日期："&TEXT(TODAY(),"dddd,dd,显示天每工的预计完成午下")统计A列有多少日日将数据示为小数点对齐=TEXT(B2,"#.0????")计A列的日几于季{=SUM((--(TEXT(A1:A11,"m"))>{3,6})*{1,-1})}在A列产1到月的月名=TEXT((ROW())&"-1","mmmm")将期显示为中文大=TEXT("2008-8-10","[DBNum2]yyyy年月日将字金额显示人民币大写通格元;负dbnum2]G/通格整零元整

负通用格式元角分零"零)),"零",""))判单元格的数据类:大eq\o\ac(○,于)小eq\o\ac(○,于)eq\o\ac(○,;)eq\o\ac(○,)文"计算达成率，不同格显示:=TEXT(B2/800,"[>=1]0.0倍计字母“A”的首次出现位置，忽略小写没到第"&SEARCH("a",A2&"a")&"")从身份号码中提取表性别的数:将三数交位算年奖计算星日完工的工程数日)^0)}计本月星期日的个:日))}检日期是否升序排=TEXT(N(A3>=A2),";;日误判断单元格首字符的类=TEXT(IF(AND(CODE(UPPER(A3))>64,CODE(UPPER(A3))<91),CODE(A3),A3),"[="&CODE(A3)&"]字母;;数;汉"计算个度天将据重复显示次？？将表示起止时间的数字格式化为时间格根据起止时间计算经过时间:=TEXT(INT(((TEXT(RIGHT(B4,4),"#!:00")-TEXT(LEFT(B4,3+(LEN(B4)=8)),"#!:00"))*24*60)/60)+MOD(((TEXT(RIGHT(B4,4),"#!:0小分钟将字转化成电话格:=TEXT(A2,"(0000)0000-0000")在区产生星期一到星期日的英全将汇总金额保留位小数并显示千位分隔符计订金并以“百”单显示:=FIXED(SUMPRODUCT(B2:B10,C2:C10),-6)/1000000将据对齐显示将空白以“位利用公式作易图表｜■",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("〓",4+MAX(ABS(B$2:B$8)))&IF(ROW()=2,"→",IF(B2>OFFSET(B2,-1,0),"↑","↓")))&REPT("■",ABS(B2))&B2&REPT("",4+MAX(ABS(B$2:B$8))-ABS(B2)),REPT("■→",IF(B2>OFFSET(B2,-1,0),"↓计单元格中数字个:=LEN(A2)*2-LENB(A2)将数字倒

序

排列计算物金中数位数最多{=MAX(LEN(B2:B10*C2:C10)-LEN(INT(B2:B10*C2:C10)))-1}计算英句子中有几个词",""))+1将英句子范分别取省县提取英名:=LEFT(A2,FIND("",A2)-1)将分转换小:=(LEFT(A2,FIND("/",A2)-1)+RIGHT(A2,LEN(A2)-FIND("/",A2)))/2从英短句中提取每一单:=IFERROR(MID($A2,FIND("~",SUBSTITUTE(""&$A2&"","","",COLUMN(A2)))),"")将单位为“双”与“片”混合的数汇总片提取工表根据产品规格计算产品体积:=PRODUCT(LEFT(B2,FIND("*",B2)-1),MID(B2,FIND("*",B2)+1,FIND("*",B2,FIND("*",B2)+1)-1-FIND("*",B2)),RIGHT(B2,LEN(B提取号的符:=IFERROR(MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1),"")分别提取长、宽、高:=MID($B2,FIND("@",SUBSTITUTE($B2,"（）（提取校医地{=IF(OR(IFERROR(FIND({"学校","院"},A2),FALSE)),A2,"")}计算密字符串中字符数通讯录单列转三列:{=MID(INDEX($A:$A,SMALL(IF(IFERROR(FIND(C$1,$A$1:$A$15),FALSE),ROW($1:$15),100000),ROW(A1))),LEN(C$1)+1,100)}将位身份证号码升级为位将产型号范:最大间{=TEXT(MAX(--TEXT(REPLACE(LEFT(A2:A7,7),5,1,RIGHT(A2:A7,2)),"00!:00分别提小时、分钟、秒=REPLACE(REPLACE($A$1&$A2,FIND(B$1,$A$1&$A2),100,),1,FIND(A$1,$A$1&$A2)+1,)将级或者专业班级名称分:提取软的本=REPLACE(REPLACE(A2,1,SEARCH("(",A2),),LEN(REPLACE(A2,1,SEARCH("(",A2),)),1,)店名分:小","酒"茶咖啡"电","休闲网吧饮娱乐干洗","医","药茶蛋"面,"物","驾","开锁家"装饰搬家维修中介卫","旅"},A2))=1,"便服务,IF(COUNT(SEARCH({"乐"旅社旅"旅游)))查找编中重复出现的数字:重复字个数{=COUNT(SEARCH((ROW($1:$10)-1)&"*"&(ROW($1:$10)-1),A2))};重复字符OW($1:$9)*10^(9-ROW($1:$9))),0,)统计名“刘星”者人数{=COUNT(SEARCH("?刘星剔除余的省:=SUBSTITUTE(A2,IF(ISERROR(SEARCH("重庆"四省),"")将日规化求提取两符号之间的字串=TRIM(MID(SUBSTITUTE(B2,"*",REPT("产品格格转=SUBSTITUTE(SUBSTITUTE(A2,"：判调配中是否包色B”提取名与省=TRIM(MID(A2,1,FIND("|",A2)-1)&MID(SUBSTITUTE(A2,"|",REPT("将址规范化提最后一次短跑成:=REPLACE(A2,1,FIND("々"从地中提省:=LEFT(A2,FIND("省计算学赛人小))}计算四方向飞机票总价四"),N(B2:B11="飞"通过身证号码计算年龄=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将金分散填:￥提取绩计平:{=AVERAGE(MID(A2:A7,4,LEN(A2:A7)-3)*1)}提取赛选姓:=MID(A2,FIND("：从混合符串中提取金额=LOOKUP(307,--MID(B2,MIN(FIND({1;2;3;4;5;6;7;8;9},B2&123456789)),ROW($1:$99)))从卡机据提取打卡时间根据卡据员工部门=CHOOSE(MATCH(--RIGHT(A2,3),{1,38,14,11,8,21,43,9,28},0),"生部,"业,"总务部,"人事","食"保卫部,"购送货,"财务部根据身份证号统计男人数{=SUM(MOD(LEFT(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)从格式不规范日期中出日=TRIM(RIGHT(SUBSTITUTE(A2,".","计算平成忽考员:=ROUND(AVERAGE(B2:B10),2)计分以上平成:{=ROUND(AVERAGE(IF(ISNUMBER(B2:B10)*(B2:B10>90),B2:B10)),2)}计算当前表以外的所有工作表平均值2:=AVERAGE(班五班B:B)计算二车间女工的平工资二间女计一间三车间女工平工资{=AVERAGE(IF((B2:B10="车三车"女计算各务员的平均奖金{=AVERAGE(1500+300*(INT((C2:C11-80000)/10000)))}计算均工资不忽略薪人员计算人均口{=AVERAGEA((C2:C11="A")*D2:D11)}计平均成绩，绩空白也计计算年级有员的平均获:{=TEXT(AVERAGEA(IF(LEFT(A2:A10,3)="二级统计前名人员的平均绩求季度平均支出金:支"计算个车大均量去掉尾求平:=AVERAGEIFS(B2:B11,B2:B11,">"&MIN(B2:B11),B2:B11,"<"&MAX(B2:B11))生品异的机台平均产:=AVERAGEIFS(C2:C11,B2:B11,"A",D2:D11,"")计算生车间异常机台数=COUNT(C2:C11)算及率{=TEXT(COUNT(0/(B2:B11>=60))/COUNT(B2:B11),"0.00%")}统属于餐饮娱业的店名个:小"酒",","啡"电","闲"吧},A2:A11))}统计分数人{=COUNT(0/((B$2:B$11>ROW(A6)*10)*(B$2:B$11<=ROW(A7)*10)))}统计多少选:统计勤异人判断否有缺:有"有)统计未验完成的产品数=COUNTBLANK(B2:B11)统计量达标根毕业学校统中学学历人:中学)计两列数据相同个统连续三次进前十名的人统计汰者人{=SUM(N(COUNTIF(A2:C11,A2:C11)=1))}统计区中不重复数据数统计诺基亚、摩罗拉和联想已隹手机个数亚摩托罗拉,"联想统计想比托拉手机的销高多少{=SUM(COUNTIF(B2:B11,{"诺基亚联统计冠军榜前三名:{=INDEX(B:B,SMALL(IF(COUNTIF(B$2:B$12,B$2:B$12)*((MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1))>=LARGE(COUNTIF(统计真、假空单元格数成!C2:C11,"=")对名册表进行混合号:=IF(RIGHT(B1)<>"班班班"),"[DBNum2]0"))提不重复数5:{=INDEX(B:B,MATCH(0,COUNTIF($D$1:D1,B$2:B$11),0)+1)}国式名统计于80分三好学生数三学生,C2:C11,">80")}统业绩在6到8之的女务员数=COUNTIFS(B2:B11,"女统计班和班学竞赛获奖:=SUM(COUNTIFS(B2:B11,{"班","三数学根据身计算各班淘汰数计算列后个空元行号计女职工的最大年:女消除位提数计单日最高销售金{=MAX(SUMIF(A2:A11,A2:A11,C2:C11))}查找一学姓统季度最高产值合根据达率计算员工奖金提取产品最后价和最报价计算卫失败最多的次数第名第一名))*ROW(2:10)))}低于平成绩中的最优绩{=MAX(IF(B2:B11<AVERAGE(B2:B11),B2:B11,))}计算文成大分的最总成=DMAX(A1:E11,5,G1:G2)计算学成等男高绩总",B1:B2)根据下列表计算不同目的最大:=DMAX(A1:E11,G4,G1:G2)计算中成:=MEDIAN(B2:B11)显示动日期，但不能过月日根据工作时间算可休天数确定最成计文类品和家具产最利率:{=TEXT(MIN(IF(ISNUMBER(SEARCH("（具",A2:A11)),B2:B11)),"0.00%")}计得票最少者有几:{=MIN(COUNTIF(B2:C11,B2:C11))}根据工程的难系数计奖金将科与成分计算五班的第一名人的最低成一,"二三四五"}&"班!B2:b11")))根员工生产记统计价850元的最利率是多少统计文类和厨具类产的最低单=DMIN(A1:B11,2,D1:D2)第三最小成:=SMALL(B2:B11,3)计算最三名成绩的平值将成按升排:{=SMALL(B$2:B$11,ROW(A1))}罗三个班第一名成:{=SMALL(IF(C$2:C$11="第一",D$2:D$11),ROW(A1))}将文月名称升序排列查看产品经销售的有位罗三个工作表列最后三名成:=SMALL(一:三班第个最小成绩第个最小成绩数计算第个大值并列的个计大于等于10个最产量之:=SUMPRODUCT((B2:C11>LARGE(B2:C11,11))*B2:C11)

间的人按成绩列出学生排

行榜:{=INDEX(A$2:A$11,MATCH(LARGE(10-ROW($2:$11)+B$2:B$11*1000,ROW(A1)),10-ROW($2:$11)+B$2:B$11*1000,0))}最后一次获得一名是几届第一名)*ROW(2:11),1))}提取销量的三名的外销产品名称{=LOOKUP(0,0/($B$2:$B$10*100+ROW($2:$10)=(LARGE(IF(RIGHT(A$2:A$10,3)="外销",B$2:B$10*100+ROW($2:$10)),ROW(A1)))),A$2:A$10)}哪产品生产次数最{=TEXT(MODE(B2:B9*1),"00")}罗列被投多的工作人员对学成绩排计两列数值相同个=COUNT(RANK(B2:B11,C2:C11))查某人成绩在个班中的排成绩名=RANK(I2,(B2:B11,D2:D11,F2:F11),0)分统计每个分段的人员个:蝉联军多次{=MAX(FREQUENCY(ROW(B$2:B$11),(B$2:B$10<>B$3:B$11)*ROW(B$2:B$10)))}计算最多经过次测试成功成功)*ROW(2:11)))}计三个不连续间的频率分:{=SUM(LOOKUP({1,3,5},ROW(1:5),FREQUENCY(B2:B11,{500,550,600,650})))}计算因码错误被锁定次错")*ROW(B2:B12))-1)>=3))}计小加中人数及专大人数小初),0)}计算本频分{=FREQUENCY(CODE(B2:B11),CODE(D2:D5))}夺

冠

排行:$B$11,$B$2:$B$11,),ROW($1:$9))-ROW($1:$10)%,ROW(A1)),FREQUENCY(MATCH($B$2:$B$11,$B$2:$B$11,),ROW($1:$9))-R谁蝉联冠军次数最多:=INDEX(B2:B11,MATCH(MAX(FREQUENCY(ROW(2:11),(B2:B10<>B3:B11)*ROW(2:10))),FREQUENCY(ROW(2:11),(B2:B10<>B3:B11)*ROW(2:10)),))国式名谁

获

得

第

二名记录前期时:=TEXT(NOW(),"m月日确是否已到加油时庆倒时:=TEXT("10-1"-TEXT(NOW(),"mm-dd"),"00")统计货收天:=ROUNDUP(IF(B2<>"",B2-A2,NOW()-A2),0)统已到达收款间的货品数本月需完成几批货物产计本季度收款的合判断今年是否闰年=0))计算年期日"))}计算月有少:=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0),"D")确今年母亲节的日今年包含多少个星期将身份证号码转换成出生日期序列:=DATE(MID(B2,7,2+(LEN(B2)=18)*2),MID(B2,9+(LEN(B2)=18)*2,2),MID(B2,11+(LEN(B2)=18)*2,2))计算国多周:=YEAR(TODAY())-1949算年前电培训均收:{=AVERAGE(IF(YEAR(A2:A11)<2000,B2:B11))}计算天离年最后一天的:=(YEAR(TODAY())&"-12-31")-TODAY()计算本需要交货的数量计算份筒毛进数量笔毛"计算货起止月-"&MAX(MONTH(B2:B11))&"月有几月要交:{=COUNT(0/FREQUENCY(MONTH(B2:B11),MONTH(B2:B11)))}哪几交货:{=IFERROR(SMALL(IF(FREQUENCY(MONTH(B$2:B$11),MONTH(B$2:B$11)),MONTH(B$2:B$11)),ROW(A1))&"月统计家类和文具类产在1月的出库次:{=SUM((B2:B11={"文类家类计今年平均每月天:{=AVERAGE(DAY(DATE(YEAR(TODAY()),ROW(2:13),0)))}计算工转时:=DATE(YEAR(B2),MONTH(B2)+3+(DAY(B2)>15),16)统本月下旬出库数:{=SUM(C2:C11*(DAY(B2:B11)>20))}计生产速度是否达计算至天利计算还日:=TEXT(EDATE(B2,C2),"yy-mm-dd")计算年到年共有多少天提示合续合过即到;;")计算借款日期本月底天数计算季度天:=SUM(DAY(EOMONTH(NOW(),{0,1,2}-MOD(MONTH(NOW())-1,3))))生成资结日:=TEXT(EOMONTH(B2,0)+1,"e年月日统计两工资的加班小数六计算员工数根据进厂日期计算员工可假休天数:=MIN(IF(DATEDIF(B2,TODAY(),"M")<6,0,IF(DATEDIF(B2,TODAY(),"M")<=12,3,3+2*(DATEDIF(B2,TODAY(),"Y")))),15)根据身份证号码计算年(包括年月):=CONCATENATE(DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),"#-00-00"),TODAY(),"Y"),"年",DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),"#-00-00"),TODAY(),"YM"),"

月",DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),"#-00-00"),TODAY(),"MD"),"天计算资:=10*MIN(DATEDIF(B2,TODAY(),"y"),15)+MAX(DATEDIF(B2,TODAY(),"y")-15,0)*5计算时工工:=ROUND(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(B2,"分小时",":"))/(8/24)*50,)计算日工工计算00一：均电压计算工时间，精确到钟根据完工时间计算奖金:=IF(HOUR(B2)>=18,-(ROUNDUP((HOUR(B2-"18:00")*60+MINUTE(B2))/30,0))*3,(ROUNDDOWN((HOUR("18:00"-B2)*60+60-MINUTE(B2))/30,0))*3)计算工时=SUMPRODUCT(MINUTE(B2:B11)+(SECOND(B2:B11)>0))计算天是期:汇星期日的支出金{=SUM((WEEKDAY(A2:A11,2)=7)*(B2:B11="支出")*C2:C11)}汇总第个星期的出库量计算日工工:计算指定日期所在月份有几个日{=SUM(N(WEEKDAY(DATE(YEAR(A2),MONTH(A2),ROW(INDIRECT("1:"&DAY(EOMONTH(A2,0))))))=1))}

星

期按

周

汇总

产量{=SUM(((WEEKDAY($B1,2)-WEEKDAY($B1:$AF1,2))+(COLUMN($B1:$AF1)-1)=(1+(COLUMN(A1)-1)*7))*$B2:$AF2)}按周汇总进仓与出仓数量罗

列

本

月

休

息日{=IFERROR(SMALL(IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0))))),2)=计算末奖补:=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(B2&":"&C2))-1,2)>5))*10罗列值日:计算本月加班时间:{=SUM((MOD(MOD(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0))))),2),7今天本度几本月括多少:=WEEKNUM(EOMONTH(NOW(),0),2)-WEEKNUM((EOMONTH(NOW(),-1)+1),2)+1罗

列

第

周

日期"YYYY-MM-DD")}统计某月第周的支额{=SUM((WEEKNUM(A2:A11*1,1)-WEEKNUM(YEAR(A2:A11)&"-"&MONTH(A2:A11)&"-1")+1=4)*B2:B11)}

出

金判

断

本

月

休

息日:{=(SUM(N(WEEKNUM(ROW((INDIRECT((EOMONTH(NOW(),-1)+1)&":"&EOMONTH(NOW(),0)))),2)-WEEKNUM(EOMONTH(N计算离日:计算程完日:{=WORKDAY(A2,B2,EOMONTH(A2,ROW(INDIRECT("1:"&INT(B2/30*2)))))}计算年第季度有多少个工作日:=NETWORKDAYS(EOMONTH(NOW(),-MONTH(NOW()))+1,EOMONTH(NOW(),3-MONTH(NOW())),{"2008-2-7","2008-2-8","2008-2-9"})计

算

2008

年

第

季

度

有

多

少

个

非

工

作日NOW()))+1,EOMONTH(NOW(),3-MONTH(NOW())),{"2008-2-7","2008-2-8","2008-2-9"})计算今天离国庆节还有多少个工作日充月的月份第月产生“标检查日仓库报日期是正确{=IF(SUM(N((11-RANK(A2:A11,A2:A11))=(ROW(2:11)-1)=FALSE)),"非递","递检字串哪一个字出次最多产每两行累1的编号=IF(ROW()=1,1,IF(MOD(ROW(),3),COUNT(OFFSET(A$1,,,ROW()-1))+1,""))最后一不及格是哪次试计第到第名学员平成:计算成绩排名不能产生并

列

名次=SUMPRODUCT(--((A$2:A$15=A2)*(($C$2:$C$15)+1/ROW($C$2:$C$15))>C2+1/ROW(2:2)))+1计第一次收入金额大于元的金是多计扣除所有扣后的最高薪{=MAX(B2:B10-MMULT(C2:G10*1,ROW(1:5)^0))}对级和成绩升序排罗列今日售的基亚

手

机

型

号

诺

基

亚",B$2:B$11)),10^6,ROW($2:$11)),ROW(1:1))))}统计图数{=IF(B2="","",MIN(IF(B3:B$13<>"",ROW(3:$13),13))-ROW())}罗列一学姓罗列1到1000之质判断某数是否为质数非

质

非

合",IF(SMALL(IF(MOD(A2,ROW(INDIRECT("1:"&A2)))=0,ROW(INDIRECT("1:"&A2))),2)=A2,"质","合数))}计算某个的数个及罗列所有数数个罗列约数将个号码组合成一将每个的贷款重新分组检每个志愿是与之前的重:=MATCH(B2,$B$2:$B$10,)<>ROWS($2:2)将列转换数重组事资料:=REPLACE(INDIRECT("B"&1+(ROW(A1)-1)*4+COLUMN(A:A)),1,LEN(D$1)+1,"")班级成查罗每日缺席名单全成员

全体成员计算所有人的一周产量并排名将金额分散填充，空位以

-

”

占位提取引区域右下角的据理成单:=INDIRECT(CHAR(ROWS($1:22)*3)&COLUMN())合并个工作表的数:=INDIRECT(CHOOSE(MOD(ROW(A2)-1,3)+1,"一级二年级!A"&INT((ROW(A3))/3)+1,"三年区域数:=SUM(COUNTIF(INDIRECT({"C2:C11","F2:F11","I2:I11"}),"<60"))求积、求和两宜:=SUM(IF(C2="",INDIRECT("E"&LOOKUP(1,0/ISERROR((0/$C$1:C1="")),ROW($C$2:C2))&":E"&(ROW()-1)),C2*D2))计算五工作表最大平值{=MAX(SUBTOTAL(1,INDIRECT({"一二三,"四,"五班

相按卡号中的文及数

排序{=INDIRECT("A"&MOD(SMALL(CODE(B$2:B$11)*10000+MID(B$2:B$11,2,9)*100+ROW($2:$11),ROW(B1)),100))}多行多列取唯一值0),ROW(B$2:D$5)*1000+COLUMN(B:D))),"r0c???"),),"")}罗三个表中的最大{=SUBTOTAL(4,INDIRECT({"A组组";"C组"}&"!B2:B11"))}将列程转换成单列忽

略

空值罗列两个正整数的所有约数:{=IFERROR(SMALL(IF((MOD(A$2,ROW(INDIRECT("1:"&GCD(A$2:B$2))))=0)*(MOD(B$2,ROW(INDIRECT("1:"&GCD(A$2:B$2))))=0),ROW(INDIRECT("1:"&GCD(A$2:B$2)))),ROW()-1),"")}B列最大值的址记录最后一销量于3000的址OLUMN(B2:D7)*1000)/1000))}根据下列表引用不同作表的产根据下拉列表罗班级成绩第名姓名:{=IFERROR(INDIRECT(ADDRESS(LARGE(((INDIRECT(D$1&"!B2:B10")=MAX(INDIRECT(D$1&"!B2:B10")))*ROW($2:$10)),ROW查询绩:=OFFSET(A1,MATCH(F1,A2:A11,0),MATCH(G1,B1:D1,0))在有合并单元格的列产生然编:=1+COUNT(OFFSET($A$2,,,ROW()-2,))引合并区域时防止产生值:=IF(A1<>"",A1,OFFSET(B1,-1,))计算10届运动会中有几次破纪录:=SUMPRODUCT(N(SUBTOTAL(5,OFFSET(B2,,,ROW(2:10)))<SUBTOTAL(5,OFFSET($B$2,,,ROW(1:9)))))计第奎续三天之总产量大于等于元的数进、库合查:=SUM(OFFSET(A1,E2,MATCH(G2&"总根据人数自动调整表格大小累计据计至少两科不格的学生人列出成绩最好的科目B:E))-1)}计算格率超过的科数罗列两次未打卡人员")>=2)*ROW($2:$11),ROW(A1))-1,),"")}

×计语文英化政哪总分高=CHOOSE(MATCH(MAX(SUBTOTAL(9,OFFSET(A1,1,MATCH({"语文,"英","化学政治"语文语化学政治},$B$1:$G$1,0),10,)),0),"语英"化治)连续三达100的次数:=SUMPRODUCT(N(COUNTIF(OFFSET(B1,ROW(2:9)-1,,3,1),">=100")=3))罗列及格率最的学生姓名:{=INDEX(A:A,SMALL(IF(MAX(COUNTIF(OFFSET(A$1,ROW($2:$11)-1,1,1,COLUMNS(B:G)),">=60"))=COUNTIF(OFFSET(A1,ROW($2:$11)-1,1,1,COLUMNS(B:G)),">=60"),ROW($2:$11),12),ROW(A1)))&""}计算类图多量名:{=MAX(SUMIF(OFFSET(B1,ROW(2:11)-1,1,1,6),">=100")*(B2:B11="excel"))}计算类图书进货最多的是哪一个月FSET(C2,,COLUMN(C:H)-3,ROWS(2:11),1)),0))}根据下拉列表中的间和产品名计算销量冠军0)-1,ROWS(2:11),)*(B2:B11=K2),0))}根据下拉列表中的品提取姓名与销量10)),1),"")}计算产量最高的季度:季度)分栏打印资料资料!$B$1,""),IF(MOD(COLUMN(),3)=0,"",OFFSET(资料分类总分

类汇

总

并

排序+ROW($2:$11),ROWS($1:$11)+1),ROW(1:1)),3)-1,)&""}工资询无人)}多表成查{=SUBTOTAL(9,OFFSET(INDIRECT(ADDRESS(1,MATCH(H1,1:1,0),1,1,{"一"班;"三"})),1,,ROWS(2:11),))}计算每个学生总分是高于本班平

均

成绩{=SUM(C2:E2)>AVERAGE(IF((A2=A$2:A$11),SUBTOTAL(9,OFFSET(B$1,ROW($2:$11)-1,1,,COLUMNS(C:E)))))}计算个学生进前三名的目总数:{=SUM(N((RANK(N(OFFSET($B$2,ROW()-2,COLUMN(B:F)-2,1,1)),OFFSET($B$2,0,COLUMN(B:F)-2,ROWS($2:$11),1)))<=3))}计算高于单科平均值的目总数{=SUM(N(N(OFFSET($B$2,ROW()-2,COLUMN(B:F)-2,1,1))>SUBTOTAL(1,OFFSET($B$2,0,COLUMN(B:F)-2,ROWS($2:$11),1))))}罗列平均成绩倒数三名的级:{=OFFSET(A1,MATCH(SMALL(SUBTOTAL(1,OFFSET(A1,ROW($2:$9)-1,1,1,COLUMNS(B:F)))*1000+ROW(2:9),ROW(1:3)),SUBTOTAL(1,OFFSET(A1,ROW($2:$9)-1,1,1,COLUMNS(B:F)))*1000+ROW(2:9),),)}将姓重复三:多表汇金华南区,"华东区华北"}&"!B1:C1"),ROW(2:10)-1,)))}从单价表引用价并汇金额从单表引最单价并汇总:{=SUM((N(OFFSET(F1,MATCH(A2:A7,D2:D13,)+(COUNTIF(D2:D13,A2:A7)-1),)))*B2:B7)}根据完状况汇总工程款{=SUM(SUBTOTAL(9,OFFSET(C1,ROW(2:11)-1,,1,2))*(E2:E11=G2))}统计最三天的平均销量:{=SUBTOTAL(1,OFFSET(INDIRECT("B"&MAX((A:A<>"")*ROW(1:1048576))),,,-3,1))}重组培训科目表姓科目从多个产品相同单价的单价表中引用单:=SUMPRODUCT(COUNTIF(OFFSET(A$2,ROW($2:$4)-2,0,1,4),G2)*E$2:E$4)*H2统计所有业务员售利润并罗排列榜按季度引用不同价格统计金额与累计累计合计累计")))}计算个月销润排:{=OFFSET(A1,MOD(LARGE(INT(MMULT(SUBTOTAL(6,OFFSET(INDIRECT({"华区华区华区华中","西南区"}&"!A1"),ROW(2:11)-1,1,1,3)),{1;1;1;1;1}))*1000+ROW(2:11),ROW(1:10)),1000)-1,)}计算五个地区销售利:{=TRANSPOSE(MMULT({1,1,1,1,1,1,1,1,1,1},SUBTOTAL(6,OFFSET(INDIRECT({"华东南区华北区华中区西南区西南区计算第几轮销量最高以及售货员姓名:{=OFFSET(A1,RIGHT(MAX(SUBTOTAL(9,OFFSET(D1,5*(ROW(INDIRECT("1:"&CEILING(COUNTA(C:C)/5,1)))-1),,5))*10+ROW(INDIRECT("1:"&CEILING(COUNTA(C:C)/5,1)))))*5-1,)}提组名及计算组平均达标:{=TEXT(SUBTOTAL(1,OFFSET(B1,((ROW(1:4))*2-1),,,8)),"0.00%")}判断是否超过半人达率90%以上{=COUNTIF(OFFSET(B1,((ROW(1:4))*2-1),,,8),">=0.9")>COLUMNS(B:I)/2}分别计算每个班第名的成绩和姓名名{=MAX(SUBTOTAL(9,OFFSET(B$1,ROW($2:$31)-1,1,,COLUMNS(C:I)))*(B$2:B$31=K2))};

名{=OFFSET(A$1,MOD(MAX((SUBTOTAL(9,OFFSET(B$1,ROW($2:$31)-1,1,,COLUMNS(C:I)))*1000+ROW($2:$31))*(B$2:B$31=K2)),计哪一个月完成目:=OFFSET(A1,LOOKUP(,1*(SUBTOTAL(9,OFFSET(B1,1,0,ROW(2:12)-1))>=200),ROW(2:12)),)有几次连续三月的平均值低于整体平均值:{=SUM(N((SUBTOTAL(9,OFFSET(B4,ROW(2:11)-2,,3,2))/3<AVERAGE(SUBTOTAL(9,OFFSET(B4,ROW(2:11)-2,,,2))))))}算个月销润排:{=OFFSET(A1,MOD(LARGE(INT(MMULT(SUBTOTAL(6,OFFSET(INDIRECT({"华区华区华区华中","西南区"}&"!A1"),ROW(2:11)-1,1,1,3)),{1;1;1;1;1}))*1000+ROW(2:11),ROW(1:10)),1000)-1,)}将表转置方对组进行排万万",10000,1)))),ROW(2:11)^0)+1}区分大写提取产品单价单表!A2:A5)))*TRANSPOSE(单价表区小写查单价且统三总金额单价表!A2:A5)))*TRANSPOSE(单表!B2:B5),{1;1;1;1})),1*(A2:A11={"A组组组引用销售金额高于200次数最多者:{=INDEX(A:A,RIGHT(MAX(MMULT((B2:H9>200)*1,TRANSPOSE(COLUMN(B:H)^0))*10+ROW(2:9))))}根评评和权重分统最得分罗列选手得分前三名的姓名:{=OFFSET($A1,RIGHT(LARGE(MMULT($B2:$F8*TRANSPOSE($I2:$I6),TRANSPOSE(COLUMN($B:$F)^0))*10^6+ROW(2:8),COLUMN(A1)),2)-1,,)}根据字母评语转换得分评语换算得分!A$2:A$11=TRANSPOSE(E2:E11))*1,评语换算得分多列隔数汇赵还珠计算犯低于次的人数提取名从电话中选择性引用据消除厂打印资料照片行{=INDEX(A:A,SMALL(IF(MOD(ROW($1:$12),3)>0,ROW($1:$12),1048576),ROW(A1)))&""}罗列优员{=INDEX(A:A,MOD(SMALL(B$2:B$11*100+ROW($2:$11),ROW(8:8)),100))}插入行分数仅提取通讯录四分之三信:罗列月中产量倒数一名次数最多者名单$2:$B$13,0)=ROW($2:$13)-1),ROW($2:$13),1048576),ROW(A1)))&""}按投诉次数序排列服姓名(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1,ROW($2:$12),9999999),9999999),ROW(A1)),10^5))&""}计算到95分间的员罗列导致产品不良的因ROW($2:$11)),COLUMN(A1)))),"")}按身高学生排列

次表:{=INDEX($A:$A,MOD(SMALL($C$2:$C$49*1000+ROW($2:$49),(ROW(A1)-1)*6+MOD(COLUMN(A1)-1,6)+1),1000))}重组师授课班!班级!提取三不规则区域的集

组组!$C$2:$D$4,$B$2:$B$9),ROW($B$2:$B$9),10),ROW(A4)))&""}不分大小写查找单单表乱序资表中查找多个目:=VLOOKUP($B2,价!$A$2:$E$11,MATCH(C$1,单价!$A$1:$E$1,0),0)将得转换等:=VLOOKUP(B2,{0,"D";60,"C";80,"B";90,"A"},2)查美元与人民币报价条件找:{=VLOOKUP(A2&B2&C2,IF({1,0},资料!A2:A11&资表!B2:B11&资料资料表!C2:C11),2,0)}查找最后更新单价单表单表单价表单价表!A:A,0))),0),ROW($1:$16)),1)}查找双信提姓名拼音的首字:=VLOOKUP(LEFT(A2),拼拼拼,2)用不确定条件查找资料表!A2:A10,A2)=0,资表资料表资料表按学历对姓名排序:{=VLOOKUP(MOD(SMALL(MATCH(B$2:B$10,{"大学";"高中初中";"小学"},0)*1000+ROW($2:$10),ROW(A1)),1000),IF({1,0},ROW($2:$10),A$2:A$10),2,0)}使用通配进查找资表料多作表查找最大值年级

年级对有并元格的区查年天数:=VLOOKUP(F2,OFFSET(B2,MATCH(E2,A2:A13,0)-1,,4,2),2)查找某业务员某季度销量:=HLOOKUP(G2,A1:E9,MATCH(H2,A:A,0),0)在同行查数{=HLOOKUP(MAX(A2:H2),IF({1;0},B2:H2,A2:G2),2,FALSE)}计算两个产品同时期单价:=HLOOKUP(MONTH(A2),IF(B2="塑机多条件计算加班费:=TEXT(HOUR(B2)+HLOOKUP(MINUTE(B2),{0,20.0001,50.0001;0,0.5,1},2),"[>2]6;5")*HOUR(B2)+HLOOKUP(MINUTE(B2),{0,20.0001,50.0001;0,0.5,1},2)根据进厂日期算有薪天数作准证=HLOOKUP(B2,学生库$1:$11,ROUNDUP(COLUMN()/5,0)+1+INT(ROW()/7)*2,FALSE)不区分小写判断两列同数据个:{=COUNT(MATCH(A2:A11,B2:B11,0))}按

字

语

进行排序

排名准!$A$2:$A$9,)*100+ROW($B$2:$B$12),ROW(2:12)-1),100),{1,2})}取列最个据提字符串中的汉字

啊"),),SUM(MATCH({1,2},1/(MID(A2,ROW($1:$99),1)>="啊将文件号中的中文大写转小写

第"&TEXT(SUM((MATCH(MID(A2,{2,3,4},1),TEXT(ROW($1:$10)-1,"[DBNum2]"),0)-1)*{100,10,1}),"000")&"号件计算课科总:{=COUNT(0/(MATCH(B2:B8,B2:B8,0)=ROW(2:8)-1))}产生混编:=TEXT(COUNTIF(C$1:C1,"*"),"[DBNum2]")&TEXT(ROW()-MATCH("提取迟次数最多者姓名罗列多次迟到者

姓名:{=IFERROR(INDEX(B$2:B$11,MODE(IF(COUNTIF(D$1:D1,B$2:B$11)=0,MATCH(B$2:B$11,B$2:B$11,0)))),"")}区分、不区分大小写计字符个数按、银、铜牌排名:{=MATCH(B2:B11+C2:C11%+D2:D11%%,LARGE(B2:B11+C2:C11%+D2:D11%%,ROW(2:11)-1),0)}按班级插入分隔行统计一、二班重参赛员数:{=COUNT(MATCH(B2:B11&C2:C11,{"一班,"班}&"举",))}累计量并列排行榜:{=OFFSET($B$1,MATCH(1,N(MAX(IF(COUNTIF($D$1:D1,B$2:B$12)=0,SUMIF(B$2:B$12,B$2:B$12,C$2:C$12)))=IF(COUNTIF($利用公式对入库表进行数据分析:罗列每个区的获奖人

员

姓名:{=IFERROR(INDEX($A:$A,MATCH(1,(COUNTIF(E$1:E1,$A$2:$A$10)=0)*($B$2:$B$10=E$1),)+1),"")}对合并域进行数据查询冰箱空","洗机"将一维人事资料表转维*",IF(COUNTIF(OFFSET(A$1,,,ROW($1:$42)),"")=ROW()-2,A$1:A$42),0)-1,),""),1,LEN(C$1:F$1)+1,"")}区分小查单根据姓名查找边的身证号:=LOOKUP(E2,B2:B9,A2:A9)

：将中文大写编号换

成

阿

位

伯

数字小写:=TEXT(LOOKUP(1,0/(B2=TEXT(ROW($1:$1000),"[DBNum2]000")),ROW($1:$1000)),"000")将姓名按拼音升序

排列:{=LOOKUP(0,0/(ROW(A1)=MMULT(N($A$2:$A$11>=TRANSPOSE($A$2:$A$11)),ROW($2:$11)^0)),A$2:A$11)}将酒店按星级降序

排列计算某六年中谁获第名次数最罗列每个名次的所有姓名:{=IFERROR(INDEX($A:$A,(SMALL(IF($B$2:$B$11=LARGE(IF(FREQUENCY($B$2:$B$11,$B$2:$B$11),$B$2:$B$11),ROW(A1)),ROW($2:$11)),COLUMN(A2)))),"")}提取书印批:=LOOKUP(9E+307,--RIGHT(LEFT(A2,FIND("[",A2)-1),ROW($1:$99)))罗列年每月第一个及最后一个星期日,ROW(INDIRECT("1:"&DAY(DATE(2008,ROW(A1)+1,0)))))))}补空区将字转换评:{=AVERAGE(LOOKUP(B2:I2,{"A","B","C","D","E"},{10,9.5,8,7,5}))}将字母转换成评分并对选手排名$7),ROW(A1)),10),ROW($2:$7),A$2:A$7)}标识选手应得奖牌冠军亚军季军计算各厂商参赛人数:{=IFERROR(LOOKUP(SMALL(IF(A$2:A$21<>"",ROW($2:$21)),ROW(A1)),ROW($2:$21),A$2:A$21)&":"&MMULT(SMALL(IF(A$2:A$21<>"",ROW($1:$20),21),ROW(A1)+{0,1}),{-1;1}),"")}从名信息中分提取多段数:=IFERROR(-LOOKUP(0,-MID($A2,FIND(B$1,$A2)+LEN(B$1),ROW($1:$100))),"--")反向查数:=LEN(A2)-LOOKUP(100,SEARCH(B2,A2,ROW($1:$99)))-LEN(B2)+2一级、二级分组编号:=TEXT(COUNTIF(B$1:B2,"第第"),ROW($1:2)),"[=0];000")计算购

货

金额:{=LOOKUP(9E+307,--MID($A2,MATCH(0,0*MID($A2,ROW($1:$1000),1),0),ROW($1:$15)))*(LOOKUP(9E+307,--LEFT(REPLAC谁是百冠:=LOOKUP(0,0/(B2:B11=MIN(B2:B11)),A2:A11)从售记录中提取量与单价并计算额:{=LOOKUP(10^16,--RIGHT(REPLACE(A2,FIND("公斤元,A2),100,""),ROW($1:$100)))}根据比赛结果降序排列选手且标识名次冠军,"军"季",""})}计算个工得=LOOKUP(,-FIND(B2,{"A**","A*","A","B**","B*","B","C**","C*","C","D"}),11-ROW($1:$10))查询业务员的负责地区48576),ROW(1:1))))}根据产计算员工产量分{=LOOKUP(B2,{3,0.5}*(ROW($1:$11)-1))}根据员工得等与分等级分值等级与值A$2:A$6)提取量军组:区分工种和达标计奖:=LOOKUP(C2*100,1*LEFT(达与金标准!B$1:K$1,FIND("%",达标与奖金标准!B$1:K$1)-1),OFFSET(达标与标达标与奖金准使用通配符查找所有符合条件的

数

据

:{=IFERROR(LOOKUP(1,0/SEARCH("*

医

院分别提取身份证号码中的年月日:=TEXT(TEXT(MID($A2,7,8),"0000-00-00"),"[DBNum1]"&CHOOSE(MATCH(B$1,{"年月"日"年,"M月,"D日根不良率判断货品处理办合","允,"退