lambda+byrow

xiezuowei

老师们有空帮我看一下，利用lambda+byrow函数能解吗

无关岁月

1. =LET(_a,BYROW(B2:G6,LAMBDA(_b,MAX(_b))),IF(COUNTIF(B2:G6,_a)>1,_a,""))

复制代码

小凡、、、

一定要用lambda不可吗？

xiaoai007

1. =TEXTSPLIT(TEXTJOIN("-",,BYROW(B2:G6,LAMBDA(x,TEXTJOIN(",",,IF(x=MAX(x),x,""))))),",","-",,,"")

复制代码

mykitami

=IFNA(DROP(REDUCE("",A2:A6,LAMBDA(x,y,LET(_a,OFFSET(y,,1,,6),VSTACK(x,FILTER(_a,_a=MAX(_a)))))),1),"")

mykitami

=IFNA(DROP(REDUCE("",BYROW($B$2:$G$6,LAMBDA(x,TEXTJOIN(" ",,FILTER(x,x=MAX(x))))),LAMBDA(y,z,VSTACK(y,TEXTSPLIT(z," ",,1)))),1),"")

喂你吃粑粑

1. =IFERROR(INDIRECT("R"&ROW(A2)&"C"&LARGE(($B2:$G2=MAX($B2:$G2))*COLUMN($B2:$G2),COLUMN(A1)),0),"")

复制代码

boyuanoffice

可惜Byrow的每一行只能返回单值, 要是能返回数组就不用后续又是合并又是拆分的了.

1. =TEXTSPLIT(CONCAT(BYROW(B2:G6,LAMBDA(r,ARRAYTOTEXT(FILTER(r,r=MAX(r)))))&";"),", ",";",1,,"")

复制代码

1. =REDUCE("Max",BYROW(B2:G6,LAMBDA(r,ARRAYTOTEXT(FILTER(r,r=MAX(r))))),LAMBDA(x,y,IFNA(VSTACK(x,TEXTSPLIT(y,", ")),"")))

复制代码

加了一个楼下的公式, 留此备用, 这个用Offset引用数据行不用合并拆分.

1. =REDUCE("Max",B2:B6,LAMBDA(x,y,LET(r,OFFSET(y,,,,6),IFNA(VSTACK(x,FILTER(r,r=MAX(r))),""))))

复制代码

咔咔乱坠

这个情景reduce比byrow好用，byrow只能返回单值

1. =IFNA(REDUCE(I1,B2:B6,LAMBDA(x,y,VSTACK(x,LET(a,OFFSET(y,,,,6),FILTER(a,MAX(a)=a))))),"")

复制代码

或者递归也可以哦
递归1

1. =LET(a,B2:G2,f,LAMBDA(f,n,IF(AND(LARGE(a,n)>=a),f(f,n+1),n)),LARGE(a,SEQUENCE(,f(f,1)-1)))

复制代码

递归2

1. =LET(a,B2:G2,f,LAMBDA(f,n,IF(AND(LARGE(a,n)>=a),HSTACK(LARGE(a,n),f(f,n+1)),"")),f(f,1))

复制代码

或者最基本的filter

1. =LET(f,LARGE(B2:G2,COLUMN(A:C)),FILTER(f,@f=f))

复制代码