|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
'人数<8直接输出为一组,否则按8-10余数最小分组,,,
Option Explicit
Sub test()
Dim arr, i, j, k, kk, a, b, m, n, p, cnt
arr = [a1].CurrentRegion.Offset(1)
ReDim brr(1 To UBound(arr, 1), 20)
For i = 1 To UBound(arr, 1) - 1
For j = i To UBound(arr, 1) - 1
If arr(j, 1) <> arr(j + 1, 1) Then
For a = i To j
For b = a To j
If arr(b, 1) <> arr(b + 1, 1) Or arr(b, 2) <> arr(b + 1, 2) Then
If b - a + 1 >= 8 Then
n = (b - a + 1) Mod 8: p = 8
For k = 9 To 10
If (b - a + 1) Mod k < n Then p = k: n = (b - a + 1) Mod k
Next
Else
n = 0: p = (b - a + 1)
End If
For k = a To b - n Step p
m = m + 2: cnt = cnt + 1: brr(m - 1, 0) = "组别:" & cnt
For kk = k To k + p - 1
brr(m - 1, kk - k + 1) = arr(kk, 1)
brr(m, kk - k + 1) = arr(kk, 2)
Next kk, k
If n > 0 Then
For k = b - n + 1 To b
brr(m - 1, k - (b - n + 1) + 1 + p) = arr(k, 1)
brr(m, k - (b - n + 1) + 1 + p) = arr(k, 2)
Next
End If
a = b: Exit For
End If
Next b, a
i = j: cnt = 0: Exit For
End If
Next j, i
[d1].Resize(UBound(brr, 1), UBound(brr, 2)) = brr
End Sub |
评分
-
1
查看全部评分
-
|