如何自动生成合并后的客户名单，并按业绩从大到小顺序排列

alan57

1. =LET(_c,SORT(VSTACK(HSTACK(F4:F7,MAP(F4:F7,LAMBDA(x,SUM(FILTER(D4:D18,MMULT((C4:C18=INDEX(C4:C18,MATCH(TEXTSPLIT(x,"/"),C4:C18,0)))*1,ROW(1:2)^0)=1))))),LET(_b,TEXTSPLIT(TEXTJOIN(",",1,TEXTSPLIT(C4:C18,DROP(REDUCE("",F4:F7,LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,,"/")))),1))),,","),HSTACK(_b,MAP(_b,LAMBDA(x,FILTER(D4:D18,C4:C18=x)))))),2,-1),VSTACK(H3:J3,HSTACK(SEQUENCE(ROWS(_c)),_c)))

复制代码

hcm19522

E4:E18=IFERROR(VLOOKUP("*"&C4&"*",F$4:F$7,1,),C4)

I4:I15{=IFERROR(INDEX(E:E,RIGHT(LARGE(IF(MATCH(E$4:E$18,E:E,)=ROW($4:$18),MMULT(N(E$4:E$18=TRANSPOSE(E$4:E$18)),$D$4:$D$18)/1%+ROW($4:$18)),ROW(A1)),2)),"")

J4:J15=IF(I4="","",SUMIF(E:E,I4,D:D))

13600903288

hcm19522 发表于 2022-11-6 10:25
E4:E18=IFERROR(VLOOKUP("*"&C4&"*",F$4:F$7,1,),C4)

I4:I15{=IFERROR(INDEX(E:E,RIGHT(LARGE(IF(MATCH( ...

谢谢！这个公式比较好理解