[發問] 請教應如何將編號按規則自動合併取得表述結果?

sujane0701

問題.rar (6.99 KB)

下載次數: 11

2019-5-9 04:09

問題如附件檔所示,請教應如何取得表述結果?

准提部林

VBE一般模組貼入以下程式碼:
Function GetSerNo(Rng As Range) As String
Dim A, T0$, T$, T1$, V0, V1, V2, TT$
For Each A In Rng
    If A = "" Then GoTo 101
    T0 = Left(A, 2): V0 = Mid(A, 3)
    If T0 <> T1 Or Val(V0) - Val(V2) <> 1 Then
       T = T1 & V1 & IIf(V1 = V2, "", "~" & T1 & V2)
       TT = Trim(TT & " " & T)
       T1 = T0: V1 = V0: V2 = V0
    Else
       V2 = V0
    End If
101: Next
TT = Trim(TT & " " & T1 & V1 & IIf(V1 = V2, "", "~" & T1 & V2))
GetSerNo = Replace(TT, " ", ",")
End Function

==============================
E2/公式:
=IF(C2="","",GetSerNo(OFFSET(A2,,,C2)))  
下拉即可

=====

sujane0701

回復 2# 准提部林


    太好用了,非常感謝版主出手相助,感恩~