找出一行中前3个大于0小于300的数相加之和小于500，高亮显示3个数，再在后方逐列显示

fthx1

请大神帮忙，找出一行中前3个大于0且小于300的数，使这3个数相加之和小于500。在原数据列中高亮显示3个数，再在后方的列中逐列显示这3个数。 工作簿1 - 副本.7z (7.94 KB, 下载次数: 16)

2024-11-28 02:02 上传

点击文件名下载附件

zwzc

=TAKE(TEXTSPLIT(REDUCE("0-",B2:K2,LAMBDA(x,y,LET(a,--TEXTBEFORE(x,"-"),b,TEXTAFTER(x,"-"),IF(y<=0,x,IF(a+y<=500,(a+y)&"-"&TEXTJOIN("/",,b,y),x))))),"/","-"),-1,3)

zwzc

条件格式
=(SUM(ISNUMBER(FIND($B2:B2,TEXTJOIN("-",,$M$2:$O$2),1))*($B2:B2))<=500)*(B2>0)*(ISNUMBER(FIND(B2,TEXTJOIN("-",,$M2:$O2),1)))=1

leepoison

=LET(x,B2:K2,
         y,FILTER(x,(x>0)*(x<500)),
         z,DROP(y,,1),
         a,@y,
         b,@FILTER(z,z<=500-a),
         w,MATCH(b,z,0)+1,k,DROP(y,,w),
         c,@FILTER(k,k<=500-a-b),
         HSTACK(a,b,c)
)

mykitami

M2公式
=LET(s,DROP(REDUCE(0,FILTER(B2:L2,(B2:L2>0)*(B2:L2<300)),LAMBDA(x,y,IF(SUM(x)+y<500,HSTACK(x,y),x))),,1),IF(COLUMNS(s)>=3,TAKE(s,,3),""))
下拉
条件格式公式
=MATCH(B2,$M2#,)*(COUNT(TAKE(MATCH($B2:$L2,$M2#,),,COLUMN(B2)-1))<=3)

hugeinfo

=TAKE(DROP(REDUCE(0,FILTER(B2:K2,(B2:K2<>0)*(B2:K2<300)),LAMBDA(x,y,IF(SUM(x)+y<500,HSTACK(x,y),x))),,1),,3)

yllmessi

=CHOOSECOLS(TEXTSPLIT(REDUCE("0-",B2:K2,LAMBDA(x,y,IF((y>0)*(y<300)*((SUM(--TEXTSPLIT(x,"-",,TRUE))+y)<500),x&y&"-",x))),"-"),2,3,4)

我走我流

=DROP(REDUCE(0,FILTER(B2:K2,(B2:K2<300)*B2:K2),LAMBDA(x,y,IF((SUM(x,y)<=500)*(COUNT(x)<4),HSTACK(x,y),x))),,1)

hxj119

1. =LET(a,B2:K2,b,FILTER(a,(a>0)*(a<300)),f,LAMBDA(x,IF(SUM(TAKE(x,,3))<500,TAKE(x,,3),f(DROP(x,,1)))),f(b))

复制代码