如何简化连续编号

aman1516

ynzsvt 发表于 2024-11-9 17:12
就最后一步改了改。

第二行：'=SUM(=SUM(A3,A7:A10,A15,A18:A20,A28:A30,A32:A32)   这种情况，最末格式不对，还得再加个判断，我就是判断与处理过多了

aman1516

ynzsvt 发表于 2024-11-9 17:12
就最后一步改了改。

If f = True Then
  s = "=SUM(" & Sr(0) & ":" & b
Else
  If Sr(t - 1) <> a Then s = s & ":" & b
End If

cidanji

aman1516 发表于 2024-11-9 19:30
非常巧妙的方法！但仅适合符合单元格式字母与数字组合的字符串，非单元格字母前缀，或字母后缀时，就会有 ...

1. Function ss(str)
   
2.     With CreateObject("VBScript.RegExp")
   
3.         .Pattern = "([^\d,]+)?([\d]+)([^\d,]+)?(,|:|$)"
   
4.         .Global = True
   
5.         s = .Replace(str, "A$2$4")
   
6.         s = Union(Range(Split(s, ",")(0)), Range(s)).Address(0, 0)
   
7.         ss = .Replace(s, .Replace(Split(str, ",")(0), "$1") & "$2" & .Replace(Split(str, ",")(0), "$3") & "$4")
   
8.     End With
   
9. End Function

复制代码

perfect131

cidanji 发表于 2024-11-9 20:46

你没考虑range 255限制 听说wps 对range没限制 但ms有
A3,A7,A8,A9,A10,A15,A18,A19,A20,A28,A29,A30,A32,A3,A7,A8,A9,A10,A15,A18,A19,A20,A28,A29,A30,A3,A7,A8,A9,A10,A15,A18,A19,A20,A28,A29,A30,A3,A7,A8,A9,A10,A15,A18,A19,A20,A28,A29,A30,A3,A7,A8,A9,A10,A15,A18,A19,A20,A28,A29,A30,A3,A7,A8,A9,A10,A122,A9,A10,A222

256字符 ss会报错



A3,A7,A8,A9,A10,A15,A18,A19,A20,A28,A29,A30,A32,A3,A7,A8,A9,A10,A15,A18,A19,A20,A28,A29,A30,A3,A7,A8,A9,A10,A15,A18,A19,A20,A28,A29,A30,A3,A7,A8,A9,A10,A15,A18,A19,A20,A28,A29,A30,A3,A7,A8,A9,A10,A15,A18,A19,A20,A28,A29,A30,A3,A7,A8,A9,A10,A122,A9,A10,A22


255可以

aman1516

perfect131 发表于 2024-11-9 21:17
你没考虑range 255限制 听说wps 对range没限制 但ms有
A3,A7,A8,A9,A10,A15,A18,A19,A20,A28,A29, ...

  长字符串这种情况有哦，我原来就写的很简单，后来各种数据测试后才发现东一个BUG，西一个BUG，后来代码改复杂后就把自己绕晕了

cidanji

做题速度赶不上出题速度

Qs18

1. Sub qs()
   
2.     Dim arr, i, rng As Range
   
3.     arr = Sheet1.[a2:a6]
   
4.     For i = 1 To UBound(arr)
   
5.             ar = Split(arr(i, 1), ",")
   
6.             For j = 0 To UBound(ar)
   
7.                 If rng Is Nothing Then
   
8.                 Set rng = Range(ar(j))
   
9.                 Else
   
10.                 Set rng = Union(rng, Range(ar(j)))
    
11.                 End If
    
12.             Next j
    
13.         a = "=SUM(" & rng.Address(0, 0) & ")"
    
14.         arr(i, 1) = a
    
15.         Set rng = Nothing
    
16.     Next i
    
17.     Sheet1.Range("d2").Resize(UBound(arr), 1) = arr
    
18. End Sub

复制代码

Qs18

试试.............

Qs18

试试..............

咔咔乱坠

365函数，参与一下

1. =LET(a,TEXTSPLIT(A2,{",","，"},"A",1),b,a-SEQUENCE(ROWS(a)),"=SUM("&TEXTJOIN(",",,"A"&MAP(UNIQUE(b),LAMBDA(x,TEXTJOIN(":A",,UNIQUE(TAKE(FILTER(a,b=x),{1;-1}))))))&")")

复制代码