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

wyslw2004

=MAP(DROP(A2:A568,-1),DROP(A2:A568,1),LAMBDA(x,y,IF(y-x=1,"",IF(y-x=2,y-1,x+1&"-"&y-1))))
这个意思吗？

1433405066

wyslw2004 发表于 2025-4-10 21:03
=MAP(DROP(A2:A568,-1),DROP(A2:A568,1),LAMBDA(x,y,IF(y-x=1,"",IF(y-x=2,y-1,x+1&"-"&y-1))))
这个意思 ...

大佬牛逼，就是要这个效果

1433405066

已解决，谢谢各位大佬！

xtmyd5156

无佬的简洁

1433405066

13570449347 发表于 2025-4-10 17:56
如附件123456

老哥，你的附件里我没看到公式啊，然后我改了A列的数据，刷新后也没变

niko88819

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

老师，您附件还上密码喔，可以分享学习吗？

一杯绿茶来了

=LET(s,ROW(1:4001),TEXTJOIN(",",,FILTER(s,COUNTIF(A2:A568,s)=0)))
=TEXTJOIN("、",,IF(A3:A568-A2:A567=1,"",A2:A567&"-"&A3:A568))

mykitami

=LET(_a,","&TEXTJOIN(",",,A2:A568)&",",_b,SEQUENCE(MAX(A2:A568)),_c,FILTER(_b,ISERROR(FIND(","&_b&",",_a))),DROP(GROUPBY(_c-SEQUENCE(ROWS(_c)),_c,LAMBDA(x,@x&IF(ROWS(x)>1,-MAX(x),)),0,0),,1))

niko88819

1. Sub niko()
   
2.     Dim rng As Range, cell As Range
   
3.     Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
   
4.     Dim minVal As Long, maxVal As Long, val As Long
   
5.     Dim i As Long, count As Long
   
6.     Dim missing() As Long
   
7.     Dim startRange As Long, endRange As Long, outputRow As Long
   
8.     Dim ws As Worksheet: Set ws = ActiveSheet
   
9.     Dim lastRow As Long
   
10.     lastRow = ws.Cells(ws.Rows.count, 1).End(xlUp).Row
    
11.     If lastRow < 2 Then Exit Sub
    
12.     Set rng = ws.Range("A2:A" & lastRow)
    
13.     On Error Resume Next
    
14.     minVal = Application.WorksheetFunction.Min(rng)
    
15.     maxVal = Application.WorksheetFunction.Max(rng)
    
16.     On Error GoTo 0
    
17.     If minVal = 0 And maxVal = 0 Then Exit Sub
    
18.     For Each cell In rng
    
19.         If IsNumeric(cell.Value) Then
    
20.             val = CLng(cell.Value)
    
21.             If val > 0 Then dict(val) = True
    
22.         End If
    
23.     Next
    
24.     ReDim missing(1 To maxVal - minVal + 1)
    
25.     count = 0
    
26.     For i = minVal To maxVal
    
27.         If Not dict.exists(i) Then
    
28.             count = count + 1
    
29.             missing(count) = i
    
30.         End If
    
31.     Next
    
32.     If count = 0 Then
    
33.         ws.Range("B2").Value = "No missing numbers"
    
34.         Exit Sub
    
35.     End If
    
36.     ReDim Preserve missing(1 To count)
    
37.     outputRow = 2
    
38.     startRange = missing(1)
    
39.     endRange = startRange
    
40.     For i = 2 To count
    
41.         If missing(i) = endRange + 1 Then
    
42.             endRange = missing(i)
    
43.         Else
    
44.             ws.Cells(outputRow, 2).Value = IIf(startRange = endRange, startRange, startRange & "-" & endRange)
    
45.             outputRow = outputRow + 1
    
46.             startRange = missing(i)
    
47.             endRange = startRange
    
48.         End If
    
49.     Next
    
50.     ws.Cells(outputRow, 2).Value = IIf(startRange = endRange, startRange, startRange & "-" & endRange)
    
51. End Sub

复制代码

niko88819

来个公式解法
B2 公式

1. =LET(rng,A2:A568,s,DROP(rng,-1)+1,e,DROP(rng,1)-1,f,IF(e<s,"",IF(e=s,s,s&"-"&e)),FILTER(f,f<>""))

复制代码