|
提取各软件的版本号:=REPLACE(REPLACE(A2,1,SEARCH("(",A2),),LEN(REPLACE(A2,1,SEARCH("(",A2),)),1,) 店名分类:=IF(COUNT(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:$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,"：","("),"*",")*")&")" 判断调色配方中是否包含色粉“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)) 计算小学参赛者人数:{=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(A2: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(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,"."," ",2),3)) 计算平均成绩(忽略缺考人员):=ROUND(AVERAGE(B2:B10),2) 计算90分以上的平均成绩:{=ROUND(AVERAGE(IF(ISNUMBER(B2:B10)*(B2:B10>90),B2:B10)),2)} 计算当前表以外的所有工作表平均值2:=AVERAGE(一班:五班!B:B) 计算二车间女职工的平均工资:{=AVERAGE(IF((B2:B10="二车间")*(C2:C10="女"),D2:D10))} 计算一车间和三车间女职工的平均工资:{=AVERAGE(IF((B2:B10="一车间")+(B2:B10="三车间")*(C2:C10="女"),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})) 求每季度平均支出金额:=AVERAGEIF(B2:B9,"支出",C2) 计算每个车间大于250的平均产量:=AVERAGEIF(B2:C11,">250") 去掉首尾求平均:=AVERAGEIFS(B2:B11,B2:B11,">"&MIN(B2:B11),B2:B11,"<"&MAX(B2:B11)) 生产A产品且无异常的机台平均产量:=AVERAGEIFS(C2:C11,B2:B11,"A",D2:D11,"") 计算生产车间异常机台个数:=COUNT(C2:C11) 计算及格率:{=TEXT(COUNT(0/(B2:B11>=60))/COUNT(B2:B11),"0.00%")} 统计属于餐饮娱乐业的店名个数:{=COUNT(SEARCH({"小吃","酒吧","茶","咖啡","电影","休闲","网吧"},A2:A11))} 统计各分数段人数:{=COUNT(0/((B$2:B$11>ROW(A6)*10)*(B$2:B$11<=ROW(A7)*10)))} 统计有多少个选手:{=COUNT(0/(MATCH(B2:B11,B2:B11,)=(ROW(2:11)-1)))} 统计出勤异常人数:=COUNTA(B2:B11) 判断是否有人缺考:=IF(COUNTA(B2:E10)=ROWS(B2:E10)*COLUMNS(B2:E10),"没有","有") 统计未检验完成的产品数:=COUNTBLANK(B2:B11) 统计产量达标率:=TEXT(COUNTIF(B2:B11,">=800")/COUNT(B2:B11),"0.00") 根据毕业学校统计中学学历人数:=COUNTIF(B2:B11,"*中学") 计算两列数据相同个数:{=SUM(COUNTIF(A2:A11,B2:B11))} 统计连续三次进入前十名的人数:{=SUM(COUNTIF(C2:C11,IF(COUNTIF(A2:A11,B2:B11),B2:B11)))} 统计淘汰者人数:{=SUM(N(COUNTIF(A2:C11,A2:C11)=1))} 统计区域中不重复数据个数:{=SUM(1/COUNTIF(B2:B8,B2:B8))} 统计诺基亚、摩托罗拉和联想已隹出手机个数:=SUM(COUNTIF(B2:B11,"*"&{"诺基亚","摩托罗拉","联想"}&"*")) 统计联想比摩托罗拉手机的销量高多少:{=SUM(COUNTIF(B2:B11,{"诺基亚*","*联想*"})*{1,-1})} 统计冠军榜前三名:{=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(B$2:B$12,B$2:B$12)*((MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1)),3),ROW($2:$12)),ROW(A1)))} 统计真空、假空单元格个数:=COUNTIF(成绩!C2:C11,"=") 对名册表进行混合编号:=IF(RIGHT(B1)<>"班",ROW()-COUNTIF($B$1:B1,"??班"),TEXT(COUNTIF($B$1:B1,"??班"),"[DBNum2]0")) 提取不重复数据5:{=INDEX(B:B,MATCH(0,COUNTIF($D$1:D1,B$2:B$11),0)+1)} 中国式排名:{=SUM(IF(B$2:B$11>B2,1/COUNTIF(B$2:B$11,B$2:B$11)))+1} 统计大于80分的三好学生个数:{=COUNTIFS(B2:B11,"三好学生",C2:C11,">80")} 统计业绩在6万到8万之间的女业务员个数:=COUNTIFS(B2:B11,"女",C2:C11,">60000",C2:C11,"<=800000") 统计二班和三班数学竞赛获奖人数:=SUM(COUNTIFS(B2:B11,{"二班","三班"},C2:C11,"数学*")) 根据身高计算各班淘汰人数:=SUM(COUNTIFS(B$2:B$11,E1,C$2:C$11,{"<160",">180"})) 计算A列最后一个非空单元格行号:{=MAX((A:A<>"")*ROW(A:A))} 计算女职工的最大年龄:{=MAX((B2:B11="女")*C2:C11)} 消除单位提取数据:{=MAX(IFERROR(ABS(LEFT(A2,ROW($1:$100))),))*IF(LEFT(A2)="-",-1,1)} 计算单日最高销售金额:{=MAX(SUMIF(A2:A11,A2:A11,C2:C11))} 查找第一名学生姓名:=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,)) 统计季度最高产值合计:{=MAX(SUBTOTAL(9,OFFSET(B2,,COLUMN(B:E)-2,ROWS(2:10),1)))} 根据达标率计算员工奖金:=MAX((B2>{0,0.8,0.9,1,1.05})*{200,250,300,450,550}) |