|
在网上看到了用JavaScript的sort方法进行排序,并与单元格排序对比了下,测试500000个随机数从小到大排序,感觉JavaScript被完爆啊,有没有比单元格排序更快的方法?
测试结果:
JavaScript排序:13秒
单元格排序:3秒
- '测试500000个数据的排序时间为13秒。
- Sub Java数值排序速度测试()
- Dim Js As New ScriptControl
- Dim Arr, kk$, Brr()
- Dim i&, t1#, t2#
-
-
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
-
- t1 = Timer
- ReDim Arr(1 To 500000)
-
- For i = 1 To UBound(Arr)
- Arr(i) = Rnd * 10000
- Next i
-
- kk = Join(Arr, ",")
-
- With Js
- .Language = "JavaScript"
-
- .AddCode "function aa(bb){x=bb.split(',');x.sort(function(a,b){return a-b});return x;}"
-
- Arr = Split(.Eval("aa('" & kk & "')"), ",")
- End With
-
- ReDim Brr(1 To UBound(Arr) + 1, 1 To 1)
-
- For i = 0 To UBound(Arr)
- Brr(i + 1, 1) = Arr(i)
- Next i
-
- With ActiveSheet.Range("B1").Resize(UBound(Brr), 1)
- .ClearContents
- .Value = Brr
- End With
- t2 = Timer
-
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
-
- MsgBox "用时:" & Round(t2 - t1, 0) & "秒。"
- End Sub
- '测试500000个数据的排序时间为3秒。
- Sub 单元格排序速度测试()
- Dim Arr()
- Dim i&, t1#, t2#
-
- t1 = Timer
- ReDim Arr(1 To 500000, 1 To 1)
-
- For i = 1 To UBound(Arr)
- Arr(i, 1) = Rnd * 10000
- Next i
-
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
-
- With Sheets.Add(before:=Sheets(1))
- With .Range("C1").Resize(UBound(Arr), 1)
- .Value = Arr
- .Sort key1:=ActiveSheet.Range("C1")
- Arr = .Value
- End With
- .Delete
- End With
-
- With Sheets("Sheet1").Range("C1").Resize(UBound(Arr), 1)
- .EntireColumn.ClearContents
- .Value = Arr
- End With
- t2 = Timer
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- MsgBox "用时:" & Round(t2 - t1, 0) & "秒。"
- End Sub
复制代码
|
|