|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
'你在给我出考题?毫无意义的问题,我都不知道你到底想干什么。从一开始很难理解的文字题到现在无休止的改变条件。这是我在这个帖子中最后一次回复你,你得要考虑别人的感受,如果这问题确实有意义我会给你花点时间看看,但我觉得不是。108个主题没有一朵小花,已经767个帖子了,是否你也应该给与别人一些帮助?
Option Explicit
Const BAD = 10 ^ 8
Sub test()
Dim arr, i, cnt, j, n, brr, t, m, k, crr, dic, key, pos, ii, offset
Set dic = CreateObject("scripting.dictionary")
arr = Range("a1:r" & Cells(Rows.Count, "a").End(xlUp).Row)
If UBound(arr, 1) Mod 9 > 0 Then MsgBox "!": Exit Sub
cnt = UBound(arr, 1) \ 9: pos = Array(11, 15, 16)
ReDim brr(1 To cnt, 1 To 3), crr(1 To UBound(arr, 1), 1 To 1)
For i = 1 To UBound(arr, 1): dic(arr(i, 1)) = vbNullString: Next
For i = 1 To cnt: brr(i, 3) = i: Next
For ii = 0 To UBound(pos)
For Each key In dic.keys
For i = 1 To 9
n = 0
For j = 1 To cnt
If arr((j - 1) * 9 + i, 1) = Val(key) Then
n = n + 1
If Len(arr((j - 1) * 9 + i, pos(ii))) = 0 Then
brr(n, 1) = BAD
Else
brr(n, 1) = arr((j - 1) * 9 + i, pos(ii))
End If
End If
Next
For j = 1 To n - 1
For k = j + 1 To n
If brr(j, 1) > brr(k, 1) Then
t = brr(j, 1): brr(j, 1) = brr(k, 1): brr(k, 1) = t
t = brr(j, 3): brr(j, 3) = brr(k, 3): brr(k, 3) = t
End If
Next k, j
m = 1: brr(1, 2) = m
For j = 2 To n
If brr(j, 1) <> brr(j - 1, 1) Then m = m + 1
brr(j, 2) = m
Next
For j = 1 To n - 1
For k = j + 1 To n
If brr(j, 3) > brr(k, 3) Then
t = brr(j, 1): brr(j, 1) = brr(k, 1): brr(k, 1) = t
t = brr(j, 3): brr(j, 3) = brr(k, 3): brr(k, 3) = t
t = brr(j, 2): brr(j, 2) = brr(k, 2): brr(k, 2) = t
End If
Next k, j
n = 0
For j = 1 To cnt
If arr((j - 1) * 9 + i, 1) = Val(key) Then
n = n + 1
If brr(n, 1) = BAD Then
crr((j - 1) * 9 + i, 1) = vbNullString
Else
crr((j - 1) * 9 + i, 1) = brr(n, 2)
End If
End If
Next j, i
Next
offset = offset + 1
Cells(1, pos(UBound(pos)) + offset + 1).Resize(UBound(crr, 1), 1) = crr
Next
End Sub |
评分
-
1
查看全部评分
-
|