Excel跨文件公式打开卡顿，寻求VBA或替代优化方案

VBA批量读取方案 #

VBA可以一次性读取所有目标文件的数据，避免公式反复调用外部文件的开销，具体实现如下：

1. 打开你的结果工作簿，按下Alt+F11打开VBA编辑器，插入新模块，粘贴以下代码：

Sub ExtractCPTypeData()
    Dim wbSource As Workbook
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim filePaths As Variant
    Dim i As Integer, lastRow As Long, destRow As Long
    
    ' 指定结果写入的工作表（自行修改为你的目标表名称）
    Set wsDest = ThisWorkbook.Sheets("Sheet1")
    destRow = 2 ' 结果从第2行开始写入，按需调整
    
    ' 填入所有25个目标文件的完整路径
    filePaths = Array( _
        "Y:\Reports\FI Daily Reports\1. CP Type Incorrect\CP Type Incorrect.xlsx", _
        "第二个文件的完整路径", _
        "第三个文件的完整路径" _
        ' 依次添加剩余文件路径
    )
    
    ' 关闭屏幕刷新和事件，大幅提升运行速度
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    For i = LBound(filePaths) To UBound(filePaths)
        ' 以只读模式打开源文件，避免锁定和误修改
        Set wbSource = Workbooks.Open(Filename:=filePaths(i), ReadOnly:=True)
        ' 指定源文件中的目标工作表（替换为实际工作表名）
        Set wsSource = wbSource.Sheets("BM_EU_20140705")
        
        ' 获取源数据M列的最后一行
        lastRow = wsSource.Cells(Rows.Count, "M").End(xlUp).Row
        
        ' 遍历行，判断M列是否为空，符合条件则写入L列数据到结果表
        For j = 2 To lastRow ' 假设源数据从第2行开始，按需调整
            If IsEmpty(wsSource.Cells(j, "M")) Then
                wsDest.Cells(destRow, "A").Value = wsSource.Cells(j, "L").Value ' 目标列按需修改
                destRow = destRow + 1
            End If
        Next j
        
        ' 关闭源文件，不保存任何修改
        wbSource.Close SaveChanges:=False
    Next i
    
    ' 恢复屏幕刷新和事件
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    MsgBox "数据提取完成！"
End Sub

2. 代码调整要点：

• 把filePaths数组里的路径替换为你的25个实际文件路径
• 修改wsDest为你要存放结果的工作表名称
• 源数据的起始行、结果写入的列可根据实际需求调整
• 代码采用只读模式打开源文件，不会影响原文件的使用

非VBA优化方案 #

如果不想用VBA，可通过以下方式降低跨文件公式的开销：

• 导入外部数据到本地工作簿：使用「数据」选项卡的「自Excel工作簿」功能，将25个文件的数据分别导入到当前工作簿的独立工作表中，之后公式引用本地单元格即可，避免跨文件实时链接。导入后可设置手动刷新，无需每次打开工作簿都自动计算。
• 用Power Query批量处理：Power Query能一次性批量导入文件夹内的所有文件，同时实现你需要的判断逻辑：
  1. 点击「数据」→「获取数据」→「从文件」→「从文件夹」，选择存放25个文件的文件夹
  2. 在Power Query编辑器中，添加自定义列，输入公式= if [M] = null then [L] else null
  3. 过滤掉自定义列为空的行，然后将数据加载到当前工作簿，后续只需点击「刷新全部」即可更新数据
• 关闭自动计算：打开工作簿前先设置「公式」→「计算选项」为「手动」，等需要更新数据时再手动触发计算，避免打开时自动计算所有跨文件公式导致的卡顿

内容的提问来源于stack exchange，提问作者Kazim H