☰

匹配优化了 RAM 消耗，而不是 CPU 消耗

tag-icon • tag-icon microsoft-excel memory cpu performance vba
匹配优化了 RAM 消耗，而不是 CPU 消耗

我正在寻找不同的方法来获取另一个范围内相同单元格的行号。

最快的方法之一是使用 Match 函数。当跨两个非常大的范围（每个范围超过 500,000 行）运行时，它将（默认情况下）在 CPU 上并行运行，并且如果 CPU 性能良好，运​​行速度会非常快。但是，当 CPU 性能较弱或核心数较少（或不支持多线程）时，这可能需要很长时间。它几乎不占用任何 RAM。

有没有一种匹配类型会占用大量 RAM 但占用很少的 CPU？这对于拥有大量 RAM 但 CPU 较旧的计算机非常有用。

答案1

我知道有 4 种搜索方法，但我认为决定因素始终是 CPU

考试：

- out of the max of 1048576
- find cell        A1048573

没有什么能比得上 Match

Time -   LookupAppMatch(): 0.047 sec      CPU usage 1%
Time -   LookupAppMatch(): 0.031 sec      CPU usage 0%
Time -   LookupAppMatch(): 0.031 sec      CPU usage 1%

Time -    LookupWSMatch(): 0.047 sec      CPU usage 1%
Time -    LookupWSMatch(): 0.063 sec      CPU usage 0%
Time -    LookupWSMatch(): 0.047 sec      CPU usage 0%

Time -       LookupFind(): 0.672 sec      CPU usage 8%
Time -       LookupFind(): 0.625 sec      CPU usage 5%
Time -       LookupFind(): 0.625 sec      CPU usage 7%

Time -    LookupForLoop(): 0.297 sec      CPU usage 5%
Time -    LookupForLoop(): 0.297 sec      CPU usage 3%
Time -    LookupForLoop(): 0.297 sec      CPU usage 3%

Time - LookupAutoFilter(): 0.672 sec      CPU usage 7%
Time - LookupAutoFilter(): 0.359 sec      CPU usage 5%
Time - LookupAutoFilter(): 0.375 sec      CPU usage 7%

代码：

Option Explicit

Private Const FND = "A1048573"
Private t As Double

Public Sub SetupData()
    Sheet1.Columns(1).Formula = "=Address(Row(), Column(), 4)"
End Sub

Public Sub LookupAppMatch()
    t = Timer
    Sheet1.Cells(1, 2) = Application.Match(FND, Sheet1.Columns(1), 0)
    Debug.Print "Time -   LookupAppMatch(): " & Format(Timer - t, "0.000") & " sec"
End Sub

Public Sub LookupWSMatch()
    t = Timer
    Sheet1.Cells(2, 2) = WorksheetFunction.Match(FND, Sheet1.Columns(1), 0)
    Debug.Print "Time -    LookupWSMatch(): " & Format(Timer - t, "0.000") & " sec"
End Sub

Public Sub LookupFind()
    t = Timer
    Sheet1.Cells(3, 2) = Sheet1.Columns(1).Find(What:=FND, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True).Row
    Debug.Print "Time -       LookupFind(): " & Format(Timer - t, "0.000") & " sec"
End Sub

Public Sub LookupForLoop()
    Dim ur As Variant, r As Long
    t = Timer
    ur = Sheet1.Columns(1)
    For r = 1 To UBound(ur)
        If ur(r, 1) = FND Then Exit For
    Next
    Sheet1.Cells(4, 2) = r
    Debug.Print "Time -    LookupForLoop(): " & Format(Timer - t, "0.000") & " sec"
End Sub

Public Sub LookupAutoFilter()
    t = Timer
    Application.ScreenUpdating = False
    With Sheet1.Columns(1)
        .AutoFilter Field:=1, Criteria1:=FND
        Sheet1.Cells(5, 2) = .Rows.Count - .SpecialCells(xlVisible).Cells.CountLarge - 1
        .AutoFilter
    End With
    Application.ScreenUpdating = True
    Debug.Print "Time - LookupAutoFilter(): " & Format(Timer - t, "0.000") & " sec"
End Sub

更多细节这里和这里

相关内容