|
有大量随机数据,要找出最大和最小值,然后按最大和最小值等分成x组(比如40组),然后把所有数据按大小归类到各自x组中去。根据例子数据处理,第三列中的Mx为目标,第一和第二列一起归类。
- Sub Macro1()
- On Error Resume Next
- Dim arr, wb As Workbook, i%, j&, k&
- Set wb = ThisWorkbook
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- '保留界面工作表,删除多余表
- For i = Sheets.Count To 1 Step -1
- If Sheets(i).Name <> "界面" Then Sheets(i).Delete
- Next
- n = Val(Application.InputBox("请输入组数"))
- '获得最大数和最小数,方便分组
- mymax = 0: mymin = 10 ^ 8
- With Workbooks.Open(ThisWorkbook.Path & "\原始数据.xls")
- For i = 1 To .Sheets.Count
- arr = .Sheets(i).Range("a1").CurrentRegion
- For j = 2 To UBound(arr)
- If arr(j, 3) > mymax Then mymax = arr(j, 3)
- If arr(j, 3) < mymin Then mymin = arr(j, 3)
- Next
- Next
- .Close False
- End With
- '分组,命名工作表名称
- s = Round((mymax - mymin) / n, 3)
- For k = mymin To mymax + 0.1 * s Step s
- With Sheets.Add(after:=Sheets(Sheets.Count))
- [a1:c1] = "标题"
- ActiveSheet.Name = k + s & "以下"
- End With
- Next
- With Workbooks.Open(ThisWorkbook.Path & "\原始数据.xls")
- For i = 1 To .Sheets.Count
- arr = .Sheets(i).Range("a1").CurrentRegion
- For j = 2 To UBound(arr)
- n2 = (arr(j, 3) - mymin) \ s + 2
- h = wb.Sheets(n2).Cells(65536, 1).End(xlUp).Row + 1
- .Sheets(i).Cells(j, 1).Resize(1, 3).Copy wb.Sheets(n2).Cells(h, 1)
- Next
- Next
- .Close False
- End With
- Sheets("界面").Activate
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
复制代码
|
-
-
求助.rar
268.38 KB, 下载次数: 7
例子数据
|