楼主
liuguansky |
Q：如何返回四个三位数各数字之间组合成两位数，去重从小到大排列的序列？
具体效果如下图示：
A:用如下代码可以实现：
方案一
- Function twoN(rng As Range) As Variant
- Application.Volatile
- Dim arr, i%, m%, n%, dic, arrs, d
- Set dic = CreateObject("scripting.dictionary")
- twoN = ""
- If rng.Count <> 4 Then
- Exit Function
- Else: arr = rng.Value
- For m = 1 To 3
- For n = m + 1 To 4
- arrs = ar(arr(1, m), arr(1, n))
- For Each d In arrs
- If Not dic.exists(d) Then dic.Add d, ""
- Next
- Next n, m
- If dic.Count > 0 Then twoN = Application.Transpose(dic.keys)
- End If
- End Function
- Function ar(ByVal a1 As String, ByVal a2 As String) As Variant
- Dim arr1, arr2, i%, j%, dic, str1$
- Set dic = CreateObject("scripting.dictionary")
- ReDim arr1(1 To 3): ReDim arr2(1 To 3)
- For i = 1 To 3
- arr1(i) = CInt(Mid(a1, i, 1))
- arr2(i) = CInt(Mid(a2, i, 1))
- Next i
- For i = 1 To 3
- For j = 1 To 3
- If arr1(i) >= arr2(j) Then
- str1 = CInt(arr2(j) & arr1(i))
- Else: str1 = CInt(arr1(i) & arr2(j))
- End If
- If Not dic.exists(str1) Then dic.Add str1, ""
- Next j, i
- If dic.Count > 0 Then ar = dic.keys Else ar = ""
- End Function
方案二：
- Function justtwoN(rng As Range) As Variant
- Application.Volatile
- Dim arrt(), i%, m%, n%, str1$, a1$, a2$, k%
- justtwoN = ""
- If rng.Count <> 4 Then Exit Function
- arr = rng.Value
- For i = 0 To 99
- str1 = Format(i, "00")
- a1 = CInt(Mid(str1, 1, 1)): a2 = CInt(Mid(str1, 2, 1))
- If a1 <= a2 Then
- For m = 1 To 4
- For n = 1 To 4
- If InStr(1, arr(1, m), a1) > 0 And InStr(1, arr(1, n), a2) > 0 And m <> n Then
- k = k + 1: ReDim Preserve arrt(1 To 1, 1 To k)
- arrt(1, k) = str1
- GoTo 100
- End If
- Next n, m
- End If
- 100
- Next i
- If k > 0 Then justtwoN = arrt
- End Function
具体示例文件 如下：
|