按类分项统计序号范围

备查。

对在一个单元格中列出相同“名”所对应的“序”，以“，”和“~”分割的解答公式写法之一

=LET(ca,A2:A33,cb,B2:B33,cc,C2:C33,fxc,LAMBDA(tarr,REDUCE("",tarr,LAMBDA(x,y,IF(y-(IF(MATCH(y,tarr,0)>1,INDEX(tarr,1,MATCH(y,tarr,0)-1),0))=1,IF(x="",y,CONCAT(x,"~"&y)),IF(x="",y,CONCAT(x,","&y)))))),err,LET(fxb,LAMBDA(bstr,LET(crr,UNIQUE(FILTER(cc,cb=bstr)),IFERROR(HSTACK(bstr,IFERROR(HSTACK(bstr,REGEXP(fxc(TOROW(FILTER(ca,cb=bstr))),"(?<=~)(\d+~)+",2,""),crr),"")),bstr))),fxa,LAMBDA(arr,IF(ROWS(arr)=1,fxb(arr),VSTACK(fxb(INDEX(arr,1)),fxa(DROP(arr,1))))),fxa(UNIQUE(cb))),HSTACK(CHOOSECOLS(err,2),CHOOSECOLS(err,3),CHOOSECOLS(err,4),BYROW(err,LAMBDA(ttx,REGEXP(fxc(TOROW(FILTER(ca,(cb=CHOOSECOLS(ttx,1))*(cc=CHOOSECOLS(ttx,4))))),"(?<=~)(\d+~)+",2,"")))))