PowerShell执行Excel公式效率低下求助（90k数据量）

问题背景 #

使用以下PowerShell代码操作Excel执行公式计算时，小文件（1000-2000行）速度正常，但处理90k行的实际文件时耗时极长，拆分文件后依然速度缓慢：

$excel = new-object -comobject Excel.Application
$excel.visible = $false  
$workbook = $excel.workbooks.open("C:\SLAFile.xlsx")
 
$worksheet = $workbook.Worksheets.Item(1)
$rows = $worksheet.range("D2").currentregion.rows.count

$STDSheet = $workbook.WorkSheets.Add()
$STDSheet.Name = 'STD'

###### Copy Date #####
$worksheet.activate()
$lastRow1 = $worksheet.UsedRange.rows.count
$range1 = $worksheet.Range("B2:B$lastRow1")
$range1.copy()

$STDSheet.activate()
$lastRow2 = $STDSheet.UsedRange.rows.count + 1
$range2 = $STDSheet.Range("A$($lastRow2)")
$STDSheet.Paste($range2)

###### Copy Date ######

###### Copy SYMM_ID ####

$worksheet.activate()
$lastRow3 = $worksheet.UsedRange.rows.count
$range3 = $worksheet.Range("A2:A$lastRow3")
$range3.copy()

$STDSheet.activate()
$lastRow4 = $STDSheet.UsedRange.rows.count + 1
$range4 = $STDSheet.Range("C$($lastRow2)")
$STDSheet.Paste($range4)

###### Copy SYMM_ID ####
####### STD Column Header #######
$STDSheet.Cells(1,1) = 'Time (DD.MM.YYYY HH:MM:SS)'
$STDSheet.Cells(1,2) = 'DATE (TEXT FORMAT)'
$STDSheet.Cells(1,3) = 'SYMM_ID'
$STDSheet.Cells(1,4) = 'STD_RT'
$STDSheet.Cells(1,5) = 'IO/sec'
$STDSheet.Cells(1,6) = 'DATA/sec'
$STDSheet.Cells(1,7) = 'Block Size'
$STDSheet.Cells(1,8) = 'MIN RT/6h'

####### STD Column Header #######
##### STD Report Part ####
$count=73
for($j=2;$j -le $rows;$j++)
{
$k = $j-1
$STDSheet.range("B$j:B$j").formula = '=TEXT(A'+$j+',"dd-mm-yyyy hh:mm")'
$STDSheet.range("E$j:E$j").formula = '=IFERROR(SUMIFS(SLA!$G:$G,SLA!$C:$C,"SG_STG_*_L_*STD",SLA!$B:$B,$B'+$j+',SLA!$D:$D,"<>0"),0)+IFERROR(SUMIFS(SLA!$H:$H,SLA!$C:$C,"SG_STG_*_L_*STD",SLA!$B:$B,$B'+$j+',SLA!$D:$D,"<>0"),0)'
$STDSheet.range("D$j:D$j").formula = '=IFERROR(AVERAGEIFS(SLA!$D:$D,SLA!$C:$C,"SG_STG_*_L_*STD",SLA!$B:$B,SLA!B'+$j+',SLA!$D:$D,"<>0"),"-")'
$STDSheet.range("F$j:F$j").formula = '=IFERROR(SUMIFS(SLA!$E:$E,SLA!$C:$C,"SG_STG_*_L_*STD",SLA!$B:$B,$B'+$j+',SLA!$D:$D,"<>0"),0)+IFERROR(SUMIFS(SLA!$F:$F,SLA!$C:$C,"SG_STG_*_L_*STD",SLA!$B:$B,$B'+$j+',SLA!$D:$D,"<>0"),0)'
$STDSheet.range("G$j:G$j").formula = '=IFERROR($F'+$j+'*1024/$E'+$j+',"-")'
$STDSheet.range("H$j:H$j").formula = '=IF(COUNTIF($D'+$j+':$D'+$count+',">0")=72,MIN($D'+$j+':$D'+$count+"),"-")'
$STDSheet.range("I1:I1").formula = '="Nb Period with Min RT > "&5&"ms"'
$STDSheet.range("I$j:I$j").formula = '=IFS($H'+$j+'="-","NA",$H'+$j+'<=5,"OK",$H'+$k+'>5,"NOK CONT",1,"NOK START")'
$STDSheet.range("j1:j1").formula = '=COUNTIF($I:$I,"NOK START")'
}

