请问能否实现不连续编号字段汇总功能

Amnesia

左边是数据，右边是需要实现的结果，能否实现，请教大神们。

x-xx-xx

循环判断连续

一把小刀闯天下

Option Explicit

Sub test()
  Dim arr, i, m, p
  arr = [a1].CurrentRegion.Offset(1).Resize(, 4).Value
  For i = 1 To UBound(arr, 1) - 1
    If arr(i, 1) <> arr(i + 1, 1) Then
      m = m + 1
      arr(m, 1) = arr(i, 1): arr(m, 2) = arr(p + 1, 2)
      arr(m, 3) = arr(i, 2): arr(m, 4) = i - p
      p = i
    End If
  Next
  Call bsort(arr, 1, m, 1, UBound(arr, 2), 1)
  With [e2]
    .Resize(UBound(arr, 1), UBound(arr, 2)).ClearContents
    .Resize(m, UBound(arr, 2)) = arr
  End With
End Sub

Function bsort(arr, first, last, left, right, key)
  Dim i, j, k, t
  For i = first To last - 1
    For j = first To last + first - 1 - i
      If StrComp(arr(j, key), arr(j + 1, key), vbTextCompare) = 1 Then
        For k = left To right
          t = arr(j, k): arr(j, k) = arr(j + 1, k): arr(j + 1, k) = t
        Next
      End If
    Next
  Next
End Function

lwwy1981

1. Sub aaa()
   
2.     On Error Resume Next
   
3.     arr = Sheet1.[A2:B25]
   
4.     ReDim Brr(1 To UBound(arr), 1 To 4)
   
5.     For I = 1 To UBound(arr)
   
6.         X = X + 1
   
7.         If arr(I, 1) <> arr(I + 1, 1) Then
   
8.             K = K + 1
   
9.             Brr(K, 1) = arr(I, 1)
   
10.             Brr(K, 2) = arr(I - X + 1, 2)
    
11.             Brr(K, 3) = arr(I, 2)
    
12.             Brr(K, 4) = X
    
13.             X = 0
    
14.         End If
    
15.     Next I
    
16.    
    
17.     Sheet1.[E2].Resize(UBound(Brr), UBound(Brr, 2)) = Brr
    
18. End Sub
    
19. 
    

复制代码

duquancai

1. # -*- coding: utf-8 -*-
   
2. # version: Python 3.7.0
   
3. import pandas as pd
   
4. 
   
5. df = pd.read_excel('./工作簿1.xlsx',usecols='A:B')
   
6. def func(df):
   
7.     temp = df.sort_values('编号').set_index('编号',drop=False)
   
8.     temp = temp.reindex([i for i in range(temp.index[0],temp.index[-1]+1)])
   
9.     return pd.Series({
   
10.         '名称':temp['名称'].dropna().iat[0],
    
11.         '编号':','.join(temp['编号'].astype('str'))})
    
12. res = df.groupby('名称',sort=False).apply(func)
    
13. res['编号'] = res['编号'].str.split('nan')
    
14. result = res.explode('编号')
    
15. result['编号'] = resul['编号'].str.strip(',')
    
16. result = result[result['编号'].str.contains('\d')]
    
17. result['起始'] = result['编号'].str.split(',').str[0].astype('f').map(lambda x:int(x))
    
18. result['结束'] = result['编号'].str.split(',').str[-1].astype('f').map(lambda x:int(x))
    
19. result['计数'] =result['结束']-result['起始']+1
    
20. result[['起始','结束']] = result[['起始','结束']].applymap(lambda x:'{0:0>3}'.format(x))
    
21. result[['名称','起始','结束','计数']].to_excel('./统计结果.xlsx',index=False)

复制代码

deadzlq

PowerQuery参考解法