[第131期]招生录取（已评分待总结）

delete_007

竞赛日期：2020-12-26至2021-1-30

A1233515

我果然是飘了，现在竟然敢看这种竞赛题了

bsgn2017

1. =IFERROR(INDEX($A$1:$A$23,SMALL(IF(IFNA(INDEX($W$2:$W$6,IF(MATCH(INDEX($W$2:$W$6,LOOKUP($B2,SMALL($X$2:$X$6,ROW($1:$5)),6-ROW($1:$5))),$W$2:$W$6,)>MATCH($C2:$E2,$W$2:$W$6,),MATCH(INDEX($W$2:$W$6,LOOKUP($B2,SMALL($X$2:$X$6,ROW($1:$5)),6-ROW($1:$5))),$W$2:$W$6,),MATCH($C2,$W$2:$W$6,))),"")=Q$2,ROW(2:23),4^8),ROW(A1))),"")

复制代码

320火车






补充内容 (2021-2-9 17:58):

版本不高，少数显示错误

丫头的

177字符：=IFERROR(INDEX($A:$A,SMALL(IF(INDEX($B$2:$E$23,ROW($1:$22),LEFT(MMULT(N($B$2:$B$23>=INDEX($N$2:$N$6,MATCH($C$2:$E$23,$G$2:$K$2,))),{100;20;3}))+1)=G$2,ROW($2:$23)),ROW(A1))),"")去掉了几个累赘162字符：再砍下去，的换方法了
=IFERROR(INDEX($A:$A,SMALL(IF(INDEX($B$2:$E$23,ROW($1:$22),LEFT(MMULT(N($B$2:$B$23>=VLOOKUP($C$2:$E$23,$M$2:$N$6,2,)),{200;30;4})))=G$2,ROW($2:$23)),ROW(A1))),"")






补充内容 (2021-1-9 20:34):
=INDEX($A:$A,SMALL(IF(T(OFFSET($A$1,ROW($1:$22),LEFT(MMULT(N($B$2:$B$23>=VLOOKUP($C$2:$E$23,$M$2:$N$6,2,)),{200;30;4}))))=G$2,ROW($2:$23)),ROW(A1)))

补充内容 (2021-1-11 07:39):
150字符的，搞不定了，在弄，得换思路了
=INDEX($A:$A,SMALL(IF(T(OFFSET($A$1,ROW($1:$22),LEFT(MMULT(N($B$2:$B$23>=VLOOKUP($C$2:$E$23,$M:$N,2,)),{200;30;4}))))=G$2,ROW($2:$23),25),ROW(A1)))&""

补充内容 (2021-1-18 19:27):
半区域函数...哎...136字符
=INDEX($A:$A,MODE.MULT(IF(T(OFFSET($A1,ROW(1:22),LEFT(MMULT(N($B2:$B23>=VLOOKUP($C2:$E23,$M:$N,2,)),{200;30;4}))))=G2,ROW(2:23)*{1,1})))

补充内容 (2021-1-18 21:58):
=INDEX($A:$A,SORT(IF(INDEX($B:$E,ROW(2:23),LEFT(MMULT(N($B2:$B23>=SUMIF($M:$M,$C2:$E23,$N1)),{200;30;4})))=G2,ROW(2:23),26)))&""

128字符，加了个sort，365函数，右拉（点错了，跑24楼了）

补充内容 (2021-1-19 07:51):

本着不用或少用365函数的理念，.....又用了filter，现在是106字符：如下

=FILTER($A2:$A23,INDEX($B:$E,ROW(2:23),LEFT(MMULT(N($B2:$B23>=SUMIF($M:$M,$C2:$E23,$N1)),{200;30;4})))=G2)

天宇星空

=IFERROR(INDEX($A:$A,SMALL(IFERROR(IF(MID(CONCAT(IF($B$2:$B$23>=--MID(CONCAT($M$2:$N$6),FIND($C$2:$E$23,CONCAT($M$2:$N$6))+LEN($C$2:$E$23),3),$A$2:$A$23&$C$2:$E$23)),FIND($A$2:$A$23,CONCAT(IF($B$2:$B$23>=--MID(CONCAT($M$2:$N$6),FIND($C$2:$E$23,CONCAT($M$2:$N$6))+LEN($C$2:$E$23),3),$A$2:$A$23&$C$2:$E$23)))+LEN($A$2:$A$23),4)=LEFT(G$2,4),ROW($2:$23)),99),ROW(A1)))&"","")