代码问题分析 #

1. 逐单元格写入公式：循环90k次逐单元格设置公式，每次都要和Excel COM对象交互，累积开销极大。
2. 整列引用公式：公式中使用SLA!$G:$G这类整列引用，每次计算都会扫描整列（1048576行），而非实际数据范围。
3. 不必要的工作表激活：频繁调用activate()切换工作表，增加无意义的COM交互开销。
4. 重复写入固定公式：循环内重复写入I1、J1的固定公式，完全多余。
5. 未禁用Excel自动计算/屏幕更新：每次写入公式都会触发自动计算和屏幕刷新，大幅拖慢速度。

优化方案与代码修改 #

1. 开启Excel性能模式 #

初始化Excel时禁用屏幕更新、自动计算和警告弹窗，减少后台操作：

$excel = new-object -comobject Excel.Application
$excel.visible = $false  
$excel.ScreenUpdating = $false       # 禁用屏幕更新
$excel.Calculation = -4135          # 设置手动计算（xlCalculationManual）
$excel.DisplayAlerts = $false       # 禁用警告弹窗

2. 批量操作，避免循环逐单元格写入 #

将同一列的公式一次性写入整个数据范围，替代循环逐单元格设置：

$lastRow = $rows
# 批量写入B列TEXT公式
$STDSheet.Range("B2:B$lastRow").Formula = '=TEXT(A2,"dd-mm-yyyy hh:mm")'
# 批量写入D列AVERAGEIFS公式
$STDSheet.Range("D2:D$lastRow").Formula = '=IFERROR(AVERAGEIFS(SLA!$D$2:$D$slaLastRow,SLA!$C$2:$C$slaLastRow,"SG_STG_*_L_*STD",SLA!$B$2:$B$slaLastRow,$B2,SLA!$D$2:$D$slaLastRow,"<>0"),"-")'

3. 缩小公式引用范围 #

先获取SLA表的实际数据行数，将整列引用改为精确范围：

$slaSheet = $workbook.Worksheets.Item(1)
$slaLastRow = $slaSheet.UsedRange.Rows.Count
# 后续公式中用SLA!$G$2:$G$slaLastRow替代SLA!$G:$G

4. 移除不必要的工作表激活 #

直接操作工作表对象，无需激活即可复制/粘贴：

# 复制B列到STD表A列
$slaSheet.Range("B2:B$slaLastRow").Copy($STDSheet.Range("A2"))
# 复制A列到STD表C列
$slaSheet.Range("A2:A$slaLastRow").Copy($STDSheet.Range("C2"))

5. 把固定公式移到循环外 #

I1、J1的公式只需设置一次，放在循环之前：

$STDSheet.Range("I1").Formula = '="Nb Period with Min RT > "&5&"ms"'
$STDSheet.Range("J1").Formula = '=COUNTIF($I:$I,"NOK START")'

6. 处理H列和I列的依赖公式 #

H列依赖连续72行的D列数据，可通过批量填充公式实现：

# 先写入第一个H列公式，再批量填充
$STDSheet.Range("H2").Formula = '=IF(COUNTIF($D2:$D73,">0")=72,MIN($D2:$D73),"-")'
$STDSheet.Range("H2:H$lastRow").FillDown()

# I列公式依赖上一行的H列，同样先写第一个再填充
$STDSheet.Range("I2").Formula = '=IFS($H2="-","NA",$H2<=5,"OK",$H1>5,"NOK CONT",1,"NOK START")'
$STDSheet.Range("I2:I$lastRow").FillDown()

7. 最后恢复Excel设置并释放COM对象 #

操作完成后恢复自动计算和屏幕更新，彻底释放COM对象避免内存泄漏：

# 恢复Excel设置
$excel.Calculation = -4105  # 恢复自动计算（xlCalculationAutomatic）
$excel.ScreenUpdating = $true

# 保存并关闭文件
$workbook.Save()
$workbook.Close()
$excel.Quit()

