標題: [發問] 複雜的串連與合併 [打印本頁]

---

作者: 軒云熊    時間: 2023-6-9 12:38     標題: 複雜的串連與合併

本帖最後由 軒云熊 於 2023-6-9 12:44 編輯

看看有沒有大大能幫忙
這題串聯有難度 ...小弟解不出來 ...請問有沒有大大可以解這題 感恩.~
[attach]36564[/attach]

[attach]36563[/attach]

---

作者: 軒云熊    時間: 2023-6-9 13:08

這題 卡在 如何判斷 同一組數字 是不是連號 如果是 如何取代最大值 如果不是 繼續串聯

---

作者: Andy2483    時間: 2023-6-12 09:52

回復 2# 軒云熊


    謝謝前輩發表此主題與範例
後學藉此帖練習字典與自訂函數,學習方案如下,請前輩參考

執行結果:
[attach]36567[/attach]


Option Explicit
Sub TEST()
MsgBox N_L("235-237,238", "-", ",") & vbLf & vbLf _
   & N_L("235~237;238;236~240;260~261;25", "~", ";")
End Sub
Function N_L(數字串$, 連續符$, 間隔符$) As String
Dim A, Z, V, i&, X&, Mi&, Ma&, N&, T$
Set Z = CreateObject("Scripting.Dictionary")
If InStr(數字串, 間隔符) = 0 Or Val(數字串) = 0 Then N_L = "": Exit Function
On Error GoTo Rear
For Each A In Split(Replace(數字串, 連續符, "-"), 間隔符)
   A = A & "-" & A: A = Split(A, "-")(0) & "-" & Split(A, "-")(1)
   For i = 0 To Abs(Evaluate(A))
      V = Val(A) + i
      If N = 0 Then Mi = V: Ma = Mi: N = 1
      Z(V) = 1
      If V < Mi Then Mi = V Else: If V > Ma Then Ma = V
   Next
Next
For i = Mi To Ma
   If Z(i) <> "" And X = 0 Then T = T & 間隔符 & i: X = 1
   If Z(i + 1) = "" And X = 1 Then X = 0:  GoTo i01
   If Z(i + 1) = "" And X > 1 Then T = T & 連續符 & i: X = 0
   If Z(i) <> "" Then X = X + 1
i01: Next
Rear: If Err.Number = 0 Then N_L = Mid(T, 2) Else: N_L = ""
Set Z = Nothing
End Function

---

作者: Andy2483    時間: 2023-6-12 16:34

本帖最後由 Andy2483 於 2023-6-12 16:42 編輯

回復 3# Andy2483


    後學藉此帖練習陣列與字典,學習方案如下,請各位前輩指教


Option Explicit
Sub TEST_1()
Dim Arr, Brr, Crr, Z, i&, R&, X%, T$, T0$, T1$, T2$, T3$, xR As Range
Set Z = CreateObject("Scripting.Dictionary"): [E:E].ClearContents
Set xR = Range([B1], [A65536].End(3)): Brr = xR
With xR
   For i = 48 To 57
     .Replace Chr(i), " ", LookAt:=2: .Replace "  ", " ", LookAt:=2
   Next
   Arr = xR: xR = Brr
   With .Columns(2)
      .Replace "-", "~", LookAt:=2
      For i = 65 To 90: .Replace Chr(i), "", LookAt:=2: Next
      Crr = xR: xR = Brr
   End With
End With
ReDim Brr(1 To UBound(Brr) * 3, 1 To 1)
For i = 2 To UBound(Crr)
   T = Crr(i, 1): T0 = T
   T = Replace(T & "1|", StrReverse(Val(StrReverse(T & 1))) & "|", "")
   If Z(T) = "" Then
      Z(T) = R * 3 + 1: R = R + 1: Brr(Z(T), 1) = "PO# " & T0
      T1 = Trim(Split(Arr(i, 2) & "-", "-")(0))
      If T1 = "" Then: T1 = Split(Arr(i, 1), " ")(0)
      If InStr(T1, " ") = 0 Then T1 = T1 & " "
      Z(T & "|e") = T1: Z(T & "|n") = Crr(i, 2)
   End If
   If InStr(Brr(Z(T), 1), T0) = 0 Then Brr(Z(T), 1) = Brr(Z(T), 1) & "/" & T0
   Z(T & "|n") = Z(T & "|n") & "," & Crr(i, 2)
   T2 = Z(T & "|n"): T3 = N_L(T2, "~", ",")
   If T3 = "" Then
      Brr(Z(T) + 1, 1) = "#VALUE!"
      Else
      T2 = Replace(Z(T & "|e"), " ", "(" & N_L(T2, "~", ",") & ")")
      Brr(Z(T) + 1, 1) = "C/NO. " & Replace(T2, "~", "-")
   End If
Next
[E2].Resize(R * 3 - 1, 1) = Brr
Set Z = Nothing: Set xR = Nothing: Erase Arr, Brr, Crr
End Sub

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)