本帖最后由 我走我流 于 2020-3-23 10:20 编辑
回来太晚,占个楼,学习下楼上各位大神,,先来第1问,都是严重超标的
=IFERROR(IF(ISODD(ROW(A2)),LARGE(IF(($B$5:$B$33=K$4)*($C$5:$G$33=$I$5),$C$6:$G$34),INT(ROW(A2)/2)),INDEX($A:$G,MATCH(K$4,$B:$B,)-1,1/MOD(LARGE(IF(($B$5:$B$33=K$4)*($C$5:$G$33=$I$5),$C$6:$G$34+1/COLUMN($C:$G)),INT(ROW(A2)/2)),1))),"")
233字符
=IFERROR(INDIRECT(TEXT(RIGHT(LARGE(IF(($B$5:$B33=K$4)*($C$5:$G33=$I$5),$C$6:$G34/1%-COLUMN($C:$G)+IF(MOD(ROW(A1),2),MATCH(K$4,$B:$B,)-1,ROW($6:34))%+COLUMN($C:$G)%%),INT(ROW(A2)/2)),4),"R0C00"),),"")
199字符,,终于混到170了。。。。
=IFERROR(INDIRECT(TEXT(SMALL(IF(($B$5:$B33&$C$5:$G33=K$4&$I$5),TIME(50-$C$6:$G34,{3,4,5,6,7},IF(-1^ROW()<0,MATCH(K$4,$B:$B,)-1,ROW($6:34)))),INT(ROW(A2)/2)),"rScm"),),"")
第2问开个长火车-355字
=IFERROR(INDEX(K$4:N$4,MATCH(1=1,ROW(A1)<=MMULT(N(ROW($1:$4)>=COLUMN(A:D)),MMULT(COUNTIFS(OFFSET(B$5:B$34,,COLUMN(A:E)),$I$5,B$5:B$34,TRANSPOSE(K$4:N$4)),ROW(1:5)^0)),))&-COUNTIF(Q4:Q$4,INDEX(K$4:N$4,MATCH(1=1,ROW(A1)<=MMULT(N(ROW($1:$4)>=COLUMN(A:D)),MMULT(COUNTIFS(OFFSET(B$5:B$34,,COLUMN(A:E)),$I$5,B$5:B$34,TRANSPOSE(K$4:N$4)),ROW(1:5)^0)),))&"*")-1,"")
=CONCAT(TEXT(SMALL(IF(N(INDIRECT("r"&ROW($3:7)*2&"c"&COLUMN(K:N),)),ROW($1:5)+{1,2,3,4}*10,99),ROW(A1)),"[="&{1,2,3,4}&ROW($1:5)&"]"&K$4:N$4&-ROW($1:5)&";"))
再减去几个字符,还是超标
=CONCAT(TEXT(SMALL(IF(SUBTOTAL(4,OFFSET(J$4,ROW($1:5)*2,{1,2,3,4})),ROW($1:5)+{1,2,3,4}*10,99),ROW(A1)),"[="&{1,2,3,4}&ROW($1:5)&"]"&K$4:N$4&-ROW($1:5)&";"))
140字
=IFNA(HLOOKUP("*",K$4:N4&(0&COUNTIF(OFFSET(J:J,,{1,2,3,4}),">0")-COUNTIF(Q$4:Q4,K$4:N$4&"*")-1)-COUNTIF(OFFSET(J:J,,{1,2,3,4}),">0"),1,),"")
|