VBA如何不使用循环实现批量条件乘法以提升大数据处理效率？

原VBA循环处理5000+行数据速度慢的核心原因是逐行读写单元格的IO操作开销过大，以下提供两种无循环（或近似无循环）的高效实现方案：

方案一：直接使用Excel工作表公式 #

在E2单元格输入以下公式，然后批量填充至所有数据行（可通过鼠标下拉或VBA批量设置）：

嵌套IF版本（兼容所有Excel版本） #

=IF(AND(B2="fruit",C2="good"),D2*$I$2,IF(AND(B2="fruit",C2="bad"),D2*$J$2,IF(AND(B2="salad",C2="good"),D2*$I$3,IF(AND(B2="salad",C2="bad"),D2*$J$3,"error"))))

IFS函数版本（Excel 2019及以上可用，更简洁） #

=IFS(B2="fruit",IF(C2="good",D2*$I$2,D2*$J$2),B2="salad",IF(C2="good",D2*$I$3,D2*$J$3),TRUE,"error")

如果需要用VBA自动批量填充公式，可执行以下无循环代码：

Sub BatchFormulaFill()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim targetRange As Range
    
    Set ws = ThisWorkbook.Worksheets("Page 1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set targetRange = ws.Range("E2:E" & lastRow)
    
    ' 关闭屏幕更新和事件减少卡顿
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    ' 批量设置公式
    targetRange.Formula = "=IF(AND(B2=""fruit"",C2=""good""),D2*$I$2,IF(AND(B2=""fruit"",C2=""bad""),D2*$J$2,IF(AND(B2=""salad"",C2=""good""),D2*$I$3,IF(AND(B2=""salad"",C2=""bad""),D2*$J$3,""error""))))"
    
    ' 可选：将公式转为静态数值，避免后续参数变动影响结果
    targetRange.Value = targetRange.Value
    
    ' 恢复系统设置
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

方案二：VBA数组批量处理（内存操作，速度极致） #

虽然存在数组内部的遍历，但所有操作都在内存中完成，仅两次IO（读入数据、写入结果），比原循环快数十倍：

Sub CalculateWithArray()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataArr As Variant
    Dim resultArr As Variant
    Dim i As Long
    ' 预读取价格参数，避免重复访问单元格
    Dim fruitGood As Double, fruitBad As Double
    Dim saladGood As Double, saladBad As Double
    
    Set ws = ThisWorkbook.Worksheets("Page 1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 读取价格配置
    fruitGood = ws.Range("I2").Value
    fruitBad = ws.Range("J2").Value
    saladGood = ws.Range("I3").Value
    saladBad = ws.Range("J3").Value
    
    ' 一次性读取B-D列数据到数组
    dataArr = ws.Range("B2:D" & lastRow).Value
    ' 初始化结果数组
    ReDim resultArr(1 To UBound(dataArr, 1), 1 To 1)
    
    ' 内存中处理逻辑
    For i = 1 To UBound(dataArr, 1)
        Select Case dataArr(i, 1)
            Case "fruit"
                resultArr(i, 1) = IIf(dataArr(i, 2) = "good", dataArr(i, 3) * fruitGood, _
                                  IIf(dataArr(i, 2) = "bad", dataArr(i, 3) * fruitBad, "error"))
            Case "salad"
                resultArr(i, 1) = IIf(dataArr(i, 2) = "good", dataArr(i, 3) * saladGood, _
                                  IIf(dataArr(i, 2) = "bad", dataArr(i, 3) * saladBad, "error"))
            Case Else
                resultArr(i, 1) = "error"
        End Select
    Next i
    
    ' 一次性写入结果到E列
    ws.Range("E2:E" & lastRow).Value = resultArr
End Sub

核心优化点说明 #

1. 减少IO操作：原循环每行读写单元格，属于磁盘级IO；方案中仅一次性读取/写入数据，其余操作在内存完成。
2. 关闭不必要的系统事件：批量公式方案中关闭屏幕更新和事件，避免Excel频繁刷新界面。
3. 预读取参数：数组方案中提前读取价格参数，避免循环中重复访问单元格。

内容的提问来源于stack exchange，提问作者Francisco Augusto Varela Aguir