动态单元格指针，包含存储在 IF 公式中的单元格中的文件路径

Question

我有以下公式(请注意，文件路径，小册子名称和工作表名称在此处显示为通用)

=IF('C:Potatodocuments[book.xlsm]sheet1'!$J$5>0,'C:documents[book.xlsm]sheet1'!$J$5,'C:documents[book.xlsm]sheet1'!$B$6)-'C:documents[book.xlsm]sheet1'!$J$5

上述公式没有问题。

现在我的问题： 我在单元格中有文件路径，假设K21在电子表格中是这样的：

C:Potatodocuments

我不允许更改此单元格的地址格式。路径指向包含booklet2的文件夹。请注意，我的公式在booklet1.

我想做的是能够在K21动态更改此地址，而无需更改公式。 以及在后续行中复制公式，并使K21动态更改为K22、K23、等。你明白了。

所以我正在寻找的理论是这样的：

=IF('**K21**[book.xlsm]sheet1'!$J$5>0,'C:documents[book.xlsm]sheet1'!$J$5,'**k21**[book.xlsm]sheet1'!$B$6)-'**k21**[book.xlsm]sheet1'!$J$5

当我将其复制到下面的行中以像这样更改时

=IF('**K22**[book.xlsm]sheet1'!$J$5>0,'C:documents[book.xlsm]sheet1'!$J$5,'**k22**[book.xlsm]sheet1'!$B$6)-'**k22**[book.xlsm]sheet1'!$J$5

现在我知道上述方法不起作用，因为单元格 K21 不会被解释为动态单元格指针。

我一直在研究间接功能，但没有运气。另外，我认为这对我没有用，因为我不想打开另一本小册子。

任何帮助将不胜感激。

查看我的研究链接：

• http://www.contextures.com/xlFunctions05.html
• https://www.ablebits.com/office-addins-blog/2015/02/10/excel-indirect-function/
• 在公式中使用预定义单元格中的文件路径
• 使用存储在单元格中的文件路径的公式
• https://www.excelforum.com/excel-general/743697-formula-with-cell-reference-in-file-path.html
• https://answers.microsoft.com/en-us/office/forum/office_2010-excel/how-do-i-use-a-pathfilename-in-a-cell-in-a-formula/faf0355f-88fa-427b-81b2-e7811eeaeb33?db=5
• https://superuser.com/questions/145326/adding-a-file-path-into-a-formula-that-is-typed-into-a-another-cell
• http://www.excel-ticker.com/create-and-use-dynamic-references-to-external-files-in-excel/
• https://www.lifewire.com/excel-sum-indirect-dynamic-range-formula-3124100

Answer 1

我对你的要求做了一些研究，似乎没有办法做到这一点。 根据您的 Excel 版本(年份，x32 x64)，我找到了以下解决方案(我有 Excel 2013 x64，但没有一个适合我)：

1. MoreFunc：MOREFUNC是Excel的加载项，为Excel 95-2007和2010和2013提供了62位版本的67个新工作表函数。 它包含这个函数：间接。EXT：返回关闭工作簿中包含的单元格或区域的内容

更多函数下载

2. VBA函数：我找到了 2 个 vba 函数。作为第一个解决方案，它的工作取决于Excel版本。

   - IndirectEx() ： Wilson So的扩展间接函数v1.0。

'------------------------------------ '扩展间接函数 v1.0 '------------------------------------'版权所有 (c) 2009 威尔逊·索。 '电子邮件：@'------------------------------------ '信用： '- 由Wilson So设计和编写。 "CreateObject("Excel.Application")"技巧的灵感来自Harlan Grove 的 PULL 函数源代码。 '------------------------------------ '这是一个开源。您可以 自由重新分发和修改它，但请注明 贡献者。'也请报告任何错误/建议 通过电子邮件或在我发布它的论坛中。 '------------------------------------ '如何使用： '- 基本相同 作为 Excel 中的 INDIRECT() - ref_text参数的概念相同。 "- 要更新特定引用的静态内存，请键入 TRUE 在第二个参数中(只是包含该参数的 IndirectEx() 之一 参考)'并计算一次。 '------------------------------------'特点： '- 您可以参考 关闭的工作簿数据。'- 检索到的已关闭工作簿数据将 存储在静态存储器中，'所以在下一次，关闭 工作簿将不会再次打开以进行快速检索。'- 一个范围 而不是数组 如果路径被省略 ref_text，'所以如果用户引用一个巨大的 数组，例如"Sheet1！1：65536"。'- 你可以在 INDEX() 中使用它， VLOOKUP()， MATCH() etc. '- 你可以将它与 OFFSET() 一起使用，但仅适用于 打开的工作簿数据。'- 该过程不会盲目地检索所有 所要求的数据;' 它不会检索超出"Ctrl + 结束"单元，以保持内存尽可能小。'——#NUM！ 如果缺少内存，将返回。'——#REF！将被退回 万一路径错误。'——#VALUE！如果出现其他情况，将退回 错误。'------------------------------------ '已知问题： '- 由于 使用SpecialCells()，#VALUE！如果工作表 对于封闭的工作簿是受保护的。 '-----------------------------------

-

