|
发表于 2012-1-18 09:15
|
显示全部楼层
本楼为最佳答案
- Private Sub CommandButton1_Click()
- Dim t
- t = Timer
- Dim arr, d, brr, crr(), result(0 To 9, 1 To 2), i%, j%, x As Byte, y As Byte, m%, sr$
- Dim drr(1 To 10000, 0 To 9), arr1(1 To 3) As Integer, w As Byte, wn As Byte, wk As Byte, z As Byte
- arr = Range("e6").CurrentRegion.Value '数据源装入数组
- ReDim crr(1 To UBound(arr) - 2, 1 To 3)
- brr = Range("k2").CurrentRegion.Value '判定源装入数组
- For j = 1 To UBound(arr) - 2
- sr = "": wk = 0
- '按指定顺序3个作为一组装入新数组
- crr(j, 1) = arr(j, 1)
- crr(j, 2) = arr(j + 1, 1)
- crr(j, 3) = arr(j + 2, 1)
- For m = 1 To UBound(brr, 2)
- Erase arr1
- For k = 1 To 3
- For n = 2 To UBound(brr)
- '判断判定源中是否包含源数据
- If InStr(1, brr(n, m), crr(j, k)) Then
- '存在,累加计数
- arr1(n - 1) = arr1(n - 1) + 1
- If arr1(n - 1) = 3 Then z = n - 1
- Exit For
- End If
- Next n
- Next k
- '判定结果属于何种类型
- '______________________________________________________________
- ' 3 4 3
- ' arr1 1 1 1 不提取
- ' arr1 0 3 0 不提取
- ' arr1 3 0 0 按另一个3位段提取
- ' arr1 0 0 3 按另一个3位段提取
- ' arr1 其他情形,全部按未包含数字段提取
- '规则范围
- If (arr1(1) = arr1(2) And arr1(1) = arr1(3)) Or arr1(2) = 3 Then
- ElseIf arr1(1) = 3 Or arr1(3) = 3 Then
- If crr(j, 1) = crr(j, 2) And crr(j, 1) = crr(j, 3) Then
- sr = sr & brr(IIf(z = 1, 3, 1) + 1, m)
- Else
- sr = sr & brr(3, m)
- End If
- Else
- sr = sr & brr(Application.Match(0, arr1, 0) + 1, m)
- End If
- '规则范围
- Next m
- x = Len(sr)
- '按0-9排列计数
- For i = 0 To 9
- y = Len(Replace(sr, i, ""))
- If InStr(1, sr, i) = 0 Then
- result(i, 1) = i: result(i, 2) = 0
- Else
- result(i, 1) = i: result(i, 2) = x - y
- End If
- Next
- '按频率排序
- For w = 0 To 9
- For wn = 0 To 9
- If result(wn, 2) = w Then
- drr(j, wk) = wn
- wk = wk + 1
- End If
- Next
- Next
- Next j
- Range("v:ae").ClearContents '清空
- Range("v9").Resize(j, 10) = drr '返回单元格区域
- MsgBox Timer - t
- End Sub
复制代码 |
|