|
根据姓名查找左边的身份证号:=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)} 将酒店按星级降序排列:{=LOOKUP(ROUND(1/MOD(LARGE(LEN(B$2:B$10)+1/ROW($2:$10),ROW(A1)),1),0),ROW($2:$10),A$2:A$10)} 计算某班六年中谁获第一名次数最多:{=MAX(COUNTIF(B2:B7,B2:B7))} 罗列每个名次的所有姓名:{=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))) 罗列2008年每月第一个及最后一个星期日:{=MIN(IF(WEEKDAY(DATE(2008,ROW(A1),ROW(INDIRECT("1:"&DAY(DATE(2008,ROW(A1)+1,0))))),2)=7,DATE(2008,ROW(A1),ROW(INDIRECT("1:"&DAY(DATE(2008,ROW(A1)+1,0)))))))} 填补空白区:=LOOKUP(1,0/($A$2:A2<>""),A$2:A2) 将字母转换成评分:{=AVERAGE(LOOKUP(B2:I2,{"A","B","C","D","E"},{10,9.5,8,7,5}))} 将字母转换成评分并对选手排名:{=LOOKUP(MOD(LARGE(MMULT(LOOKUP($B$2:$I$7,{"A","B","C","D","E"},{10,9.5,8,7,5}),ROW($1:$8)^0)*10000+ROW($2:$7),ROW(A1)),10),ROW($2:$7),A$2:A$7)} 标识各选手应得的奖牌:{=LOOKUP(SUM(N(IF(FREQUENCY(B$2:B$11,B$2:B$11),B$2:B$11,0)>B2))+1,ROW($1:$4),{"冠军","亚军","季军",""})} 计算各厂商参赛人数:{=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,"第*"),"00")&TEXT(ROW()-LOOKUP(1,0/(LEFT(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(REPLACE(A2,1,FIND("*",A2),""),ROW($1:$1000))))} 谁是百米冠军:=LOOKUP(0,0/(B2:B11=MIN(B2:B11)),A2:A11) 从销售记录中提取销量与单价并计算金额:{=LOOKUP(10^16,--RIGHT(REPLACE(A2,FIND("公斤",A2),100,""),ROW($1:$100)))*LOOKUP(10^16,--RIGHT(REPLACE(A2,FIND("元",A2),100,""),ROW($1:$100)))} 根据比赛结果降序排列选手且标识名次:{=LOOKUP(SUM(N(COUNTIF(B$2:B$21,E2)<--IF(FREQUENCY(COUNTIF($B$2:$B$21,B$2:B$21),COUNTIF($B$2:$B$21,B$2:B$21)),COUNTIF($B$2:$B$21,B$2:B$21))))+1,ROW($1:$4),{"冠军","亚军","季军",""})} 计算每个职工的得分:=LOOKUP(,-FIND(B2,{"A**","A*","A","B**","B*","B","C**","C*","C","D"}),11-ROW($1:$10)) 查询业务员的负责地区:{=T(INDEX(B:B,SMALL(IF(LOOKUP(ROW(A$2:A$11),IF(A$2:A$11<>"",ROW(A$2:A$11)),A$2:A$11)=$D$2,ROW(A$2:A$11),1048576),ROW(1:1))))} 根据产量计算员工产量得分:{=LOOKUP(B2,{3,0.5}*(ROW($1:$11)-1))} 根据员工得分转换为相应的等级:=LOOKUP(B2,--REPLACE(等级与分值!B$2:B$6,FIND("-",等级与分值!B$2:B$6),10,""),等级与分值!A$2:A$6) 提取产量冠军的组别:=IF(COUNTA(B2:E2),LOOKUP(1,0/ISTEXT(B2:E2),B$1:E$1),"") 区分工种和达标率计算奖金:=LOOKUP(C2*100,1*LEFT(达标与奖金标准!B$1:K$1,FIND("%",达标与奖金标准!B$1:K$1)-1),OFFSET(达标与奖金标准!B$1,MATCH(B2,达标与奖金标准!A$2:A$4,0),,,10)) 使用通配符查找所有符合条件的数据:{=IFERROR(LOOKUP(1,0/SEARCH("*医院*",IF(COUNTIF($C$1:C3,A$2:A$12)=0,A$2:A$12,)),A$2:A$12),"")} 分别提取身份证号码中的年月日:=TEXT(TEXT(MID($A2,7,8),"0000-00-00"),"[DBNum1]"&CHOOSE(MATCH(B$1,{"年","月","日"},0),"YYYY年","M月","D日")) 根据不良率判断送货品处理办法:=CHOOSE((SUM(N(C2/B2>={0,0.005,0.01}))),"合格","允收","退货") 让VLOOKUP函数在多区域查找:=VLOOKUP(A11,CHOOSE(MATCH(B11,{"一年级","二年级","三年级"},0),A1:B9,D1:E9,G2:H9),2,0) 将区域互换位置:=VLOOKUP(E2&"",CHOOSE({2,1},A2:A9,C2:C9),2,0) 跨表统计最大值:{=CHOOSE(MOD(MAX(SUBTOTAL(9,INDIRECT({"A组";"B组";"C组"}&"!B2:B10"))*100+{1;2;3}),100),"A组","B组","C组")} 罗列所有参加田径的人员:{=IFERROR(VLOOKUP(1,CHOOSE({1,2},--(COUNTIF(OFFSET(C$2,,,ROW($2:$11)-1),"田径")=ROW(1:1)),A$2:A$11),2,),"")} 计算今天是本月的上旬、中旬还是下旬:=CHOOSE(MIN(CEILING(DAY(TODAY())/10,1),3), "上旬","中旬","下旬") 建立文件目录:=HYPERLINK("[E:\产量表\"&TEXT(ROW(1:1),"[DBNum1]")&"月产量表.xlsx]sheet1!A1",TEXT(ROW(1:1),"[DBNum1]")&"月产量表") 链接“总表”中B列最大值单元格:{=HYPERLINK("#总表!B"&MAX((MAX(总表!B:B)=总表!B:B)*ROW(B:B)),"至总表B列最大值")} 链接至B列最末的非空单元格:{=HYPERLINK("#B"&MAX(((B:B)<>"")*ROW(B:B)),"B列最后非空值")} 选择冠军姓名:{=HYPERLINK("#"&TEXT(SUM(SMALL(IF(B2:B13=MAX(B2:B13),ROW(2:13)),ROW(INDIRECT("1:"&COUNTIF(B2:B13,MAX(B2:B13)))))*10^((ROW(INDIRECT("1:"&COUNTIF(B2:B13,MAX(B2:B13))))-1)*2)),REPT("A00!,",COUNTIF(B2:B13,MAX(B2:B13))-1)&"A00"),"得票冠军")} 选择二年级旷课人员名单:{=HYPERLINK("#A"&MIN(IF(A2:A12="二年级",ROW(2:12)))&":B"&MAX(IF(A2:A12="二年级",ROW(2:12))),"二年级名单")} 选择产量最高工作表:{=HYPERLINK("#"&CHAR(64+MOD(MAX(SUBTOTAL(9,INDIRECT(CHAR(64+ROW(1:8))&"组!B2:B11"))*100+ROW(1:8)),100))&"组!A1","跳至最大产量组")} 选择打印区域:=HYPERLINK("#Print_Area",IF(ISERR(INDEX(Print_Area,1,1)),"未设置打印区","跳至打印区域")) 计算期末平均成绩:{=AVERAGE(IF(ISEVEN(COLUMN(B:I)-1),B3:I3))} 提取期末成绩明细:{=INDEX(成绩表!1:1,SMALL(IF(ISEVEN(COLUMN($B:$I)-(ROW()<>1)),COLUMN($B:$I)),COLUMN(A1)))} 提取每日累计出库数和每日库存数:日期=INDEX(A:A,ROW(A1)*2);累计出库数{=SUM(ISODD(ROW(INDIRECT("2:"&(ROW(A1)*2)+1)))*OFFSET(C$1,1,,ROWS($1:1)*2))};每日库存数{=SUM(SUMIF(OFFSET(B$1,1,,ROW(A1)*2),{"进库","出库"},C$2)*{1,-1})} 根据身份证号码汇总男、女职工总数:男{=SUM(--ISODD(MID(B2:B10,15,3)))};女{=SUM(--ISEVEN(MID(B2:B10,15,3)))} 提取当前表打印区域地址:=IF(ISNA(VLOOKUP("苹果",A2:B5,2,0)),10,LOOKUP(10^16,--LEFT(VLOOKUP("苹果",A2:B5,2,0),ROW(1:100)))) 计算生产部人数和非生产部人数:生产部人数{=SUM((NOT(ISERR(FIND("车间",A2:A11)))*B2:C11))};非生产部人数{=SUM((ISERR(FIND("车间",A2:A11)))*B2:C11)} 提取A、B列相同项与不同项:{=T(INDEX(A:A,SMALL(IF(NOT(ISERROR(MATCH(A$2:A$11,B$2:B$11,0))),ROW($2:$11),1048576),ROW(A1))))} 计算产品体积:=IF(ISERROR(FIND("/",B2)),B2^3,PRODUCT(1*TRIM(MID(SUBSTITUTE(B2,"/",REPT(" ",100)),{1,100,200},100)))) 引用单价并去除干扰符:=IF(ISNA(MATCH(B2,单价表!B$1:E$1,0)),"请更新单价",C2*LOOKUP(10^16,--LEFT(HLOOKUP(B2,单价表!B$1:E$2,2,0),ROW($1:$100)))) 查询书籍在七年中的最高单价:{=IF(ISNA(MATCH(A10,A2:A8,0)),"书名错误",MAX(VLOOKUP(A10,A1:H8,COLUMN(B:H),0)))} 根据计价单位查询单价:=IF(ISNA(MATCH(B2,F$1:H$1,0)),"未设定汇率",C2*HLOOKUP(B2,F$1:H$2,2,0)) 数字、字母与汉字个数计算:数字个数{=SUM(--(ERROR.TYPE(INDIRECT("XFD"&MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)&1))=3))} |