# 释放COM对象
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($STDSheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($slaSheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

优化后完整代码示例 #

$excel = new-object -comobject Excel.Application
$excel.visible = $false  
$excel.ScreenUpdating = $false
$excel.Calculation = -4135
$excel.DisplayAlerts = $false

$workbook = $excel.workbooks.open("C:\SLAFile.xlsx")
$slaSheet = $workbook.Worksheets.Item(1)
$slaLastRow = $slaSheet.UsedRange.Rows.Count
$rows = $slaSheet.range("D2").currentregion.rows.count

$STDSheet = $workbook.WorkSheets.Add()
$STDSheet.Name = 'STD'

# 复制数据列
$slaSheet.Range("B2:B$slaLastRow").Copy($STDSheet.Range("A2"))
$slaSheet.Range("A2:A$slaLastRow").Copy($STDSheet.Range("C2"))

# 设置表头
$headers = @(
    'Time (DD.MM.YYYY HH:MM:SS)',
    'DATE (TEXT FORMAT)',
    'SYMM_ID',
    'STD_RT',
    'IO/sec',
    'DATA/sec',
    'Block Size',
    'MIN RT/6h'
)
for ($i=0; $i -lt $headers.Count; $i++) {
    $STDSheet.Cells(1, $i+1) = $headers[$i]
}

# 批量写入公式
$lastRow = $rows
# B列：日期格式化
$STDSheet.Range("B2:B$lastRow").Formula = '=TEXT(A2,"dd-mm-yyyy hh:mm")'
# D列：平均响应时间
$STDSheet.Range("D2:D$lastRow").Formula = "=IFERROR(AVERAGEIFS(SLA!`$D`$2:`$D`$$slaLastRow,SLA!`$C`$2:`$C`$$slaLastRow,""SG_STG_*_L_*STD"",SLA!`$B`$2:`$B`$$slaLastRow,$B2,SLA!`$D`$2:`$D`$$slaLastRow,""<>0""),""-"")"
# E列：IO/sec总和
$STDSheet.Range("E2:E$lastRow").Formula = "=IFERROR(SUMIFS(SLA!`$G`$2:`$G`$$slaLastRow,SLA!`$C`$2:`$C`$$slaLastRow,""SG_STG_*_L_*STD"",SLA!`$B`$2:`$B`$$slaLastRow,$B2,SLA!`$D`$2:`$D`$$slaLastRow,""<>0""),0)+IFERROR(SUMIFS(SLA!`$H`$2:`$H`$$slaLastRow,SLA!`$C`$2:`$C`$$slaLastRow,""SG_STG_*_L_*STD"",SLA!`$B`$2:`$B`$$slaLastRow,$B2,SLA!`$D`$2:`$D`$$slaLastRow,""<>0""),0)"
# F列：DATA/sec总和
$STDSheet.Range("F2:F$lastRow").Formula = "=IFERROR(SUMIFS(SLA!`$E`$2:`$E`$$slaLastRow,SLA!`$C`$2:`$C`$$slaLastRow,""SG_STG_*_L_*STD"",SLA!`$B`$2:`$B`$$slaLastRow,$B2,SLA!`$D`$2:`$D`$$slaLastRow,""<>0""),0)+IFERROR(SUMIFS(SLA!`$F`$2:`$F`$$slaLastRow,SLA!`$C`$2:`$C`$$slaLastRow,""SG_STG_*_L_*STD"",SLA!`$B`$2:`$B`$$slaLastRow,$B2,SLA!`$D`$2:`$D`$$slaLastRow,""<>0""),0)"
# G列：块大小计算
$STDSheet.Range("G2:G$lastRow").Formula = '=IFERROR($F2*1024/$E2,"-")'

# H列：6小时最小RT（先写第一个再填充）
$STDSheet.Range("H2").Formula = '=IF(COUNTIF($D2:$D73,">0")=72,MIN($D2:$D73),"-")'
$STDSheet.Range("H2:H$lastRow").FillDown()

# I列：状态判断（先写第一个再填充）
$STDSheet.Range("I2").Formula = '=IFS($H2="-","NA",$H2<=5,"OK",$H1>5,"NOK CONT",1,"NOK START")'
$STDSheet.Range("I2:I$lastRow").FillDown()

# 固定公式（仅设置一次）
$STDSheet.Range("I1").Formula = '="Nb Period with Min RT > "&5&"ms"'
$STDSheet.Range("J1").Formula = '=COUNTIF($I:$I,"NOK START")'

# 恢复设置并保存
$excel.Calculation = -4105
$excel.ScreenUpdating = $true
$workbook.Save()
$workbook.Close()
$excel.Quit()

# 释放COM对象
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($STDSheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($slaSheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

内容的提问来源于stack exchange，提问作者Empty Coder