[第129期]评选足球先生（开帖待总结）

LWQ张无忌

=IFERROR(IF(ISEVEN(ROW()),IF(K4="","",SMALL(IFERROR(FIND(K4&$B$5:$B$35&$I$5,OFFSET($C$1:$G$1,MATCH(K$4,$B:$B,)-2,)&K$4&$C$4:$G$34&$C$5:$G$35)*$C$5:$G$35,"鲜"),ROW($N$1))),LOOKUP(6-MOD(LARGE(IFERROR(FIND(K$4&$I$5,$B$5:$B$35&$C$4:$G$34)*$C$5:$G$35/1%+6-COLUMN($A:$E),""),INT(ROW(A2)/2)),100),ROW($1:$5),OFFSET($C$1:$G$1,MATCH(K$4,$B:$B,)-2,))),"")
第一题火车，

减几个=IFERROR(OFFSET($B$1,IF(ISEVEN(ROW()),MAX(IFERROR(FIND(K4&K$4&$I$5,OFFSET($C$1:$G$1,MATCH(K$4,$B:$B,)-2,)&$B$5:$B$34&$C$5:$G$34)*ROW($5:$35),"")),MATCH(K$4,$B:$B,)-2),MIN(6-RIGHT(LARGE(IFERROR(FIND(K$4&$I$5,$B$5:$B$35&$C$4:$G$34)*$C$5:$G$35/1%+(6-COLUMN($A:$E)),""),INT(ROW(A2)/2))))),"")

再减几个
=IFERROR(IF(MOD(ROW(),2),OFFSET($B$1,MATCH(K$4,$B:$B,)-2,6-MOD(LARGE(IFERROR(--(FIND(K$4&$I$5,$B$5:$B$35&$C$4:$G$34)*$C$5:$G$35&{5,4,3,2,1}),""),ROW(B2)/2),10)),LARGE(IF($I$5=IF($B$4:$B$35=K$4,$C$4:$G$35),$C$5:$G$36),ROW(B1)/2)),"")

第二题区域数组进化
=TRIM(MID(CONCAT(IFERROR(TRANSPOSE(IF(-K5:N14<0,K4:N4&-ROW(1:10)/2&REPT(" ",99))),"")),ROW(1:18)*99-98,99))
第二题区域数组

=IFERROR(INDEX(K4:N4,,SMALL(IFERROR(--IF(-K5:N14<0,{1,2,3,4}),""),ROW(1:18)))&"-"&RIGHT(SMALL(IFERROR(IF(-K5:N14<0,{1,2,3,4}*10+ROW(1:10)/2),""),ROW(1:18))),"")

太阳之子

第一题，公式1，单数组，173字符，返回的数字为数值型；
公式2，单数组，166字符，返回的数字为文本型，会影响第二题计算。如果不用常量数组，{3,4,5,6,7}改成COLUMN($B:$G),增加2字符，为168字符。

1. =IFERROR(INDIRECT(TEXT(SMALL(IF(($B$5:$B34=K$4)*($C$5:$G34=$I$5),TIME(,IF(D4&9<7&0,MATCH(K$4,$B:$B,)-1,ROW($6:35)),COLUMN($C:$G))+60-$C$6:$G35),INT(ROW(A2)/2)),"RMCS"),),"")
   
2. =INDIRECT(TEXT(SMALL(60+IF(($B$5:$B34=K$4)*($C$5:$G34=$I$5),TIME(,IF(D4&9<7&0,MATCH(K$4,$B:$B,)-1,ROW($6:35)),{3,4,5,6,7})-$C$6:$G35,4/7),INT(ROW(A2)/2)),"RMCS"),)&""

复制代码

第二题，公式1，列数组，136字符。公式2，列数组，132字符

1. =IFERROR(LOOKUP(ROW()-5,SUBTOTAL(2,OFFSET(J:J,,,,COLUMN(A:D))),K4:N4)&-MOD(SMALL(IF(K6:N14<99,ROW(2:10)/2+COLUMN(A:D)*6),ROW()-4),6),"")
   
2. =IFERROR(LOOKUP(ROW()-5,SUBTOTAL(2,OFFSET(J:J,,,,{1,2,3,4})),K4:N4)&-MOD(SMALL(IF(K6:N14<99,ROW(2:10)/2+{1,2,3,4}*9),ROW()-4),9),"")

复制代码

江苏永能朱兴华

第一题：
=IFERROR(INDIRECT(TEXT(SMALL(IF($B$5:$B34&$C$5:$G34=K$4&$I$5,60-$C$6:$G35+TIME(,{3,4,5,6,7},IF(-1^ROW()=1,ROW($6:35),MATCH(K$4,$B:$B,)-1)),99),ROW(A2)/2),"RSCM"),),"")

=IFERROR(INDIRECT(TEXT(SMALL(IF($B$5:$B34&$C$5:$G34=K$4&$I$5,60-$C$6:$G35+TIME({3,4,5,6,7},,IF(-1^ROW()=1,ROW($6:35),MATCH(K$4,$B:$B,)-1)),99),ROW(A2)/2),"RSCh"),),"")


第二题不知这样写算不算：

=REPT(LOOKUP("鲜",K$4:O$4&-1/(1/(0&ROW(A1)-SUBTOTAL(2,OFFSET(J$5,,,10,COLUMN(A:E)))))),ROW()-5<COUNT(K:N))

=TEXT(LOOKUP("鲜",K$4:O$4&-1/(1/(0&ROW(A1)-SUBTOTAL(2,OFFSET(J$5,,,10,COLUMN(A:E)))))),";;")

sayhi95

