统计表（三）求公式

huangrk

附件  统计表（三）求公式，结果  参考统计表（一）、统计表（二）

inrever

1. =DROP(REDUCE("",B33:B39,LAMBDA(x,y,VSTACK(x,IFERROR(LET(a,WRAPROWS(TOROW(CHOOSEROWS(C3:K9,MATCH(y,B3:B9,)),1),3),MAP(C32:M32,LAMBDA(m,BYCOL(IF(BYROW(DROP(a,,1),LAMBDA(n,AND(m>=MIN(n),m<=MAX(n)))),TAKE(a,,1),""),CONCAT)))),"")))),1)

复制代码

part98

=DROP(REDUCE(0,B33:B39,LAMBDA(x,y,VSTACK(x,LET(s,WRAPROWS(TOROW(FILTER(C3:K9,B3:B9=y),3),3),BYCOL(IF(ISNUMBER(VDB(1,,CHOOSECOLS(s,3),CHOOSECOLS(s,2),C32:M32)),CHOOSECOLS(s,1),""),CONCAT))))),1)

博爱风雨同舟

=LET(s,BYROW(WRAPROWS(TOCOL(IF(ISNUMBER(C3:K9),C3:K9,0/0),2),2),LAMBDA(X,NPV(-2,X)))+1,PIVOTBY(TOCOL(IF(COLUMN(A:E)<=s,TOCOL(IF(ISTEXT(C3:K9),B3:B9,0/0),2),J),2),--TEXTSPLIT(TEXTJOIN("、",,MAP(s,TAKE(WRAPROWS(TOCOL(IF(ISNUMBER(C3:K9),C3:K9,0/0),2),2),,1),LAMBDA(X,Y,TEXTJOIN("、",,SEQUENCE(,X)-1+Y)))),,"、"),TOCOL(IF(COLUMN(A:E)<=s,TOCOL(IF(ISTEXT(C3:K9),C3:K9,0/0),2),J),3),ARRAYTOTEXT,,0,,0))
一步到位

你是那块小饼干

1. =IFERROR(DROP(REDUCE("",B3:B9,LAMBDA(X,Y,VSTACK(X,HSTACK(Y,LET(T,WRAPROWS(TOCOL(FILTER(C3:K9,B3:B9=Y,""),3),3),_T,INDEX(T,,1),IFNA(VLOOKUP($C$22:$M$22,REDUCE("",_T,LAMBDA(_X,_Y,VSTACK(_X,IFNA(HSTACK(SEQUENCE(SUM(INDEX(T,MATCH(_Y,_T,0),{2,3})*{-1,1},1),,INDEX(T,MATCH(_Y,_T,0),2),1),_Y),_Y)))),2,0),"")))))),1),"")

复制代码