如何通过PowerShell执行Excel AVERAGEIFS公式实现计算自动化

你写的PowerShell脚本没达到预期，核心是4个问题：

• 公式里的INSTANCE匹配规则写错了：你手动用的匹配符是"SG_SG_*_L_*"，脚本里多加了STD后缀写成"SG_SG_*_L_*STD"，根本匹配不到目标实例
• 写入列错位：原始4个字段占A到D列，追加结果应该放E列，你写到了F列，和你给的预期输出位置对不上
• 多了不必要的日期匹配条件：你手动执行的原始公式没有按日期逐行匹配的逻辑，脚本里额外加了SLA!$B:$B,SLA!B2的条件，统计范围不对
• 缺保存和资源释放逻辑：脚本写完公式既没存文件，也没退出Excel进程，改的内容不会落盘，还会在后台留僵尸Excel进程

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false

try {
    $workbook = $excel.Workbooks.Open("C:\SFile.xlsx")
    # 直接取存储原始数据的SLA工作表，避免取错工作表序号
    $slaSheet = $workbook.Worksheets.Item("SLA")
    # 取数据最后一行行号
    $lastRow = $slaSheet.Range("D2").CurrentRegion.Rows.Count

    # 给新增列写表头
    $slaSheet.Range("E1").Value2 = "统计平均响应时间"
    # 写入和手动逻辑完全一致的公式，从第2行到最后一行数据
    $slaSheet.Range("E2:E$lastRow").Formula = '=AVERAGEIFS(SLA!$D:$D,SLA!$C:$C,"SG_SG_*_L_*",SLA!$D:$D,"<>0")'

    # 如果要把结果写到其他工作表，取消下面这段注释即可
    <#
    $resultSheet = $workbook.Worksheets.Add()
    $resultSheet.Name = "统计结果"
    # 复制原始数据到结果表
    $slaSheet.Range("A1:D$lastRow").Copy()
    $resultSheet.Range("A1").PasteSpecial(-4163)
    # 结果表写入统计公式
    $resultSheet.Range("E1").Value2 = "统计平均响应时间"
    $resultSheet.Range("E2:E$lastRow").Formula = '=AVERAGEIFS(SLA!$D:$D,SLA!$C:$C,"SG_SG_*_L_*",SLA!$D:$D,"<>0")'
    #>

    # 保存所有修改
    $workbook.Save()
}
finally {
    # 释放COM资源，避免后台残留Excel进程
    if ($workbook) { $workbook.Close($true) }
    $excel.Quit()
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
    [GC]::Collect()
    [GC]::WaitForPendingFinalizers()
}

• 运行脚本前务必关闭正在打开的C:\SFile.xlsx，否则会因为文件占用报错
• 如果你实际需要逐行匹配和当前行同日期的平均响应时间，把公式替换为'=AVERAGEIFS(SLA!$D:$D,SLA!$C:$C,"SG_SG_*_L_*",SLA!$B:$B,SLA!B2,SLA!$D:$D,"<>0")'即可
• 如果需要固定结果的小数位数，可以在写入公式后加一行$slaSheet.Range("E2:E$lastRow").NumberFormat = "0.000000000"，对应你示例里的9位小数格式

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