jivy1212

1. =INDEX($A:$A,SMALL((T(OFFSET($B$1,ROW($1:$22),LEFT(MMULT(($B$2:$B$23>=SUMIF($M:$M,$C$2:$E$23,$N:$N))*{1,2,3},10^{2;1;0}))))<>G$2)/1%+ROW($2:$23),ROW(A1)))&""

复制代码

勉强有解
365可以短很多，

1. =FILTER($A2:$A23,T(OFFSET($B1,ROW(1:22),LEFT(MMULT(($B2:$B23>=SUMIF($M:$M,$C2:$E23,$N:$N))*{1,2,3},10^{2;1;0}))))=G2)

复制代码

sayhi95

1. =IFERROR(INDEX($A:$A,SMALL(IF(INDEX($C:F,ROW($2:$23),4-LEN(TEXT(MMULT(N($B$2:$B$23>=SUMIF($M:M,$C$2:$E$23,$N:N)),{100;10;1}),"0;;")))=G$2,ROW($2:$23)),ROW(A1))),"")

复制代码

重在参与，一分不好拿啊~

1. =FILTER($A2:$A23,LET(x,MMULT(N($B2:$B23>=SUMIF($M:M,$C2:$E23,$N1)),{10;1;0.1}),IF(x>9,$C2:$C23,IF(x>1,$D2:$D23,IF(x,$E2:$E23))))=G2)

复制代码

G3右拉，365LET函数，动态溢出数组，还是差点...

1. =FILTER($A2:$A23,LET(x,MMULT(N($B2:$B23>=SUMIF($M:M,$C2:$E23,$N1)),10^{3;2;1}),CHOOSE(LEN(x),,$E2:$E23,$D2:$D23,$C2:$C23))=G2)

复制代码

高版本，免强入围

1. =FILTER($A2:$A23,LET(x,MMULT(N($B2:$B23>=SUMIF($M:M,$C2:$E23,$N1)),10^{3;2;1}),INDEX($C:$F,ROW(2:23),5-LEN(x)))=G2)

复制代码

尽力了，低版本不会~LET会算定义名称吗

1. =FILTER($A1:$A23,MMULT((LEN(MMULT(N($B1:$B23>=SUMIF($M:M,$C1:$E23,$N1)),10^{3;2;1}))={4,3,2})*($C1:$E23=G2),{1;1;1}))

复制代码

1列22行区域数组公式

1. =T(INDEX($A:$A,SMALL((LEN(MMULT(N($B2:$B99>=SUMIF($M:M,$C2:$E99,$N1)),10^{3;2;1}))<>{4,3,2}*($C2:$E99=G2))/1%+ROW(2:99),ROW()-2)))

复制代码

静听溪流

=FILTER($A$2:$A23,T(INDIRECT(CHAR(LEFT(MMULT(N(SUMIF($M:$M,$C$2:$E23,$N:$N)<=$B$2:$B23),{300;40;4})+1,1)+64)&ROW(2:23),1))=G2)

象山海鲜

常规
G3:G23 区域数组 右拉

1. =INDEX($A:$A,SMALL((MMULT(($C2:$E23=G2)+($B2:$B23>=SUMIF($M:$M,$C2:$E23,$N1)),{100;10;1})&0<"2")/1%+ROW(2:23),ROW()-2))&""

复制代码

2019或 365新版本,新函数且自动扩展公式
G3，右拉

1. =FILTER($A2:$A23,MMULT(($C2:$E23=G2)+($B2:$B23>=SUMIF($M:$M,$C2:$E23,$N1)),{100;10;1})&0>"2")

复制代码

太阳之子

1. =INDEX($A:$A,SMALL(IF(T(INDIRECT(TEXT(SMALL(IF(SUMIF($M:$M,$C2:$E23,$N1)<=$B2:$B23,{3,4,5},6)+ROW(2:23)/1%,ROW(1:22)*3-2),"R0C00"),0))=G2,ROW(2:23),99),ROW()-2))&""
   
2. =INDEX($A:$A,SMALL(IF(MMULT(N(SUMIF($M:M,$C2:$E23,$N1)<=$B2:$B23),{1,2,3;0,1,2;0,0,1})*($C2:$E23=G2)=1,ROW()-1,99),ROW()-2))&""

复制代码