Function IndirectEx(ref_text As String, Optional refresh_memory As Boolean = False) As Variant
On Error GoTo ClearObject
Dim RefName As String
Dim SheetName As String
Dim WBName As String
Dim FolderName As String
Dim vExcel As Object
Dim vWB As Workbook
Static dbOutput() As Variant
Static dbKey() As String
Static dbTotalOutput As Integer
Dim dbIndex As Integer
Dim UserEndRow As Long, UserEndCol As Integer
Dim RealEndRow As Long, RealEndCol As Integer
Dim EndRow As Long, EndCol As Integer
Dim RangeHeight As Long, RangeWidth As Integer
GetNames ref_text, RefName, SheetName, WBName, FolderName
If dbTotalOutput = 0 Then
ReDim dbOutput(1 To 1) As Variant
ReDim dbKey(1 To 1) As String
End If
For i = 1 To dbTotalOutput
If dbKey(i) = FolderName & WBName & "!" & SheetName & "!" & RefName Then
dbIndex = i
End If
Next
If dbIndex = 0 Or refresh_memory Then
If dbIndex = 0 Then
dbTotalOutput = dbTotalOutput + 1
dbIndex = dbTotalOutput
ReDim Preserve dbOutput(1 To dbTotalOutput) As Variant
ReDim Preserve dbKey(1 To dbTotalOutput) As String
dbKey(dbIndex) = FolderName & WBName & "!" & SheetName & "!" & RefName
End If
If FolderName = "" Then
Set dbOutput(dbIndex) = Workbooks(WBName).Worksheets(SheetName).Range(RefName)
ElseIf Dir(FolderName & WBName) <> "" Then
Set vExcel = CreateObject("Excel.Application")
Set vWB = vExcel.Workbooks.Open(FolderName & WBName)
With vWB.Sheets(SheetName)
On Error GoTo ClearObject
UserEndRow = .Range(RefName).Row + .Range(RefName).Rows.Count - 1
UserEndCol = .Range(RefName).Column + .Range(RefName).Columns.Count - 1
RealEndRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
RealEndCol = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
EndRow = IIf(UserEndRow < RealEndRow, UserEndRow, RealEndRow)
EndCol = IIf(UserEndCol < RealEndCol, UserEndCol, RealEndCol)
RangeHeight = EndRow - .Range(RefName).Row + 1
RangeWidth = EndCol - .Range(RefName).Column + 1
On Error Resume Next
dbOutput(dbIndex) = .Range(RefName).Resize(RangeHeight, RangeWidth).Value
If Err.Number <> 0 Then
IndirectEx = CVErr(xlErrNum)
GoTo ClearObject
End If
End With
On Error GoTo ClearObject
vWB.Close False
vExcel.Quit
Set vExcel = Nothing
Else
IndirectEx = CVErr(xlErrRef)
Exit Function
End If
End If
If TypeOf dbOutput(dbIndex) Is Range Then
Set IndirectEx = dbOutput(dbIndex)
Else
IndirectEx = dbOutput(dbIndex)
End If
Exit Function
ClearObject:
On Error Resume Next
If Not (vExcel Is Nothing) Then
vWB.Close False
vExcel.Quit
Set vExcel = Nothing
End If
End Function
Private Sub GetNames(ByVal ref_text As String, ByRef RefName As String, ByRef SheetName As String, ByRef WBName As String, ByRef FolderName As String)
Dim P_e As Integer
Dim P_b1 As Integer
Dim P_b2 As Integer
Dim P_s As Integer
P_e = InStr(1, ref_text, "!")
P_b1 = InStr(1, ref_text, "[")
P_b2 = InStr(1, ref_text, "]")
P_s = InStr(1, ref_text, ":")
If P_e = 0 Then
RefName = ref_text
Else
RefName = Right$(ref_text, Len(ref_text) - P_e)
End If
RefName = Replace$(RefName, "$", "")
If P_e = 0 Then
SheetName = Application.Caller.Parent.Name
ElseIf P_b1 = 0 Then
SheetName = Left$(ref_text, P_e - 1)
Else
SheetName = Mid$(ref_text, P_b2 + 1, P_e - P_b2 - 1)
End If
SheetName = Replace$(SheetName, "'", "")
If P_b1 = 0 Then
WBName = Application.Caller.Parent.Parent.Name
Else
WBName = Mid$(ref_text, P_b1 + 1, P_b2 - P_b1 - 1)
End If
If P_s = 0 Then
FolderName = ""
Else
FolderName = Left$(ref_text, P_b1 - 1)
End If
If Left$(FolderName, 1) = "'" Then FolderName = Right$(FolderName, Len(FolderName) - 1)
End Sub

- PULL() ： by Harlan Grove

"灵感来自鲍勃·菲利普斯和洛朗·朗格尔"，但由哈兰撰写 格罗夫 '----------------------------------------------------------------- '版权所有 (c) 2003 哈兰格罗夫。' '此代码是自由软件;你 可以根据 GNU 的条款重新分发它和/或修改它 由自由软件基金会发布的通用公共许可证; 许可证的第 2 版，或(根据您的选择)任何更高版本 版本。 '-----------------------------------------------------------------

Function pull(xref As String) As Variant
Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, c As Range, n As Long
pull = Evaluate(xref)
If CStr(pull) = CStr(CVErr(xlErrRef)) Then
On Error GoTo CleanUp                    'immediate clean-up at this point
Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add           'needed by .ExecuteExcel4Macro
On Error Resume Next                     'now clean-up can wait
n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)
Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))
If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)
Else
For Each c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
Next c
pull = r.Value
End If
CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing
End If
End Function