|
感觉二分法最快!
我自己测试的,如果数据达到一万行,要查询的也是一万行,用普通循环来查询要大约10秒,二分法0.2秒完成。
- Sub 二分法()
- Dim StarTime As Date
- StarTime = Timer
- Dim aR As Long, bR As Long
- Dim aArr, bArr, cArr()
- Dim x As Long, y As Long, i%, N%
- With Sheet1
- .Range("A:D").Sort key1:=Range("A1"), order1:=1, Header:=xlYes
- aR = .Cells(99999, 1).End(xlUp).Row
- bR = .Cells(99999, 8).End(xlUp).Row
- aArr = .Range("A1:D" & aR).Value
- bArr = .Range("H1:H" & bR).Value
- .Range("I2:K" & bR).ClearContents
- End With
- ReDim cArr(2 To bR, 1 To 3)
- For y = 2 To bR
-
- m = Round(aR - 1 + 0.5, 0)
- N = 1
- x = m / 2
-
- Do While aArr(x, 1) <> bArr(y, 1)
- N = N + 1
- If aArr(x, 1) < bArr(y, 1) Then
- x = Round(x + m / (2 ^ N), 0)
- Else
- x = Round(x - m / (2 ^ N), 0)
- End If
- Loop
-
- For i = 1 To 3
- cArr(y, i) = aArr(x, i + 1)
- Next i
-
- Next y
- Sheet1.Range("I2").Resize(bR - 1, 3) = cArr
- MsgBox "共用时" & Format(Timer - StarTime, "0.000000") & "秒!" & Chr(10) & "查了" & bR - 1 & "条数据。"
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|