[开_154]鼠年春节特辑，诸侯争霸二

delete_007

区域数组公式（113字符）：
=T(INDIRECT(TEXT(SMALL(IF(COLUMN(A:G)<=F2:F4,DATE({1;2;3},MATCH(E2:E4&"?",1:1,),COLUMN(B:H))),ROW()-1),"rdcm"),))

piny

H2=INDIRECT(TEXT(MOD(SMALL((ROW($1:$7)>VLOOKUP(T(IF(1,LEFT(A$2:C$8))),E:F,2,))/1%%+MATCH(LEFT(A$2:C$8),E:E,)/1%+ROW($2:$8)*10+{1,2,3},ROW(A1)),100),"!R0C0"),) 數組 下拉
=

H2:H8=T(INDIRECT(TEXT(MOD(SMALL((ROW(1:7)>VLOOKUP(T(IF(1,LEFT(A2:C8))),E:F,2,))/1%%+MATCH(LEFT(A2:C8),E:E,)/1%+ROW(2:8)*10+{1,2,3},ROW(1:7)),100),"!R0C0"),)) 區域數組

sbdk007

大家新年快乐
先贴一个
=INDIRECT(TEXT(SMALL(IFERROR(DATE(MATCH(LEFT(A$1:C$1),E$2:E$4,),{1,2,3},IF(ROW($1:7)<=MOD(SMALL(--(MATCH(E$2:E$4&"*",A$1:C$1,)&F$2:F$4),{1,2,3}),10),ROW($2:8),"")),""),ROW(A1)),"rdcm"),)
再精简一下，116字
=INDIRECT(TEXT(SMALL(IF(COLUMN(A:G)<=F$2:F4,DATE({1;2;3},MATCH(E$2:E4&"*",A$1:C$1,),COLUMN(B:H))),ROW(A1)),"rdcm"),)
整行引用还可少两个字符
=INDIRECT(TEXT(SMALL(IF(COLUMN(A:G)<=F$2:F4,DATE({1;2;3},MATCH(E$2:E4&"*",$1:$1,),COLUMN(B:H))),ROW(A1)),"rdcm"),)

太阳之子

1. =HLOOKUP(LOOKUP(ROW()-2,SUMIF(OFFSET(F$1,,,ROW($1:4)),">0"),E$2:E5)&"*",A:C,ROW()-LOOKUP(ROW()-2,SUMIF(OFFSET(F$1,,,ROW($1:4)),">0")),)
   
2. =HLOOKUP(LOOKUP(ROW()-2,SUMIF(OFFSET(F1,,,ROW(1:4)),">0"),E2:E5)&"*",A:C,ROW()-LOOKUP(ROW()-2,SUMIF(OFFSET(F1,,,ROW(1:4)),">0")),)
   
3. =HLOOKUP(MID(REPT(E2,F2)&REPT(E3,F3)&REPT(E4,F4),ROW()-1,1)&"*",A:C,ROW()-LOOKUP(ROW()-2,SUMIF(OFFSET(F1,,,ROW(1:4)),">0")),)
   
4. =HLOOKUP(MID(CONCAT(REPT(E2:E4,F2:F4)),ROW()-1,1)&"*",A:C,ROW()-LOOKUP(ROW()-2,SUMIF(OFFSET(F1,,,ROW(1:4)),">0")),)

复制代码

公式1，下拉公式；公式2和公式3，区域数组，假数组。公式4用了2019版函数CONCAT

wodewan

=INDIRECT(TEXT(RIGHT(SMALL(IF($F$2:$F$4<COLUMN(A:E),10^3,COLUMN(B:F)&FIND($E$2:$E$4,"秦楚魏"))+{1;2;3}*100,ROW(A1)),2),"R0C0"),)

象山海鲜

1. =T(INDIRECT(TEXT(SMALL((SUMIF(E:E,LEFT(A2:C8),F:F)<ROW(1:7))+TIME(MATCH(LEFT(A2:C8),E:E,),{1,2,3},ROW(2:8)),ROW()-1),"rscm"),))

复制代码

1. =VLOOKUP("*",T(OFFSET(A$1,F$2:F$4-(0&F$2:F$4-COUNTIF(H$1:H1,E$2:E$4&"*")-1),MATCH(E$2:E$4&"*",$1:$1,)-1)),1,)

复制代码

@刘卓

=HLOOKUP(LOOKUP(ROW(A1),MMULT(N({1;2;3}>{1,2,3}),F$2:F$4)+1,E$2:E4)&"*",A:C,ROW(A2)-LOOKUP(ROW(A1)-1,MMULT(N({1;2;3}>{1,2,3}),F$2:F$4)),)

sayhi95

7行1列区域数组，130入围了

1. =T(INDIRECT(TEXT(MOD(SMALL(IFERROR({1;2;3}/(COLUMN(A:G)<=F2:F4)%+COLUMN(B:H)*10+MATCH(E2:E4&"*",1:1,),""),ROW()-1),100),"R0C0"),))

复制代码

省个IFERROR

1. =T(INDIRECT(TEXT(RIGHT(LARGE(IF(COLUMN(A:G)<=F2:F4,-({1;2;3}&COLUMN(B:H)&MATCH(E2:E4&"*",1:1,))),ROW()-1),2),"R0C0"),))

复制代码

CheryBTL

之前未注意三国的顺序也是变动的。。。。 更正如下：


区域数组108字符：

1. =T(INDIRECT(TEXT(RIGHT(SMALL((F2:F4<COLUMN(A:G))+COLUMN(B:H)%+FIND(E2:E4,"秦楚魏")*0.101,ROW()-1),2),"R0C0"),))

复制代码

cleverzhzhf

很久没做竞赛，人变懒了，水平也退化了，先凑出一个来吧：=INDEX(A:C,COUNTIF(H$1:H1,INDEX(E$2:E$4,MATCH(ROW(1:1)-1,SUBTOTAL(9,OFFSET(F$1,,,ROW($1:$3))))))+2,MATCH(INDEX(E$2:E$4,MATCH(ROW(1:1)-1,SUBTOTAL(9,OFFSET(F$1,,,ROW($1:$3)))))&"*",A$1:C$1,))