如何将Excel硬编码的跨工作簿查找函数改造为动态引用版本

方案1：VBA自定义函数（支持未打开的外部工作簿，无需修改现有文件结构） #

你之前调用WorksheetFunction报错是因为该方法无法直接识别未打开文件的引用路径，改用ExecuteExcel4Macro方法即可实现对关闭工作簿的读取，可用以下自定义函数：

Function GetExternalHours(folderPath As String, wbName As String, shtName As String, searchDate As Date, returnCol As Long) As Variant
    Dim fullPath As String
    Dim matchRow As Long
    ' 补全路径格式
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    fullPath = "'" & folderPath & "[" & wbName & ".xlsm]" & shtName & "'!"
    
    On Error Resume Next
    ' 遍历匹配日期行，可根据实际数据量调整最大行号
    For matchRow = 1 To 1000
        If CDate(ExecuteExcel4Macro(fullPath & "A" & matchRow)) = searchDate Then
            ' 匹配成功返回对应列值
            GetExternalHours = ExecuteExcel4Macro(fullPath & Cells(matchRow, returnCol).Address(, , xlR1C1))
            Exit Function
        End If
    Next matchRow
    ' 未匹配到返回空值
    GetExternalHours = ""
End Function

使用方法：在汇总表目标单元格输入公式 =IFERROR(GetExternalHours("D:\Folder1\FolderA\",$A$4,A5,G$2,3),"") 即可，不需要提前打开源工作簿。

方案2：Power Query批量拉取（无代码，适合人员异动频繁的场景） #

无需写公式或代码，可自动同步源文件数据，维护成本极低：

• 点击汇总表「数据」选项卡→「获取数据」→「来自文件」→「来自文件夹」，选择源文件存放路径D:\Folder1\FolderA
• 在弹出的文件列表中筛选后缀为.xlsm的目标文件，点击「组合」→「合并并编辑」，选择统一的人员工作表结构批量加载所有产能数据
• 将清洗完成的数据加载到汇总表隐藏工作表，后续直接用普通的INDEX+MATCH/XLOOKUP公式匹配对应维度的数值即可，人员异动时仅需点击「数据」→「全部刷新」就能同步最新数据

方案3：可接受打开源文件时的简化方案 #

如果允许每次汇总时提前打开所有用到的源工作簿，原来的INDIRECT公式仅需补充路径前后的单引号即可正常运行，修正后的公式如下：

=IFERROR(
    INDEX(
        INDIRECT("'D:\Folder1\FolderA\["&$A$4&".xlsm]"&A5&"'!$A:$Q"),
        MATCH(
            G$2,
            INDIRECT("'D:\Folder1\FolderA\["&$A$4&".xlsm]"&A5&"'!$A:$A"),
            0
        ),
        3
    ),
""
)

注意路径首尾的单引号为必填项，是Excel识别跨表引用的格式要求

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