|
- Sub kagawa()
- Dim i&, j&, k&, m&, n&, r&, t&, tms#
- tms = Timer
-
- ar = [a1].CurrentRegion
- m = UBound(ar)
- ReDim a&(m - 1)
- For i = 1 To m
- a(i - 1) = ar(i, 1)
- Next
-
-
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
-
- For Each Sh In Worksheets '删除其他工作表
- If Sh.Name <> ActiveSheet.Name Then Sh.Delete
- Next
-
- Randomize
- c = 16000 '列数
- gs = 10 '生成的工作簿数
-
- cs = InputBox("请输入执行次数", , 5)
- For xx = 1 To cs
- ReDim arr&(m, 1 To c)
- ReDim brr(m + 5, 1 To c)
- p = 0
- For k = 1 To gs
- wName = Right(0 & k, 2) & ".xlsx"
- For j = 1 To c
- For i = 0 To m - 1
- r = Int(Rnd * (m - i)) + i
- t = a(r): a(r) = a(i): a(i) = t: arr(i, j) = t
- Next
- ''''''''''''''''''''''''' 新增判断部分
- r = m: n = 0 'r是最末一行(有数据的下一行)
- For i = r To 3 * r / 4 - 1 Step -1 '只需判断最末一行至最末一行的3/4处
- n = n + 1
- If arr(i, j) = n And i > 3 * n Then '倒数第n行数值为n
- If arr(i - n, j) = n And arr(i - 2 * n, j) = n And arr(i - 3 * n, j) = n Then
- p = p + 1
- For kk = 0 To m - 1
- brr(kk, p) = arr(kk, j)
- Next
-
- brr(kk + 2, p) = n
- brr(kk + 3, p) = wName
- brr(kk + 4, p) = Split(Cells(1, j).Address, "$")(1) '第j列的列号
- Exit For
- End If
- End If
- Next
- '''''''''''''''''''''''''''''''''''''''
- Next
- Next
-
- If p > 0 Then
- Sheets.Add after:=Sheets(Sheets.Count)
- With ActiveSheet
- .Cells.ClearContents
- .[a1].Resize(m + 5, p) = brr
- .Name = Format(xx, "00")
- End With
- End If
- Next
-
- Application.ScreenUpdating = True
- MsgBox Format(Timer - tms, "0.000s")
- End Sub
复制代码 |
评分
-
查看全部评分
|