如何实现INDEX-MATCH自动重引用数组及VBA自动选指定工作簿

问题1：INDEX-MATCH更新Button表后返回#N/A，需手动回车修复 #

原因分析 #

原公式依赖INDIRECT(CELL("address"))，CELL函数结果会被Excel缓存，且INDIRECT属于惰性求值函数，Button表更新后不会自动触发重新匹配；跨工作簿引用的缓存也会导致旧数据未更新。

解决方案 #

方案1：优化公式，移除缓存依赖

将原公式中的INDIRECT(CELL("address"))替换为当前单元格的直接引用，避免缓存问题：

• A1格式：=INDEX(BUTTON!A:C,MATCH(@,BUTTON!A:A,0),3)
• R1C1格式：=INDEX(BUTTON!C[-3]:C[-1],MATCH(RC,BUTTON!C[-3],0),3)

方案2：批量更新后强制全量刷新

在批量更新数据的宏末尾添加以下代码，强制Excel重建所有公式依赖，自动刷新结果：

' 强制全量计算并重建公式依赖，彻底刷新所有数据
Application.CalculateFullRebuild

问题2：Macro2宏自动选择名为TOLEDO的工作簿 #

原因分析 #

录制的宏中[sheet15]是临时工作簿名称，Excel无法识别，因此弹出文件选择窗口；需直接引用TOLEDO工作簿，且先确保该工作簿已打开。

修改后的Macro2代码 #

Sub Macro2()
    Dim wbToledo As Workbook
    Dim wbTarget As Workbook
    
    ' 设置当前操作的目标工作簿（即正在更新的14个工作簿之一）
    Set wbTarget = ActiveWorkbook
    
    ' 检查TOLEDO工作簿是否已打开，未打开则尝试打开（需替换为实际文件路径）
    On Error Resume Next
    Set wbToledo = Workbooks("TOLEDO.xlsx") ' 注意替换为实际扩展名，如.xlsm
    On Error GoTo 0
    
    If wbToledo Is Nothing Then
        ' 如果未打开，自动打开TOLEDO工作簿（请修改为实际文件路径）
        Set wbToledo = Workbooks.Open("C:\YourPath\TOLEDO.xlsx")
    End If
    
    ' 直接给单元格区域赋值公式，避免使用Select提升效率
    With wbTarget.Sheets("你的工作表名称") ' 替换为实际目标工作表名称
        ' D4:M7区域公式
        .Range("D4:M7").Formula2R1C1 = _
            "=INDEX('" & wbToledo.Name & "'!BUTTON!C[-3]:C[-1],MATCH(RC,'" & wbToledo.Name & "'!BUTTON!C[-3],0),3)"
        ' N4:X7区域公式
        .Range("N4:X7").Formula2R1C1 = _
            "=INDEX('" & wbToledo.Name & "'!BUTTON!C[-13]:C[-11],MATCH(RC,'" & wbToledo.Name & "'!BUTTON!C[-13],0),2)"
        ' Y4:AD5区域公式
        .Range("Y4:AD5").Formula2R1C1 = _
            "=INDEX('" & wbToledo.Name & "'!BUTTON!C[-24]:C[-17],MATCH(RC,'" & wbToledo.Name & "'!BUTTON!C[-24],0),7)"
        ' Y6:AD7区域公式
        .Range("Y6:AD7").Formula2R1C1 = _
            "=INDEX('" & wbToledo.Name & "'!BUTTON!C[-24]:C[-17],MATCH(RC,'" & wbToledo.Name & "'!BUTTON!C[-24],0),8)"
    End With
    
    ' 强制刷新所有公式
    Application.CalculateFullRebuild
End Sub

代码说明 #

• 自动检查TOLEDO工作簿是否已打开，未打开则自动打开（需替换为实际文件路径）
• 移除冗余的Select操作，直接给单元格区域赋值公式，提升运行效率
• 使用wbToledo.Name动态引用工作簿名称，避免硬编码临时文件名
• 末尾添加CalculateFullRebuild确保公式自动刷新最新数据

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