|
计算10个月中的销售利润并排名:{=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(A1:E5)} 对组数进行排名:{=MMULT(N(B2:B11*(IF(LEFT(C2:C11)="万",10000,1))<TRANSPOSE(B2:B11*(IF(LEFT(C2:C11)="万",10000,1)))),ROW(2:11)^0)+1} 区分大小写提取产品单价:{=MMULT((EXACT(B2:B11,TRANSPOSE(单价表!A2:A5)))*TRANSPOSE(单价表!B2:B5),{1;1;1;1})} 区分大小写查单价且统计三组总金额:{=MMULT(TRANSPOSE(SUBTOTAL(9,OFFSET(B1,ROW(2:11)-1,1,,5))*MMULT((EXACT(B2:B11,TRANSPOSE(单价表!A2:A5)))*TRANSPOSE(单价表!B2:B5),{1;1;1;1})),1*(A2:A11={"A组","B组","C组"}))} 引用销售金额高于200次数最多者:{=INDEX(A:A,RIGHT(MAX(MMULT((B2:H9>200)*1,TRANSPOSE(COLUMN(B:H)^0))*10+ROW(2:9))))} 根据评委评分和权重分配统计最后得分:{=SUM(B2:F8*(A2:A8=B10)*TRANSPOSE(I2:I6))} 罗列选手得分前三名的姓名:{=OFFSET($A1,RIGHT(LARGE(MMULT($B2:$F8*TRANSPOSE($I2:$I6),TRANSPOSE(COLUMN($B:$F)^0))*10^6+ROW(2:8),COLUMN(A1)),2)-1,,)} 根据字母评语转换得分:{=MMULT(TRANSPOSE(评语换算得分!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,"D";60,"C";80,"B";90,"A"},2) 查找美元与人民币报价:=VLOOKUP(B2,INDIRECT(E2&"报价!A2:B9"),2,0) 多条件查找:{=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"&MATCH(B2,单价表!A:A,0)&":Z"&MATCH(B2,单价表!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&"",IF({1,0},IF(COUNTIF(资料表!A2:A10,A2)=0,资料表!B2:B10,资料表!A2:A10),资料表!E2:E10),2,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("*"&A2&"*",IF({1,0},资料表!B$2:B$9,资料表!A$2:A$9),2,0)} 多工作表查找最大值:{=TEXT(VLOOKUP(MAX(SUBTOTAL(9,INDIRECT(TEXT(ROW(1:6),"[DBNum1]")&"年级!B"&MATCH(D2,A:A,0)))),IF({1,0},SUBTOTAL(9,INDIRECT(TEXT(ROW(1:6),"[DBNum1]")&"年级!B"&MATCH(D2,A:A,0))),ROW(1:6)),2,0),"[DBNum1]")} 对带有合并单元格的区域查找年假天数:=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="塑胶机",{0,3,8;25,19,18},{0,5,10;12.5,10,11}),2) 多条件计算加班费:=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(DATEDIF(B2,TODAY(),"y"),{0,1,3,5,7,10;0,2,3,5,7,10},2) 制作准考证:=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("A"&(MATCH(1,0/(A:A<>""))))} 提取字符串中的汉字:{=MID(A2,MATCH(1,1/(MID(A2,ROW($1:$99),1)>="啊"),),SUM(MATCH({1,2},1/(MID(A2,ROW($1:$99),1)>="啊"),{0,1})*{-1,1})+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("々",C$1:C1),"(000);;") 提取迟到次数最多者姓名:=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,{"冰箱","空调","洗衣机"},0),MATCH(I2,C1:E1,0)) 将一维人事资料表转二维:{=REPLACE(IFERROR(OFFSET($A$1,MATCH(C$1:F$1&"：*",IF(COUNTIF(OFFSET(A$1,,,ROW($1:$42)),"")=ROW()-2,A$1:A$42),0)-1,),""),1,LEN(C$1:F$1)+1,"")} 区分大小写查找单价:{=INDEX(B:B,MATCH(0,0/EXACT(E1,A1:A8),0))} |