函数题目！

=REDUCE("班级",D3:D22,LAMBDA(x,y,VSTACK(x,IF(LOOKUP(,-1/(x>""),x)&OFFSET(y,-1,)=CONCAT(XLOOKUP(y,B3:B8,A3:B8,,,-1)),"",INDEX(FILTER(A3:A8,B3:B8=y),MOD(SUM((OFFSET(y,-1,):D2=y)*(x>"")),SUM(N(y=B3:B8)))+1)))))

=LET(cs,BYROW(DROP(REDUCE("",UNIQUE(D3:D22),LAMBDA(m,n,HSTACK(m,IF(D3:D22<>n,"",SCAN("",D3:D22,LAMBDA(x,y,LET(gs,COUNTIF(B3:B8,y),IF((OFFSET(y,-1,)<>n)*(x>=gs)*(y=n),1,IF(y=n,x+1,x))))))))),,1),SUM),tj,MAP(B3:B8,LAMBDA(x,COUNTIF(B3:x,x))),XLOOKUP(D3:D22&cs,B3:B8&tj,A3:A8,""))
有点长：主要是第二个辅助列有点长，费脑子。思路就是红框的那个编号数字。BYROW(DROP(REDUCE("",UNIQUE(D3:D22),LAMBDA(m,n,HSTACK(m,IF(D3:D22<>n,"",SCAN("",D3:D22,LAMBDA(x,y,IF((OFFSET(y,-1,)<>n)*(x>=3)*(y=n),1,IF(y=n,x+1,x)))))))),,1),SUM)
这段是辅助列的公式

=MAP(D3:D22,REDUCE(1,ROW(2:20),LAMBDA(s,n,LET(p,INDEX(D3:D22,n),q,IFNA(-LOOKUP(,-s/(OFFSET(D3,,,n)=p)),),VSTACK(s,IF(p=INDEX(D3:D22,n-1),TAKE(s,-1)+1,IF(q<COUNTIF(B3:B8,p),q+1,1)))))),LAMBDA(x,y,IFERROR(INDEX(FILTER(A3:A8,B3:B8=x),y),"")))

扫二维码下载贴吧客户端

下载贴吧APP
看高清直播、视频！

6回复贴，共1页

<<返回excel吧

分享到:

日	一	二	三	四	五	六