求助公式如何写，手动太麻烦了。感谢感谢！

tyd1203

求助一下，公式如何写，数据多的话，手动统计太头痛了。多谢多谢！

leepoison

1. =LET(l,B1:B4,k,DROP(REDUCE("",SEQUENCE(ROWS(l)),LAMBDA(x,y,VSTACK(x,IFERROR(TAKE(CHOOSECOLS(l&TRANSPOSE(l),y),y-1),"")))),2),DROP(REDUCE("",SEQUENCE(ROWS(k)),LAMBDA(a,b,VSTACK(a,IFNA(VSTACK(IFNA(HSTACK(b,DROP(REDUCE("",REGEXP(INDEX(k,b),"."),LAMBDA(m,n,VSTACK(m,FILTER(OFFSET(l,,1,,11),l=n)&""))),1)),""),""),"")))),1))

复制代码

hmy16888

不同的组合，自已去选ABCD，这样很方便

edwin11891

如果组合方式有变，如取3个组合，修改fx函数的第二参数即可。

1. =LET(fx,LAMBDA(m,n,IF(n=1,m,IF(ROWS(m)=n,TOROW(m),VSTACK(IFNA(HSTACK(@m,fx(DROP(m,1),n-1)),@m),fx(DROP(m,1),n))))),sa,fx(SEQUENCE(4),2),sb,TOCOL(HSTACK(sa,REPTARRAY({""},ROWS(sa))),,0),sc,REDUCE("",sb,LAMBDA(x,y,IFNA(VSTACK(x,IF(y="","",CHOOSEROWS(C1:G4,y))),""))),DROP(sc,1))

复制代码

你是那块小饼干

1. =DROP(REDUCE("",{1;2;3;4;5;6},LAMBDA(X,Y,LET(_T,CHOOSE(Y,"ab","ac","ad","bc","bd","cd"),IFNA(VSTACK(X,VSTACK("",HSTACK(Y,CHOOSEROWS(C1:M4,MATCH(MID(_T,SEQUENCE(LEN(_T)),1),B1:B4,0))))),"")))),2)

复制代码

mykitami

=LET(_a,DROP(REDUCE("",SEQUENCE(ROWS(B1:B4)-1),LAMBDA(x,y,VSTACK(x,INDEX(C1:G4,y,)&" "&DROP(C1:G4,y)))),1),DROP(DROP(REDUCE("",SEQUENCE(ROWS(_a)),LAMBDA(m,n,LET(_b,TRANSPOSE(TEXTSPLIT(TEXTJOIN(",",,INDEX(_a,n))," ",",",1)),IFNA(VSTACK(m,HSTACK(n,_b),""),"")))),1),-1))

袁勇

VBA