题一，170以内好难，先交,175

1. =IFERROR(INDIRECT(TEXT(SMALL(IF(($B$5:$B34=K$4)*($C$5:$G34=$I$5),99-$C$6:$G35+TIME(,{3,4,5,6,7},IF(MOD(ROW(),2),MATCH(K$4,$B:$B,)-1,ROW($6:35)))),INT(ROW(A2)/2)),"RsCm"),),"")

复制代码

强行改到166

1. =INDIRECT(TEXT(SMALL(IF(($B$5:$B34=K$4)*($C$5:$G34=$I$5),99-$C$6:$G35+TIME(,{3,4,5,6,7},IF(MOD(ROW(),2),MATCH(K$4,$B:$B,)-1,ROW($6:35))),9/7%),ROW(A2)/2),"RsCm"),)&""

复制代码

题二，严格遵守这一条“第二题的数据源为B:G列和K4:N14”不引用不在这两个区域内的任意单元格,116

1. =IF(ROW(B1)>COUNT(K$5:N$14),"",LOOKUP("唑",K$4:N$4&-TEXT(ROW(B1)-1%-SUBTOTAL(2,OFFSET(K$6,,-1,9,{1,2,3,4,5})),"0;")))

复制代码

借两个单元格投机,95

1. =TEXT(LOOKUP("唑",K$4:O$4&-TEXT(ROW(A1)-1%-SUBTOTAL(2,OFFSET(J$6,,,9,{1,2,3,4,5})),"0;")),";;@")

复制代码

题二与题一166解法配套方案1,130

1. =IF(ROW(B1)>COUNTIF(K$5:N$14,"><")/2,"",LOOKUP("唑",K$4:N$4&-TEXT(ROW(B1)-1%-COUNTIF(OFFSET(K$5,,-1,10,{1,2,3,4,5}),"><")/2,"0;")))

复制代码

配套方案2,100

1. =TEXT(LOOKUP("唑",K$4:O$4&-TEXT(ROW(B1)-1%-COUNTIF(OFFSET(J$5,,,10,{1,2,3,4,5}),"><")/2,"0;")),";;@")

复制代码

wodewan

第一题：166字(K5,右拉下拉）
=INDIRECT(TEXT(99+SMALL(IF($C$4:$G$34&$B$4:$B$34=$I$5&K$4,TIME(,COLUMN($C:$G),IF(MOD(ROW(),2),MATCH(K$4,$B:$B,)-1,ROW($5:$35)))-$C$5:$G$35,1%),ROW(A2)/2),"RSCM"),)&""

第二题：175字（区域数组，超字数了）
=TEXT(INDEX(T(OFFSET(J4,,ROW(1:88)/10))&-MOD(ROW(1:88),10),SMALL(--IF(FREQUENCY(IF(C1:G34=I5,MATCH(B1:B34,K4:N4,)),ROW(1:3))<COLUMN(A:E),80,ROW(1:4)&COLUMN(A:E)),ROW()-4)),"")

mmlzh

=INDIRECT(TEXT(MOD(SMALL(IF($B$4:$B34&$C$4:$G34=K$4&$I$5,(IF(ISODD(ROW()),MATCH(K$4,$B:$B,)-1,ROW($5:35))&COLUMN($C:$G))-$C$5:$G35/1%%,99),ROW(A2)/2),10^3),"r0c0"),)&""


=TEXT(LOOKUP("球",K$4:O$4&-(0&ROW()-MMULT(MMULT({1,1,1,1,1},COUNTIFS(OFFSET(B:B,,ROW($1:$5)),I$5,B:B,K$4:N$4)),N(ROW($1:$4)<COLUMN(A:E)))-5)-1),";")
=TEXT(LOOKUP("球",K$4:O$4&-(0&ROW()-COUNTIF(OFFSET(J$5:J$14,,,,COLUMN(A:E)),">""")/2-5)-1),";")

我走我流

回来太晚，占个楼，学习下楼上各位大神，，先来第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,),"")

265094

题1
=INDIRECT(TEXT(LARGE(IF($B$4:$B34&$C$4:$G34=K$4&$I$5,$C$5:$G35+TIME({5,4,3,2,1},IF(ISODD(ROW()),MATCH(K$4,$B:$B,)-1,ROW($5:35)),{3,4,5,6,7}),1%),INT(ROW(A2)/2)),"rmcs"),)&""
硬减几个字符凑数
=INDIRECT(TEXT(LARGE(IF($B:$B&$C:$G=K$4&$I$5,$C$2:$G35+TIME({5,4,3,2,1},IF(ISODD(ROW()),MATCH(K$4,$B:$B,)-1,ROW($2:35)),{3,4,5,6,7}),1%),INT(ROW(A2)/2)),"rmcs"),)&""

=INDIRECT(TEXT(MOD(SMALL(IF($B$4:$B34&$C$4:$G34=K$4&$I$5,(IF(MOD(ROW(),2),MATCH(K$4,$B:$B,)-1,ROW($5:35))&{3,4,5,6,7})-$C$5:$G35/1%%,99),ROW(A2)/2),1000),"r0c0"),)&""

题2
=IFERROR(INDEX(T(OFFSET(J4,,ROW(5:24)/5))&-MOD(ROW(5:24),5)-1,SMALL(IF((K5:N13>"")*MOD(ROW(1:9),2),ROW(2:10)/2+{0,1,2,3}*5),ROW()-4)),"")

delete_007

先开贴，请以上楼层不要再编辑，以免影响评分。

delete_007

如楼评分如下，如有疑议，请与我联系。（财富奖励，酌情添加，未在表格中反应。）