excel 有没有一种方法可以一次性将VBA中的工作表函数直接应用于多个目标单元格？

mhd8tkvw 于 2022-12-24 发布在其他

关注(0)|答案(2)|浏览(126)

我有一个过程涉及到一个sumifs函数，该函数将多个参数应用于许多单元格的大范围。最终产品应该涉及只包含值的单元格。excel用户应该：
1.键入公式。
1.将此复制到表格中的所有单元格。
1.复制所有单元格并仅替换为值。（下面的第一种方法）
在vba中，我通常喜欢通过将所有单元格复制到一个变量中来执行计算，从而避免电子表格中的这些可见中间步骤（利用Excel自己的函数“application. worksheetfunction”），并将变量粘贴回范围（下面的第二种方法）。使用这种方法，在计算期间用户干扰的机会较少，并且通常也是相当有效的。我似乎无法击败使用变体模拟Excel用户方法的性能。事实上，在大范围内，它会变得慢得多。
'

Sub countif_2methods()
    Range("A1") = "Marty"
    Range("A2") = "Marty"
    Range("A3") = "Emmet"
    Range("A4") = "Emmet"
    Range("A5") = "Biff"
    
    '1st method
    With Range("B1")
        .Formula2R1C1 = "=COUNTIF(C1,R[]C1)"
        .AutoFill Destination:=Range("B1:B5")
    End With
    With Range("B1:B5")
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
    
    '2nd method
    Dim v As Variant
    v = Range("B1:B5")
    Dim i As Long
    For i = LBound(v, 1) To UBound(v, 1)
        v(i, 1) = Application.WorksheetFunction.countif(Range("A:A"), Range("A" & i))
    Next i
    Range("B1:B5") = v
    
End Sub

'
是否有某种方法可以将application.worksheetfunction直接应用于整个目标区域，其效率至少与“writing_formula -〉复制到其他单元格-〉复制并粘贴为值”（第一种方法）相同？
我大规模地尝试了所描述的两种方法，对于35 K单元格的搜索范围，3 K单元格的目标范围使用带6个参数的sumifs函数，第一种方法比在变体内部进行计算快10倍。

excel

来源：https://stackoverflow.com/questions/74873055/is-there-a-way-to-apply-worksheetfunctions-in-vba-directly-on-multiple-destinati

2条答案

按热度按时间

kpbwa7wx1#

在一次操作中将公式应用于整个范围，并将范围值分配给公式本身，可以使用公式产生最佳结果。但不要忘记通过关闭Application.ScreenUpdating并将Application.Calculation设置为xlCalculationManual来优化性能。

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'3rd method
With Range("B1").Resize(5)
    .Formula2R1C1 = "=COUNTIF(C1,R[]C1)"
    .Value = .Value
End With

Application.Calculation = xlCalculationAutomatic

执行count-if的最快方法是使用字典来计算匹配项，然后填充一个数组以写回工作表。

Sub DictionaryMatch()
    Dim Map As Object
    Set Map = CreateObject("Scripting.Dictionary")
    
    Dim Data As Variant
    Dim Result As Variant
    

    '3rd method
    With Range("A1").CurrentRegion
        Data = .Columns(1).Value
        Result = .Columns(2).Value
    End With
    
    Dim Key As String
    Dim r As Long
    
    For r = 1 To UBound(Data)
        Key = Data(r, 1)
        Map(Key) = Map(Key) + 1
    Next
    
    For r = 1 To UBound(Data)
        Key = Data(r, 1)
        Result(r, 1) = Map(Key)
    Next
       
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    With Range("A1").CurrentRegion
         .Columns(2).Value = Result
    End With
    
    Application.Calculation = xlCalculationAutomatic
  
    
End Sub

赞(0）回复(0）举报 2022-12-24

yqlxgs2m2#

不保证会更快，但你可以直接这样做：

Range("B1:B5").Value = Application.countif(Range("A:A"), Range("A1:A5"))

赞(0）回复(0）举报 2022-12-24

我来回答

excel 有没有一种方法可以一次性将VBA中的工作表函数直接应用于多个目标单元格？

2条答案

相关问题

热门标签

最新问答