做个记号,这个想的是用text2参数组来解决问题,但2参数组的构建一直都没有成功,想了好久,放弃了
=T(INDIRECT(TEXT(RIGHT(SMALL(IFERROR(TEXT(CHOOSE({1,2,3},P4:P13,T4:T13,X4:X13),"[<="&CEILING(SUMIF(C:C,{"吃*","酒*","玩*"},D1),INT(D4/2))&"]")*10^FIND({"吃","酒","玩"},10^3&C4&C5&C6)+ROW(4:13)/1%+IF(SUMIF(C:C,{"吃*","酒*","玩*"},D$1)>=CHOOSE({1,2,3},P4:P13,T4:T13,X4:X13),{17,21,25},66),""),1/MOD({1,2,3,4},5-(D4<8))^-1+INT(D4/2)*(ROW()-4)),4),"r0c00"),))&""
贴脸抄M同学的优秀解
=T(INDIRECT(TEXT(SMALL(IFERROR(MATCH(Q3:Y3,C:C,)+TIME(TEXT(P4:X13,"[<="&CEILING(SUMIF(C:C,Q3:Y3,D1),INT(D4/2))&"]"),ROW(4:13),2^(P4:X13>SUMIF(C:C,Q3:Y3,D1))*COLUMN(Q:Y)),""),1/MOD({1,2,3,4},5-(D4<8))^-1+INT(D4/2)*(ROW()-4)),"rmcs"),))
下面这种方式也不失为一种方案,但是构建没有短的,就不贴了。
![](http://files.c.excelhome.net/forum/201911/23/152922n4euzllyl4qbl46y.gif)
|