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

LWQ张无忌

=OFFSET($A$1,--MID(CONCAT(IF(MID(REPT($E$2:$E$4,$F$2:$F$4),COLUMN(A:G),1)<>"",COLUMN(A:G),"")),ROW(A1),1),MATCH(MID(CONCAT(REPT($E$2:$E$4,$F$2:$F$4)),ROW(A1),1)&"*",$1:$1,)-1)
只能减几个，再去绕
=INDEX(A:C,MID(CONCAT(IF(MID(REPT($E$2:$E$4,$F$2:$F$4),COLUMN(A:G),1)<>"",COLUMN(A:G),"")),ROW(A1),1)+1,MID(CONCAT(REPT(MATCH($E$2:$E$4&"*",$1:$1,),$F$2:$F$4)),ROW(A1),1))

继续绕
=INDIRECT(TEXT(MOD(SMALL(IFERROR(--TEXT({1;2;3}&COLUMN(B:H)&MID(REPT(MATCH(E$2:E$4&"*",$1:$1,),F$2:F$4),COLUMN(A:E),1),"[>100]"),""),ROW(A1)),100),"R0C0"),)

delete_007

红包已经发完，恭祝大家新年快乐，远离疾疫，阖家安康。

@刘卓

还得重新学习下老师们的思路

HoverCopper

H2单元格  =IF(($F$2-(ROW()-1))>=0,HLOOKUP("*"&$E$2&"*",$A$1:$C$8,ROW(),0),IF(($F$3-(ROW()-1-$F$2))>=0,HLOOKUP("*"&$E$3&"*",$A$1:$C$8,ROW()-$F$2,0),HLOOKUP("*"&$E$4&"*",$A$1:$C$8,ROW()-$F$2-$F$3,0)))
新手上路！

xingqiliu666

1. =INDEX(INDEX(IF(IF(TRANSPOSE(SEQUENCE(3)^0),SEQUENCE(7))<=IF(SEQUENCE(7)^0,VLOOKUP(LEFT($A$2:$C$2,1),$E$2:$F$4,2,0)),$A$2:$C$8,""),MOD(SEQUENCE(7*3)-1,7)+1,ROUNDUP(SEQUENCE(7*3)/7,0)),SMALL(IF(INDEX(IF(IF(TRANSPOSE(SEQUENCE(3)^0),SEQUENCE(7))<=IF(SEQUENCE(7)^0,VLOOKUP(LEFT($A$2:$C$2,1),$E$2:$F$4,2,0)),$A$2:$C$8,""),MOD(SEQUENCE(7*3)-1,7)+1,ROUNDUP(SEQUENCE(7*3)/7,0))<>"",SEQUENCE(7*3)),ROW(A1)))

复制代码

调整顺序后的公式：

1. =INDEX(INDEX(IF(IF({1,1,1},SEQUENCE(7))<=IF(SEQUENCE(7)^0,TRANSPOSE($F$2:$F$4)),CHOOSE(TRANSPOSE(MATCH($E$2:$E$4&"*",$A$2:$C$2,0)),$A$2:$A$8,$B$2:$B$8,$C$2:$C$8),""),MOD(SEQUENCE(21)-1,7)+1,ROUNDUP(SEQUENCE(21)/7,)),SMALL(IF(INDEX(IF(IF({1,1,1},SEQUENCE(7))<=IF(SEQUENCE(7)^0,TRANSPOSE($F$2:$F$4)),CHOOSE(TRANSPOSE(MATCH($E$2:$E$4&"*",$A$2:$C$2,0)),$A$2:$A$8,$B$2:$B$8,$C$2:$C$8),""),MOD(SEQUENCE(21)-1,7)+1,ROUNDUP(SEQUENCE(21)/7,))<>"",SEQUENCE(21)),ROW(A1)))

复制代码

solextrade

xingqiliu666 发表于 2022-12-14 18:08

排列顺序不太对哈

alan57

1. =LET(fx,LAMBDA(x,y,TAKE(INDEX(A2:C8,,XMATCH(x,LEFT(A1:C1,1))),y)),VSTACK(fx(E2,F2),fx(E3,F3),fx(E4,F4)))

复制代码