根据条件取相应位数

eastwin · 发表于 2023-6-27 19:19

本帖最后由 eastwin 于 2023-6-27 21:47 编辑

如果B27、B28的值满足：同时都大于B29，则将C列的Number从后往前放置于E2:AQ2,求函数公式

绊成ρìи · 发表于 2023-6-27 20:00

=LEFT(RIGHT(SUBSTITUTE(REPT(TEXTJOIN(",",1,$C2:$C26),AND($B27:$B28>$B29)),",",REPT(" ",99)),COLUMN(A1)*99),99)/1

复制代码

w4275 · 发表于 2023-6-27 20:18

=TEXTSPLIT(TEXTJOIN(",",,IFERROR(INDEX(C:C,SORT(IF(AND(B27:B28>B29)*(C2:C28<>""),ROW(2:28),""),1,-1)),"")),",")

复制代码

quqiyuan · 发表于 2023-6-27 20:39

本帖最后由 quqiyuan 于 2023-6-27 20:40 编辑

=IF(AND(B27:B28>B29),TEXTSPLIT(TEXTJOIN(",",1,T(OFFSET($C$29,-ROW(1:27),))),","))

alan57 · 发表于 2023-6-27 21:22

=LET(_a,TOCOL(IF(AND(B27:B28>B29)*(C2:C28<>""),C2:C28,\),3),_b,SORTBY(_a,-SEQUENCE(ROWS(_a))),DROP(REDUCE(0,_b,LAMBDA(x,y,HSTACK(x,TEXTSPLIT(y,",",,1)))),,1))

复制代码

弄个复杂点的。

minchar · 发表于 2023-6-27 22:10

=IF(AND(B27:B28>B29),TEXTSPLIT(TEXTJOIN(",",1,SORTBY(C2:C28,1/ROW(C2:C28))),","),"")

		自动登录	找回密码
密码			免费注册

[已解决] 根据条件取相应位数