根据已有序号列判断空缺序号（或空缺区间）

1433405066

问题描述如图，感谢大佬解答！

1433405066

已解决，谢谢各位大佬！

溺水鱼

1. =LET(a,A2:A568,b,SEQUENCE(MAX(a)-MIN(a)+1,,MIN(a)),FILTER(b,NOT(COUNTIF(a,b))))

复制代码

燕山湾

=LET(x,MIN(A2:A568),y,MAX(A2:A568),FILTER(SEQUENCE(y-x+1,,x),ISERROR(MATCH(SEQUENCE(y-x+1,,x),A:A,0))))
水平有限

hugeinfo

=LET(a_,MIN(A2:A568),b_,MAX(A2:A568),a,FILTER(SEQUENCE(b_,,a_,1),NOT(ISNUMBER(MATCH(SEQUENCE(b_,,a_,1),A2:A568,)))),b,a-SEQUENCE(ROWS(a)),DROP(GROUPBY(b,a,LAMBDA(x,IF(ROWS(x)=1,MAX(x),MIN(x)&"-"&MAX(x))),,0),,1))

szl3178

1. =LET(x,FILTER(SEQUENCE(MAX(A2:A568)-1+1,,1),ISNA(MATCH(SEQUENCE(MAX(A2:A568)-1+1,,1),A2:A568,))),DROP(GROUPBY(x-SEQUENCE(ROWS(x),,1),x,LAMBDA(x,TEXTJOIN("-",,UNIQUE(HSTACK(MIN(x),MAX(x)),1))),,0),,1))

复制代码

13570449347

123456789

13570449347

13570449347 发表于 2025-4-10 17:56
123456789

如附件123456

无关岁月

1. =LET(x,SEQUENCE(A568),FILTER(x,1-BYROW(x=TOROW(A2:A568),OR)))

复制代码

daiyanwei

=FILTER(SEQUENCE(4001),COUNTIF(A2:A568,SEQUENCE(4001))=0)

无关岁月

空缺区间

1. =LET(x,SEQUENCE(A568),y,FILTER(x,1-BYROW(x=TOROW(A2:A568),OR)),DROP(REGEXP(GROUPBY(SCAN(,y-VSTACK(0,DROP(y,-1))-1,SUM),-y,CONCAT),"^-|-.+(?=-)",2),-1,1))

复制代码