分!A$2:A$11=TRANSPOSE(E2:E11))*1,评语换算得分!B$2:B$11)+SUBTOTAL(9,OFFSET(B2,ROW(2:11)-2,,,COLUMNS(B:D)))}
多列、隔行数据汇总:{=SUM(MMULT(D2:G11,TRANSPOSE(COLUMN(D:G)^0))*(A2:A11=\赵还珠\
计算犯规低于3次的人数:{=SUM(N(MMULT(--(B2:B21=TRANSPOSE(B2:B21)),ROW(2:21)^0)={1,2})/{1,2})}
提取姓名:=INDEX(B:B,ROW()*2)&\
从电话簿中选择性引用数据:=INDEX($A:$B,ROW(A1)*3-2,COLUMN(A:A)) 消除厂牌打印资料照片行:{=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))}
插入空行分割数据:=IF(MOD(ROW(),3)>0,INDEX(A:A,ROW(A2)*2/3),\仅仅提取通讯录中四分之三信息:=INDEX(A:B,ROW(A2)*2/3,(MOD(ROW(A3),3)+1)/3+1)
罗列12月中产量倒数第一名次数最多者名单:{=INDEX(B:B,SMALL(IF((COUNTIF(B$2:B$13,B$2:B$13)=MAX(COUNTIF($B$2:$B$13,$B$2:$B$13)))*(MATCH($B$2:$B$13,$B$2:$B$13,0)=ROW($2:$13)-1),ROW($2:$13),1048576),ROW(A1)))&\
按投诉次数升序排列客服姓名:{=INDEX(B:B,MOD(SMALL(IF(MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1,COUNTIF(B$2:B$12,B$2:B$12)*10^5+IF(MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1,ROW($2:$12),9999999),9999999),ROW(A1)),10^5))&\
计算60分到95分之间的人员个数:=INDEX(FREQUENCY(B2:B11,{60,95}),2)
罗列导致产品不良的主因:{=IFERROR(T(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(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))}
重组教师授课表:{=INDEX(班级!$A:$A,SMALL(IF(班级!$B$2:$D$11=$A3,ROW($2:$11),1048576),COLUMN(C$1)))&\
提取三个不规则区域的交集:{=INDEX($B:$B,SMALL(IF(COUNTIF(C组!$B$2:$I$2,$B$2:$B$9)*COUNTIF(B组!$C$2:$D$4,$B$2:$B$9),ROW($B$2:$B$9),10),ROW(A4)))&\
不区分大小写查找单价:=VLOOKUP(B2,单价表!A$2:C$11,3,0)*C2
乱序资料表中查找多个项目:=VLOOKUP($B2,单价表!$A$2:$E$11,MATCH(C$1,单价表!$A$1:$E$1,0),0)
将得分转换成等级:=VLOOKUP(B2,{0,\
查找美元与人民币报价:=VLOOKUP(B2,INDIRECT(E2&\报价!A2:B9\多条件查找:{=VLOOKUP(A2&B2&C2,IF({1,0},资料表!A2:A11&资料表!B2:B11&资料表!D2:D11,资料表!C2:C11),2,0)}
查找最后更新单价:{=VLOOKUP(10^16,--LEFT(VLOOKUP(B2,单价表!A:Z,COUNTA(INDIRECT(\单价表!A\单价表!A:A,0)&\单价表!A:A,0))),0),ROW($1:$16)),1)}
查找双列信息:{=VLOOKUP(A9,CHOOSE({3,2,1},A1:A6&B1:B6,C1:C6&D1:D6,E1:E6&F1:F6),{2,3},)}
提取姓名拼音的首字母:=VLOOKUP(LEFT(A2),拼音,2)&VLOOKUP(MID(A2,2,1),拼音,2)&VLOOKUP(MID(A2,3,1),拼音,2)
用不确定条件查找:{=VLOOKUP(A2&\资料表!A2:A10,A2)=0,资料表!B2:B10,资料表!A2:A10),资料表!E2:E10),2,0)}
按学历对姓名排序:{=VLOOKUP(MOD(SMALL(MATCH(B$2:B$10,{\大学\高中\初中\小学\}
使用通配符进行查找:{=VLOOKUP(\资料表!B$2:B$9,资料表!A$2:A$9),2,0)}
多工作表查找最大值:{=TEXT(VLOOKUP(MAX(SUBTOTAL(9,INDIRECT(TEXT(ROW(1:6),%um1]\年级!B\,\年级!B\
对带有合并单元格的区域查找年假天数:=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\
根据进厂日期计算有薪假天数:=HLOOKUP(DATEDIF(B2,TODAY(),\
制作准考证:=HLOOKUP(B2,学生档案库!$1:$11,ROUNDUP(COLUMN()/5,0)+1+INT(ROW()/7)*2,FALSE)
不区分大小写判断两列相同数据个数:{=COUNT(MATCH(A2:A11,B2:B11,0))}
按汉字评语进行排序:{=INDEX(A:B,MOD(SMALL(MATCH($B$2:$B$12,排名标准!$A$2:$A$9,)*100+ROW($B$2:$B$12),ROW(2:12)-1),100),{1,2})}
提取A列最后一个数据:{=INDIRECT(\提取字符串中的汉字:{=MID(A2,MATCH(1,1/(MID(A2,ROW($1:$99),1)>=\
啊\啊\
将文件号中的中文大写转小写:{=\第\0)-1)*{100,10,1}),\号文件\
计算补课科目总数:{=COUNT(0/(MATCH(B2:B8,B2:B8,0)=ROW(2:8)-1))} 产生混合编号:=TEXT(COUNTIF(C$1:C1,\々\
提取迟到次数最多者姓名:=INDEX(B2:B11,MODE(MATCH(B$2:B$11,B$2:B$11,0)))
罗列多次迟到者姓名:{=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)))),\
区分、不区分大小写统计字符个数:{=COUNT(0/(MATCH(MID(A2,ROW($1:$100),1),MID(A2,ROW($1:$100),1),0)=ROW($1:$100)))-1}
按金、银、铜牌排名次:{=MATCH(B2:B11+C2:C11%+D2:D11%%,LARGE(B2:B11+C2:C11%+D2:D11%%,ROW(2:11)-1),0)}
按班级插入分隔行:{=INDEX(A:B,MOD(SMALL(IF({1,0},ROW(2:11)*1001,IF(ROW(2:11)-1=MATCH(A2:A11,A2:A11,0),((MATCH(A2:A11,A2:A11,)+COUNTIF(A2:A11,A2:A11))*1000+100),1048576)),ROW(1:100)),1000),{1,2})&\
统计一、二班举重参赛人员数:{=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($D$1:D1,B$2:B$12)=0,SUMIF(B$2:B$12,B$2:B$12,C$2:C$12))),),)&\
利用公式对入库表进行数据分析:{=INDEX(B:B,SMALL(IF(MATCH(B$2:B$200,B$2:B$200,0)=ROW($2:$200)-1,ROW($2:$200),65536),ROW(A1)))&\
罗列每个地区的获奖人员姓名:{=IFERROR(INDEX($A:$A,MATCH(1,(COUNTIF(E$1:E1,$A$2:$A$10)=0)*($B$2:$B$10=E$1),)+1),\
对合并区域进行数据查询:=OFFSET(B1,MATCH(G2,A2:A13,0)-1+MATCH(H2,{\冰箱\空调\洗衣机\
将一维人事资料表转二维:{=REPLACE(IFERROR(OFFSET($A$1,MATCH(C$1:F$1&\：*\1,LEN(C$1:F$1)+1,\
区分大小写查找单价:{=INDEX(B:B,MATCH(0,0/EXACT(E1,A1:A8),0))} 根据姓名查找左边的身份证号:=LOOKUP(E2,B2:B9,A2:A9)
将中文大写编号转换成阿位伯数字小写:=TEXT(LOOKUP(1,0/(B2=TEXT(ROW($1:$1000),\$1000)),\
将姓名按拼音升序排列:{=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(\
罗列2008年每月第一个及最后一个星期日:{=MIN(IF(WEEKDAY(DATE(2008,ROW(A1),ROW(INDIRECT(\TE(2008,ROW(A1)+1,0))))),2)=7,DATE(2008,ROW(A1),ROW(INDIRECT(\AY(DATE(2008,ROW(A1)+1,0)))))))}
填补空白区:=LOOKUP(1,0/($A$2:A2<>\将字母转换成评分:{=AVERAGE(LOOKUP(B2:I2,{\
将字母转换成评分并对选手排名:{=LOOKUP(MOD(LARGE(MMULT(LOOKUP($B$2:$I$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<>\OW($2:$21),A$2:A$21)&\\,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,\第*\第\
计算购货金额:{=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(\W($1:$1000))))}
谁是百米冠军:=LOOKUP(0,0/(B2:B11=MIN(B2:B11)),A2:A11)
从销售记录中提取销量与单价并计算金额:{=LOOKUP(10^16,--RIGHT(REPLACE(A2,FIND(\公斤\元\
根据比赛结果降序排列选手且标识名次:{=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,{\11-ROW($1:$10))
查询业务员的负责地区:{=T(INDEX(B:B,SMALL(IF(LOOKUP(ROW(A$2:A$11),IF(A$2:A$11<>\W(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(\医院*\
分别提取身份证号码中的年月日:=TEXT(TEXT(MID($A2,7,8),\B$1,{\年\月\日\年\月\日\
根据不良率判断送货品处理办法:=CHOOSE((SUM(N(C2/B2>={0,0.005,0.01}))),\合格\允收\退货\
让VLOOKUP函数在多区域查找:=VLOOKUP(A11,CHOOSE(MATCH(B11,{\一年级\二年级\三年级\
将区域互换位置:=VLOOKUP(E2&\跨表统计最大值:{=CHOOSE(MOD(MAX(SUBTOTAL(9,INDIRECT({\组\组\组\组\组\组\
罗列所有参加田径的人员:{=IFERROR(VLOOKUP(1,CHOOSE({1,2},--(COUNTIF(OFFSET(C$2,,,ROW($2:$11)-1),\田径\
计算今天是本月的上旬、中旬还是下旬:=CHOOSE(MIN(CEILING(DAY(TODAY())/10,1),3), \上旬\中旬\下旬\