算区间号

dongfangxielong

无关岁月

1. =LET(_x,A2:A10,_y,C2:C10,REDUCE(F1:H1,UNIQUE(_x),LAMBDA(_a,_b,VSTACK(_a,HSTACK(_b,TRIM(CONCAT(IFERROR(MID("- ",3-MMULT(COUNTIFS(_x,_b,_y,_y+{-1,1}),{2;1}),1)&1/IF(_x=_b,_y)^-1,""))),COUNTIF(_x,_b))))))

复制代码

飞天篮球猪

一杯绿茶来了

=LET(a,UNIQUE(A2:A10),DROP(REDUCE("",a,LAMBDA(x,y,LET(_a,FILTER(C2:C10,A2:A10=y),_b,ROWS(_a),_c,_a-SEQUENCE(_b),j,TEXTJOIN(" ",,MAP(UNIQUE(_c),LAMBDA(z,TEXTJOIN("-",,QUARTILE(FILTER(_a,_c=z),{0,4}))))),VSTACK(x,HSTACK(y,j,_b))))),1))

浮生若梦~~~

=VSTACK(F1:H1,LET(a,A2:A10,b,UNIQUE(a),HSTACK(b,MAP(b,LAMBDA(x,LET(c,FILTER(C2:C10,a=x),MID(CONCAT(IFS(ISNA(MATCH(c-1,c,))," "&c,ISNA(MATCH(c+1,c,)),-c,1,"")),2,99)))),MAP(b,LAMBDA(x,SUM(N(a=x)))))))

edwin11891

拉个火车，没有任何技巧，纯蛮干：

1. =LET(Rsa,A2:A10,Rsb,C2:C10,REDUCE({"单位","证书区间号","数量"},UNIQUE(Rsa),LAMBDA(x,y,VSTACK(x,LET(t,SORT(FILTER(Rsb,Rsa=y)),s,DROP(REDUCE(0,SEQUENCE(ROWS(t)),LAMBDA(_x,_y,VSTACK(_x,IF(_y=1,1,IF(INDEX(t,_y)=INDEX(t,_y-1)+1,TAKE(_x,-1),TAKE(_x,-1)+1))))),1),m,TEXTJOIN("，",,MAP(UNIQUE(s),LAMBDA(_m,LET(sLet,FILTER(t,s=_m),sMin,MIN(sLet),sMax,MAX(sLet),IF(sMin=sMax,sMin,sMin&"-"&sMax))))),HSTACK(y,m,ROWS(FILTER(Rsb,Rsa=y))))))))

复制代码

comeet

1. =LET(t,DROP(GROUPBY(HSTACK(SCAN(0,(A1:A9<>A2:A10)+(C1:C9<>C2:C10-1)>0,SUM),A2:A10),C2:C10,HSTACK(LAMBDA(x,IF(COUNT(x)>1,MIN(x)&"-"&MAX(x),MIN(x))),COUNT),0,0),1,1),HSTACK(GROUPBY(TAKE(t,,1),CHOOSECOLS(t,2),LAMBDA(y,TEXTJOIN(" ",1,y)),0,0),TAKE(GROUPBY(TAKE(t,,1),TAKE(t,,-1),SUM,0,0),,-1)))

复制代码

也蛮干一下